Automating Currency Conversion: Streamline Financial Processes with Power Automate
Introduction
In the fast-paced world of claims management, precision and efficiency are crucial. Companies worldwide face the challenge of processing claims in multiple currencies, necessitating a reliable solution for real-time currency conversion. This blog delves into an approach using Microsoft Power Automate and a custom connector to fetch live currency exchange rates, improving the accuracy and consistency of financial analysis in claims management.
Overview of the Solution Components
Automating Currency Conversion : Power Automate and Custom Connector
The architecture for automating currency conversion centres around three key components:
Custom Connector: This component is responsible for connecting with a financial data API to fetch real-time currency exchange rates. It acts as a bridge between the external currency data source and your internal systems.
Power Automate: Utilizes the custom connector to trigger API calls for the latest currency rates. It orchestrates the flow of data, ensuring that currency conversion rates are updated as per the configured schedule, whether it's hourly, daily, or in real-time.
Database Update: Once the latest exchange rates are retrieved, Power Automate updates the currency table in your database, ensuring that all financial data reflects the most current rates for accurate conversion and reporting.
Creating a Custom Connector in Power Automate for Exchange Rates
In this section, we'll walk through the steps to create a custom connector in Power Automate, which will enable us to fetch live exchange rates from the ExchangeRates API provided by apilayer. This setup will form the backbone of our automated currency conversion solution, allowing us to retrieve up-to-date currency information seamlessly within our workflows.
Access ExchangeRates API:
Begin by visiting apilayer's ExchangeRates API page.
With the free subscription, you're allowed 100 requests per month. Once you sign in, an API key is generated, which is crucial for accessing the API.
Navigate to Custom Connectors in Power Automate
In your Power Automate dashboard, locate the "Custom Connectors" menu in the left panel, click on "New custom connector" and select "Create from blank."
Assign a meaningful name to your connector that reflects its purpose, like "ExchangeRatesConnector."
Configure the General Settings
In the general section, set the schema to
httpsand enterapi.apilayer.comas the Host value. This configures the connector to communicate with the ExchangeRates API.
Set Up the Security
In the security section, opt for "No authentication" for simplicity. This choice is made under the assumption that the API key will be provided directly in the Power Automate flow.
Define the API Details
Move to the definition section. Here, you will specify the actions that the connector will perform. Provide a concise summary, a detailed description, and an operation ID that uniquely identifies this operation.
Set up the action to make a GET request to the URL
https://api.apilayer.com/exchangerates_data/latest.Add query parameters for the API request. You will need parameters for the base currency and the API key, ensuring the API fetches the correct data.
Define the response structure. Since the API's default response format is typically JSON, you can set it as the default response type, ensuring the connector correctly interprets the data from the API.
Testing the Connector
Finally, in the test section, input the base currency (like EUR) and your API key to validate that the connector is set up correctly and can retrieve data from the ExchangeRates API. This step is crucial to ensure that your connector is ready to be used in flows.
Building the Power Automate Flow for API Calls and Data Updates
Power Automate Flow & Custom Connector - Currency Exchange Rates
Recurrence Trigger
Initiate your flow with a scheduled trigger called Recurrence.
Configurable frequency: Adjust the schedule to fit your requirements.
Customizable timing: Specify the day and time for the flow to trigger.
Regular updates: Ensures consistent currency data refresh without manual effort.
Invoke the Custom Connector
Add a new step to call the custom connector that you created, which is set up to fetch the latest exchange rates from the ExchangeRates API. You will find it in the ‘custom’ section.
You will need to provide:
The ISO currency code for the base currency (e.g., EUR).
The API key that was generated when you signed up for the service.
The connector will send a request to the API and receive a JSON response containing the latest exchange rates like this:
{ "success": true, "timestamp": 1709401023, "base": "EUR", "date": "2024-03-02", "rates": { "AED": 3.985487, "AFN": 78.312185, ... } }
Retrieve Current Currency Data
Use ‘List rows’ action to fetch the currency data from Dataverse.
You can use any other database where the currency table is stored. For example, SharePoint List or SQL table. You can use the respective List rows steps for SharePoint or SQL in Power Automate.
Important point is that we need currency name, currency code, exchange rate columns in our table.
Iterate and Update Records
Initiate 'Apply to each' Step: Start by adding the 'Apply to each' step, looping through the currency list from your database.
Utilize the 'List rows' Output: Feed the 'Apply to each' step with the 'value' output from the 'List rows' action, which enumerates the database currencies.
Configure 'Update a row' Step: Incorporate an 'Update a row' action, selecting the Currencies table. Pass the unique ID for each currency being iterated over by the 'Apply to each' step.
Apply Power Automate Expression: To accurately update each currency, use the expression: float(body('Get_exchange_rates')?['rates']?[items('For_Each_Requested_Rate')['isocurrencycode']])
This formula dynamically fetches the exchange rate for the current currency in the loop from the JSON response obtained via the custom connector.
Update the Exchange Rate Field: Place this formula in the 'Exchange Rate' field within the 'Update a row' step, ensuring each currency's exchange rate is updated to the latest value provided by the API.
Conclusion
Efficiently managing currency exchange rates is crucial for businesses involved in international transactions. Utilizing Microsoft Power Automate and a custom connector enables the automation of currency rate updates, streamlining financial accuracy and operational efficiency.
This process involves creating a custom connector for live exchange rates, setting up a Power Automate flow to fetch and update these rates regularly, and ensuring your financial data reflects the latest market trends. This automation not only conserves resources but also minimizes errors linked to manual updates, supporting more informed decision-making and maintaining financial integrity.
