How do i retrieve the OHLCVm marketcap and price to book of all S&P500 stocks from 2010 to 2025 in a single call.
Is there a code for using ek.get_data
Hi @ken03 ,
I am afraid you can't ingest the information you are after via a single call.
One option is to use the following code by changing the date in a loop:
const_df, err = ek.get_data(instruments=["0#.SPX(20200101)"], fields=["TR.PriceClose.date", "TR.PriceClose","TR.PriceOpen", "TR.PriceHigh", "TR.PriceLow" ], parameters={"SDATE":"2020-01-01", "EDATE":"2020-01-01"} )const_df
A more effective approach though is:
1. request the initial constituent list as of the first day as shown above:
2. Request the historical joiners and leavers from that point on and then request the prices for those days only after constructing the SPX constituents for the change day. The joiner/leaver call is below:
const_changes, err = ek.get_data(instruments=".SPX", fields = ["TR.IndexJLConstituentRIC", "TR.IndexJLConstituentRIC.date", "TR.IndexJLConstituentRIC.instrument", "TR.IndexJLConstituentRIC.change","TR.IndexJLConstituentName"], parameters={"SDATE":"2010-01-01","EDATE":"2024-03-12", 'IC':'B'} )const_changes
Hope this helps.
Best regards,
Haykaz
Hi aramyan,
what do you mean by changing the date in a loop?
2. Request the historical joiners and leavers from that point on and then request the prices for those days only after constructing the SPX constituents for the change day. What do you mean by this and how do i execute it?
Thanks
Ken
Hi Ken,
1. What I mean you would need to make a separate request for each day you want the constituents.
2. Let's say you have the constituents with the first code as of "2020-01-01". Then you can use the second code to get index Joiners and leavers from that day onwards. This will give you the joiner/leaver RIC along with the change date. Then for each change date you can construct the SPX constituents yourself (as you have the original constituents and the joiners/leavers for that date). Finally you can get the prices for that specific date and constituents by using ek.get_data.
Hope this clarifies the workflow.
so if i need the data for across a time period say for 3 years, how would i implement the above? one date at a time?
since the constituents doesn't change every day, you can get the constituents (along with the market cap and P/B) per the change date. See an example with one date below:
const_df, err = ek.get_data(instruments=["0#.SPX(20200101)"], fields=["TR.CompanyMarketCap.date", "TR.CompanyMarketCap", "TR.PtoBPSMeanEst"], parameters={"SDATE":"2020-01-01", "EDATE":"2020-01-01"} )const_df
Hi Aramyan,
for those stocks which have been delisted are you still able to pull its data via the ric?
if the specific data is available, you will. For delisted instruments you would have RICs followed by ^ symbol and delisting date information. Please refer to ABMD.OQ^L22 RIC as an example shown in the screenshot above. As you see when requesting the constituents historically you will have the delisted RIC for a delisted instrument in the list allowing to pull the information directly.
