Compustat Data: Net Income Yield Calculations

Import stuff

In [1]:
#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

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=[1988,1991,1994,1997,2000,2003,2006,2009,2012,2015]
all_years=range(1980,2017)

Load compustat data

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.

  • GVKEY -- Global Company Key
  • FYEAR -- Data Year - Fiscal
  • FIC -- Foreign Incorporation Code
  • CSHO -- Common Shares Outstanding
  • PRCC_F -- Price Close - Annual - Fiscal
  • PI -- Pretax Income
In [2]:
#load compustat data
df = pd.read_stata('./compustat_mergenfo_plus_sale_at.dta')
df['fyear'] = df['fyear'].astype(int)
In [3]:
#set index after sorting
df = df.drop_duplicates(subset = ['gvkey', 'fyear']) #this is not ok.
df = df.sort_values(['gvkey', 'fyear'])
df = df.set_index(['gvkey', 'fyear'], verify_integrity = True, drop = False)
df = df[df['fic'] == 'USA']

Measurement Concepts

We now construct some user-defined variables:

  • mkvalt: market value at the close of fiscal year, MARKET VALUE = CSHO x PRCC_F
  • wgt_mkvalt: weights proportional to mkvalt
  • pi_mval_ratio: Net income yield, PI/mkvalt
  • X_wins: winsorised version of X
In [4]:
#calculate self-defined market value variable
df['mkvalt'] = df['csho'] * df['prcc_f']
df = df[pd.notnull(df['pi']) & pd.notnull(df['dvt']) & (pd.notnull(df['mkvalt']))]
#construct value weight
df['wgt_mval'] = df['mkvalt'] / df['mkvalt'].groupby(level = 1).sum()
#create income yield vars
df['pi_mval_ratio'] = (df['pi']/df['mkvalt']).replace([np.inf, -np.inf], np.nan)
#winsorize
df['pi_mval_ratio_wins'] = wins_one(df['pi_mval_ratio'])
In [5]:
sel=df['fyear'].isin(scf_years)

Moments

To change to scf_years replace the variable "all_years" by "scf_years." Means are computed after winsorizing.

Pre-tax income/ Market Value for All Firms for SCF survey years

In [6]:
EW_pi_mval=(df[sel]['pi_mval_ratio_wins']).groupby(level = 1).mean()
VW_pi_mval=(df[sel]['pi_mval_ratio_wins']*df[sel]['wgt_mval']).groupby(level = 1).sum()

num=(df[sel]['pi_mval_ratio_wins']).groupby(level = 1).count()

pi_mval_mom=pd.DataFrame([num,EW_pi_mval,VW_pi_mval])
pi_mval_mom=pi_mval_mom.T
pi_mval_mom.columns=['num','EW_mean','VW_mean']

per_pi_mval=[df[sel]['pi_mval_ratio'].groupby(level = 1).quantile(p) for p in percentile]
per_head=['p90','p75','p50','p25','p10']
for p in range(len(percentile)):
    pi_mval_mom[per_head[p]]=per_pi_mval[p]

    
pi_mval_mom=pi_mval_mom[['num','EW_mean','VW_mean','p10','p25','p50','p75','p90']]
pi_mval_mom.to_csv('compustat_pi_mval_all_firms.csv')
pi_mval_mom
Out[6]:
num EW_mean VW_mean p10 p25 p50 p75 p90
fyear
1988 5268.0 -0.064754 0.124336 -0.346054 -0.063410 0.078231 0.140517 0.193712
1991 5161.0 -0.178124 0.061864 -0.489336 -0.071173 0.049283 0.103116 0.156330
1994 6788.0 -0.012227 0.098175 -0.247581 -0.017575 0.073897 0.125753 0.173712
1997 7291.0 -0.047093 0.062075 -0.263591 -0.043251 0.051859 0.090276 0.123436
2000 6396.0 -0.275789 0.046411 -0.752837 -0.118606 0.048191 0.124477 0.197480
2003 5057.0 -0.014558 0.061809 -0.195183 -0.032297 0.048114 0.087709 0.119153
2006 4813.0 0.003327 0.079718 -0.159975 -0.006003 0.054470 0.088386 0.121192
2009 4210.0 -0.162357 0.056758 -0.502311 -0.116813 0.031154 0.089554 0.135849
2012 3897.0 -0.023285 0.079632 -0.243603 -0.017992 0.059879 0.104524 0.151912
2015 4062.0 -0.148565 0.054432 -0.369993 -0.066205 0.041277 0.084278 0.120208

Pre-tax income/ Market Value for All Firms for SCF all years

In [7]:
all_years=np.arange(1988,2016)
sel=df['fyear'].isin(all_years)
EW_pi_mval=(df[sel]['pi_mval_ratio_wins']).groupby(level = 1).mean()
VW_pi_mval=(df[sel]['pi_mval_ratio_wins']*df[sel]['wgt_mval']).groupby(level = 1).sum()
num=(df[sel]['pi_mval_ratio_wins']).groupby(level = 1).count()
pi_mval_mom=pd.DataFrame([num,EW_pi_mval,VW_pi_mval])
pi_mval_mom=pi_mval_mom.T
pi_mval_mom.columns=['num','EW_mean','VW_mean']

per_pi_mval=[df[sel]['pi_mval_ratio'].groupby(level = 1).quantile(p) for p in percentile]
per_head=['p90','p75','p50','p25','p10']
for p in range(len(percentile)):
    pi_mval_mom[per_head[p]]=per_pi_mval[p]

pi_mval_mom=pi_mval_mom[['num','EW_mean','VW_mean','p10','p25','p50','p75','p90']]    
pi_mval_mom.to_csv('compustat_pi_mval_all_firms_allyears.csv')
pi_mval_mom
Out[7]:
num EW_mean VW_mean p10 p25 p50 p75 p90
fyear
1988 5268.0 -0.064754 0.124336 -0.346054 -0.063410 0.078231 0.140517 0.193712
1989 5112.0 -0.083287 0.098167 -0.394644 -0.075455 0.066430 0.129429 0.180700
1990 5057.0 -0.253553 0.100187 -0.680184 -0.107005 0.070301 0.141739 0.209138
1991 5161.0 -0.178124 0.061864 -0.489336 -0.071173 0.049283 0.103116 0.156330
1992 5321.0 -0.054414 0.067641 -0.289328 -0.045008 0.057654 0.102387 0.149803
1993 6416.0 -0.014024 0.072135 -0.217731 -0.028665 0.059317 0.107833 0.154211
1994 6788.0 -0.012227 0.098175 -0.247581 -0.017575 0.073897 0.125753 0.173712
1995 6931.0 -0.010322 0.081934 -0.219226 -0.021353 0.062189 0.111551 0.152348
1996 7344.0 -0.018439 0.076935 -0.220585 -0.029329 0.056115 0.105430 0.144862
1997 7291.0 -0.047093 0.062075 -0.263591 -0.043251 0.051859 0.090276 0.123436
1998 6894.0 -0.067693 0.049626 -0.355223 -0.064357 0.055000 0.104075 0.154980
1999 6670.0 -0.011915 0.048989 -0.240048 -0.036718 0.055105 0.124291 0.184916
2000 6396.0 -0.275789 0.046411 -0.752837 -0.118606 0.048191 0.124477 0.197480
2001 5741.0 -0.287146 0.023116 -0.778434 -0.144048 0.031644 0.095596 0.144780
2002 5349.0 -0.244145 0.048600 -0.762450 -0.132552 0.053505 0.105957 0.154910
2003 5057.0 -0.014558 0.061809 -0.195183 -0.032297 0.048114 0.087709 0.119153
2004 5006.0 0.003801 0.066693 -0.153550 -0.010370 0.051821 0.084721 0.114044
2005 4906.0 0.001457 0.077524 -0.155835 -0.009043 0.053785 0.089708 0.119634
2006 4813.0 0.003327 0.079718 -0.159975 -0.006003 0.054470 0.088386 0.121192
2007 4692.0 -0.039159 0.069316 -0.245041 -0.028240 0.051449 0.095040 0.132419
2008 4437.0 -0.533831 0.038887 -1.429045 -0.286159 0.037256 0.112995 0.183122
2009 4210.0 -0.162357 0.056758 -0.502311 -0.116813 0.031154 0.089554 0.135849
2010 4082.0 -0.029481 0.082566 -0.214753 -0.026524 0.052337 0.096034 0.145340
2011 3966.0 -0.036848 0.093309 -0.276506 -0.018006 0.062569 0.108387 0.159660
2012 3897.0 -0.023285 0.079632 -0.243603 -0.017992 0.059879 0.104524 0.151912
2013 3963.0 -0.009317 0.071341 -0.162604 -0.019996 0.046645 0.081738 0.116978
2014 4106.0 -0.033093 0.066426 -0.205406 -0.030264 0.044036 0.082323 0.116619
2015 4062.0 -0.148565 0.054432 -0.369993 -0.066205 0.041277 0.084278 0.120208

Pre-tax income/ Market Value for "Small" Firms

Small by market cap, cutoff bottom 20%

In [8]:
x=0.2
var='mkvalt'
var_10=var+'_10'
tmp = df.merge(how = 'left', right = df[var].groupby(level = 1).quantile(x).to_frame(var_10), left_index = True, right_index = True)
df_p10 = df[df[var] < tmp[var_10]]
df_p10['wgt_mval'] = df_p10['mkvalt'] / df_p10['mkvalt'].groupby(level = 1).sum()

sel_10=df_p10['fyear'].isin(scf_years)
EW_pi_mval=(df_p10[sel_10]['pi_mval_ratio_wins']).groupby(level = 1).mean()
num=(df_p10[sel_10]['pi_mval_ratio_wins']).groupby(level = 1).count()

VW_pi_mval=(df_p10[sel_10]['pi_mval_ratio_wins']*df_p10[sel_10]['wgt_mval']).groupby(level = 1).sum()
pi_mval_mom=pd.DataFrame([num,EW_pi_mval,VW_pi_mval])
pi_mval_mom=pi_mval_mom.T
pi_mval_mom.columns=['num','EW_mean','VW_mean']
per_pi_mval=[df_p10[sel_10]['pi_mval_ratio'].groupby(level = 1).quantile(p) for p in percentile]
per_head=['p90','p75','p50','p25','p10']
for p in range(len(percentile)):
    pi_mval_mom[per_head[p]]=per_pi_mval[p]
    
pi_mval_mom=pi_mval_mom[['num','EW_mean','VW_mean','p10','p25','p50','p75','p90']]
pi_mval_mom.to_csv('compustat_pi_mval_small_firms_mc.csv')
pi_mval_mom

C:\Users\admin\Anaconda3\lib\site-packages\ipykernel_launcher.py:6: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
Out[8]:
num EW_mean VW_mean p10 p25 p50 p75 p90
fyear
1988 1053.0 -0.435957 -0.269967 -1.565283 -0.523336 -0.142824 0.060863 0.178730
1991 1032.0 -0.728548 -0.489911 -2.370858 -0.723946 -0.159246 0.050776 0.173996
1994 1357.0 -0.233424 -0.142300 -0.981368 -0.340904 -0.041099 0.092934 0.176146
1997 1458.0 -0.298704 -0.192368 -1.054894 -0.432452 -0.084581 0.071404 0.136607
2000 1279.0 -1.041485 -0.718157 -3.452889 -1.034324 -0.164402 0.104018 0.226318
2003 1012.0 -0.142035 -0.091790 -0.597383 -0.210495 -0.009138 0.077880 0.132970
2006 963.0 -0.120773 -0.080836 -0.536028 -0.207689 -0.001637 0.076156 0.115198
2009 842.0 -0.650399 -0.472915 -2.124260 -0.724000 -0.225010 0.047351 0.135825
2012 780.0 -0.226935 -0.126461 -0.952222 -0.355635 -0.037616 0.103965 0.180540
2015 813.0 -0.595741 -0.355661 -1.721846 -0.554069 -0.115282 0.063014 0.126491

Small by book value of assets, cutoff bottom 20%

In [9]:
x=0.2
var='at'
var_10=var+'_10'
tmp = df.merge(how = 'left', right = df[var].groupby(level = 1).quantile(x).to_frame(var_10), left_index = True, right_index = True)
df_p10 = df[df[var] < tmp[var_10]]
df_p10['wgt_mval'] = df_p10['mkvalt'] / df_p10['mkvalt'].groupby(level = 1).sum()

sel_10=df_p10['fyear'].isin(scf_years)
EW_pi_mval=(df_p10[sel_10]['pi_mval_ratio_wins']).groupby(level = 1).mean()
num=(df_p10[sel_10]['pi_mval_ratio_wins']).groupby(level = 1).count()

VW_pi_mval=(df_p10[sel_10]['pi_mval_ratio_wins']*df_p10[sel_10]['wgt_mval']).groupby(level = 1).sum()
pi_mval_mom=pd.DataFrame([num,EW_pi_mval,VW_pi_mval])
pi_mval_mom=pi_mval_mom.T
pi_mval_mom.columns=['num','EW_mean','VW_mean']
per_pi_mval=[df_p10[sel_10]['pi_mval_ratio'].groupby(level = 1).quantile(p) for p in percentile]
per_head=['p90','p75','p50','p25','p10']
for p in range(len(percentile)):
    pi_mval_mom[per_head[p]]=per_pi_mval[p]
    
pi_mval_mom=pi_mval_mom[['num','EW_mean','VW_mean','p10','p25','p50','p75','p90']]
pi_mval_mom.to_csv('compustat_pi_mval_small_firms_at.csv')
pi_mval_mom.describe()

C:\Users\admin\Anaconda3\lib\site-packages\ipykernel_launcher.py:6: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
Out[9]:
num EW_mean VW_mean p10 p25 p50 p75 p90
count 10.000000 10.000000 10.000000 10.000000 10.000000 10.000000 10.000000 10.000000
mean 1059.100000 -0.266135 -0.085223 -0.788456 -0.290212 -0.077406 0.039844 0.117146
std 234.278301 0.138012 0.031014 0.343385 0.098433 0.030928 0.012568 0.019075
min 780.000000 -0.571285 -0.147961 -1.575370 -0.491529 -0.126786 0.013469 0.087592
25% 872.250000 -0.292368 -0.093957 -0.793171 -0.328824 -0.100541 0.033734 0.105164
50% 1022.500000 -0.252606 -0.078663 -0.748656 -0.270087 -0.075395 0.042833 0.119906
75% 1222.750000 -0.183799 -0.063437 -0.619515 -0.236093 -0.052365 0.046884 0.124984
max 1458.000000 -0.115641 -0.051541 -0.412865 -0.163783 -0.033811 0.058921 0.154929

Small by revenues, cutoff bottom 20%

In [10]:
x=0.2
var='sale'
var_10=var+'_10'
tmp = df.merge(how = 'left', right = df[var].groupby(level = 1).quantile(x).to_frame(var_10), left_index = True, right_index = True)
df_p10 = df[df[var] < tmp[var_10]]
df_p10['wgt_mval'] = df_p10['mkvalt'] / df_p10['mkvalt'].groupby(level = 1).sum()

sel_10=df_p10['fyear'].isin(scf_years)
EW_pi_mval=(df_p10[sel_10]['pi_mval_ratio_wins']).groupby(level = 1).mean()
num=(df_p10[sel_10]['pi_mval_ratio_wins']).groupby(level = 1).count()

VW_pi_mval=(df_p10[sel_10]['pi_mval_ratio_wins']*df_p10[sel_10]['wgt_mval']).groupby(level = 1).sum()
pi_mval_mom=pd.DataFrame([num,EW_pi_mval,VW_pi_mval])
pi_mval_mom=pi_mval_mom.T
pi_mval_mom.columns=['num','EW_mean','VW_mean']
per_pi_mval=[df_p10[sel_10]['pi_mval_ratio'].groupby(level = 1).quantile(p) for p in percentile]
per_head=['p90','p75','p50','p25','p10']
for p in range(len(percentile)):
    pi_mval_mom[per_head[p]]=per_pi_mval[p]
    
pi_mval_mom=pi_mval_mom[['num','EW_mean','VW_mean','p10','p25','p50','p75','p90']]
pi_mval_mom.to_csv('compustat_pi_mval_small_firms_sale.csv')
pi_mval_mom

C:\Users\admin\Anaconda3\lib\site-packages\ipykernel_launcher.py:6: SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame.
Try using .loc[row_indexer,col_indexer] = value instead

See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy
Out[10]:
num EW_mean VW_mean p10 p25 p50 p75 p90
fyear
1988 1053.0 -0.271516 -0.087859 -0.783450 -0.263373 -0.086195 0.011276 0.091830
1991 1033.0 -0.316878 -0.060428 -0.783600 -0.233217 -0.055910 0.015324 0.085937
1994 1358.0 -0.180845 -0.092426 -0.582123 -0.248436 -0.066251 0.039988 0.112247
1997 1458.0 -0.210752 -0.084573 -0.602096 -0.254129 -0.079940 0.026520 0.091064
2000 1279.0 -0.528326 -0.123624 -1.340882 -0.421905 -0.106520 0.021648 0.130146
2003 1012.0 -0.095050 -0.072096 -0.353032 -0.152140 -0.032932 0.055473 0.095537
2006 963.0 -0.118586 -0.086290 -0.405136 -0.185503 -0.050708 0.046732 0.091290
2009 842.0 -0.325955 -0.109754 -0.920999 -0.345912 -0.108436 0.029735 0.107952
2012 780.0 -0.170875 -0.057265 -0.550771 -0.226889 -0.054113 0.067297 0.134526
2015 813.0 -0.375906 -0.114877 -1.032843 -0.357929 -0.119295 0.019283 0.087611