Currency Conversion

Getting Real-time Currency Conversion for Other Currencies

If you would like your currency converted into something other than AUD, EUR, GBP, or USD, here's a way to convert them in real time on your metrics.

1. In your metric, add a new report and select Custom Rest API for the data source.
2. In the Data Settings for the new report, enter http://data.fixer.io/api/latest in the URL field.
3. Modify the base currency to the desired currency by using the following Parameter:
   • Key: base
   • Value: USD
   The default base currency in fixer.io is Euros (EUR).
4. Make sure the Request Method is set to GET.
5. Click on Show Advanced Settings, and check the Rotate Table Data checkbox.

6. Select Get Data to get the following response in your table:
   
7. Then, by using a substring, we can extract the date line and column A to just the three-letter currency code.
   In the builder, you can clean up the table of data by adding a SQL transform and enter the following SQL query:

   • SELECT SUBSTRING("success",7,10) as "currency", "true" AS "conversion_rate" 
     FROM report
     WHERE "success" NOT IN ('timestamp', 'base', 'date')

   And that should give you a chart similar to this:

   

   This allows Grow to match up the currency conversion in this report with the currencies in your original report.

8. To finish this out, use a Joined Report to join the two and display the converted currencies with your original data.

Getting Historical Data

What if you want to see a currency conversion for the last month or the last year?

This does have a couple of limitations. First, the date ranges are not dynamic. This means that you have to set the dates to a specific range (i.e. June 1, 2017 to May 31, 2018).

Second, fixer.io limits the historical data to 12 months back from the end date. So, if you set the end date to April 30 of this year, you can pull data up to May 1st of last year, regardless of today's date.

To connect to historical rate data, add a new report:

1. Select Custom REST API Connection as the data source.
2. After selecting the connection, you need to enter in the URL and Header sections:
   • URL: http://data.fixer.io/api/timeseries
   • Key: date
   • Value: YYYY-MM-DD
     
3. After you have entered the headers, enter the parameters. To add multiple parameters, click Add Parameter.

   

   • Key: base
   • Value: AUD
     This value is the three-letter currency symbol that your data is in.
   • Key: symbols
   • Value: USD
     This value is the three-letter currency symbol that you are converting your data into.
   • Key: end_date
   • Value: 2018-05-31
     The end date is the most recent date for which you want the currency conversion.
   • Key: start_date
   • Value: 2017-06-01
     The start date is when you want to begin pulling the currency conversions back.

     

4. Once you have completed the above steps, click Get Data to bring in the data. This returns the exchange rate between those currencies, with each column representing one day within that date range.
5. To clean up the data, add a SQL transform and run this query:

SELECT SUBSTRING("success",7,10) as "date", "true" AS "conversion_rate"
FROM report
WHERE "success" NOT IN ('timeseries', 'start_date', 'end_date', 'base');

You can then join this report with another report that has the daily currency values you want to convert.

This example only uses the conversion rate from one currency to another. If you are pulling in historical data for multiple currencies, then you will want to adjust the SQL statement to create a currency column as well:

SELECT SUBSTRING("success",7,10) as "date", SUBSTRING("success",18,3) as "currency", "true" AS "conversion_rate"
FROM report
WHERE "success" NOT IN ('timeseries', 'start_date', 'end_date', 'base');