2

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.

Ged
  • 67
  • 2
  • 6
  • Have you tried this? =GOOGLEFINANCE("CURRENCY:EURGBP", "price", B13, B100, "DAILY") as the B13 will be the start date and B100 the end date. If this does not work please post the Spreadsheet to take a look to the data and the errors. – J_P Feb 22 '18 at 20:04

2 Answers2

2

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).

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • I have successfully called out the exchange rates, however I am struggling to match the rates to the dates using VLOOKUP. I am attaching a simplified spreadsheet with required data only showing what I have done so far: https://docs.google.com/spreadsheets/d/1kX7EGsbGNX2IUEj1wPeY9WhR4okrLUqO7QzsapDw3Ug/edit?usp=sharing The formula that I am using for this particular spreadsheet is: `=VLOOKUP(A3,A3:D73,3)` The currency rates in this formula follow transaction dates, however the rates become inaccurate or so it seems. The rates also cut off at row 49 for some reason. – Ged Feb 25 '18 at 19:13
  • If I understood correctly, this is the formula that I need to use `=VLOOKUP(A3,$A$3:$D$73,4)`. I have also added False at the end to show exact matches only, which changed how the data appears. So the full current formula is `=VLOOKUP(A3,$A$3:$D$73,4,False)` The currency rates still cut off at row 54 this time and I have also noticed that the currency rates are not incorrect but rather skip some rows. For instance at row 7 (04/01/2018) the rate should be 0.89053278, however it appears as 0.88642613, which is actually the rate for 05/01/2018. – Ged Feb 25 '18 at 19:55
0

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

Serghei Gorodetki
  • 162
  • 3