/***********************************************************************************************************/
/***********************************************************************************************************/
*This do-file produces Figure 1 ("Debt structure")
* 
*Input
*- Master_CapitalIQ_Compustat.dta
/***********************************************************************************************************/
/***********************************************************************************************************/


/****************************************************************/
/**********   0. Intros   ***************************************/
/****************************************************************/
set more off
global path "C:\Users\tobia\Dropbox\6_WIP Papers\Trends in Corporate Borrowing"

/****************************************************************/
/**********   1. Graph for the U.S.  *****************************/
/****************************************************************/

	use "$path/2_IntermediateFiles/Master_CapitalIQ_Compustat.dta", clear
	
		*Sample
		keep if fic == "USA"
		
		*Footnote in Section 3 (Europe): statements about firm size
		tabstat at , s(p25 median mean p75)
		table year, c(sum at)
		table year if at > 300, c(sum at)
		
		*Collapse
		collapse (sum) at che undrawncrdtportionrevolvingcrdt outstandingbalrrevolvingcredit srbondsandnotes generalotherborrowings outstandingbaltermloans totoutstbalcommercialpaper outstandingbalcapitalleases subordinatedbondsandnotes (mean) GDP , by(year)
			
		/*Percentage relative to GDP*/
			gen fractionsrbonds = srbondsandnotes/GDP
			gen fractioncompaper = totoutstbalcommercialpaper/GDP
			gen fractionsubordinated = subordinatedbondsandnotes/GDP
			
			gen fractiontermloans = outstandingbaltermloans/GDP
			gen fractionrevolver_drawn = outstandingbalrrevolvingcredit/GDP
			
			gen fractionrevolver_undrawn = undrawncrdtportionrevolvingcrdt/GDP
			
			gen fractionleases = outstandingbalcapitalleases/GDP

				*Aggregates across items
				gen bankdebt_onbalance = fractiontermloans + fractionrevolver_drawn
				gen bankdebt_onandoffbalance = bankdebt_onbalance + fractionrevolver_undrawn
				gen revolverdebt = fractionrevolver_drawn + fractionrevolver_undrawn
				gen bonddebt = fractionsrbonds + fractioncompaper + fractionsubordinated
			
		/*Relative to total assets*/
			gen fractionsrbonds_ta = srbondsandnotes/at
			gen fractioncompaper_ta = totoutstbalcommercialpaper/at
			gen fractionsubordinated_ta = subordinatedbondsandnotes/at
			
			gen fractiontermloans_ta = outstandingbaltermloans/at
			gen fractionrevolver_drawn_ta = outstandingbalrrevolvingcredit/at
			
			gen fractionrevolver_undrawn_ta = undrawncrdtportionrevolvingcrdt/at
			
			gen fractionleases_ta = outstandingbalcapitalleases/at
			
				*Aggregates across items
				gen bankdebt_onbalance_ta = fractiontermloans_ta + fractionrevolver_drawn_ta
				gen bankdebt_onandoffbalance_ta = bankdebt_onbalance_ta + fractionrevolver_undrawn_ta
				gen revolverdebt_ta = fractionrevolver_drawn_ta + fractionrevolver_undrawn_ta
				gen bonddebt_ta = fractionsrbonds_ta + fractioncompaper_ta + fractionsubordinated_ta
			
		*Cash holdings
			gen che_gdp = che / GDP
			gen che_ta = che / at
		
		/* Loan/Bond-ratio */
		gen ratioloanbond = bankdebt_onandoffbalance / bonddebt
		
		/*Figure*/
		clonevar yearx=year
		replace yearx = year+0.3
		
		clonevar yearxx=year
		replace yearxx = year+0.6
				
		#d ;
		twoway (bar bankdebt_onbalance  year, color (gs6) barwidth(0.3)  yaxis(1))
				(rbar bankdebt_onbalance bankdebt_onandoffbalance year, color (gs9) barwidth(0.3)  yaxis(1))	
				(bar bonddebt yearx, color (gs13) barwidth(0.3) yaxis(1))
				(line ratioloanbond year, color(gs0) yaxis(2))
				,graphregion(fcolor(white) lcolor(white) ifcolor(white) ilcolor(white))
				legend(pos(6) ring(50) col(1) order(1 "Loans (term loans + drawn credit lines)" 2 "Loans (undrawn credit lines)" 3 "Bonds" 4 "Loan/Bond-ratio (loans include undrawn credit lines)"))
				xtitle("Year") 
				xlabel(2002(1)2019, labsize(small) angle(vertical)) xmtick(2002(1)2019, nolabels ticks)
				ytitle("Fraction of GDP", axis(1))
				ytitle("Loan/Bond-ratio", axis(2))
				ylabel(0.00(0.05)0.25, labsize(small) angle(90) nogrid axis(1))
				ylabel(0.00(0.05)0.45, labsize(small) angle(90) nogrid axis(2))			
				;
		#d cr
		graph		export "$path/3_Results/Figure1.pdf", replace
		
		/*Figure + robustness as tables*/
		tabstat bankdebt_onbalance bankdebt_onandoffbalance bonddebt ratioloanbond, by(year) format(%9.3f)
				tabstat fractiontermloans fractionrevolver_drawn fractionrevolver_undrawn, by(year) format(%9.3f)
				
		tabstat bankdebt_onbalance_ta bankdebt_onandoffbalance_ta bonddebt_ta ratioloanbond, by(year) format(%9.3f)
				tabstat fractiontermloans_ta fractionrevolver_drawn_ta fractionrevolver_undrawn_ta, by(year) format(%9.3f)
		
		/*TREND 3: Statement on increase in cash holdings*/
		tabstat che_gdp che_ta, by(year) format(%9.3f)
		
	
/****************************************************************/
/**********   2. Graph for Eurozone *****************************/
/****************************************************************/

	use "$path/2_IntermediateFiles/Master_CapitalIQ_Compustat.dta", clear
	
		*Sample
		keep if inlist(fic, "AUT", "BEL", "DEU", "FIN", "FRA", "GRC", "IRL", "ITA", "LUX") | inlist(fic, "NLD", "PRT", "ESP")
		
		*Footnote in Section 3 (Europe): statements about firm size
		tabstat at , s(p25 median mean p75)
		table year, c(sum at)
		table year if at > 300, c(sum at)
		
		*Collapse (same as for US, but with different GDP measure)
		collapse (sum) at che undrawncrdtportionrevolvingcrdt outstandingbalrrevolvingcredit srbondsandnotes generalotherborrowings outstandingbaltermloans totoutstbalcommercialpaper outstandingbalcapitalleases subordinatedbondsandnotes (mean) GDP=GDP_Euro12 , by(year)
			
		/*Percentage relative to GDP*/
			gen fractionsrbonds = srbondsandnotes/GDP
			gen fractioncompaper = totoutstbalcommercialpaper/GDP
			gen fractionsubordinated = subordinatedbondsandnotes/GDP
			
			gen fractiontermloans = outstandingbaltermloans/GDP
			gen fractionrevolver_drawn = outstandingbalrrevolvingcredit/GDP
			
			gen fractionrevolver_undrawn = undrawncrdtportionrevolvingcrdt/GDP
			
			gen fractionleases = outstandingbalcapitalleases/GDP

				*Aggregates across items
				gen bankdebt_onbalance = fractiontermloans + fractionrevolver_drawn
				gen bankdebt_onandoffbalance = bankdebt_onbalance + fractionrevolver_undrawn
				gen revolverdebt = fractionrevolver_drawn + fractionrevolver_undrawn
				gen bonddebt = fractionsrbonds + fractioncompaper + fractionsubordinated
			
		/*Relative to total assets*/
			gen fractionsrbonds_ta = srbondsandnotes/at
			gen fractioncompaper_ta = totoutstbalcommercialpaper/at
			gen fractionsubordinated_ta = subordinatedbondsandnotes/at
			
			gen fractiontermloans_ta = outstandingbaltermloans/at
			gen fractionrevolver_drawn_ta = outstandingbalrrevolvingcredit/at
			
			gen fractionrevolver_undrawn_ta = undrawncrdtportionrevolvingcrdt/at
			
			gen fractionleases_ta = outstandingbalcapitalleases/at
			
				*Aggregates across items
				gen bankdebt_onbalance_ta = fractiontermloans_ta + fractionrevolver_drawn_ta
				gen bankdebt_onandoffbalance_ta = bankdebt_onbalance_ta + fractionrevolver_undrawn_ta
				gen revolverdebt_ta = fractionrevolver_drawn_ta + fractionrevolver_undrawn_ta
				gen bonddebt_ta = fractionsrbonds_ta + fractioncompaper_ta + fractionsubordinated_ta
			
		*Cash holdings
			gen che_gdp = che / GDP
			gen che_ta = che / at
		
		/* Loan/Bond-ratio */
		gen ratioloanbond = bankdebt_onandoffbalance / bonddebt
		
		/*Figure*/
		clonevar yearx=year
		replace yearx = year+0.3
		
		clonevar yearxx=year
		replace yearxx = year+0.6
				
		#d ;
		twoway (bar bankdebt_onbalance  year, color (gs6) barwidth(0.3)  yaxis(1))
				(rbar bankdebt_onbalance bankdebt_onandoffbalance year, color (gs9) barwidth(0.3)  yaxis(1))	
				(bar bonddebt yearx, color (gs13) barwidth(0.3) yaxis(1))
				(line ratioloanbond year, color(gs0) yaxis(2))
				,graphregion(fcolor(white) lcolor(white) ifcolor(white) ilcolor(white))
				legend(pos(6) ring(50) col(1) order(1 "Loans (term loans + drawn credit lines)" 2 "Loans (undrawn credit lines)" 3 "Bonds" 4 "Loan/Bond-ratio (loans include undrawn credit lines)"))
				xtitle("Year") 
				xlabel(2002(1)2019, labsize(small) angle(vertical)) xmtick(2002(1)2019, nolabels ticks)
				ytitle("Fraction of GDP", axis(1))
				ytitle("Loan/Bond-ratio", axis(2))
				ylabel(0.00(0.05)0.25, labsize(small) angle(90) nogrid axis(1))
				ylabel(0.00(0.1)1.00, labsize(small) angle(90) nogrid axis(2))			
				;
		#d cr
		graph		export "$path/3_Results/Figure1 (Europe).pdf", replace
		
		/*Figure + robustness as tables*/
		tabstat bankdebt_onbalance bankdebt_onandoffbalance bonddebt ratioloanbond, by(year) format(%9.3f)
				tabstat fractiontermloans fractionrevolver_drawn fractionrevolver_undrawn, by(year) format(%9.3f)
				
		tabstat bankdebt_onbalance_ta bankdebt_onandoffbalance_ta bonddebt_ta ratioloanbond, by(year) format(%9.3f)
				tabstat fractiontermloans_ta fractionrevolver_drawn_ta fractionrevolver_undrawn_ta, by(year) format(%9.3f)
		
		/*TREND 3: Statement on increase in cash holdings*/
		tabstat che_gdp che_ta, by(year) format(%9.3f)