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?

What do you mean "How to use it"? Do you mean outside of spreadsheets? FYI the Finance API [is being shutdown on October 20](https://groups.google.com/d/msg/google-finance-apis/q-DbjbzQDGQ/5s8m4hra5S8J) — Tim, Oct 12 '12 at 20:37
well, I just came across the line above, I want to create a service call that returns currency exchange from eur to aud, for example... but even in spreadsheet, that function is not documented anywhere! I think this function is not affected by the spring cleanup they announced — Ayyash, Oct 13 '12 at 09:12

DavidTaubmann · Answer 1 · 2022-12-21T19:28:23.990

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

Pylinux · Answer 2 · 2018-08-08T06:05:32.647

14

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.

edited Aug 08 '18 at 06:05

answered Aug 08 '16 at 13:38

Pylinux

11,278
4
60
67

score 9 · Accepted Answer · edited Oct 21 '15 at 23:04

9

Here you go:

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

This is all the documentation that Google provides.

edited Oct 21 '15 at 23:04

callum

34,206
35
106
163

answered Apr 08 '13 at 22:13

Addie

1,653
5
21
32

yup, they finally added it to documentation – Ayyash Apr 13 '14 at 05:28

score 3 · Answer 4 · edited Dec 14 '17 at 06:37

3

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.

edited Dec 14 '17 at 06:37

Divyang Desai

7,483
13
50
76

answered Jan 29 '14 at 13:31

danmullen

2,556
3
20
28

score 3 · Answer 5 · answered Jan 05 '20 at 20:45

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)

score 2 · Answer 6 · edited Sep 18 '18 at 21:19

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.

edited Sep 18 '18 at 21:19

Asclepius

57,944
17
167
143

answered Apr 26 '17 at 08:31

MurderByNumbers

21
2

kenorb · Answer 7 · 2015-07-18T20:21:50.620

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.

score -2 · Answer 8 · edited Dec 14 '17 at 06:38

-2

You have to use the equal sign in the formula box

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

edited Dec 14 '17 at 06:38

Divyang Desai

7,483
13
50
76

answered Aug 07 '16 at 02:04

user167145

1

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

8 Answers8