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.