S&P 500 Constituents

This code illustrates how to get historical S&P 500 index constituents and their identifiers from CRSP and Compustat.

##########################################
# S&P 500 Index Constituents             #
# CRSP CIZ Format                        #
# Qingyi (Freda) Song Drechsler          #
# Date: October 2020                     #
# Updated: October 2026                  #
##########################################

import pandas as pd
import wrds

###################
# Connect to WRDS #
###################
conn=wrds.Connection()

2. Get S&P 500 Index Membership from CRSP

  • I opt for the monthly frequency of the data (crsp.msp500list_v2), but one can choose to work with crsp.dsp500list_v2 if more precise date range is needed.
  • Monthly returns come from the CRSP monthly stock file in the new CIZ format (msf_v2).
sp500 = conn.raw_sql("""
    select distinct a.permno, a.mbrstartdt, a.mbrenddt,
           b.mthcaldt, b.mthret
    from crsp_m_indexes.msp500list_v2 as a,
         crsp_m_stock.msf_v2 as b
    where a.permno=b.permno
    and b.mthcaldt >= a.mbrstartdt and b.mthcaldt <= a.mbrenddt
    and b.mthcaldt between '01/01/2000' and '06/30/2026'
    order by b.mthcaldt, a.permno
    """, date_cols=['mbrstartdt', 'mbrenddt', 'mthcaldt'])

3. Add Other Company Identifiers from crsp.stksecurityinfohist

  • You don’t need this step if only PERMNO is required
  • This step aims to add TICKER, CUSIP, company name, share type, primary exchange, SIC code and etc.
# Add Other Descriptive Variables

stkinfo = conn.raw_sql("""
    select distinct permno, issuernm, cusip, ticker,
           sharetype, securitytype, securitysubtype,
           primaryexch, siccd, secinfostartdt, secinfoenddt
    from crsp_m_stock.stksecurityinfohist
    """, date_cols=['secinfostartdt', 'secinfoenddt'])

# Merge with SP500 data
sp500_full = pd.merge(sp500, stkinfo, how='left', on='permno')

# Impose the date range restrictions
sp500_full = sp500_full.loc[
    (sp500_full.mthcaldt >= sp500_full.secinfostartdt)
    & (sp500_full.mthcaldt <= sp500_full.secinfoenddt)]

4. Add Compustat Identifiers

  • Link with Compustat’s GVKEY and IID if need to work with fundamental data
  • Linkage is done through crsp.ccmxpf_linktable
# Linking with Compustat through CCM

ccm=conn.raw_sql("""
    select distinct gvkey, liid as iid, lpermno as permno,
           linktype, linkprim, linkdt, linkenddt
    from crsp_m_ccm.ccmxpf_linktable
    where substr(linktype,1,1)='L'
    and (linkprim ='C' or linkprim='P')
    """, date_cols=['linkdt', 'linkenddt'])

# if linkenddt is missing then set to today date
ccm['linkenddt']=ccm['linkenddt'].fillna(pd.to_datetime('today'))

# Merge the CCM data with S&P500 data
# First just link by matching PERMNO
sp500ccm = pd.merge(sp500_full, ccm, how='left', on=['permno'])

# Then set link date bounds
sp500ccm = sp500ccm.loc[(sp500ccm['mthcaldt']>=sp500ccm['linkdt'])\
                        &(sp500ccm['mthcaldt']<=sp500ccm['linkenddt'])]

# Rearrange columns for final output

sp500ccm = sp500ccm[['mthcaldt', 'permno', 'issuernm', 'cusip',
                     'ticker', 'sharetype', 'primaryexch', 'siccd',
                     'gvkey', 'iid', 'mbrstartdt', 'mbrenddt',
                     'mthret']]

Sample output from the final data frame:

10 randomly drawn rows of the 159,142 in sp500ccm (Jan 2000–Jun 2026):

mthcaldt permno issuernm cusip ticker sharetype primaryexch siccd gvkey iid mbrstartdt mbrenddt mthret
0 2000-08-31 41443 ALLTEL CORP 02003910 AT NS N 6711 001318 01 1994-12-21 2007-11-16 -0.179513
1 2007-01-31 86356 EBAY INC 27864210 EBAY NS Q 7380 114524 01 2002-07-22 2026-08-31 0.077153
2 2008-11-28 86176 KING PHARMACEUTICALS INC 49558210 KG NS N 2834 112033 01 2000-10-03 2010-12-17 0.093288
3 2009-11-30 46886 K L A TENCOR CORP 48248010 KLAC NS Q 3827 006304 01 1997-09-15 2026-08-31 -0.034694
4 2016-01-29 82642 LAUDER ESTEE COS INC 51843910 EL NS N 2844 061567 01 2006-01-05 2026-08-31 -0.031910
5 2016-09-30 14702 APPLIED MATERIALS INC 03822210 AMAT NS Q 3550 001704 01 1995-03-16 2026-08-31 0.010389
6 2017-02-28 70033 HARLEY DAVIDSON INC 41282210 HOG NS N 3751 012389 01 2000-01-31 2020-06-19 -0.005259
7 2018-04-30 13963 NEWS CORP NEW 65249B10 NWSA NS Q 2711 018043 01 2013-07-01 2026-08-31 0.011392
8 2020-04-30 21573 INTERNATIONAL PAPER CO 46014610 IP NS N 2621 006104 01 1925-12-31 2026-08-31 0.100225
9 2021-11-30 42200 PERKINELMER INC 71404610 PKI NS N 3845 004145 01 1985-05-09 2026-08-31 0.029793