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)
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
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
Try
instead of
The
2,2means: of the table returned byGoogleFinance("CURRENCY:USDEUR", "price",E1), take the second row, second column.I believe that
0,2means: of table returned byGoogleFinance("CURRENCY:USDEUR", "price",E1), take every row (0has special meaning), and the second column. Try playing around with these numbers so see what I mean.Hope this helps!
Damn, I did play a bit with those index but obviously did not go up to 2,2
It did the trick.
Thank you so much Fons van der Plas!!!
What if I want to user another box that include date in other format? like 24/04/2022? Can I take date from their?
Looks like you can! I just implemented this exactly for helping calculate my tax in Australia, trading US equities.