Exchange Rate Conversion with Power Automate

Many of us will work in organisations that have a global presence and therefore may have a need to know the value of something in a local currency. There isn’t any out of the box way to convert a base currency to another currency in Power Automate, so I wanted to see how easy it would be to configure.

The Solution

For the purposes of this blog I’m going to configure a simple manually triggered flow that will take a number input for a value to convert, and the base currency from which we are converting, and it will return this value at it’s current exchange rate in 10 different currencies. There are two parts to this solution:

  1. Configure a Custom Connector to retrieve current exchange rates
  2. Configure the Flow to output the converted value

Configuring a Custom Connector

As there is no out-of-the-box connector to do exchange rate conversion, we need to create our own. If you’ve not created a custom connector before, it is really straightforward; you just need an API that you can access with the appropriate authorisation and then it is a really simple process. Joe Unwin, AKA Flow Joe, covered the steps to creating a custom connector on his blog here.

As I needed an API to access, a bit of google-fu led me to this free Exchange Rates API, which really handily allows you to request the rates against a specific currency by setting the base parameter in the requet:

So, how do we set up this Custom Connector? There are basically 2 main steps for this one, as there is no authentication required.

Step 1 – input the host URL in the General Information section of the Custom Connector:

Step 2 – as we saw above, the Open Exchange Rates API allows us to request the latest rates by specifying a base currency parameter. To add this to our custom connector we simply add a new Action in the Definition section, then under the request heading we click Import from Sample and paste the URL with the parameter into the flyout window and specify it as a GET Request. To show just how easy it is, watch the gif below:

Now that we have the custom connector created, we’re ready to create the Flow

COnverting Values with a Flow

This Flow is pretty straightforward so I’ll run through each step in turn:

1. Manually trigger a Flow – for the purposes of this demo I’m using a manual trigger, and I’m specifying two inputs: the value to convert and the base currency we’re converting from. Of course, if you were implementing this in your environment any trigger could be used, as long as you’re providing these input values

2. Initialize RateConversion Array variable – In this array I want to specify the Currency name, Currency Code (ISO 4217 code) and the Locale (BCP 47 Code). The Currency Code is used to identify the exchange rate we’ll be converting to in the returned results, while the Locale will be used as part of a formatNumber expression later in the Flow. The Array I’m using is:

[
  {
    "Currency Name": "US Dollar",
    "Currency Code": "USD",
    "Locale": "en-US"
  },
  {
    "Currency Name": "British Pound Stirling",
    "Currency Code": "GBP",
    "Locale": "en-GB"
  },
  {
    "Currency Name": "Japanese Yen",
    "Currency Code": "JPY",
    "Locale": "ja-JP"
  },
  {
    "Currency Name": "Euro",
    "Currency Code": "EUR",
    "Locale": "fr-FR"
  },
  {
    "Currency Name": "Swedish Krona",
    "Currency Code": "SEK",
    "Locale": "sv-SE"
  },
  {
    "Currency Name": "Australian Dollar",
    "Currency Code": "AUD",
    "Locale": "en-AU"
  },
  {
    "Currency Name": "Canadian Dollar",
    "Currency Code": "CAD",
    "Locale": "en-CA"
  },
  {
    "Currency Name": "New Zealand Dollar",
    "Currency Code": "NZD",
    "Locale": "en-NZ"
  },
  {
    "Currency Name": "South African Rand",
    "Currency Code": "ZAR",
    "Locale": "en-ZA"
  },
  {
    "Currency Name": "Brazilian Real",
    "Currency Code": "BRL",
    "Locale": "pt-BR"
  }
]

3. Parse RateConversion Array – in this step we’re using a Parse JSON action to enable us to use the elements from the array we create above later in the Flow. The Schema for this step is:

{
    "type": "array",
    "items": {
        "type": "object",
        "properties": {
            "Currency Name": {
                "type": "string"
            },
            "Currency Code": {
                "type": "string"
            },
            "Locale": {
                "type": "string"
            }
        },
        "required": [
            "Currency Name",
            "Currency Code",
            "Locale"
        ]
    }
}

4. Retrieve Latest Rates – in this step we’re going to use our Custom Connector that we created earlier to retrieve the latest rates. As you can see, because we specified a “base” parameter, it is asking us for that input in the action. We’re using the “Base Currency” input from our trigger in this field

The output from this step looks like:

{ 
   "rates":{ 
      "CAD":1.7211925099,
      "HKD":10.0868447976,
      "ISK":164.0533917057,
      "PHP":65.6558566704,
      "DKK":8.8888624521,
      "HUF":403.8758952152,
      "CZK":29.5962311737,
      "GBP":1.0,
      "RON":5.670251493,
      "SEK":12.4872112113,
      "IDR":17741.190606486,
      "INR":92.5961835875,
      "BRL":5.6254015085,
      "RUB":81.7923338647,
      "HRK":8.871850389,
      "JPY":142.7942611054,
      "THB":40.3863998668,
      "CHF":1.2663874943,
      "EUR":1.1896547622,
      "MYR":5.3701015965,
      "BGN":2.3267267839,
      "TRY":7.8425610888,
      "CNY":9.0500606724,
      "NOK":11.9494872588,
      "NZD":2.0024268957,
      "ZAR":19.1850864879,
      "USD":1.2983892075,
      "MXN":24.1825882129,
      "SGD":1.7989959314,
      "AUD":1.9242665778,
      "ILS":4.4403863999,
      "KRW":1532.2753336982,
      "PLN":5.062456875
   },
   "base":"GBP",
   "date":"2020-02-12"
}

5. Initialize ConvertedRates string variable – In this step we’re just creating an empty string variable that we’ll add to when we loop through the Array we created in step 2 to convert our values

6. For each requested Rate – We’re going to loop through each of the Rates in the Array we created in Step 2 to convert them

6A. Check that there is a value returned for the requested Rate – this condition is to allow for some error handling in the event that the rate we’ve requested in our Array isn’t returned in the results from our Custom Connector. The expression we’re using is

body('Retrieve_Latest_Rates')?['rates']?[items('For_Each_Requested_Rate')['Currency Code']]

6B. Append No Conversion Available to String – If the condition above returns a negative result then we’ll use an Append to String Variable action to append a note that there is No Conversion Available for the requested rate. This ensures consistency of results

6C – Append Converted Value to String – if the condition at step 6A returns a positive result then we want to multiply the requested value from our trigger by the exchange rate returned, then format the results in the local currency. The expression we use to do this is:

formatnumber(mul(triggerBody()['number'], float(body('Retrieve_Latest_Rates')?['rates']?[items('For_Each_Requested_Rate')['Currency Code']])), 'C', items('For_Each_Requested_Rate')['Locale'])

Lets break this down from inside out:

float(
body('Retrieve_Latest_Rates')?['rates']?[items('For_Each_Requested_Rate')['Currency Code']])

For this part of the expression, we’re interrogating the JSON that is returned from our custom connector in Step 4 above to find an exchange rate. We’re using the Currency Code for the current item in our Array to find the related Exchange Rate. We then need to convert this string to a Float value using the Float expression so we can use it in our multiplication

mul(triggerBody()['number'],[FLOAT_VALUE_FROM_ABOVE])

Now that we’ve returned a Float value, we’re going to use a mul expression to multiply the number we input in our trigger by this value.

formatnumber([MULTIPLICATION_RESULT], 'C', items('For_Each_Requested_Rate')['Locale'])

The final step is to take the value that we’ve returned from our multiplication and format it in a manner that’s representative of the currency we’re displaying. The formatNumber expression allows us to specify that the number we’re working with is a Currency, and the Locale that we retrieve from the array specifies exactly what currency the value is.

7. Respond to a PowerApp or Flow – for this demo I’m assuming we’d want to return the string of converted values to a PowerApp or Flow, but of course you could use this output for any appropriate purpose

The output of the Flow for an input of 10000 GBP looks like:

US Dollar: $12,983.89
British Pound Stirling: £10,000.00
Japanese Yen: ¥1,427,943
Euro: 11 896,55 €
Swedish Krona: 124 872,11 kr
Australian Dollar: $19,242.67
Canadian Dollar: $17,211.93
New Zealand Dollar: $20,024.27
South African Rand: R191 850,86
Brazilian Real: R$ 56.254,02

Conclusion

This is a relatively simple Flow that shows how easy it is to configure a Custom Connector and use an open API in the context of a Flow. Converting values to other currencies could be useful in a number of scenarios, in particular if you are using multiple currencies in your Dynamics 365 environment where keeping the exchange rates up to date is vitally important.

See the Flow in action below:

Published by

One thought on “Exchange Rate Conversion with Power Automate

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s