/******************************************************************************/
/*****************************Introductory remarks*****************************/
/******************************************************************************/

/*This do-file extracts fee information from Dealscan using the offline/CD-version 
of Dealscan.

As input, the do-file requires that the table "CurrFacPricing" has been converted
to dta-format and is available in the "path"-folder.

As output, this do-file produces a dta-file with the FacilityID in the first column
and various fee types in the following columns
*/

/*0. Intros*/

set more off
global path "XXX"

/*d) Dealscan: Spread and Fee Info*/
		use 		"$path\CurrFacPricing", clear 
			replace 	BaseRate = trim(BaseRate)
			replace 	Fee = trim(Fee)
			keep 		FacilityID BaseRate Fee MinBps MaxBps AllInDrawn AllInUndrawn
			rename		AllInDrawn AISD
			rename		AllInUndrawn AISU
			
			/*Note: There are no duplicates on the FacilityID/Fee-level:
			duplicates report duplicates report FacilityID Fee if Fee != ""
			The following code is similar to a "reshape wide", i.e. it puts Dealscan fee information,
			which is separate rows, into separate columns*/
			
			*Spread
			gen			_LIBOR = MaxBps if BaseRate == "LIBOR"
			bysort 		FacilityID: egen LIBOR = max(_LIBOR)
			drop		_LIBOR
			
			*Facility regular fee / Annual regular fee
			gen			_AFR = MaxBps if Fee == "Annual Regular Fee"
			bysort 		FacilityID: egen AFR = max(_AFR)
			drop		_AFR
			
			*Commitment regular fee
			gen			_CF = MaxBps if Fee == "Commitment Regular Fee"
			bysort 		FacilityID: egen CF = max(_CF)
			drop		_CF
			
			*Upfront regular fee
			gen			_UFR = MaxBps if Fee == "Upfront Regular Fee"
			bysort 		FacilityID: egen UFR = max(_UFR)
			drop		_UFR
			
			*Utilization fee
			gen			_UTF = MaxBps if Fee == "Utilization Fee"
			bysort 		FacilityID: egen UTF = max(_UTF)
			drop		_UTF
			
			*Cancellation fee
			gen			_CAF = MaxBps if Fee == "Cancellation Fee"
			bysort 		FacilityID: egen CAF = max(_CAF)
			drop		_CAF
			
			*Standby LC fee
			gen			_LCF = MaxBps if Fee == "Standby LC"
			bysort 		FacilityID: egen LCF = max(_LCF)
			drop		_LCF

			*Trade LC fee
			gen			_TLF = MaxBps if Fee == "Trade LC"
			bysort 		FacilityID: egen TLF = max(_TLF)
			drop		_TLF
			
			*Documentary LC fee
			gen			_DLF = MaxBps if Fee == "Documentary LC Fee"
			bysort 		FacilityID: egen DLF = max(_DLF)
			drop		_DLF
			
			*Term-out fee
			gen			_TOF = MaxBps if Fee == "Term-Out Fee"
			bysort 		FacilityID: egen TOF = max(_TOF)
			drop		_TOF
			
			*Extension fee
			gen			_EXF = MaxBps if Fee == "Extension Fee"
			bysort 		FacilityID: egen EXF = max(_EXF)
			drop		_EXF
			
			*Collateral Monitoring fee
			gen			_CMF = MaxBps if Fee == "Collateral Monitoring Fee"
			bysort 		FacilityID: egen CMF = max(_CMF)
			drop		_CMF	
			
			*Management fee 
			gen			_MGF = MaxBps if Fee == "Mgt & u/w fee"
			bysort 		FacilityID: egen MGF = max(_MGF)
			drop		_MGF	
			
			*Combined fee
			gen			_CBF = MaxBps if Fee == "Combined fees"
			bysort 		FacilityID: egen CBF = max(_CBF)
			drop		_CBF
			
			*Selling concession
			gen			_SEC = MaxBps if Fee == "Selling concession"
			bysort 		FacilityID: egen SEC = max(_SEC)
			drop		_SEC
			
			*Put-option waiver fee
			gen			_POF = MaxBps if Fee == "Put-option waiver fee"
			bysort 		FacilityID: egen POF = max(_POF)
			drop		_POF
			
			*Guarantee fee
			gen			_GUF = MaxBps if Fee == "Guarantee Fee"
			bysort 		FacilityID: egen GUF = max(_GUF)
			drop		_GUF
			
			*Additional fee
			gen			_ADF = MaxBps if Fee == "Additional fee"
			bysort 		FacilityID: egen ADF = max(_ADF)
			drop		_ADF
					
			*Wrap up
			keep 		if BaseRate != ""
			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
			
			/*Note: Final file contains duplicates on FacilityID-level (if multiple Base Rates exists, e.g. LIBOR or PRIME-based borrowing)
			Most papers focus on one base rate, e.g. LIBOR, which can easily be achieved by using "keep if BaseRate == "LIBOR"
			*/