Date format

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

Find more posts tagged with

date

data

Download

Accepted answers

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)

All comments

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)

mahdiehvg

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 ?

mahdiehvg

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

EXPLORE OUR SITES