How to download historical transaction values for each constituent of the S&P 500 composite?

Question

I could not download the historical data of constituent companies of S&P 500. Here is my codes.

import lseg.data as ld

import pandas as pd

ld.open_session(key)

target_date = ["2023-03-01", '2023-03-10', '2023-03-20', '2023-03-31']

fields = ['TR.CommonName','TR.Turnover','TR.CompanyMarketCap]

f = pd.ExcelWriter('sp500comp.xlsx')

for i in range(3):

    df = ld.get_data(universe='LS&PCOMP0323', fields=fields, parameters={"SDate": target_date[i], "EDate": target_date[i+1]})

   df.to_excel(f, i)

f.close()

ld.close_session()

ujjawal_khandelwal · Answer

@linfin301,

If you want to find the relevant RIC for the Datastream Mnemonic, you can use following code:

import DatastreamPy as dsws

ds = dsws.DataClient(

        username='', # username typically starts with Z

        password='',

)

ds.get_data(tickers = 'S&PCOMP', fields= ['RIC'], kind= 0)

Then, find the required info from LSEG Data Library. The info is also available in Datastream too, but you will need to first expand the constituent list and then run iterative calls to get the data or you can use a bundle function which can help you increase the limit of a single call to 500 items.

Detailed document here: 621665.pdf

Please let me know if this helps :)

Thank you!

Jirapongse · Answer

@linfin301

Thank you for reaching out to us.

You can't use the Datastream codes with the LSEG Data Library for Python. The datastream codes must be used with the Datastream Webservice.

import DatastreamPy as DSWS

…

ds = DSWS.DataClient(None, username = username, password = password)

…

df = ds.get_data(tickers="LS&PCOMP0323|L",
            fields=["NAME","MV","VO"], kind=0)
df.pivot(index='Instrument', columns='Datatype', values = 'Value')

Typically, the LSEG Data Library for Python requires RICs. You can use the RIC Search to search RICs.

I think the RIC for the S&P 500 composite is .INX.

Therefore the code looks like this:

df =ld.get_data(
        universe = ['.INX'],
        fields = ['TR.IndexConstituentRIC'],
        parameters = {'SDate':'2023-03-01','EDate':'2023-03-01'})

df1 = ld.get_data(
        universe = df['Constituent RIC'].to_list(),
        fields = ['TR.CommonName','TR.Turnover','TR.CompanyMarketCap'],
        parameters = {'SDate':'2023-03-01'})

df1

wasin.w V3 · Answer

Hello @linfin301

What is the error message or issue? I have tried to replicate the issue on my end with the Data Library for Python (Workspace Session). The library and the Workspace cannot find LS&PCOMP0323 RIC information. Where did you get this LS&PCOMP0323 RIC?

However, the code works fine when I changed the universe parameter to other RICs.

Example:

for i in range(3):

    df = ld.get_data(universe=['NVDA.O','GOOG.O'], fields=fields, parameters={"SDate": target_date[i], "EDate": target_date[i+1]})

    print(df)

EXPLORE OUR SITES