# 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

In [1]:

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

In [2]:

    %matplotlib inline
    %load_ext autoreload
    %autoreload 2

In [3]:

    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

In [4]:

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

### Extract Information from Document Names¶

In [5]:

    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

In [6]:

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

In [7]:

    %%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

In [8]:

    # 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`

In [9]:

    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`.

In [10]:

    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)

In [11]:

    enddate_string = '2022-12-31'

In [12]:

    _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'])

In [13]:

    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)

In [14]:

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

In [15]:

    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

In [16]:

    %%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

In [17]:

    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'])

In [18]:

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

In [19]:

    df2 = df.join(result)

#### Merge with conference schedule based on event names¶

In [20]:

    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`

In [21]:

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

In [22]:

    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`

In [23]:

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

In [24]:

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

In [25]:

    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

In [26]:

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

In [27]:

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

In [28]:

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

In [29]:

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

Define functions to extract participants info

In [30]:

    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

In [31]:

    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)

In [32]:

    df4 = df4.join(parwide)

In [33]:

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

In [34]:

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

In [35]:

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

In [36]:

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

In [37]:

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

In [38]:

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

In [39]:

    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.

In [40]:

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

In [41]:

    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

In [42]:

    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

In [43]:

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

In [44]:

    #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

In [45]:

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

Identify key executives

In [46]:

    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.

In [47]:

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

In [48]:

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

Merge back

In [49]:

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

### Save¶

`transcriptid` level data

In [50]:

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

In [51]:

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

In [52]:

    dfinal[selvars].to_pickle(outputpath_trans)

`transcriptid`-`par_name` level data

In [53]:

    parlong.to_pickle(outputpath_par)

In [ ]:

In [ ]:

In [ ]: