Free Exchange Rates from Bank of Canada in Dynamics NAV & Dynamics 365 Business Central: Currency Exchange Rate Services
Companies that use foreign currencies need to enter exchange rates periodically into their ERP system to ensure that AP & AR transactions as well as Journal transactions and, eventually, Bank and GL balances are accurate. There two ways that exchange rates can be entered in NAV and Dynamics 365 Business Central (D365BC): 1) Manually on the “Currency Exchange Rates” page or 2) Automatically using the “Currency Exchange Rate Services” function. The focus of this blog post is on the latter.
Importantly, the discussion in this article and the above video are pertinent to NAV 2016, 2017, 2018, and D365BC. Currency Exchange Rate Services can be set up to get rates from any Web source that provides information in the acceptable format, this post explores how this can be done using the Bank of Canada (BOC) website. This website is available free of charge to the public and the services can also be enabled free of charge, unlike some other Web sources providing exchange rate information. It is worth mentioning that a post on the topic has already been published on the Encore blog however, due to the changes in how the BOC provides rates and to add more details to the earlier post, I’ve decided to revisit the topic here.
In order to set up Currency Exchange Rate Services, it is necessary to open the corresponding List first (Departments> Administration> IT Administration> Services >Administration> Currency Exchange Rate Services). Once there, it is necessary to click on “New” in the Ribbon which opens the corresponding Page.
The Fields that are of particular interest on this Page are:
- Code – This is the brief name of the service
- Description – This is the detailed description of the service
- Enabled – This is the Field that is going to be ticked off at the very end of all setup as enabling it prevents from setting it up further
- Service URL – The Web link to the service that’s being setup
- Service Provider – The official name of the service provider
- Terms of Service – The link to the official terms of service
- Log Web Requests – This function may help troubleshoot any issues with the service setup
Once completed, the “General” and “Service” FastTabs should look like the screenshot shown below:
The link to BOC website should look like this: http://www.bankofcanada.ca/valet/observations/group/FX_RATES_DAILY/xml?start_date=2018-07-01 – the date at the end of the link can be manually changed to reflect the start day of the Currency Exchange Rate Service – which is the earliest date that the system should update the Currencies to. For example, if …2018-07-01 is entered, the first time the service is run, it will overwrite all the currency rates in the system (even if those have been entered manually) on July 1, 2018 and later.
Once the “General” and “Service” FastTabs are filled out, it possible to get down to the “Field Mapping” FastTab. The five Caption lines are mandatory to be able to set up BOC service and these are pre-set up in the out-of-the-box system, all that remains to do is provide Source, Default, and Transformation Rules values:
1. Parent Node for Currency Code – Clicking on the ellipsis button in the “Source” Field opens a Page where it is possible to select the Parent Node for Currency Code – highlight it and click “OK” – this populates the “Source” Field [alternatively, you can enter the Source manually]; if you are have set this up correctly, it should look like this: /data/observations/o/v;
2. Currency Code – Repeat the steps above and select the line where the Currency Code appears; if set up correctly, it should look like this: /data/observations/o/v/@s; since the BOC website provides rate codes in the following format FXEURCAD (where ‘FX’ and ‘CAD’ are invariable values and the three letters in between are the variable foreign currency codes), it is necessary to set up a Transformation Rule as shown on the below screenshot:
By setting it up this way, we are telling the system: take a Substring (a portion of the text) starting at Position 3 (e.g. E) and take three characters (e.g. EUR). If your Currency Codes are in the FX…CAD format, then you do not need a Transformation Rule. If your Currency Codes are anything other than standard “EUR”, “USD”, etc., then the above Transformation Rule will not work and you will have to come up with a different one. The next important Caption lines and the related Fields are:
3. Starting Date – Repeat the steps above to get the following: /data/observations/o/@d;
4. Exchange Rate Amount – Should have nothing in the “Source” Field, and “1” in the “Default Value” – this is the money amount to exchange rates for (e.g. what is the rate of 1 EUR compared to 1 CAD);
5. Relational Exchange Rate Amount – Repeat the steps above to get the following: /data/observations/o/v – this where the system is getting the value of foreign exchange currency in the amount of 1.
Once the “Field Mapping” FastTab is set up, it should look like below:
Lastly, it is necessary to enable the Currency Exchange Rate Service – this can be done by clicking on the “Enabled” button in the General FastTab. This, in turn, opens the “Job Queue Entry” Page which can be set up as shown below:
The Fields that may be of interest are:
- Earliest Start Date/Time – On what date and at what time should the service run for the first time – by default, the system suggests today’s date in Windows, but it can be overwritten
- Recurring Job – Typically this Field should be ticked, which will further enable the Fields related to the days of week; since BOC provides rates Monday through Friday, there is no need for checking rates on Saturdays and Sundays, therefore, these Fields should remain without a check mark
- Number of Minutes Between Runs – Since exchange rates are provided daily on the BOC website, it makes sense to set this Field to 1440 minutes corresponding to 24 hours
- The set up above helps automate exchange rate updates and is based on the BOC daily service as an example. If you would like to setup other monthly or yearly updates only, see Useful Links below.
- The BOC service can only update those currencies that are provided by BOC. Therefore if your company uses for example; UAH (Ukrainian hryvnia), this currency will not get updated. For the full list of the currencies for which BOC currently provides updates, see Bank of Canada Currencies below.
- If your company’s local currency is other than CAD, then you have to consider a different service provider, as BOC has discontinued providing reciprocal rates, meaning that users are unable to use the BOC service for any local currency other than CAD. Other providers may charge a fee for their services.
To recap, this blog post has discussed how foreign currency rates can be updated automatically using the “Currency Exchange Rate Services” function in NAV 2016, 2017, 2018 + and D365BC. There are at least three benefits of using this tool: it saves time (1), helps improve accuracy of rates entered (2) and can update rates shortly after they become available to help ensure that invoices are issued using the most up-to-date rates (3). The implementation of the “Currency Exchange Rate Services” function has been discussed in connection with BOC. One of the benefits of the service is the fact that it is provided for free to the public and can be fully integrated with the tool as shown in this blog post. The limitation of using the BOC service lies in the fact that it only relies on CAD as the local currency and has a limited set of currencies.
At Encore, our Solution Specialists help companies set up Currency Exchange Rate Services and functions in the most efficient and effective way. The team at Encore are happy to answer your questions about Dynamics 365 Business Central and NAV to help you and your company achieve your ERP goals – feel free to contact us!
Bank of Canada Useful Links
- Terms of Service
- Daily average exchange rates (published once each business day by 16:30 ET)
- Monthly average exchange rates (published by 16:30 ET on the last business day of each month)
Attention: Transformation Rule Substring: Start Position: 4, Length 3
- Annual average exchange rates (published by 16:30 ET on the last business day of each year)
Attention: Transformation Rule Substring: Start Position: 4, Length 3
Bank of Canada Currencies
Is Your ERP Software Hurting Your Business?
Discover 15 real-world inventory, accounting, and process red flags that indicate your ERP software is hurting your business.Get The White Paper