Google Sheets has many useful functions. One of these is it’s currency conversion formula that allows users to get a live conversion rate between different currencies.
To illustrate how the currency feature works, I will show how to use Google Sheets and Google Data Studio to create a currency dashboard.
The first step is to create a Google Sheet that you will use as a basis for the dashboard.
In this example above, I want to know the Rand Value of the various currencies I’ve chosen. That is to say I want to know how many South African Rand each unit of currency is worth.
The first step is to find out the currency code for each of them.
Once we know the currency codes, we can create a formula to give us the live currency value.
We use the formula =[amount we want to convert]*GoogleFinance(“CURRENCY:[first currency][second currency]“)
So, for converting 1 US dollar to South African Rand we will use the formula below.
Therefore, adding the correct formulas for each of the currencies will give us a table that looks like this.
If we are concerned about the accuracy of the values, we can check against Google’s exchange rate. For example, the screenshot above was taken on 24 January 2019. It shows one British Pound as being worth R18.06 Rand. If we do a search on the same day we get the following data.
This shows that the formula is displaying the correct exchange rate.
Displaying the exchange rates in Google Data Studio
Now that we have our spreadsheet with live currency data, we can create a Google Data Studio report to display the information.
We select the correct sheet and worksheet as our data source.
For the exchange rate value we can select ZAR – South African Rand (R) as the currency.
We can then create a chart in Data Studio and display the data however we wish. Take a look at the version below.