How can I access historical s&p500 weightings through the Python Eikon API?

william.carroll.20

Find more posts tagged with

python

eikon

historical

constituents

workspace

eikon-data-api

refinitiv-dataplatform-eikon

workspace-data-api

Accepted answers

jason.ramchandani01

@william.carroll.20 Please see the response on this thread unfortunately you will have to do this one date at a time. I hope this can help.

df_constituents, err = ek.get_data(['0#.SPX'],["TR.IndexConstituentWeightPercent.date","TR.IndexConstituentWeightPercent", {'SDate':'2020-10-04'})

df_constituents

I don't think the data for this index goes back before 2019 - if you have additional content queries please feel free to make a content query using Help -> Contact Us and specify content query. I hope this can help.

All comments

william.carroll.20

I am conducting a research project and would like to create a dataset of S&P500 weightings going back to 2000 with monthly data on the weights of each stock.

jason.ramchandani01

@william.carroll.20 Please see the response on this thread unfortunately you will have to do this one date at a time. I hope this can help.

df_constituents, err = ek.get_data(['0#.SPX'],["TR.IndexConstituentWeightPercent.date","TR.IndexConstituentWeightPercent", {'SDate':'2020-10-04'})

df_constituents

william.carroll.20

@jason.ramchandani01 thank you for the response. Sadly I am looking to get access to these weightings going back much further than 2019, ideally to 2000. But my subscription (through UCL) does not give me access to the helpdesks sadly - was informed about this when I asked for help.

I shall see what I can do.

jason.ramchandani01

@william.carroll.20 Ah OK I will try to find out and post something here later. You can always go via the librarian or person responsible for the Eikon's there to raise a content ticket.

raksina.samasiri

Hi @william.carroll.20 ,

I'd like to add that there is a reported issue at the moment affecting Historical data for Index Constituents, and Weightings for trade dates before January 2020 are temporarily unavailable. (if this issue is fixed, the data would ordinarily be available from 1998)

Our Product Team is working on the resolution. You can monitor the status of this Alert by opening your Eikon quote app and enter the code ALERT96.

for an alternative solution to get monthly data, you may generate a list of months you interested then call eikon to get data of each month in the list, please find an example below

1. to get a list of month (put the start date you interested in start_date variable)

import datetime

start_date = datetime.date(2020, 1, 1)
end_date = datetime.date.today()

year = start_date.year
month = start_date.month

monthly = []
while (year, month) <= (end_date.year, end_date.month):
    monthly.append(datetime.date(year, month, 1).isoformat())
    if month == 12:
        month = 1
        year += 1
    else:
        month +=

from the code above, the monthly list will look like below

2. then you can looping through the date in monthly list to get the data

dfs = {}
for date in monthly:
    df, err = ek.get_data('0#.SPX',
                   ['TR.IndexConstituentName.date','TR.IndexConstituentName',
                    'TR.IndexConstituentWeightPercent.date','TR.IndexConstituentWeightPercent'],
                   {'SDate': date})
    print(f'data of {date}')
    display(df)
    dfs[date] = df

hope this could help

william.carroll.20

@raksina.samasiri Thank you for the advice. That is a real shame about the data issue as would be unbelievably useful to have that weighting data accessible before my deadline in September. I don't even seem to be able to get the weightings any dates.

Could I perhaps manually calculate these weightings historically? I have a good idea of the weighting formula but the issue I run into is those stocks that were delisted don't have data available. I have created a CSV file of the SPX constituents going back to 2000 but when I try and request data on these stocks I get this issue of data availability for those that have been delisted. Any suggestions would be great.

raksina.samasiri

Hi @william.carroll.20 ,

Actually, I can get the weight data of date 2020-06-01 properly (as in the screenshot below)

The reason you see the field as <NA> may be related to your Eikon account entitlements. For S&P indices access to the list of constituents is included with base Eikon subscription, while access to weights is fee liable and requires additional entitlements. See if you can view constituent weights for .SPX in Eikon application's Constituents Analysis app. Try the Summary or Index Sector report in the Constituents Analysis app. If you see "-" in the Weights column, this indicates that your Eikon account is not entitled to this data.

For the calculation of weightings, could you please provide the formula you're going to use? Then I'll try to find a way to fetch the data needed.

william.carroll.20

Hi @raksina.samasiri

I definitely think you are right. As predicted the - is there in the weights column. This is the issue I am sadly experiencing using the university license.

For the calculation of the weighting, I am expecting to estimate them using Free Float * Price = Free float market cap. Then divide this by the market cap of the entire index, either directly or by summing these values across all constituents, finally multiplying by 100 to get the percentage weights.

raksina.samasiri

Hi @william.carroll.20 ,

Thank you for your patience, you can use Datastream to get a list of constituents of each month, then use Eikon to fetch the data from that list, please find an example below.

here's the Python code

import DatastreamDSWS as DSWS
import pandas as pd
import eikon as ek

# set eikon app key
ek.set_app_key('#### PUT YOUR APP KEY HERE ####')

# set datastream username and password
ds = DSWS.Datastream(username = "#### DSWS USERNAME ####", password = '#### DSWS PASSWORD ####')

# crate list of months and years to fetch a list of RICs on each month
mmyy_list = ['0100','0200','0300','0521']

# call Datastream to get RICs list
rics_list = {}
for mmyy in mmyy_list:
    dat = ds.get_data(tickers=f'LS&PCOMP{mmyy}|L', fields=['RIC'], kind=0)
    # save rics list in python dict
    rics_list[mmyy] = dat['Value'].tolist()

# call get data to get data required for weight calculation
monthly_data = {}
err = {}
for mmyy in rics_list:
    rics = rics_list[mmyy]
    fields = ['TR.PriceClose.date','TR.PriceClose','TR.FreeFloat']
    start_date = datetime.strptime(mmyy, '%m%y').strftime("%Y-%m-%d")
    monthly_data[start_date], err[start_date] = ek.get_data(rics, fields, {'SDate': start_date})
    # calculate weight from the folmula provided
    monthly_data[start_date]['Free Float * Price Close'] = monthly_data[start_date]['Free Float'] * monthly_data[start_date]['Price Close']
    sum_freefloat_market_cap = monthly_data[start_date]['Free Float * Price Close'].sum()
    monthly_data[start_date]['Weight %'] = monthly_data[start_date]['Free Float * Price Close'] / sum_freefloat_market_cap * 100

# to see data in each month
for monthly in monthly_data:
    print(monthly, monthly_data[monthly])

In case you don't have Datastream username and password, you may contact your account owner to provide it. You should already have a permission to use Datastream.

I hope this could help have a great day!

raksina.samasiri

Plus, If you'd like to get other fields or get the field's description, Data Item Browser or CodeCreator in the Eikon Desktop app can be used.

EXPLORE OUR SITES