S&P 500 Components

Back in the middle of 2014 I shared a Google Sheet with the S&P 500 components. I let that get out of date, so I finally got around to updating it. My source for the updates is this Wikipedia page:

https://en.wikipedia.org/wiki/List_of_S%26P_500_companies

My Google Sheet is here:

https://docs.google.com/spreadsheets/d/1K1v65_r4wCQLETbmNHCY…

My Google Sheet has 2 tabs, Rankings and Data.

The Data tab contains the following columns for each stock:

A Ticker symbol
B Security
C GICS Sector
D GICS Sub Industry
E Address of Headquarters
F Date first added
G CIK
H Current Price - =googlefinance(ticker)
I Today’s % chg - =googlefinance(ticker,“changepct”)
J 52 week high - =googlefinance(ticker,“high52”)
K % off high - =H2/J2-1
L Rank % off high - =rank(K2,K$2:K$505,1)+countif(K$2:K2,K2)-1
M 52 week low - =googlefinance(ticker,“low52”)
N % above low - =H2/M2-1
O Rank % above low - =rank(N2,N$2:N$505)+countif(N$2:N2,N2)-1

Q Price 12/31/14 - =index(googlefinance(ticker,“close”, “12/31/14”),2,2)
R Price 12/31/15 - =index(googlefinance(ticker,“close”, “12/31/15”),2,2)
S % chg 2015 - =R2/Q2-1
T Rank % Chg 2015 - =rank(S2,PctChg2015)+countif(S$2:S2,S2)-1

V % Chg YTD - =H2/R2-1
W Rank % Chg YTD - =rank(V2,PctChgYTD)+countif(V$2:V2,V2)-1

In columns Q & R, I copy pasted values for most of the rows, so that I didn’t have GoogleFinance attempt to update values which are static. Also, GoogleFinance is a bit flaky on obtaining historical prices, so if you leave the formulas there, you are liable to find NA’s where before there was a value. There are also a number of stocks which were added to the S&P 500 after Dec 31, 2014, so I have used the earliest quote shown by Google Finance or Yahoo Finance, and inserted a comment to indicate the starting date.

According to the Wikipedia page there should be 505 stocks. I only have 504, so if someone with sharp eyes spots the missing stock, let me know and I will add it. (Correction - I subsequently noticed that one of the Comcast share classes was eliminated in December.)

(Even as I’m typing this, the quotes for INTC have gone to #REF!, so I have to paste in the values - note to aleax and Anurag - maybe Google needs to acquire the Yahoo Finance assets if Marissa does decide to spin things off - Yahoo Finance seems much more robust than Google Finance.)

Down at the bottom of the Data tab, beginning on row 509 is a summary of the average performance by GICS sector.

The Rankings tab currently has the following four rankings:

Percent off 52 Week High Rankings
Percent Above 52 Week Low Rankings
2015 % Change
YTD % Change

Part of the purpose of this is to show you how to do this. All of these ranking tables are driven off the Data tab. I’ll use the Percent off 52 Week High Rankings as an example.

In column E I have the formula =match(A3,RankOffHigh,0). This is going to find the row in the data range RankOffHigh which has the rank in cell A3.

The rest of the columns use this row:

B - =index(Tickers,E3)
C - =index(StockNames,E3)
D - =index(PctOffHigh,E3)

Tickers, StockNames and PctOffHigh are named ranges on the Data tab.

All of this is a long winded way to show that the Average S&P 500 stock is down 25.4% from it’s high. The average SA stock is down 32.3% from it’s high, the average RB stock is down 38.7% from it’s high and the average HG stock is down 39.1% from it’s high.

Also, people who subscribe to SA, RB and HG, I have shared Google Sheets which look at the % off 52 week high for SA, RB and HG stocks. You can find the posts here:

SA:

http://discussion.fool.com/1081/google-sheet-52-week-highs-and-l…

RB:

http://discussion.fool.com/1069/google-sheet-rb-52-week-highs-an…

HG:

http://discussion.fool.com/1008/google-sheet-hg-52-week-highs-an…

John

If you found this post useful, please click the “Recommend It!” link at the top right. Thank you.

48 Likes

Hi John,

Have you thought about updating the doc? I just went in and the first two column groups are missing data. Also, 2016 is coming to a close, so it’d be interesting to add a 2016 return column group.