How to Convert Currency in Google Sheets (Google Finance Function)

When working with money-related data in your spreadsheets, there may come a time where you need to convert between currencies.

Of course, it is always possible to Google the conversion rates and then enter them manually.

An easier way, however, is using a very convenient Google Sheets function that fetches the conversion rates for you.

In this tutorial, I will show you how to convert currency in Google Sheets using the GoogleFinance function.

Using GOOGLEFINANCE to Convert Currency in Google Sheets

The GOOGLEFINANCE function in Google Sheets is a really convenient tool that fetches currency conversion rates in real-time (well almost in real-time).

As the name suggests, the function makes use of financial data provided by Google. So whatever conversion rates you were planning to Google, are directly available within your Google Sheets! All you need is the correct formula.

Syntax of the GOOGLEFINANCE FUNCTION

The basic syntax of the GOOGLEFINANCE FUNCTION is as follows:

=GOOGLEFINANCE("CURRENCY:<source_currency_symbol><target_currency_symbol>")

where:

  • source_currency_symbol is a three-letter code for the currency you want to convert from.
  • target_currency_symbol is a three-letter code for the currency you want to convert to.

For example, if you want the conversion rate from dollars to rupees, you will enter the function:

=GOOGLEFINANCE("CURRENCY:USDINR")

Notice that there is no space between the two currency codes.

Here are some other currency codes:

Currency Code
US Dollar USD
Japan Yen JPY
Canada Dollar CAD
Indian Rupee INR
Iran Rial IRR
Russia Ruble RUB
Euro EUR
Singapore Dollar SGD
Hong Kong Dollar HKD
United Kingdom Pound GBP

How to use GOOGLEFINANCE to Fetch Currency Exchange Rates

Let us look at an example. Here we have three currencies in column B and we want to convert the currency in column A to those in column B:

Currency codes for conversion

Here are the steps you need to follow in order to get the exchange rate of dollars to the three currencies in column B:

  1. Select the first cell of the column where you want the results to appear (C2).
  2. Type the formula: =GOOGLEFINANCE(“CURRENCY:USDINR”)
  3. Press the return key.

You should see the current exchange rate for conversion of USD to INR in cell C2.

Google finance function

  1. Alternately, you could even include references to cells in the function, by combining them with the ampersand operator, as follows: =GOOGLEFINANCE(“CURRENCY:”&A2&B2)
  2. Press the return key.
  3. Double click on the fill handle of cell C2 to copy the formula to the rest of the cells of column C.

You should now see conversion rates for USD to all three currencies shown in the sheet.

Google finance usnig references

How to Convert USD to INR Using GOOGLEFINANCE

The above steps only get you the conversion rates between two currencies, but they don’t actually convert money from one currency to another. Let us assume we have the following list of prices in dollars and we want to convert them to INR.

Converting USD to INR in Google Sheets

To convert the money in the table above from USD to INR, follow these steps:

  1. Select the first cell of the column where you want the results to appear (B2).
  2. Type the formula:
    =GOOGLEFINANCE("CURRENCY:USDINR")*A2

    Google finance USD to INR formula

  3. Press the return key.
  4. Double click on the fill handle of cell B2 to copy the formula to the rest of the cells of column C.
  5. You should now see column B populated with prices in INR.apply the fornula to the entire column

Notice we simply multiplied the result of the GOOGLEFINANCE function with the cell value in column A, in order to convert the price to INR.

Entering your parameters along with just the general GOOGLEFINANCE function is enough to give you an accurate conversion rate.

However, there are other optional parameters that the GOOGLEFINANCE function lets you enter in order to get specifically what you need. For example, you can use it to display historical exchange rates too.

How to Use GOOGLE FINANCE to Fetch Historical Exchange Rates

You can make some alterations to the GOOGLEFINANCE function to fetch exchange rates over a period of time, instead of just one day.

To fetch historical exchange rates, the GOOGLEFINANCE function can be customized to the following syntax:

GOOGLEFINANCE("CURRENCY:<source_currency_symbol><target_currency_symbol>", [attribute], [start_date], [number_of_days|end_date], [interval])

In the above syntax, all parameters shown in square brackets are optional. Here’s what they mean:

  • The attribute parameter specifies the type of data you want to be retrieved. This is a string value and its default value is “price”. This means we want real-time price quotes fetched from Google Finance. We have provided a list of attribute values and what they mean at the end of this tutorial.
  • The start_date parameter specifies the date from when we want the historical data to start
  • In the fourth parameter you can either specify the end_date for the historical data or the number of days from start_date for which you want the historical data.
  • The interval parameter specifies the frequency of the returned data. It can be either “DAILY” or “WEEKLY”, depending on your requirement,

How to use GOOGLEFINANCE to Fetch Currency Exchange Rates over a Time Period

Let us take an example to understand how the GOOGLEFINANCE function can be used to fetch exchange rates (USD to INR) from 10th October 2020 to 20th October 2020.

  1. Select a cell from where you want to start displaying the exchange rates. You don’t need to add a header for the columns, since the function adds the column headers automatically.
  2. Type the formula:=GOOGLEFINANCE(“CURRENCY:USDEUR”, “price”, DATE(2020,10,10), DATE(2020,10,20), “DAILY”)
  3. Press the Return key

You should now see two new columns automatically inserted starting from the cell in which you entered the formula.

GOOGLEFINANCE to Fetch Currency Exchange Rates over a Time Period

The first column contains the date for each day between 10th Oct 2020 and 20th Oct 2020. The second column contains the closing exchange rate for each day.

If you want to display weekly rates instead of daily rates, you can simply replace the interval parameter in the function from DAILY to WEEKLY.

How to use GOOGLEFINANCE to Fetch Currency Exchange Rates over the Past Week

If you want to dynamically display exchange rates over the past, say one week depending on the day the sheet is opened, you can use the TODAY function, instead of the DATE.

Let us see an example where we want to dynamically display exchange rates of the previous 10 days, irrespective of which day the sheet is opened.

Follow these steps:

  1. Select a cell from where you want to start displaying the exchange rates.
  2. Type the formula: =GOOGLEFINANCE(“CURRENCY:USDEUR”, “price”, TODAY()-10, TODAY(), “DAILY”)
  3. Press the Return key

You should now see two new columns automatically inserted starting from the cell in which you entered the formula.

GOOGLEFINANCE to Fetch Currency Exchange Rates over the Past Week

The first column contains the date for each day, from 10 days before to the current date. The second column contains the closing exchange rate for each day.

Here, the TODAY() function returns the current date on which the file is opened. So each time it is opened, this function will refresh and return a new value.

Few Points to Remember

Here are a few important things you need to know to understand the GOOGLEFINANCE function:

  • When we say real-time exchange rates, you can expect a delay by up to 20 minutes.
  • For real-time rates, the function returns a single value. However, for historical rates, the function returns an array along with column headers.
  • If you do not provide any date parameters, GOOGLEFINANCE assumes that you only want real-time results. If you provide any date parameter, the request is considered as a request for historical data.

Attribute Values and their Meanings

Here are some of the commonly used values for the attribute parameter of GOOGLEFINANCE:

For real-time data:

  • priceopen” – We want the price at the time of the market open.
  • high” – We want the current day’s high price.
  • low” – We want the current day’s low price.
  • volume” – We want the current day’s trading volume.
  • marketcap” – We want the market capitalization of the stock.

For historical data:

  • open” – We want the opening price for the specified date(s).
  • close” – We want the closing price for the specified date(s).
  • high” – We want the high price for the specified date(s).
  • low” – We want the low price for the specified date(s).
  • volume” – We want the volume for the specified date(s).
  • all” – We want all the above information.

There are a number of other attributes that you can use too. You can refer to the official documentation of Google Sheets to find out more.

In this tutorial, we looked at how you can use the GOOGLEFINANCE function to fetch both real-time and historical exchange rates.

We also looked at how you can use the fetched rates to convert currencies in Google Sheets. We hope this has been informative and helpful for you.

Other Google Sheets tutorials you may like:

Leave a Comment