Currency exchange rates in Qlik Sense – Fast, Reliable & Free

If you too like me need a solution to retrieve currency exchange rates in Qlik Sense, for free if possible 😉, then this post is for you.

In this post we will see together how to easily and for free get the exchange rates of one currency against 32 other currencies without going through the services of Qlik DataMarket.

We will use a free and open source API called exchangeratesapi.io. The API offers a service for current and historical foreign exchange rates published by the European Central Bank.

To call this service and retrieve the exchange rates in Qlik Sense, we will use the native REST connector.

The API service is without any rate limits or api key requirements 🙏.

How to use the API?

The exchangeratesapi API supports a number of parameters that will allow us to retrieve the exchange rate of the currencies of our choice on the date of our choice.
Here is the structure of the URL:

https://api.exchangeratesapi.io/latest[|date]?base=’XXX’&symbols=’YYY,ZZZ’

If no parameters are passed, the API will return the latest exchange rate for all available currencies against Euro.
The possible parameters are:
date (optional): If you want to get the latest exchange rates, use ‘latest‘, otherwise put a date from 1999 in the format ‘yyyy-MM-dd’.
base (optional): the base currency. By default it is ‘EUR’.
symbols (optional): the list of currencies for which you want to have the exchange rate against the base currency.

In my example I want to retrieve the exchange rates of the different currencies against the Euro as of August 31, 2020.
The URL I’m going to use will be:
https://api.exchangeratesapi.io/2020-08-31?base=’EUR’

Create a connection with the REST connector in Qlik Sense

Once we have chosen the exchangeratesapi.io API params, we can now create a connection in Qlik Sense with the REST connector.
As a reminder, the REST connector in Qlik Sense allows you to make http requests (GET and POST). In our case, it will be a GET request.
To create a connection with the REST connector, follow these steps:

• Open your Qlik Sense app
• Go to Data Load Editor
• Click on Create new connection
• Choose REST connector
• In the URL field, enter https://api.exchangeratesapi.io
• Leave all options as default
• Enter a name for the connection. Example: REST_ExchangeRates
• Click on Test Connection to test the connection
• If the connection is correctly established, click Save

After creating the connection, use the code below. I modified the code generated by the REST connector using the Crosstable function to put all currencies in the Currency field and their conversion rates in the Rate field.

You can find and copy this code here.

After reloading the data, the result will be like this:

Now according to your needs you can modify the code by changing the base currency, the list of currencies you want to have and the date.
If you want to have the exchange rates for multiple dates, I advise you to loop through the list of dates, then call the API with each date and concatenate the result.

Hope this article helps you to easily integrate currency exchange rate into your Qlik Sense applications.
If you haven’t already done so, subscribe 👇 to be notified of the publication of future articles.