Historical Index Constituents in Bloomberg
This post follows on from Historical Index Constituents (e.g. S&P 500) written in October 2014.
It is easy to find the current constituents for an equity index such as the FTSE 100 or S&P 500 but finding the historical members for a given date or range of dates requires more work. This blog post shows briefly how to use four screens in Bloomberg.
In the Bloomberg search bar, type ‘SECF<GO>’ (that is, SECF then the Enter key) to use the Security Finder to find the Equity Index you need. You could just type the name of the Index in the search box and see if the correct Index is suggested. In this example, click on SPX Index (S&P 500 Index). A menu will appear, choose Member Weightings MEMB.
You can get straight to the Member Weightings screen if you know the code of the index. Type, for example, ‘SPX <Index> MEMB <GO>’ (that is, SPX then the F10 key then MEMB then Enter). The date can be changed in the ‘As of’ box at the top-right and extra columns can be added. From here, on the red menu, click Output > Excel to export the data to a new Excel document (requires the Excel add-in to be configured, see desktop icon “Install Bloomberg Office Add-ins” at Manchester).
If you requested ISIN or CUSIP identifier numbers, these can be used in other databases such as Datastream, Compustat or Capital IQ.
Getting constituents for a range of dates
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.
You can do this and keep all the columns (name, weight, ISIN etc.) but the image above just keeps Ticker. 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).
Is there a way to do this more easily in Bloomberg? Well, yes and no. There is a screen in Bloomberg called Historical Index Member Changes CHNG.
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.
You will see a line in the report for each member’s entry and exit to the index, with more detail. But you cannot export the list of member tickers to Excel or do anything else systematic like that. It will be better to use the MEMB screen and export each month.
Do you know a better way? Please let us know by commenting on this post.
See also Historical FTSE100 Index Constituents on Datastream, dated July 2012.