In this post I’ll show you how to make Currency Exchange Rate report in Google Data Studio showing five years worth of data.

Before I begin I owe a big thank you to Lee Hurst, who helped me figure out how to fix some issues in Google Sheets.

Below is the report and I’ll show you how to create it.

There are three steps to creating this report.

1. Create the first Currency sheet
2. Create a second Currency sheet
3. Create the Data Studio report

1. Creating the first Currency Google Sheet

The first step is to determine what currencies we want to change.

In my spreadsheet, I want to see how the South African Rand (ZAR) has been trading against the following currencies

• US dollar (USD)
• Canadian dollar (CAD)
• British Pound sterling (GBP)
• The Euro (EUR)
• Australian dollar (AUD)

If I wanted to get the current exchange rate for each currency to the ZAR, I’d use this formula:

=1*GoogleFinance(“CURRENCY:USDZAR”)

That formula converts 1 USD to however many ZAR is the current exchange rate.

However, I want to see historical data for each of these currencies. So I need to use this formula;

=index(GoogleFinance( “Currency:USDZAR” , “close” , $A2),2,2)

In the formula above, we want to find out what the US dollar to South African rand exchange rate was on 1 January 2014. So we make reference to the cell A2 in our formula.

You can see the formula at work in the image below.

We can use this formula for each of the currencies we want to convert, so that we have a sheet that looks like the one below. It has an amount in ZAR that was the exchange rate for each of the currencies on the date listed in the left hand column.

However, for some reason this data does not display correctly when connected directly to Google Data Studio.

It displays the values as null.

To get around this, we must create a second currency spreadsheet.

2. Creating the second Currency Google Sheet

For this next step, simply create a separate Google sheet. Then, in cell A1 enter the following formula;

=IMPORTRANGE(“<<<URL of original currency spreadsheet>>>“,”Currency Historic!A1:F2500”)

Once you’ve added that into cell A1 then the sheet should import the data from your original currency spreadsheet.

Once you’ve created your second currency Google sheet, we can move onto creating the Data Studio report.

3. Creating the Google Data Studio report

The next step is to add our second Google Sheet, which uses IMPORTRANGE to display the data, as a data source in Data Studio.

I’ve added the data in like this, with each of the different currency fields as a ZAR Currency type.

The first chart is a five year time series chart showing the change in the exchange rate over time. I initially tried to use ten years worth of data, but it caused issues for the report and many of the formulas didn’t pull through correctly.

I then have a a thirty day time series chart and scorecard for each of the currencies. It compares the exchange rate to the rate 30 days prior.

It’s important to make sure to group the two different sets of charts, as they have different date ranges. Grouping them allows you to have two date ranges on one chart.

I hope you liked this post about creating a Currency Exchange Rate report in Data Studio.

I’ve previously created a simpler exchange rate dashboard in Data Studio you might want to look at.

How to create a Currency Conversion dashboard in Google Data Studio

If you liked this article you might like my other blog posts.

Or you can follow me on Twitter!