Symbol for S&P 500

[neasds]

What symbol do you use to query the S&P 500 using the stockhistory function?

 

[Joe4]

It looks like the StockHistory function returns information of certain stocks/funds based on their ticker symbol, as explained here:

How to Use the Excel STOCKHISTORY Function

Discover how to use the Excel STOCKHISTORY function to retrieve and visualize stock data. Track monthly price changes and identify highs and lows.

www.xelplus.com

Since the "S&P 500" really isn't an individual fund, I don't know if it will work.
But you can try, based on the symbol mentioned here: S&P 500 Index Ticker Symbol.

 

[neasds]

It looks like the StockHistory function returns information of certain stocks/funds based on their ticker symbol, as explained here:

How to Use the Excel STOCKHISTORY Function

Discover how to use the Excel STOCKHISTORY function to retrieve and visualize stock data. Track monthly price changes and identify highs and lows.

www.xelplus.com

Since the "S&P 500" really isn't an individual fund, I don't know if it will work.
But you can try, based on the symbol mentioned here: S&P 500 Index Ticker Symbol.

Thanks, but that doesn't work.

 

[Joe4]

If you look further down the article in the second link I provided, it looks like it says you can use "SPX" for that (see the section entitled "Other Tickers Symbols for the S&P 500").

So I entered this formula in cell A1 to get yesterday's closing price:

=STOCKHISTORY("SPX",TODAY()-1,TODAY()-1)

And this is what it returned for me:

 

[alansidman]

This site provides a free Add in that I use to track stock prices including the S&P

Stock Connector add-in for Excel

www.michael-saunders.com

 

[Joe4]

This site provides a free Add in that I use to track stock prices including the S&P

Stock Connector add-in for Excel

www.michael-saunders.com

Alan,

Just curious, what are the pros/cons of the Add In?
How is it better/worse than the StockHistory function?

I have never really used either before, so am just curious about the limitations.

 

[alansidman]

It updates every x seconds with a 20 minute delay. You set the interval. I've been using it since before Stock History became available. It also supports some ETFs and Mutual Funds that don't seem to be available with StockHistory. Also, can get prices on Precious Metals, International, Foreign Currency and Crypto Currencies. Easy to use.

 

[MARK858]

If you look further down the article in the second link I provided, it looks like it says you can use "SPX" for that (see the section entitled "Other Tickers Symbols for the S&P 500").

So I entered this formula in cell A1 to get yesterday's closing price:

=STOCKHISTORY("SPX",TODAY()-1,TODAY()-1)

And this is what it returned for me:
View attachment 116759

Hi Joe, that is the closing price for SPX Technologies, the closing price for S&P Index was $ 5,554.13, you need to type SPX in a cell, click Stock History, scroll down the list, expand it to show all results and select manually.

The S&P Index has been an unresolved issue for Excel for a long time (You have to do the same with the INX ticker it shows as that defaults to Intouch Insight Ltd, although you can see S&P Index in the initial results without expanding it).

A few images of the SPX ticker below

 

[Joe4]

Alan & Mark,
Thanks for the information and clarification. I learned something new today!