*******************************************************************************
** Please acknowlegde the source
** Boermans, M.A. (2022). A literature review of Securities Holdings Statistics research and a practitioner's guide. DNB Working Paper No 757.
** Version 2.0 (XX August 2024) (First version: 8 December 2022)
*******************************************************************************
* Data cleaning
********************************************************************************

* bonds and money market SHS clean copies from the ECB selection:

* use 	"F32_2024Q1.dta", clear		
* use 	"F31_2024Q1.dta", clear	
 
 * stocks and investment funds, edit quotation_basis filter ad 6.
	* use 	"F511_2024Q1.dta", clear		
	* use 	"F52E_2024Q1.dta", clear	
 
	*1. exclude FDI
		drop if functional_category=="D"
		
	*2. exclude short positions
		keep if obs_value>0
	
	*3. Only active securities 
		keep if security_status==100
		
		* active bonds
			gen issue_qdate = substr(issue_date,1,7)
			split issue_qdate, p("-")
			destring issue_qdate1 issue_qdate2, replace
			replace issue_qdate2 = ceil(issue_qdate2/3)
			gen issuance_date = yq(issue_qdate1, issue_qdate2)
			format %tq issuance_date
			drop issue_qdate1 issue_qdate2 issue_qdate issue_date
			*
			gen maturity_qdate = substr(mat_date,1,7)
			split maturity_qdate, p("-")
			destring maturity_qdate1 maturity_qdate2, replace
			replace maturity_qdate2 = ceil(maturity_qdate2/3)
			gen maturity_date = yq(maturity_qdate1, maturity_qdate2)
			format %tq maturity_date
			drop maturity_qdate maturity_qdate1 maturity_qdate2 mat_date
	
	*4. exclude tax havens 
		drop if issuer_country == "VI" | issuer_country == "CW" | issuer_country == "KY" | ///
				issuer_country == "BS" | issuer_country == "BM" | issuer_country == "VG" | ///
				issuer_country == "IM" | issuer_country == "MH" | issuer_country == "GG" | ///
				issuer_country == "GI" | issuer_country == "JE" | issuer_country == "LI"
		
	*5. exclude AO threshold
		 drop if amount_out_eur<100000000
		* drop if market_cap_eur<100000000

	*6 exclude certificates (often quotation basis in CCY) (only relevant for F32)
		drop if debt_type=="D.18"
	
	* only in PCL for bonds and money market (F32, F31)
	keep if quotation_basis=="PCL"
	* only in kind for listed stocks and investment fund participations (F511, F52)
	*! keep if quotation_basis=="CCY"
********************************************************************************
* Data aggregation
********************************************************************************

		* new holder sector definition
		gen holder_sector_new = "banks" if holder_sector=="S_122"
		replace holder_sector_new = "insur" if holder_sector=="S_128" 
		replace holder_sector_new = "pfund" if holder_sector=="S_129" 
		replace holder_sector_new = "invfd" if holder_sector=="S_123" | holder_sector=="S_124"  
		replace holder_sector_new = "hhold" if holder_sector=="S_14" | holder_sector=="S_15" | holder_sector=="S_1MU"
		replace holder_sector_new = "omfis" if holder_sector=="S_125A" | holder_sector=="S_125W"
		replace holder_sector_new =  "gov" if holder_sector=="S_1311" | holder_sector=="S_1312" | holder_sector=="S_1313" | holder_sector=="S_1314" | holder_sector=="S_13U"
		replace holder_sector_new = "nonfc" if holder_sector=="S_11"
		drop holder_sector
		encode holder_sector_new, gen(holder_sector)
		*!  table period if holder_sector=="S_16" | holder_sector=="U" , statistic(sum obs_value) 
    
  
		* holder country - holder sector level
		egen id1 = group(identifier holder_area holder_sector period)
		bys id1: egen temp = total(obs_value)
		replace obs_value = temp
		bys id1: gen tempvar = _n
		drop if tempvar > 1
		drop temp tempvar id1 functional_category


********************************************************************************	
* Final cleaning after data aggregations
********************************************************************************

		* for bonds and money market
		*! otherwise market_cap_eur instead of amount_out_eur for F511 and F52
		replace obs_value =  amount_out_eur if obs_value> amount_out_eur & obs_value!=. & amount_out_eur!=.
		keep if obs_value>10000
		
		* for BONDS - drop unneccesary variables for better performance
		drop   /* obs_status */ identifier_type instr_class dividend_amount dividend_amount_type dividend_currency dividend_settl_date last_split_factor last_split_date market_cap_eur market_cap conf_status conf_status_calc sensitivity escb
		drop  accr_interest accr_income_factor  issue_price  last_coupon_date last_coupon_freq    poolfactor orig_mat_days orig_mat_bracket unit_measure 
	*! add more drops of irrelevant vars.
	
********************************************************************************	
* Generate variables
********************************************************************************

* use "G:\ALGEMEEN\SHS\Projecten\Concentratie\SHS Workshop\Do files\F32_13Q4-24Q1_clean_v20240805.dta"

	gen hold_ln = log(obs_value)
	
	winsor price_value, gen(price_value_w) p(0.05)
	*! prices between 80-120 (for 13q4-24q1 time-series)
	
	gen mv	= (amount_out_eur*price_value_w)/100
	gen mvsize_ln = log(mv)

	
	gen EUR =.
	replace EUR = 1 if nom_curr=="EUR" 
	replace EUR = 0 if nom_curr!="EUR" 

	gen home=.
	replace home = 0 if holder_area!=issuer_country
	replace home = 1 if holder_area==issuer_country


egen holder_area_num = group(holder_area)
egen holder_sector_num = group(holder_sector_new)
egen issuer_country_num = group(issuer_country)

********************************************************************************
* benchmark investor sectors: gov, nonfc 
********************************************************************************			
gen 		EUR_banks 	= 0
replace 	EUR_banks	= EUR if holder_sector_new=="banks" 

gen 		EUR_hhold 	= 0
replace 	EUR_hhold	= EUR if holder_sector_new=="hhold"

gen 		EUR_insur 	= 0
replace 	EUR_insur	= EUR if holder_sector_new=="insur" 

gen 		EUR_omfis 	= 0
replace 	EUR_omfis	= EUR if holder_sector_new=="omfis"

gen 		EUR_invfund 	= 0
replace 	EUR_invfund	= EUR if holder_sector_new=="invfund"

gen 		EUR_pfund	= 0
replace 	EUR_pfund	= EUR if holder_sector_new=="pfund"


*			
gen 		mvsize_ln_banks 	= 0
replace 	mvsize_ln_banks	= mvsize_ln if holder_sector_new=="banks" 

gen 		mvsize_ln_hhold 	= 0
replace 	mvsize_ln_hhold	= mvsize_ln if holder_sector_new=="hhold"

gen 		mvsize_ln_insur 	= 0
replace 	mvsize_ln_insur	= mvsize_ln if holder_sector_new=="insur" 

gen 		mvsize_ln_omfis 	= 0
replace 	mvsize_ln_omfis	= mvsize_ln if holder_sector_new=="omfis"

gen 		mvsize_ln_invfund 	= 0
replace 	mvsize_ln_invfund	= mvsize_ln if holder_sector_new=="invfund"

gen 		mvsize_ln_pfund	= 0
replace 	mvsize_ln_pfund	= mvsize_ln if holder_sector_new=="pfund"

gen id_panel_sj = identifier + " " + holder_area + " " + holder_sector_new 
gen id_panel_cluster = holder_area + " " + holder_sector_new 
********************************************************************************
save "regression_crosssection_2024.dta", replace
********************************************************************************	
** Regressions cross-section
********************************************************************************
local date 20240805_v2.02
********************************************************************************	
* keep if if qdate==tq(2024q1)
********************************************************************************
reg 	hold_ln 	EUR EUR_banks  	 EUR_omfis  EUR_insur   EUR_invfund EUR_pfund EUR_hhold  ///
						mvsize_ln mvsize_ln_banks  	 mvsize_ln_omfis  mvsize_ln_insur   mvsize_ln_invfund mvsize_ln_pfund mvsize_ln_hhold  ///
						i.holder_area_num  i.holder_sector_num i.holder_area_num##c.holder_sector_num i.issuer_country_num   ///
							, rob cluster(id_panel_cluster)