Notes and Thoughts on Retrieving Historical Members of S&P 500 from WRDS

By Tee Lip Hwe, Research Librarian, Accountancy & Business (Finance)

Retrieving all historical members or constituents of S&P 500 can be done using Python coding on the CRSP dataset through an API connection with WRDS.

Removal of S&P Index Constituents dataset from Compustat

Previously, the constituents of S&P 500 were retrievable via Compustat. In July 2020 however the S&P Dow Jones Indices (SPDJI) constituent names data was removed from Compustat, due to SPDJI direct licensing. With the move, the constituents of S&P 500, along with other S&P indices, such as the S&P 1500 Super Composite to name few, are no longer available via Compustat.

About CRSP’s S&P 500 historical member listing dataset

The historical members of SPX can be retrieved using the tables crsp_a_indexes.dsp500list_v2 or crsp_a_indexes.dsp500list, both of which capture the daily membership of SPX.

The script below retrieves the SPX members for 29 December 2023, which is the last trading day of 2023. The output shows there were 503 SPX members on 29 December 2023.

spx_2023_12_29 = db.raw_sql(""" 
select count (*)  
from crsp_a_indexes.dsp500list_v2 
where not (mbrenddt < '12/29/2023')
--order by 3  
--limit 10  
""")
spx_2023_12_29

The SQL condition with its mnemonic variable

“not (mbrenddt < '12/29/2023')”

The above script works because the CRSP’s SPX members data is updated annually in WRDS. Hence, at the point of writing this article, we can retrieve the SPX members only up to the year 2023, or more specifically up to 29 December 2023 the last trading day of 2023, which is also the ‘maximum allowed date’ (for the year 2023) for the script execution to be meaningful or sound.

It is also for the above reason that all the stock entities that were members as at 29 December 2023 still (or at least till 29 December 2023 as we speak), would have the mnemonic variable ‘mbrenddt’ indicated as or defaulted to 12/29/2023. This practice was carried out to keep track and allow for the next members data update for the year 2024.

spx_2023_12_29 = db.raw_sql(""" 
select mbrenddt, count (*)  
from crsp_a_indexes.dsp500list_v2 
where not (mbrenddt < '12/29/2023')
group by mbrenddt  
--order by 1  
""")
spx_2023_12_29

In other words, SPX members with

‘mbrenddt = 12/29/2023’

That is to say, come last trading day of 2024, stock entities that remain as SPX members as at the last trading day of 2024 will have their ‘member end date’ indicated as or defaulted to the last trading day of 2024, to make way for the next annual update for 2025. To leave a deep impression on the unique approach and rationale of this data update practice, I coin the catchphrase/slogan ‘There is no tomorrow’.

Note: Considering that the data for SPX members is recorded daily (as indicated by 'dsp' in 'dsp500list_v2', which stands for 'daily stock price'), specifying non-trading days like '12/31/2023' or '12/30/2023' in the condition “not (mbrenddt < 'MM/DD/YYYY')” will result in retrieving no data.

spx_2023_dec_31_or_30 = db.raw_sql(""" 
select count (*)  
from crsp_a_indexes.dsp500list_v2 
where not (mbrenddt < '12/31/2023' or mbrenddt < '12/30/2023')
--group by 3  
--limit 10  
""")
spx_2023_dec_31_or_30

S&P 500 members demographic

As shown in the output of the following script which retrieves the 503 SPX members for 29 December 2023, there were 15 that became a member of SPX in 2023, 16 in 2022, 15 in 2021, and so on, as shown in the field ‘member_start_date’ or ‘mbrstartdt’.

spx_2023_12_29 = db.raw_sql(""" 
select substr(cast(mbrstartdt as VARCHAR(11)),1,4) as member_start_date, count (*)  
from crsp_a_indexes.dsp500list_v2 
where not (mbrenddt < '12/29/2023')
group by member_start_date  
order by 1 desc  
""")
spx_2023_12_29

From the above script we can therefore identify the oldest/older members of SPX (as at 29 December 2023). We can see that there are 8 stock entities (out of 503) where the ‘member start date’ began in 1925.

Given that the earliest data available date for CRSP is 31 December 1925, it is important to recognise that a stock entity with a 'member start date' of 31 December 1925 would be either: (i) a member of the SPX (or its predecessors) beginning 31 December 1925, or (ii) potentially a member the SPX (or its predecessors) before 31 December 1925 but the date was defaulted to 31 December 1925, or (iii) possibly having no recognisable 'member start date' and hence the date was defaulted to 31 December 1925.

From Britannica Money, the S&P 500 Index (SPX), was formerly called the Composite Index and later Standard & Poor’s Composite Index. It was launched on a small scale in 1923, began tracking 90 stocks in 1926, and expanded to 500 in 1957.

Retrieving all S&P 500 members for 2023 or multiple years

It follows from the same data logic above that in order to retrieve all SPX members for the whole year of 2023, researchers will need

“not (mbrenddt < '01/01/2023')”

spx_2023 = db.raw_sql(""" 
select mbrenddt, count (*)  
from crsp_a_indexes.dsp500list_v2 
where not (mbrenddt < '01/01/2023')
group by mbrenddt  
order by 1  
""")
spx_2023

One can further deduce that in order to retrieve all the constituents of SPX that were members at any time during the 3-year period between 2021 and 2023, the SQL condition would therefore be

“not (mbrenddt < '01/01/2021')”

spx_2023_to_2021 = db.raw_sql(""" 
select permno, mbrstartdt, mbrenddt  
from crsp_a_indexes.dsp500list_v2 
where not (mbrenddt < '01/01/2021')
order by 3, 2  
""")
spx_2023_to_2021

What’s Next

With ‘permno’, the permanent stock-level identifier from CRSP, researchers can proceed to set their desired SPX members universe to retrieve their desired data items from CRSP, or perform data linking with other datasets such as Compustat, etc.

Start exploring by connecting to WRDS using Python today.

For help, please contact: Tee Lip Hwe, Research Librarian, Accountancy & Business (Finance)