CODEWITHSUNDEEP
google-sheetsgoogle-docscurrency-exchange-rates
Ged
Ged

Reputation: 67

How to list currency exchange rates for each day on Google Spreadsheet?

I would like to know if it is possible to get currency exchange rates for each past day on Google Spreadsheets.

Currently I have the following formula (Column B contains all the dates that had cash flow): =GOOGLEFINANCE("CURRENCY:EURGBP", "price", B13, B13+100, "DAILY")

The drawback with this formula is that even though it starts with the first given date it disregards that some of the dates on my spreadsheet appear more than once or that some of the dates are not in subsequent order and simply lists new exchange rates in a regular order. With that said I want the formula to follow the dates on my spreadsheet. For example if I would have 30/01/2018 a few times in a row in Column B I would like the formula to give the same exchange rate in each row as long as the date remains the same.

I have also tried this formula =GOOGLEFINANCE("CURRENCY:EURGBP", "price", B13:B100, "DAILY") however the spreadsheet is giving an error.

Upvotes: 2

Views: 5360

Answers (2)

Serghei Gorodetki
Serghei Gorodetki

Reputation: 162

Other option is using the CurrencyConverter function from this Google Sheets add-on. It is fast, has simple syntax and supports 40 currencies. For example, 

=CurrencyConverter(100, "USD", "EUR", "2/28/2020")

returns 91.09957183

Upvotes: 0

pnuts
pnuts

Reputation: 59485

Call all the exchange rates you need (and possibly more) once with GOOGLEFINANCE, say with:

=GOOGLEFINANCE("CURRENCY:EURGBP", "price", date(2017,1,1), date(2018,12,31), "DAILY")

Then use the resulting array as a lookup table to match the rates to the dates you require (say with VLOOKUP or INDEX/MATCH).

Upvotes: 3

Related Questions