22

I'm having trouble stopping the googlefinance function on a specific date to use "that" specific exchange rate to convert currency from GBP to EUR.

Here is my formulae: =Finance!B4*GOOGLEFINANCE("CURRENCY:GBPEUR","price",date(2017,15,11))

Here is the error: When evaluating GOOGLEFINANCE, the query for the symbol: 'CURRENCY:GBPEUR' returned no data.

I've looked at other solutions on SO but none to avail. I've actually added "date" and "price" to my formulae from other solutions. Like so:- Solution 1

player0
  • 124,011
  • 12
  • 67
  • 124
cala
  • 767
  • 4
  • 11
  • 28

6 Answers6

54

First of all, date(2017,15,11) means the 11th day of 15th month of the year, so you'll need to swap the month and day.

Secondly, historical data queries, such as 

=GOOGLEFINANCE("CURRENCY:GBPEUR", "price", date(2017,11,15))

return a table with columns and headers. 

Date                Close
11/15/2017 23:58:00 1.1163

From the way you use this formula, I see you just want the exchange rate. Wrap the function in index(..., 2, 2) to get the second cell in second row.

=index(GOOGLEFINANCE("CURRENCY:GBPEUR", "price", date(2017,11,15)), 2, 2)
  • 1
    @user6655984 What if instead of the date I need to use a refernce to call with the date? Say =index(GOOGLEFINANCE("CURRENCY:GBPEUR", "price", date(A5)), 2, 2)??? – AlexShevyakov Feb 02 '19 at 10:14
  • 3
    @AlexShevyakov it would be: `=INDEX(GOOGLEFINANCE("CURRENCY:GBPEUR", "price", A5), 2, 2)` – player0 Oct 09 '19 at 17:45
8

Just to make sure it's known, the , operator isn't used in today's Google Sheets, so the correct expression to avoid a parser error would be (in your case):

=index(GOOGLEFINANCE("CURRENCY:GBPEUR"; "price"; date(2017;11;15)); 2; 2)
Niloct
  • 9,491
  • 3
  • 44
  • 57
  • Google sheet help pages use the `,` separator though... https://support.google.com/docs/answer/3093281 – MrSlash Apr 10 '22 at 11:59
4

In Google Sheets, I have done like this, using semicolons:

=index(googlefinance("currency:USDNOK";"price";G11);2;2)

G11 is my cell containing the date. It seems to do the work for me. I tested it with todays date in G11 and got the same result as for this simplified version for today's currency rate:

=googlefinance("currency:USDNOK")
Procrastinator
  • 2,526
  • 30
  • 27
  • 36
Thomas K. Føre
  • 41
  • 1
3

Here's my version of this formula.

Where B3 should be valid date.

=index(GOOGLEFINANCE("CURRENCY:USDCNY", "price", B3), 2, 2)

PS. I'm not sure why, but when I specify the 15-Dec-2018 the formula shows me an error. All other dates are work correct.

Gaurav Jeswani
  • 4,410
  • 6
  • 26
  • 47
Ferooz
  • 31
  • 1
1

your issue is (was) that you trying to force data for non-existing 15th month. syntax for DATE is:

=DATE(YEAR(), MONTH(), DAY())

so the full formula should be:

=Finance!B4*INDEX(GOOGLEFINANCE("CURRENCY:GBPEUR", "price", DATE(2017, 11, 15)), 2, 2)

alternatives are:

=Finance!B4*INDEX(GOOGLEFINANCE("CURRENCY:GBPEUR", "price", "15/11/2014"), 2, 2)

=Finance!B4*INDEX(GOOGLEFINANCE("CURRENCY:GBPEUR", "price", DATEVALUE("15/11/2014")), 2, 2)

=Finance!B4*INDEX(GOOGLEFINANCE("CURRENCY:GBPEUR", "price", A1), 2, 2)

where A1 contains valid date

player0
  • 124,011
  • 12
  • 67
  • 124
0

15-Dec-2018 is a Sunday - hence the error. You can add a -2 or +2 because the error will also pop up on Saturdays. I prefer the -2

SBM
  • 1