Index constituents every month

rajanraju

How can I get index constituents every month? I am looking for the .HSLI index constituents every month end since 2015/1/30. I have used

df=ek.get_data('.HSLI', ['TR.IndexConstituentRIC'], {'SDate':'-50M'})[0]

With a loop from -50 to 0 so I get every month...

However, the data is there only for the last couple of years. On asking the support desk, they suggested Datastream would be a better source. I quote:

For historical constituents of <.HSLI> I would recommend to use Datastream excel add in formula to extract the historical constituents since it has more historical data.
The historical constituents of this index are available from March 2010 - April 2019 with the following codes below:

LHKHCOLC - Current constituent list of Hang Seng Large Cap index.
LHKHCOLC0419 - Latest constituent list of Hang Seng Large Cap index as of April 2019.
LHKHCOLC0310 - Earliest constituent list of Hang Seng Large Cap index as of March 2010.

Please note that the numerical figures in the code represents the month and year, so if you would like to get the constituent list as of January 2017, just add 0117 after the code LHKHCOLC so the code will be LHKHCOLC0117.

January 2015 =DSGRID("LHKHCOLC0115","NAME","Latest Value","","","RowHeader=true;ColHeader=true;DispSeriesDescription=true;DispDatatypeDescription=true","")
February 2015 =DSGRID("LHKHCOLC0215","NAME","Latest Value","","","RowHeader=true;ColHeader=true;DispSeriesDescription=true;DispDatatypeDescription=true","")

ENDQUOTE

Is it possible to query data stream using the python API? Appreciate your help.

Alex Putkov.1

@rajanraju
There are multiple ways to retrieve historical index constituents as of a given date using Eikon Data APIs. The following method requires you to know the portfolio code of the index, which is different from the index RIC and which you can only look up manually in the PORT app in Eikon.

ek.get_data('Index(HSLI,20150331)',['TR.PortfolioConstituentName'])

The following method is the one I personally prefer, however it does not work at the moment due to a known bug. The fix for this bug is expected to be deployed in production at the end of June.

ek.get_data('0#.HSLI(20150331)',['TR.RIC'])

For your purposes I think the most efficient way would be to reconstruct index constituents using the list of leavers and joiners. I.e. retrieve current constituents, retrieve the list of stocks that joined and left the index over your observation period, and reconstruct the constituents for each date in your series. Here's the code sample.

import pandas as pd
import datetime as dt
import dateutil.relativedelta
import eikon as ek
ek.set_app_key('your_app_key')
ic, err = ek.get_data('.HSLI', ['TR.IndexConstituentRIC'])
lj, err = ek.get_data('.HSLI', 
                      ['TR.IndexJLConstituentChangeDate',
                       'TR.IndexJLConstituentRIC.change',
                       'TR.IndexJLConstituentRIC'],
                     {'SDate':'0D', 'EDate':'-55M', 'IC':'B'})
lj['Date']=pd.to_datetime(lj['Date']).dt.date
lj.sort_values(['Date','Change'],ascending=False,inplace=True)
dates = [dt.date(2015,1,30)]
i = 0
while (dates[0] + dateutil.relativedelta.relativedelta(months=+i+1)) < dt.date.today():
    dates.append(dates[0] + dateutil.relativedelta.relativedelta(months=+i+1))
    i = i + 1
dates.append(dt.date.today())
df = pd.DataFrame(index=dates, columns=['Index Constituents'])
ic_list = ic['Constituent RIC'].tolist()
for i in range(len(dates)):
    print(str(dates[len(dates)-i-1]))
    df.at[dates[len(dates)-i-1],'Index Constituents'] = ic_list[:]
    for j in lj.index:
        if lj['Date'].loc[j] <= dates[len(dates)-i-1]:
            if lj['Date'].loc[j] > dates[len(dates)-i-2]:
                if lj['Change'].loc[j] == 'Joiner':
                    print('Removing ' + lj['Constituent RIC'].loc[j])
                    ic_list.remove(lj['Constituent RIC'].loc[j])
                elif lj['Change'].loc[j] == 'Leaver':
                    print('Adding ' + lj['Constituent RIC'].loc[j])
                    ic_list.append(lj['Constituent RIC'].loc[j])
            else:
                break
df