How to calculate currency exchange rates in Google Sheets with GoogleFinance

It often happens that we need to attach a price to a certain currency. At the same time, the item may be sold in various currencies. Google Sheets contains an extremely convenient tool for currency conversion that you won't find in other programs.

I'm speaking about the GOOGLEFINANCE function. It retrieves current or archival financial information from Google Finance. And today we'll examine the function together.

How to use GOOGLEFINANCE to get current currency exchange rates

Even though GOOGLEFINANCE is capable of many things, we're interested in its ability to fetch currency exchange rates. The syntax of the function is as follows:

GOOGLEFINANCE("CURRENCY:<from currency symbol><to currency symbol>")

Get current exchange rates with GOOGLEFINANCE.

Note. The arguments of the function CURRENCY:<from currency symbol><to currency symbol> must be text strings.

For example, to get the current USD to EUR exchange rate, you can use the formula below:

=GOOGLEFINANCE("CURRENCY:USDEUR")

The same can be applied to convert $ to £:

=GOOGLEFINANCE("CURRENCY:USDGBP")

And US dollar to Japanese yen:

=GOOGLEFINANCE("CURRENCY:USDJPY")

To convert currencies even easier, just replace the text in the formulas with cell references:
Use cell references to get the exchange rate.

Here B3 contains the formula that combines two currency names in A1 and A3:

=GOOGLEFINANCE("CURRENCY:"&$A$1&A3)

Tip. You will find a full list of all currency codes including few cryptocurrencies below.

GOOGLEFINANCE to get currency exchange rates over any period of time

We can use the GOOGLEFINANCE function to see how the currency exchange rates have changed over a specified period of time or for the last N days.

Exchange rates over a specified period of time

To pull exchange rates over some period of time, you need to extend your GOOGLEFINANCE function with additional optional arguments:

GOOGLEFINANCE("CURRENCY:<from currency symbol><to currency symbol>", [attribute], [start_date], [num_days|end_date], [interval])
  • attribute (optional) - use "price" to get real time currency exchange rates (with a short delay up to 20 minutes)
    Tip. See a full list of available attributes here.
  • start_date, end_date (optional) - your period of interest. Use the DATE function in these arguments.
  • interval (optional) - how often you wish to pull the data. It can be "DAILY" or "WEEKLY", or in numbers - 1 for daily, 7 for weekly.

Here's an example of such a formula:

=GOOGLEFINANCE("CURRENCY:USDEUR", "price", DATE(2017,9,1), DATE(2017,9,10), "DAILY")

As a result, we have a table with the rates finalized at the end of the day.
Live daily currency exchange rates.

You can use cell references instead of dates to simplify the formula and adjust it in a couple of clicks:

=GOOGLEFINANCE("CURRENCY:USDEUR", "price", A1, A1+5, "DAILY")

A1 is a start date, and we add the needed number of days to it:
Cell references instead of dates in GOOGLEFINANCE.

Exchange rates for the last N days

We can also use the GOOGLEFINANCE function to pull the exchange rates for the last N days (10 days in the formula below):

=GOOGLEFINANCE("CURRENCY:USDEUR","price",TODAY()-10,TODAY())
Currency exchange rates for the last 10 days.

Get exchange rates easier using cell references

One more example of GOOGLEFINANCE in Google Sheets illustrates how you can use cell references in all arguments of the function.

Let's find out the EUR to USD exchange rates over a 7-day period:

=GOOGLEFINANCE(CONCATENATE("CURRENCY:", C2, B2), "price", DATE(year($A2), month($A2), day($A2)), DATE(year($A2), month($A2), day($A2)+7), "DAILY")
GOOGLEFINANCE with cell references in each argument.

The source data - currency codes and start date - are in A2:C2.

To combine a few variables into one, we use the CONCATENATE function instead of a traditional ampersand (&).

The DATE function returns year, month, and day from A2. Then we add 7 days to our start date.

We can always add months as well:

=GOOGLEFINANCE(CONCATENATE("CURRENCY:", C2, B2), "price", DATE(year($A2), month($A2), day($A2)), DATE(year($A2), month($A2)+1, day($A2)+7), "DAILY")

All currency codes for the GOOGLEFINCANCE function

Currency codes consist of ALPHA-2 Code (2-letter country code) and of the first letter of the currency name. For example, the currency code for Canadian dollar is CAD:

CAD = CA (Canada) + D (Dollar)

To use the GOOGLEFINANCE function properly, you need to know currency codes. Here's a full list of currencies of the world along with few cryptocurrencies supported by GOOGLEFINANCE.

I hope that this article will help you get the up-to-date information about currency exchange rates and you won't be caught unawares when it comes to working with finances.

You may also be interested in