Currency Conversion

Using the Currency Conversion Datasets

To help make it easier for you to convert your currency, we have created four specific datasets that bring in the up-to-date conversion rates for AUD, EUR, GBP, and USD.

If you have a currency type column that specifies the currency being used in the report (e.g., USD, EUR, etc.), then bring in the dataset that shows the conversion rates for your desired currency.

Next, add a Joined Report and add the report with your data as "Connection 1" and the dataset as "Connection 2." Make sure to select the column that has the type of currency (e.g., USD) as the common value for Connection 1 and "Currency Code" for the common value in Connection 2.

After you've made the joined report, add a Calculated Column transform to multiply the currency value with the currency rate ("Currency Rate per ___") to get the converted value for the desired currency.

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.

In your metric, add a new report and select Custom Rest API for the data source.

In the Data Settings for the new report, enter the following:

URL: http://data.fixer.io/api/latest

Note: The default base currency in fixer.io is Euros (EUR). But you're in luck! You can modify the base currency to the currency by using the following Parameter:

Key: base

Value: USD

Check the Advanced options box, and make sure the Request method is set to GET. You will also need to check the Rotate table data checkbox.

If you press connect, this will give you the following response in your table:

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 will allow Grow to match up the currency conversion in this report with the currencies in your original report.

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 be able to see a currency conversion for the last month or the last year?

This does have a couple of limitations. First, you will not be able to make the date ranges dynamic. This means that you will 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 I set the end date to April 30 of this year, I could pull data up to May 1st of last year, regardless of today’s date.

To connect to historical rate data, add a new report. Select Custom REST API Connection as the data source.

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: