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

Copper Contributor

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

@rammina 

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.

=SORT(STOCKHISTORY(A1,TODAY()-120,TODAY(),0,1,0,2,3,4,1,5),1,-1)
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.

@rammina 

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.

@Millersville 

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.

@Millersville 

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