How to use GOOGLEFINANCE(("CURRENCY:EURAUD")) function

Question

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?

Answer 1

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()))

Those 3 cells would result in something like this (the second line prints the value 30 days ago):

Answer 2

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)

Answer 3

=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.

Answer 4

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.

Answer 5

You have to use the equal sign in the formula box

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

Answer 6

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.

Answer 7

Here you go:

https://support.google.com/docs/answer/3093281

This is all the documentation that Google provides.

Answer 8

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.