Update exchange rates automatically on Dynamics 365

The Currency field in Microsoft Dynamics 365 is designed to manage monetary values across different currencies. Specifically, it is particularly beneficial for businesses operating in multiple countries or dealing with international customers, vendors, or transactions.

For instance, Dynamics 365 allows you to set up exchange rates for various currencies. As a result, when you enter transactions in a foreign currency, the system automatically calculates the equivalent amount in your company’s base currency using the configured exchange rate.

However, there is no built-in automation for updating exchange rates. Consequently, updating rates manually is a time-consuming task. To address this issue, you can use Power Automate along with an exchange rate API of your choice. In this post, I’ll demonstrate how to use the Bank of Canada’s Valet API to fetch the latest USD/CAD exchange rate. Furthermore, I’ll show you how to create a flow that updates the rate daily in Dynamics 365. By the way, you can access BOC’s Valet API here.

When you are done your flow will look like this:

First, login to create a scheduled flow on Power Automate and set your schedule to once a day.

Step 1 – Base Currency: Add a List rows action and select Organizations table and set Expand Query to  

basecurrencyid($select=isocurrencycode)

Step 2 – Send HTTP Request: Add a HTTP request action, set to GET and URI to  

https://www.bankofcanada.ca/valet/observations/FXUSD/CAD/json?recent=1

Note: If you can’t use the HTTP request action try using a custom connector. You can refer to this article for custom connectors. 

Step 3 – Parse Exchange Rate: Add a Parse JSON action and set content to Body of HTTP Request action. You can use the sample payload below to generate the schema.

{
  "terms": {
    "url": "https://www.bankofcanada.ca/terms/"
  },
  "seriesDetail": {
    "FXUSDCAD": {
      "label": "USD/CAD",
      "description": "US dollar to Canadian dollar daily exchange rate",
      "dimension": {
        "key": "d",
        "name": "Date"
      }
    }
  },
  "observations": [
    {
      "d": "2024-11-22",
      "FXUSDCAD": {
        "v": "1.3979"
      }
    }
  ]
}

Step 4 – USD Exchange Rate: Add Select action to get the USD/CAD exchange rate from the returned JSON. Insert Body observations for From and map CAD to Body v for the rate.

Step 5 – Initialize USD-CAD variable: Add an Initialize Variable action and set Name to USDCAD, type to Float and Value to

float(first(body('USD_Exchange_Rate'))?['CAD])

Step 6 – Get all currencies in Dataverse: Add another List Rows action and select Currencies as the table name and enter the XML query below in Fetch XML Query box.

<fetch>
   <entity name="transactioncurrency >
      <link-entity name="organization" from="basecurrencyid" to="transactioncurrencyid" link-type="outer" alias="org" />
         <filter type="and" >
            <condition entityname="org" attribute="basecurrencyid" operator="null" />
         </filter>
   </entity>
</fetch>

Step 7 – Update USD rate on Dataverse: Add an Apply to each loop and  set Output to body/value from Get All Currencies step.

Add an If condition. Set Condition to isocurrencycode is equal to USD.

And for the last step, add an Update a row action and then set:

• Table Name to Currencies.
• Row ID to transactioncurrencyid from Appy to each.
• And set Exchange Rate property to USDCAD variable.

Please don’t forget to test your flow and follow me for future content.🙂