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?
This question is related to
google-sheets
currency
google-finance
google-finance-api
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])
=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.
Source: Stackoverflow.com