Answered By: IESE Library Last Updated: Jan 08, 2020 Views: 850
- Bloomberg (available at Barcelona Library Financial room)
Getting the membership for the start date and the end date in your range will obviously not consider any company that joined and left in the intervening time. It is common to collect the membership at month end for each month in the range, paste the results into one long list in Excel then use the Data > Remove Duplicates function to get a tidy list of each company once only.
(1) Export each month’s constituents
See FAQ Where can I find current constituent lists for the S&P and other indices?
(2) Copy and paste the Tickers into one long column for all months
(3) Select that column and choose Data > Remove Duplicates.
You can get all the other data again using the Bloomberg Excel add-in if you wish (with formulas such as =BDP (“AAPL US Equity”, “ID_ISIN”) where the first parameter is a reference to the cell with the Ticker).
There is a screen in Bloomberg called Historical Index Member Changes CHNG. But only works for some indices and the report cannot be exported.
For the FTSE 100, type ‘UKX <Index> CHNG <GO>’. Choose the date range and click the top ‘Enter 1<GO> to generate report’. A report will be generated and it will tell you the number of the report. Go to the Retrieve Reports screen RPT. View these on screen or save one page at a time to an image or printer.
- Compustat via WRDS - Wharton Research Data Services (First time users will need to click on Register to request a user account and then fill out the form. It takes 24 hours to get approved)
Historical Lists (not comprehensive, but includes the S&P indices and others)
Select data set Compustat-Capital IQ then North America-Daily and then Index Constituents then follow the instructions.
Step 1: What date range do you want to use? Select start date and end date
Step 2: How would you like to search this dataset? Click: Search the entire database
Step 3: What variables do you want in your query
Under Index Information, Click: CONM -- Index Name
Under Company Information, Click: Check All
Step 4: How would you like the query output? Choose Excel Spreadsheet (*.xlsx)
Search on Specific Index (example: S&P 500)
For current: choose from Jan 1964 to the current month.
First follow the instructions above, then limit the list to all S&P 500 constituents:
Step 1: Set the date range to start with Jan 1964
Step 2: Select TIC and enter i0003 (this is the S&P 500 ticker symbol)
- EIKON (Available at Barcelona and Madrid Libraries. Ask staff for password)
See FAQ: How can I access Eikon?
Search for the index on the main search box. Click the result to load the content. In addition, on the right, you will see the RIC (REUTERS INSTRUMENT CODE). You can use it on your future searches.
Go to the constituents section and click on Leavers and Joiners.
You can also use a shortcut in the command line. Use CA (Leavers and Joiners) after an index RIC to see the constituents section. Enter .SPX LJ in the command line to get to the Leavers and Jointers section of the S&P 500 Index.