Within Microsoft Dynamics AX 2012 R2 it is possible to download exchange rates from providers who offers online download files. Out of the box three service providers are available, also the European Central Bank exchange rates. The standard service as developed for AX 2012 R2 has a limitation to download the rates from the last 90 days. For a conversion we needed older rates. But is it possible to download them? The answer is yes! Continue reading and you will get two nice tricks on the download of the exchange rates.
Before I will continue I have to say that the standard functionality is sufficient for the normal downloads of the exchange rates. The exchange rate provider for the European Central Bank has the history for 90 days. When you start the download for a date range, there is a limitation of downloading the rates for a maximum of 31 days. So when downloading rates daily, weekly or monthly, these constraints are naturally.
In this blog post I will only tell about the functionality for the European Central Bank. I have not looked into the possibility on the other exchange rate providers.
Remove 90 days history limitation
When you look at the standard setup for the exchange rate providers, the setup for the Central Bank of Europe has three parameters.
Now the trick is simple. It is possible to change the value of the HistoricalServiceUrl in http://www.ecb.europa.eu/stats/eurofxref/eurofxref-hist.xml. This URL has the same XML file format, but contains the exchange rates starting in 1999. So if you have the requirement to download the rates of 2012 and 2013 for conversion purposes, you can use this URL instead of the standard. When you open this link in an internet browser it is very slow due to the large amount of data. Surprisingly it had no problems on performance when downloading the rates from January 2012. An additional suggestion is to enable the checkbox Create necessary currency pairs when you need old Currencies now been replaced by e.g. the Euro.
Remove the 31 day period limitation
When you try to download the rates for a longer period, e.g. 100 days, you will receive the error stating that the date range cannot exceed 31 days.
Well, this is a limitation designed in AX, it is not due to this particular Exchange rate provider. Also here the trick is simple. Click the Edit button on the Infolog or browse the Application Object Tree (AOT) for the class named ExchangeRateImportRequest. This class contains the method validate where the error is included. This limitation is not needed because of the exchange rate providers. I think this will protect you from creating performance issues and/or wrong wrong parameter entries. As mentioned earlier in this post, the 31 days limitation is normally not a problem.
When you want to download more days within one import action or just likes to see what will happen, you can disable this validation. When you have to download the history starting from 2012, you have to start the Import currency exchange rates function more than 24 times in a row. So to decrease the number of clicks and executing this task, disable the validation, and you can import the rates for e.g. 800 days in one go.
On my test machine it took about 5 minutes to download the rates starting from January 1 in 2012 until today. Due to the number of warnings when no rates can be found for a currency pair the Infolog will stop at the default 10000 lines limit. No exchange rates are provided for e.g. weekend days. So not all warning for a very large number of day scan be handled.
The rates needed are in the system, so now and I will remove the changes on the ExchangeRateImportRequest class to have the default validation enabled again.
When you are really in the need for importing a large number of rates, this post is helpful. Consider it as a temporary change which will be reversed after you completed the bulk load. The functionality out of the box is sufficient for the daily needs.
That’s all for now. Till next time!