Anyone analyzing stocks or ETFs on Google Sheets has been using the GOOGLEFINANCE formula to get daily or weekly stock quotes. Unfortunately, the Google Finance formula has never offered intraday stock prices and there is currently no way to get intraday stock data using the Google Finance formula. However, Market Data offers a simple formula that works exactly the same way as Google Finance, but can provide intraday stock data down to the minute.
Get Intraday Stock Prices in Google Sheets
Getting intraday stock prices in Google Sheets has never been easier. Just use Market Data’s STOCKDATA formula the same way you’ve been using the Google Finance formula. Let’s assume you want intraday stock prices for AAPL on 1/10/2020. If you were using Google Finance, the formula would be as follows: =GOOGLEFINANCE("AAPL", "all", "1/10/2020", "1/11/2020", "daily"). The Google Finance formula would return just a single stock candle for 1/10/2020. However, if you use the STOCKDATA formula the exact same way: =STOCKDATA("AAPL", "all", "1/10/2020", "1/11/2020", "minutely")the STOCKDATA function will return intraday data (at the 1 minute resolution) for the day of 1/10/2020.
Get 5 Minute, 30 Minute, Hourly Candles or Any Other Resolution In Your Spreadsheet
By modifying the last parameter of the STOCKDATA formula, you can adjust the resolution of the candle output to whatever time resolution you are working in. Market Data supports any resolution, not just the commonly used ones. If you want to generate 23 minute candles, the STOCKDATA formula will generate candles with 23 minutes of data. Here are some of the resolutions you can use:
- Minutely Resolutions: (“minutely”, “1”, “3”, “5”, “15”, “30”, “45”, …)
- Hourly Resolutions: (“hourly”, “H”, “1H”, “2H”, …)
- Daily Resolutions: (“daily”, “D”, “1D”, “2D”, …)
- Weekly Resolutions: (“weekly”, “W”, “1W”, “2W”, …)
- Monthly Resolutions: (“monthly”, “M”, “1M”, “2M”, …)
- Yearly Resolutions: (“yearly”, “Y”, “1Y”, “2Y”, …)
=STOCKDATA("AAPL", "all", "1/1/2021", "12/31/2021", "4H").How To Get Intraday Historical Closing Prices in Google Sheets
If you are only interested in closing prices, you can filter the result from the formula using the second parameter. Let’s say you wanted the closing price on 2 hour candles for January 2022 for AAPL. The formula to use would be: =STOCKDATA("AAPL", "close", "1/1/2022", "1/31/2022", "2H").
Use Market Data Formulas As Inputs Into Your Other Formulas
Sometimes you might want feed the results of a Market Data formula into one of your other formulas as an input. You can instruct the Market Data formula to return a 1 column array with no headers by using the “noheaders” option when you make your request. For example, let’s suppose you wanted to calculate the average closing price during all of January 2022 for AAPL. This is easy to do by requesting the closing price for AAPL during January and embedding the result inside an AVERAGE formula. For example: =AVERAGE(STOCKDATA("AAPL", "close, noheaders", "1/1/2022", "1/31/2022", "daily")). The possibilities are endless.
