Calculate monthly betas from rolling window regressions

[Christopher Koedding]

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

// 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

Method 2: rolling command (takes quite some time)

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.

This is very similar to http://www.statalist.org/forums/foru...68#post1355368, in particular to Clyde's code:

rolling _b, window(60) saving(betas, replace) reject(e(N) < 24): regress stockexcessret mktrf

Method 3: Compute regression coefficients "by hand" (seems to be much faster, but more prone to typos)

// 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
    }

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