Back to Blog

Build a Custom Currency Exchange Rate Tracker in Google Sheets Without Coding

Small and medium-sized companies usually use Google Sheets to monitor their income and costs. Quite often, their cash flow includes different currencies, while their tax reporting must be submitted in their national currency. This means that you have to take into account the currency rate when calculating income and costs. Regular users simply Google “currency converter” to solve this task.

However, it would be quite useful to automate this flow and embed the conversion feature into your spreadsheet. Read on to find out how you can do this!

Track currency rates in Google Sheets with the native function – GOOGLEFINANCE

GOOGLEFINANCE is the Google Sheets function to import real-time data on financial markets. The function takes this data from Google Finance, a web service that provides information on local and world market trends including daily stock prices, currency rates, and so on. 

For our currency exchange rate monitor, we can use a simple GOOGLEFINANCE formula syntax without any optional parameters:

=GOOGLEFINANCE("ticker-symbol")
  • ticker-symbol, in our case, is an abbreviation to represent currency pairs, for example, EURUSD, USDCAD, etc. 

Note: EURUSD shows the Euro to USD exchange rate. If you need the USD to Euro exchange rate, use the reverse ticker-symbol – USDEUR.

Currency exchange rate monitor with GOOGLEFINANCE

Now, let’s use the function in practice. We will track the exchange rate of following currencies to USD:

  • EUR (Euro)
  • AUD (Australian dollar)
  • CAD (Canadian dollar)
  • GBP (Pound sterling)
  • IDR (Indonesian rupiah)
  • INR (Indian rupee)
  • PHP (Philippine peso)
  • BRL (Brazilian real)
  • PLN (Polish złoty)
  • JPY (Japanese yen)
  • CNY (Chinese Yuan Renminbi)

To use the power of the GOOGLEFINANCE function, we need to have a ticker-symbol consisting of two currency codes – Our conversion currency and USD. The CONCAT function nested with ARRAYFORMULA will help us handle this:

=arrayformula(if(len(B2:B)=0,,concat(B2:B,"USD")))

Read our blog post to learn more about how to merge data in Google Sheets.

Even better, we can remove the Ticker column and simply insert the CONCAT formula in the GOOGLEFINANCE formula as follows:

=GoogleFinance(concat("USD",B2:B))

It would seem that we could simply combine ARRAYFORMULA and this GOOGLEFINANCE formula to expand the results. However, GOOGLEFINANCE as an argument for ARRAYFORMULA doesn’t work 🙁 So, you’ll have simply to drag the formula down or use the Ctrl+Enter Google Sheets shortcut

It’s not convenient, since every time you add a new row at any point and populate it with a new ticker, you’ll have to manually copy and paste the GOOGLEFINANCE formula. 

Let’s modify our GOOGLEFINANCE formula to get the exchange rate to USD. You need to swap “USD” and B2:B in the CONCAT formula piece, and drag the formula again:

=GoogleFinance(concat(B2:B,"USD"))

That’s it! That’s it! The currency rates will be updated on change, but you can set it to refresh every minute. Go to File => Spreadsheet settings => Calculation and choose “On change and every minute“. Save settings to enable the every-minute refresh.

You can copy this exchange rate tracker template to your spreadsheet and customize it for your needs.  

GOOGLEFINANCE #N/A error

Though GOOGLEFINANCE is a native Google Sheets function, we can’t claim that it is highly reliable. On StackOverflow and Google community, you can find recent comments that GOOGLEFINANCE formulas have stopped working. The #N/A error message usually returns the following:

When evaluating Google Finance, the query for symbol "****" returned no data.

This is typically a GOOGLEFINANCE internal error, so neither adding IFERROR to your formula nor changing the refresh interval will help. What’s the solution?

The GOOGLEFINANCE function is best when you are using a smaller number of ticker symbols (let’s say, less than 25). For a reliable import of bigger sets of stock data, you should use an API service.

How you can import currency rates data from financial services into Google Sheets via APIs

Usually, developers use APIs to integrate some capabilities, such as currency exchange, into their apps. We’re going to explain how you can use APIs to import currency data into Google Sheets without any coding.

JSON Client importer

As a rule, financial services use the JSON format to transfer data via APIs. We will use the JSON Client importer to fetch and convert JSON data into Google Sheets. It is a solution by Coupler.io, a product for importing data into Google Sheets from different sources. Visit the Coupler.io homepage to learn more about it.   

Step 1: Install Coupler.io 

Install Coupler.io from the Google Workspace Marketplace.

Step 2: Set up the JSON Client importer

You’ll need to open your target spreadsheet, go to the Add-ons tab, select Coupler.io => Open dashboard, click on the +Add Importer button and choose JSON Client.

After that, you can set up the importer:

1. Enter the name of your importer in the “Title” field.
2. Set up the Source

2.1. Insert the API endpoint to import data from in the “JSON URL” field. You will find the endpoint in the API documentation of the financial service you use. For example, 

https://xecdapi.xe.com/v1/currencies.json/?iso=USD,EUR,CAD

Click Show advanced to expand the optional fields:

2.2. Select GET in the HTTP Method field.

2.3. Enter the specific HTTP headers for your request in the HTTP headers field. You will find information about the required HTTP headers in the API documentation of the financial service you use. For example, here is how the Authorization header may look:

Authorization: Basic enJlY29yZHM0OTEamFiZjk2MWZtNnA4ZjY=

2.4. Assign values to the specified parameters in the “URL query string” field or leave it blank.

2.5. Leave the “Request body” field blank.

2.6. Enter the names of specific columns you want to import in the “Fields” field. Otherwise, leave it blank.

2.7. Enter a string in the “Path” field to select nested objects from the JSON response or leave it blank.

3. Set up the Destination

3.1. Connect to your Google account.

3.2. Enter the name of the sheet in the “Sheet name” field.

Click Show advanced if you need to change a start cell (A1) where the data will be imported.

4. Configure importer settings

In this section, you can enable the Automatic data refresh, choose the import mode and add a column specifying the date of the last data refresh. For more on this, read the Coupler.io knowledge base.

Let’s check out how it works on a real-life example.

Import currency exchange rate data into Google Sheets via API

We’ve reviewed top 10 currency and forex APIs by Yasu and picked two options to test.

Foreign Exchange Rates API

Exchange rates API is a free service for current and historical foreign exchange rates published by the European Central Bank. No sign up is required, and the endpoint examples are provided right on the go.

To import data using the JSON Client importer, you’ll need to following essential parameters:

Once you fill out the fields, click Save & Import and welcome your data into the spreadsheet. 

That was easy, but not exactly what we needed. The API retrieved many currency rates that are quoted against the Euro. Now, let’s request specific currency rate quotes against the USD. Here are the parameters:

base: USD 
symbols: AUD,BRL,CAD,CNY,EUR,GBP,IDR,INR,JPY,PHP,PLN

Note: Alternatively, you can attach the URL query string parameters to the JSON URL as follows:

https://api.exchangeratesapi.io/latest?base=USD&symbols=AUD,BRL,CAD,CNY,EUR,GBP,IDR,INR,JPY,PHP,PLN

If you enable the Automatic data refresh, Coupler.io will automatically request currency exchange rates on the schedule you specified. 

XE Currency Data API

Now, let’s check out how the JSON Client works with a paid solution. We chose the XE Currency Data API since it provides a free trial and endpoints for converting one currency to another. After signing up, we downloaded the technical documentation explaining the API endpoints. The main difference between XE and Exchange Rates API is that all requests to the XE API must be authenticated via HTTP Basic Access Authentication

This means that you’ll have to enter the Authorization header in the “HTTP headers” field in the following format:

Authorization: Basic {credentials}

{credentials} is the Base64 encoding of account ID and API key joined by a single colon “:“. For example, if your XE account ID is example491919043 and your account API Key is jabf961f4u6p8k9usfmfu6amf6, you’ll get the following:

example491919043:jabf961f4u6p8k9usfmfu6amf6

Encode this string to Base64 using a dedicated tool and you’ll get {credentials}:

ZXhhbXBsZTQ5MTkxOTA0MzpqYWJmOTYxZjR1NnA4azl1c2ZtZnU2YW1mNg==

Now, let’s check out the JSON Client parameters required to import currency rates data from XE:

from: USD
to: AUD, BRL, CAD, CNY, EUR, GBP, IDR, INR, JPY, PHP, PLN
amount: 1

Note: Alternatively, you can attach the URL query string parameters to the JSON URL as follows:

https://xecdapi.xe.com/v1/convert_from.json/?from=USD&to=AUD,BRL,CAD,CNY,EUR,GBP,IDR,INR,JPY,PHP,PLN&amount=1

If you want to include a column with the inverse rate (a quote for which the base and target currencies are switched), add inverse: true to the “URL Query string” field, or attach inverse=true to the API endpoint:

from: USD
to: AUD, BRL, CAD, CNY, EUR, GBP, IDR, INR, JPY, PHP, PLN
amount: 1
inverse: true

Note: Alternatively, you can attach the URL query string parameters to the JSON URL as follows:

https://xecdapi.xe.com/v1/convert_from.json/?from=USD&to=AUD,BRL,CAD,CNY,EUR,GBP,IDR,INR,JPY,PHP,PLN&amount=1&inverse=true

When you have the data imported into your spreadsheet, you can reference it for your calculations using VLOOKUP, QUERY or FILTER. Check out our blog post dedicated to each of these Google Sheets functions. 

Why you should use a custom currency tracker in Google Sheets instead of an exchange rate app 

The best thing about Google Sheets is that you can automate much of your workflow using different functions or add-ons. This means that you spend less of your time on recurring manual work, and have more time available for more valuable tasks. Some exchange rate apps are excellent, but they cannot be embedded into your spreadsheet. So, you’ll have to either manually transfer data from an app to Google Sheets or set up some complex integrations. The options introduced in this blog post are time-efficient and easy to implement. Between GOOGLEFINANCE and Coupler.io, the solution you choose is up to you. Good luck!

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free