Tutorial: Getting real-time currency data in Google Sheets

Introduction

We all have our preferred and, let's call them, "less preferred" tasks at work. And if I have learned one thing throughout my professional life, a lot of people share a non-existing enthusiasm for (drum-roll please) SPREADSHEETS.

Who would have thought this article would go in this direction, except for maybe everyone who read the title.

I am here to tell you one more practical use for the swiss army knife that is spreadsheets.

In this tutorial, we focus on currency data. Whether you need this data for trading or your business, or any other use, I am here to show you how you can easily use Google Sheets to convert currencies in (almost) real-time.

If you are working with Microsoft Excel instead of GSheets, this tutorial won't help you much, but don't worry - I am also working on an Excel article and will soon publish it.

This tutorial focuses on two methods of getting currency data into Google Sheets:

• Using the GOOGLEFINANCE function
• Using a currency API

Let's take a closer look at what that means.

Short Summary

If you don't have time to read the whole article, here is a summary of the methods I will show you and why they might be right (or wrong) for you.

Method 1: GOOGLEFINANCE

• Fast and easy to use
• Great for small data sets
• No real-time data

Method 2: CURRENCY API

• Accurate, real-time data
• API key needed
• Script needed (don't worry, I got you)

Using GOOGLEFINANCE

The fastest way to get your currency data is by using the GOOGLEFINANCE function. The feature allows you to get almost real-time currency data provided by Google into your spreadsheet.

There is a simple formula to fetch the data. It consists of the function "CURRENCY", a base currency (from) and a target currency (to).
It requires the international three-letter code for currencies without a space between them to work correctly. For example, we will write USD for US Dollar, EUR for Euro, etc.
With this in mind, the formula goes as follows:

=GOOGLEFINANCE("CURRENCY:<base currency><target currency>")

or in more practical terms:

=GOOGLEFINANCE("USDEUR")

Let's try this formula out with an example.

We want to convert three currencies. Our base currency is US Dollar (USD). Let's assume we want to convert them into Euro, British Pound Sterling (GBP), and Canadian Dollar (CAD). Our first column will therefore include our currency pair.

• Step 1: Using the formula isn't rocket science. We simply select the cell we want our results in column two, type =GOOGLEFINANCE("USDEUR"), and hit enter. Now, we have the current conversion rate for USD to EUR.

• Step 2: Including References helps us save time, as we don't need to type in the formula manually for each currency pair. To do so, we adjust the formula a little:

=GOOGLEFINANCE("CURRENCY:"&A2&B2)

We now only need to drag the formula over the cells we want to convert.

• Step 3: Working with prices is possible by using simple multiplication. So, the formula changes to:

=GOOGLEFINANCE("CURRENCY:<base currency><target currency>")*cell

Using an API

While method one is straightforward, method two is adjustable and allows to work with big data sets.

Step 1: Getting an API key is necessary for this method to work. I used currencyapi.com in my example. They offer a free version of their API so that you can try the tutorial with their API.
We use the " latest " endpoint since we want current conversions. Your API key should look something like this:

Step 2: Create a table with the data you want to get. I would advise to go with three columns.

• Column 1: Price to convert (in base currency)
• Column 2: Three-letter currency code (i.e. USD, EUR, GBP, etc.)
• Column 3: Conversion rate (will be filled in by script)

Here is what your document should now look like.

Step 3: Open Apps Script by going to Extensions > Apps Script. This will open up a new tab.

Step 4: Enter the Code. If you don't want to invest time in writing your own code, the people at currencyapi.com have prepared a code that you can simply copy+paste or download from their tutorial. Just click here to see their blog post.

Step 5: Click "Save" and "Run" to fetch the data from currencyapi.com.

Et voila! We now have the currency data we need in our Google Sheet.

More to come

I hope these two easy methods help you convert currency data in GSheets. I had to go through quite a few tutorials to find the easiest ways to do so.

If you want to know how getting currency data works in Excel, stay tuned for my next post.