Converting from PLN to USD based on the Date of Input

0
votes

I am traveling to Poland for a work trip and I would like to keep track of the restaurants I go to and the prices that I am paying for my meals.

I have a Google Sheet with a column for Date, Price (PLN) and Price (USD). In the Price (USD) column, I have the following: =D2* GoogleFinance("CURRENCY:PLNUSD") where D2 is the PLN column.

Now, how do I take into account the ending price of the PLN-USD exchange rate on the day that I made the purchase?

The sheet can be found here: https://docs.google.com/spreadsheets/d/1j19mtRZ9IeSYT3D5N1oxlZ02zM7jY_o7ayQIB8clCrM/edit?usp=sharing

Thanks!

1
google-sheets

1 Answers

1
votes

Change E2 to:

=iferror(index(GoogleFinance("CURRENCY:PLNUSD","close",$C2),2,2)*$D2)

This will get the currency exchange rate close for the date in column C (in your sample sheet 1/19/2016 and multiply it by your amount in column D (in your example 34.25). The iferror will let you copy the formula down without an error so you can fill in dates and amounts later. Index gets just the retutned exchange rate. without index =GoogleFinance("CURRENCY:PLNUSD","close",$C2) returns

Date    Close
1/19/2016 23:58:00  0.24522434