If you’re working with data a lot, it may occur that you need to convert currencies. The rates of currencies can change a lot over time. There is the possibility to Google the conversion rates and enter them manually in Google Sheets. However to keep up with all these currency changes not only costs a lot of time, but it’s also a very error-prone process. The smallest error can cost you a lot of money. Luckily there is a function on Google Sheets that helps you with currencies.
There’s a currency converter available in the Google Finance function. This function is very extensive and is mostly used to retrieve stock information. With the currency converter, you are able to retrieve not only the current rate of currencies but also historical data! Which is awesome. This all can be retrieved automatically, resulting in that you can spend your time on more important tasks.
As the name suggests, the function makes use of financial data provided by Google (Finance). So whatever conversion rates you were planning to Google, they are directly available within your Google Sheets! All you need is the correct formula.
In this tutorial, I will show you how to convert currencies in Google Sheets. Both with current and historical rates.
Syntax
The Syntax of the currency converter is as follows:
=GOOGLEFINANCE("CURRENCY:<source_currency_symbol><target_currency_symbol>")
To use the Google Finance function in Google Sheets, select the cell and type in the formula above. Change the <source_currency_symbol> to the currency code you want to convert from. Change the <target_currency_symbol> to the currency code you want to convert to.
Both symbols are three-character codes. E.g. The dollar’s three-character code is DOL. The euro’s three-character is EUR. Further down this post, we have a list with shortcodes of all (Crypto)currencies. In total, you’ll have 6 consecutive characters. See the example below.
Example 1: Converts dollar to euro
=GOOGLEFINANCE(“CURRENCY:USDEUR”)
! Note that there’s no space between the currency codes.
! Note that the arguments of the function CURRENCY must be strings.
The result of example 1 is (At the time of writing) 0.85, which is the current conversion rate from the US Dollar to Euro.
Example 2: Converts cell A1 (Japanese Yen) to B1 (Bitcoin)
In this example, we’ll convert the currency code in A1, which is Yen to B1, Bitcoin. This is done by extending the “Currency:” string by cells A1 & B1.
=GOOGLEFINANCE(“CURRENCY:”&A1&B1)
! The “&” in the formula connects strings. An example of using the “&” operator would be =”Hello,”&” World!”. This formula would result in the string “Hello, World!” since the strings are concatenated.
Example 3: Converts price of cell A2 from the currency of cell B2 (USD) to the currency of cell C2 (EUR)
In this example, we convert the price of cell A2 from USD to EUR. I’ve filled in the 2 currencies in cells B2 and C2. By multiplying the price by the conversion rate. I get the converted price.
=A2*GOOGLEFINANCE(“CURRENCY:”&B2&C2)
Supported Currencies
Since the data that Google Sheet uses is directly from Google Finance. There are a lot of currencies available. 166, to be exact. Of which 4 are cryptocurrencies. Here’s a list of the most popular currencies and their shortcodes. Click here to see the full list!
| Currency | Shortcode |
| United States 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 |
Supported Cryptocurrencies
| Cryptocurrencies | Code |
| Bitcoin | BTC |
| Ethereum | ETH |
| Litecoin | LTC |
| BitcoinCash | BCH |
! Click here to see the full list of supported currencies and their shortcodes. If you miss any shortcodes, please let us know. We’ll add them!
How to convert prices in Google Sheets
Alright! Now you have seen what the function can do, and you have the shortcodes of the currencies you want to convert. The next step is to convert your prices. As shown in example 3 above.
- The first step would be to open Google Sheets
- On Google Sheets, fill in a column name and set the prices that you want to convert in Column A.
- Give column B also a column name and write down the shortcodes you want to convert from. I set the column name to ‘From’. In my case, I want to convert the prices from US Dollar to Euro, so I fill in ‘USD’, which is the shortcode of the US Dollar.
- Do the same for column C, but now for the currency, you want to convert to. In my case ‘EUR’, which is the shortcode of Euro.
- Now, the only thing we have to do is to use the formula using the previously filled-in cells. Go ahead and select cell D2. Paste the formula below and fill it out downwards. See the animation below.
=A2*GOOGLEFINANCE(“CURRENCY:”&B2&C2)
Historical Currency Converter
To get the historical values of the conversion of currencies, we can use the same GoogleFinance function. It will list the rate at the end of each day, for a specific period. This could be a custom period. So the possiblities are limitless! Do you want to know the rates from this year? No problem.
To do this, click on an empty cell and enter the formula below. You can specify the dates yourself, they need to be in the following format: Year, Month, Date. An example:
=GOOGLEFINANCE(“Currency:USDEUR”, “price”, “01/01/2021”, “01/03/2021”)
It’s also possible to retrieve the rate for a specific day. Like the example below. In this example, we will get the rate of USD to EUR from the 1st of January 2021.
=GOOGLEFINANCE(“Currency:USDEUR”, “price”, “01/01/2021”)
You can replace the dates with all sorts of date formulas. For example, you could retrieve the data of today by replacing the date with TODAY().
Real-Time Currency Converter
The last thing that I want to show you is our currency converter. In this converter, you get the ‘real-time’ conversion rates. If you want to see how it works, make your own copy, so that you can take a look at all logic and formulas.
We have automated the retrieval of rates using the GoogleFinance formula. The formula used in D8 is the following:
=D7*GOOGLEFINANCE(concatenate(“CURRENCY:”,vlookup(F7,Currencies!A:B,2,false),vlookup(F8,Currencies!A:B,2,false)))
It multiplies the amount filled in in D7 by the rate between the 2 currencies that are filled in. The vlookup in the formula looks up the shortcodes in the second tab in the Google Sheet. By using Data validation, we made sure that the currencies filled in by the user always are correct. This way we always have correct input, and more importantly, the system never fails.
Currency in GoogleFinance not working?
There are a couple of common errors with Google Finance.
- There are no 6 consecutive characters after ‘CURRENCY:’
Make sure that the 2 currency shortcodes after your ‘CURRENCY:’ are 6 consecutive characters, so make sure there is no space or character between the currency shortcodes. E.g. “CURRENCY:USDEUR” works. “CURRENCY:USD-EU” doesn’t. Also make sure all characters are letters and the cells strings.
- #REF! error when retrieving historical data
When retrieving historical data, you might encounter the ‘#REF!’ error. This error happens when cells near your function already contain data. This function would have to overwrite the nearby data. Luckily, Google Sheets doesn’t do this. If you want to solve this error, make sure there are enough empty cells around the formula. You can do this by replacing nearby data or adding rows and columns.
If your error is not in this list, please let us know, we’ll try to figure it out and add it to the list!
Sum up
That’s it! This is all you need to know to work with the Currency syntax of the GOOGLEFINANCE formula in Google Sheets. If you still have any questions, don’t hesitate and leave a comment down below. We’ll check all comments daily and try to help you out with your issues. Cheers!