NaT Issues and Duplicated Dates

AGBC
AGBC Newcomer

I hope you are doing well. I am reaching out regarding some data inconsistencies I haveencountered while retrieving fundamental and market data for specific RICs using theRefinitiv API. I would appreciate your assistance in understanding these issues and findingpossible solutions.Issue 1: NaT Values for TR.FwdEVToEBITDA.calcdate for GH.OQI am using the Refinitiv API to retrieve daily forward EV/EBITDA (NTM) and other fundamentalmetrics for multiple RICs. However, when requesting the'TR.FwdEVToEBITDA(Period=NTM,SDate=2020-01-01,EDate=2022-01-01,Frq=D).calcdate' fieldfor GH.OQ, the returned values are all NaT (Not a Timestamp), while for other RICs, the field ispopulated correctly.Here is the request I am using:

import refinitiv.data as rdfrom refinitiv.data.content import fundamental_and_referencerd.open_session()prepared_metric_daily_codes_ev_ebitda = [ 'TR.FwdEVToEBITDA(Period=NTM,SDate=,EDate=,Frq=D)', 'TR.FwdPEG(Period=NTM,SDate=,EDate=,Frq=D)', 'TR.RecMean(SDate=,EDate=,Frq=D)', 'TR.NumOfBuy(SDate=,EDate=,Frq=D)', 'TR.NumOfSell(SDate=,EDate=,Frq=D)', 'TR.NumOfHold(SDate=,EDate=,Frq=D)', 'TR.NumOfStrongBuy(SDate=,EDate=,Frq=D)', 'TR.NumOfStrongSell(SDate=,EDate=,Frq=D)', 'TR.NumOfRecommendations(SDate=,EDate=,Frq=D)', 'TR.FwdEVToEBITDA(Period=NTM,SDate=,EDate=,Frq=D).calcdate']ticker = ['GH.OQ']response = fundamental_and_reference.Definition( ticker, prepared_metric_daily_codes_ev_ebitda,).get_data()

data = response.data.df

The 'TR.FwdEVToEBITDA.calcdate' field is returning NaT for GH.OQ but works fine for othertickers. Could you clarify why this is happening? Is this expected behavior due to missingcalculations for this specific RIC during the requested period, or is there another issue?

Issue 2: PETR3.SA Close Price Returning NaN and Duplicate DatesAdditionally, I have encountered another issue when retrieving Close Price, Price TargetMean, Enterprise Value (EV), and Market Capitalization for PETR3.SA.1. The Close Price (TR.CLOSEPRICE) field is returning only NaN values for PETR3.SAacross the entire requested period (2006-01-01 to 2025-02-18).2. The .date field (from TR.CompanyMarketCap.date) contains duplicate dates,specifically:○ 2012-08-17○ 2013-01-24Here is the request I am using:

import refinitiv.data as rdfrom refinitiv.data.content import fundamental_and_referencerd.open_session()daily_metrics = [ 'TR.CLOSEPRICE(SDate=2006-01-01,EDate=2025-02-18,Frq=D,Adjusted=1)', 'TR.PriceTargetMean(SDate=2006-01-01,EDate=2025-02-18,Frq=D)', 'TR.H.EV(SDate=2006-01-01,EDate=2025-02-18,Frq=D)', 'TR.CompanyMarketCap(SDate=2006-01-01,EDate=2025-02-18,Frq=D)', 'TR.CompanyMarketCap(SDate=2006-01-01,EDate=2025-02-18,Frq=D).date']ticker = ['PETR3.SA']response = fundamental_and_reference.Definition( ticker, daily_metrics,).get_data()data = response.data.dfduplicated_dates = data['Date'][data['Date'].duplicated(keep=False)].unique()print(duplicated_dates)['2012-08-17 00:00:00', '2013-01-24 00:00:00']

Pattern Affecting Multiple RICs and Other FieldsI have noticed that this issue is not limited to GH.OQ and PETR3.SA. This pattern of missingvalues (NaN) or duplicate timestamps also appears for other RICs when retrieving specificmetrics. In particular:● TR.FwdEVToEBITDA.calcdate is NaT for some tickers but works for others.● TR.ISOriginalAnnouncementDate also exhibits similar issues, with missing values orunexpected duplicates across different RICs.Could you clarify:● Why is the Close Price returning only NaN values for PETR3.SA? Is there a known issuewith this data?● Why are duplicate dates appearing in the .date field? Is this expected behavior, orshould I handle it differently?● Is there an underlying reason why some metrics (like TR.FwdEVToEBITDA.calcdateand TR.ISOriginalAnnouncementDate) return inconsistent values for specific RICs?● Are there recommended best practices to verify or mitigate these issues whenretrieving historical data?I appreciate your time and support in resolving these issues. Looking forward to yourguidance.

Answers

  • jonathan.legrand0
    edited February 21

    Hi @AGBC ,

    When it comes to your 1st question, I checked myself and saw that there is indeed no data for the RIC and Field you are after. Using hte DIB (Data Item Browser), I was able to find `TR.H.ForwardEVToSales` however; is this a suitable replacement?

    ld.get_history(
    universe="GH.OQ",
    fields=['TR.ClosePrice', 'TR.FwdEVToEBITDA', 'TR.H.ForwardEVToSales.calcdate', 'TR.H.ForwardEVToSales'],
    interval="1D",
    start = '2022-10-01',
    end = '2025-01-28')
    image.png

    If it isn't a suitable replacement, please reach out to myaccount.lseg.com so that they can investigate why this field is not populated for this RIC. (Indeed, this LSEG Developer Q&A Forum is for LSEG API technical questions only. For content questions such as yours, please use myaccount.lseg.com.)

    When it comes to issue 2, I want to thank you for sharing your code. It is very useful and helps us investigate. I was able to replicate your issue with this:

    daily_metrics = [
    'TR.CLOSEPRICE(SDate=2006-01-01,EDate=2025-02-18,Frq=D,Adjusted=1)',
    'TR.PriceTargetMean(SDate=2006-01-01,EDate=2025-02-18,Frq=D)',
    'TR.H.EV(SDate=2006-01-01,EDate=2025-02-18,Frq=D)',
    'TR.CompanyMarketCap(SDate=2006-01-01,EDate=2025-02-18,Frq=D)',
    'TR.CompanyMarketCap(SDate=2006-01-01,EDate=2025-02-18,Frq=D).date']
    ric = ['PETR3.SA']
    response = ld.content.fundamental_and_reference.Definition(
    universe=ric,
    fields=daily_metrics).get_data()
    data = response.data.df

    duplicated_dates = data['Date'][data['Date'].duplicated(keep=False)].unique()

    print(duplicated_dates) # ['2012-08-17 00:00:00', '2013-01-24 00:00:00']

    data.set_index('Date').loc[duplicated_dates]

    image.png

    As well as with

    data = ld.get_data(
    universe=['PETR3.SA'],
    fields=[
    'TR.CLOSEPRICE(SDate=2006-01-01,EDate=2025-02-18,Frq=D,Adjusted=1)',
    'TR.PriceTargetMean(SDate=2006-01-01,EDate=2025-02-18,Frq=D)',
    'TR.H.EV(SDate=2006-01-01,EDate=2025-02-18,Frq=D)',
    'TR.CompanyMarketCap(SDate=2006-01-01,EDate=2025-02-18,Frq=D)',
    'TR.CompanyMarketCap(SDate=2006-01-01,EDate=2025-02-18,Frq=D).date'])

    duplicated_dates = data['Date'][data['Date'].duplicated(keep=False)].unique()

    print(duplicated_dates) # ['2012-08-17 00:00:00', '2013-01-24 00:00:00']

    data.set_index('Date').loc[duplicated_dates]
    image.png

    As well as with

    data = ld.get_history(
    universe=['PETR3.SA'],
    fields=[
    'TR.CLOSEPRICE',
    'TR.PriceTargetMean',
    'TR.H.EV',
    'TR.CompanyMarketCap'],
    interval="1D",
    start = '2006-01-02',
    end = '2025-02-18')

    duplicated_dates = data.index[data.index.duplicated(keep=False)].unique()

    print(duplicated_dates) # ['2012-08-17 00:00:00', '2013-01-24 00:00:00']

    data.loc[duplicated_dates]

    image.png

    I reached out to an internal team to investigate and find out if this is an issue with the LSEG Data Library for Python (the Python LDL) or the backend (i.e.: the data feeding hte Python LDL). I will revert as soon as I have an update.