Google sheets: get currency rate exchange for a specific date

#googlespreadsheets #googlefinance #currencyexchangerate

Hi there, here's a quick post on how to get and display a currency rate exchange for a specific date in Google Spreadsheets. This is useful in some situations.

The formula to use is GOOGLEFINANCE and combine it with the INDEX formula to get only the relevant data.

Here's an example: =index(GoogleFinance("CURRENCY:USDEUR", "price", DATE(2020, 04, 10)), 2, 2) would get the USD to EUR rate as for 2020/04/10.

Enjoy!

Top comments (6)

NacNac • Jun 12 '22

Hello
thank you for your post. It has helped me a lot as I am the closest so far to what I want to do.

Please see screenshot attached.
The date will be a variable from a different cell, in my example the date I want the rate USDEUR is on E1.
This works. However, I want the result in the cell where I put the formula.
Here I have written the formula in A1 but the result is in A2.

I am creating a table with many row to reflect average cost and I need the result in the same cell as the formula as for most formula (eg if I write =A1+A2 in A3 the result will be in A3)

I hope there is a way with the googlefinance formula.

When I created my spreadsheet for the first time I used =GOOGLEFINANCE("Currency:USDEUR")
This did the job until I realise that it was today's date and some calculations needed the exchange rates for a given date.

I hope someone will be kind enough to help me.
Thank you very much

NacNac • Jun 12 '22

For some reasons it did not take my screenshot :(
so the formula I wanted to show you is

=index(GoogleFinance("CURRENCY:USDEUR", "price",E1),0,2 )

E1 is the where the date is

The result is, after having written the formula in the cell A1

A1 shows Close
A2 shows the exchange rate of USDEUR for the date in A1

What I would like to do is write the formula in A1 and the result is shown in A1

Thanks

Fons van der Plas • Jun 15 '22

Try

=index(GoogleFinance("CURRENCY:USDEUR", "price",E1),2,2 )

instead of

=index(GoogleFinance("CURRENCY:USDEUR", "price",E1),0,2 )

The 2,2 means: of the table returned by GoogleFinance("CURRENCY:USDEUR", "price",E1), take the second row, second column.

I believe that 0,2 means: of table returned by GoogleFinance("CURRENCY:USDEUR", "price",E1), take every row (0 has special meaning), and the second column. Try playing around with these numbers so see what I mean.

Hope this helps!