Skip to content

brycewang-stanford/Auto-Empirical-Research-Skills / skills/18-jusi-aalto-stata-accounting-research

stata-accounting-research

Unverified

STATA code pattern library for empirical archival accounting research. Provides tested syntax from 126 peer-reviewed JAR (Journal of Accounting Research) replication files (2017-2025). Use when the user asks procedural questions like "How do I implement [method]?" or "Show me code for [technique]" — including: entropy balancing, propensity score matching (PSM), difference-in-differences (DiD), regression discontinuity (RDD), instrumental variables (IV), event studies (CAR/BHAR), survival analysis, Fama-MacBeth regressions, bootstrap, quantile regression, reghdfe/xtreg/areg, clustering standard errors, fixed effects, esttab/outreg2 table formatting, winsorization, leads/lags. Users can specify their variables (e.g., treatment, outcomes, controls) and receive adapted syntax. NOTE: This skill provides code patterns from published papers, not research design advice.

FileKindSize
README-original.mdmarkdown2.9 KB
SKILL.mdskill md4.8 KB
references/JAR_55_bbcl.doother59.1 KB
references/JAR_55_chaplinksy-hanley-moon.doother46.3 KB
references/JAR_55_clm.doother95.1 KB
references/JAR_55_elw.doother58.0 KB
references/JAR_55_hlsw.doother104.1 KB
references/JAR_55_kks.doother365.5 KB
references/JAR_55_lzz.doother25.5 KB
references/JAR_55_madsen.doother307.5 KB
references/JAR_55_mr.doother41.5 KB
references/JAR_56_al.doother75.3 KB
references/JAR_56_bch.doother13.5 KB
references/JAR_56_blv.doother24.6 KB
references/JAR_56_csmw.doother27.6 KB
references/JAR_56_en.doother21.2 KB
references/JAR_56_granja.doother107.9 KB
references/JAR_56_htw.doother18.6 KB
references/JAR_56_kmt.doother5.6 KB
references/JAR_56_ksvw.doother8.0 KB
references/JAR_56_llz.doother3.1 KB
references/JAR_56_ls.doother147.5 KB
references/JAR_56_lv2018.doother123.7 KB
references/JAR_57_bht.doother24.4 KB
references/JAR_57_bl.doother22.6 KB
references/JAR_57_bw.doother263.2 KB
references/JAR_57_cct.doother347.1 KB
references/JAR_57_cgw.doother195.8 KB
references/JAR_57_hcp.doother38.0 KB
references/JAR_57_honigsberg.doother99.5 KB
references/JAR_57_lehmann.doother112.8 KB
references/JAR_57_lm.doother85.5 KB
references/JAR_58_bsz.doother36.9 KB
references/JAR_58_ckm.doother52.3 KB
references/JAR_58_eyring.doother58.1 KB
references/JAR_58_gj.doother187.5 KB
references/JAR_58_gsz.doother121.6 KB
references/JAR_58_htyz.doother30.9 KB
references/JAR_58_jansen.doother99.5 KB
references/JAR_58_joshi.doother43.0 KB
references/JAR_58_kvy.doother19.5 KB
references/JAR_58_msz.doother35.0 KB
references/JAR_58_rauter.doother433.2 KB
references/JAR_58_wy.doother64.5 KB
references/JAR_58_zz.doother125.5 KB
references/JAR_59_breuer.doother826.9 KB
references/JAR_59_ctv.doother383.7 KB
references/JAR_59_dess.doother78.4 KB
references/JAR_59_hmo.doother351.6 KB
references/JAR_60_abkp.doother129.9 KB
references/JAR_60_alv.doother35.4 KB
references/JAR_60_at.doother38.5 KB
references/JAR_60_barrios.doother119.6 KB
references/JAR_60_bbin.doother72.9 KB
references/JAR_60_bdem.doother11.6 KB
references/JAR_60_bl.doother43.5 KB
references/JAR_60_bmsw.doother7.7 KB
references/JAR_60_cc.doother20.1 KB
references/JAR_60_cpss.doother15.4 KB
references/JAR_60_cstv.doother175.3 KB
references/JAR_60_dg.doother19.9 KB
references/JAR_60_duguay.doother8.0 KB
references/JAR_60_fhl.doother71.3 KB
references/JAR_60_gls.doother37.0 KB
references/JAR_60_hmryz.doother28.6 KB
references/JAR_60_hst.doother18.3 KB
references/JAR_60_kt.doother10.4 KB
references/JAR_60_mliu.doother28.3 KB
references/JAR_60_nrwx.doother14.6 KB
references/JAR_60_ptwy.doother26.6 KB
references/JAR_60_skim.doother14.7 KB
references/JAR_61_bcdr.doother524.9 KB
references/JAR_61_bkr.doother51.6 KB
references/JAR_61_bls.doother79.2 KB
references/JAR_61_bo.doother29.7 KB
references/JAR_61_ccm.doother48.7 KB
references/JAR_61_cdss.doother3.9 KB
references/JAR_61_cfhll.doother47.1 KB
references/JAR_61_cfmp.doother12.7 KB
references/JAR_61_ckor.doother77.5 KB
references/JAR_61_cllw.doother11.2 KB
references/JAR_61_clm.doother11.0 KB
references/JAR_61_deller.doother368.9 KB
references/JAR_61_dlrtw.doother30.6 KB
references/JAR_61_dlz.doother13.6 KB
references/JAR_61_drs.doother15.8 KB
references/JAR_61_ds.doother197.5 KB
references/JAR_61_glt.doother1.9 KB
references/JAR_61_gyz.doother7.5 KB
references/JAR_61_ll.doother89.3 KB
references/JAR_61_lszz.doother15.9 KB
references/JAR_61_os.doother128.9 KB
references/JAR_61_zureich.doother22.2 KB
references/JAR_62_1_bdsy_banerjee_etal.doother6.6 KB
references/JAR_62_1_bhs_bouwens_etal.doother59.7 KB
references/JAR_62_1_gsz_gu_sun_zhou.doother46.6 KB
references/JAR_62_2_dgzz_defranco_etal.doother1.8 KB
references/JAR_62_2_gmp_grewal_etal.doother37.2 KB
references/JAR_62_2_xyz_xin_etal.doother25.3 KB
references/JAR_62_3_bht_bloomfield_etal.doother16.0 KB
references/JAR_62_3_ccz_cheynel_etal.doother23.3 KB
references/JAR_62_3_kim.doother2.6 KB
references/JAR_62_3_rr_raghunandan_ruchti.doother15.5 KB
references/JAR_62_4_bd_breuer_dehaan.doother15.3 KB
references/JAR_62_4_cc_cadman_carrizosa.doother20.1 KB
references/JAR_62_4_kmsv_kohler_etal.doother91.5 KB
references/JAR_62_4_mps_marra_etal.doother68.0 KB
references/JAR_62_4_raghunandan.doother50.0 KB
references/JAR_62_4_stv_sran_etal.doother41.1 KB
references/JAR_62_4_xu.doother49.4 KB
references/JAR_62_5_aov_abraham_etal.doother418.9 KB
references/JAR_62_5_gibbons.doother80.1 KB
references/JAR_62_5_jiang.doother114.3 KB
references/JAR_62_5_kstz_krueger_etal.doother26.7 KB
references/JAR_62_5_llz_li_lou_zhang.doother6.2 KB
references/JAR_62_5_lrst_lins_etal.doother66.2 KB
references/JAR_62_5_lswy_lin_etal.doother85.2 KB
references/JAR_62_5_lw_li_wang.doother16.6 KB
references/JAR_63_1_blmv_bastiaansen_etal.doother18.5 KB
references/JAR_63_1_cvw_chen_etal.doother61.2 KB
references/JAR_63_1_fgr_frankel_etal.doother30.3 KB
references/JAR_63_1_hrsx_huang_etal.doother35.5 KB
references/JAR_63_1_jia.doother12.0 KB
references/JAR_63_2_bcrw_bakke_etal.doother271.3 KB
references/JAR_63_2_dlr_desimone_etal.doother83.5 KB
references/JAR_63_2_mqtz_mao_etal.doother94.2 KB
references/JAR_63_2_npstv_nessa_etal.doother7.0 KB
references/JAR_63_3_breuer.doother51.4 KB
references/REFERENCES.mdmarkdown30.7 KB

references/JAR_57_lm.do

View on GitHub
*****************************************************************************************************
*****************************************************************************************************
*****      The follwing codes replicate the Main Samples in "Financial Gatekeepers and   	    ***** 
*****        Investor Protection: Evidence from Criminal Background Checks"          	        ***** 
*****        by Kelvin K. F. Law and Lillian F. Mills (2019, Journal of Accounting Research)	***** 
*****                                    Version: March 8, 2019									***** 
*****************************************************************************************************
*****************************************************************************************************
*The following codes construct main advisor sample
*Read raw FINRA employment data 
	use "D:\Dropbox\19-03-08 JAR Replications\advisor\employment_combined.dta", clear
*Construct advisor-year panel
	keep individualid firmid begin end zip5
	gduplicates drop _all, force
	gsort individualid begin firmid 
	drop if begin==.
	gen begyear = year(begin)
	gen endyear = year(end)
	gen length = endyear - begyear + 1
	gen recordid = _n
	expand(length)
	gsort recordid
	by recordid: gen id = _n
	gen year = begyear + id - 1
	rename id tenure
	drop if year(end)<year & end!=. & year!=.
	drop begyear endyear length
	gduplicates drop _all, force
	gsort recordid year begin 
*Years in profession
	by individualid, sort: egen since = min(year)
	gen genexp = year - since + 1 if since>=1946
	gsort recordid year begin 
*Firm size
	preserve
	keep year firmid individualid 
	gduplicates drop year firmid individualid, force
	gen dum = 1
	gcollapse (sum) firmsize = dum, by(firmid year)
	tempfile temp1
	save "`temp1'", replace
	restore
	merge m:1 firmid year using "`temp1'"
	drop if _merge==2
	drop _merge
	gsort recordid year begin 
*Branch size
	preserve
	keep year firmid individualid zip5
	drop if zip5==.
	drop if firmid==.
	gduplicates drop year firmid individualid zip5, force
	gen dum = 1
	gcollapse (sum) branchsize = dum, by(firmid zip5 year)
	tempfile temp2
	save "`temp2'", replace
	restore
	merge m:1 firmid zip5 year using "`temp2'"
	drop if _merge==2
	drop _merge
	gsort recordid year begin 
*One advisor obs per year
	egen groupid = group(individualid year)
	sort groupid firmsize
	by groupid: gen id = _n
	by groupid: egen maxid = max(id)
	keep if maxid==id
	drop groupid maxid id
	gsort recordid year begin 
	gduplicates drop individualid year, force
*Gender
	merge m:1 individualid using "D:\Dropbox\19-03-08 JAR Replications\advisor\Individual ID gender data.dta"
	drop if _merge==2
	drop _merge
	gsort individualid year
*Basic information (e.g., names, exam, bar status)
	merge m:1 individualid using "D:\Dropbox\19-03-08 JAR Replications\advisor\basicinformation.dta"
	drop if _merge==2
	drop _merge
	gsort individualid year
*Criminal records
	gen individual_id = individualid 
	merge m:1 individualid using "D:\Dropbox\19-03-08 JAR Replications\advisor\All criminals.dta" 
	drop if _merge==2
	drop _merge
	replace criminal = 0 if criminal==.
	gsort individualid year
*Exclude financial advisors deregistered between 1971 and 2006
	merge m:1 individualid using "D:\Dropbox\19-03-08 JAR Replications\advisor\List of excluded advisors deregistered between 1971 and 2006.dta"
	drop if _merge==2
	drop _merge
	drop if excluded==1
	gsort individualid year
*Exclude not-in-scope financial advisors
	merge m:1 individualid using "D:\Dropbox\19-03-08 JAR Replications\advisor\Not in scope financial advisors.dta"
	drop if _merge==2
	drop _merge
	drop if notinscope==1
	gsort individualid year
	save "D:\Dropbox\19-03-08 JAR Replications\advisor\Main Advisor Sample.dta"
*****************************************************************************************************
*****************************************************************************************************
*The following codes construct main advisory firm sample
*Read raw FINRA advisory firm data 
	use "D:\Dropbox\19-03-08 JAR Replications\firm\basicInformation.dta", clear
	drop if bcscope==""
	gen firmid = firm_id
	gsort firmid
*Get number of advisors with criminal records per firm
	merge 1:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\Num advisor with criminal per firm.dta"
	drop if _merge==2
	drop _merge
	gen lnnumadvisor = ln(numadvisor)
	gen lnnumcriminal = ln(1+numcriminal)
*Small firm
	gen small = 0
	replace small = 1 if numadvisor<=150
	replace small = 1 if firmsize=="Small"
*Formation year
	gen formyear = substr(formeddate,-4,.)
	destring(formyear), replace
*Firm expelled
	gen cancel = 0
	replace cancel = 1 if strpos(sanctions_sanctiondetails_messag,"cancel")
	gen expel = 0
	replace expel = 1 if strpos(sanctions_sanctiondetails_messag,"expel")
	replace expel = 1 if expelleddate!=. & expel==0
	gen expel_or_cancel = 0
	replace expel_or_cancel = 1 if cancel==1
	replace expel_or_cancel = 1 if expel==1
*Corporation status
	gen corp = 0
	replace corp = 1 if firmtype=="Corporation"
*Zip code
	merge 1:1 firm_id using "D:\Dropbox\19-03-08 JAR Replications\firm\firmAddressDetails.dta"
	drop if _merge==2
	drop _merge
	gsort firmid
*Address
	merge 1:1 firm_id using "D:\Dropbox\19-03-08 JAR Replications\firm\iaFirmAddressDetails.dta", update
	drop if _merge==2
	drop _merge
	gsort firmid
	duplicates drop _all, force
*Registration
	merge 1:1 firm_id using "D:\Dropbox\19-03-08 JAR Replications\firm\registrations.dta"
	drop if _merge==2
	drop _merge
	replace mailingaddress_postalcode = officeaddress_postalcode if mailingaddress_postalcode=="" & officeaddress_postalcode!=""
	gsort firmid
*Branch location
	merge m:1 firm_id using "D:\Dropbox\19-03-08 JAR Replications\firm\Find location based on branch office.dta"
	drop if _merge==2
	drop _merge
	gsort firmid
*Zip code
	gen zip5 = substr(mailingaddress_postalcode,1,5)
	gen zip3 = substr(mailingaddress_postalcode,1,3)
	destring(zip3), replace force
	destring(zip5), replace force
	replace zip5 = branchoffice_zipcode if zip5==. & branchoffice_zipcode!=.
	rename zip5 zip
	rename state stateabbr
*State, County, and MSA FIPS
	merge m:1 zip using "D:\Dropbox\19-03-08 JAR Replications\firm\zipcode.dta", keepusing(x y city state statecode county msa)
	drop if _merge==2
	drop _merge
	gen statecounty = state*1000 + county
	gsort firmid
*City ID
	egen cityid = group(city)
*State ID
	egen formstate_id = group(formedstate)
*Control variables
	gen referral = 0
	replace referral = 1 if referotherbd=="N"
	gen affil = 0
	replace affil=1 if hasaffliation=="Y"
	foreach var in referral affil businesstypecount approvedstateregistrationcount{
		replace `var' = 0 if `var'==.
	}
	gen lnbusinesstypecount = ln(1+businesstypecount)
	gen lnstatereg = ln(1+approvedstateregistrationcount)
*Identify firms that hire advisors with pre-advisor criminal records
	merge 1:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\List of firm which hire advisor with pre-advisor criminal records.dta"
	drop if _merge==2
	drop _merge
	replace firmhire_preadvisor_criminal=0 if firmhire_preadvisor_criminal==.
	gsort firmid
*Identify the fraction of advisors with pre-advisor criminal records
	merge 1:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\Fraction of advisors with pre-advisor criminal records.dta"
	drop if _merge==2
	drop _merge
	gsort firmid
*SEC ADV form 2017 Data
	merge m:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\2017 Data cleaned.dta" 
	drop if _merge==2
	drop _merge
	gsort firmid
*Retail
	merge m:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\Identify retail investment advisers based on 2010-2017 data.dta"
	drop if _merge==2
	drop _merge
	gsort firmid
*Number of firm regulatory disclosure
	merge m:1 firm_id using "D:\Dropbox\19-03-08 JAR Replications\firm\Regulatory event per firm active only.dta"
	drop if _merge==2
	drop _merge
	replace disclosurecount = 0 if disclosurecount==.
	gen lndisclosurecount = ln(1+disclosurecount)
	gsort firmid
*High-risk brokerage
	gen regfirm_90 = 0
	replace regfirm_90 = 1 if disclosurecount>=9 & disclosurecount!=.
*Fee structure
	merge m:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\Identify fee structure.dta"
	drop if _merge==2
	drop _merge
	gsort firmid
*Number of accounts
	gen num_of_accounts = 0
	replace num_of_accounts = _5f_2_f if _5f_2_f!=.
	gen mil_num_of_accounts = num_of_accounts/1000000
*Missing variable indicator
	gen missing_indicator = 0
	replace missing_indicator = 1 if retail==.
	save "D:\Dropbox\19-03-08 JAR Replications\firm\Main Advisory Firm Sample.dta"
*****************************************************************************************************
*****************************************************************************************************
*****      The follwing codes replicate Tables 1-10 in "Financial Gatekeepers and Investor  	***** 
*****        Protection: Evidence from Criminal Background Checks" by Kelvin K. F. Law         	***** 
*****                 and Lillian F. Mills (2019, Journal of Accounting Research) 				***** 
*****                                    Version: March 8, 2019									***** 
*****************************************************************************************************
*****************************************************************************************************
log using "D:\Dropbox\19-03-08 JAR Replications\Tables 1-10"
global path "D:\Dropbox\19-03-08 JAR Replications"
*Table 1A: Overview of Criminal Records: By Year
	use "$path\Data for Table 01A.dta", clear
	levelsof year, local(levelofyear) clean
*Column 1: #Financial advisors with criminal records by year
	forvalues k = 2007(1)2017 {
		gdistinct individualid if criminal==1 & year==`k'
	}
	distinct individualid if criminal==1
*Column 2: #Financial advisors by year
	forvalues k = 2007(1)2017 {
		gdistinct individualid if year==`k'
	}
	distinct individualid
*Column 3: #Criminal records that are felony by year
	forvalues k = 2007(1)2017 {
		gdistinct individualid if felony==1 & year==`k'
	}
	distinct individualid if felony==1
	forvalues k = 2007(1)2017 {
		gdistinct individualid if misdemeanor==1 & year==`k'
	}
	distinct individualid if misdemeanor==1
*Column 4: #Male financial advisors by year
	forvalues k = 2007(1)2017 {
		gdistinct individualid if male==1 & year==`k'
	}
	gdistinct individualid if male==1
*Column 4: #Male financial advisors with criminal records by year
	forvalues k = 2007(1)2017 {
		gdistinct individualid if male==1 & criminal==1 & year==`k'
	}
	gdistinct individualid if male==1 & criminal==1
*****************************************************************************************************
*Table 1B: Overview of Criminal Records: By Charge
	use "$path\Data for Table 01B.dta", clear
*Column 1: # Criminal records
	foreach var in theft fraud substance violent traffic public others{
		sum `var' if `var'==1
	}
	tab charge_type
*Column 2: % Felony
	foreach var in theft fraud substance violent traffic public others{
		sum `var' if `var'==1 & charge_type=="FELONY"
	}
	sum dum if charge_type=="FELONY"
*Column 3: % Misdemeanor
	foreach var in theft fraud substance violent traffic public others{
		sum `var' if `var'==1 & charge_type=="MISDEMEANOR"
	}
	sum dum if charge_type=="MISDEMEANOR"
*Column 4: % Dismissed
	foreach var in theft fraud substance violent traffic public others{
		sum `var' if `var'==1 & dismissed==1
	}
	sum dismissed
*Column 5: % Male
	foreach var in theft fraud substance violent traffic public others{
		sum `var' if `var'==1 & male==1
	}
	sum male
*****************************************************************************************************
*Table 1C: Overview of Criminal Records: By Geography
*Top 5 cities: more than 2,000 advisors
	use "$path\Data for Table 01C.dta", clear
	keep if numadvisor>=2000
	gsort -ratio_criminal 
	gen id = _n
	keep if id<=5
	drop id
	list
*Top 5 cities: less than 2,000 advisors
	use "$path\Data for Table 01C.dta", clear
	keep if criminal>=30 & numadvisor<2000
	gsort -ratio_criminal 
	drop criminal
	gen id = _n
	keep if id<=5
	drop id
	list
*****************************************************************************************************
*Table 3A: Profiling of Financial Advisors with Criminal Records: Pre- and Post-Advisor Criminal Records
	use "$path\Data for Table 03A.dta", clear
	label variable criminal 		"Criminal record"
	label variable male 			"Male"
	label variable minority 		"Minority"
	label variable hb 				"Minority (excluding Asian names)"
	label variable felony 			"Felony"
	label variable misdemeanor 		"Misdemeanor"
*Column 1
	reghdfe criminal male minority, absorb(startingyear cityid)
*Column 2
	reghdfe felony male minority, absorb(startingyear cityid)
*Column 3
	reghdfe misdemeanor male minority, absorb(startingyear cityid)
*Column 4
	reghdfe criminal male hb, absorb(startingyear cityid)
*Column 5
	reghdfe felony male hb, absorb(startingyear cityid)
*Column 6
	reghdfe misdemeanor male hb, absorb(startingyear cityid)
*Summary statistics for Table 2
	tabstat criminal felony misdemeanor male minority hb if e(sample)==1, column(statistic) statistics(mean sd p25 p50 p75 n)
*****************************************************************************************************
*Table 3B: Profiling of Financial Advisors with Criminal Records: Pre-Advisor Criminal Records Only
	use "$path\Data for Table 03B.dta", clear
	label variable preadvisor_criminal 		"Pre-advisor criminal record"
	label variable male 					"Male"
	label variable minority 				"Minority"
	label variable hb 						"Minority (excluding Asian names)"
	label variable preadvisor_felony 		"Pre-advisor felony"
	label variable preadvisor_misdemeanor 	"Pre-advisor misdemeanor"
*Column 1
	reghdfe preadvisor_criminal male minority, absorb(startingyear cityid)
*Column 2
	reghdfe preadvisor_felony male minority, absorb(startingyear cityid)
*Column 3
	reghdfe preadvisor_misdemeanor male minority, absorb(startingyear cityid)
*Column 4
	reghdfe preadvisor_criminal male hb, absorb(startingyear cityid)
*Column 5
	reghdfe preadvisor_felony male hb, absorb(startingyear cityid)
*Column 6
	reghdfe preadvisor_misdemeanor male hb, absorb(startingyear cityid)
*Summary statistics for Table 2
	tabstat preadvisor_criminal preadvisor_felony preadvisor_misdemeanor male minority hb if e(sample)==1, column(statistic) statistics(mean sd p25 p50 p75 n)
*****************************************************************************************************
*Table 4: Who Hires Financial Advisors with Criminal Records?
*Table 10B: Do Investors Face Higher Exposure to Service Disruption?: Firm Expelled
	use "$path\Data for Table 04 and 10B.dta", clear
	label variable firmhire_preadvisor_criminal 	"Hiring advisor with pre-advisor criminal record"
	label variable pct_firmhire_pre 				"% Pre-advisor criminal record at advisory firm"
	label variable highrisk_brokerage 				"High-risk brokerage"
	label variable expel_or_cancel 					"Firm expelled"
	label variable small 							"Small firm"
	label variable solo 							"Solo firm"
	label variable corp 							"Corporation"
	label variable referral 						"Referral business"
	label variable affil 							"Affiliated"
	label variable num_business_lines 				"Number of business lines (Ln)"
	label variable lnnum_state_reg 					"Number of state registrations (Ln)"
	label variable retail 							"Retail"
	label variable mil_num_of_accounts 				"Number of accounts (in million)"
	label variable prt_aum 							"Percentage of asset"
	label variable hourly 							"Hourly charge"
	label variable fixed 							"Fixed fee"
	label variable commissions 						"Commission"
	label variable performancebased 				"Performance"
	label variable missing_indicator 				"Missing indicator"
*Table 4: Column 1
	reghdfe firmhire_preadvisor_criminal highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg, cluster(zip3) absorb(cityid formyear)
*Table 4: Column 2
	reghdfe firmhire_preadvisor_criminal highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg retail mil_num_of_accounts prt_aum hourly fixed commissions performancebased missing_indicator, cluster(zip3) absorb(cityid formyear)
*Table 4: Column 3
	reghdfe pct_firmhire_pre highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg, cluster(zip3) absorb(cityid formyear)
*Table 4: Column 4
	reghdfe pct_firmhire_pre highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg retail mil_num_of_accounts prt_aum hourly fixed commissions performancebased missing_indicator, cluster(zip3) absorb(cityid formyear)
*Summary statistics
	tabstat firmhire_preadvisor_criminal pct_firmhire_pre expel_or_cancel highrisk_brokerage small solo corp referral affil num_business_lines num_state_reg if e(sample)==1, column(statistic) statistics(mean sd p25 p50 p75 n)
	tabstat retail mil_num_of_accounts prt_aum hourly fixed commissions performancebased if missing_indicator==0 & e(sample)==1, column(statistic) statistics(mean sd p25 p50 p75 n)
*Table 10B: Column 1
	reghdfe expel_or_cancel firmhire_preadvisor_criminal highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg, cluster(zip3) absorb(cityid formyear)
*Table 10B: Column 2
	reghdfe expel_or_cancel firmhire_preadvisor_criminal highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg retail mil_num_of_accounts prt_aum hourly fixed commissions performancebased missing_indicator, cluster(zip3) absorb(cityid formyear)
*Table 10B: Column 3
	reghdfe expel_or_cancel pct_firmhire_pre highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg, cluster(zip3) absorb(cityid formyear)
*Table 10B: Column 4
	reghdfe expel_or_cancel pct_firmhire_pre highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg retail mil_num_of_accounts prt_aum hourly fixed commissions performancebased missing_indicator, cluster(zip3) absorb(cityid formyear)
*****************************************************************************************************
*Table 5: Where Do Hiring Firms Cluster?
	use "$path\Data for Table 05.dta", clear
	label variable firmhire_preadvisor_criminal 	"Pre-advisor criminal record at advisory firm"
	label variable pct_firmhire_pre 				"% Pre-advisor criminal record at advisory firm"
	label variable retireperhousehold 				"Retirement income"
	label variable lnretireperhousehold 			"Retirement income (Ln)"
	label variable area_wealthy_retirees 			"Area populated with wealthy retirees"
	label variable pop 								"Population"
	label variable lnpop 							"Population (Ln)"
	label variable income 							"Income"
	label variable lnincome 						"Income (Ln)"
	label variable medianage 						"Age (median)"
	label variable malefemaleratio 					"Male-to-female ratio"
	label variable pct_white 						"% White"
	label variable collegeabove 					"% College and above"
	label variable laborforce 						"% Labor force"
*Column 1
	reghdfe firmhire_preadvisor_criminal lnretireperhousehold lnpop lnincome medianage malefemaleratio pct_white collegeabove laborforce, cluster(county_fips) absorb(state_fips formyear)
*Column 2
	reghdfe firmhire_preadvisor_criminal area_wealthy_retirees lnpop lnincome medianage malefemaleratio pct_white collegeabove laborforce, cluster(county_fips) absorb(state_fips formyear)
*Column 3
	reghdfe pct_firmhire_pre lnretireperhousehold lnpop lnincome medianage malefemaleratio pct_white collegeabove laborforce, cluster(county_fips) absorb(state_fips formyear)
*Column 4
	reghdfe pct_firmhire_pre area_wealthy_retirees lnpop lnincome medianage malefemaleratio pct_white collegeabove laborforce, cluster(county_fips) absorb(state_fips formyear)
*Summary statistics for Table 2
	tabstat retireperhousehold area_wealthy_retirees pop income medianage malefemaleratio pct_white collegeabove laborforce if e(sample)==1, column(statistics) statistics(mean sd p25 p50 p75 n)
*****************************************************************************************************
*Table 6: Predicting Post-Advisor Criminal Records
*Table 7A: Predicting Customer Complaints and Settlements: Baseline Matched Sample
*Table 10A: Do Investors Face Higher Exposure to Service Disruption?: Advisor Suspended and Advisor Barred
	use "$path\Data for Table 06, 07A, and 10A.dta", clear
	label variable futurecriminal 			"Post-advisor criminal record"
	label variable futurelien 				"Personal lien or judgment"
	label variable futurecivil 				"Civil litigation"
	label variable customer_complaint 		"Customer complaint"
	label variable complaint_with_merit 	"Complaint with merit"
	label variable settled_complaint 		"Settled complaint"
	label variable lareg_settlement 		"Large settlement"
	label variable suspend 					"Advisor suspended"
	label variable bar 						"Advisor barred"
	label variable preadvisor 				"Pre-advisor criminal record"
	label variable firmsize 				"Firm size"
	label variable lnfirmsize 				"Firm size (Ln)"
	label variable avgperjob 				"Tenure per firm"
	label variable lnavgperjob 				"Tenure per firm (Ln)"
	label variable years_in_profession 		"Years in profession"
	label variable lnyears_in_profession 	"Years in profession (Ln)"
	label variable advisor 					"Investment adviser"
	label variable stateexamcount 			"Number of state exams"
	label variable principalexamcount 		"Number of principal exams"
	label variable productexamcount 		"Number of product exams"
	label variable lapse 					"Years since pre-advisor record"
	label variable numfirm 					"Number of advisory firms"
	label variable num_separation 			"Resigned after allegations"
	label variable ownerstopexec 			"Top executive or owner"
	label variable branchid 				"Branch ID"
	label variable since 					"Cohort year"
*Table 6, column 1
	reghdfe futurecriminal preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 6, column 2
	reghdfe futurelien preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 6, column 3
	reghdfe futurecivil preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 7, panel A, column 1
	reghdfe customer_complaint preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 7, panel A, column 2
	reghdfe complaint_with_merit preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 7, panel A, column 3
	reghdfe settled_complaint preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 7, panel A, column 4
	reghdfe lareg_settlement preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 10, panel A, column 1
	reghdfe suspend preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 10, panel A, column 2
	reghdfe bar preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Summary statistics for Table 2
	tabstat preadvisor futurecriminal futurelien futurecivil customer_complaint complaint_with_merit settle lareg_settlement years_in_profession numfirm avgperjob num_separation ownerstopexec suspend bar firmsize advisor stateexamcount principalexamcount productexamcount lapse if e(sample)==1, column(statistics) statistics(mean sd p25 p50 p75 n)
	tabstat lapse if e(sample)==1 & lapse>0, column(statistics) statistics(mean sd p25 p50 p75 n)
*****************************************************************************************************
*Table 7B: Predicting Customer Complaints and Settlements: Alternative Matched Sample
	use "$path\Data for Table 07B.dta", clear
	label variable customer_complaint 		"Customer complaint"
	label variable complaint_with_merit 	"Complaint with merit"
	label variable settled_complaint 		"Settled complaint"
	label variable lareg_settlement 		"Large settlement"
	label variable preadvisor_criminal 		"Pre-advisor criminal record"
	label variable lnavgperjob 				"Tenure per firm (Ln)"
	label variable lnyears_in_profession 	"Years in profession (Ln)"
	label variable advisor 					"Investment adviser"
	label variable stateexamcount 			"Number of state exams"
	label variable principalexamcount 		"Number of principal exams"
	label variable productexamcount 		"Number of product exams"
	label variable branchid 				"Branch ID"
	label variable year 					"Year"
	label variable since 					"Cohort year"
*Column 1
	reghdfe customer_complaint preadvisor_criminal lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(year#branchid since) vce(robust)
*Column 2
	reghdfe complaint_with_merit preadvisor_criminal lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(year#branchid since) vce(robust)
*Column 3
	reghdfe settled_complaint preadvisor_criminal lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(year#branchid since) vce(robust)
*Column 4
	reghdfe lareg_settlement preadvisor_criminal lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(year#branchid since) vce(robust)
*Summary statistics for Table 2
	tabstat customer_complaint complaint_with_merit settled_complaint lareg_settlement if e(sample)==1, statistics(mean sd p25 p50 p75 n) column(statistics)
*****************************************************************************************************
*Table 8: Are Investors Compensated with Higher Returns?
	use "$path\Data for Table 08.dta", clear
	label variable ret_nohire 	"Clean (no advisors with pre-advisor criminal records)"
	label variable ret_highrisk "High (High % of advisors with pre-advisor criminal records)"
	label variable high_no 		"Clean - High"
	label variable mktrf		"Market"
	label variable smb			"Small-minus-big"
	label variable hml			"High-minus-low"
	label variable umd			"Momentum"
	label variable ps_vwf		"Liquidity"
	label variable st_rev		"Short-term reversal"
	label variable lt_rev		"Long-term reversal"
*Fama-French 3-factor model
	foreach var in ret_nohire ret_highrisk high_no {
	newey `var' mktrf smb hml, lag(4)
	}
*Carhart 4-factor model
	foreach var in ret_nohire ret_highrisk high_no {
	newey `var' mktrf smb hml umd, lag(4)
	}
*7-factor model
	foreach var in ret_nohire ret_highrisk high_no {
	newey `var' mktrf smb hml umd ps_vwf st_rev lt_rev, lag(4)
	}
*****************************************************************************************************
*Table 9, column 1: Are Investors Compensated with Lower Fees?: Expense Ratio
	use "$path\Data for Table 09_1.dta", clear
	label variable exp_ratio 	"Expense ratio"
	label variable highrisk 	"Top hiring firm"
	label variable indexfund 	"Index fund"
	label variable nav_latest 	"Assets under management"
	label variable lnnav_latest "Assets under management (Ln)"
	label variable age 			"Fund age"
	label variable lnage 		"Fund age (Ln)"
	label variable turn_ratio 	"Turnover ratio"
	label variable objid 		"Style"
	label variable year 		"Year"
	label variable crsp_fundno 	"CRSP_FUNDNO"
*Column 1
	xi: areg exp_ratio i.year highrisk indexfund lnnav_latest lnage turn_ratio, r absorb(objid) cluster(crsp_fundno)
	tabstat exp_ratio highrisk indexfund nav_latest age turn_ratio if e(sample)==1, statistics(mean sd p25 p50 p75 n) column(statistics)
*****************************************************************************************************
*Table 9, columns 2 and 3: Are Investors Compensated with Lower Fees?: Maximum Initial Purchase Charge and Average Initial Purchase Charge
	use "$path\Data for Table 09_2-3.dta", clear
	label variable maxfront 	"Maximum initial purchase charge"
	label variable meanfront 	"Average initial purchase charge"
	label variable highrisk 	"Top hiring firm"
	label variable indexfund 	"Index fund"
	label variable objid 		"Style"
	label variable vintage		"Vintage"
	label variable crsp_fundno 	"CRSP_FUNDNO"
*Column 2
	xi: areg maxfront i.vintage highrisk indexfund, r absorb(objid) cluster(crsp_fundno)
*Column 3
	xi: areg meanfront i.vintage highrisk indexfund, r absorb(objid) cluster(crsp_fundno)
*Summary statistics for Table 2
	tabstat maxfront meanfront highrisk indexfund if e(sample)==1, statistics(mean sd p25 p50 p75 n) column(statistics)
*****************************************************************************************************
log close
*****************************************************************************************************
*****************************************************************************************************
*****      The follwing codes replicate the Main Samples in "Financial Gatekeepers and   	    ***** 
*****        Investor Protection: Evidence from Criminal Background Checks"          	        ***** 
*****        by Kelvin K. F. Law and Lillian F. Mills (2019, Journal of Accounting Research)	***** 
*****                                    Version: March 8, 2019									***** 
*****************************************************************************************************
*****************************************************************************************************
*The following codes construct main advisor sample
*Read raw FINRA employment data 
	use "D:\Dropbox\19-03-08 JAR Replications\advisor\employment_combined.dta", clear
*Construct advisor-year panel
	keep individualid firmid begin end zip5
	gduplicates drop _all, force
	gsort individualid begin firmid 
	drop if begin==.
	gen begyear = year(begin)
	gen endyear = year(end)
	gen length = endyear - begyear + 1
	gen recordid = _n
	expand(length)
	gsort recordid
	by recordid: gen id = _n
	gen year = begyear + id - 1
	rename id tenure
	drop if year(end)<year & end!=. & year!=.
	drop begyear endyear length
	gduplicates drop _all, force
	gsort recordid year begin 
*Years in profession
	by individualid, sort: egen since = min(year)
	gen genexp = year - since + 1 if since>=1946
	gsort recordid year begin 
*Firm size
	preserve
	keep year firmid individualid 
	gduplicates drop year firmid individualid, force
	gen dum = 1
	gcollapse (sum) firmsize = dum, by(firmid year)
	tempfile temp1
	save "`temp1'", replace
	restore
	merge m:1 firmid year using "`temp1'"
	drop if _merge==2
	drop _merge
	gsort recordid year begin 
*Branch size
	preserve
	keep year firmid individualid zip5
	drop if zip5==.
	drop if firmid==.
	gduplicates drop year firmid individualid zip5, force
	gen dum = 1
	gcollapse (sum) branchsize = dum, by(firmid zip5 year)
	tempfile temp2
	save "`temp2'", replace
	restore
	merge m:1 firmid zip5 year using "`temp2'"
	drop if _merge==2
	drop _merge
	gsort recordid year begin 
*One advisor obs per year
	egen groupid = group(individualid year)
	sort groupid firmsize
	by groupid: gen id = _n
	by groupid: egen maxid = max(id)
	keep if maxid==id
	drop groupid maxid id
	gsort recordid year begin 
	gduplicates drop individualid year, force
*Gender
	merge m:1 individualid using "D:\Dropbox\19-03-08 JAR Replications\advisor\Individual ID gender data.dta"
	drop if _merge==2
	drop _merge
	gsort individualid year
*Basic information (e.g., names, exam, bar status)
	merge m:1 individualid using "D:\Dropbox\19-03-08 JAR Replications\advisor\basicinformation.dta"
	drop if _merge==2
	drop _merge
	gsort individualid year
*Criminal records
	gen individual_id = individualid 
	merge m:1 individualid using "D:\Dropbox\19-03-08 JAR Replications\advisor\All criminals.dta" 
	drop if _merge==2
	drop _merge
	replace criminal = 0 if criminal==.
	gsort individualid year
*Exclude financial advisors deregistered between 1971 and 2006
	merge m:1 individualid using "D:\Dropbox\19-03-08 JAR Replications\advisor\List of excluded advisors deregistered between 1971 and 2006.dta"
	drop if _merge==2
	drop _merge
	drop if excluded==1
	gsort individualid year
*Exclude not-in-scope financial advisors
	merge m:1 individualid using "D:\Dropbox\19-03-08 JAR Replications\advisor\Not in scope financial advisors.dta"
	drop if _merge==2
	drop _merge
	drop if notinscope==1
	gsort individualid year
	save "D:\Dropbox\19-03-08 JAR Replications\advisor\Main Advisor Sample.dta"
*****************************************************************************************************
*****************************************************************************************************
*The following codes construct main advisory firm sample
*Read raw FINRA advisory firm data 
	use "D:\Dropbox\19-03-08 JAR Replications\firm\basicInformation.dta", clear
	drop if bcscope==""
	gen firmid = firm_id
	gsort firmid
*Get number of advisors with criminal records per firm
	merge 1:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\Num advisor with criminal per firm.dta"
	drop if _merge==2
	drop _merge
	gen lnnumadvisor = ln(numadvisor)
	gen lnnumcriminal = ln(1+numcriminal)
*Small firm
	gen small = 0
	replace small = 1 if numadvisor<=150
	replace small = 1 if firmsize=="Small"
*Formation year
	gen formyear = substr(formeddate,-4,.)
	destring(formyear), replace
*Firm expelled
	gen cancel = 0
	replace cancel = 1 if strpos(sanctions_sanctiondetails_messag,"cancel")
	gen expel = 0
	replace expel = 1 if strpos(sanctions_sanctiondetails_messag,"expel")
	replace expel = 1 if expelleddate!=. & expel==0
	gen expel_or_cancel = 0
	replace expel_or_cancel = 1 if cancel==1
	replace expel_or_cancel = 1 if expel==1
*Corporation status
	gen corp = 0
	replace corp = 1 if firmtype=="Corporation"
*Zip code
	merge 1:1 firm_id using "D:\Dropbox\19-03-08 JAR Replications\firm\firmAddressDetails.dta"
	drop if _merge==2
	drop _merge
	gsort firmid
*Address
	merge 1:1 firm_id using "D:\Dropbox\19-03-08 JAR Replications\firm\iaFirmAddressDetails.dta", update
	drop if _merge==2
	drop _merge
	gsort firmid
	duplicates drop _all, force
*Registration
	merge 1:1 firm_id using "D:\Dropbox\19-03-08 JAR Replications\firm\registrations.dta"
	drop if _merge==2
	drop _merge
	replace mailingaddress_postalcode = officeaddress_postalcode if mailingaddress_postalcode=="" & officeaddress_postalcode!=""
	gsort firmid
*Branch location
	merge m:1 firm_id using "D:\Dropbox\19-03-08 JAR Replications\firm\Find location based on branch office.dta"
	drop if _merge==2
	drop _merge
	gsort firmid
*Zip code
	gen zip5 = substr(mailingaddress_postalcode,1,5)
	gen zip3 = substr(mailingaddress_postalcode,1,3)
	destring(zip3), replace force
	destring(zip5), replace force
	replace zip5 = branchoffice_zipcode if zip5==. & branchoffice_zipcode!=.
	rename zip5 zip
	rename state stateabbr
*State, County, and MSA FIPS
	merge m:1 zip using "D:\Dropbox\19-03-08 JAR Replications\firm\zipcode.dta", keepusing(x y city state statecode county msa)
	drop if _merge==2
	drop _merge
	gen statecounty = state*1000 + county
	gsort firmid
*City ID
	egen cityid = group(city)
*State ID
	egen formstate_id = group(formedstate)
*Control variables
	gen referral = 0
	replace referral = 1 if referotherbd=="N"
	gen affil = 0
	replace affil=1 if hasaffliation=="Y"
	foreach var in referral affil businesstypecount approvedstateregistrationcount{
		replace `var' = 0 if `var'==.
	}
	gen lnbusinesstypecount = ln(1+businesstypecount)
	gen lnstatereg = ln(1+approvedstateregistrationcount)
*Identify firms that hire advisors with pre-advisor criminal records
	merge 1:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\List of firm which hire advisor with pre-advisor criminal records.dta"
	drop if _merge==2
	drop _merge
	replace firmhire_preadvisor_criminal=0 if firmhire_preadvisor_criminal==.
	gsort firmid
*Identify the fraction of advisors with pre-advisor criminal records
	merge 1:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\Fraction of advisors with pre-advisor criminal records.dta"
	drop if _merge==2
	drop _merge
	gsort firmid
*SEC ADV form 2017 Data
	merge m:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\2017 Data cleaned.dta" 
	drop if _merge==2
	drop _merge
	gsort firmid
*Retail
	merge m:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\Identify retail investment advisers based on 2010-2017 data.dta"
	drop if _merge==2
	drop _merge
	gsort firmid
*Number of firm regulatory disclosure
	merge m:1 firm_id using "D:\Dropbox\19-03-08 JAR Replications\firm\Regulatory event per firm active only.dta"
	drop if _merge==2
	drop _merge
	replace disclosurecount = 0 if disclosurecount==.
	gen lndisclosurecount = ln(1+disclosurecount)
	gsort firmid
*High-risk brokerage
	gen regfirm_90 = 0
	replace regfirm_90 = 1 if disclosurecount>=9 & disclosurecount!=.
*Fee structure
	merge m:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\Identify fee structure.dta"
	drop if _merge==2
	drop _merge
	gsort firmid
*Number of accounts
	gen num_of_accounts = 0
	replace num_of_accounts = _5f_2_f if _5f_2_f!=.
	gen mil_num_of_accounts = num_of_accounts/1000000
*Missing variable indicator
	gen missing_indicator = 0
	replace missing_indicator = 1 if retail==.
	save "D:\Dropbox\19-03-08 JAR Replications\firm\Main Advisory Firm Sample.dta"
*****************************************************************************************************
*****************************************************************************************************
*****      The follwing codes replicate Tables 1-10 in "Financial Gatekeepers and Investor  	***** 
*****        Protection: Evidence from Criminal Background Checks" by Kelvin K. F. Law         	***** 
*****                 and Lillian F. Mills (2019, Journal of Accounting Research) 				***** 
*****                                    Version: March 8, 2019									***** 
*****************************************************************************************************
*****************************************************************************************************
log using "D:\Dropbox\19-03-08 JAR Replications\Tables 1-10"
global path "D:\Dropbox\19-03-08 JAR Replications"
*Table 1A: Overview of Criminal Records: By Year
	use "$path\Data for Table 01A.dta", clear
	levelsof year, local(levelofyear) clean
*Column 1: #Financial advisors with criminal records by year
	forvalues k = 2007(1)2017 {
		gdistinct individualid if criminal==1 & year==`k'
	}
	distinct individualid if criminal==1
*Column 2: #Financial advisors by year
	forvalues k = 2007(1)2017 {
		gdistinct individualid if year==`k'
	}
	distinct individualid
*Column 3: #Criminal records that are felony by year
	forvalues k = 2007(1)2017 {
		gdistinct individualid if felony==1 & year==`k'
	}
	distinct individualid if felony==1
	forvalues k = 2007(1)2017 {
		gdistinct individualid if misdemeanor==1 & year==`k'
	}
	distinct individualid if misdemeanor==1
*Column 4: #Male financial advisors by year
	forvalues k = 2007(1)2017 {
		gdistinct individualid if male==1 & year==`k'
	}
	gdistinct individualid if male==1
*Column 4: #Male financial advisors with criminal records by year
	forvalues k = 2007(1)2017 {
		gdistinct individualid if male==1 & criminal==1 & year==`k'
	}
	gdistinct individualid if male==1 & criminal==1
*****************************************************************************************************
*Table 1B: Overview of Criminal Records: By Charge
	use "$path\Data for Table 01B.dta", clear
*Column 1: # Criminal records
	foreach var in theft fraud substance violent traffic public others{
		sum `var' if `var'==1
	}
	tab charge_type
*Column 2: % Felony
	foreach var in theft fraud substance violent traffic public others{
		sum `var' if `var'==1 & charge_type=="FELONY"
	}
	sum dum if charge_type=="FELONY"
*Column 3: % Misdemeanor
	foreach var in theft fraud substance violent traffic public others{
		sum `var' if `var'==1 & charge_type=="MISDEMEANOR"
	}
	sum dum if charge_type=="MISDEMEANOR"
*Column 4: % Dismissed
	foreach var in theft fraud substance violent traffic public others{
		sum `var' if `var'==1 & dismissed==1
	}
	sum dismissed
*Column 5: % Male
	foreach var in theft fraud substance violent traffic public others{
		sum `var' if `var'==1 & male==1
	}
	sum male
*****************************************************************************************************
*Table 1C: Overview of Criminal Records: By Geography
*Top 5 cities: more than 2,000 advisors
	use "$path\Data for Table 01C.dta", clear
	keep if numadvisor>=2000
	gsort -ratio_criminal 
	gen id = _n
	keep if id<=5
	drop id
	list
*Top 5 cities: less than 2,000 advisors
	use "$path\Data for Table 01C.dta", clear
	keep if criminal>=30 & numadvisor<2000
	gsort -ratio_criminal 
	drop criminal
	gen id = _n
	keep if id<=5
	drop id
	list
*****************************************************************************************************
*Table 3A: Profiling of Financial Advisors with Criminal Records: Pre- and Post-Advisor Criminal Records
	use "$path\Data for Table 03A.dta", clear
	label variable criminal 		"Criminal record"
	label variable male 			"Male"
	label variable minority 		"Minority"
	label variable hb 				"Minority (excluding Asian names)"
	label variable felony 			"Felony"
	label variable misdemeanor 		"Misdemeanor"
*Column 1
	reghdfe criminal male minority, absorb(startingyear cityid)
*Column 2
	reghdfe felony male minority, absorb(startingyear cityid)
*Column 3
	reghdfe misdemeanor male minority, absorb(startingyear cityid)
*Column 4
	reghdfe criminal male hb, absorb(startingyear cityid)
*Column 5
	reghdfe felony male hb, absorb(startingyear cityid)
*Column 6
	reghdfe misdemeanor male hb, absorb(startingyear cityid)
*Summary statistics for Table 2
	tabstat criminal felony misdemeanor male minority hb if e(sample)==1, column(statistic) statistics(mean sd p25 p50 p75 n)
*****************************************************************************************************
*Table 3B: Profiling of Financial Advisors with Criminal Records: Pre-Advisor Criminal Records Only
	use "$path\Data for Table 03B.dta", clear
	label variable preadvisor_criminal 		"Pre-advisor criminal record"
	label variable male 					"Male"
	label variable minority 				"Minority"
	label variable hb 						"Minority (excluding Asian names)"
	label variable preadvisor_felony 		"Pre-advisor felony"
	label variable preadvisor_misdemeanor 	"Pre-advisor misdemeanor"
*Column 1
	reghdfe preadvisor_criminal male minority, absorb(startingyear cityid)
*Column 2
	reghdfe preadvisor_felony male minority, absorb(startingyear cityid)
*Column 3
	reghdfe preadvisor_misdemeanor male minority, absorb(startingyear cityid)
*Column 4
	reghdfe preadvisor_criminal male hb, absorb(startingyear cityid)
*Column 5
	reghdfe preadvisor_felony male hb, absorb(startingyear cityid)
*Column 6
	reghdfe preadvisor_misdemeanor male hb, absorb(startingyear cityid)
*Summary statistics for Table 2
	tabstat preadvisor_criminal preadvisor_felony preadvisor_misdemeanor male minority hb if e(sample)==1, column(statistic) statistics(mean sd p25 p50 p75 n)
*****************************************************************************************************
*Table 4: Who Hires Financial Advisors with Criminal Records?
*Table 10B: Do Investors Face Higher Exposure to Service Disruption?: Firm Expelled
	use "$path\Data for Table 04 and 10B.dta", clear
	label variable firmhire_preadvisor_criminal 	"Hiring advisor with pre-advisor criminal record"
	label variable pct_firmhire_pre 				"% Pre-advisor criminal record at advisory firm"
	label variable highrisk_brokerage 				"High-risk brokerage"
	label variable expel_or_cancel 					"Firm expelled"
	label variable small 							"Small firm"
	label variable solo 							"Solo firm"
	label variable corp 							"Corporation"
	label variable referral 						"Referral business"
	label variable affil 							"Affiliated"
	label variable num_business_lines 				"Number of business lines (Ln)"
	label variable lnnum_state_reg 					"Number of state registrations (Ln)"
	label variable retail 							"Retail"
	label variable mil_num_of_accounts 				"Number of accounts (in million)"
	label variable prt_aum 							"Percentage of asset"
	label variable hourly 							"Hourly charge"
	label variable fixed 							"Fixed fee"
	label variable commissions 						"Commission"
	label variable performancebased 				"Performance"
	label variable missing_indicator 				"Missing indicator"
*Table 4: Column 1
	reghdfe firmhire_preadvisor_criminal highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg, cluster(zip3) absorb(cityid formyear)
*Table 4: Column 2
	reghdfe firmhire_preadvisor_criminal highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg retail mil_num_of_accounts prt_aum hourly fixed commissions performancebased missing_indicator, cluster(zip3) absorb(cityid formyear)
*Table 4: Column 3
	reghdfe pct_firmhire_pre highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg, cluster(zip3) absorb(cityid formyear)
*Table 4: Column 4
	reghdfe pct_firmhire_pre highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg retail mil_num_of_accounts prt_aum hourly fixed commissions performancebased missing_indicator, cluster(zip3) absorb(cityid formyear)
*Summary statistics
	tabstat firmhire_preadvisor_criminal pct_firmhire_pre expel_or_cancel highrisk_brokerage small solo corp referral affil num_business_lines num_state_reg if e(sample)==1, column(statistic) statistics(mean sd p25 p50 p75 n)
	tabstat retail mil_num_of_accounts prt_aum hourly fixed commissions performancebased if missing_indicator==0 & e(sample)==1, column(statistic) statistics(mean sd p25 p50 p75 n)
*Table 10B: Column 1
	reghdfe expel_or_cancel firmhire_preadvisor_criminal highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg, cluster(zip3) absorb(cityid formyear)
*Table 10B: Column 2
	reghdfe expel_or_cancel firmhire_preadvisor_criminal highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg retail mil_num_of_accounts prt_aum hourly fixed commissions performancebased missing_indicator, cluster(zip3) absorb(cityid formyear)
*Table 10B: Column 3
	reghdfe expel_or_cancel pct_firmhire_pre highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg, cluster(zip3) absorb(cityid formyear)
*Table 10B: Column 4
	reghdfe expel_or_cancel pct_firmhire_pre highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg retail mil_num_of_accounts prt_aum hourly fixed commissions performancebased missing_indicator, cluster(zip3) absorb(cityid formyear)
*****************************************************************************************************
*Table 5: Where Do Hiring Firms Cluster?
	use "$path\Data for Table 05.dta", clear
	label variable firmhire_preadvisor_criminal 	"Pre-advisor criminal record at advisory firm"
	label variable pct_firmhire_pre 				"% Pre-advisor criminal record at advisory firm"
	label variable retireperhousehold 				"Retirement income"
	label variable lnretireperhousehold 			"Retirement income (Ln)"
	label variable area_wealthy_retirees 			"Area populated with wealthy retirees"
	label variable pop 								"Population"
	label variable lnpop 							"Population (Ln)"
	label variable income 							"Income"
	label variable lnincome 						"Income (Ln)"
	label variable medianage 						"Age (median)"
	label variable malefemaleratio 					"Male-to-female ratio"
	label variable pct_white 						"% White"
	label variable collegeabove 					"% College and above"
	label variable laborforce 						"% Labor force"
*Column 1
	reghdfe firmhire_preadvisor_criminal lnretireperhousehold lnpop lnincome medianage malefemaleratio pct_white collegeabove laborforce, cluster(county_fips) absorb(state_fips formyear)
*Column 2
	reghdfe firmhire_preadvisor_criminal area_wealthy_retirees lnpop lnincome medianage malefemaleratio pct_white collegeabove laborforce, cluster(county_fips) absorb(state_fips formyear)
*Column 3
	reghdfe pct_firmhire_pre lnretireperhousehold lnpop lnincome medianage malefemaleratio pct_white collegeabove laborforce, cluster(county_fips) absorb(state_fips formyear)
*Column 4
	reghdfe pct_firmhire_pre area_wealthy_retirees lnpop lnincome medianage malefemaleratio pct_white collegeabove laborforce, cluster(county_fips) absorb(state_fips formyear)
*Summary statistics for Table 2
	tabstat retireperhousehold area_wealthy_retirees pop income medianage malefemaleratio pct_white collegeabove laborforce if e(sample)==1, column(statistics) statistics(mean sd p25 p50 p75 n)
*****************************************************************************************************
*Table 6: Predicting Post-Advisor Criminal Records
*Table 7A: Predicting Customer Complaints and Settlements: Baseline Matched Sample
*Table 10A: Do Investors Face Higher Exposure to Service Disruption?: Advisor Suspended and Advisor Barred
	use "$path\Data for Table 06, 07A, and 10A.dta", clear
	label variable futurecriminal 			"Post-advisor criminal record"
	label variable futurelien 				"Personal lien or judgment"
	label variable futurecivil 				"Civil litigation"
	label variable customer_complaint 		"Customer complaint"
	label variable complaint_with_merit 	"Complaint with merit"
	label variable settled_complaint 		"Settled complaint"
	label variable lareg_settlement 		"Large settlement"
	label variable suspend 					"Advisor suspended"
	label variable bar 						"Advisor barred"
	label variable preadvisor 				"Pre-advisor criminal record"
	label variable firmsize 				"Firm size"
	label variable lnfirmsize 				"Firm size (Ln)"
	label variable avgperjob 				"Tenure per firm"
	label variable lnavgperjob 				"Tenure per firm (Ln)"
	label variable years_in_profession 		"Years in profession"
	label variable lnyears_in_profession 	"Years in profession (Ln)"
	label variable advisor 					"Investment adviser"
	label variable stateexamcount 			"Number of state exams"
	label variable principalexamcount 		"Number of principal exams"
	label variable productexamcount 		"Number of product exams"
	label variable lapse 					"Years since pre-advisor record"
	label variable numfirm 					"Number of advisory firms"
	label variable num_separation 			"Resigned after allegations"
	label variable ownerstopexec 			"Top executive or owner"
	label variable branchid 				"Branch ID"
	label variable since 					"Cohort year"
*Table 6, column 1
	reghdfe futurecriminal preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 6, column 2
	reghdfe futurelien preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 6, column 3
	reghdfe futurecivil preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 7, panel A, column 1
	reghdfe customer_complaint preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 7, panel A, column 2
	reghdfe complaint_with_merit preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 7, panel A, column 3
	reghdfe settled_complaint preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 7, panel A, column 4
	reghdfe lareg_settlement preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 10, panel A, column 1
	reghdfe suspend preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 10, panel A, column 2
	reghdfe bar preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Summary statistics for Table 2
	tabstat preadvisor futurecriminal futurelien futurecivil customer_complaint complaint_with_merit settle lareg_settlement years_in_profession numfirm avgperjob num_separation ownerstopexec suspend bar firmsize advisor stateexamcount principalexamcount productexamcount lapse if e(sample)==1, column(statistics) statistics(mean sd p25 p50 p75 n)
	tabstat lapse if e(sample)==1 & lapse>0, column(statistics) statistics(mean sd p25 p50 p75 n)
*****************************************************************************************************
*Table 7B: Predicting Customer Complaints and Settlements: Alternative Matched Sample
	use "$path\Data for Table 07B.dta", clear
	label variable customer_complaint 		"Customer complaint"
	label variable complaint_with_merit 	"Complaint with merit"
	label variable settled_complaint 		"Settled complaint"
	label variable lareg_settlement 		"Large settlement"
	label variable preadvisor_criminal 		"Pre-advisor criminal record"
	label variable lnavgperjob 				"Tenure per firm (Ln)"
	label variable lnyears_in_profession 	"Years in profession (Ln)"
	label variable advisor 					"Investment adviser"
	label variable stateexamcount 			"Number of state exams"
	label variable principalexamcount 		"Number of principal exams"
	label variable productexamcount 		"Number of product exams"
	label variable branchid 				"Branch ID"
	label variable year 					"Year"
	label variable since 					"Cohort year"
*Column 1
	reghdfe customer_complaint preadvisor_criminal lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(year#branchid since) vce(robust)
*Column 2
	reghdfe complaint_with_merit preadvisor_criminal lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(year#branchid since) vce(robust)
*Column 3
	reghdfe settled_complaint preadvisor_criminal lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(year#branchid since) vce(robust)
*Column 4
	reghdfe lareg_settlement preadvisor_criminal lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(year#branchid since) vce(robust)
*Summary statistics for Table 2
	tabstat customer_complaint complaint_with_merit settled_complaint lareg_settlement if e(sample)==1, statistics(mean sd p25 p50 p75 n) column(statistics)
*****************************************************************************************************
*Table 8: Are Investors Compensated with Higher Returns?
	use "$path\Data for Table 08.dta", clear
	label variable ret_nohire 	"Clean (no advisors with pre-advisor criminal records)"
	label variable ret_highrisk "High (High % of advisors with pre-advisor criminal records)"
	label variable high_no 		"Clean - High"
	label variable mktrf		"Market"
	label variable smb			"Small-minus-big"
	label variable hml			"High-minus-low"
	label variable umd			"Momentum"
	label variable ps_vwf		"Liquidity"
	label variable st_rev		"Short-term reversal"
	label variable lt_rev		"Long-term reversal"
*Fama-French 3-factor model
	foreach var in ret_nohire ret_highrisk high_no {
	newey `var' mktrf smb hml, lag(4)
	}
*Carhart 4-factor model
	foreach var in ret_nohire ret_highrisk high_no {
	newey `var' mktrf smb hml umd, lag(4)
	}
*7-factor model
	foreach var in ret_nohire ret_highrisk high_no {
	newey `var' mktrf smb hml umd ps_vwf st_rev lt_rev, lag(4)
	}
*****************************************************************************************************
*Table 9, column 1: Are Investors Compensated with Lower Fees?: Expense Ratio
	use "$path\Data for Table 09_1.dta", clear
	label variable exp_ratio 	"Expense ratio"
	label variable highrisk 	"Top hiring firm"
	label variable indexfund 	"Index fund"
	label variable nav_latest 	"Assets under management"
	label variable lnnav_latest "Assets under management (Ln)"
	label variable age 			"Fund age"
	label variable lnage 		"Fund age (Ln)"
	label variable turn_ratio 	"Turnover ratio"
	label variable objid 		"Style"
	label variable year 		"Year"
	label variable crsp_fundno 	"CRSP_FUNDNO"
*Column 1
	xi: areg exp_ratio i.year highrisk indexfund lnnav_latest lnage turn_ratio, r absorb(objid) cluster(crsp_fundno)
	tabstat exp_ratio highrisk indexfund nav_latest age turn_ratio if e(sample)==1, statistics(mean sd p25 p50 p75 n) column(statistics)
*****************************************************************************************************
*Table 9, columns 2 and 3: Are Investors Compensated with Lower Fees?: Maximum Initial Purchase Charge and Average Initial Purchase Charge
	use "$path\Data for Table 09_2-3.dta", clear
	label variable maxfront 	"Maximum initial purchase charge"
	label variable meanfront 	"Average initial purchase charge"
	label variable highrisk 	"Top hiring firm"
	label variable indexfund 	"Index fund"
	label variable objid 		"Style"
	label variable vintage		"Vintage"
	label variable crsp_fundno 	"CRSP_FUNDNO"
*Column 2
	xi: areg maxfront i.vintage highrisk indexfund, r absorb(objid) cluster(crsp_fundno)
*Column 3
	xi: areg meanfront i.vintage highrisk indexfund, r absorb(objid) cluster(crsp_fundno)
*Summary statistics for Table 2
	tabstat maxfront meanfront highrisk indexfund if e(sample)==1, statistics(mean sd p25 p50 p75 n) column(statistics)
*****************************************************************************************************
log close
*****************************************************************************************************
*****************************************************************************************************
*****      The follwing codes replicate the Main Samples in "Financial Gatekeepers and   	    ***** 
*****        Investor Protection: Evidence from Criminal Background Checks"          	        ***** 
*****        by Kelvin K. F. Law and Lillian F. Mills (2019, Journal of Accounting Research)	***** 
*****                                    Version: March 8, 2019									***** 
*****************************************************************************************************
*****************************************************************************************************
*The following codes construct main advisor sample
*Read raw FINRA employment data 
	use "D:\Dropbox\19-03-08 JAR Replications\advisor\employment_combined.dta", clear
*Construct advisor-year panel
	keep individualid firmid begin end zip5
	gduplicates drop _all, force
	gsort individualid begin firmid 
	drop if begin==.
	gen begyear = year(begin)
	gen endyear = year(end)
	gen length = endyear - begyear + 1
	gen recordid = _n
	expand(length)
	gsort recordid
	by recordid: gen id = _n
	gen year = begyear + id - 1
	rename id tenure
	drop if year(end)<year & end!=. & year!=.
	drop begyear endyear length
	gduplicates drop _all, force
	gsort recordid year begin 
*Years in profession
	by individualid, sort: egen since = min(year)
	gen genexp = year - since + 1 if since>=1946
	gsort recordid year begin 
*Firm size
	preserve
	keep year firmid individualid 
	gduplicates drop year firmid individualid, force
	gen dum = 1
	gcollapse (sum) firmsize = dum, by(firmid year)
	tempfile temp1
	save "`temp1'", replace
	restore
	merge m:1 firmid year using "`temp1'"
	drop if _merge==2
	drop _merge
	gsort recordid year begin 
*Branch size
	preserve
	keep year firmid individualid zip5
	drop if zip5==.
	drop if firmid==.
	gduplicates drop year firmid individualid zip5, force
	gen dum = 1
	gcollapse (sum) branchsize = dum, by(firmid zip5 year)
	tempfile temp2
	save "`temp2'", replace
	restore
	merge m:1 firmid zip5 year using "`temp2'"
	drop if _merge==2
	drop _merge
	gsort recordid year begin 
*One advisor obs per year
	egen groupid = group(individualid year)
	sort groupid firmsize
	by groupid: gen id = _n
	by groupid: egen maxid = max(id)
	keep if maxid==id
	drop groupid maxid id
	gsort recordid year begin 
	gduplicates drop individualid year, force
*Gender
	merge m:1 individualid using "D:\Dropbox\19-03-08 JAR Replications\advisor\Individual ID gender data.dta"
	drop if _merge==2
	drop _merge
	gsort individualid year
*Basic information (e.g., names, exam, bar status)
	merge m:1 individualid using "D:\Dropbox\19-03-08 JAR Replications\advisor\basicinformation.dta"
	drop if _merge==2
	drop _merge
	gsort individualid year
*Criminal records
	gen individual_id = individualid 
	merge m:1 individualid using "D:\Dropbox\19-03-08 JAR Replications\advisor\All criminals.dta" 
	drop if _merge==2
	drop _merge
	replace criminal = 0 if criminal==.
	gsort individualid year
*Exclude financial advisors deregistered between 1971 and 2006
	merge m:1 individualid using "D:\Dropbox\19-03-08 JAR Replications\advisor\List of excluded advisors deregistered between 1971 and 2006.dta"
	drop if _merge==2
	drop _merge
	drop if excluded==1
	gsort individualid year
*Exclude not-in-scope financial advisors
	merge m:1 individualid using "D:\Dropbox\19-03-08 JAR Replications\advisor\Not in scope financial advisors.dta"
	drop if _merge==2
	drop _merge
	drop if notinscope==1
	gsort individualid year
	save "D:\Dropbox\19-03-08 JAR Replications\advisor\Main Advisor Sample.dta"
*****************************************************************************************************
*****************************************************************************************************
*The following codes construct main advisory firm sample
*Read raw FINRA advisory firm data 
	use "D:\Dropbox\19-03-08 JAR Replications\firm\basicInformation.dta", clear
	drop if bcscope==""
	gen firmid = firm_id
	gsort firmid
*Get number of advisors with criminal records per firm
	merge 1:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\Num advisor with criminal per firm.dta"
	drop if _merge==2
	drop _merge
	gen lnnumadvisor = ln(numadvisor)
	gen lnnumcriminal = ln(1+numcriminal)
*Small firm
	gen small = 0
	replace small = 1 if numadvisor<=150
	replace small = 1 if firmsize=="Small"
*Formation year
	gen formyear = substr(formeddate,-4,.)
	destring(formyear), replace
*Firm expelled
	gen cancel = 0
	replace cancel = 1 if strpos(sanctions_sanctiondetails_messag,"cancel")
	gen expel = 0
	replace expel = 1 if strpos(sanctions_sanctiondetails_messag,"expel")
	replace expel = 1 if expelleddate!=. & expel==0
	gen expel_or_cancel = 0
	replace expel_or_cancel = 1 if cancel==1
	replace expel_or_cancel = 1 if expel==1
*Corporation status
	gen corp = 0
	replace corp = 1 if firmtype=="Corporation"
*Zip code
	merge 1:1 firm_id using "D:\Dropbox\19-03-08 JAR Replications\firm\firmAddressDetails.dta"
	drop if _merge==2
	drop _merge
	gsort firmid
*Address
	merge 1:1 firm_id using "D:\Dropbox\19-03-08 JAR Replications\firm\iaFirmAddressDetails.dta", update
	drop if _merge==2
	drop _merge
	gsort firmid
	duplicates drop _all, force
*Registration
	merge 1:1 firm_id using "D:\Dropbox\19-03-08 JAR Replications\firm\registrations.dta"
	drop if _merge==2
	drop _merge
	replace mailingaddress_postalcode = officeaddress_postalcode if mailingaddress_postalcode=="" & officeaddress_postalcode!=""
	gsort firmid
*Branch location
	merge m:1 firm_id using "D:\Dropbox\19-03-08 JAR Replications\firm\Find location based on branch office.dta"
	drop if _merge==2
	drop _merge
	gsort firmid
*Zip code
	gen zip5 = substr(mailingaddress_postalcode,1,5)
	gen zip3 = substr(mailingaddress_postalcode,1,3)
	destring(zip3), replace force
	destring(zip5), replace force
	replace zip5 = branchoffice_zipcode if zip5==. & branchoffice_zipcode!=.
	rename zip5 zip
	rename state stateabbr
*State, County, and MSA FIPS
	merge m:1 zip using "D:\Dropbox\19-03-08 JAR Replications\firm\zipcode.dta", keepusing(x y city state statecode county msa)
	drop if _merge==2
	drop _merge
	gen statecounty = state*1000 + county
	gsort firmid
*City ID
	egen cityid = group(city)
*State ID
	egen formstate_id = group(formedstate)
*Control variables
	gen referral = 0
	replace referral = 1 if referotherbd=="N"
	gen affil = 0
	replace affil=1 if hasaffliation=="Y"
	foreach var in referral affil businesstypecount approvedstateregistrationcount{
		replace `var' = 0 if `var'==.
	}
	gen lnbusinesstypecount = ln(1+businesstypecount)
	gen lnstatereg = ln(1+approvedstateregistrationcount)
*Identify firms that hire advisors with pre-advisor criminal records
	merge 1:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\List of firm which hire advisor with pre-advisor criminal records.dta"
	drop if _merge==2
	drop _merge
	replace firmhire_preadvisor_criminal=0 if firmhire_preadvisor_criminal==.
	gsort firmid
*Identify the fraction of advisors with pre-advisor criminal records
	merge 1:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\Fraction of advisors with pre-advisor criminal records.dta"
	drop if _merge==2
	drop _merge
	gsort firmid
*SEC ADV form 2017 Data
	merge m:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\2017 Data cleaned.dta" 
	drop if _merge==2
	drop _merge
	gsort firmid
*Retail
	merge m:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\Identify retail investment advisers based on 2010-2017 data.dta"
	drop if _merge==2
	drop _merge
	gsort firmid
*Number of firm regulatory disclosure
	merge m:1 firm_id using "D:\Dropbox\19-03-08 JAR Replications\firm\Regulatory event per firm active only.dta"
	drop if _merge==2
	drop _merge
	replace disclosurecount = 0 if disclosurecount==.
	gen lndisclosurecount = ln(1+disclosurecount)
	gsort firmid
*High-risk brokerage
	gen regfirm_90 = 0
	replace regfirm_90 = 1 if disclosurecount>=9 & disclosurecount!=.
*Fee structure
	merge m:1 firmid using "D:\Dropbox\19-03-08 JAR Replications\firm\Identify fee structure.dta"
	drop if _merge==2
	drop _merge
	gsort firmid
*Number of accounts
	gen num_of_accounts = 0
	replace num_of_accounts = _5f_2_f if _5f_2_f!=.
	gen mil_num_of_accounts = num_of_accounts/1000000
*Missing variable indicator
	gen missing_indicator = 0
	replace missing_indicator = 1 if retail==.
	save "D:\Dropbox\19-03-08 JAR Replications\firm\Main Advisory Firm Sample.dta"

*****************************************************************************************************
*****************************************************************************************************
*****      The follwing codes replicate Tables 1-10 in "Financial Gatekeepers and Investor  	***** 
*****        Protection: Evidence from Criminal Background Checks" by Kelvin K. F. Law         	***** 
*****                 and Lillian F. Mills (2019, Journal of Accounting Research) 				***** 
*****                                    Version: March 8, 2019									***** 
*****************************************************************************************************
*****************************************************************************************************
log using "D:\Dropbox\19-03-08 JAR Replications\Tables 1-10"
global path "D:\Dropbox\19-03-08 JAR Replications"
*Table 1A: Overview of Criminal Records: By Year
	use "$path\Data for Table 01A.dta", clear
	levelsof year, local(levelofyear) clean
*Column 1: #Financial advisors with criminal records by year
	forvalues k = 2007(1)2017 {
		gdistinct individualid if criminal==1 & year==`k'
	}
	distinct individualid if criminal==1
*Column 2: #Financial advisors by year
	forvalues k = 2007(1)2017 {
		gdistinct individualid if year==`k'
	}
	distinct individualid
*Column 3: #Criminal records that are felony by year
	forvalues k = 2007(1)2017 {
		gdistinct individualid if felony==1 & year==`k'
	}
	distinct individualid if felony==1
	forvalues k = 2007(1)2017 {
		gdistinct individualid if misdemeanor==1 & year==`k'
	}
	distinct individualid if misdemeanor==1
*Column 4: #Male financial advisors by year
	forvalues k = 2007(1)2017 {
		gdistinct individualid if male==1 & year==`k'
	}
	gdistinct individualid if male==1
*Column 4: #Male financial advisors with criminal records by year
	forvalues k = 2007(1)2017 {
		gdistinct individualid if male==1 & criminal==1 & year==`k'
	}
	gdistinct individualid if male==1 & criminal==1
*****************************************************************************************************
*Table 1B: Overview of Criminal Records: By Charge
	use "$path\Data for Table 01B.dta", clear
*Column 1: # Criminal records
	foreach var in theft fraud substance violent traffic public others{
		sum `var' if `var'==1
	}
	tab charge_type
*Column 2: % Felony
	foreach var in theft fraud substance violent traffic public others{
		sum `var' if `var'==1 & charge_type=="FELONY"
	}
	sum dum if charge_type=="FELONY"
*Column 3: % Misdemeanor
	foreach var in theft fraud substance violent traffic public others{
		sum `var' if `var'==1 & charge_type=="MISDEMEANOR"
	}
	sum dum if charge_type=="MISDEMEANOR"
*Column 4: % Dismissed
	foreach var in theft fraud substance violent traffic public others{
		sum `var' if `var'==1 & dismissed==1
	}
	sum dismissed
*Column 5: % Male
	foreach var in theft fraud substance violent traffic public others{
		sum `var' if `var'==1 & male==1
	}
	sum male
*****************************************************************************************************
*Table 1C: Overview of Criminal Records: By Geography
*Top 5 cities: more than 2,000 advisors
	use "$path\Data for Table 01C.dta", clear
	keep if numadvisor>=2000
	gsort -ratio_criminal 
	gen id = _n
	keep if id<=5
	drop id
	list
*Top 5 cities: less than 2,000 advisors
	use "$path\Data for Table 01C.dta", clear
	keep if criminal>=30 & numadvisor<2000
	gsort -ratio_criminal 
	drop criminal
	gen id = _n
	keep if id<=5
	drop id
	list
*****************************************************************************************************
*Table 3A: Profiling of Financial Advisors with Criminal Records: Pre- and Post-Advisor Criminal Records
	use "$path\Data for Table 03A.dta", clear
	label variable criminal 		"Criminal record"
	label variable male 			"Male"
	label variable minority 		"Minority"
	label variable hb 				"Minority (excluding Asian names)"
	label variable felony 			"Felony"
	label variable misdemeanor 		"Misdemeanor"
*Column 1
	reghdfe criminal male minority, absorb(startingyear cityid)
*Column 2
	reghdfe felony male minority, absorb(startingyear cityid)
*Column 3
	reghdfe misdemeanor male minority, absorb(startingyear cityid)
*Column 4
	reghdfe criminal male hb, absorb(startingyear cityid)
*Column 5
	reghdfe felony male hb, absorb(startingyear cityid)
*Column 6
	reghdfe misdemeanor male hb, absorb(startingyear cityid)
*Summary statistics for Table 2
	tabstat criminal felony misdemeanor male minority hb if e(sample)==1, column(statistic) statistics(mean sd p25 p50 p75 n)
*****************************************************************************************************
*Table 3B: Profiling of Financial Advisors with Criminal Records: Pre-Advisor Criminal Records Only
	use "$path\Data for Table 03B.dta", clear
	label variable preadvisor_criminal 		"Pre-advisor criminal record"
	label variable male 					"Male"
	label variable minority 				"Minority"
	label variable hb 						"Minority (excluding Asian names)"
	label variable preadvisor_felony 		"Pre-advisor felony"
	label variable preadvisor_misdemeanor 	"Pre-advisor misdemeanor"
*Column 1
	reghdfe preadvisor_criminal male minority, absorb(startingyear cityid)
*Column 2
	reghdfe preadvisor_felony male minority, absorb(startingyear cityid)
*Column 3
	reghdfe preadvisor_misdemeanor male minority, absorb(startingyear cityid)
*Column 4
	reghdfe preadvisor_criminal male hb, absorb(startingyear cityid)
*Column 5
	reghdfe preadvisor_felony male hb, absorb(startingyear cityid)
*Column 6
	reghdfe preadvisor_misdemeanor male hb, absorb(startingyear cityid)
*Summary statistics for Table 2
	tabstat preadvisor_criminal preadvisor_felony preadvisor_misdemeanor male minority hb if e(sample)==1, column(statistic) statistics(mean sd p25 p50 p75 n)
*****************************************************************************************************
*Table 4: Who Hires Financial Advisors with Criminal Records?
*Table 10B: Do Investors Face Higher Exposure to Service Disruption?: Firm Expelled
	use "$path\Data for Table 04 and 10B.dta", clear
	label variable firmhire_preadvisor_criminal 	"Hiring advisor with pre-advisor criminal record"
	label variable pct_firmhire_pre 				"% Pre-advisor criminal record at advisory firm"
	label variable highrisk_brokerage 				"High-risk brokerage"
	label variable expel_or_cancel 					"Firm expelled"
	label variable small 							"Small firm"
	label variable solo 							"Solo firm"
	label variable corp 							"Corporation"
	label variable referral 						"Referral business"
	label variable affil 							"Affiliated"
	label variable num_business_lines 				"Number of business lines (Ln)"
	label variable lnnum_state_reg 					"Number of state registrations (Ln)"
	label variable retail 							"Retail"
	label variable mil_num_of_accounts 				"Number of accounts (in million)"
	label variable prt_aum 							"Percentage of asset"
	label variable hourly 							"Hourly charge"
	label variable fixed 							"Fixed fee"
	label variable commissions 						"Commission"
	label variable performancebased 				"Performance"
	label variable missing_indicator 				"Missing indicator"
*Table 4: Column 1
	reghdfe firmhire_preadvisor_criminal highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg, cluster(zip3) absorb(cityid formyear)
*Table 4: Column 2
	reghdfe firmhire_preadvisor_criminal highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg retail mil_num_of_accounts prt_aum hourly fixed commissions performancebased missing_indicator, cluster(zip3) absorb(cityid formyear)
*Table 4: Column 3
	reghdfe pct_firmhire_pre highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg, cluster(zip3) absorb(cityid formyear)
*Table 4: Column 4
	reghdfe pct_firmhire_pre highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg retail mil_num_of_accounts prt_aum hourly fixed commissions performancebased missing_indicator, cluster(zip3) absorb(cityid formyear)
*Summary statistics
	tabstat firmhire_preadvisor_criminal pct_firmhire_pre expel_or_cancel highrisk_brokerage small solo corp referral affil num_business_lines num_state_reg if e(sample)==1, column(statistic) statistics(mean sd p25 p50 p75 n)
	tabstat retail mil_num_of_accounts prt_aum hourly fixed commissions performancebased if missing_indicator==0 & e(sample)==1, column(statistic) statistics(mean sd p25 p50 p75 n)
*Table 10B: Column 1
	reghdfe expel_or_cancel firmhire_preadvisor_criminal highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg, cluster(zip3) absorb(cityid formyear)
*Table 10B: Column 2
	reghdfe expel_or_cancel firmhire_preadvisor_criminal highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg retail mil_num_of_accounts prt_aum hourly fixed commissions performancebased missing_indicator, cluster(zip3) absorb(cityid formyear)
*Table 10B: Column 3
	reghdfe expel_or_cancel pct_firmhire_pre highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg, cluster(zip3) absorb(cityid formyear)
*Table 10B: Column 4
	reghdfe expel_or_cancel pct_firmhire_pre highrisk_brokerage small solo corp referral affil lnnum_business_lines lnnum_state_reg retail mil_num_of_accounts prt_aum hourly fixed commissions performancebased missing_indicator, cluster(zip3) absorb(cityid formyear)
*****************************************************************************************************
*Table 5: Where Do Hiring Firms Cluster?
	use "$path\Data for Table 05.dta", clear
	label variable firmhire_preadvisor_criminal 	"Pre-advisor criminal record at advisory firm"
	label variable pct_firmhire_pre 				"% Pre-advisor criminal record at advisory firm"
	label variable retireperhousehold 				"Retirement income"
	label variable lnretireperhousehold 			"Retirement income (Ln)"
	label variable area_wealthy_retirees 			"Area populated with wealthy retirees"
	label variable pop 								"Population"
	label variable lnpop 							"Population (Ln)"
	label variable income 							"Income"
	label variable lnincome 						"Income (Ln)"
	label variable medianage 						"Age (median)"
	label variable malefemaleratio 					"Male-to-female ratio"
	label variable pct_white 						"% White"
	label variable collegeabove 					"% College and above"
	label variable laborforce 						"% Labor force"
*Column 1
	reghdfe firmhire_preadvisor_criminal lnretireperhousehold lnpop lnincome medianage malefemaleratio pct_white collegeabove laborforce, cluster(county_fips) absorb(state_fips formyear)
*Column 2
	reghdfe firmhire_preadvisor_criminal area_wealthy_retirees lnpop lnincome medianage malefemaleratio pct_white collegeabove laborforce, cluster(county_fips) absorb(state_fips formyear)
*Column 3
	reghdfe pct_firmhire_pre lnretireperhousehold lnpop lnincome medianage malefemaleratio pct_white collegeabove laborforce, cluster(county_fips) absorb(state_fips formyear)
*Column 4
	reghdfe pct_firmhire_pre area_wealthy_retirees lnpop lnincome medianage malefemaleratio pct_white collegeabove laborforce, cluster(county_fips) absorb(state_fips formyear)
*Summary statistics for Table 2
	tabstat retireperhousehold area_wealthy_retirees pop income medianage malefemaleratio pct_white collegeabove laborforce if e(sample)==1, column(statistics) statistics(mean sd p25 p50 p75 n)
*****************************************************************************************************
*Table 6: Predicting Post-Advisor Criminal Records
*Table 7A: Predicting Customer Complaints and Settlements: Baseline Matched Sample
*Table 10A: Do Investors Face Higher Exposure to Service Disruption?: Advisor Suspended and Advisor Barred
	use "$path\Data for Table 06, 07A, and 10A.dta", clear
	label variable futurecriminal 			"Post-advisor criminal record"
	label variable futurelien 				"Personal lien or judgment"
	label variable futurecivil 				"Civil litigation"
	label variable customer_complaint 		"Customer complaint"
	label variable complaint_with_merit 	"Complaint with merit"
	label variable settled_complaint 		"Settled complaint"
	label variable lareg_settlement 		"Large settlement"
	label variable suspend 					"Advisor suspended"
	label variable bar 						"Advisor barred"
	label variable preadvisor 				"Pre-advisor criminal record"
	label variable firmsize 				"Firm size"
	label variable lnfirmsize 				"Firm size (Ln)"
	label variable avgperjob 				"Tenure per firm"
	label variable lnavgperjob 				"Tenure per firm (Ln)"
	label variable years_in_profession 		"Years in profession"
	label variable lnyears_in_profession 	"Years in profession (Ln)"
	label variable advisor 					"Investment adviser"
	label variable stateexamcount 			"Number of state exams"
	label variable principalexamcount 		"Number of principal exams"
	label variable productexamcount 		"Number of product exams"
	label variable lapse 					"Years since pre-advisor record"
	label variable numfirm 					"Number of advisory firms"
	label variable num_separation 			"Resigned after allegations"
	label variable ownerstopexec 			"Top executive or owner"
	label variable branchid 				"Branch ID"
	label variable since 					"Cohort year"
*Table 6, column 1
	reghdfe futurecriminal preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 6, column 2
	reghdfe futurelien preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 6, column 3
	reghdfe futurecivil preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 7, panel A, column 1
	reghdfe customer_complaint preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 7, panel A, column 2
	reghdfe complaint_with_merit preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 7, panel A, column 3
	reghdfe settled_complaint preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 7, panel A, column 4
	reghdfe lareg_settlement preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 10, panel A, column 1
	reghdfe suspend preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Table 10, panel A, column 2
	reghdfe bar preadvisor lnfirmsize lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(branchid since) vce(robust)
*Summary statistics for Table 2
	tabstat preadvisor futurecriminal futurelien futurecivil customer_complaint complaint_with_merit settle lareg_settlement years_in_profession numfirm avgperjob num_separation ownerstopexec suspend bar firmsize advisor stateexamcount principalexamcount productexamcount lapse if e(sample)==1, column(statistics) statistics(mean sd p25 p50 p75 n)
	tabstat lapse if e(sample)==1 & lapse>0, column(statistics) statistics(mean sd p25 p50 p75 n)
*****************************************************************************************************
*Table 7B: Predicting Customer Complaints and Settlements: Alternative Matched Sample
	use "$path\Data for Table 07B.dta", clear
	label variable customer_complaint 		"Customer complaint"
	label variable complaint_with_merit 	"Complaint with merit"
	label variable settled_complaint 		"Settled complaint"
	label variable lareg_settlement 		"Large settlement"
	label variable preadvisor_criminal 		"Pre-advisor criminal record"
	label variable lnavgperjob 				"Tenure per firm (Ln)"
	label variable lnyears_in_profession 	"Years in profession (Ln)"
	label variable advisor 					"Investment adviser"
	label variable stateexamcount 			"Number of state exams"
	label variable principalexamcount 		"Number of principal exams"
	label variable productexamcount 		"Number of product exams"
	label variable branchid 				"Branch ID"
	label variable year 					"Year"
	label variable since 					"Cohort year"
*Column 1
	reghdfe customer_complaint preadvisor_criminal lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(year#branchid since) vce(robust)
*Column 2
	reghdfe complaint_with_merit preadvisor_criminal lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(year#branchid since) vce(robust)
*Column 3
	reghdfe settled_complaint preadvisor_criminal lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(year#branchid since) vce(robust)
*Column 4
	reghdfe lareg_settlement preadvisor_criminal lnavgperjob lnyears_in_profession advisor stateexamcount principalexamcount productexamcount, absorb(year#branchid since) vce(robust)
*Summary statistics for Table 2
	tabstat customer_complaint complaint_with_merit settled_complaint lareg_settlement if e(sample)==1, statistics(mean sd p25 p50 p75 n) column(statistics)
*****************************************************************************************************
*Table 8: Are Investors Compensated with Higher Returns?
	use "$path\Data for Table 08.dta", clear
	label variable ret_nohire 	"Clean (no advisors with pre-advisor criminal records)"
	label variable ret_highrisk "High (High % of advisors with pre-advisor criminal records)"
	label variable high_no 		"Clean - High"
	label variable mktrf		"Market"
	label variable smb			"Small-minus-big"
	label variable hml			"High-minus-low"
	label variable umd			"Momentum"
	label variable ps_vwf		"Liquidity"
	label variable st_rev		"Short-term reversal"
	label variable lt_rev		"Long-term reversal"
*Fama-French 3-factor model
	foreach var in ret_nohire ret_highrisk high_no {
	newey `var' mktrf smb hml, lag(4)
	}
*Carhart 4-factor model
	foreach var in ret_nohire ret_highrisk high_no {
	newey `var' mktrf smb hml umd, lag(4)
	}
*7-factor model
	foreach var in ret_nohire ret_highrisk high_no {
	newey `var' mktrf smb hml umd ps_vwf st_rev lt_rev, lag(4)
	}
*****************************************************************************************************
*Table 9, column 1: Are Investors Compensated with Lower Fees?: Expense Ratio
	use "$path\Data for Table 09_1.dta", clear
	label variable exp_ratio 	"Expense ratio"
	label variable highrisk 	"Top hiring firm"
	label variable indexfund 	"Index fund"
	label variable nav_latest 	"Assets under management"
	label variable lnnav_latest "Assets under management (Ln)"
	label variable age 			"Fund age"
	label variable lnage 		"Fund age (Ln)"
	label variable turn_ratio 	"Turnover ratio"
	label variable objid 		"Style"
	label variable year 		"Year"
	label variable crsp_fundno 	"CRSP_FUNDNO"
*Column 1
	xi: areg exp_ratio i.year highrisk indexfund lnnav_latest lnage turn_ratio, r absorb(objid) cluster(crsp_fundno)
	tabstat exp_ratio highrisk indexfund nav_latest age turn_ratio if e(sample)==1, statistics(mean sd p25 p50 p75 n) column(statistics)
*****************************************************************************************************
*Table 9, columns 2 and 3: Are Investors Compensated with Lower Fees?: Maximum Initial Purchase Charge and Average Initial Purchase Charge
	use "$path\Data for Table 09_2-3.dta", clear
	label variable maxfront 	"Maximum initial purchase charge"
	label variable meanfront 	"Average initial purchase charge"
	label variable highrisk 	"Top hiring firm"
	label variable indexfund 	"Index fund"
	label variable objid 		"Style"
	label variable vintage		"Vintage"
	label variable crsp_fundno 	"CRSP_FUNDNO"
*Column 2
	xi: areg maxfront i.vintage highrisk indexfund, r absorb(objid) cluster(crsp_fundno)
*Column 3
	xi: areg meanfront i.vintage highrisk indexfund, r absorb(objid) cluster(crsp_fundno)
*Summary statistics for Table 2
	tabstat maxfront meanfront highrisk indexfund if e(sample)==1, statistics(mean sd p25 p50 p75 n) column(statistics)
*****************************************************************************************************
log close

brainstormingobra/superpowersYou MUST use this before any creative work - creating features, building components, adding functionality, modifying behavior, or planning anything new, in software or out of it (a talk, a business, a renovation).Stars: 323kUpdated Claude CodeCodexVerifiedInstall
brand-guidelinesanthropics/skillsApplies Anthropic's official brand colors and typography to any sort of artifact that may benefit from having Anthropic's look-and-feel. Use it when brand colors or style guidelines, visual formatting, or company design standards apply.Stars: 198kUpdated Claude CodeCodexVerifiedInstall
frontend-designanthropics/skillsGuidance for distinctive, intentional visual design when building new UI or reshaping an existing one. Helps with aesthetic direction, typography, and making choices that don't read as templated defaults.Stars: 198kUpdated Claude CodeCodexVerifiedInstall
mcp-builderanthropics/skillsGuide for creating high-quality MCP (Model Context Protocol) servers that enable LLMs to interact with external services through well-designed tools. Use when building MCP servers to integrate external APIs or services, whether in Python (FastMCP) or Node/TypeScript (MCP SDK).Stars: 198kUpdated Claude CodeCodexVerifiedInstall