Vincent Voyer
Posted on
#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)
Subscribe
NacNac
NacNac
-
Joined
• Jun 12 '22
- Copy link
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
NacNac
-
Joined
• Jun 12 '22
- Copy link
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
- Copy link
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!
NacNac
NacNac
-
Joined
• Jun 15 '22
- Copy link
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!!!
XDavidT
XDavidT
-
Joined
• Apr 24 '22
- Copy link
What if I want to user another box that include date in other format? like 24/04/2022? Can I take date from their?
Jack Hales
Jack Hales
Developer developer developer
-
Location
Australia
-
Work
Data Analyst
-
Joined
• May 9 '22
- Copy link
Looks like you can! I just implemented this exactly for helping calculate my tax in Australia, trading US equities.
For further actions, you may consider blocking this person and/or reporting abuse
