Rolling market beta calculation (rolling rejected results from regress while using the entire dataset)

[Peter Eze]

Hi everyone!
I am using Stata 13. I have a strongly balanced panel data containing 473 firm's stocks from July 2000 to June 2016 . I am currently trying to calculate rolling market betas for each firm's stock using a maximum of 60 and a minimum of 24 monthly excess stock returns from months prior to month t. If less than 24 returns are available, market beta is set to missing.
I was able to try some Stata codes which I found in Stata forum (http://www.statalist.org/forums/foru...tas-for-stocks)

*Regression of stockexcessret on market value-weighted excessreturn (mktrf)
levelsof permno, local(permno)
foreach s of local permno {
    rolling _b, window(60) saving(betas_`s', replace) reject(e(N) < 24):  ///
       regress stockexcessret mktrf if permno == `s'                
}
levelsof permno, local(permnos)
foreach p of local permno {
    merge 1:1 permno dm using betas`p', nogenerate
}

I also tried the rangestat code too from the same link as above:

xtset permno month
save "test_data.dta", replace

* ------------ regressions over a window of 60 periods using -rangestat- --------
* define a linear regression in Mata using quadcross() - help mata cross(), 
mata:
mata clear
mata set matastrict on
real rowvector myreg(real matrix Xall)
{
    real colvector y, b, Xy
    real matrix X, XX

    y = Xall[.,1]                // dependent var is first column of Xall
    X = Xall[.,2::cols(Xall)]    // the remaining cols are the independent variables
    X = X,J(rows(X),1,1)         // add a constant
    
    XX = quadcross(X, X)        // linear regression, see help mata cross(), example 2
    Xy = quadcross(X, y)
    b  = invsym(XX) * Xy
    
    return(rows(X), b')
}
end

* regressions with a constant over a rolling window of 60 periods by permno
rangestat (myreg) stockexcessret mktrf, by(permno) interval(time -59 0) casewise

* the Mata function returns first the number of observations and then as many
* variables as there are independent variables (plus the constant) for the betas
rename (myreg1 myreg2 myreg3) (nobs rs_mktrf rs_cons)

* reject results if the window is less than 60 or if the number of obs < 24
isid permno month
by permno: replace rs_mktrf = . if _n < 60 | nobs < 24
by permno: replace rs_cons = . if _n < 60 | nobs < 24
save "rangestat_results.dta", replace

* ----------------- replicate using -rolling- ----------------------------------
use "test_data.dta", clear
levelsof permno, local(permno)
foreach s of local permno {
    rolling _b, window(60) saving(betas_`s', replace) reject(e(N) < 24):  ///
       regress stockexcessret mktrf if permno == `s'                
}

clear
save "betas.dta", replace emptyok
foreach s of local permno {
    append using "betas_`s'.dta"
}
rename end month
merge 1:1 permno month using "rangestat_results.dta"
isid permno month, sort

gen diff_mktrf =  abs(_b_mktrf - float(rs_mktrf))
gen diff_cons =  abs(_b_cons - float(rs_cons))
summ diff*

My problem is that for the first 44 stocks, the regressions are run properly,however, the loop stops on the 45th stock with the following message below:
rolling rejected results from regress while using the entire dataset
r(9);
All the two above codes still showed me the same result.
-> permno = 42

Rolling replications (133)
----+--- 1 ---+--- 2 ---+--- 3 ---+--- 4 ---+--- 5
.................................................. 50
.................................................. 100
.................................
file betas_42.dta saved
(running regress on estimation sample)

-> permno = 45

Rolling replications (133)
----+--- 1 ---+--- 2 ---+--- 3 ---+--- 4 ---+--- 5
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 50
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx................ 100
.................................
file betas_44.dta saved
(running regress on estimation sample)

-> permno = 45

Rolling replications (133)
----+--- 1 ---+--- 2 ---+--- 3 ---+--- 4 ---+--- 5
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 50
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 100
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx...
file betas_45.dta saved
(running regress on estimation sample)
rolling rejected results from regress while using the entire dataset
r(9);

end of do-file

r(9);


I have tried to regress stock 45 alone and even some other prior stocks as suggested in the stataforum link above , I got the result as no observation


regress stockexcessret mktrf if stock == 45
no observations
r(2000);




Below is a sample of my panel data

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(month permno stockexcessret mktrf) int time
486 1 .          .   1
487 1 .          .   2
488 1 .          .   3
489 1 .          .   4
490 1 .          .   5
491 1 .          .   6
492 1 .          .   7
493 1 .          .   8
494 1 .          .   9
495 1 .          .  10
496 1 .          .  11
497 1 .          .  12
498 1 .  -2.666597  13
499 1 .  3.5859656  14
500 1 .  -4.768708  15
501 1 . -4.7542515  16
502 1 .   .8977337  17
503 1 . -20.062456  18
504 1 .  13.092803  19
505 1 .  -9.423961  20
506 1 . -.01542697  21
507 1 .   9.851523  22
508 1 .   18.13945  23
509 1 .  -2.840696  24
510 1 .   2.641178  25
511 1 .  -8.554174  26
512 1 .    5.11478  27
513 1 .   5.957329  28
514 1 .  11.100168  29
515 1 .    5.30089  30
516 1 .   7.313002  31
517 1 .   2.284107  32
518 1 .   -3.55335  33
519 1 .   6.486986  34
520 1 .   .9358135  35
521 1 .   5.765306  36
522 1 .   4.773961  37
523 1 .   5.940833  38
524 1 .   10.34814  39
525 1 .   5.599309  40
526 1 .   9.423548  41
527 1 .  -.4387302  42
528 1 .  2.1820347  43
529 1 .  11.583542  44
530 1 .   3.107684  45
531 1 .  11.022295  46
532 1 .  -3.569324  47
533 1 .   3.057642  48
534 1 .   8.880916  49
535 1 .  -1.249484  50
536 1 .   9.944527  51
537 1 .   9.023089  52
538 1 .   25.71964  53
539 1 .  10.824612  54
540 1 .   7.584715  55
541 1 .  14.726665  56
542 1 .   5.745068  57
543 1 .  -5.350675  58
544 1 .  -1.273437  59
545 1 .  1.9274507  60
546 1 .   9.804824  61
547 1 .    8.53234  62
548 1 .   9.051862  63
549 1 .   8.378527  64
550 1 .  10.610908  65
551 1 .    7.01289  66
552 1 .  21.654003  67
553 1 . -1.6742957  68
554 1 .  -2.859784  69
555 1 .  10.776278  70
556 1 .  -2.765255  71
557 1 .  -9.189032  72
558 1 .  1.2453502  73
559 1 .   8.592383  74
560 1 .   6.241508  75
561 1 .   7.328311  76
562 1 .  18.458912  77
563 1 .   9.132421  78
564 1 .   6.122295  79
565 1 .   6.564981  80
566 1 .   7.204051  81
567 1 .   7.411033  82
568 1 .  -2.748398  83
569 1 .  -.3336661  84
570 1 .  10.429055  85
571 1 .   4.844315  86
572 1 .   54.17649  87
573 1 .  2.2536652  88
574 1 .   5.300388  89
575 1 . -3.4062684  90
576 1 .   5.909176  91
577 1 .   6.878344  92
578 1 .   6.149116  93
579 1 .   9.310979  94
580 1 .   3.699365  95
581 1 .  1.4750223  96
582 1 .  -9.142094  97
583 1 .  -5.088081  98
584 1 .  -3.735739  99
585 1 .  -26.71731 100
end
format %tm month

Please I need your help on what to do so as to generate betas for the entire dataset,thank you.

[Clyde Schechter]

Well, as I read the output, the problem is not arising with permno = 45. Stata says "file betas_45.dta saved", which implies that the iteration for permno = 45 has been successfully completed. The problem, I believe, is arising with whichever permno comes immediately after 45. I don't know what the problem is, but that is where you should look for it.

Another question. Do you have a reason for separately saving a file for each permno? In the end you are just appending them all together. But if you use -rolling- that is not necessary. In order to use -rolling- you will have already -tsset- (or -xtset-) your data, and I assume that -permno- is your panel variable. If that is so, just running -rolling-, outside of any loop and without any -if- qualifier will automatically do the rolling regressions separately for each permno. The value of permno will be saved in the betas file that you create. So all you need is:

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

to create a single file, identified by permno, with all of the results. (Changing this will simplify your code and possibly enable it to run faster--but it won't solve the problem arising with rejected regressions.)

[Peter Eze]

Yes!!! This worked perfectly well. Thank you Clyde Schechter for always being around to help out,I am very grateful.