About: Building 🏗 https://turnshift.app, Shift Scheduling for Slack Teams. Also: https://nextjsnews.com https://github.com/vvo https://dev.to/vvo Before: JavaScript and support @algolia .
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!
Looks like you can! I just implemented this exactly for helping calculate my tax in Australia, trading US equities.
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
=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!
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!!!
Fantastic post @vvo thank you.
I just wanted to share how flexible and easy the Google function is to work with. See Pic and then the function in column D is: =index(GoogleFinance("CURRENCY:GBP"&B2, "price", A2),2,2)
So it seems as long as the date column is recognised as a date the format doesn't matter and you can pull it from another cell (see A2), also the currency type can be changed easily with a simple text append (& with the currency code from another cell). So here I'm converting the currency against GBP for different dates and different currencies.
Very cool!
What if I want to user another box that include date in other format? like 24/04/2022? Can I take date from their?