Python code for 3K ISIN

UdayP_K45

What I want is to download the data for 3K ISINs. That can be done using Python.if I use python I don't get auto-imputed data.

Below is the Python code client is refering

import refinitiv.data as rdfrom tqdm import tqdmfor isin in tqdm(isins):try:response = rd.get_history(universe=[isin.strip()],fields=["TR.DividendYield","TR.F.ComStockBuybackNet","TR.NumberofSharesOutstandingActual","TR.NetDebtToEBITDA","TR.FreeCashFlow","TR.SharesOutstanding","TR.F.PriceToCFPerShr","TR.PCReturnOnInvestedCapPct","TR.WACC","TR.CompanyMarketCap","AVAIL(TR.F.IntrCovRatioTTM(AlignType=PeriodEndDate),TR.F.IntrCovRatio(AlignType=PeriodEndDate, Period=FY0))","AVAIL(TR.DPSMean( AlignType=PeriodEndDate, Methodology=InterimSum, Period=NTM),TR.DPSMean(AlignType=PeriodEndDate, Methodology=WeightedAnnual, Period=NTM))","TR.F.DPSComGrossIssue5YrCAGR(AlignType=PeriodEndDate, InstrumentType=Primary)","AVAIL(TR.F.EVToLeveredFOCFTTM(AlignType=PeriodEndDate),TR.F.EVToLeveredFOCF(AlignType=PeriodEndDate, Period=FY0))","AVAIL(TR.F.FOCFYldPctTTM(AlignType=PeriodEndDate),TR.F.FOCFYldPct(AlignType=PeriodEndDate, Period=FY0))","TR.FreeFloatPct","TR.Volume",],start="2015-05-29",end="2025-06-03",interval = "monthly")response.to_csv(f"historic_data/historic_refinitv_data_{isin}.csv")except Exception as ex:print(ex)with open("historic_data/errores.csv", "a") as file:file.write(f"{isin}\n")

Gurpreet

@UdayP_K45

Please see the Data Limits guideline document on the dev portal, and implement the recommendations.

You can also see the example usage of get_history API call in the code samples. The fields being used are for get_data and not available for historical data.

Uday_PK

@Gurpreet Client has follow up

I believe there seems to be a misunderstanding. We aim to obtain 10 years of 19 characteristics from 3,000 companies (some of which we know support historical series and some do not):

 

• Supports historical series:
  • "TR.DividendYield"
  • "TR.F.ComStockBuybackNet"
  • "TR.NumberofSharesOutstandingActual"
  • "TR.NetDebtToEBITDA"
  • "TR.FreeCashFlow"
  • "TR.SharesOutstanding"
  • "TR.F.PriceToCFPerShr"
  • "TR.PCReturnOnInvestedCapPct"
  • "TR.WACC"
  • "TR.CompanyMarketCap"
  • "AVAIL(TR.F.IntrCovRatioTTM(AlignType=PeriodEndDate),TR.F.IntrCovRatio(AlignType=PeriodEndDate, Period=FY0))"
  • "AVAIL(TR.DPSMean(AlignType=PeriodEndDate, Methodology=InterimSum, Period=NTM),TR.DPSMean(AlignType=PeriodEndDate, Methodology=WeightedAnnual, Period=NTM))"
  • "TR.F.DPSComGrossIssue5YrCAGR(AlignType=PeriodEndDate, InstrumentType=Primary)"
  • "AVAIL(TR.F.EVToLeveredFOCFTTM(AlignType=PeriodEndDate),TR.F.EVToLeveredFOCF(AlignType=PeriodEndDate, Period=FY0))"
  • "AVAIL(TR.F.FOCFYldPctTTM(AlignType=PeriodEndDate),TR.F.FOCFYldPct(AlignType=PeriodEndDate, Period=FY0))"
  • "TR.FreeFloatPct"
  • "TR.Volume"
• Does not support historical series:
  • "TR.HQCountryCode"
  • "TR.GICSSector"

 

We have two methods to mass obtain this data:

Using Excel:

=@RDP .Data($B$7:$B$40;D$1:V$1;"Sdate=#1 EDate=#2 Frq=CM Curn:USD RH:Calcdate Null=BLANK CH:FD,IN";C2;$B$4;$B$5)

 

Using Python:

import refinitiv.data as rd from tqdm import tqdm   for isin in tqdm(isins):     try:         response = rd.get_history(             universe=[isin.strip()],             fields=[                 "TR.DividendYield",                 "TR.F.ComStockBuybackNet",                 "TR.NumberofSharesOutstandingActual",                 "TR.NetDebtToEBITDA",                 "TR.FreeCashFlow",                 "TR.SharesOutstanding",                 "TR.F.PriceToCFPerShr",                 "TR.PCReturnOnInvestedCapPct",                 "TR.WACC",                 "TR.CompanyMarketCap",                 "AVAIL(TR.F.IntrCovRatioTTM(AlignType=PeriodEndDate),TR.F.IntrCovRatio(AlignType=PeriodEndDate, Period=FY0))",                 "AVAIL(TR.DPSMean( AlignType=PeriodEndDate, Methodology=InterimSum, Period=NTM),TR.DPSMean(AlignType=PeriodEndDate, Methodology=WeightedAnnual, Period=NTM))",                 "TR.F.DPSComGrossIssue5YrCAGR(AlignType=PeriodEndDate, InstrumentType=Primary)",                 "AVAIL(TR.F.EVToLeveredFOCFTTM(AlignType=PeriodEndDate),TR.F.EVToLeveredFOCF(AlignType=PeriodEndDate, Period=FY0))",                 "AVAIL(TR.F.FOCFYldPctTTM(AlignType=PeriodEndDate),TR.F.FOCFYldPct(AlignType=PeriodEndDate, Period=FY0))",                 "TR.FreeFloatPct",                 "TR.Volume",             ],             start="2015-05-29",             end="2025-06-03",             interval="monthly"         )         response.to_csv(f"historic_data/historic_refinitv_data_{isin}.csv")     except Exception as ex:         print(ex)         with open("historic_data/errores.csv", "a") as file:             file.write(f"{isin}\n")

Client

• A way to download the data using Python with auto-imputing of the data as done in Excel.

Jirapongse

@Uday_PK

What does the client mean by "auto-imputing of the data"?

Python doesn't support the RH, Null, and CH parameter used in Excel.

RH:Calcdate Null=BLANK CH:FD,IN

The library returns a pandas dataframe. Typically, the client can use the dataframe's functions to manipulate the dataframe.

pventura

Hi @Jirapongse ,

Thank you for clarifying the use of parameters in Excel and Python. The formula we are using in Excel, as your colleague mentioned earlier, is the following:

=@RDP  .Data($B$7:$B$40;D$1:V$1;"Sdate=#1 EDate=#2 Frq=CM Curn:USD RH:Calcdate Null=BLANK CH:FD,IN";C2;$B$4;$B$5)

The code we use in Python is as follows:

import refinitiv.data as rdfrom tqdm import tqdmfor isin in tqdm(isins):    try:        response = rd.get_history(            universe=[isin.strip()],            fields=[                "TR.DividendYield",                "TR.F.ComStockBuybackNet",                "TR.NumberofSharesOutstandingActual",                "TR.NetDebtToEBITDA",                "TR.FreeCashFlow",                "TR.SharesOutstanding",                "TR.F.PriceToCFPerShr",                "TR.PCReturnOnInvestedCapPct",                "TR.WACC",                "TR.CompanyMarketCap",                "AVAIL(TR.F.IntrCovRatioTTM(AlignType=PeriodEndDate),TR.F.IntrCovRatio(AlignType=PeriodEndDate, Period=FY0))",                "AVAIL(TR.DPSMean( AlignType=PeriodEndDate, Methodology=InterimSum, Period=NTM),TR.DPSMean(AlignType=PeriodEndDate, Methodology=WeightedAnnual, Period=NTM))",                "TR.F.DPSComGrossIssue5YrCAGR(AlignType=PeriodEndDate, InstrumentType=Primary)",                "AVAIL(TR.F.EVToLeveredFOCFTTM(AlignType=PeriodEndDate),TR.F.EVToLeveredFOCF(AlignType=PeriodEndDate, Period=FY0))",                "AVAIL(TR.F.FOCFYldPctTTM(AlignType=PeriodEndDate),TR.F.FOCFYldPct(AlignType=PeriodEndDate, Period=FY0))",                "TR.FreeFloatPct",                "TR.Volume",            ],            start="2015-05-29",            end="2025-06-03",            interval="monthly"        )        response.to_csv(f"historic_data/historic_refinitv_data_{isin}.csv")    except Exception as ex:        print(ex)        with open("historic_data/errores.csv", "a") as file:            file.write(f"{isin}\n")

For example, in the case of ISIN CH0010570767 (please refer to the image below, Python on the left side and Excel on the right side), we obtain the following result and, setting aside the discrepancy in the data (which we are resolving separately), the behavior of both extraction methods differs:

• Indexes (dates) differ as you can see.
• Excel auto-imputes missing values (for “NumberofSharesOutstandingActual” it uses forward-fill method), but Python does not, it leaves the blanks.

So we have to handle the missing data ourselves or provide an unknown (if it exists) parameter to the function `get_history`, we rather use this last option.

Our goal is to obtain every company's data (3k companies by 19 features) for a 10 year period with the missing-values already filled using Python.

Best regards,
Pedro Ventura

Jirapongse

The client should use the rd.get_data method instead of rd.get_history. For example:

response = rd.get_data(
            universe=['CH0010570767'],
            fields=[
                "TR.DividendYield.Calcdate",
                "TR.DividendYield",
                "TR.F.ComStockBuybackNet",
                "TR.NumberofSharesOutstandingActual",
                "TR.NetDebtToEBITDA",
                "TR.FreeCashFlow",
                "TR.SharesOutstanding",
                "TR.F.PriceToCFPerShr",
                "TR.PCReturnOnInvestedCapPct",
                "TR.WACC",
                "TR.CompanyMarketCap",
                "AVAIL(TR.F.IntrCovRatioTTM(AlignType=PeriodEndDate),TR.F.IntrCovRatio(AlignType=PeriodEndDate, Period=FY0))",
                "AVAIL(TR.DPSMean( AlignType=PeriodEndDate, Methodology=InterimSum, Period=NTM),TR.DPSMean(AlignType=PeriodEndDate, Methodology=WeightedAnnual, Period=NTM))",
                "TR.F.DPSComGrossIssue5YrCAGR(AlignType=PeriodEndDate, InstrumentType=Primary)",
                "AVAIL(TR.F.EVToLeveredFOCFTTM(AlignType=PeriodEndDate),TR.F.EVToLeveredFOCF(AlignType=PeriodEndDate, Period=FY0))",
                "AVAIL(TR.F.FOCFYldPctTTM(AlignType=PeriodEndDate),TR.F.FOCFYldPct(AlignType=PeriodEndDate, Period=FY0))",
                "TR.FreeFloatPct",
                "TR.Volume",
            ],
            parameters={'SDate':'2015-05-29','EDate':'2025-06-03','Frq':'CM','Curn':'USD'}
        )
response

pventura

Hi @Jirapongse ,
This is exactly what we needed — thank you very much.

Would you mind pointing me to the documentation where all the fields and their properties are listed? For instance, I wasn’t aware that "TR.DividendYield" has a "Calcdate" property. Is this documented somewhere?

Thanks again,
Pedro Ventura

Jirapongse

You can use the Formula Builder in the LSEG Workspace Excel or the Data Item Browser tool.