For a deeper look into our Eikon Data API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
1 0 0 1

Don't include rows if data is unavailable

Hello, I am trying to get some factor and price data for different companies that have ESG scores available as well. My code looks like:

df, err = ek.get_data("SCREEN(U(IN(Equity(active,public,primary))/*UNV:Public*/), IN(TR.ExchangeCountryCode,""SE""), TR.TRESGScore(Period=FY0)>0, CURN=USD)",
                      ["TR.PriceClose", "TR.PriceClose.Date", "TR.EBITMarginPercent", "TR.PretaxROAPercent", "TR.FreeOperatingCashFlowExclDividends","TR.RevenueSmartEstLastYrGrowth" , "TR.FreeCashFlow", "TR.WACCBeta"],
                      {'SDate': '2010-01-01', 'EDate': '2022-01-29','Frq':'M', 'SORTD': 'date'})

The only problem is that some companies don't have price data for all periods, but the empty periods are still included as NaN. Is there a easy way to make so that if there is no price data at date n, that whole row is not included.

Thanks in advance!

eikoneikon-data-apiapiscreening-apiprice-history
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Upvotes
Accepted
5.1k 8 5 8

hi @student210

To investigate this, RIC "BMAX.ST", which is one of the results of this screening query, was picked to check the output dataframe, please see it in detail below.

  1. There're rows with NaN and <NA> in the output dataframe (see screenshot 1 in the reply due to the limit of image per each comment)
  2. First, about the TR.PriceClose of BMAX.ST, its data's been available since its IPO date
    To remove the row before that, you may update the SDate and EDate Parameters or use the code below
    df3 = df3[df3['Date'] != '']
    1643605918991.png
  3. Next, to clarify how the output dataframe is returned, the date of each field is included as its parameter. It's shown that each row doesn't represent each date but fields are merged into the dataframe without joining each field's date
    1643606132572.png

So I'd like to suggest you do the step below

  1. get the list of RIC from the screener criteria
    df1, err = ek.get_data("SCREEN(U(IN(Equity(active,public,primary))/*UNV:Public*/), IN(TR.ExchangeCountryCode,""SE""), TR.TRESGScore(Period=FY0)>0, CURN=USD)", "TR.RIC")
    rics = df1["RIC"].to_list()
  2. then use the list of RICs above as input instruments to get data from each set of data that return the same date-set (you may check this using CodeCreator app in Eikon or ask content specialist via MyRefinitivto clarify the content)
    df2, err = ek.get_data(rics,
                         ["TR.PriceClose", "TR.PriceClose.Date"],
                         {'SDate': '2010-01-01', 'EDate': '2022-01-29','Frq':'M', 'SORTD': 'date'})
    df2
  3. use the dataframe from the previous step and join them together if needed

Hope this could help, please tell me in case you have any further questions


1643605918991.png (245.8 KiB)
1643606132572.png (91.4 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

screenshot of the dataframe mentioned in step 1)

category of each field can be checked using CodeCreator app in Eikon or Refinitiv workspace1643607936419.png

1643607936419.png (134.5 KiB)
1643605303675.png (60.3 KiB)