Currency Rates function

Ronald Grentzius

Hello,

I have tried to use the automatic update of the currency rates by using the example which is posted on this forum. I have changed the code as instructed, but every time I get the failure message that no currency rates can be found on yahoo.

Does anybody use this function as it was already posted in 2006?

If it still works hopefully someone can help. I have adjusted the url and when I tested the url in a browser a csv file is downloaded without any problems.

Thanks!

I used the following code:

// The Path to the Yahoo Quotes Service
// Abholen von akutellen Wechselkurs 
// Replace EURCHF with USDCHF, GBPCHF, ... Visit finance.yahoo.com
try
{
SwissAddonFramework.UI.Components.Matrix mtr = (SwissAddonFramework.UI.Components.Matrix) SwissAddonFramework.UI.Components.Matrix.GetFromUID(pVal.Form, "9");
string cur = mtr.GetValue("2", pVal.Row - 1).ToString();
 
 
string fullpath = @"http://de.old.finance.yahoo.com/d/quotes.csv?s=EURXXX=X&f=sl1d1t1ba&e=.csv";
 
if(cur.Equals("Amerikaanse dollar"))
{
fullpath = fullpath.Replace("XXX", "USD");
}
else if (cur.Equals("Hong Kong Dollar"))
{
fullpath = fullpath.Replace("XXX", "HKD");
}
else if (cur.Equals("Engels pond"))
{
fullpath = fullpath.Replace("XXX", "GBP");
}
 
// Create a HttpWebRequest object on the Yahoo url
System.Net.HttpWebRequest webreq = (System.Net.HttpWebRequest) System.Net.WebRequest.Create(fullpath);
 
// Get a HttpWebResponse object from the Yahoo url
System.Net.HttpWebResponse webresp = (System.Net.HttpWebResponse) webreq.GetResponse();
 
// Create a StreamReader object and pass the Yahoo Server stream as a parameter
System.IO.StreamReader strm = new System.IO.StreamReader(webresp.GetResponseStream(), System.Text.Encoding.ASCII);
 
// Read a single line from the stream (from the server) 
// We read only a single line, since the Yahoo server returns all the
// information needed by us in just one line.
string csv = strm.ReadLine();
 
// Close the stream to the server and free the resources.
strm.Close();
string rate = csv.Substring(csv.IndexOf(";") + 1);
rate = rate.Substring(0, rate.IndexOf(";"));
 
 
// Add Code to Fill the Value
SwissAddonFramework.Messaging.SendKeys.Send(rate.Replace(",", "."));
}
catch
{
SwissAddonFramework.Messaging.StatusBar.WriteError("No Currency Rate found by yahoo");
}

Paolo Manfrin

Hello Ronald,
please could you modify the code with the following one?

I just changed a couple of lines to have a more significant error:

// The Path to the Yahoo Quotes Service
// Abholen von akutellen Wechselkurs 
// Replace EURCHF with USDCHF, GBPCHF, ... Visit finance.yahoo.com
string cur="";
try
{

SwissAddonFramework.UI.Components.Matrix mtr = (SwissAddonFramework.UI.Components.Matrix) SwissAddonFramework.UI.Components.Matrix.GetFromUID(pVal.Form, "9");
cur = mtr.GetValue("2", pVal.Row - 1).ToString();

string fullpath = @"http://de.old.finance.yahoo.com/d/quotes.csv?s=EURXXX=X&f=sl1d1t1ba&e=.csv";
 
if(cur.Equals("Amerikaanse dollar"))
{
fullpath = fullpath.Replace("XXX", "USD");
}
else if (cur.Equals("Hong Kong Dollar"))
{
fullpath = fullpath.Replace("XXX", "HKD");
}
else if (cur.Equals("Engels pond"))
{
fullpath = fullpath.Replace("XXX", "GBP");
}

 
// Create a HttpWebRequest object on the Yahoo url
System.Net.HttpWebRequest webreq = (System.Net.HttpWebRequest) System.Net.WebRequest.Create(fullpath);
 
// Get a HttpWebResponse object from the Yahoo url
System.Net.HttpWebResponse webresp = (System.Net.HttpWebResponse) webreq.GetResponse();
 
// Create a StreamReader object and pass the Yahoo Server stream as a parameter
System.IO.StreamReader strm = new System.IO.StreamReader(webresp.GetResponseStream(), System.Text.Encoding.ASCII);
 
// Read a single line from the stream (from the server) 
// We read only a single line, since the Yahoo server returns all the
// information needed by us in just one line.
string csv = strm.ReadLine();
 
// Close the stream to the server and free the resources.
strm.Close();
string rate = csv.Substring(csv.IndexOf(";") + 1);
rate = rate.Substring(0, rate.IndexOf(";"));
 
 
// Add Code to Fill the Value
SwissAddonFramework.Messaging.SendKeys.Send(rate.Replace(",", "."));
}
catch
{
SwissAddonFramework.Messaging.StatusBar.WriteError("No Currency Rate found by yahoo for currency " + cur);
}

It seems like one of the currency you are tring to update is not taken into consideration.

Please post me the error you get after executing the code above.
Could you also send a screeshot about the form "Set Rate For Selection Criteria"? (it's the form where you're currently running the function rule)
Thank You,
paolo

Ronald Grentzius

Hello Paulo,

Thanks for the support!

I have tested you updated code and get a failure notice:

"No Currency Rate found by Yahoo for currency"

Because there is no currency in the failure notice I have tested this with all of the 3 currency's I tried to update. But all currency's have the same failure.

Best regards,

Ronald

Attachment
[url=http://www.coresystems.ch/wp-content/../wp-content/forum-image-uploads/r-grentzius/Customize_currency.jpg]Customize_currency.jpg[/url]

Paolo Manfrin

Hi Ronald,
please could you export the full .cocu rule and tell me which localization are you using?
thanks

Ronald Grentzius

Hi Paolo,

Attached the cocu file. Our system is set to use euro as standard currency and the notation is 'indirect'.

Best regards,

Ronald

Attachment
[url=http://www.coresystems.ch/wp-content/../wp-content/forum-file-uploads/r-grentzius/Fin.cocu]Fin.cocu[/url]

Paolo Manfrin

Hi Ronald,
please make sure that you are with your cursor over the right rate you want to update.
Consider the following example:
You need to have an if else statement for the currency US Dollar into your code

else if (cur.Equals("US Dollar"))
{
fullpath = fullpath.Replace("XXX", "USD");
}

and before running your rule, click with the mouse into the correspondent cell (Row "Us Dollar", Column "Rate") then right click and update the value.
If you keep your cursor into the date field as seems from your previous snapshot then the value will not be updated.
Regards,
Paolo

Attachment
[url=http://www.coresystems.ch/wp-content/../wp-content/forum-image-uploads/paolo-manfrin/error.png]error.png[/url]

Ronald Grentzius

Hi Paolo,

Thanks, it seems to work! Only one issue left, after the update the decimals are separated by a point: 0.84 for example. Because of this the currency is saved as 84,00 instead of 0,84. Can this be changed?

Thanks,

Ronald



Attachment
[url=http://www.coresystems.ch/wp-content/../wp-content/forum-image-uploads/r-grentzius/Currency_update.jpg]Currency_update.jpg[/url]

Paolo Manfrin

Hi Ronald,

replace

SwissAddonFramework.Messaging.SendKeys.Send(rate.Replace(",", "."));

with

SwissAddonFramework.Messaging.SendKeys.Send(rate);

Cheers,
Paolo

Ronald Grentzius

Hi Paolo,

Thanks, works perfectly!

Best regards,

Ronald

Ronald Grentzius

Hi Paolo,

One more question to optimize the working of this function (also maybe usefull for other users of this function).

The url in the code downloads "old" currency rates. When I checked the url today (20th of september) I saw the currency rates returned in the csv file were from the 17th of September. As we update our currency rates daily, I need to have actual rates.

Maybe you have had this question before; Do you have an url which up dates the rates with real-time information?

I found the url http://quote.yahoo.com/d/quotes.csv?s=EURXXX=X&f=l1&e=.csv returns the real time currency rate, only this url is not in the right format for the customize rule to be used.

This url also returns 4 decimals for the rate, which is more accurate then the 2 decimals in the current url.

Thanks for all help!!

Ronald

Paolo Manfrin

Here you are Ronald

// The Path to the Yahoo Quotes Service
// Abholen von akutellen Wechselkurs 
// Replace EURCHF with USDCHF, GBPCHF, ... Visit finance.yahoo.com
string cur = "";
try
{
 
SwissAddonFramework.UI.Components.Matrix mtr = (SwissAddonFramework.UI.Components.Matrix) SwissAddonFramework.UI.Components.Matrix.GetFromUID(pVal.Form, "9");
cur = mtr.GetValue("2", pVal.Row - 1).ToString();
 
//string fullpath = @"http://de.old.finance.yahoo.com/d/quotes.csv?s=EURXXX=X&f=sl1d1t1ba&e=.csv";
string fullpath = @"http://download.finance.yahoo.com/d/quotes.csv?s=EURXXX=X&f=l1&e=.csv";
 
if(cur.Equals("Engels pond"))
{
fullpath = fullpath.Replace("XXX", "GBP");
}
else if (cur.Equals("Hong Kong Dollar"))
{
fullpath = fullpath.Replace("XXX", "HKD");
    }
else if (cur.Equals("Engels pond"))
{
fullpath = fullpath.Replace("XXX", "GBP");
}
else if (cur.Equals("Euro"))
{
fullpath = fullpath.Replace("XXX", "EUR");
}
else if (cur.Equals("US Dollar"))
{
fullpath = fullpath.Replace("XXX", "USD");
}
 
 
// Create a HttpWebRequest object on the Yahoo url
System.Net.HttpWebRequest webreq = (System.Net.HttpWebRequest) System.Net.WebRequest.Create(fullpath);
 
// Get a HttpWebResponse object from the Yahoo url
System.Net.HttpWebResponse webresp = (System.Net.HttpWebResponse) webreq.GetResponse();
 
// Create a StreamReader object and pass the Yahoo Server stream as a parameter
System.IO.StreamReader strm = new System.IO.StreamReader(webresp.GetResponseStream(), System.Text.Encoding.ASCII);
 
// Read a single line from the stream (from the server) 
// We read only a single line, since the Yahoo server returns all the
// information needed by us in just one line.
string csv = strm.ReadLine();


// Close the stream to the server and free the resources.
strm.Close();
/*
string rate = csv.Substring(csv.IndexOf(";") + 1);
rate = rate.Substring(0, rate.IndexOf(";"));
*/

string rate = csv; 
 
// Add Code to Fill the Value
SwissAddonFramework.Messaging.SendKeys.Send(rate);
}
catch
{
SwissAddonFramework.Messaging.StatusBar.WriteError("No Currency Rate found by yahoo for currency " + cur);
}

Ronald Grentzius

Good morning Paolo,

Works perfect! Small detail: the values where with a point again, but this was solved easily as you informed me how this works before:

I have replaced:
SwissAddonFramework.Messaging.SendKeys.Send(rate);

With:
SwissAddonFramework.Messaging.SendKeys.Send(rate.Replace(".", ","));

To change the point into a comma.

Thanks for the great service, this really helps us out!!!

Best regards,

Ronald