Using GOOGLEFINANCE to Convert Currency in Google Sheets | 2 min easy guide

Using GOOGLEFINANCE to Convert Currency in Google Sheets | 2 min easy guide
Reading Time: 3 minutes
Convert currency in Google Sheets
Convert Currency in Google Sheets

In this article we would see how to convert currency in Google Sheets on a real-time basis using Google Finance. 

What is Google Finance

The GOOGLEFINANCE function in google sheets helps you fetch real-time financial data along with historical securities data from Google Finance. Google Finance is a web service by Google, focused on business news and financial information.

It is a very powerful tool that can be used in many scenarios. In this article, we would look at how to use Google Finance to Convert Currency in Google Sheets.

Need to Convert Currency in Google Sheets

If your business is in a country and your clients are from another country then it becomes very necessary to have updated data from currency exchanges in your sheet all the time. Tallying and updating the exchange rates is a manual work. Therefore to save effort and time you can use Google Finance.

How to Convert Currency in Google Sheets

The syntax for the given function is 

=GOOGLEFINANCE(“CURRENCY:<source><target>”)

  • source: The currency code for the source currency
  • target: The currency code for the target currency

You can use the function as given to get the real-time values for currency conversion. The syntax is very straightforward. It returns you the real-time data with delay up to 20 minutes, so it’s fairly accurate. You can also reference the cells as done in the diagram given below.

These conversion rates can then be used in formulas to suit your needs.

Real-time currency conversion in Google Sheets
Figure 1: Convert currency in Google Sheets in real-time

As you can see you require currency codes to perform the conversions. You can find the ISO 4217 currency codes here.

Using Google Finance to fetch Historical Exchange Rates

The syntax to fetch Historical Exchange Rates is as follows

=GOOGLEFINANCE(“CURRENCY:<source><target>”, [attribute], [start_date], [number_of_days|end_date], [interval])

  • source: The currency code for the source currency
  • target: The currency code for the target currency
  • attribute: The type of value you want to retrieve. The default value for this is “price”. This means it will return real-time values of the data. You can check out more attributes for the function here.
  • start_date: The date we want historical data to start
  • number_of_days|end_date: You can either specify a date which would be the end_date or specify the number of days of the data you want.
  • interval: The frequency of the data returned. It can have two values “DAILY” and “WEEKLY”.

Let’s say we want to get the daily conversion rates from USD to INR from 1 Jan 2022 to 10 Jan 2022. The formula for the same would be 

=GOOGLEFINANCE(“CURRENCY:USDINR”, “price”, DATE(2020,1,1), DATE(2020,1,10), “DAILY”)

You can read more about the DATE function here. Below is the demonstration of the given statement.

Historical currency conversion rates using Google Finance
Figure 2: Historical Currency Conversion Rates using Google Finance

Using Google Finance to fetch data over the past 10 days

Sometimes it is necessary to see the list of recent data. You can use the TODAY() function to fetch the conversion days for the past few days. TODAY()-10 signifies a date 10 days ago.

The formula used for the same would be like

=GOOGLEFINANCE(“CURRENCY:USDINR”, “price”, TODAY()-10, TODAY(), “DAILY”)

Historical conversion rates for the past 10 days
Figure 3: Historical Conversion rates for the past 10 days

Conclusion and Points to Remember

We saw how to convert currency in Google Sheets using Google Finance. But still, there are some points that you should keep in mind.

  • The “real-time data” can be delayed by 20 minutes.
  • The historical data returns an array instead of a single value.
  • The default for attribute is “price”, which returns the real-time data

See Also

Delete Empty rows in Google Sheets: In this article we would see how to delete empty rows in Google Sheets using two different ways.

How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.