Tutorial: Getting real-time currency data in Google Sheets
Tags: tutorial, googlesheets, currencydata, spreadsheets
Published: 2022-03-30 13:32:34

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

{% details Method 1: **GOOGLEFINANCE** %} 
- Fast and easy to use
- Great for small data sets
- No real-time data
{% enddetails %}

{% details Method 2: **CURRENCY API** %} 
- Accurate, real-time data
- API key needed
- Script needed (don't worry, I got you)
{% enddetails %}

##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:

![API key with the latest endpoint](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5pzwioe1r2sxki1kiw35.png)

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

![Table for currency conversions in Google Sheets](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/schnhdob4v8x1g7skjwo.png)

**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](https://currencyapi.com/currency-data-in-google-sheets/) 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.