Use GOOGLEFINANCE in Google Sheets to easily convert currency.
Syntax=GOOGLEFINANCE("CURRENCY:<source><target>")source – The currency code for the source currencytarget – The currency code for the target currency
Sample Usage=GOOGLEFINANCE("CURRENCY:USDINR")
//Converts US dollar to Indian rupee=GOOGLEFINANCE("CURRENCY:BTCUSD")
//Converts Bitcoin to US dollar=GOOGLEFINANCE("CURRENCY:BTCUSD", "price", DATE(2022,10,1), DATE(2022,10,10), "DAILY")
//Retrieves the historical daily price of Bitcoin from October 1 through 10.
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.
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.
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")
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.