How to calculate currency exchange rates in Google Sheets with GoogleFinance
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:
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)
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)
- 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")
Full list of currency codes for the GOOGLEFINANCE 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. Here's a full list of currencies of the world.
I hope that this article will help you get up-to-date information about currency exchange rates and you won't be caught unawares when it comes to working with finances.
20 Responses to "How to calculate currency exchange rates in Google Sheets with GoogleFinance"
Hi,
Thank you for sharing this useful formula.
I have some trouble to use it, and I would like to know if there is a solution to my problem.
On my google sheet, I have a column for our delivery dates. It's not daily nor monthly, it depends on our customers.
I would like to have the currency exchange rate on a 2nd column, using dates from the first one, and extend the function so when we put a new date, the currency exchange rate appear automatically.
For the moment I have two problems:
- When I use the formula to refer to a specific date (using the cell instead of the date), the result takes 4 cells because it creates the table date/close that we can see on your examples. So I can't extend the formula to have a column of rates corresponding to the column of dates.
- If I use the formula by indicating the column of dates, it creates a table date/close with daily results from the oldest date to the newest (dates are not in order in our column, depending on when we bill the customer), it means that if I have two order separated by 10 days, I will have a date/close table with 10 lines.
I spent two hours trying to "play" with the formula, but perhaps you can avoid me a headache!
Best regards,
Eric
How to do to have in a cell in google sheet with today USD currency ?
Thank you for your help
I am looking to use a currency code that is not listed in the GOOGLEFINANCE exchange list. The currency is kwacha to USD. Is there a way to insert the exchange rate in google sheets?
Hi, Joe,
Zambian kwacha code is ZMW.
Just put it into your formula along with USD and you'll get the conversion rate.
Hi,
This is an incredibly useful article, especially for those of us new to Google Finance. Thank you.
I am trying to match the dates on historical USD/GBP rates with those of historical stock prices. Unfortunately there is a currency entry 7 days a week, whilst stock prices are only given when the market is open, so typically 5 entries per week. The dates in the two columns get quickly out of sync. Is there a way around this please?
Many thanks
David
I want to try this soon. But, I don't think if I can do it! Thanks for sharing this idea! I will follow these instructions, also will share it with my friends!
Houston, we have a problem or two!
This doesn't seem to work in my Google Sheets.
In addition: it is unfortunate to include fixed currency names as parameters. What I mean?
I have the base currency in a specific cell and the currency to convert to in other cell. So I need googlefinance("CURRENCY:$cellx$celly") or something like this.
Sorry to hear the formula doesn't work. Please make sure you enter all parts correctly, i.e. ("CURRENCY:"&$A$2&A6, ...)
You can check this function help page for lots of examples.
Amazing. This sorted my problem out in ten seconds. Perfect solution, thank you.
This is a great article thank you.
Is it possible to have the exchange rate update only values entered today?
Hence any historical calculations using the exchange rate wouldn't be changed by the new exchange rate?
Thank you for your feedback, William.
The result changes depending on the attributes you select. You can find the full list here.
If you use attributes for realtime data, depending on your choice, the result will be updated every now and then.
If you use attributes for historical data and specify exact dates, the result will be returned ones and won't change in the future.
Awesome Post
Many thanks for the insigth!
I use paypal is it possible to change the formula to take into account their commission structure. Apologies if this is too easy a question. For Example If I were to change $1000 it is £767.995 using your formula but the actual exchange using paypal is £802.
Thank you
Hello Andrew,
I'm afraid it's impossible since the function is designed by Google in a way to fetch data from Google Finance only.
Hi, I copy and paste =GOOGLEFINANCE("CURRENCY:USDEUR","price",TODAY()-10,TODAY()) and answer is #ERROR! . Why? What Did I Do Wrong? 10 in TODAY()-10 didnt light.
Hi Martin,
Go to File > Spreadsheet settings and check your locale. It may not recognise a comma as a separator and require a different one. You will see which one you need in a formula example when you click the formula in the formula bar.
Formula works perfectly but when I convert EUR to PLN it shows 69.36530277!
It's exactly correct but I don't want to have so many number on a single cell. Is there a possibility to round the number to 67 for example?
Hello Beka,
You can decrease the number of decimal places using the corresponding tool on the Google Sheets toolbar. The screenshot here shows where to find it.
Or you can wrap your GoogleFincance in the ROUND function.
Hello, I have a list with three columns, base currency, foreign currency and date, the formula =GOOGLEFINANCE("currency:"&E2&F2,"price",G2) works, but only with one row, I am trying to expand it to use it for all rows, but I get an error since the result is not just a cell but a table with a row of titles (date and close) and a row of values. Is there any workaround for this?
Hello Christina,
I'm afraid such array formulas like GoogleFinance cannot be expanded if the range is filled with something else, for example, other formulas.