Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Calculate monthly betas from rolling window regressions

    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
    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
    Method 2: rolling command (takes quite some time)
    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.
    This is very similar to http://www.statalist.org/forums/foru...68#post1355368, in particular to Clyde's code:
    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)
    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
        }
    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
    Last edited by Christopher Koedding; 14 Dec 2016, 04:39.
Working...
X