Get exchange rate on a specific date from GoogleFinance
Solution 1
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)
Solution 2
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.
Solution 3
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")
Solution 4
:D Nothing works. No matter what date I insert or address from a cell, no matter what currency pair I declare, what price I insert or address from a cell, with or without index → it returs with "analysis error". I love it when this happens.
Solution 5
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
cala
Diving in to React & Redux, and learning how to implement new technologies along the way. Interested in using firebase, mongodb, express & node.
Updated on July 09, 2022Comments
-
cala almost 2 years
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
-
Paul Razvan Berg about 4 yearsI never encountered this issue for weekend days.
-
Niloct about 3 yearssee a possible cause in my answer.
-
MrSlash about 2 yearsGoogle sheet help pages use the
,
separator though... support.google.com/docs/answer/3093281