Daily Bank of Canada Exchange Rates

jamesdlyn Microsoft Dynamics 365 FO

It has been a while since I have last posted. A lot has happened in my career in the past couple of years and I was out of the Dynamics world for a bit. Alas, I have returned however not to GP, but instead to Dynamics 365 Finance and Operations.

I figured I would start small in journaling some of my light development work that could prove useful to other D365FO users with access to the Power Platform. The Power Platform (BI, Automate and Apps) opens us up to more lightweight integrations using low/no-code solutions. When the available connectors work, they work great. When they do not, well, then other alternatives need to be explored. In the case of daily updating exchange rates it works quite while (at least in Canada).

The Bank of Canada provides its exchange rates in easy to digest formats through its Valet API (https://www.bankofcanada.ca/valet/docs), most notably in JSON. Modifying the URL to query on a specific date is simple:

https://www.bankofcanada.ca/valet/observations/group/FX_RATES_DAILY/json?start_date=2021-01-07&end_date=2021-01-07

This returns all CAD to foreign currency closing rates for January 7th, 2021. Each rate is returned as part of the “observations” object:

This is great and all, but how do we get it into D365FO? Depending on your licensing levels, it is as simple as creating a Flow in Power Automate to do it for you.

NOTE: The following requires the HTTP Request and Dynamics 365 connectors which are premium connectors. Premium connectors are available for any paid Power Automate plan starting at $15/month (at time of writing) and I highly recommend it for anyone wanting to automate repetitive tasks in the system. You may also get access to the Dynamics 365 connectors as part of your D365 license, though you may not have access to the HTTP Request connector so be sure to check your access rights.

Flow overview:

  1. Setup recurring flow daily to run in the morning. I set mine to run at 7am to fetch the previous day’s close rate.
  2. Send an HTTP Request to the BoC Valet API to request rates for the day before
  3. Parse the JSON for the currency(ies) you want use in D365FO. For each currency, I modify the schema to only return the currency object I want. I repeat this for each currency so change any instance of “FXUSDCAD” to “FX###CAD” where ### is the currency code you wish to retrieve.
{
    "type": "object",
    "properties": {
        "observations": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "d": {
                        "type": "string"
                    },
                    "FXUSDCAD": {
                        "type": "object",
                        "properties": {
                            "v": {
                                "type": "string"
                            }
                        }
                    }
                },
                "required": [
                    "d",
                    "FXUSDCAD"
                ]
            }
        }
    }
}
  1. Check to make sure a “v” object was returned which indicates a value exists for the day requested. Querying on a weekend for example would not return a value.
  2. Create exchange rate record in D365FO using the “Create record” connector.
  3. [Optional] Post a message to Teams using the Flow Bot to let yourself know it ran

I grab USD, EUR and GBP so I have three concurrent branches where the only difference is the Parse JSON schema and the currency code being updated:

And there you have it, simple daily exchange rates into Dynamics, at least if you are in Canada. For everyone else you could take the inverse of the rate if you only care about the exchange rate with CAD.

Disclaimer: I am very much an amateur programmer. I have never taken an official Microsoft course. I cannot guarantee any code/program that I provide as I only have the ability to test my work on a small sample set. Users accept all the risk of using anything I provide and are encouraged to thoroughly test in a dedicated test environment before moving to production. Files I provide are free for use and are not to be resold. By using my files you agree to not hold me liable for any damages caused by said files.

Leave a comment