Home > Business Databases > Historical Index Constituents in Bloomberg

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.

Bloomberg screen SECF

Use Security Finder SECF to find the code for the Equity Index. Choose Category: Index/Stats and Sub-Category: Equity Index. You can type part of the name or description to further filter the list.

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.

Bloomberg screen MEMB

For an Index, the MEMB screen shows constituent members (and weightings) for a given date. You can add extra Fields such as ISIN or CUSIP identifiers to use later.

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).

Bloomberg Export MEMB to Excel

Open the output file, accept a warning message about file types.

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.

Bloomberg output without duplicates

(1) Export each month’s constituents. (2) Copy and paste the Tickers into one long column for all months (without copying the heading ‘Ticker’ each time). (3) Select that column and choose Data > Remove Duplicates.

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.

Bloomberg screen CHNG

Historical Index Member Changes for a given Index and date range. 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.

Bloomberg screen RPT

Retrieve reports generated in Bloomberg. View these on screen or save one page at a time to an image or printer.

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.

Advertisements
Categories: Business Databases Tags:
  1. Phil Reed
    13 April 2016 at 11:21 am

    A further note: there is a Bloomberg API Excel formula to obtain the current constituents of an index (up to 2,500 members).
    =BDS(index_id, "INDX_MEMBERS") where index_id is something like “RIY Index” or a cell reference to such. The INDX_MEMBERS field does not work with historical data series requests.

  1. 12 April 2016 at 3:15 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s