With the help of the Google Finance function, we can easily convert the currency in Google Spreadsheet. The finance feature of Google Sheets lets users convert currency rates automatically without any manual calculations since Google Sheets fetches updated currency rates automatically as and when we enter the formulas.
Also Read: Java Program to Convert Kilogram to Pound and Pound to Kilogram
In this article, let us discuss everything about how to convert currency on a specific date with the help of Google Sheets tips provided on this page. Read further to find more.
Google Sheets Currency Conversion Syntax
The syntax to convert currency in Google Sheets using the Google Finance function are explained below:
=GOOGLEFINANCE(“CURRENCY:<source_currency_symbol><target_currency_symbol>”)
Here,
- source_currency_symbol: The three-letter code for the currency you want to convert from is the source currency symbol.
- target_currecny_symbol: The three-letter code for the currency you want to convert to is the target currency symbol.
Google Finance Currency Codes: Country Names and their Currency Codes for Conversions
In order to convert the currency using syntax, we will have to enter the proper country code. The list country names and their currency codes are given below:
| Currency Name |
Currency Code
|
| Canada Dollar | CAD |
| Euro | EUR |
| Hong Kong Dollar | HKD |
| Indian Rupee | INR |
| Iran Rial | IRR |
| Japan Yen | JPY |
| Russia Ruble | RUB |
| Singapore Dollar | SGD |
| United Kingdom Pound | GBP |
| US Dollar | USD |
Now, for example, if you want to convert dollars to rupees then you will need to enter the following formula in Google Sheets without any space.
=GOOGLEFINANCE(“CURRENCY:USDINR”)
- How To Track Stocks in Google Sheets? – Stock Analysis Using Google Finance
- How To Convert Text to Numbers in Google Sheets: String to Text Conversion
- How to Convert Text to Date in Google Sheets? (With Examples)
How to Change Currency in Google Sheets?
As discussed above we can easily change the currency in Google Sheets by using the GOOGLEFINANCE function. The steps to convert the currency is given below:
- Step 1: Select the cell where you want to convert the currency. Here we are trying to convert the currency from USD to INR.
- Step 2: Now enter the formula =GOOGLEFINANCE(“CURRENCY:USDINR”).
- Step 3: Press the “Enter” button. You will see the results as shown below.
Alternatively, for the same dataset, we can follow the steps outlined below for currency conversion.
- Step 1: Move to the cell where you want to get the currency conversion results.
- Step 2: Now simply enter the formula =GOOGLEFINANCE(“CURRENCY:”&A2&B2)
- Step 3: Press the “Enter” button and you will see the results as shown below.
How to Convert USD to INR Using GOOGLEFINANCE?
The methods above simply provide you with the exchange rates between two currencies; they do not convert money from one currency to another. Assume we have the following list of dollar pricing and wish to convert them to INR.
- Step 1: Move to the cell where you want to convert USD rates into INR rates.
- Step 2: Now simply enter the formula =GOOGLEFINANCE(“CURRENCY:USDINR”)*A2.
- Step 3: Press the “Enter” button and you will see the results.
- Step 4: Now drag the formula applied cells to other cells where you want to convert the currency.
To convert the price to INR, we simply multiplied the output of the GOOGLEFINANCE function by the cell value in column A.
Google Finance Currency Historical Data
You can modify the GOOGLEFINANCE function to obtain exchange rates for a longer period of time rather than just one day.
The GOOGLEFINANCE function can be modified to retrieve historical exchange rates using the following syntax:
GOOGLEFINANCE(“CURRENCY:<source_currency_symbol><target_currency_symbol>”, [attribute], [start_date], [number_of_days|end_date], [interval])
The steps to fetch historical data currency conversion are given below:
- Step 1: Choose the cell where you want to fetch the historical currency exchange rate.
- Step 2: Now type the formula: =GOOGLEFINANCE(“CURRENCY:USDINR”, “price”, DATE(2021,10,10), DATE(2021,10,20), “DAILY”)
- Step 3: Press the “Enter” button and you will start seeing the results as shown below.
How to use GOOGLEFINANCE to Fetch Currency Exchange Rates Over the Past Week?
Instead of using the DATE method, you can use the TODAY function to dynamically display exchange rates for the past, say one week, based on the day the sheet is opened.
Consider the following scenario: we want to dynamically display exchange rates for the preceding 10 days, regardless of the day the sheet is opened on. The steps to get this done are given below:
- Step 1: Open the spreadsheet where you want to fetch the currency conversion rates.
- Step 2: Now enter the formula =GOOGLEFINANCE(“CURRENCY:USDEUR”, “price”, TODAY()-10, TODAY(), “DAILY”)
- Step 3: Press the “Enter” button and you will see results as shown below.
