Articles in this section

See more

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.

  1. 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.
  2. 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.
  3. 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.

using-the-currency-datasets-75.gif

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.

rest-api-currency-conversion.png

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

rates-example.png

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:

rates-example-after-sql.png

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.

Once you’ve done that, you’ll enter in the URL and Header sections:

  • URL: http://data.fixer.io/api/timeseries
  • Key: date
  • Value: YYYY-MM-DD

api-headers-currency.png

After you’ve entered the headers, you’ll enter the parameters. To add multiple parameters, click "Add Parameter."

add-parameter.png

  • 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.

api-parameters-currency.png

Once you have done all of this, click "Get Data" to bring in the data. This will return the exchange rate between those currencies, with each column representing one day within that date range.

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');

Related articles