Date format

Options

Hi,

I was wondering which date format is correct when downloading S&P 500 constituents for 2nd of January 2002. The following are the two alternatives, I was wondering which one is the correct one:

sp2002, err = ek.get_data('0#.SPX(20020201)',

'TR.IndexConstituentRIC',

)

Or,

sp2002, err = ek.get_data('0#.SPX(20020102)',

'TR.IndexConstituentRIC',

)

Best,

Mahdieh

Tagged:

Welcome!

It looks like you're new here. Sign in or register to get started.

Best Answer

  • pf
    pf LSEG
    Answer ✓

    Hi @mahdiehvg,

    Standard string date format is "YYYY-MM-DD" (see https://www.w3.org/TR/NOTE-datetime)

    About your code, the right way to request index constituents on a specific date is:

    sp2002, err = ek.get_data('0#.SPX',
    'TR.RIC',
    {'SDate': '2002-01-02', 'EDate': '2002-01-02'})

    Since 2002, for some constituents, the ric name changed (ex: in 2019, BBT.N merged with SunTrust and was renamed TFC.N)

Answers

  • Thanks for the reply.

    I have downloaded the S&P 500 constituent list for 02.01.2002 from Refinitiv Workplace. I also have downloaded the same list for the same date using the code I wrote above and the code you wrote in your reply. I merged the constituent list from Refinitiv Workplace with the list I obtained with my code and in another attempt I merged the list from Refinitiv Workplace with the list I got with your code, I merged using PermID.

    The difference is huge. With your code the matching is even weeker (only 221 firms were matched). Any suggestions or comments on this? Honestly I don't know which one is the right constituent list!

  • I don' know how ADC server manages "0#SPX(20020102)" instrument but you're right, when I run following code:

    sp2002, err = ek.get_data('0#.SPX',
    ['TR.RIC'],
    {'SDate': '2002-01-02', 'EDate': '2002-01-02'})

    I retrieve 503 instruments and all are mapped with a RIC which could be different than instrument (because of rename/merge/...) but make sense.

    Lastly, if run this code to retrieve current S&P constituents:

    sp2002, err = ek.get_data('0#.SPX', ['TR.RIC'])

    I still retrieving same 503 rics, so {'SDate': '2002-01-02', 'EDate': '2002-01-02'} is used to instrument get ric name on the 2002/01/02. And I suppose that instruments without Date means it wasn't yet in the S&P index the 2nd of January 2002 :

        Instrument                  Date       RIC
    0 POOL.OQ POOL.OQ
    1 CHRW.OQ CHRW.OQ
    2 AJG.N 1990-03-23T00:00:00Z AJG.N
    3 CNP.N CNP.N
    4 AMCR.N AMCR.N
    5 WM.N 1998-07-17T00:00:00Z WMI.N
    6 BA.N 1990-03-23T00:00:00Z BA.N
    7 FOX.OQ FOX.OQ
    8 LIN.N LIN.N
    9 WY.N 1990-03-23T00:00:00Z WY.N
    10 MCD.N 1990-03-23T00:00:00Z MCD.N


    Now, when I run your code:

    sp2002, err = ek.get_data('0#.SPX(20020102)', ['TR.IndexConstituentRIC'])

    I get only 498 instruments... but with a lot of unknown items without RIC value:

           Instrument                  Date Constituent RIC
    0           MMM.N  2022-08-10T00:00:00Z           MMM.N
    1       TNB.N^E12                                      
    2           ABT.N  2022-08-10T00:00:00Z           ABT.N
    3         ADBE.OQ  2022-08-10T00:00:00Z         ADBE.OQ
    4          ADP.OQ  2022-08-10T00:00:00Z          ADP.OQ
    5          AEP.OQ  2022-08-10T00:00:00Z          AEP.OQ
    6           AES.N  2022-08-10T00:00:00Z           AES.N
    7       AET.N^K18                                      
    8           AFL.N  2022-08-10T00:00:00Z           AFL.N
    9             A.N  2022-08-10T00:00:00Z             A.N
    10          AIG.N  2022-08-10T00:00:00Z           AIG.N
    11          APD.N  2022-08-10T00:00:00Z           APD.N
    12      ACV.N^K06                                      
    13      ABS.N^F06                                      
    14       AL.N^K07                                      
    ...

    It seems that all instrument with "^Xyz" suffix are matching with delisted shares from S&P, so your code appears to be the right one to get S&P constituents in the past.


    Could you share more details on the way you downloaded the S&P 500 from Refinitiv Workspace ?

  • Of course. The steps to download S&P 500 constituent list from the Workspace is as follows:


    1. open the Screener app

    2. on the left-hand-side to choose a list click on the Edit box of "Include: Indices, Lists, Portfolios, Stocks"

    3. In the new Include window, on the right-hand-side of the search box for "Lists:" click on the 3 line box (a new window opens)

    4. maximize the new window

    5. choose "All Indices" from the left-hand-side menu

    5. in the new search box, "Search for portfolios, lists or indices", type S&P 500

    6. Find and select the S&P 500 with USD currency in the search result list

    7. Click on the magnifier sign on the right-hand-side of the selected S&P 500

    8. here you can find the S&P 500 constituents list for all dates in daily frequency and back to January 1980


    Lest me know if I can help more.


    Mahdieh


Welcome!

It looks like you're new here. Sign in or register to get started.

Welcome!

It looks like you're new here. Sign in or register to get started.