Exchange Excellence, Eh? Connecting Dynamics 365 for Financials and NAV 2017 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

Next, we need to hop into NAV and create a new Currency Exchange Rate Service, so navigate to the Currency Exchange Rate Services page and click New, which opens up the page below:

 

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


Like What You've Read? Subscribe to our Newsletter!

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/D365F 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 me if you have any additional questions or comments about connecting Dynamics NAV 2017 or Dynamics 365 for Financials to Currency Exchange Rate Services. Or, leave a comment below.

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

Share this post

Comments

  1. www.forexrobotnation.com

    I appreciate you for this, I am going to absolutely readily share it with my
    favorite audience.

  2. Kenneth

    Very nice! Best Tut on exchange setup i’ve seen 🙂
    Everything is set up… but i still get no currencies when pressing Preview.
    Currency Exchange Rates is empty 🙁 This is true for every source of .xml i’ve tried. I don’t know why. My setup follows this tutorial 100 %.

    1. Elliot Fishman

      Hi Kenneth, thanks for the feedback and sorry you are having difficulties. I’ve emailed you and Ramin directly to follow up. Hope we can help!

Leave a Comment

Your email address will not be published. Required fields are marked *

*

Narrow your topic

Archives

Get ERP & CRM Insights

Subscribe for the latest ERP/CRM Insights delivered to your inbox.