* Author: Calvin Price
* Companion program for presentation "Estimating the Average Life of Non-Maturity Deposits"
* Stata Conference 2018
* Email: caprice@us.mufg.jp
*
* Purpose: Create toy data on customer balances over time, aggregate balances by customer account age, estimate decay rate of balances
* Written with Stata/IC 14.2
* =============================================================




* Create customers
* ------------------------------

clear
set obs 36
generate tmo=tm(2015m1)+_n-1
format %tmm_CY tmo 
tsset tmo


set seed 5678
foreach var of newlist z1-z200 {	// number of customers
	gen `var' = sum(round(10*runiform()))									// increasing balance for every customer
	local k1=36*runiform()													// starting month of account
	local k2=3*rchi2(1)														// age of account, choosing this distribution is what really forces our result
	replace `var' = 0 if !inrange(tmo, tmo[`k1'], tmo[`k1'+`k2'])
}
br tmo z1 z2 z27 z44 z45 z47


* To aggregate by account age, you need long shape data (panel data)
* Reshape from wide to long
reshape long z, i(tmo) j(Customer)
gsort Customer tmo

drop if z==0
bys Customer: gen Age = _n


* Plot individual customers  (Plot vs Time)
tsset Customer tmo
xtline z if Customer <=50 , overlay xsize(9) xlabel( , labsize(small)) ylabel( , labsize(small)) xtitle(" ") ytitle( "$ Dollars") legend(off) subtitle("Toy Data Example: Individual Customer Balances" "Plot vs Time") ///
	caption("Sample of 50 customers is shown. By design each customer is given an increasing balance with time." "When balances are aggregated by age the result will still be exponential decay.", size(vsmall))


* Plot individual customers  (Plot vs Age)
tsset Customer Age
xtline z if Customer <=50 , overlay legend(off) xlabel( , labsize(small)) ylabel( , labsize(small)) xtitle(" " "Age") ytitle( "$ Dollars" " " ) subtitle("Toy Data Example: Individual Customer Balances" "Plot vs Age") ///
	caption("Sample of 50 customers is shown. By design each customer is given an increasing balance with time." "When balances are aggregated by age the result will still be exponential decay.", size(vsmall))




* Group balances by age
* ------------------------------
collapse (sum) z , by(Age) 
scatter z Age , ms(oh) $g2  ytitle("Grouped Balance" " ") ylabel( , format(%8.0fc))  xtitle(" " "Account Age")

 

 
* Do the nl estimation
* ------------------------------
rename (Age z) (age GroupedBalance)
nl (GroupedBalance = {b1}*exp({b2}*age))
 

 
* Plot result
* ------------------------------
predict MyFitted, yhat
 
scatter GroupedBalance age ,  xscale(range(0 40)) mcolor(*.50) xtitle(" ") || line MyFitted age, xscale(range(0 40))   xtitle(" " "Account Age (months) ") ytitle("Grouped Balance" " ") lwidth(medium) legend(off) ///
			ylabel( , format(%8.0fc)) title("Balance Decay Function" ) ///
			caption(" " "Note: Decay Parameter = `: disp %5.3fc `=[b2]_cons'' "  , size(small) ) ///
			ttext(18000 4.0 "Average life = 3.38 months" , place(e) color(gray) j(left) size(small)) ///		
			xline(3.38 , lcolor(gray*.50) )