[google-sheets] How to use GOOGLEFINANCE(("CURRENCY:EURAUD")) function

This function:

GOOGLEFINANCE(("CURRENCY:EURAUD"))

works well in Google Sheets, I have searched the web up and down for some documentation regarding this function and how it is used, the closest I've got was this

http://www.google.com/finance?q=EURAUD

Does anyone know how to use this one? or where to find documentation regarding this function?

The answer is


Bear in mind that the GoogleFinance() function isn't working 100% in the new version of Google Sheets. For example, converting from USD to GBP using the formula GoogleFinance("CURRENCY:USDGBP") gives 0.603974 in the old version, but only 0.6 in the new one. Looks like there's a rounding error.


Some currency pairs have no historical data for certain days.

Compare =GOOGLEFINANCE("CURRENCY:EURNOK", "close", DATE(2016,1,1), DATE(2016,1,12):

Date                Close
1/1/2016 23:58:00   9.6248922
1/2/2016 23:58:00   9.632922114
1/3/2016 23:58:00   9.579957264
1/4/2016 23:58:00   9.609146435
1/5/2016 23:58:00   9.573877808
1/6/2016 23:58:00   9.639368875
1/7/2016 23:58:00   9.707103569
1/8/2016 23:58:00   9.673324479
1/9/2016 23:58:00   9.702379872
1/10/2016 23:58:00  9.702721875
1/11/2016 23:58:00  9.705679083

and =GOOGLEFINANCE("CURRENCY:EURRUB", "close", DATE(2016,1,1), DATE(2016,1,12):

Date                Close
1/1/2016 23:58:00   79.44402768
1/4/2016 23:58:00   79.14048175
1/5/2016 23:58:00   80.0452446
1/6/2016 23:58:00   80.3761125
1/7/2016 23:58:00   81.70830185
1/8/2016 23:58:00   81.70680013
1/11/2016 23:58:00  82.50853122

So, =INDEX(GOOGLEFINANCE("CURRENCY:EURRUB", "close", DATE(2016,1,1)), 2, 2) gives

79.44402768

But =INDEX(GOOGLEFINANCE("CURRENCY:EURRUB", "close", DATE(2016,1,2)), 2, 2) gives

#N/A

Therefore, when working with currency pairs that have no exchange rates for weekends/holidays, the following formula may be used for getting the exchange rate for the first following working day:

=INDEX(GOOGLEFINANCE("CURRENCY:EURRUB", "close", DATE(2016,1,2), 4), 2, 2)

=INDEX(GoogleFinance("CURRENCY:" & "EUR" & "USD", "price", A2), 2, 2)

where A2 is the cell with a date formatted as date.

Replace "EUR" and "USD" with your currency pair.


You have to use the equal sign in the formula box

=GOOGLEFINANCE("GOOG", "price", DATE(2014,1,1), DATE(2014,12,31), "DAILY")

Exchange rate from Euro to NOK on the first of January 2016:

=INDEX(GOOGLEFINANCE("CURRENCY:EURNOK"; "close"; DATE(2016;1;1)); 2; 2)

The INDEX() function is used because GOOGLEFINANCE() function actually prints out in 4 separate cells (2x2) when you call it with these arguments, with it the result will only be one cell.


The specific instructions for what you are looking for are in here: https://support.google.com/docs/answer/3093281

Remember your Google Spreadsheets Formulas might use semicolon (;) instead of comma (,) depending on Regional Settings.

Once made the replacement on some examples would look like this:

=GoogleFinance("CURRENCY:USDEUR")
=INDEX(GoogleFinance("USDEUR","price",today()-30,TODAY()),2,2)
=SPARKLINE(GoogleFinance("USDEUR","price",today()-30,today()))

The syntax is:

=GOOGLEFINANCE(ticker, [attribute], [start_date], [num_days|end_date], [interval])

Sample usage:

=GOOGLEFINANCE("GOOG", "price", DATE(2014,1,1), DATE(2014,12,31), "DAILY")
=GOOGLEFINANCE("GOOG","price",TODAY()-30,TODAY())
=GOOGLEFINANCE(A2,A3)
=117.80*Index(GOOGLEFINANCE("CURRENCY:EURGBP", "close", DATE(2014,1,1)), 2, 2)

For instance if you'd like to convert the rate on specific date, here is some more advanced example:

=IF($C2 = "GBP", "", Index(GoogleFinance(CONCATENATE("CURRENCY:", C2, "GBP"), "close", DATE(year($A2), month($A2), day($A2)), DATE(year($A2), month($A2), day($A2)+1), "DAILY"), 2))

where $A2 is your date (e.g. 01/01/2015) and C2 is your currency (e.g. EUR).

See more samples at Docs editors Help at Google.


Examples related to google-sheets

Import data into Google Colaboratory Is there a Google Sheets formula to put the name of the sheet into a cell? How to label scatterplot points by name? Adding Buttons To Google Sheets and Set value to Cells on clicking How to automatically import data from uploaded CSV or XLS file into Google Sheets How to negate 'isblank' function How can I copy a conditional formatting from one document to another? Multiple IF statements between number ranges Apply formula to the entire column How to define global variable in Google Apps Script

Examples related to currency

Converting Float to Dollars and Cents Best data type to store money values in MySQL How to use GOOGLEFINANCE(("CURRENCY:EURAUD")) function What data type to use for money in Java? Cast a Double Variable to Decimal Yahoo Finance All Currencies quote API Documentation How can I correctly format currency using jquery? Currency format for display Print Currency Number Format in PHP Why not use Double or Float to represent currency?

Examples related to google-finance

Download all stock symbol list of a market How to make google spreadsheet refresh itself every 1 minute? How to use GOOGLEFINANCE(("CURRENCY:EURAUD")) function Download history stock prices automatically from yahoo finance in python Alternative to google finance api How can I get stock quotes using Google Finance API?

Examples related to google-finance-api

How to use GOOGLEFINANCE(("CURRENCY:EURAUD")) function How do I get currency exchange rates via an API such as Google Finance?