*** Sample Code 

/*****
This code creates two main classes of variables that are used in our paper "Unchecked Intermediaries: Price Manipulation in an Emerging Stock Market" forthcoming in the JFE
The two classes our variable are our main RHS measure - "PRIN"
and the main RHS measure of profitability, "ARR"

To construct both these measures all that is required is data on the low and high price of a stock on a given trading day,
and the amount of stock bought and sold by each broker for the given stock on each day

Each constructed variable is labelled 
This code is run on a sample of 3 brokers trading in two stocks from our data.
The data is NOT representative. It is only meant to explain the definitions of our 
measures, so that people having similar data can reuse our methodology.
The broker and stock idetifiers have been changed to ensure confidentiality.
If there any questions on the code please feel free to contact either of us
Asim Ijaz Khwaja (akhwaja@ksg.harvard.edu), Atif Mian (atif@gsb.uchicago.edu)

If you want to get access to the full data used in our paper, please contact:

Securities & Exchange Commission of Pakistan,
NIC Building,
Jinnah Avenue,
Blue Area,
Islamabad, Pakistan
Fax: 92-51-920-4915 
Tel: 92-51-920-7091
website: www.secp.gov.pk
****/


**Run code on Sample Data

use C:\aRes\StockMkt\Data\ksedataFBT_Sample, clear
gen FTavprice=(high+low)/2
label variable FTavprice "Average price of a stock (firm) on a given date"


**1. Creating the main RHS - PRIN and its various forms

*First Definining Four types of Trades: Cycle, PrinBuy, PrinSale, and Intermediary (As identified in Table 1 of the paper)

sort firmid broker date
quietly by firmid broker: gen leadpur=purchase[_n+1]
quietly by firmid broker: gen leadsal=sale[_n+1]
quietly by firmid broker: gen lagpur=purchase[_n-1]
quietly by firmid broker: gen lagsal=sale[_n-1]
gen byte cyc=((purchase==leadsal & sale==leadpur) | (sale==purchase & sale!=0 & sale!=.)) |  (purchase==lagsal & sale==lagpur)
gen byte cycrestr=((purchase==leadsal & sale==leadpur) | (purchase==lagsal & sale==lagpur))
sort firmid broker date
quietly by firmid broker: gen supcyc2=(cyc==1&cyc[_n+1]==1&cyc[_n+2]==1) 
gen byte prinsale= (sale>0 & purchase==0) & cyc!=1
gen byte prinbuy= (sale==0 & purchase>0)  & cyc!=1
gen byte prin=cyc+prinsale+prinbuy
gen byte prinrestr=cycrestr+prinsale+prinbuy
drop leadpur leadsal lagpur lagsal
label variable cyc "whether trade a cycle, inc. sale==purchase"
label variable cycrestr "whether trade a cycle, exc. sale==purchase"
label variable supcyc2 "3 consecutive cycles"
label variable prinsale "non-cyc prin sale"
label variable prinbuy "non-cyc prin buy"
label variable prin "PRIN measure"
label variable prinrestr "Restricted PRIN measure"	
gen str2 ttype=""
replace ttype="CT" if cyc==1
replace ttype="PB" if prinbuy==1
replace ttype="PS" if prinsale==1
replace ttype="IN" if prin==0
label variable ttype "Type of Trade"

*Generating some FB (firm-broker) level characteristics
egen FBprinsale=mean(prinsale), by(firmid broker)
egen FBprinbuy=mean(prinbuy), by(firmid broker)
egen FBcyc=mean(cyc), by(firmid broker)
egen FBsupcyc2=mean(supcyc2), by(firmid broker)
egen FBnoncyc=mean(prinsale+prinbuy), by(firmid broker)
egen FBprin=mean(prin), by(firmid broker)
egen FBprinrestr=mean(prinrestr), by(firmid broker)
egen FBnonsupcyc2=mean(prin-supcyc2), by(firmid broker)
egen FBtag=tag(firmid broker)
label variable FBcyc "whether trade a cycle, inc. sale==purchase"
label variable FBnoncyc "non-cyc PRIN"
label variable FBsupcyc2 "3 consecutive cycles"
label variable FBprinsale "non-cyc prin sale"
label variable FBprinbuy "non-cyc prin buy"
label variable FBprin "PRIN measure"
label variable FBprinrestr "Restricted PRIN measure"

*Creating firm-broker level trading volume measure.
egen FBtvol=sum((sale+purchase)/2), by(firmid broker)
label variable FBtvol "Total number of shares (sale+purchase)/2 traded at the FB level"
egen FBtval=sum(FTavprice*(sale+purchase)/2), by(firmid broker)
label variable FBtval "Total value of shares (sale+purchase)/2 traded at the FB level"
egen Ftvol=sum((sale+purchase)/2), by(firmid)
gen FBtvolshare=FBtvol/Ftvol
label variable FBtvolshare "Share of stock volum traded by the broker"
drop Ftvol




**2. Creating the main LHS - ARR (FBiarrcap100) and its various forms

**** Creating the Profit (ARR) measure at the firm-broker level
gen lastprice=.
sort firmid date
quietly by firmid: replace lastprice=FTavprice[_N]
egen liquidate=sum(net), by(broker firm)
replace liquidate= -1*liquidate
sort firmid broker date
quietly by firmid broker: gen investflow=net*FTavprice if net>0
replace investflow=0 if net<=0
quietly by firmid broker: gen revflow=-1*net*FTavprice if net<0
replace revflow=0 if net>=0
quietly by firmid broker: gen netrprof=revflow-investflow if _n==1
quietly by firmid broker: replace netrprof=netrprof[_n-1]+(revflow-investflow) if _n>1 & _n!=_N
quietly by firmid broker: replace netrprof=netrprof[_n-1]+(revflow-investflow)-liquidate*lastprice if _n==_N
quietly by firmid broker: gen FBmanipprof=netrprof[_N]
label variable FBmanipprof "Prof. from pure manip. act. (not inc. cap. cost)"
drop revflow investflow lastprice liquidate


*** Creating the Average Capital used measure at the firm-broker level assuming different financing margins.
sort firmid broker date
quietly by firmid broker: gen rnetb=sum(net)
gen capreq=-1*(netrprof+(rnetb<0)*FTavprice*rnetb)*((netrprof+(rnetb<0)*FTavprice*rnetb)<0)
quietly by firmid broker: gen duration=date[_n+1]-date if _n!=_N
egen FBmaxcapreq=max(capreq), by(firmid broker)
egen FBduration=sum(duration), by(firmid broker)
egen FBimargin=sum(100*(capreq/FBmaxcapreq)*(duration/FBduration)), by(firmid broker)
gen FBtotcapimargin=(FBimargin/100)*FBmaxcapreq
label variable FBtotcapimargin "Total capital required using avg. capital"
local margin=20
while `margin'<=100 {
gen FBitotcap`margin'=(`margin'/100)*FBtotcapimargin
label variable FBitotcap`margin' "Total capital required using avg. capital & margin"
local margin=`margin'+20
}

*** Creating the annual profitability measure at the firm-broker level for different financing margins
gen Fenddate=.
gen Fstartdate=.
sort firmid date
quietly by firmid: replace Fstartdate=date[1]
quietly by firmid: replace Fenddate=date[_N]
gen Fduration=Fenddate-Fstartdate
local margin=20
while `margin'<=100 {
gen FBiarrtot`margin'=FBmanipprof/FBitotcap`margin'
replace FBiarrtot`margin'=-1 if FBiarrtot`margin'<-1
gen FBiarrcap`margin'=100*((1+FBiarrtot`margin')^(365/Fduration)-1)
label variable FBiarrcap`margin' "ARR based on avg. capital & margin"
drop FBiarrtot`margin'
local margin=`margin'+20
}
drop FBmaxcapreq FBduration FBimargin FBtotcapimargin FBitotcap*

* Now only keep Firm-Broker unique observations for Regression Analysis
keep if FBtag
keep firmid broker FB*
drop FBtag
sort firmid broker
save C:\aRes\StockMkt\Data\ksedataFB_Sample, replace