Exchange Excellence, Eh? Connecting NAV 2017 and Business Central Currency Exchange Rate Services to the Bank of Canada - Catapult
Catapult brand logo
Article

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.

Find the Feed

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:

Bank of Canada Daily Exchange Rates

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.

Configuring NAV

Create a new Currency Exchange Rate Service

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:

Parent Node for Currency Code

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

Schema Explorer

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:

Field Mapping

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.”

Transformation Rules

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:

Edit Transformation Rule

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:

Currency Exchange Rules

Running on a Queue

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.

Update Currency Exchange Rate

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.

Questions or Comments?

Feel free to reach out to us if you have any additional questions or comments about connecting Dynamics NAV 2017 or Business Central to Currency Exchange Rate Services.

You can also check out Month End Close blog series for more information:

Register to receive the latest Dynamics 365 Insights

Catapult reinvented consulting and implementation services for the subscription economy.

Subscription packages that include everything you need to get up and running, and transform your business

  • Icon

    Lean
    Deployment

  • Icon

    On-Demand
    Training

  • Icon

    Platform
    Upgrades

  • Icon

    Amazing
    Support

  • Icon

    Software
    Licensing

Packages from

$2,000

/mo

36-month commitment

Get Pricing

Catapult Team

Invest in training. Long-term success comes from making sure that your users are prepared to adopt the changes to the way they do their jobs!

Jeff Bickle, Team Lead Customer Engagement