#display options
from IPython.core.display import display, HTML
display(HTML("<style>.container { width:100% !important; }</style>"))
import numpy as np
import pandas as pd
import time
%matplotlib inline
import matplotlib as mpl
mpl.rc("savefig",dpi=100)
from matplotlib import pyplot as plt
#pandas option
pd.options.display.max_columns = 50
idx = pd.IndexSlice
# #define winsorizing filter
# #winsorize the top and bottom 1% values of the target variable
# def wins_one(df, var):
# return df.groupby(level = 1).apply(lambda x: pd.Series(winsorize(x[var], limits = [0.01, 0.01]), index = x['gvkey'])).reorder_levels(order = ['gvkey', 'fyear'])
def wins_one(df_var, p = 0.01):
return df_var.groupby(level = 1).apply(lambda x: x.clip(lower = x.quantile(p), upper = x.quantile(1. - p)))
percentile = [0.1, 0.25, 0.50, 0.75, 0.9][::-1]
scf_years=[1989,1992,1995,1998,2001,2004,2007,2010,2013,2016]
all_years=range(1980,2017)
Load compustat data from WRDS. We use annual data from 1980-2016. The following variables (named as per WRDS conventions) are used in our analysis.
df = pd.read_stata('./compustat_mergenfo_plus_sale_at.dta')
df['fyear'] = df['fyear'].astype(int)
#set index after sorting
df = df.drop_duplicates(subset = ['gvkey', 'fyear'])
df = df.sort_values(['gvkey', 'fyear'])
df = df.set_index(['gvkey', 'fyear'], verify_integrity = True, drop = False)
df = df[df['fic'] == 'USA']
We now construct some user-defined variables:
df['mkvalt'] = df['csho'] * df['prcc_f']
df = df[pd.notnull(df['pi']) & pd.notnull(df['dvt']) & (pd.notnull(df['mkvalt']))]
df['wgt_mval'] = df['mkvalt'] / df['mkvalt'].groupby(level = 1).sum()
df['pi_mval_ratio'] = (df['pi']/df['mkvalt']).replace([np.inf, -np.inf], np.nan)
df['dvt_mval_ratio'] = (df['dvt']/df['mkvalt']).replace([np.inf, -np.inf], np.nan)
df['capital_gain']= (df['mkvalt']/df['mkvalt'].shift()-1).replace([np.inf, -np.inf], np.nan)
df['capital_gain_3']= ((df['mkvalt']/df['mkvalt'].shift(3))**(1.0/3)-1).replace([np.inf, -np.inf], np.nan)
These moments are computed after winsorising and for all years. To change to scf_years replace the variable "all_years" by "scf_years"
sel=df['fyear'].isin(all_years)
df['capital_gain_wins'] = wins_one(df['capital_gain'])
df['capital_gain_3_wins'] = wins_one(df['capital_gain_3'])
VW_CG_surv_compustat=(df[sel]['capital_gain_wins']*df[sel]['wgt_mval'].shift(1)).groupby(level = 1).sum()
VW_CG_surv_compustat_3yr=(df[sel]['capital_gain_3_wins']*df[sel]['wgt_mval'].shift(3)).groupby(level = 1).sum()
mkvalt_sum = df[sel]['mkvalt'].groupby(level = 1).sum()
VW_CG_index_compustat=mkvalt_sum/mkvalt_sum.shift()-1
L=3
VW_CG_index_compustat_3yrs=(mkvalt_sum/mkvalt_sum.shift(L))**(1.0/L)-1
compstat=pd.DataFrame([VW_CG_surv_compustat,VW_CG_surv_compustat_3yr,VW_CG_index_compustat,VW_CG_index_compustat_3yrs]).T
compstat.columns=['surving','surving3','index','index3']
compstat.to_csv('compustat.csv')
compstat
num=(df[sel]['capital_gain_wins']).groupby(level = 1).count()
CG_mom=pd.DataFrame([num])
CG_mom=CG_mom.T
CG_mom.columns=['num']
CG_mval=[df[sel]['capital_gain'].groupby(level = 1).quantile(p) for p in percentile]
per_head=['p90','p75','p50','p25','p10']
for p in range(len(percentile)):
CG_mom[per_head[p]]=CG_mval[p]
CG_mom.to_csv('compustat_CG_all_firms.csv')
CG_mom