Calculate market betas for firm stocks.

[Shaquille Wijngaarde]

Hi Statalisters,

I could use your help with the following:

I have a panel of daily return data for all CRSP firm from 2002 till 2017 and I have to calculate an individual beta for each stock on a rolling basis.
That is, for a particular firm-year, I want to regress the firm's daily stock returns from the last 3 years on the daily market returns from the last 3 years.

For example, the beta of Apple's stock beta in 2006 would be a regression of Apple's returns from 01/01/2004 to 12/31/2006 on the market returns during this period.
The beta for 2007 would then include the returns from 01/01/2005 till 12/31/2007.

(perhaps it is relevant to know that my dataset period of interest is 2006 till 2017, I added back till 2002 because this variable and a few others I have to generate require more historical data.)

I tried replicating the -rangestat- code from:
https://www.statalist.org/forums/for...tas-for-stocks
https://www.statalist.org/forums/for...ing-stock-beta

It has been calculating for a while and hasn't finished. I can imagine that it would take a while, considering I have around 8.2 million observation and 3800 unique firms (permco).
However, it'd like to know for sure that the code works before I let it spend such a long time calculating.

Current code:

rangestat (reg) exret mktrf, by(permco) interval(bdate -755 0)

One problem I think I already have with this code is the fact that I have a count of the amount of trading days for each year (trading_days) which varies between 250 and 252.
So if I am using different, rolling periods of 3 years of daily data the number in the interval also changes continuously, right?

Secondly, I read in the help -rangestat- the following: ' Since this is panel data, we use the by(company) option to restrict calculations to observations within the same company group.'
Should I also be doing this since I have panel data?

I also want to reject the results for observations that did not have 3 years of past daily data available for the beta.

I know this is a lot, but I hope I have been clear in what I am looking for and can get some much needed help. My -dataex- is below

* Example generated by -dataex-. To install: ssc install dataex
clear
input long permco str32 comnam float(year bdate mktrf exret smb hml rf trading_days)
7 "APPLE COMPUTER INC" 2002  0   .31          .  -.79 -.04 .01 252
7 "APPLE COMPUTER INC" 2002  1   .98  1.1845564   .56 -.34 .01 252
7 "APPLE COMPUTER INC" 2002  2   .71   .4554149   .22  .52 .01 252
7 "APPLE COMPUTER INC" 2002  3  -.63  -3.401615  -.49  .98 .01 252
7 "APPLE COMPUTER INC" 2002  4  -.25  -1.284458   1.2 -.41 .01 252
7 "APPLE COMPUTER INC" 2002  5  -.41 -4.3486876  -.04  .26 .01 252
7 "APPLE COMPUTER INC" 2002  6   .03 -1.9690182   .18 -.39 .01 252
7 "APPLE COMPUTER INC" 2002  7  -.86   -.861473   -.1  .06 .01 252
7 "APPLE COMPUTER INC" 2002  8  -.81   .4639364   -.8  .25 .01 252
7 "APPLE COMPUTER INC" 2002  9   .59   2.557241  -.43  .26 .01 252
7 "APPLE COMPUTER INC" 2002 10 -1.58  -4.342128  -.26  .66 .01 252
7 "APPLE COMPUTER INC" 2002 11   .93   7.853498    .2 -.65 .01 252
7 "APPLE COMPUTER INC" 2002 12  -.99 -1.3985977  -.48  .72 .01 252
7 "APPLE COMPUTER INC" 2002 13  -.75  -1.601306  -.28  .24 .01 252
7 "APPLE COMPUTER INC" 2002 14   .87   5.343646   .62  .02 .01 252
7 "APPLE COMPUTER INC" 2002 15    .4   .8119758   .12  .14 .01 252
7 "APPLE COMPUTER INC" 2002 16   .09   .1621951   -.3  .34 .01 252
7 "APPLE COMPUTER INC" 2002 17   .02   .0759865   .06  .74 .01 252
7 "APPLE COMPUTER INC" 2002 18 -2.51  -.8731938  1.32   .1 .01 252
7 "APPLE COMPUTER INC" 2002 19     1   4.316376  -.11 -.36 .01 252
7 "APPLE COMPUTER INC" 2002 20  1.34   2.571578  -.55 -.02 .01 252
7 "APPLE COMPUTER INC" 2002 21  -.64 -1.2719727   .46 -.36 .01 252
7 "APPLE COMPUTER INC" 2002 22  -2.4  3.7685866   .22  .78 .01 252
7 "APPLE COMPUTER INC" 2002 23  -.46   .3837028   .14 -.61 .01 252
7 "APPLE COMPUTER INC" 2002 24  -.74  -3.122784  -.84   .5 .01 252
7 "APPLE COMPUTER INC" 2002 25  -.35 -1.5211614  -.81 1.02 .01 252
7 "APPLE COMPUTER INC" 2002 26  1.53 -1.1273241  -.05  .37 .01 252
7 "APPLE COMPUTER INC" 2002 27  1.38   3.867241  -.35 -.17 .01 252
7 "APPLE COMPUTER INC" 2002 28  -.31 -1.0967504   .68 -.49 .01 252
7 "APPLE COMPUTER INC" 2002 29   .95   1.196777  -.12  .01 .01 252
7 "APPLE COMPUTER INC" 2002 30  -.26 -1.6629295  -.76  .38 .01 252
7 "APPLE COMPUTER INC" 2002 31 -1.06 -2.8968015   .61  .32 .01 252
7 "APPLE COMPUTER INC" 2002 32 -1.86  -5.514393  -.12  .83 .01 252
7 "APPLE COMPUTER INC" 2002 33  1.24  2.2195933  -.04 -.47 .01 252
7 "APPLE COMPUTER INC" 2002 34 -1.43   -7.31775  -.24  .88 .01 252
7 "APPLE COMPUTER INC" 2002 35   .67   5.597254   .16   .1 .01 252
7 "APPLE COMPUTER INC" 2002 36  1.65   4.588016 -1.28  .29 .01 252
7 "APPLE COMPUTER INC" 2002 37   .12  -.5997211    .6  .06 .01 252
7 "APPLE COMPUTER INC" 2002 38   .15  -7.508585   .03  .74 .01 252
7 "APPLE COMPUTER INC" 2002 39  -.29  -1.201028  -.43  .87 .01 252
7 "APPLE COMPUTER INC" 2002 40  2.21   7.745823  -.51 -.46 .01 252
7 "APPLE COMPUTER INC" 2002 41  2.02  3.5094254  -.01 -.26 .01 252
7 "APPLE COMPUTER INC" 2002 42  -.51  -3.188855   .55  .02 .01 252
7 "APPLE COMPUTER INC" 2002 43  1.38  2.2590008  -.09  .24 .01 252
7 "APPLE COMPUTER INC" 2002 44   -.4   1.269685    .6 -.01 .01 252
7 "APPLE COMPUTER INC" 2002 45   .66  1.1319402   .47 -.53 .01 252
7 "APPLE COMPUTER INC" 2002 46   .29  1.5990436   .08    0 .01 252
7 "APPLE COMPUTER INC" 2002 47  -.29 -1.3760324  -.15  .35 .01 252
7 "APPLE COMPUTER INC" 2002 48  -.89  -.9447743   .23  .23 .01 252
7 "APPLE COMPUTER INC" 2002 49  -.06 -.25529638   .36  .04 .01 252
end
format %tbtrade_day1 bdate

[Clyde Schechter]

Well, your description of your problem is unclear. On the one hand, you state that you want a "rolling" regression, but then you state that you want it by year with a three year window. Which is it? They are very different. In a "rolling" regression, you have to do a new regression for every single day, extending back 3 years from that date. But if you want a three year window, you only need to do it once for each firm and year, spreading the observations to all other observations for the firm and year--which is much, much faster.

The -rangestat- command you show will give a rolling regression with a lookback window of 755 business days (which, I guess, is more or less 3 years).

But if you want a firm-year regression with a 3-year look back, then you would do:

rangestat (reg) exret mktrf, by(permco) interval(year -2 0)

[Clyde Schechter]

Actually, if you want a firm-year regression with a 3-year look back, then the following code will also do it, but much, much faster:

gen ll = year - 2
gen ul = year
by permco year (bdate), sort: replace ll = year+1 if _n > 1

rangestat (reg) exret mktrf, by(permco) interval(year ll ul)

foreach v of varlist reg_* b_* se_* {
    by permco year (bdate), sort: replace `v' = `v'[1]
}

This code will only do the regression once for each firm-year combination, and then just copy the results to the rest of the observations for that firm-year. There is no comparable speedup available for the rolling regression, because there every observation requires a separate regression.

[Shaquille Wijngaarde]

Well, your description of your problem is unclear. On the one hand, you state that you want a "rolling" regression, but then you state that you want it by year with a three year window. Which is it? They are very different. In a "rolling" regression, you have to do a new regression for every single day, extending back 3 years from that date. But if you want a three year window, you only need to do it once for each firm and year, spreading the observations to all other observations for the firm and year--which is much, much faster.

The -rangestat- command you show will give a rolling regression with a lookback window of 755 business days (which, I guess, is more or less 3 years).

But if you want a firm-year regression with a 3-year look back, then you would do:

rangestat (reg) exret mktrf, by(permco) interval(year -2 0)

Hi Clyde,

Thank you for your reply! So this codes still takes into take I have daily stock return and market return data over those 3 years windows?

My apologies for the confusing explanation. I am indeed wondering about how to calculate both versions.
I have an example that ignores the rolling properties of this calculation and just uses the same beta calculated over the window of the three previous years for all the daily observations in a specific year.

Officially, since I am going to use beta to calculate expected returns on daily basis, it would be more accurate to have an actual rolling regression.
What would the code look like then, if I have to deal with changing number of trading days over the three years?

Also is there anyway to generate a sample of the regressions outputted by rangestat in a more conventional form, for example how it looks like when you run a single regression?

[Clyde Schechter]

What would the code look like then, if I have to deal with changing number of trading days over the three years?

I'm not really sure. I don't deal with this kind of problem in my line of work. In terms of a general direction, instead of using -interval(bdate, -755 0), I would calculate, based on the number of trading days in each year (which, I presume, you know, and I don't) a new variable, call it back3yr, that is, in each observation, the business date corresponding to three years prior. Then I would use -interval(bdate, back3yr, 0)-.

Also is there anyway to generate a sample of the regressions outputted by rangestat in a more conventional form, for example how it looks like when you run a single regression?

No, -rangestat- doesn't do that. Nor would it really make sense to try. You have millions of regressions. Putting the output in the form you are accustomed to seeing would generate hundreds of thousands of pages of output. How could you possibly make any use of that? When you are mass-producing regression results, the only sensible way to present the output is as new variables in a data set.

[Shaquille Wijngaarde]

But if you want a three year window, you only need to do it once for each firm and year, spreading the observations to all other observations for the firm and year--which is much, much faster.

The -rangestat- command you show will give a rolling regression with a lookback window of 755 business days (which, I guess, is more or less 3 years).

But if you want a firm-year regression with a 3-year look back, then you would do:

rangestat (reg) exret mktrf, by(permco) interval(year -2 0)

Thank you Clyde! I was able to generate back3yr and try the actually rolling regression, it took about an hour to run :s.

One question to make sure I understand your quoted writing above correctly. The three year window per firm year should suffice for my purpose.
However, I would change the interval to -3 -1 because I do not actually need to include year 0 because I want calculate beta based on the 3 years before.
For example, I want to calculate beta for 2006, by regressing the daily returns of the stock (exret) on the market returns (mktrf) for the 3 year period from 2005, 2004, 2003.

There is one thing I want to confirm with you to make sure I understand how the code works, so by running the code below or the foreach loop version:

rangestat (reg) exret mktrf, by(permco) interval(year -3 -1)

For each firm year, Stata regresses the daily stock returns of the past three years (exret) on the daily market returns of the past three year (mktrf), delivering the outcome once and replicating it for all observations in year zero.

So for 2006, it is does exret (2003-01-01 till 2005-12-31) on mktrf (2003-01-01 till 2005-12-31)?

[Clyde Schechter]

However, I would change the interval to -3 -1 because I do not actually need to include year 0 because I want calculate beta based on the 3 years before.
For example, I want to calculate beta for 2006, by regressing the daily returns of the stock (exret) on the market returns (mktrf) for the 3 year period from 2005, 2004, 2003.

That's fine. I had used the interval -2 to 0 because in #1 you said that for year 2006 you wanted to include observations from 2004, 2005, and 2006.

There is one thing I want to confirm with you to make sure I understand how the code works, so by running the code below or the foreach loop version:

Code:
rangestat (reg) exret mktrf, by(permco) interval(year -3 -1)

For each firm year, Stata regresses the daily stock returns of the past three years (exret) on the daily market returns of the past three year (mktrf), delivering the outcome once and replicating it for all observations in year zero.

So for 2006, it is does exret (2003-01-01 till 2005-12-31) on mktrf (2003-01-01 till 2005-12-31)?

Correct.

You can verify it yourself. Just do a regression of exret on mktrf using the 2003 through 2005 observations and see that the results you get match what the code gives you.

[Nils Fabricius]

Hello,

Like Shaquille I am also trying to calculate betas for firm stocks, I am dealing with a panel data set and I am trying to calculate monthly firm betas based on a (prior) 60 months window.
I have run the following code:

rangestat (reg) RET sprtrn, by (cusip) interval(date -60 0)

Where 'RET' is the monthly return of the individual stock and 'sprtrn' is the return of the market index.
My timeframe of interest is january 1999 until december 2019, in order to be able to calculate the beta I have downloaded the monthly return data from january 1994 onwards. After running the code above I have a question since I received a different output than I expected and I do not know where I made a mistake. First of all, the function already returns output even though there has not been a 60 month window beforehand, e.g. I have a model output for march 1994. Additionally, the output is missing even for stocks for which I have the data for the full timeframe without any missing input values. My reg_nobs variable (number of obs) ranges between 1 and 3 which does not make sense to me, might the mistake be related to that?

Hopefully my question and description are clear, I did not choose to upload (part of) data since a firm for which I have all the necessary data contains 300 observations which may be too large? However if its necessary I can still upload (part of) data.


Thanks in advance!

Nils