Dear Statalisters,
I have a panel dataset of stock and market returns where permno is the panel variable and date (or D, respectively) is the time variable. The data has been tsset. The sample of interest covers the period from January 1996 through October 2012.
I want to run rolling regressions of excess stock returns (ret_rf) on excess market returns (MSCI_RF) and lagged excess market returns (MSCI_RF_1) to collect betas for each stock (permno). Specifically, I want to use rolling windows of 60 months. So using the data from Febraury 1991 through January 1996, inclusive, should give me the beta for January 1996. Also, I require a minimum of 24 monthly observations. If there are less than 24 monthly observations, beta should be set to missing.
So far, I've tried to achieve this in three different ways. What confuses me is that all three methods yield different results.
Method 1: looping and statsby command to collect betas
Method 2: rolling command (takes quite some time)
This is very similar to http://www.statalist.org/forums/foru...68#post1355368, in particular to Clyde's code:
Method 3: Compute regression coefficients "by hand" (seems to be much faster, but more prone to typos)
So my questions are:
Q1: Why do the methods yield different results? Where did I make mistakes here?
Q2: Which one do you recommend to use in my case?
Thank you very much in advance.
Best,
Christopher
I have a panel dataset of stock and market returns where permno is the panel variable and date (or D, respectively) is the time variable. The data has been tsset. The sample of interest covers the period from January 1996 through October 2012.
I want to run rolling regressions of excess stock returns (ret_rf) on excess market returns (MSCI_RF) and lagged excess market returns (MSCI_RF_1) to collect betas for each stock (permno). Specifically, I want to use rolling windows of 60 months. So using the data from Febraury 1991 through January 1996, inclusive, should give me the beta for January 1996. Also, I require a minimum of 24 monthly observations. If there are less than 24 monthly observations, beta should be set to missing.
So far, I've tried to achieve this in three different ways. What confuses me is that all three methods yield different results.
Method 1: looping and statsby command to collect betas
Code:
// Monthly sample: months from Jan1996-Oct2012 local k = 1996*12+1 // k=Jan1996; t=i, i.e. time-t beta = time-i beta --> time-t+1 return local l = 2012*12+10 // l=Oct2012 forvalues i=`k'(1)`l'{ disp `i' quietly use date permno ret_rf MSCI_RF MSCI_RF_* if year(date)*12+month(date)<=`i'&year(date)*12+month(date)>`i'-60 using "C:\...\MSCI World\Output\MonthlyStockAndMarketExcessReturns.dta", clear quietly drop date if r(N)>=24 { // A minimum of 24 monthly observations is required. Otherwise beta should be set to missing. } quietly statsby _b e(rmse), by(permno) saving(C:/.../MSCI World/Output/betas`i', replace) nodots: reg ret_rf MSCI_RF MSCI_RF_1 } // Create new file with all beta estimates clear gen x=. save "C:\...\MSCI World\Output\betas_total.dta", replace // Append all separate beta files to one file containing all beta estimates local k=1996*12+2 // k=Feb1996 // first beta file to attend local l=2012*12+10 // l=Oct2012 // very last beta file (to attend) local j=1996*12+1 // j=Jan1996 // very first beta file use "C:\...\MSCI World\Output\betas`j'.dta", replace forvalues i=`k'(1)`l'{ quietly use "C:\...\MSCI World\Output\betas`i'.dta", replace quietly gen date2=`i' quietly append using "C:\...\MSCI World\Output\betas_total.dta" quietly save "C:\...\MSCI World\Output\betas_total.dta", replace } // Generate beta coefficients as the sum of regression slopes gen beta=_b_MSCI_RF + _b_MSCI_RF_1 // Here: one lagged market excess return gen sigma=_eq2_stat_1 gen year=int((date2-1)/12) gen month=date2-12*year keep beta sigma year month date2 permno sort permno date2 save "C:\...\MSCI World\Output\betas_total.dta", replace
Code:
rolling _b, window(60) saving(C:\...\MSCI World\Output\rolling_betas, replace) reject(e(N) < 24): reg ret_rf MSCI_RF MSCI_RF_1 // A minimum of 24 monthly observations is required. Otherwise beta should be set to missing.
Code:
rolling _b, window(60) saving(betas, replace) reject(e(N) < 24): regress stockexcessret mktrf
Code:
// Generate beta file for later clear gen x=. save "C:\...\MSCI World\Output\betas_total_monthly.dta", replace levelsof D, local(levels) foreach i of local levels{ disp `i' preserve quietly keep if D>=`i' & D<`i'+60 // Rolling window: 60 months // Generate "input" variables (for calculating regression coefficients) quietly cap drop S_Y quietly egen S_Y = sum(ret_rf) if ret_rf!=. & MSCI_RF!=. & MSCI_RF_1!=., by(permno) quietly cap drop S_X1 quietly egen S_X1 = sum(MSCI_RF) if ret_rf!=. & MSCI_RF!=. & MSCI_RF_1!=., by(permno) quietly cap drop S_X2 quietly egen S_X2 = sum(MSCI_RF_1) if ret_rf!=. & MSCI_RF!=. & MSCI_RF_1!=., by(permno) quietly cap drop Y_sq quietly gen Y_sq = ret_rf^2 if ret_rf!=. & MSCI_RF!=. & MSCI_RF_1!=. quietly cap drop S_Y_sq quietly egen S_Y_sq = sum(Y_sq) if ret_rf!=. & MSCI_RF!=. & MSCI_RF_1!=., by(permno) quietly cap drop X1_sq quietly gen X1_sq = MSCI_RF^2 if ret_rf!=. & MSCI_RF!=. & MSCI_RF_1!=. quietly cap drop S_X1_sq quietly egen S_X1_sq = sum(X1_sq) if ret_rf!=. & MSCI_RF!=. & MSCI_RF_1!=., by(permno) quietly cap drop X2_sq quietly gen X2_sq = MSCI_RF_1^2 if ret_rf!=. & MSCI_RF!=. & MSCI_RF_1!=. quietly cap drop S_X2_sq quietly egen S_X2_sq = sum(X2_sq) if ret_rf!=. & MSCI_RF!=. & MSCI_RF_1!=., by(permno) quietly cap drop X1Y quietly gen X1Y = MSCI_RF * ret_rf if ret_rf!=. & MSCI_RF!=. & MSCI_RF_1!=. quietly cap drop S_X1Y quietly egen S_X1Y = sum(X1Y) if ret_rf!=. & MSCI_RF!=. & MSCI_RF_1!=., by(permno) quietly cap drop X2Y quietly gen X2Y = MSCI_RF_1 * ret_rf if ret_rf!=. & MSCI_RF!=. & MSCI_RF_1!=. quietly cap drop S_X2Y quietly egen S_X2Y = sum(X2Y) if ret_rf!=. & MSCI_RF!=. & MSCI_RF_1!=., by(permno) quietly cap drop X1X2 quietly gen X1X2 = MSCI_RF * MSCI_RF_1 if ret_rf!=. & MSCI_RF!=. & MSCI_RF_1!=. quietly cap drop S_X1X2 quietly egen S_X1X2 = sum(X1X2) if ret_rf!=. & MSCI_RF!=. & MSCI_RF_1!=., by(permno) quietly cap drop N quietly egen N=count(X1Y), by(permno) // Generate "output" variables (for calculating regression coefficients) quietly cap drop S_y_sq quietly gen S_y_sq = S_Y_sq - S_Y^2 / N quietly cap drop S_x1_sq quietly gen S_x1_sq = S_X1_sq - S_X1^2 / N quietly cap drop S_x2_sq quietly gen S_x2_sq = S_X2_sq - S_X2^2 / N quietly cap drop S_x1y quietly gen S_x1y = S_X1Y - (S_X1 * S_Y) / N quietly cap drop S_x2y quietly gen S_x2y = S_X2Y - (S_X2 * S_Y) / N quietly cap drop S_x1x2 quietly gen S_x1x2 = S_X1X2 - (S_X1 * S_X2) / N quietly cap drop S_x1x2_sq quietly gen S_x1x2_sq = S_x1x2^2 // Compute the two regression coefficients (according to formula for two independent variables) quietly replace b_contemp = (S_x2_sq * S_x1y - S_x1x2 * S_x2y) / (S_x1_sq * S_x2_sq - S_x1x2_sq) if D==`i'+59 & N>=12 quietly replace b_L1 = (S_x1_sq * S_x2y - S_x1x2 * S_x1y) / (S_x1_sq * S_x2_sq - S_x1x2_sq) if D==`i'+59 & N>=12 // Compute beta coefficient as the sum of regression slopes quietly cap drop beta quietly gen beta = b_contemp + b_L1 quietly keep permno date date2 beta sigma2 N quietly append using "C:\...\MSCI World\Output\betas_total_monthly.dta" quietly save "C:\...\MSCI World\Output\betas_total_monthly.dta", replace restore }
Q1: Why do the methods yield different results? Where did I make mistakes here?
Q2: Which one do you recommend to use in my case?
Thank you very much in advance.
Best,
Christopher