by It often happens that we need to attach a price to a certain currency. At the same time, the item may be sold in various currencies. Google Sheets contains an extremely convenient tool for currency conversion that you won't find in other programs.
I'm speaking about the GOOGLEFINANCE function. It retrieves current or archival financial information from Google Finance. And today we'll examine the function together.
How to use GOOGLEFINANCE to get current currency exchange rates
Even though GOOGLEFINANCE is capable of many things, we're interested in its ability to fetch currency exchange rates. The syntax of the function is as follows:
Note. The arguments of the function CURRENCY:<from currency symbol><to currency symbol> must be text strings.
For example, to get the current USD to EUR exchange rate, you can use the formula below:
=GOOGLEFINANCE("CURRENCY:USDEUR")
The same can be applied to convert $ to £:
=GOOGLEFINANCE("CURRENCY:USDGBP")
And US dollar to Japanese yen:
=GOOGLEFINANCE("CURRENCY:USDJPY")
To convert currencies even easier, just replace the text in the formulas with cell references:
Here B3 contains the formula that combines two currency names in A1 and A3:
=GOOGLEFINANCE("CURRENCY:"&$A$1&A3)
Tip. You will find a full list of all currency codes including few cryptocurrencies below.
GOOGLEFINANCE to get currency exchange rates over any period of time
We can use the GOOGLEFINANCE function to see how the currency exchange rates have changed over a specified period of time or for the last N days.
Exchange rates over a specified period of time
To pull exchange rates over some period of time, you need to extend your GOOGLEFINANCE function with additional optional arguments:
- attribute (optional) - use "price" to get real time currency exchange rates (with a short delay up to 20 minutes)
Tip. See a full list of available attributes here.
- start_date, end_date (optional) - your period of interest. Use the DATE function in these arguments.
- interval (optional) - how often you wish to pull the data. It can be "DAILY" or "WEEKLY", or in numbers - 1 for daily, 7 for weekly.
Here's an example of such a formula:
=GOOGLEFINANCE("CURRENCY:USDEUR", "price", DATE(2017,9,1), DATE(2017,9,10), "DAILY")
As a result, we have a table with the rates finalized at the end of the day.
You can use cell references instead of dates to simplify the formula and adjust it in a couple of clicks:
=GOOGLEFINANCE("CURRENCY:USDEUR", "price", A1, A1+5, "DAILY")
A1 is a start date, and we add the needed number of days to it:
Exchange rates for the last N days
We can also use the GOOGLEFINANCE function to pull the exchange rates for the last N days (10 days in the formula below):
=GOOGLEFINANCE("CURRENCY:USDEUR","price",TODAY()-10,TODAY())
Get exchange rates easier using cell references
One more example of GOOGLEFINANCE in Google Sheets illustrates how you can use cell references in all arguments of the function.
Let's find out the EUR to USD exchange rates over a 7-day period:
=GOOGLEFINANCE(CONCATENATE("CURRENCY:", C2, B2), "price", DATE(year($A2), month($A2), day($A2)), DATE(year($A2), month($A2), day($A2)+7), "DAILY")
The source data - currency codes and start date - are in A2:C2.
To combine a few variables into one, we use the CONCATENATE function instead of a traditional ampersand (&).
The DATE function returns year, month, and day from A2. Then we add 7 days to our start date.
We can always add months as well:
=GOOGLEFINANCE(CONCATENATE("CURRENCY:", C2, B2), "price", DATE(year($A2), month($A2), day($A2)), DATE(year($A2), month($A2)+1, day($A2)+7), "DAILY")
All currency codes for the GOOGLEFINCANCE function
Currency codes consist of ALPHA-2 Code (2-letter country code) and of the first letter of the currency name. For example, the currency code for Canadian dollar is CAD:
CAD = CA (Canada) + D (Dollar)
To use the GOOGLEFINANCE function properly, you need to know currency codes. Below you will get a full list of currencies of the world along with few cryptocurrencies supported by GOOGLEFINANCE.
I hope that this article will help you get the up-to-date information about currency exchange rates and you won't be caught unawares when it comes to working with finances.
Spreadsheet with currency codes
Currency exchange rates for GOOGLEFINANCE (make yourself a copy if you need to edit)
57 comments
Houston, we've got a couple of issues!
My Google Sheets don't seem to support this.
Furthermore, it is regrettable that the parameters contain names of fixed currencies. In what sense?
In one cell, I have the base currency, and in another, I have the currency I need to convert. Thus, I require something akin to googlefinance Indian Crypto Coin).
Hello Alisha,
Sorry, it's hard to tell what's going on on your side. For me to be able to help you, please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) with your formula and the example of the expected result. I'll look into it and try to help.
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Do not email there. Once you share the file, just confirm by replying to this comment.
Nice Article and Helpful
Hi,
Date time and stock will be entered into column A1 and B1. I want to get the price of the A1,B1 in C1, and have that number no longer update. In D I want the price to be tracked, like Google Finance normally functions in the same row.
That's the goal. Any help would be very much appreciated :)
Hi Fahad,
Please consider sharing an editable copy of your spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get. I kindly ask you to shorten the tables to 10-20 rows.
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.
I'll look into it and try to help.
Hi There,
I am trying to convert my Excel file into a Google Sheet.
In Excel, I can easily obtain the high52 and low52 for an exchange rate USDEUR.
However, I can't get it right in Google Sheet.
Can anyone explain why this works - =GOOGLEFINANCE(A2,"high52") with A2=GOOG but the same with A2=USDEUR doesn't?
Hello!
I have to convert lots of rows of USD to CDN for the specific dates on my spreadsheet.
So...
Date - B2,B3,B4...etc
USD - I2, I3,I4... (known)
CAD - J2,J3,J4... (unknown)
What is the formula to convert USD in column I to CAD in column J with the exchange rate for that date in column B?
Thanks in advance
Hello Adam,
You may try solutions offered some time ago to Brian, please see these comments below.
Hi guys,
I'm trying to write a prediction formula for the USD/TRY. I'm trying to do a budget for FY22. I know noone can not predict it but maybe someone used a formula which makes the prediction more stable.
Did you ever try such kind of a formula for any kind of currency vs ?
Hi Emrah,
No, sorry, we've never tried that. You may try to find a solution here – an overview of Google Apps Script with a lot of helpful content and links: https://developers.google.com/apps-script/overview
Or in Google Sheets Community: https://support.google.com/docs/threads?hl=en&thread_filter=(category:docs_sheets)
how do i get the changepct for currency?
Hello Stephon,
"changepct" doesn't work for currencies at the moment I'm afraid. You can try solutions similar to this one: https://www.reddit.com/r/googlesheets/comments/nbausy/change_percent_for_bitcoin_price/
Hi,
I am unable to get the BTC Close price Only on a certain date, would someone be able to help with the formula for that please.
Regards
Hi Shashankraj,
Please specify the formula that doesn't work for you.
You haven't included cryptocurrencies such as Bitcoin (BTC) in your full list
Hello Olly,
That's a fair point :) GoogleFinance supports only few cryptocurrencies but, nevertheless, I've just added them to the list. Thank you!