Automate your Treasury Operations: Get the latest Exchange Rate for Financial & Accounting Needs

[gsharan]

Application Integration - Proficiency levels - Intermediate

Detailed Scenario

Exchange rates are crucial within an enterprise for various financial and accounting needs that includes

1. Accurate Financial Reporting
2. Risk Mitigation
3. International Operations
4. Compliance and Taxation
5. Efficient Resource Allocation:

Given the importance of this, we want to ensure all internal systems within the enterprise are using the latest and refer to a single source of truth. This brings in the need for integration and automation.

The entire process of collecting and storing the latest exchange rate will be automated using application integration with drag and drop user experience and low-code.

To achieve this, we propose that we use XE.com, which publishes exchange rates on a daily basis, and that we use BigQuery as our System of Record (SoR) that other applications within the organization can look up for exchange information.

There are two major blocks involved in this:

1. Retrieve exchange rates from XE.com.
2. Write the exchange rates to a BigQuery table.

Note: You can use other websites like Bloomberg.com to pull Foreign currency exchange rates. Also you can use other Google cloud Storage solutions like Cloud SQL,Cloud Spanner etc to store extracted Exchange Rates from XE.com

Detailed Steps:

In this scenario we will cover below key steps:

1. Setup up Big Query as our System of Record to hold Exchange Rates
2. Establish connection to Big Query
3. Design the automation flow that connects to XE.com in Application integration to fetch Exchange rates using API on a daily basis
4. Map Data between XE and and Big Query Schema in a Sub Integration
5. For every currency rate, invoke the Sub integration to insert into Big Query
6. Monitor Logs for successful execution of integration
7. Validate Exchange rates in Big Query Table

Note:

• In the example below, Convert FROM API query endpoint is to convert from USD (Default used by XE.com) to all available currencies (the asterisk * (=%) represents ALL currencies).

Request url - https://xecdapi.xe.com/v1/convert_from?to=%2A

• Please note that this is just a suggestion, and the specific frequency and use cases for updating exchange rates may vary depending on the organization’s needs.

1. Create Big Query Exchange Rates Table:

If you are new to Big Query, please refer to this documentation to createdataset and Tables. For this use case, I have created below Datasets as “FX_Rates”

and Table as “FX Rate from XE com”. To keep it simple, I created this table manually by adding each column and type. There are various ways to create table schema

2. Connect Big Query to Integration Connector:

If you are new to Integration connectors for Big Query Please refer to this documentation for step by step guidance to establish connections between BQ and Integration Connectors**.** For this use case I have created connector as “fx-rate-update”

3. Connect and configure XE.com to the Application integration to fetch Exchange rates using API

Create an Integration with the name of “FX_Rate_Update_to_BQ” and then follow below steps.

• Schedule Trigger: Usually Business wants to update the Exchange rates on a daily basis so from Triggers let us use schedule trigger with below configurations:

• Authorization: XE.com uses Basic Auth with API ID:API Key as the value. Use your generated API ID:API Key and convert to Base64 and we will use it in our next step

Reference: XE.com documentation says - “The API uses HTTP Basic Access Authentication, which requires that a properly constructed «Authorization» header be included in your HTTP request. This header will need to include your Xe Currency Data API account ID and API key in an encoded form”

• Call REST Endpoint to XE.com: Configure the Rest API endpoint by populating the API url provided by XE.com and Basic Authorization of API ID:API Key base64 value as shown below in the screenshot

4. Create Data Mapping rules between APIs query and Big Query table

The API response used by XE.com for exchange rate comes with the below format. Its Response Body has an Array of “to currency” value against object “from currency” of USD as show below

So we decided to configure in this way by creating the Sub integration and then calling this sub integration in loop to post “to currency” value against common “from currency” of USD (Note - There could be various other ways to configure this integrations)

1. Convert XE.com response to Jason.

2. Create Sub integration - “SubIntegration-for-FXrates” to create a rule to create 1:1 mapping with the BQ table.

a. Create an API trigger to start the integration.

b. Map API response to Big Query table fields in “Data Mapping 1”. In this step, creating 1:1 mapping between API response and the Big Query table fields

Also in this step create an input Variable for “to currency” values as this will be used in the loop in step 5 below. Use JSON schema and copy “To currency schema” from XE.com response body as shown in Step 4

c. Connect with the Big Query table created in Step 1. In this step connect your Big Query Table connector created in step 2 “fx-rate-update” to the current Sub integration

Sub Integration once configured should look like this:

5. Create Parallel processing configuration to call Sub integration and push API Array data to BQ:

From Step 4, return to the main integration created in Step 3 and configure the For Each Parallel task.

There are several ways to process the data. One option is to use the For Each Loop task, which will make repeated calls to a sub-integration from your current (main) integration until all rates are posted for Arrey for rates against USD rate. This process takes time and consumes a lot of resources.

Therefore, I have used the For Each Parallel task, which allows you to run other integrations (sub-integrations - SubIntegration-for-FXrates) from your main integration. As the task name suggests, the sub-integrations are run in parallel, which can significantly reduce the processing time. This is lots more faster approach when there is no dependency of data

At the end, end to end integration for “FX_Rate_Update_to_BQ” should look like this:

6. Monitor Logs for successful execution of integration:

After execution you can monitor the log by clicking on the logs and selecting the Integration “FX_Rate_Update_to_BQ”

7. **Validate Exchange rates in Big Query Table - “**FX Rate from XE com”

You can view the FX rates in the BigQuery table by clicking the View table button in the BigQuery table details page. The FX Rate Update to BQ integration is a simple and easy-to-use way to keep your BigQuery table up-to-date with the latest FX rates.

[willie]

Thanks for posting @gsharan