Stock History Open/Close Price Not Working for S&P 500

Occasional Visitor

The Stock History function is not working for me when trying to find the Open/Close Price for the S&P 500 (INX). It works for other stocks during the same range, but when I try to use the S&P 500, I keep getting a value error. How do I fix this? Does anyone have recommendations for a different stock/index that provides a proxy for the market who has a historical open/close price connected to Excel?

7 Replies


Did you notice the list of possible matches that appeared on the right side of your screen? You need to select the choice that best fits your query.

This formula will provide a descending sort of stock price historical data for the past 120 days for the symbol listed in cell "A1". The columns are: Date, Open, High, Low, Close, and Volume.


Yes, Nasdaq Composite Index works somehow, but S&P 500 Index not.

I just reconfirmed this process works: Type INX into a cell; Select Data>> Data Types>> Stocks; Select "Select" below the S&P 500 INDEX Index - INX on the right; Select the Insert Data pop-up; Select the Field you wish to insert; repeat this last step for all desired fields. If you are still having issues, then I suggest updating your Excel: File>> Account>> Update Options>> Update Now and try again.


I have no problems with S&P 500 Index for the Stock, but STOCKHISTORY() doesn't work with it

@Sergei Baklan I also could not find a way to directly obtain the S&P500 historical data using STOCKHISTORY, however, the index fund IVV has a 99.976% correlation with the S&P500 over the past year so I suggest you consider using that as a work around.


Thanks for sharing. However, that's workaround. Real problem is that not all indexes are shown by STOCKHISTORY()