Unlike many other Google Sheets functions, the GOOGLEFINANCE function is too complex to explain fully in a single post. Here, you can learn how to use GOOGLEFINANCE to convert currency in Google Sheets.

GOOGLEFINANCE is highly beneficial in Google Sheets for those involved in stock markets, as it can import current and historical security information. Additionally, it allows users to fetch current and historical currency exchange rates from Google Finance.

Syntax of GOOGLEFINANCE Function:

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval])

In this tutorial, I will focus specifically on the currency conversion capabilities of the GOOGLEFINANCE function. Let’s explore how to convert currency in Google Sheets.

Retrieve Latest Currency Exchange Rates in Google Sheets

Suppose your business operates across countries such as the US, UK, Canada, Japan, France, China, India, etc. In that case, you may need to stay updated with the current currency exchange rates.

You may need to understand how much of one currency (e.g., US Dollar) is required to buy or sell one unit of another currency (e.g., Indian Rupee).

This knowledge helps determine the cost of your country’s currency concerning the currency of the country where your business operates, allowing you to manage payments effectively and track overseas cash flow in your local currency.

Using the GOOGLEFINANCE function, you can easily access both current and historical currency exchange rates in Google Sheets.

What are Historical Currency Exchange Rates?

The GOOGLEFINANCE function allows importing currency exchange rates for specific periods or past days. This data is referred to as historical currency exchange rates.

Can I get Real-time Currency Exchange Rates in Google Sheets?

No, the function retrieves data at its set interval, which is not documented, and displays the information at that time. You may manually refresh your sheet to see any updates in the rate.

Currency Conversion in Google Sheets

The following formula currently (as of 2024-06-23) returns the exchange rate of USD to INR as 83.56295, meaning 1 USD = 83.56 INR:

=GOOGLEFINANCE("CURRENCY:USDINR")

Alternatively, you can enter USD in cell A1 and INR in cell B1 and use the formula =GOOGLEFINANCE("CURRENCY:" & A1 & B1).

In the formula:

• “USD” represents the currency code for the US Dollar.
• “INR” represents the currency code for the Indian Rupee.

If you use this formula in your sheet, your results may differ because the exchange rate updates automatically in Google Sheets.

However, these updates occur on the server side, not within your spreadsheet.

Currency Codes (ISO 4217) for Converting Currencies in Google Sheets

Currency codes, specifically ISO 4217 codes, provide a standardized way to represent different currencies globally. These codes are used within GOOGLEFINANCE for currency conversion.

It can be challenging to recall all currency codes, as there are over 180 codes representing various currencies worldwide. For reference, you can visit https://en.wikipedia.org/wiki/ISO_4217.

Historical Currency Exchange Rates

To obtain the currency exchange rate for a specific date, use the following formula:

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

This formula returns the exchange rate of USD to EUR on September 1, 2017.

For currency exchange rates over a specific period (historical data), use the formula with a start and end date:

=GOOGLEFINANCE("CURRENCY:USDINR", "price", DATE(2017, 12, 1), DATE(2017, 12, 31), "DAILY")

This formula returns the currency exchange rates of USD to INR for December 2017.

Removing Headers in GOOGLEFINANCE Historical Currency Exchange Data

In the examples above, you can observe that the results are present in two columns: one for the date and another for the exchange rate. Both columns have header labels.

To remove these headers, you can use the QUERY function with the GOOGLEFINANCE function as shown below:

=QUERY(GOOGLEFINANCE("CURRENCY:USDEUR", "price", DATE(2017, 12, 20)), "SELECT Col1, Col2 LABEL Col1 '', Col2 '' ", 1)

This query will display the data without the header labels for date and exchange rate.

Resources

• Finding Max and Min Values in GoogleFinance Historical Data in Sheets