DEV Community

Discussion on: Google sheets: get currency rate exchange for a specific date

View post

Replies for: 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 wi...

Collapse
 
nacnac profile image
NacNac •

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

Collapse
 
fonsp profile image
Fons van der Plas •

Try

=index(GoogleFinance("CURRENCY:USDEUR", "price",E1),2,2 )
Enter fullscreen mode Exit fullscreen mode

instead of

=index(GoogleFinance("CURRENCY:USDEUR", "price",E1),0,2 )
Enter fullscreen mode Exit fullscreen mode

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!

Thread Thread
 
nacnac profile image
NacNac •

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!!!