Clean Investor Conference Schedule

import pandas as pd
import numpy as np
import os, re, datetime

%matplotlib inline 
%load_ext autoreload
%autoreload 2

from functions_01 import *

pd.set_option('display.memory_usage', 'deep')
pd.set_option('display.precision', 2) 
pd.set_option('display.width', 200)
pd.set_option('display.max_rows', 300)
pd.set_option('display.max_columns', None)
pd.set_option('display.max_colwidth', 80)
pd.options.display.float_format = '{:,.4f}'.format

from filepath_01 import * # change to reflect input and output file path 

1. Load Schedule

Conference schedule data was downloaded from Refinitiv. The data is at the transcript level. Each row is assigned an event_id.

df= pd.read_pickle(filepath_scheduleinput)

2. Add Company Identifiers

Link Refinitiv's identifier (ric) to permno and gvkey. ric consists of a ticker symbol followed by the exchange code.

df['yearqtr'] = df['date'].dt.to_period('Q')
df['tic'] =  df['ric'].str.extract(r"^([\w\d]+)\.", expand=True)
df['exchange'] = df['ric'].str.extract(r"\.(.+)", expand=True)

msenames = pd.read_sas(filepath_msenames, format='sas7bdat', encoding="utf-8")
msenames.columns = msenames.columns.str.lower()
msenames = msenames[['permno','ticker','ncusip','namedt','nameendt','comnam','shrcd','exchcd','cusip']]
msenames = msenames.loc[(msenames.ticker.notnull()) & (msenames.permno.notnull())]
msenames = msenames.loc[msenames.duplicated(subset=['permno','ticker','ncusip','namedt','nameendt'], keep=False)==False]

ccm = pd.read_sas(filepath_ccmlink_qtr, format='sas7bdat', encoding="utf-8")
ccm.columns=ccm.columns.str.lower()
ccm['yearqtr'] = ccm['datadate'].dt.to_period('Q')
ccm = ccm.drop(columns=['datadate'])
ccm = ccm.loc[ccm.duplicated(subset=['gvkey','permno','yearqtr'], keep=False)==False].reset_index(drop=True)

Match via ticker-permno-gvkey

_match1 = df.loc[(df.tic.notnull()) & (df.date<='2022-12-31'),
                 ['event_id','date','yearqtr','event_name','company_name','ric', 'tic', 'exchange','fm_nevts']]
_match1 = _match1.merge(right=msenames[['permno','ticker','ncusip','namedt', 'nameendt']], 
                        how='left', left_on=['tic'], right_on=['ticker'], indicator=True)
_match1 = _match1.loc[(_match1.namedt<=_match1.date) & (_match1.nameendt>=_match1.date) &(_match1._merge=='both')]
_match1 = _match1.drop(columns=['_merge'])
_match1 = _match1.merge(right=ccm, how='left', left_on=['permno', 'yearqtr'], right_on=['permno', 'yearqtr'], indicator=True)
_match1 = _match1.drop(columns=['_merge'])
_match1 = _match1.sort_values(by=['event_id','gvkey', 'permno'], ascending=[True, False, False])
_match1 = _match1.drop_duplicates(subset=['event_id'])

_matchall=_match1.reset_index(drop=True)[['event_id','permno','ncusip','gvkey']]

df = df.merge(right=_matchall, how='left', on=['event_id'])

df = df.drop(columns=['yearqtr'])

3. Identify Brokers and Conferences

Extract the name of the broker from the event name (which follows the format company XXX at YYY conference). Manually inspect and clean the broker names. Assign a unique ID to brokers (broker_id).

broker_id is then linked to IBES IBES_id. The link table is manually created by matching on broker names, taking into account name changes and common name variants. For instance, Baird Capital, Baird, or Robert W. Baird is assigned broker_id of "B001" and subsequently linked to IBES_id of "BAIRD".

df['event_name_2'] = df['event_name'].str.extract(r'\bat\s(.*)')
df['event_name_2'] = df['event_name_2'].astype(str)

temp_brokerid = df.apply(assign_broker_id, axis=1)

df2 = df.merge(right=temp_brokerid, how='outer', left_index=True, right_index=True, indicator=True)
assert((df2._merge.value_counts()['right_only']==0) & df2._merge.value_counts()['left_only']==0)
df2 = df2.drop(columns=['_merge'])
temp1 = pd.DataFrame(df2["broker_ids"].to_list(), columns=['broker_id_1', 'broker_id_2']) #inspected, no co-hosting so broker_id_2 is not used.
temp2 = pd.DataFrame(df2["broker_names"].to_list(), columns=['broker_name_1', 'broker_name_2'])
df2 = df2.join(temp1).join(temp2)

df2['broker_nevts'] = df2.groupby(['broker_id_1'])['event_id'].transform('count')

df2 = df2.drop(columns=['broker_ids','broker_names','event_name_2','nonbroker_names'])

Indicate if a firm-broker relationship at the time of the conference is covered in IBES. A coverage is considered valid if an analyst issues any forecast or recommendation for a given firm in IBES. broker_id_1 is manually linked to IBES_id based on broker names.

ibesdates=pd.read_stata(filepath_ibesdata)[['permno', 'broker_id_1']]
ibesdates =ibesdates.drop_duplicates()

match1=df2[['event_id','permno','broker_id_1']].merge(right=ibesdates,
              how='inner',on=['permno','broker_id_1'])

match1 = match1.drop_duplicates(subset=['event_id'])

df2 = df2.merge(right=match1.drop(columns=['permno','broker_id_1']), how='left', on=['event_id'], indicator=True)
df2 = df2.assign(inIBES = lambda x: np.where(x._merge=="both", 1, 0)).drop(columns=['_merge'])

From the event name (firm XXX at conference YYY), extract the name of the conference. Manually inspect these noisy names and assign a conference id (conf_id). A conf_id can occur repeatedly (e.g., 2017, 2018, 2019 Raymond James Inst Investor Conference). confuniq_id denotes the occurence of conf_id at a given time. Load back manually assigned IDs.

confinfo = pd.read_pickle(filepath_confinfo)[['event_id','conf_id','confuniq_id']]

dfinal = df2.merge(right=confinfo, how='left', on=['event_id'])

Save

Non missing gvkey

dfinal = dfinal.loc[dfinal.gvkey.notnull()]

Require at least one broker to be identified in the name of the event.

dfinal = dfinal.loc[dfinal.broker_num==1]

No duplicates at gvkey-date-location level

dfinal = dfinal.sort_values(by=['gvkey','date','location','conf_id','confuniq_id'], na_position='last')
dfinal = dfinal.drop_duplicates(subset=['gvkey','date','location'])

dfinal = dfinal.sort_values(by=['event_id'])

dfinal.to_pickle(outputpath_schedule)