rd.get_history and rd.get_data comparison

vitali · April 10

Greetings,

I am in the process of developing an algorithm designed to extract specific dates and associated closing prices related to delistings, bankruptcies, or private transitions of publicly traded entities. Currently, I am encountering difficulties with the functionalities of rd.get_history and rd.get_data.

Below, I will present the complete code for your review. Following that, I will elaborate on the issues I am facing in greater detail.

Full code:

import refinitiv.data as rd
import pandas as pd
import numpy as np

# Open session with Refinitiv Data
rd.open_session()



# Search for equity quotes matching certain criteria
results = rd.discovery.search(
    view=rd.discovery.Views.EQUITY_QUOTES,
    top=500,
    filter="(IssuerOAPermID eq '5076114142' and RCSAssetCategoryLeaf eq 'Ordinary Share')",
    select="RIC, DTSubjectName, RCSAssetCategoryLeaf, ExchangeName, ExchangeCountry"
)
#  4295899979     5035167477     5048094456  5079157132  5052126324    5076114142
df = results  # Assuming 'results' returns a DataFrame

# Define valid RIC endings
substrings = ['.OQ', '.O', '.N', '.A', '.PK', '.PQ']

# Function to validate RICs based on their endings
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

# Filter DataFrame for valid RICs
filtered_df = df[df['RIC'].apply(is_valid_ric)]

# Extract list of valid RICs
rics = filtered_df['RIC'].tolist()

def fetch_data_for_rics(rics, START_DATE, END_DATE):
    rd.open_session()
    # Simulated data fetching; replace this with your actual data fetching call
    df = rd.get_data(
        universe=rics,
        fields=[
            "TR.MnAOfferPricePerShare", "TR.MnAComShrsAcquired(Scale=6)", "TR.MNASynopsis", "TR.IsDelistedQuote",
            "TR.PriceClose", "TR.PriceCloseDate", "TR.MnADateUnconditional", "TR.MnAAnnDate", "TR.MnAIsGoingPrivate"
        ],
        parameters={
            'SDate': START_DATE,
            'EDate': END_DATE,
            'Frq': 'D'
        }
    )
    return df

START_DATE = "1990-01-01"
END_DATE = "2024-12-31"

def process_bankruptcy_cases(rics, START_DATE, END_DATE):
    # Ensure an active session with Refinitiv Data
    rd.open_session()

    # Initialize an empty DataFrame to store compiled bankruptcy data
    bankruptcy_data = pd.DataFrame()

    # Fetch retire dates for all RICs in the list
    df_retire_dates = rd.get_data(
        universe=rics,
        fields=["TR.RetireDate"],
        parameters={'SDate': START_DATE, 'EDate': END_DATE, 'Frq': 'D'}
    )

    for ric in rics:
        # Fetch historical data for the current RIC
        df_history = rd.get_history(
            universe=[ric],
            fields=[
                'TR.MnAHasBankruptcy',
                "TR.PriceClose",
                "TR.PriceCloseDate",
            ],
            parameters={
                'SDate': START_DATE,
                'EDate': END_DATE,
                'Frq': 'D'
            }
        )

        # Rename columns for consistency and reset index
        df_history.rename(columns={
            'TR.MnAHasBankruptcy': 'Bankruptcy Flag',
            'TR.PriceClose': 'Price Close',
            'TR.PriceCloseDate': 'Date'
        }, inplace=True)

        df_history.reset_index(drop=True, inplace=True)
        df_history['Date'] = pd.to_datetime(df_history['Date'])
        df_history.sort_values('Date', inplace=True)

        # Initialize price_closes outside the condition to ensure it's always defined
        price_closes = {}

        # Get retire date for the current RIC from df_retire_dates
        retire_date_row = df_retire_dates[df_retire_dates['Instrument'] == ric]
        retire_date = pd.to_datetime(retire_date_row['RetireDate'].iloc[0]) if not retire_date_row.empty else pd.NaT

        if pd.notna(retire_date):
            # Calculate 'Price Close' for RetireDate, RetireDate - 1, RetireDate - 2, and RetireDate - 3 days
            for days_before in range(4):  # 0, 1, 2, 3 days before (including the retire date)
                target_date = retire_date - pd.Timedelta(days=days_before)
                price_close = df_history[df_history['Date'] == target_date]['Price Close'].iloc[0] if not df_history[df_history['Date'] == target_date].empty else np.nan
                price_closes[f'Price Close RetireDate - {days_before} days'] = price_close

        # Iterate to find Price Close before and after bankruptcy
        for index, row in df_history[df_history['Bankruptcy Flag'] == True].iterrows():
            prev_close_idx = df_history[df_history['Date'] < row['Date']].last_valid_index()
            next_close_idx = df_history[df_history['Date'] > row['Date']].first_valid_index()

            bankruptcy_data_entry = {
                'Instrument': ric,
                'Bankruptcy Flag': row['Bankruptcy Flag'],
                'Date': row['Date'],
                'RetireDate': retire_date if pd.notna(retire_date) else np.nan,
                'Price Close Before': df_history.at[prev_close_idx, 'Price Close'] if pd.notna(prev_close_idx) else np.nan,
                'Price Close on Bankruptcy Date': row['Price Close'],
                'Price Close After': df_history.at[next_close_idx, 'Price Close'] if pd.notna(next_close_idx) else np.nan,
            }

            # Merge the price closes on and before the retire date into the bankruptcy data entry
            bankruptcy_data_entry.update(price_closes)

            bankruptcy_data = bankruptcy_data.append(bankruptcy_data_entry, ignore_index=True)

    bankruptcy_data['Bankruptcy Flag'] = bankruptcy_data['Bankruptcy Flag'].astype(bool)

    rd.close_session()
    return bankruptcy_data






def process_non_bankruptcy_cases(rics, START_DATE, END_DATE):
    df = fetch_data_for_rics(rics, START_DATE, END_DATE)

    # Adjust the column name as per the actual response
    going_private_df = df[df['Going Private Flag'] == True]

    # Preparing the output DataFrame
    output_columns = ['RIC', 'Date Announced', 'Share Price Paid by Acquiror for Target Shares', 
                      'Price Close', 'Price Close Date - 1', 'Date Effective or Unconditional']
    output_df = pd.DataFrame(columns=output_columns)

    for index, row in going_private_df.iterrows():
        ric = row['Instrument']  # Adjust column name if needed
        announcement_date = row['Date Announced']
        offer_price_per_share = row['Share Price Paid by Acquiror for Target Shares']
        unconditional_date = pd.to_datetime(row['Date Effective or Unconditional'])

        if pd.notnull(unconditional_date):
            price_close_date_minus_one = unconditional_date - pd.Timedelta(days=1)
            # Find corresponding 'Price Close'
            price_close_record = df[(df['Instrument'] == ric) & 
                                    (pd.to_datetime(df['Date']) == price_close_date_minus_one)]
            if not price_close_record.empty:
                price_close = price_close_record.iloc[0]['Price Close']
            else:
                price_close = np.nan
        else:
            price_close_date_minus_one = np.nan
            price_close = np.nan

        output_df = output_df.append({
            'RIC': ric,
            'Date Announced': announcement_date,
            'Share Price Paid by Acquiror for Target Shares': offer_price_per_share,
            'Price Close': price_close,
            'Price Close Date - 1': price_close_date_minus_one if pd.notnull(price_close_date_minus_one) else "<NA>",
            'Date Effective or Unconditional': unconditional_date if pd.notnull(unconditional_date) else "<NA>"
        }, ignore_index=True)

    return output_df
# Assuming historical_data and df_data are already fetched and available

# First, process bankruptcy cases

# Function call

def main_process(rics, START_DATE, END_DATE):
    # Fetch preliminary data to check the presence of 'Bankruptcy Flag', 'Going Private Flag', and 'IsDelistedQuote'
    preliminary_data = rd.get_data(
        universe=rics,
        fields=[
            "TR.MnAHasBankruptcy", "TR.MnAIsGoingPrivate", "TR.IsDelistedQuote", "TR.RetireDate", "TR.PriceClose"
        ],
        parameters={
            'SDate': START_DATE,
            'EDate': END_DATE,
            'Frq': 'D'
        }
    )

    # Initialize DataFrame for companies with 'IsDelistedQuote' == true
    delisted_companies_data = pd.DataFrame(columns=['Instrument', 'IsDelistedQuote', 'RetireDate', 'Price Close Before RetireDate'])

    # Process based on flags
    for ric in rics:
        company_data = preliminary_data[preliminary_data['Instrument'] == ric]
        if company_data.empty:
            continue

        # Check for 'Bankruptcy Flag'
        if company_data['Bankruptcy Flag'].any():
            bankruptcy_results = process_bankruptcy_cases([ric], START_DATE, END_DATE)
            print(f"Bankruptcy {ric}:", bankruptcy_results)
            bankruptcy_results.to_csv('./Delisting_results.csv')

        # Check for 'Going Private Flag'
        elif company_data['Going Private Flag'].any():
            non_bankruptcy_results = process_non_bankruptcy_cases([ric], START_DATE, END_DATE)
            print(f"M&A {ric}:", non_bankruptcy_results)
            non_bankruptcy_results.to_csv('./Delisting_results.csv')

        # Check for 'IsDelistedQuote'
        elif company_data['Delisted Quote Flag'].any():
            retire_date = company_data['RetireDate'].iloc[0]
            price_close_date_minus_one = pd.to_datetime(retire_date) - pd.Timedelta(days=1)
            # Assuming 'TR.PriceClose' contains the closing prices for the relevant date range
            price_close_before_retire = preliminary_data[(preliminary_data['Instrument'] == ric) &
                                                         (pd.to_datetime(preliminary_data['Date']) == price_close_date_minus_one)]['Price Close'].iloc[0]
            delisted_companies_data = delisted_companies_data.append({
                'Instrument': ric,
                'IsDelistedQuote': True,
                'RetireDate': retire_date,
                'Price Close Before RetireDate': price_close_before_retire
            }, ignore_index=True)
            print(f"Delisted Company Data for {ric}:", delisted_companies_data)
            delisted_companies_data.to_csv('./Delisting_results.csv')
        else:
            print(f"No specific action required for {ric}.")

# Call the main process function
main_process(rics, START_DATE, END_DATE)



rd.close_session()

I am encountering an issue with the process_bankruptcy_cases function, specifically with comparing the "RetireDate" to the date when "Bankruptcy Flag" is set to true. The challenge arises because some records contain a Bankruptcy Date without a corresponding RetireDate, and vice versa, others have a RetireDate but lack a Bankruptcy Date. Despite attempting various methodologies, I have yet to achieve success. This discrepancy hampers my ability to accurately compare the Bankruptcy Date of one record with the RetireDate of another. For determining the Bankruptcy Date, we reference the historical date of the 'TR.MnAHasBankruptcy' field when its status changes to true.

The intended logic is as follows: If 'TR.MnAHasBankruptcy' equals true, we ascertain whether any of the records possess a RetireDate. If so, we compare this RetireDate with the date on which 'TR.MnAHasBankruptcy' became true. Should the RetireDate precede the Bankruptcy Date, we then retrieve the closing price on the RetireDate. Otherwise, we obtain the closing price one day prior to the Bankruptcy Date.

The primary issue lies in devising a method to effectively compare the RetireDate with the date when 'TR.MnAHasBankruptcy' is true.

Using this syntaxis we can print which RICs have ReitreDate and which have Bankruptcy Date.

def process_bankruptcy_cases(rics, START_DATE, END_DATE):
    # Ensure an active session with Refinitiv Data
    rd.open_session()


    # Initialize an empty DataFrame to store compiled bankruptcy data
    bankruptcy_data = pd.DataFrame()


    # Fetch retire dates for all RICs in the list
    df_retire_dates = rd.get_data(
        universe=rics,
        fields=["TR.RetireDate", "TR.PriceClose", "TR.PriceCloseDate"],
        parameters={'SDate': START_DATE, 'EDate': END_DATE, 'Frq': 'D'}
    )


    for ric in rics:
        # Fetch historical data for the current RIC
        df_history = rd.get_history(
            universe=[ric],
            fields=['TR.MnAHasBankruptcy', "TR.PriceClose", "TR.PriceCloseDate"],
            parameters={'SDate': START_DATE, 'EDate': END_DATE, 'Frq': 'D'}
        )


        # Rename columns for consistency and reset index
        df_history.rename(columns={
            'TR.MnAHasBankruptcy': 'Bankruptcy Flag',
            'TR.PriceClose': 'Price Close',
            'TR.PriceCloseDate': 'Date'
        }, inplace=True)


        df_history.reset_index(drop=True, inplace=True)
        df_history['Date'] = pd.to_datetime(df_history['Date'])
        df_history.sort_values('Date', inplace=True)


        # Get retire date for the current RIC from df_retire_dates
        retire_date_row = df_retire_dates[df_retire_dates['Instrument'] == ric]
        retire_date = pd.to_datetime(retire_date_row['RetireDate'].iloc[0]) if not retire_date_row.empty else pd.NaT


        print(f"Processing RIC: {ric}")
        print(f"Fetched Retire Date Row: {retire_date_row}")
        print(f"Assigned Retire Date: {retire_date}")


        price_closes = {}
        if pd.notna(retire_date):
            for days_before in range(4):  # 0 to 3 days before RetireDate
                target_date = retire_date - pd.Timedelta(days=days_before)
                price_close_on_target = df_history[df_history['Date'] == target_date]['Price Close'].iloc[0] if not df_history[df_history['Date'] == target_date].empty else np.nan
                price_closes[f'Price Close RetireDate - {days_before} days'] = price_close_on_target


        # Iterate to find Price Close before and after bankruptcy
        for index, row in df_history[df_history['Bankruptcy Flag'] == True].iterrows():
            bankruptcy_date = row['Date']
            print(f"Bankruptcy Date: {bankruptcy_date}")  # Correctly placed within the loop
            print(f"Price Close on Bankruptcy Date: {row['Price Close']}")
            print(f"Comparing with Retire Date: {retire_date}")


            if pd.notna(retire_date) and pd.notna(bankruptcy_date) and retire_date < bankruptcy_date:
                # If RetireDate is before the bankruptcy date, include only Retire Date and related price closes
                bankruptcy_data_entry = {
                    'Instrument': ric,
                    'RetireDate': retire_date,
                    **price_closes
                }
            else:
                prev_close_idx = df_history[df_history['Date'] < bankruptcy_date].last_valid_index() if pd.notna(bankruptcy_date) else None
                next_close_idx = df_history[df_history['Date'] > bankruptcy_date].first_valid_index() if pd.notna(bankruptcy_date) else None
                bankruptcy_data_entry = {
                    'Instrument': ric,
                    'Bankruptcy Flag': True,
                    'Date': bankruptcy_date if pd.notna(bankruptcy_date) else 'Date not available',
                    'Price Close Before': df_history.at[prev_close_idx, 'Price Close'] if prev_close_idx is not None else 'N/A',
                    'Price Close on Bankruptcy Date': row['Price Close'] if pd.notna(row['Price Close']) else 'N/A',
                    'Price Close After': df_history.at[next_close_idx, 'Price Close'] if next_close_idx is not None else 'N/A',
                }


            bankruptcy_data = bankruptcy_data.append(bankruptcy_data_entry, ignore_index=True)


    rd.close_session() 
return bankruptcy_data



Output:
Processing RIC: WEWKQ.PK
Fetched Retire Date Row:      Instrument RetireDate  Price Close       Date
0      WEWKQ.PK        NaT         <NA>        NaT
1      WEWKQ.PK        NaT         <NA>        NaT
2      WEWKQ.PK        NaT         <NA>        NaT
3      WEWKQ.PK        NaT         <NA>        NaT
4      WEWKQ.PK        NaT         <NA>        NaT
...         ...        ...          ...        ...
8711   WEWKQ.PK        NaT          0.3 2024-04-03
8712   WEWKQ.PK        NaT         0.16 2024-04-04
8713   WEWKQ.PK        NaT          0.2 2024-04-05
8714   WEWKQ.PK        NaT          0.1 2024-04-08
8715   WEWKQ.PK        NaT          0.1 2024-04-08

[8716 rows x 4 columns]
Assigned Retire Date: NaT
Bankruptcy Date: 2024-02-07 00:00:00
Price Close on Bankruptcy Date: 0.13
Comparing with Retire Date: NaT
Bankruptcy Date: 2024-02-16 00:00:00
Price Close on Bankruptcy Date: 0.2
Comparing with Retire Date: NaT
Bankruptcy WEWKQ.PK:   Instrument  Bankruptcy Flag       Date  Price Close Before  \
0   WEWKQ.PK              1.0 2024-02-07                0.30   
1   WEWKQ.PK              1.0 2024-02-16                0.15   

   Price Close on Bankruptcy Date  Price Close After  
0                            0.13               0.11  
1                            0.20               0.07  
Processing RIC: WEWKQ.PQ
Fetched Retire Date Row:   Instrument RetireDate Price Close Date
0   WEWKQ.PQ        NaT              NaT
Assigned Retire Date: NaT
Bankruptcy Date: NaT
Price Close on Bankruptcy Date: 
Comparing with Retire Date: NaT
Bankruptcy Date: NaT
Price Close on Bankruptcy Date: 
Comparing with Retire Date: NaT
Bankruptcy WEWKQ.PQ:   Instrument  Bankruptcy Flag                Date Price Close Before  \
0   WEWKQ.PQ              1.0  Date not available                N/A   
1   WEWKQ.PQ              1.0  Date not available                N/A   

  Price Close on Bankruptcy Date Price Close After  
0                                              N/A  
1                                              N/A  
Processing RIC: WE.N^K23
Fetched Retire Date Row:      Instrument RetireDate  Price Close       Date
0      WE.N^K23 2023-11-08         <NA>        NaT
1      WE.N^K23        NaT         <NA>        NaT
2      WE.N^K23        NaT         <NA>        NaT
3      WE.N^K23        NaT         <NA>        NaT
4      WE.N^K23        NaT         <NA>        NaT
...         ...        ...          ...        ...
8706   WE.N^K23        NaT       0.8355 2023-11-03
8707   WE.N^K23        NaT       0.8355 2023-11-03
8708   WE.N^K23        NaT       0.8355 2023-11-03
8709   WE.N^K23        NaT       0.8355 2023-11-03
8710   WE.N^K23        NaT       0.8355 2023-11-03

[8711 rows x 4 columns]
Assigned Retire Date: 2023-11-08 00:00:00
Bankruptcy Date: NaT
Price Close on Bankruptcy Date: <NA>
Comparing with Retire Date: 2023-11-08 00:00:00
Bankruptcy Date: NaT
Price Close on Bankruptcy Date: <NA>
Comparing with Retire Date: 2023-11-08 00:00:00
Bankruptcy WE.N^K23:   Instrument  Bankruptcy Flag                Date Price Close Before  \
0   WE.N^K23              1.0  Date not available                N/A   
1   WE.N^K23              1.0  Date not available                N/A   

  Price Close on Bankruptcy Date Price Close After  
0                            N/A               N/A  
1                            N/A               N/A  
Processing RIC: WE.A^K23
Fetched Retire Date Row:      Instrument RetireDate  Price Close       Date
0      WE.A^K23 2023-11-08         <NA>        NaT
1      WE.A^K23        NaT         <NA>        NaT
2      WE.A^K23        NaT         <NA>        NaT
3      WE.A^K23        NaT         <NA>        NaT
4      WE.A^K23        NaT         <NA>        NaT
...         ...        ...          ...        ...
8705   WE.A^K23        NaT       0.8901 2023-11-03
8706   WE.A^K23        NaT       0.8901 2023-11-03
8707   WE.A^K23        NaT       0.8901 2023-11-03
8708   WE.A^K23        NaT       0.8901 2023-11-03
8709   WE.A^K23        NaT       0.8901 2023-11-03

[8710 rows x 4 columns]
Assigned Retire Date: 2023-11-08 00:00:00
Bankruptcy Date: NaT
Price Close on Bankruptcy Date: <NA>
Comparing with Retire Date: 2023-11-08 00:00:00
Bankruptcy Date: NaT
Price Close on Bankruptcy Date: <NA>
Comparing with Retire Date: 2023-11-08 00:00:00
Bankruptcy WE.A^K23:   Instrument  Bankruptcy Flag                Date Price Close Before  \
0   WE.A^K23              1.0  Date not available                N/A   
1   WE.A^K23              1.0  Date not available                N/A   

  Price Close on Bankruptcy Date Price Close After  
0                            N/A               N/A  
1                            N/A               N/A  
Processing RIC: BOWX.N^J21
Fetched Retire Date Row:       Instrument RetireDate  Price Close       Date
0     BOWX.N^J21 2021-10-21         <NA>        NaT
1     BOWX.N^J21        NaT         <NA>        NaT
2     BOWX.N^J21        NaT         <NA>        NaT
3     BOWX.N^J21        NaT         <NA>        NaT
4     BOWX.N^J21        NaT         <NA>        NaT
...          ...        ...          ...        ...
8706  BOWX.N^J21        NaT        10.39 2021-10-20
8707  BOWX.N^J21        NaT        10.39 2021-10-20
8708  BOWX.N^J21        NaT        10.39 2021-10-20
8709  BOWX.N^J21        NaT        10.39 2021-10-20
8710  BOWX.N^J21        NaT        10.39 2021-10-20

[8711 rows x 4 columns]
Assigned Retire Date: 2021-10-21 00:00:00
Bankruptcy Date: NaT
Price Close on Bankruptcy Date: <NA>
Comparing with Retire Date: 2021-10-21 00:00:00
Bankruptcy Date: NaT
Price Close on Bankruptcy Date: <NA>
Comparing with Retire Date: 2021-10-21 00:00:00
Bankruptcy BOWX.N^J21:    Instrument  Bankruptcy Flag                Date Price Close Before  \
0  BOWX.N^J21              1.0  Date not available                N/A   
1  BOWX.N^J21              1.0  Date not available                N/A   

  Price Close on Bankruptcy Date Price Close After  
0                            N/A               N/A  
1                            N/A               N/A

Thank you in advance. Your assistance would be greatly appreciated.

Jirapongse · April 11

@vitali

Thank you reaching out to us.

Normally Developer Community Forum is for how-to/general questions but your question seems to be more complex and requires investigation e.g. review source code and verify the content.

You may contact your LSEG account team or sales team directly and ask for the professional service. The professional service group can review the source code or help you implementing the solution with an additional cost.

I checked the code and found that you are using the TR.RetireDate and TR.MNAHASBANKRUPTCY fields. These fields don't support time-series data which means they don't support the SDate and EDate parameter. Moreover, they are in the different categories. You can refer to the Data Item Browser tool for the fields' descriptions and their supported parameters.

For example, the TR.MNAHASBANKRUPTCY field is in the Mergers & Acquisitions category and its definition is Yes/No flag set to 'Y' when the target company is bankrupt or goes bankrupt during the transaction. The TR.RetireDate field is in the Reference & Identifiers category and its definition is quote retire date.

Typically, it value relates to the fields' values within the same category.

You may need to contact the helpdesk team via MyRefinitiv to verify how these fields can't be used to support your use cases.

vitali · April 11

Hi,
@Jirapongse! Thanks for the prompt reply!

rd.get_history and rd.get_data comparison

Best Answer

Answers

Categories