# Clean Investor Conference Schedule¶

In [1]:

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

In [2]:

    %matplotlib inline
    %load_ext autoreload
    %autoreload 2

In [3]:

    from functions_01 import *

In [4]:

    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 [5]:

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

In [6]:

    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.

In [7]:

    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)

In [8]:

    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]

In [9]:

    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`

In [10]:

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

In [11]:

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

In [12]:

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

In [13]:

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

In [14]:

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

In [15]:

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

In [16]:

    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)

In [17]:

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

In [18]:

    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.

In [19]:

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

In [20]:

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

In [21]:

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

In [22]:

    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.

In [23]:

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

In [24]:

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

### Save¶

Non missing `gvkey`

In [25]:

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

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

In [26]:

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

No duplicates at `gvkey`-`date`-`location` level

In [27]:

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

In [28]:

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

In [29]:

    dfinal.to_pickle(outputpath_schedule)