Exchange Excellence, Eh? Connecting NAV 2017 and Business Central Currency Exchange Rate Services to the Bank of Canada

Currency rates – the thing that makes us all multimillionaires in Indonesia and not so much in Kuwait. In our personal lives, currency rates may be the antagonists on our credit card statements while on vacation, however to a business they play an important and strategic role in managing both working capital, operational profitability, and financial risk. From natural hedges to forwards, options, and cross-currency swaptions, the choices for mitigating currency risk are legion and there are entire books written on the effective control and management of any risks associated with currency rate fluctuation.

One of the big pain points that Microsoft addressed in the last few iterations of Dynamics NAV was the tedium of having to manually enter the F/X rates for your currencies in the system by allowing you to connect to a web service that provides those rates. Out of the box, they have an example connection connecting to the Yahoo! exchange rates service, but what if you’re looking to connect to the Bank of Canada’s service instead? Although below are the steps you’d take to set that connection up in NAV 2017, they will be very similar to the ones in Dynamics 365 for Financials.

The very first step in the process is to find the web feed that we can pull into NAV. Perusing the Bank of Canada’s website brings us to this page below, with a link to the XML feed:

Copy that link, because we’ll need to paste that into NAV in a moment.

Note that you don’t have to use the Bank of Canada’s exchange rates – if your alternative provider also has a web feed, you can also consume that, but the settings below will naturally be different to handle that data.

Following the step numbers indicated in the screenshot, you need to:

1. Give this Service a meaningful code and description (e.g. “BOC” and “Bank of Canada daily exchange rates”);
2. Paste in the XML URL you copied from the Bank of Canada’s site into the Service URL textbox;
3. (Optional) Paste in the Terms of Service for the data (you can typically find this in the header of the XML feed itself);

Now that we’re done with that, we need to define the fields from the file by moving on to the Field Mapping FastTab in the screen.

The very first thing you’ll notice is that NAV will prompt you to map what it requires from the file (the Parent Node, Currency Code, etc.) Thankfully you don’t need to read through the XML file to try and figure this out, you can click the little AssistEdit buttons in the Source Field:

Clicking that brings up the schema explorer, where you can see the XML elements as well as sample data for that element:

The way that the Bank of Canada structured their feed, you’d need to use the Series (“s”) element as both the Parent Node for the currency, but also Currency Code element. The Date (”d”) element is what houses the Starting Date, and the Value (“v”) element is what contains the Relational Exchange Rate. The Exchange Rate itself will be a default value of “1”. At this point you should have a mapping that looks like this:

But you might’ve already noticed when we were using the schema explorer that the Bank of Canada uses a strange “name” for their currencies – in the Example Value above it appears as FXAUDCAD, which isn’t the same code that we’d have in NAV (which would just be “AUD”).

As such, we need to use a Transformation Rule to extract just the “AUD” part of the string. So, on the Currency Code row, click on the drop-down arrow and click “New.”

In this window, we’ll set up a Transformation Rule that performs a “substring” (i.e. a smaller string within a longer string), starting at position 3 that is 3 characters long. You can see the preview result in the bottom of the window:

This will now ”extract” the appropriate NAV Currency Codes from the Bank of Canada’s internal currency code.

As of this point, you can click the Preview button in the ribbon to vet the results:

Looks good! So let’s click on the “Enabled” checkbox on the Currency Exchange Rate Service card to activate this new service. Once you do that, you’ll be notified that a Job Queue (an automatic scheduled process) has been created and you then click “Yes” to open that up.

You can read more about Job Queue Entries in the NAV documentation, but in a nutshell, this screen allows you to specify what days and how often to go fetch these rate and update your exchange rates.

Simple as that – you’re now on your way to always have the most up-to-date Bank of Canada rates for your NAV/Business Central currencies! Again, the same methodology above will apply to whatever currency exchange rate service or feed you’d like to connect to, with some variations on the theme to handle that service’s specific data nuance.

• Mastering the Month End Close Process with NAV: The General Ledger
• Mastering the Month End Close Process with NAV: Accounts Payable and Receivable
• Mastering the Month End Close Process with NAV: Bank Reconciliation & Foreign Exchange
• Mastering the Month End Close Process with NAV: Fixed Assets and Inventory