Output.csv.zip

Greetings!

I am writing to seek your expertise in enhancing a sophisticated codebase designed to extract Reference Instrument Codes (RICs) from the three principal U.S. exchange markets: NASDAQ, NYSE, and NYSE American. This code efficiently retrieves both historical and current data, focusing on detailed information regarding bankruptcies, encompassing descriptions, announcement dates, effective dates, closing prices during the relevant periods, and deal synopses.

Upon utilization, I have observed discrepancies in the output presentation, particularly with the misalignment of the announcement and effective dates. Additionally, there appears to be a uniformity issue where each RIC is inaccurately displaying identical deal histories, irrespective of their unique event timelines.

I am aiming to resolve these issues to enhance analytical accuracy, particularly in tracking historical company delistings and assessing their post-restructuring performance. My specific inquiries are as follows:

1. What modifications are necessary to ensure the announcement and effective dates are accurately placed within the output?

2. How can I rectify the issue of identical event histories across different RICs to enable precise analysis of each company's unique events?

3. What improvements can be implemented to enhance the readability and functionality of the output table, especially considering future expansions to incorporate logic based on bankruptcy flags and closing price data?
   
   Your insights on these matters would be invaluable in refining the code's effectiveness and precision. I look forward to your guidance and am prepared to provide any additional information required.

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 '5048094456' and RCSAssetCategoryLeaf eq 'Ordinary Share')",
    select="RIC, DTSubjectName, RCSAssetCategoryLeaf, ExchangeName, ExchangeCountry"
)

df = results

substrings = ['.OQ', '.N', '.A', '.PK', '.PQ']

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()


START_DATE = "2020-04-30"
END_DATE = "2024-12-31"


df_history = rd.get_history(
    universe=rics,
    fields=[
        "TR.MnAHasBankruptcy",
        "TR.PPBankruptcyFilingDate",
        "TR.PPHasFiledForBankruptcy",
        "TR.IsDelistedQuote",
        "TR.PriceClose",
        "TR.MnAAnnDate",
        "TRADE_DATE",
        "TR.MNASynopsis",
        "TR.MnAStatus",
        "TR.MnADateUnconditional",
        "TR.RetireDate"
    ],
    parameters={
        'SDate': START_DATE,
        'EDate': END_DATE,
        'Frq': 'D'
    }
)

print(df_history)

df_history.to_csv("./Delisting_Case_Study/Merged_Delisting-Case.csv")

rd.close_session()