/***********************************************************************************************************/ /***********************************************************************************************************/ *This do-file reads, processes and merges data from Dealscan tables * *Input *a) Various Dealscan tables /***********************************************************************************************************/ /***********************************************************************************************************/ ***************************************************************** *** 0. General settings ***************************************************************** clear all global path "C:\Users\tobia\Dropbox\6_WIP Papers\Trends in Corporate Borrowing" ************************************************************** *0. Dealscan data ************************************************************* /****************************************************************/ /********** 1. Read various innput files **********************/ /****************************************************************/ /*a) Dealscan: Facility infos*/ use "$path\0_Data\Dealscan\Facility", clear /*Unique Key = FacilityID*/ keep FacilityID PackageID LoanType Maturity FacilityAmt PrimaryPurpose FacilityStartDate Currency /*Loan Type 2*/ gen LoanType2 = "TermLoan" if inlist(LoanType, "Term Loan", "Term Loan A", "Term Loan B", "Term Loan C", "Term Loan D", "Term Loan E", "Term Loan F", "Term Loan F", "Term Loan H") replace LoanType2 = "TermLoan" if inlist(LoanType, "Delay Draw Term Loan") replace LoanType2 = "Revolver" if inlist(LoanType, "Revolver/Line < 1 Yr.", "Revolver/Line >= 1 Yr.", "364-Day Facility", "Limited Line") replace LoanType2 = "Revolver" if inlist(LoanType, "Revolver/Term Loan") replace LoanType2 = "Other" if missing(LoanType2) /*Loan Type 3*/ gen LoanType3 = LoanType2 replace LoanType3 = "Revolver" if LoanType2 == "Other" & inlist(LoanType, "Bridge Loan", "Acquisition Facility") /*Loan Type 4*/ gen LoanType4 = "TermLoanB" if inlist(LoanType, "Term Loan B", "Term Loan C", "Term Loan D", "Term Loan E", "Term Loan F", "Term Loan F", "Term Loan H") replace LoanType4 = "TermLoanA" if inlist(LoanType, "Term Loan", "Term Loan A", "Delay Draw Term Loan") replace LoanType4 = "Revolver" if inlist(LoanType, "Revolver/Line < 1 Yr.", "Revolver/Line >= 1 Yr.", "364-Day Facility", "Limited Line", "Revolver/Term Loan") replace LoanType4 = "Other" if missing(LoanType2) /*Primary Purposes 2*/ gen PrimaryPurpose2 = "Corp. purposes" if inlist(PrimaryPurpose, "Corp. purposes") replace PrimaryPurpose2 = "Transactions (e.g. M&A, LBO)" if inlist(PrimaryPurpose, "Acquis. line", "Takeover", "Merger", "Spinoff", "LBO", "MBO", "SBO", "Dividend Recap") replace PrimaryPurpose2 = "WC" if inlist(PrimaryPurpose, "Work. cap.") replace PrimaryPurpose2 = "CPBackup" if inlist(PrimaryPurpose, "CP backup") replace PrimaryPurpose2 = "Other" if PrimaryPurpose2 == "" replace PrimaryPurpose2 = "Other" if LoanType3 == "TermLoan" & inlist(PrimaryPurpose2, "CPBackup", "WC", "Other") /*Scaling and variables*/ replace FacilityAmt = FacilityAmt / 1000000 /*Facility amount in USD mn*/ replace Maturity = Maturity/12 /*Maturity in years*/ gen year = year(FacilityStartDate) sort FacilityID save "$path\2_IntermediateFiles\FacilityV2", replace /*b) Dealscan: Package infos (Issue Date, Purpose, Amount)*/ use "$path\0_Data\Dealscan\Package", clear keep BorrowerCompanyID PackageID DealActiveDate RefinancingIndicator DealPurpose DealAmount *gen year = year(DealActiveDate) *gen quarter = yq(year(DealActiveDate), quarter(DealActiveDate)) *format quarter %tq *sort BorrowerCompanyID DealActiveDate *by BorrowerCompanyID: gen dealnumber = _n sort PackageID save "$path\2_IntermediateFiles\PackageV2", replace /*c) Dealscan: Borrower infos*/ use "$path\0_Data\Dealscan\Company", clear /*Unique Key = BorrowerCompanyID*/ keep CompanyID Company PrimarySICCode PublicPrivate Region Country ren CompanyID BorrowerCompanyID sort BorrowerCompanyID save "$path\2_IntermediateFiles\CompV2", replace /*d) Dealscan: Pricing Info*/ use "$path\0_Data\Dealscan\CurrFacPricing", clear replace BaseRate = trim(BaseRate) replace Fee = trim(Fee) keep FacilityID BaseRate Fee MinBps MaxBps AllInDrawn AllInUndrawn rename AllInDrawn AISD rename AllInUndrawn AISU gen _LIBOR = MaxBps if BaseRate == "LIBOR" bysort FacilityID: egen LIBOR = max(_LIBOR) drop _LIBOR gen _AFR = MaxBps if Fee == "Annual Regular Fee" bysort FacilityID: egen AFR = max(_AFR) drop _AFR gen _CF = MaxBps if Fee == "Commitment Regular Fee" bysort FacilityID: egen CF = max(_CF) drop _CF gen _UFR = MaxBps if Fee == "Upfront Regular Fee" bysort FacilityID: egen UFR = max(_UFR) drop _UFR gen _LCF = MaxBps if Fee == "Standby LC" bysort FacilityID: egen LCF = max(_LCF) drop _LCF gen _UTF = MaxBps if Fee == "Utilization Fee" bysort FacilityID: egen UTF = max(_UTF) drop _UTF gen _CAF = MaxBps if Fee == "Cancellation Fee" bysort FacilityID: egen CAF = max(_CAF) drop _CAF gen _TLF = MaxBps if Fee == "Trade LC" bysort FacilityID: egen TLF = max(_TLF) drop _TLF gen _TOF = MaxBps if Fee == "Term-Out Fee" bysort FacilityID: egen TOF = max(_TOF) drop _TOF gen _EXF = MaxBps if Fee == "Extension Fee" bysort FacilityID: egen EXF = max(_EXF) drop _EXF gen _DLF = MaxBps if Fee == "Documentary LC Fee" bysort FacilityID: egen DLF = max(_DLF) drop _DLF gen _CMF = MaxBps if Fee == "Collateral Monitoring Fee" bysort FacilityID: egen CMF = max(_CMF) drop _CMF gen _MGF = MaxBps if Fee == "Mgt & u/w fee" bysort FacilityID: egen MGF = max(_MGF) drop _MGF gen _CBF = MaxBps if Fee == "Combined fees" bysort FacilityID: egen CBF = max(_CBF) drop _CBF gen _SEC = MaxBps if Fee == "Selling concession" bysort FacilityID: egen SEC = max(_SEC) drop _SEC gen _ADF = MaxBps if Fee == "Additional fee" bysort FacilityID: egen ADF = max(_ADF) drop _ADF gen _POF = MaxBps if Fee == "Put-option waiver fee" bysort FacilityID: egen POF = max(_POF) drop _POF gen _GUF = MaxBps if Fee == "Guarantee Fee" bysort FacilityID: egen GUF = max(_GUF) drop _GUF gen baseratetype = 1 if BaseRate == "LIBOR" /*~168,000 entries*/ replace baseratetype = 2 if BaseRate == "Prime" /*~52,000 entries*/ replace baseratetype = 3 if BaseRate == "Fixed Rate" /*~20,000 entries*/ replace baseratetype = 4 if BaseRate == "Euribor" /*~17,000 entries*/ replace baseratetype = 5 if missing(baseratetype) /*Ohter base rates: ~32,000 entries"*/ replace baseratetype = 6 if missing(BaseRate) /*Missing base rates*/ sort FacilityID baseratetype duplicates drop FacilityID, force *Known errors replace UFR = . if UFR == 100000 /*<<0.01% of cases*/ keep FacilityID BaseRate AISD AISU LIBOR AFR CF UFR LCF UTF CAF TLF TOF EXF DLF CMF MGF CBF SEC ADF POF GUF MinBps MaxBps sort FacilityID save "$path\2_IntermediateFiles\PricingV2", replace /*e) Dealscan: Financial covenant */ use "$path/0_Data/Dealscan/financialcovenant.dta", clear keep PackageID bys PackageID: egen nCovenant_financial = count(PackageID) duplicates drop PackageID, force sort PackageID save "$path/2_IntermediateFiles/financialcovenantV2.dta", replace /*f) Dealscan: Net Worth Covenant*/ use "$path/0_Data/Dealscan/networthcovenant.dta", clear keep PackageID bys PackageID: egen nCovenant_networth = count(PackageID) duplicates drop PackageID, force sort PackageID save "$path/2_IntermediateFiles/networthcovenantV2.dta", replace /* Dealscan: Market Segment Data*/ use "$path/0_Data/Dealscan/marketsegment.dta", clear by FacilityID, sort: gen id=_n rename MarketSegment marketsegment gen igrade = inlist(marketsegment, "Investment Grade") gen leveraged = inlist(marketsegment, "Leveraged", "Highly Leveraged", "Non Investment Grade") gen covlite = inlist(marketsegment, "Covenant Lite") collapse (max) igrade leveraged covlite, by(FacilityID) keep FacilityID igrade leveraged covlite sort FacilityID save "$path/2_IntermediateFiles/marketsegmentV2.dta", replace /*******************************************************************************/ /**** 2. Create master data by merging the adjusted data sets ****************/ /******************************************************************************/ *a) Dealscan: Facility* use "$path\2_IntermediateFiles\FacilityV2", clear *b) Dealscan: Package infos sort PackageID merge PackageID using "$path\2_IntermediateFiles\PackageV2" keep if _merge == 3 /*note: >99.99% of facilities have package information available*/ drop _merge *c) Dealscan: Borrower infos sort BorrowerCompanyID merge BorrowerCompanyID using "$path\2_IntermediateFiles\CompV2" *drop if _merge == 2 keep if _merge == 3 drop _merge * d) Dealscan: Pricing infos sort FacilityID merge FacilityID using "$path\2_IntermediateFiles\PricingV2" drop if _merge == 2 /*keep also those without pricing information*/ drop _merge * e) Dealscan: Financial Covenants sort PackageID merge PackageID using "$path/2_IntermediateFiles/financialcovenantV2.dta" drop if _merge==2 drop _merge * f) Dealscan: Net Worth Covenants sort PackageID merge PackageID using "$path/2_IntermediateFiles/networthcovenantV2.dta" drop if _merge == 2 drop _merge egen nCovenant = rowtotal(nCovenant_financial nCovenant_networth) if nCovenant_financial != . | nCovenant_networth != . *g) Merge with Market Segment data sort FacilityID merge FacilityID using "$path/2_IntermediateFiles/marketsegmentV2.dta" drop if _merge == 2 drop _merge * h) Sample restrictions * Exclude financial firms and utilities from the sample drop if inrange(PrimarySICCode, 6000, 6999) /*drop financials*/ drop if inrange(PrimarySICCode, 4900, 4949) /*drop utilities */ *Sample period keep if year >= 2002 & year <= 2019 *Minimum data requirements keep if Maturity > 0 & Maturity != . /*Maturity available*/ keep if FacilityAmt > 0 & FacilityAmt != . /*FacilityAmt available*/ *i) Save sort FacilityID save "$path/2_IntermediateFiles/Master_Dealscan.dta", replace