How to make a Currency Exchange Rate World Map in Google Data Studio

How to make a Currency Exchange Rate World Map in Google Data Studio

So in this blog post I’ll show you how to make a currency exchange rate world map in Google Data Studio using an interactive Google Map..

Below is the dashboard. It shows the exchange of the South African rand (ZAR) against all other currencies worldwide.

Please note to view the map you’ll need to open this report in Google Data Studio itself.

Open report in Google Data Studio

Click on the Google Data Studio logo in the bottom right corner of the screen to do this.

Assuming you aren’t able to open the link to view the live dashboard this is what the world map looks like. If you hover your mouse over each bubble on the map you’ll see the name of the country, the name of the currency and the value of each unit in ZAR.

Currency Exchange Rate World Map in Google Data Studio

I’ll go over the steps needed to create this type of report.

Step 1: Get a list of Currencies and their Codes

So I got my list of currencies from this page but I’m sure similar data is also available on Wikipedia.

It has the name of the country, the name of the currency and the symbol AKA Currency Code AKA ISO 4217.

Google Sheet It has the name of the country, the name of the currency and the symbol AKA Currency Code AKA ISO 4217.

Step 2: Use a GOOGLEFINANCE formula to determine their value in ZAR

The next thing you do is have a formula converting each of these currencies to your currency of choice. I’m interested in looking at the ZAR. So I added in this formula.

This example converts the Afghan afghani (AFN) to the South African rand ZAR).

=1*GOOGLEFINANCE(“CURRENCY:AFNZAR”)

In order to drag this formula down the columns I had to mess around in Excel using some formulas like CONCATENATE. I can’t remember how I did it so I’ll leave you to figure that step out.

Once it’s done you have a new column “ZAR Exchange Rate” that shows the value of that currency in rands (or whatever currency you want to use). So for example one afghani is worth 0.25 rand and one euro is worth 20.25 rands.

a list of currencies and their values in ZAR

Step 3: Add this sheet to Google Data Studio as a data source

So the next step is to add this sheet as a data source to Google Data Studio. Use the Google Sheets data connector.

Adding Google Sheets as a data source

So we add our data to the Data Studio report. But there are still two custom fields I want to create to make this dashboard work.

Step 4: Create three custom fields in your data source

The first of the three custom fields is Currency. This simply combines the symbol AKA currency code and the Name of Currency.

CONCAT(Symbol,” – “,Name of Currency)

So it gives us a string saying something like “CAD – Canadian dollar”.

CONCAT(Symbol," - ",Name of Currency)

The second custom field is a CASE statement. So this will simply breakdown each of the currencies by their relative value to the rand.

CASE statement CASE 
WHEN ZAR Exchange Rate >=50 THEN ">=50"

Here is the CASE statement that you can copy and paste into Data Studio.

CASE 
WHEN ZAR Exchange Rate >=50 THEN ">=50"
WHEN ZAR Exchange Rate >=40 THEN ">=40"
WHEN ZAR Exchange Rate >=30 THEN ">=30"
WHEN ZAR Exchange Rate >=20 THEN ">=20"
WHEN ZAR Exchange Rate >=10 THEN ">=10"
WHEN ZAR Exchange Rate >=1 THEN ">=1"
WHEN ZAR Exchange Rate <1 THEN "<=1"
ELSE "Null"
END

Now that we have these two custom fields built let’s crack open Data Studio and make a map.

The third custom field is another CASE statement divided up the world into regions.

another CASE statement divided up the world into regions.

Here is the full CASE statement. You might need to adjust some of the country names depending on your data.

CASE 
WHEN Country  IN ("Angola","Benin","Botswana","Burkina Faso","Burundi","Cameroon","Cabo Verde","Central African Republic","Chad","Comoros","Congo (Brazzaville)","Congo (Kinshasa)","Cote d'Ivoire","Djibouti","Equatorial Guinea","Eritrea","Ethiopia","Gabon","Gambia","Ghana","Guinea","Guinea-Bissau","Kenya","Lesotho","Liberia","Madagascar","Malawi","Mali","Mauritania","Mauritius","Mozambique","Namibia","Niger","Nigeria","Rwanda","Sao Tome and Principe","Senegal","Seychelles","Sierra Leone","Somalia","South Africa","South Sudan","Sudan","Eswatini","Tanzania","Togo","Uganda","Zambia","Zimbabwe") THEN "Sub-Saharan Africa"
WHEN Country  IN ("Algeria","Egypt","Libya","Morocco","Tunisia","Western Sahara") THEN "North Africa"
WHEN Country  IN ("China","Japan","Mongolia","Korea, North","Korea, South","Taiwan*") THEN "East Asia"
WHEN Country  IN ("Brunei","Cambodia","Timor-Leste","Indonesia","Laos","Malaysia","Burma","Philippines","Singapore","Thailand","Vietnam") THEN "South East Asia"
WHEN Country  IN ("Afghanistan","Bangladesh","Bhutan","India","Maldives","Nepal","Pakistan","Sri Lanka") THEN "South Asia"
WHEN Country  IN ("Kazakhstan","Kyrgyzstan","Tajikistan","Turkmenistan","Uzbekistan") THEN "Central Asia"
WHEN Country  IN ("West Bank and Gaza","Armenia","Azerbaijan","Bahrain","Cyprus","Georgia","Iran","Iraq","Israel","Jordan","Kuwait","Lebanon","Oman","Qatar","Saudi Arabia","Syria","Turkey","United Arab Emirates","Yemen") THEN "Western Asia"
WHEN Country  IN ("Antigua and Barbuda","Anguilla","Aruba","Bahamas","Barbados","Belize","Bermuda","Bonaire","British Virgin Islands","Cayman Islands","Clipperton Island","Costa Rica","Cuba","Curacao","Dominica","Dominican Republic","El Salvador","Greenland","Grenada","Guadeloupe","Guatemala","Haiti","Honduras","Jamaica","Martinique","Montserrat","Navassa Island","Nicaragua","Panama","Puerto Rico","Saba","Saint Barthelemy","Saint Kitts and Nevis","Saint Lucia","Saint Martin","Saint Pierre and Miquelon","Saint Vincent and the Grenadines",	"Sint Eustatius","Sint Maarten","Trinidad and Tobago","Turks and Caicos","US Virgin Islands") THEN "Central America and Caribbean"
WHEN Country  IN ("Andorra","Austria","Belgium","Czechia","Denmark","Finland","France","Germany","Iceland","Ireland","Italy","Liechtenstein","Luxembourg","Malta","Monaco","Netherlands","Norway","Portugal","San Marino","Spain","Sweden","Switzerland","United Kingdom","Vatican City") THEN "Western Europe"
WHEN Country  IN ("Albania","Belarus","Bosnia and Herzegovina","Bulgaria","Croatia","Estonia","Greece","Hungary","Kosovo","Latvia","Lithuania","North Macedonia","Moldova","Montenegro","Poland","Romania","Russia","Serbia","Slovakia","Slovenia","Ukraine") THEN "Eastern Europe"
WHEN Country  IN ("Canada","Mexico","United States") THEN "North America"
WHEN Country  IN ("Australia","Federated States of Micronesia","Fiji","Kiribati","Marshall Islands","Nauru","New Zealand","Palau","Papua New Guinea","Samoa","Solomon Islands","Tonga","Tuvalu","Vanuatu") THEN "Oceania"
WHEN Country  IN ("Argentina","Bolivia","Brazil","Chile","Colombia","Ecuador","Falkland Islands","French Guiana","Guyana","Paraguay","Peru","South Georgia and the South Sandwich Islands","Suriname","Uruguay","Venezuela") THEN "South America"
ELSE "Other"
END

Once you’ve added these three custom fields we can move on.

Step 5: Create a Google Map showing the value of different currencies relative to the ZAR

So in this Google Map I’ve setup the dimensions and metrics in the following way;

Dimensions:
Bubble location = Country
Tooltip = Country
Bubble colour = Currency

Metrics:
Bubble size = ZAR Exchange Rate (SUM)

How to setup the currency exchange rate google map in google data studio

Step 6: Create a Table showing the list of Currencies and their value to the South African rand

So the other major component of the dashboard is a table showing the value of each currency and it’s relative value to the rand.

I’ve setup the table in the following way:

Dimensions:
Currency

Metrics:
ZAR Exchange Rate (AVG average)

a table in google data studio to help complement our Currency Exchange Rate World Map

It’s important to make sure the ZAR Exchange Rate (renamed in the table to ‘1 = ZAR’) is set to be an average and not a sum. Because several different countries use the same currency (US dollar and Euro for example) it can give an inaccurate value.

Because several different countries use the same currency (US dollar and Euro for example) it can give an inaccurate value.

Step 7: Adding in Filters to filter your map

The next step is to add two filters to your report. The value one let’s you only focus on values of a certain amount.

For example if we include only values above 50 and above 40 we see that only three countries (Bahrain, Kuwait and Oman) have such high value currencies.

using a filter on our Currency Exchange Rate World Map

Or we can use the Region filter to focus in on a specific region of the world. Below the map focuses on Western Europe where most countries use the euro.

the map focuses on Western Europe where most countries use the euro. Currency Exchange Rate World Map

I hope you found this article about to make Currency a Exchange Rate World Map useful and interesting!

You might enjoy my previous articles about Currency Conversion rate dashboard;

How to create a five year Currency Exchange Rate report in Google Data Studio

How to create a Currency Conversion dashboard in Google Data Studio

You might also like my two previous articles about Google Maps in Google Data Studio.

How to use Google Maps in Google Data Studio

How to build a custom Google Map in Data Studio using Google Sheets and Geocode

Download Google Data Studio Templates

If you’d like to download one of my Google Data Studio reports as a template to use for your own data, you can visit Data Studio Templates and purchase one.

My Other Blog Posts

You might also like some of these previous blog posts.