Refinitiv.data proper fields names

vitali

Greetings,

I am currently involved in a project aimed at retrieving the closing price for companies that have been delisted due to bankruptcy, specifically the day following their bankruptcy announcement. The primary methodology I employ involves checking whether 'TR.MnAHasBankruptcy' is true. If this condition is met, we proceed to use 'TR.MnAHasBankruptcy.date' to extract the closing price on the subsequent day, utilizing "TR.PriceCloseDate".

I have encountered a challenge in executing the code intended for this purpose. For reference, the dataframe columns available for extraction are as follows: Index(['Instrument', 'Delisted Quote Flag', 'RetireDate', 'Bankruptcy Flag', 'Date', 'Acquisition Techniques', 'Date Effective or Unconditional', 'Price Close', 'Date'], dtype='object'). It is noteworthy that both 'TR.MnAHasBankruptcy.date' and "TR.PriceCloseDate" correspond to the same field name "Date".

I am seeking a solution to address this ambiguity between field names. What strategies could potentially resolve this issue?

Thank you!

import refinitiv.data as rd
import pandas as pd

rd.open_session()
results = rd.discovery.search(
    view=rd.discovery.Views.EQUITY_QUOTES,
    top=500,
    filter="(IssuerOAPermID eq '4295899979' and RCSAssetCategoryLeaf eq 'Ordinary Share')",
    select="RIC, DTSubjectName, RCSAssetCategoryLeaf, ExchangeName, ExchangeCountry"
)

df = results

substrings = ['.OQ', '.O', '.N', '.A']

def is_valid_ric(ric):
    for ending in substrings:
        if ending in ric:
            pos = ric.find(ending)
            if len(ric) == pos + len(ending) or (len(ric) > pos + len(ending) and ric[pos + len(ending)] == '^'):
                return True
    return False

filtered_df = df[df['RIC'].apply(is_valid_ric)]

rics = filtered_df['RIC'].tolist()

df = rd.get_data(
    universe=rics,
    fields=[
        'TR.IsDelistedQuote',
        'DELIST_DAT',
        'TR.RetireDate',
        'TR.MnAHasBankruptcy',
        'TR.MnAHasBankruptcy.date',
        "TR.MnAAcquisitionTechnique(Concat='|')",
        'TR.MnADateUnconditional',
        "TR.PriceClose",
        "TR.PriceCloseDate",
    ],
    parameters={}
)

# Filtering DataFrame where 'TR.MnAHasBankruptcy' is True
bankruptcy_df = df[df['Bankruptcy Flag'] == True].copy()

# Adding a new column for the target date, which is one day after 'TR.MnAHasBankruptcy.date'
bankruptcy_df['TargetDate'] = pd.to_datetime(bankruptcy_df['TR.MnAHasBankruptcy.date']) + pd.Timedelta(days=1)

# Prepare a DataFrame to hold the final results
final_columns = ['RIC', 'TR.MnAHasBankruptcy.date', 'TargetDate',  'Price Close']
final_df = pd.DataFrame(columns=final_columns)

# Iterate through the bankruptcy DataFrame
for index, row in bankruptcy_df.iterrows():
    target_date = row['TargetDate']
    ric = row['RIC']
    # Attempt to find the 'TR.PriceClose' where 'TR.PriceCloseDate' matches 'TargetDate'
    price_close_row = df[(df['RIC'] == ric) & (pd.to_datetime(df['TR.PriceCloseDate']) == target_date)]
    if not price_close_row.empty:
        price_close = price_close_row.iloc[0]['Price Close']
        final_df = final_df.append({
            'RIC': ric,
            'TR.MnAHasBankruptcy.date': row['TR.MnAHasBankruptcy.date'],
            'TargetDate': target_date,
            'Price Close': price_close
        }, ignore_index=True)
    else:
        # Handle the case where no matching 'TR.PriceClose' is found
        final_df = final_df.append({
            'RIC': ric,
            'TR.MnAHasBankruptcy.date': row['TR.MnAHasBankruptcy.date'],
            'TargetDate': target_date,
            'Price Close': "<NA>"
        }, ignore_index=True)

print(final_df)
final_df.to_csv("./bankruptcy_price_close.csv")

rd.close_session()

jonathan.legrand

Hi @vitali ,

If I understand your request properly, you would simply just like to diferenciate between the two 'Date' columns? Do let me know if I missunderstood.

There are 2 ways that come to mind. The 1st is to use the `use_field_names_in_headers` argument:

df = rd.get_data(
    universe=rics,
    fields=[
        'TR.IsDelistedQuote',
        'DELIST_DAT',
        'TR.RetireDate',
        'TR.MnAHasBankruptcy',
        'TR.MnAHasBankruptcy.date',
        "TR.MnAAcquisitionTechnique(Concat='|')",
        'TR.MnADateUnconditional',
        "TR.PriceClose",
        "TR.PriceCloseDate",
    ],
    parameters={},
    use_field_names_in_headers=True)

The 2nd is to rename the columns with

for i in range(len(my_list)):
    if my_list[i] == 'Date':
        my_list[i] = replacement_values[count]
        count += 1
        if count == len(replacement_values):
            break

It's rather ugly but it does the trick:

import refinitiv.data as rd
import pandas as pd

rd.open_session()
results = rd.discovery.search(
    view=rd.discovery.Views.EQUITY_QUOTES,
    top=500,
    filter="(IssuerOAPermID eq '4295899979' and RCSAssetCategoryLeaf eq 'Ordinary Share')",
    select="RIC, DTSubjectName, RCSAssetCategoryLeaf, ExchangeName, ExchangeCountry"
)

df = results

substrings = ['.OQ', '.O', '.N', '.A']

def is_valid_ric(ric):
    for ending in substrings:
        if ending in ric:
            pos = ric.find(ending)
            if len(ric) == pos + len(ending) or (len(ric) > pos + len(ending) and ric[pos + len(ending)] == '^'):
                return True
    return False

filtered_df = df[df['RIC'].apply(is_valid_ric)]

rics = filtered_df['RIC'].tolist()

df = rd.get_data(
    universe=rics,
    fields=[
        'TR.IsDelistedQuote',
        'DELIST_DAT',
        'TR.RetireDate',
        'TR.MnAHasBankruptcy',
        'TR.MnAHasBankruptcy.date',
        "TR.MnAAcquisitionTechnique(Concat='|')",
        'TR.MnADateUnconditional',
        "TR.PriceClose",
        "TR.PriceCloseDate",
    ],
    parameters={},)

my_list = df.columns.to_list()

replacement_values = ["MnAHasBankruptcyDate", "PriceCloseDate"]
count = 0

for i in range(len(my_list)):
    if my_list[i] == 'Date':
        my_list[i] = replacement_values[count]
        count += 1
        if count == len(replacement_values):
            break

df.columns = my_list

df