* Data Source: CAMAR
* Change directory to the target folder path
cd "D:\Data\Margin_Trading_Data"

* ==============================================================================
* 1. Initial Processing
* ==============================================================================

* 1.1 Margin Trading and Securities Lending Transaction Details Table
import excel "Margin_Trading_Details_Annual.xlsx", firstrow clear
rename credit_year year
destring year, replace
rename stock_code security_code
drop exchange_code start_trading_date end_trading_date stock_abbr
save "Margin_Trading_Details_Annual.dta", replace

collapse (min) margin_include_year=year (max) last_year=year (count) N=year, by(security_code)
save "data.dta", replace

* 1.2 Listed Company Data
import excel "Company_Basic_Information.xlsx", firstrow clear
keep security_code security_name list_date industry_code_c
gen code = real(security_code)  // Generate numeric security code "code"
gen list_year = real(substr(list_date, 1, 4))

* Expand year data to generate observations from the listing year up to 2021
expand 2021 - list_year + 1   
bys security_code: gen year = list_year + _n - 1  // Generate year variable
keep if year >= 1990 & year <= 2021
save "Company_Basic_Information.dta", replace

* 1.3 Merge Data
use "Company_Basic_Information.dta", replace
merge m:1 security_code using "data.dta", keep(1 3)


* ==============================================================================
* 2. Calculation Process
* ==============================================================================

* List: Dummy variable for margin trading eligibility. 
* If a firm is included in the margin trading target list during the sample period, List = 1; otherwise, 0.
* Note on Stata system variable '_merge':
* _merge == 1: Data exists only in the master dataset (Company_Basic_Information).
* _merge == 2: Data exists only in the using dataset (data.dta) -> (Did not occur here).
* _merge == 3: Successful match; data exists in both datasets.

gen List = 1 if _merge == 3   // Match successful: company became a margin trading target during the sample period
replace List = 0 if _merge == 1  // Match failed: company never became a margin trading target during the sample period
drop _merge

* Exclude firms that dropped out of the margin trading eligibility list midway
gen interval = last_year - margin_include_year + 1
drop if interval != N & N != .

* Post: Time dummy variable for margin trading deregulation. 
* Observations in or after the year the firm was included in the list take the value 1; otherwise, 0.
gen Post = 1 if year >= margin_include_year & margin_include_year != .
replace Post = 0 if Post == .

* Merge with other margin trading and securities lending financial metrics
merge 1:1 security_code year using "Margin_Trading_Details_Annual.dta", nogen keep(1 3)

* Assign 0 to missing values (formatting nulls to zero for specific financial metrics)
foreach i in margin_buy_amt margin_repay_amt margin_bal_amt short_sell_vol short_repay_vol short_bal_vol short_bal_amt total_margin_bal {
    replace `i' = 0 if `i' == .
}


* ==============================================================================
* 3. Save and Export Calculation Results
* ==============================================================================
sort code year

keep security_code security_name list_date year industry_code_c list_year List Post ///
     margin_buy_amt margin_repay_amt margin_bal_amt margin_bal_yoy ///
     short_sell_vol short_repay_vol short_bal_vol short_bal_vol_yoy ///
     short_bal_amt short_bal_amt_yoy total_margin_bal total_margin_bal_pop

order security_code security_name list_date year industry_code_c list_year List Post ///
      margin_buy_amt margin_repay_amt margin_bal_amt margin_bal_yoy ///
      short_sell_vol short_repay_vol short_bal_vol short_bal_vol_yoy ///
      short_bal_amt short_bal_amt_yoy total_margin_bal total_margin_bal_pop

save "Margin_Trading_Final.dta", replace
export excel "Margin_Trading_Final.xlsx", firstrow(variable) replace

* Data volume statistics by year (Data Checks)
tabulate year
tabulate year Post if year >= 2010 
tabulate year List if year >= 2010 

* Descriptive Statistics Summary
use "Margin_Trading_Final.dta", clear
tabstat List Post, c(s) s(N mean sd min p50 max) format(%10.3f)