Date format

mahdiehvg
mahdiehvg Newcomer
in Eikon Data APIs

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:

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

  • mahdiehvg
    mahdiehvg Newcomer

    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!

  • pf
    pf LSEG

    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 ?

  • mahdiehvg
    mahdiehvg Newcomer

    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


Categories