Clean Investor Conference Transcripts

Extract information from the name and title section of transcripts, link to firm and broker identifiers. From the title section, identify the corporate participants (from which executives are identified) and the other participants (from which IBES analysts are identified).

Output:

• Transcripts data, unique at the transcriptid level.
• Transcript-participant level data, unique at the transcriptid-par_name level

import pandas as pd
import numpy as np
import os, re, glob, multiprocessing
from fuzzywuzzy import fuzz
import dask.dataframe as dd

%matplotlib inline 
%load_ext autoreload
%autoreload 2

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 

Extract Information from Document Names

def extract_info_filename(filelist):
    df = pd.DataFrame()
    i = 0
    for file in filelist:
        # docname: without abs path and extension
        docname= re.search(r"\\(\d\d\d\d\-\w\w\w\-\d\d\-.*)\.txt", file).group(1).lower() 
        date = re.search(r"(\d\d\d\d\-\w\w\w\-\d\d)", docname).group(1)        
        # identify RIC as anything in between date and transcriptid: 
        RIC = re.search(r"\d\d\d\d\-\w\w\w\-\d\d\-(.*)\-\d{12}\-transcript", docname).group(1) 
        transcriptid = re.search(r"\-(\d{12})\-transcript", docname).group(1)        
        df.loc[i, 'ric'] = RIC.upper()
        df.loc[i, 'datestring'] = date
        df.loc[i, 'transcriptid'] = transcriptid
        df.loc[i, 'docname'] = docname
        df.loc[i, 'filepath'] = file
        i +=1
    return df

translist = glob.glob(folderpath_transcripts+"/*/*.txt")+glob.glob(folderpath_transcripts2022+"/*.txt")

%%time
df = extract_info_filename(translist)
df['date_docname'] = pd.to_datetime(df['datestring'], format="%Y-%b-%d")
df = df.drop(columns=['datestring'])

CPU times: total: 24min 44s
Wall time: 26min 10s

# extract ticker and exchange code from RIC
df['yearqtr'] = df['date_docname'].dt.to_period('Q')
df['tic'] =  df['ric'].str.extract(r"^([\w\d]+)\.", expand=True)

Add permno and ncusip through ticker using crsp.msenames

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]

Add gvkey using ccmlink.

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)

enddate_string = '2022-12-31'

_match1 = df.loc[(df.tic.notnull()) & (df.date_docname<=enddate_string),['tic','date_docname','transcriptid','yearqtr']]
_match1 = _match1.merge(right=msenames[['permno','ticker','ncusip','namedt', 'nameendt']], 
                        how='left', left_on=['tic'], right_on=['ticker'], indicator=True)
# impose date conditions
_match1 = _match1.loc[(_match1.namedt<=_match1.date_docname) & (_match1.nameendt>=_match1.date_docname) &(_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'])
# drop duplicates, first arrange by descending gvkey and permno
_match1 = _match1.sort_values(by=['transcriptid','gvkey', 'permno'], ascending=[True, False, False])
_match1 = _match1.drop_duplicates(subset=['transcriptid'])

df = df.merge(right=_match1[['transcriptid','gvkey','permno','ticker','ncusip']], how='left', on=['transcriptid'])
df = df.drop(columns=['yearqtr'])

Extract Information from the Title Section

The title section contains the participant lists at the conference, divided into corporate participants and other participants (e.g., hosting analysts and investors)

def import_text(txtfilepath):    
    with open(txtfilepath, "r", encoding="utf-8") as f:
        doc = f.read()
    return doc

def extract_title_section_info(row):
    doc = import_text(row['filepath']).lower()
    
    ### extract title of event, date, and time
    title = re.search(r"v e r s i o n\n\n(.*)\n(\w{3,9}\s\d\d\,\s\d\d\d\d)\s\/\s(\d{1,2}\:\d{1,2}[ap]m)\s(\w{0,4})\n", doc).group(1)
    date  = re.search(r"v e r s i o n\n\n(.*)\n(\w{3,9}\s\d\d\,\s\d\d\d\d)\s\/\s(\d{1,2}\:\d{1,2}[ap]m)\s(\w{0,4})\n", doc).group(2)
    time  = re.search(r"v e r s i o n\n\n(.*)\n(\w{3,9}\s\d\d\,\s\d\d\d\d)\s\/\s(\d{1,2}\:\d{1,2}[ap]m)\s(\w{0,4})\n", doc).group(3)
    timezone  = re.search(r"v e r s i o n\n\n(.*)\n(\w{3,9}\s\d\d\,\s\d\d\d\d)\s\/\s(\d{1,2}\:\d{1,2}[ap]m)\s(\w{0,4})\n", doc).group(4)
    
    ### extract cp and op section
    cp_section, op_section = None, None
    if re.search(r"\ncorporate participants\n\=+(.*?)(?:\n\n={6,}|\n{4,}\-*\ndefinitions\n)", doc, flags=re.DOTALL):
        cp_section = re.search(r"\ncorporate participants\n\=+(.*?)(?:\n\n={6,}|\n{4,}\-*\ndefinitions\n)", doc, flags=re.DOTALL).group(1)
    if re.search(r"\nconference call participiants\n\=+(.*?)(?:\n\n={6,}|\n{4,}\-*\ndefinitions\n)", doc, flags=re.DOTALL):
        op_section = re.search(r"\nconference call participiants\n\=+(.*?)(?:\n\n={6,}|\n{4,}\-*\ndefinitions\n)", doc, flags=re.DOTALL).group(1)
    
    ### collect information
    cp_list, op_list=[],[]   
    if not cp_section is None:
        cp_section = re.sub(r"^\s?\*", "######", cp_section, flags=re.M) #identify any * that is at the start of a new line (with multiple flag) w optional blank
        cp_list = re.findall(r"######.*?(?=######)", cp_section+"######", flags=re.DOTALL) 
    if not op_section is None:
        op_section = re.sub(r"^\s?\*", "######", op_section, flags=re.M)
        op_list = re.findall(r"######.*?(?=######)", op_section+"######", flags=re.DOTALL) 
    cp_len=len(cp_list)
    op_len=len(op_list)
        
    return title, date, time, timezone, cp_section, cp_list, cp_len, op_section, op_list, op_len

%%time
ddf = dd.from_pandas(df, npartitions=(multiprocessing.cpu_count()-1)*1)
meta_df = pd.DataFrame(columns=[0,1,2,3,4,5,6,7,8,9], dtype=str)
result = ddf.apply(lambda x: extract_title_section_info(x), axis=1, result_type='expand', meta=meta_df).compute(scheduler="multiprocessing")
result.columns=['title','datestring','timestring','timezone','cp_section','cp_list','cp_len','op_section','op_list','op_len']

CPU times: total: 52.2 s
Wall time: 8h 32min 35s

result['date'] = pd.to_datetime(result['datestring'], format="%B %d, %Y")
result['datetime'] = pd.to_datetime(result['datestring']+" "+result['timestring'], format="%B %d, %Y %I:%M%p")
result = result.drop(columns=['datestring','timestring'])

# result.to_pickle("../data_temp/01_2_result.pkl")
# result = pd.read_pickle("../data_temp/01_2_result.pkl")

df2 = df.join(result)

Merge with conference schedule based on event names

scfull = pd.read_pickle(filepath_scheduleinput)[['event_id','date','ric','event_name','company_name','transcript']].rename(columns={'date':'date_sc'})

First, merge by ric-date-event_name/title

temp1 = df2[['title','date_docname','ric','date','transcriptid']].copy()
temp1 = temp1.merge(right=scfull, left_on=['ric'], right_on=['ric'], how='left')
matched1 = temp1.loc[(temp1.event_id.notnull())& (abs((temp1.date_docname-temp1.date_sc).dt.days)<=5)].copy()
lst_trans = matched1['transcriptid'].unique().tolist()
unmatched1 = df2.query('~transcriptid.isin(@lst_trans)')[['title','date_docname','ric','date','transcriptid']].copy()

matched1['_diff_date'] = abs((matched1.date_docname-matched1.date_sc).dt.days)
matched1['_diff_len'] = abs((matched1.title.str.len()-matched1.event_name.str.len()))
matched1['_score'] = matched1.apply(lambda row: fuzz.token_set_ratio(row.title, row.event_name), axis=1)
matched1 = matched1.sort_values(by=['transcriptid','_score','_diff_date','_diff_len'], ascending=[True, False, True, True])
matched1 = matched1.query('_score==100')
matched1.loc[matched1.duplicated(subset=['transcriptid'], keep=False)==True] # duplicates due to multidate events
matched1 = matched1.sort_values(by=['transcriptid','_diff_date','_diff_len'], ascending=[True,True,True])
matched1 = matched1.drop_duplicates(subset=['transcriptid'])

Second, some observation has missing ric, merge by date then event_name/title

lst_trans = matched1['transcriptid'].unique().tolist()
lst_ids = matched1['event_id'].unique().tolist()

temp2 = df2.query('~transcriptid.isin(@lst_trans)')[['title','date_docname','ric','date','transcriptid']].copy()
sc2 = scfull.query('~event_id.isin(@lst_ids) & transcript.notnull()').copy()
temp2 = temp2.merge(right=sc2, left_on=['date_docname'], right_on=['date_sc'], how='left')
matched2 = temp2.loc[(temp2.event_id.notnull())].copy()
lst_trans2 = matched2['transcriptid'].unique().tolist()+lst_trans
unmatched2 = df2.query('~transcriptid.isin(@lst_trans2)')[['title','date_docname','ric','date','transcriptid']].drop_duplicates().copy()

matched2['_diff_len'] = abs((matched2.title.str.len()-matched2.event_name.str.len()))
matched2['_score1'] = matched2.apply(lambda row: fuzz.token_set_ratio(row.title, row.event_name), axis=1)
matched2['_score2'] = matched2.apply(lambda row: fuzz.token_set_ratio(row.ric_x, row.ric_y), axis=1)
matched2 = matched2.sort_values(by=['transcriptid','_score1','_score2','_diff_len'], ascending=[True, False, False,True])
matched2 = matched2.query('_score1==100|(_score1>=95 & _score2==100)')
matched2.loc[matched2.duplicated(subset=['transcriptid'], keep=False)==True] 
matched2 = matched2.sort_values(by=['transcriptid','_diff_len'], ascending=[True,True])
matched2 = matched2.drop_duplicates(subset=['transcriptid'])
lst_trans2 = matched2['transcriptid'].unique().tolist()+lst_trans
unmatched2 = df2.query('~transcriptid.isin(@lst_trans2)')[['title','date_docname','ric','date','transcriptid']].drop_duplicates().copy()

Collect and merge in schedule information

match = pd.concat([matched1,matched2], ignore_index=True)[['transcriptid','event_id']]
df3 = df2.merge(right=match, on=['transcriptid'], how='left')
scinfo = (pd.read_pickle(filepath_schedule)[['event_id','broker_id_1','inIBES']])
df3=df3.merge(right=scinfo, on=['event_id'], how='left')

Clean Participant Information

Separately clean corporate participants (cp) and other participant (op).

#expand to wide
temp1 = pd.DataFrame(df3["cp_list"].to_list())
temp1.columns = ["cp_"+str(col+1) for col in temp1.columns]

temp2 = pd.DataFrame(df3["op_list"].to_list())
temp2.columns = ["op_"+str(col+1) for col in temp2.columns]

df4 = df3.join(temp1).join(temp2)

Define functions to extract participants info

def extract_participant_info(text):
    name, firmtitle = None, None
    if not text is None:
        if re.search(r"^######(.*)\n", text):
            name = re.search(r"^######(.*)\n", text).group(1).strip() 
        if re.search(r"\n(.*)", text):
            firmtitle = re.search(r"\n(.*)", text).group(1).strip() 

        name = None if len(name)==0 else name
        firmtitle = None if len(firmtitle)==0 else firmtitle
    
    return name, firmtitle

parwide = pd.DataFrame(index=df4.index)
listofcols=[]
for col in df4.filter(regex=r"^cp_\d|^op_\d").columns:
    listofcols.append(col)
    temp= df4.apply(lambda x: extract_participant_info(x[col]), axis=1, result_type='expand')
    temp.columns = [re.search(r"^(\w\w\_)", col).group(1)+"name"+re.search(r"(\_\d+)$", col).group(1),
                    re.search(r"^(\w\w\_)", col).group(1)+"firmtitle"+re.search(r"(\_\d+)$", col).group(1),
                   ]
    parwide = parwide.join(temp)

df4 = df4.join(parwide)

# define idvar used when recasting wide to long
idvar = ['transcriptid','filepath','docname','title','cp_len','op_len']

cplong = pd.wide_to_long(df4[idvar+df4.filter(regex=r"cp_(name|firmtitle)").columns.tolist()], 
                          stubnames=['cp_name_', 'cp_firmtitle_'], 
                          i = idvar, 
                          j = 'parno')

cplong = cplong.loc[~cplong.isna().all(axis=1)]

cplong = cplong.reset_index().rename(columns={'cp_name_':'par_name', 'cp_firmtitle_':'par_firmtitle'})
cplong['section'] = 'cp'

oplong = pd.wide_to_long(df4[idvar+df4.filter(regex=r"op_(name|firmtitle)").columns.tolist()], 
                          stubnames=['op_name_', 'op_firmtitle_'], 
                          i = idvar, 
                          j = 'parno')

oplong = oplong.loc[~oplong.isna().all(axis=1)]

oplong = oplong.reset_index().rename(columns={'op_name_':'par_name', 'op_firmtitle_':'par_firmtitle'})
oplong['section'] = 'op'

Inspect and correct misclassification of participants in the CP and OP section.

parlong = pd.concat([cplong, oplong], ignore_index=True).sort_values(by=['transcriptid','section'])

def clean_var(var):
    '''
    remove special charcters and multiple spaces
    '''
    if (not var ==None):
        var = re.sub(r"[^A-Za-z\s]", "", var) 
        var = re.sub(r"\s{2,}", " ", var) 
    return var

def identify_role(row):
    '''
    Identify a participant's role (cp or op) based on whether his/her title includes the name of the firm.
    '''
    # extract firm and broker info from the title of the document (firm XXX at YYY conference)
    firm,broker,par_1, par_2 = None, None, None, None
    if re.search(r"^(.*)\sat\s(.*)", row['title']):
        firm, broker = re.search(r"^(.*)\sat\s(.*)", row['title']).group(1,2)  
    # extract info on the participants firm and title
    if re.search(r"^(.*)\s-\s(.*)", row['par_firmtitle']):
        par_1, par_2 = re.search(r"^(.*)\s-\s(.*)", row['par_firmtitle']).group(1,2)
    
    role = None
    if (not firm ==None) & (not broker == None) & (not par_1 ==None) & (not par_2==None):
        par_1 = re.sub(r"\'s", "", par_1)
        par_2 = re.sub(r"\'s", "", par_2)

        # remove special characters
        firm = clean_var(firm)
        broker = clean_var(broker)
        par_1 = clean_var(par_1)
        par_2 = clean_var(par_2)
        
        # upon manual inspection, if either the CP or OP section is missing, misclassification is more likely 
        if row['cp_len']==0 | row['op_len']==0:            
            # firm match using up to the first two words
            if (((par_1.split()[0] in firm) & (par_1.split()[min(2,len(par_1.split())-1)] in firm)) | 
                ((par_2.split()[0] in firm) & (par_2.split()[min(2,len(par_2.split())-1)] in firm))):
                role = "cp"
            elif (((par_1.split()[0] in broker) & (par_1.split()[min(2,len(par_1.split())-1)] in broker)) | 
                  ((par_2.split()[0] in broker) & (par_2.split()[min(2,len(par_2.split())-1)] in broker))):
                role = "op"
        # if both sections are present, use more strict criteria 
        elif len(par_1.split())>=2:
            if (par_1.split()[0] in firm) & (par_1.split()[1] in firm):
                role = "cp"
            elif (par_1.split()[0] in broker) & (par_1.split()[1] in broker):
                role = "op"
        elif len(par_2.split())>=2:
            if (par_2.split()[0] in firm) & (par_2.split()[1] in firm):
                role = "cp"
            elif (par_2.split()[0] in broker) & (par_2.split()[1] in broker):
                role = "op"                    
    return role, firm, broker

parlong[['role','_firm','_broker']] = parlong.apply(lambda row: identify_role(row), axis=1, result_type='expand')

#information in role is more accurate. assign unique sequence.
parlong = parlong.assign(par_role = lambda x: np.where(x['role'].notnull(), x['role'], x['section']), 
                         par_seq = lambda x: x.groupby(['transcriptid']).cumcount()+1)
parlong=parlong.drop(columns=['parno'])

Remove duplicates at transcriptid-par_name level

# drop duplicates at transcript-name level
parlong = parlong.drop_duplicates(subset=['transcriptid','par_name'])

Identify key executives

parlong['i_cp'] = parlong.par_role=='cp'
parlong['i_op'] = parlong.par_role=='op'
parlong['CEO']=((parlong['par_firmtitle'].str.contains(r"\bCEO\b|chief executive officer", regex=True, flags=re.I))
                & (parlong['par_role']=='cp'))*1
parlong['CFO']=((parlong['par_firmtitle'].str.contains(r"\bCFO\b|chief financ[\w]{1,3} officer", regex=True, flags=re.I))
                & (parlong['par_role']=='cp'))*1
parlong['CSuite']=((parlong['par_firmtitle'].str.contains(r"\bC[\w]O\b|chief [\w]{1,30} officer", regex=True, flags=re.I))
                & (parlong['par_role']=='cp'))*1
parlong['IRO']=((parlong['par_firmtitle'].str.contains(r"\binvestor[\w]{0,2} relation|\bIR\b", regex=True, flags=re.I))
                & (parlong['par_role']=='cp'))*1

Compute statistics at the transcriptid level.

parlong['num_cp'] = parlong.groupby('transcriptid')['i_cp'].transform('sum')
parlong['num_op'] = parlong.groupby('transcriptid')['i_op'].transform('sum')
parlong['has_CEO'] = parlong.groupby('transcriptid')['CEO'].transform('max')
parlong['has_CFO'] = parlong.groupby('transcriptid')['CFO'].transform('max')
parlong['has_IRO'] = parlong.groupby('transcriptid')['IRO'].transform('max')
parlong['has_CSuite'] = parlong.groupby('transcriptid')['CSuite'].transform('max')
parlong['num_CSuite'] = parlong.groupby('transcriptid')['CSuite'].transform('sum')

parlong = parlong.drop(columns=['cp_len','op_len','section','role','i_cp','i_op','title','docname'])

Merge back

df5 = df4.merge(right=parlong.drop_duplicates(subset=['transcriptid']).filter(regex='transcriptid|num_|has_'), 
                how='left', on=['transcriptid'])

Save

transcriptid level data

dfinal = df5.filter(regex="^(?!_)")

selvars = (['transcriptid','event_id','ric', 'gvkey','permno','ticker','ncusip',
            'date','datetime','timezone', 
            'broker_id_1', 'inIBES']
           +['title','docname','filepath','date_docname','cp_section','op_section','cp_list','op_list']
           +dfinal.filter(regex="num_|has_").columns.tolist())

dfinal[selvars].to_pickle(outputpath_trans)

transcriptid-par_name level data

parlong.to_pickle(outputpath_par)