How to use Google Sheets to retrieve the previous 5 days' Open, Close, High, and Low Prices from GoogleFinance for 'CURRENCY:EURGBP'?

Other Questions Google Sheets
1

I hope this message finds you well during these challenging times. I’ve been struggling with Google Sheets and it’s starting to frustrate me. I need assistance! I’m attempting to extract data using the =GoogleFinance function, but unfortunately, I’m encountering errors. I began with this basic formula, but it hasn’t worked as expected:

=GOOGLEFINANCE("CURRENCY:EURGBP", "OPEN", TODAY())

Now, I need to find a way to obtain the last 5 days of data. Could someone guide me on how to retrieve the Open, Close, High, and Low prices for those days so I can perform some calculations? I would really appreciate your help. Wishing you a Happy New Year!

2
DataJunkie Hey FlyingEagle! It looks like Finn_Mystery got you covered with the dates setup, but just thought I'd add: make sure your sheet is set to auto-refresh. Data might not updae correctly if your sheet's been open awhile. Let me know if that helps! 😊
3

To effectively use Google Sheets to get the previous 5 days’ pricing data for ‘CURRENCY:EURGBP’, you’ll need to modify your formula to include the date range. The GoogleFinance function requires you to specify the start and end dates to retrieve historical data. You can calculate the start date by subtracting 5 days from today using the date functions within Google Sheets. Here’s a revised approach that should help:

=GOOGLEFINANCE("CURRENCY:EURGBP", "open", TODAY()-5, TODAY())
=GOOGLEFINANCE("CURRENCY:EURGBP", "close", TODAY()-5, TODAY())
=GOOGLEFINANCE("CURRENCY:EURGBP", "high", TODAY()-5, TODAY())
=GOOGLEFINANCE("CURRENCY:EURGBP", "low", TODAY()-5, TODAY())

Each of these functions will give you a column of values for each day within the last five days for the specified currency. You can use these to perform any additional calculations you need. Just ensure your date range doesn’t exceed the available data limit and adjust the range to align with market availability days, since data might not be present for weekends.

4

You might want to consider using a helper column to manage the data more effectively. Once you retrieve the historical data using the updated formulas, you can have a separate column calculate the average or any other metric you need over those five days. This can be done using basic functions like AVERAGE, MAX, or MIN in Google Sheets. Additionally, ensure the spreadsheet time zone matches your local time zone to avoid data retrieval errors due to time discrepancies.