Get exchange rate on a specific date from GoogleFinance

Question

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

Zippel

Answer 1

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)

Magnuson

@Magnuson 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)??? – Culinary

@Culinary it would be: =INDEX(GOOGLEFINANCE("CURRENCY:GBPEUR", "price", A5), 2, 2) – Livvy

perfect, just what I needed (head scratching with the specific result in the same cell as the formula. – Reticule

Answer 2

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)

Parament

Google sheet help pages use the , separator though... support.google.com/docs/answer/3093281 – Eastern

This is a time saver,.. sometimes it uses the , don't know why, but in one of my sheets it is. Maybe depend on language settings of the sheet. – Atwell

@Cyber, I did a simple test creating an en-US interfaced new spreadsheet, with Locale: United States settings, and the expression on this answer worked. I also put this expression on a 2015 last edited spreadsheet with Brazilian settings (to check if it's due to old version) and it worked too. So I guess I can't say why comma is accepted on your spreadsheet sometimes. – Parament

Answer 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")

Dian

Answer 4

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.

Estreat

Answer 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

Livvy

Answer 6

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

Farl

I never encountered this issue for weekend days. – Ful

Answer 7

2024

In google sheets, the following worked for me.

=GOOGLEFINANCE("CURRENCY:USDTRY")

and by date

=index(GOOGLEFINANCE("CURRENCY:USDTRY"; "price"; date(2023;1;15)); 2; 2)

or date with column

=index(GOOGLEFINANCE("CURRENCY:USDTRY"; "price"; A6); 2; 2)

---

output:
32,2045
18,7947
29,4768

Note: It was not working with semicolon ,

Quade

"It was not working with semicolon" see why at: https://mcmap.net/q/330818/-locale-differences-in-google-sheets-documentation-missing-pages-closed – Livvy