@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.
@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.
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
@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.
Hi @william.carroll.20 ,
Actually, I can get the weight data of date 2020-06-01 properly (as in the screenshot below)
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.
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.
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!