Using Market Data’s Google Sheets Add-On, it is possible to get data from thousands of stock indices into your Google Sheet. Even though Google Sheets users can use the =GOOGLEFINANCE formula to get a some indices, the formula can be very difficult to use. Google uses non-standard names for the indices and that can make it extremely difficult for users to find the symbol they are looking for.
How To Get Real-Time Index Data In Google Sheets
With Market Data’s Sheets Add-On you use a simple formula INDEXDATA along with the standard index symbol that you need (i.e. SPX, DJI, IXIC, NDX, VIX, etc.) There are no leading or trailing index identifiers like ^ or $ or .X. We’ve eliminated the guesswork when it comes to what index symbol to use. It has never been easier to get index data in your spreadsheet. For example, to get a real-time SPX quote, just use =INDEXDATA("SPX").
The syntax of the INDEXDATA formula is exactly the same as GOOGLEFINANCE as well as the other Market Data price formulas. So if you have used Google Finance or Market Data in the past, you’ll be able to get started with indices in just a few seconds.
How To Get Historical Index Data In Your Spreadsheet
You can get the historical price for any index by simply adding the date you need. Let’s say we want to get the price of SPX on September 1, 2022. Use =INDEXDATA("SPX","date,price","9/1/2022") and the result will be a historical closing price for the day in question. Like all Market Data formulas, if you request just a single parameter (such a price instead of date and price) then the output will populate a single cell with no headers. This is a great option if you plan on using the output in another formula.
It is possible to get time-series data with INDEXDATA as well. Just add a second date parameter and the formula will output for multiple dates. For example: =INDEXDATA("SPX","date,price","8/1/2022", "8/31/2022") will output all the closing prices for SPX during August 2022.
And there is no need to stop there. You can even get OHLC output if you’d like to build a candlestick chart. If you use the all parameter =INDEXDATA("SPX","all","8/1/2022", "8/31/2022") this will output a column for the date plus open, high, low, close values for the dates you requested.
How To Get Intraday Index Data Into Your Spreadsheet
Intraday data is available for indices as well. Resolutions as low as 1 minute can be used for most indices. To request intraday data, add a 5th parameter to the formula with the resolution of the candle you need. Here are some of the resolutions you can use for intraday data:
- Minutely Resolutions:
("minutely","1","3","5","15","30","45", …) - Hourly Resolutions: (
"hourly","H","1H","2H", …)
The daily resolution is the default (when you don’t use a 5th parameter) in the INDEXDATA formula. However, it is also possible to analyze indexes on a longer scale than daily. INDEXDATA can be used to return full OHLC candles for any time period you want to analyze:
- Daily Resolutions: (
"daily","D","1D","2D", …) - Weekly Resolutions: (
"weekly","W","1W","2W", …) - Monthly Resolutions: (
"monthly","M","1M","2M", …) - Yearly Resolutions: (
"yearly","Y","1Y","2Y", …)
Additional Features & Try For Free
INDEXDATA supports all the standard features of our other formulas, such as noheaders, nocache, etc. Check our online documentation portal for Google Sheets for complete usage information of our Google Sheets Add-On.
You can do a quick demo of INDEXDATA for free without any registration by installing the Sheets Add-On and using the VIX symbol. If you’d like to try other symbols such as SPX, DJI, NDX, etc., you’ll need to register a free account with Market Data and get a token. You can use Market Data’s Free Forever plan with our Add-on at no cost for up to 100 prices per day.
