Estimating security betas using a panel data set

[Gabija Tinginyter]

Good afternoon,

I am currently working on my thesis and I need to estimate individual stocks' betas as well as to extract the outputs of the individual regressions to a column on Stata. I have a panel dataset of monthly stock returns over the period 1994-2009 (PERMNOs are used to identify the different stocks). I need the betas to be estimated using only the 60 month-period between 29jul1994 and 30jun1999. I understand that I need to do some sort of a for loop, but I am struggling to make it work. So far, I tried variations of the following code, but it does not seem to work as the values in Beta variable are all the same. Here is the code:

levelsof PERMNO, local(PERMNO)
foreach s of local PERMNO {
gen Beta=.
reg ExReturn mktrf if inrange(date,12628,14425)
replace Beta=_b[mktrf]
}

Here is an example of my dataset:

input double PERMNO long date str36 COMNAM double(PERMCO SHROUT) float Price str6 GVKEY str10 CUSIP double(mktrf rf) float ExReturn
10001 12599 "" 7953 2191 8.625 "" "" . . .
10001 12628 "ENERGY WEST INC" . 2191 9.25 "012994" "367204104" .0282 .0028000000000000004 .06966376
10001 12661 "ENERGY WEST INC" . 2191 9 "012994" "367204104" .0401 .0037 -.030727027
10001 12691 "ENERGY WEST INC" . 2211 9.25 "012994" "367204104" -.0231 .0037 .02407778
10001 12722 "ENERGY WEST INC" . 2211 8.75 "012994" "367204104" .0134 .0038 -.05785406
10001 12752 "ENERGY WEST INC" . 2211 8.375 "012994" "367204104" -.0404 .0037 -.04655714
10001 12782 "ENERGY WEST INC" . 2224 8 "012994" "367204104" .0086 .0044 -.04917612
10001 12814 "ENERGY WEST INC" 7953 2224 7.75 "012994" "367204104" .018000000000000002 .0042 -.03545
10001 12842 "ENERGY WEST INC" 7953 2224 7.546875 "012994" "367204104" .0363 .004 -.03020968
10001 12873 "ENERGY WEST INC" 7953 2244 7.5 "012994" "367204104" .0219 .0046 -.01081118
10001 12901 "ENERGY WEST INC" 7953 2244 7.5 "012994" "367204104" .021099999999999997 .0044 -.0044
10001 12934 "ENERGY WEST INC" 7953 2244 7.875 "012994" "367204104" .028999999999999998 .0054 .0446
10001 12964 "ENERGY WEST INC" 7953 2254 8.25 "012994" "367204104" .027200000000000002 .004699999999999999 .04291905
10001 12995 "ENERGY WEST INC" 7953 2254 8.25 "012994" "367204104" .037200000000000004 .0045000000000000005 -.0045
10001 13026 "ENERGY WEST INC" 7953 2254 8 "012994" "367204104" .0055000000000000005 .004699999999999999 -.035003033
10001 13055 "ENERGY WEST INC" 7953 2280 8.25 "012994" "367204104" .0335 .0043 .02695
10001 13087 "ENERGY WEST INC" 7953 2280 8 "012994" "367204104" -.0152 .004699999999999999 -.035003033
10001 13117 "ENERGY WEST INC" 7953 2280 8.75 "012994" "367204104" .039599999999999996 .0042 .08955
10001 13146 "ENERGY WEST INC" 7953 2281 9.375 "012994" "367204104" .0103 .0049 .06652857
10001 13179 "ENERGY WEST INC" 7953 2281 9.125 "012994" "367204104" .0226 .0043 -.03096667
10001 13208 "ENERGY WEST INC" 7953 2281 9.25 "012994" "367204104" .013300000000000001 .0039000000000000003 .00979863
10001 13237 "ENERGY WEST INC" 7953 2309 9.484375 "012994" "367204104" .0073 .0039000000000000003 .021437837
10001 13269 "ENERGY WEST INC" 7953 2309 8.8125 "012994" "367204104" .0206 .0046 -.0754402
10001 13300 "ENERGY WEST INC" 7953 2309 8.625 "012994" "367204104" .0236 .0042 -.025476595
10001 13328 "ENERGY WEST INC" 7953 2321 8 "012994" "367204104" -.011399999999999999 .004 -.07646377
10001 13361 "ENERGY WEST INC" 7953 2321 8.1875 "012994" "367204104" -.059699999999999996 .0045000000000000005 .0189375
10001 13391 "ENERGY WEST INC" 7953 2321 8.5 "012994" "367204104" .0277 .0040999999999999995 .03406794
10001 13422 "ENERGY WEST INC" 7953 2346 8.75 "012994" "367204104" .0501 .0044 .025011765
10001 13453 "ENERGY WEST INC" 7953 2346 8.5 "012994" "367204104" .0086 .0042 -.032771427
10001 13482 "ENERGY WEST INC" 7953 2346 8.75 "012994" "367204104" .0625 .0040999999999999995 .025311764
10001 13514 "ENERGY WEST INC" 7953 2357 8.125 "012994" "367204104" -.017 .0046 -.07602858
10001 13545 "ENERGY WEST INC" 7953 2357 8.625 "012994" "367204104" .049800000000000004 .0045000000000000005 .05703846
10001 13573 "ENERGY WEST INC" 7953 2357 8.625 "012994" "367204104" -.0049 .0039000000000000003 -.0039
10001 13604 "ENERGY WEST INC" 7953 2357 8.625 "012994" "367204104" -.050199999999999995 .0043 -.0043
10001 13634 "ENERGY WEST INC" 7953 2357 8.625 "012994" "367204104" .0404 .0043 -.0043
10001 13664 "ENERGY WEST INC" 7953 2357 8.625 "012994" "367204104" .0674 .0049 -.0049
10001 13695 "ENERGY WEST INC" 7953 2357 8.25 "012994" "367204104" .040999999999999995 .0037 -.04717826
10001 13726 "ENERGY WEST INC" 7953 2357 8.625 "012994" "367204104" .0733 .0043 .04115455
10001 13755 "ENERGY WEST INC" 7953 2357 8.75 "012994" "367204104" -.0415 .0040999999999999995 .010392754
10001 13787 "ENERGY WEST INC" 7953 2378 8.875 "012994" "367204104" .0535 .0044 .009885714
10001 13818 "ENERGY WEST INC" 7953 2378 8.875 "012994" "367204104" -.038 .0042 -.0042
10001 13846 "ENERGY WEST INC" 7953 2378 8.75 "012994" "367204104" .0298 .0039000000000000003 -.017984508
10001 13879 "ENERGY WEST INC" 7953 2395 9 "012994" "367204104" .0132 .0048 .02377143
10001 13909 "ENERGY WEST INC" 7953 2395 9 "012994" "367204104" .0015 .0043 -.0043
10001 13937 "ENERGY WEST INC" 7953 2395 8.9375 "012994" "367204104" .0704 .0039000000000000003 -.010844445
10001 13969 "ENERGY WEST INC" 7953 2401 8.75 "012994" "367204104" .047599999999999996 .0039000000000000003 -.02487902
10001 13999 "ENERGY WEST INC" 7953 2401 8.8125 "012994" "367204104" .0073 .0043 .002842857
10001 14028 "ENERGY WEST INC" 7953 2401 8.6875 "012994" "367204104" -.030699999999999998 .004 -.018184397
10001 14060 "ENERGY WEST INC" 7953 2403 8.625 "012994" "367204104" .0318 .0040999999999999995 -.011294245
10001 14091 "ENERGY WEST INC" 7953 2403 8.75 "012994" "367204104" -.0246 .004 .010492754
10001 14122 "ENERGY WEST INC" 7953 2403 8.75 "012994" "367204104" -.16079999999999997 .0043 -.0043
10001 14152 "ENERGY WEST INC" 7953 2407 9.25 "012994" "367204104" .061500000000000006 .0046 .05254286
10001 14182 "ENERGY WEST INC" 7953 2407 9.25 "012994" "367204104" .0713 .0032 -.0032
10001 14213 "ENERGY WEST INC" 7953 2407 9.4375 "012994" "367204104" .061 .0031 .017170269
10001 14244 "ENERGY WEST INC" 7953 2421 9.625 "012994" "367204104" .0616 .0038 .016067551
10001 14273 "ENERGY WEST INC" 7953 2421 9.625 "012994" "367204104" .035 .0034999999999999996 -.0035
10001 14301 "ENERGY WEST INC" 7953 2421 8.75 "012994" "367204104" -.0408 .0034999999999999996 -.0944091
10001 14334 "ENERGY WEST INC" 7953 2421 8.75 "012994" "367204104" .0345 .0043 -.0043
10001 14364 "ENERGY WEST INC" 7953 2421 8.875 "012994" "367204104" .0433 .0037 .010585714
10001 14392 "ENERGY WEST INC" 7953 2421 8.6875 "012994" "367204104" -.0246 .0034000000000000002 -.02452676
10001 14425 "ENERGY WEST INC" 7953 2450 8.625 "012994" "367204104" .04769999999999999 .004 -.011194245
10001 14455 "ENERGY WEST INC" 7953 2450 8.8125 "012994" "367204104" -.0349 .0038 .017939132
10001 14487 "ENERGY WEST INC" 7953 2450 8.75 "012994" "367204104" -.0138 .0039000000000000003 -.010992198
10001 14517 "ENERGY WEST INC" 7953 2450 8 "012994" "367204104" -.0279 .0039000000000000003 -.08961429
10001 14546 "ENERGY WEST INC" 7953 2450 8.6875 "012994" "367204104" .061200000000000004 .0039000000000000003 .0820375
10001 14578 "ENERGY WEST INC" 7953 2450 8.65625 "012994" "367204104" .0337 .0036 -.007197123
10001 14609 "ENERGY WEST INC" 7953 2450 8.5 "012994" "367204104" .07719999999999999 .0044 -.02245054
10001 14640 "ENERGY WEST INC" 7953 2450 8.125 "012994" "367204104" -.047400000000000005 .0040999999999999995 -.04821765
10001 14669 "ENERGY WEST INC" 7953 2450 8.25 "012994" "367204104" .0245 .0043 .011084615
10001 14700 "ENERGY WEST INC" 7953 2464 8 "012994" "367204104" .052000000000000005 .004699999999999999 -.035003033
10001 14728 "ENERGY WEST INC" 7953 2464 8.09375 "012994" "367204104" -.064 .0046 .00711875
10001 14761 "ENERGY WEST INC" 7953 2464 7.90625 "012994" "367204104" -.044199999999999996 .005 -.028166024
10001 14791 "ENERGY WEST INC" 7953 2475 8 "012994" "367204104" .0464 .004 .007857707
10001 14822 "ENERGY WEST INC" 7953 2475 7.875 "012994" "367204104" -.025099999999999997 .0048 -.020425
10001 14853 "ENERGY WEST INC" 7953 2475 8.25 "012994" "367204104" .0703 .005 .04261905
10001 14882 "ENERGY WEST INC" 7953 2496 8.75 "012994" "367204104" -.0545 .0051 .05550606
10001 14914 "ENERGY WEST INC" 7953 2496 9 "012994" "367204104" -.0276 .005600000000000001 .02297143
10001 14944 "ENERGY WEST INC" 7953 2496 9.5625 "012994" "367204104" -.1072 .0051 .0574
10001 14973 "ENERGY WEST INC" 7953 2498 9.75 "012994" "367204104" .011899999999999999 .005 .014607844
10001 15006 "ENERGY WEST INC" 7953 2498 9.875 "012994" "367204104" .0313 .0054 .007420513
10001 15034 "ENERGY WEST INC" 7953 2504 9.75 "012994" "367204104" -.1005 .0038 -.016458228
10001 15064 "ENERGY WEST INC" 7953 2509 10 "012994" "367204104" -.0726 .0042 .021441026
10001 15095 "ENERGY WEST INC" 7953 2509 9.75 "012994" "367204104" .0794 .0039000000000000003 -.0289
10001 15126 "ENERGY WEST INC" 7953 2509 10.7 "012994" "367204104" .0072 .0032 .09423587
10001 15155 "ENERGY WEST INC" 7953 2513 11.8 "012994" "367204104" -.0194 .0028000000000000004 .10000377
10001 15187 "ENERGY WEST INC" 7953 2513 12.1 "012994" "367204104" -.0213 .003 .022423744
10001 15218 "ENERGY WEST INC" 7953 2513 12.35 "012994" "367204104" -.0646 .0031 .017561156
10001 15246 "ENERGY WEST INC" 7953 2518 11.95 "012994" "367204104" -.0925 .0028000000000000004 -.03518871
10001 15279 "ENERGY WEST INC" 7953 2518 11.55 "012994" "367204104" .0246 .0022 -.035672773
10001 15309 "ENERGY WEST INC" 7953 2544 11.5 "012994" "367204104" .0754 .0017000000000000001 -.00602902
10001 15340 "ENERGY WEST INC" 7953 2566 11.45 "012994" "367204104" .0161 .0015 -.005847842
10001 15371 "ENERGY WEST INC" 7953 2566 11.3 "012994" "367204104" -.0144 .0014000000000000002 -.014500404
10001 15399 "ENERGY WEST INC" 7953 2569 10.7 "012994" "367204104" -.0229 .0013 -.05439738
10001 15427 "ENERGY WEST INC" 7953 2571 10.4 "012994" "367204104" .0424 .0013 -.0293374
10001 15460 "ENERGY WEST INC" 7953 2571 9.95 "012994" "367204104" -.052000000000000005 .0015 -.04476921
10001 15491 "ENERGY WEST INC" 7953 2571 10.0975 "012994" "367204104" -.0138 .0014000000000000002 .013424125
10001 15519 "ENERGY WEST INC" 7953 2574 9.72 "012994" "367204104" -.0721 .0013 -.03868545
10001 15552 "ENERGY WEST INC" 7953 2574 8.75 "012994" "367204104" -.0818 .0015 -.10129426
10001 15582 "ENERGY WEST INC" 7953 2574 8.9 "012994" "367204104" .005 .0014000000000000002 .015742814
10001 15613 "ENERGY WEST INC" 7953 2576 8.75 "012994" "367204104" -.1035 .0014000000000000002 -.01825389
end
format %td date

I would really appreciate any possible help on this!

[Clyde Schechter]

Well, there are few things less helpful than saying only that something isn't working. There are so many ways things can fail--and so few ways we can guess which one happened. In future posts, show the results you got from Stata, and, unless it is blatantly obvious, explain how they differ from what you were hoping for.

In this case, there is one obvious problem with your code: the command -gen Beta = .- should precede your loop, not fall inside it. With it inside the loop, when you get to the second PERMNO the code will break because you are asking Stata to generate a variable that already exists.

Whether the code will work as expected after you fix that is hard to say. One thing I notice is that the -if- condition in the -reg- command makes no mention of PERMNO, so it looks to me like this loop will just repeat the same regression of all PERMNOs over and over again. I imagine what you want will require adding -& PERMNO == `s'- to that command.

Your example data also contains only a single PERMNO, so it isn't possible to debug this code any farther, though I suspect with those changes it will give you what you want.

But there is a cleaner way to do this, anyhow:

gen earliest = td(29jul1994)
gen latest = td(30jun1999)
rangestat (reg) ExReturn mktrf, by(PERMNO) interval(date earliest latest)

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer. It is available from SSC. Once you have learned it, you will find it to be one of the most useful commands in your toolbox.

Note the use of td(29jul1994) and td(30jun1999) in the code. Using "magic numbers" like 12628 and 14425 is legal and produces correct results. But if you have to come back to this code some time in the future, you will look at those numbers and strain to remember what dates they actually represent. It is better to make your code as self-explanatory as possible, and the -td()- function is there for precisely that purpose.

Thank you for using -dataex- on your very first post.

[Attaullah Shah]

You can also use asreg, that is way faster than Stata loops. asreg is available on the SSC. Here is an example using asreg

ssc install asreg
bys PERMNO: asreg ExReturn mktrf if inrange(date,12628,14425)

asreg is a powerful package for rolling-window and by-groups regression. It can be used for Fama-Macbeth's (1973) regression as well. You can read more about asreg here https://fintechprofessor.com/2017/12...ions-in-stata/

[Gabija Tinginyter]

Clyde Schechter Thank you so much for your help! The code you provided worked perfectly and saved me a lot of effort and time! I will make sure to better explain what exactly is not working with the code (or the output Stata generates) if I ever post again!

Attaullah Shah Thank you for the insights as well, I will check out the asreg package as I am indeed using Fama-MacBeth's (1973) methodology.