* code for distilling data from TF Insiders *

cd "C:\Documents and Settings\Holger\My Documents\synced\text\HLS-CorpGovProgram\bankerspay\BearLehman\Data_code_graphs\documentation" // change the path as necessary

*** this cleans and augments the basic data set ***

keep if personid==2459 | personid==5944 | personid==12203300 | personid==14006 | personid==14847 | personid==12526482 | personid==16011101 | personid==12526485 | personid==16127534 | personid==13582 // this eliminates all transactions but those of ten executives covered in the paper

replace owner=word(owner,1)
replace owner="OMEARA" if owner=="O"

gen year=year(trandate)
label variable year "transaction year"
drop if year<2000

gen month=mofd(trandate)
label variable month "transaction month"

merge month using CPI, sort uniqusing nokeep

generate acqdisp_num=1 if acqdisp=="A"
replace acqdisp_num=-1 if acqdisp=="D"
label variable acqdisp_num "numerically encoded acquisition (+) / disposition (-) flag"

generate acqdisp_num_ar=1 if acqdisp_ar=="A"
replace acqdisp_num_ar=-1 if acqdisp_ar=="D"
label variable acqdisp_num_ar "numerically encoded acquisition (+) / disposition (-), as reported"

gen shares_signed=acqdisp_num*shares
label variable shares_signed "delta shares per transaction"

gen shares_signed_ar=acqdisp_num_ar*shares
label variable shares_signed_ar "delta shares per transaction, as reported"

gen shares_adj_signed=acqdisp_num*shares_adj
label variable shares_signed "delta shares per transaction, adjusted for splits"

gen shares_adj_signed_ar=acqdisp_num_ar*shares_adj
label variable shares_signed_ar "delta shares per transaction, adjusted for splits, as reported"

gen shares_adj_signed_min=min(shares_adj_signed,shares_adj_signed_ar)
label variable shares_adj_signed_min "delta shares p. t-a, adj for splits, min(reported,cleansed)"

gen defl_cashflow= - shares_signed* tprice*100/CPI_U_Jan09
label variable defl_cashflow "cashflow deflated by CPI-U (Jan 09=100)"

gen defl_cashflow_ar= - shares_signed_ar* tprice_ar*100/CPI_U_Jan09
label variable defl_cashflow "cashflow deflated by CPI-U (Jan 09=100)"

gen defl_cashflow_min=min(defl_cashflow,defl_cashflow_ar)
label variable defl_cashflow_min "cashflow deflated by CPI-U (Jan 09=100), min(reported,cleansed)"

save insiderdata, replace


*** creates a data set of sales 2000-2007 for top-5, controlling for purchases ***

keep if (acqdisp=="D" | (acqdisp=="A" & trancode=="P")) & mi(tprice)==0
drop if year>=2008
collapse (sum) shares_adj_signed_min, by(owner)
gen shares_adj = - shares_adj_signed_min
drop shares_adj_signed_min
gen id=1
reshape wide shares_adj, i(id) j(owner) string
renpfix shares_adj
drop id
order CAYNE GREENBERG MOLINARO SCHWARTZ SPECTOR FULD GOLDFARB GREGORY OMEARA RUSSO

outsheet using shares_sold.csv, comma replace nolabel


*** creates a data set of net cashflows by person, year, and firm ***

use insiderdata, clear

collapse (sum) defl_cashflow_min, by(owner year)

format defl_cashflow_min %12.0f
reshape wide defl_cashflow_min, i(year) j(owner) string
renpfix defl_cashflow_min
sort year
order year CAYNE GREENBERG MOLINARO SCHWARTZ SPECTOR FULD GOLDFARB GREGORY OMEARA RUSSO

outsheet using cashflows.csv, comma replace nolabel

use insiderdata, clear


* the end *