question

Upvotes
Accepted
3 1 1 3

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

eikoneikon-data-apipythonworkspacerefinitiv-dataplatform-eikonworkspace-data-apihistoricalconstituents
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
Upvotes
Accepted
11k 22 6 9

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

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



icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@jason.ramchandani 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.

@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.
Upvotes
15.5k 32 5 10

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.
1629273872004.png


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 below1629274337281.png

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


1629273872004.png (33.0 KiB)
1629274337281.png (8.6 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
3 1 1 3

@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. python-issue.png

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.


python-issue.png (222.5 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
15.5k 32 5 10

Hi @william.carroll.20 ,

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

1629719311798.png

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


1629719311798.png (57.2 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.


Upvote
15.5k 32 5 10

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.

1629998393882.pnghere'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!


1629998393882.png (93.3 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.