Calculating Stock's Beta with rollreg

[Maximilian Pabst]

Dear all,

I'm trying to calculate a Stock's Beta with a rolling regression in Stata.(http://www.investopedia.com/articles...ating-beta.asp)
I have the variables "permno" which identifies a stock (Company), "date" (a specific day of each month for the stock price), "retx" (the return of the stock on this day compared to previous month, and sprtrn (the return of the S&P500 in the same period of time)

For calculating Beta I want to regress retx on sprtrn historically for 60 months using rollreg (that's what my master thesis supervisor demands ;-) )

This is what I did so far:

tsset permno date
       panel variable:  permno (unbalanced)
        time variable:  date, 30jan1987 to 31dec2015, but with gaps
                delta:  1 day

. rollreg retx sprtrn, move(60) stub(Beta)

Number of gaps in sample:  2496327

Observations with preceding time gapstoo many values
r(134);

Unfortunately it says too many values, and too many gaps?

The data ranges from 1987 to 2015, with monthly datapoint for thousand of companies, so it is a big sample...

Can anyone help me with this issue? And is the command correct anyways?

I would really appreciate your help!

Thank you in advance

[Maximilian Pabst]

Meanwhile I found a working solution if anyone faces a similar issue:

tsset permno date

gen excess_ret = retx - rf
gen excess_mkt = sprtrn - rf

fastrollreg excess_ret excess_mkt, window(60) panelvar(permno) coeff(auto)

[Simon Vasquez]

Dear Maximilian,

your code looks very compelling compared to other attempts to calculate beta.

However, Stata (version 14) does not recognize the command - fastrollreg - and I was not able to find it via - ssc install - ?



I have another rather „conceptual“ question:

1. What stands „rf“ for?
2. Why do you subtract it? I thought that the stock beta is derived simply by regressing the stock return on the benchmark-return?


Thank you!

Simon

[Robert Picard]

search fastrollreg yields no results so I don't know what Maximilian was referring to.

You can however use rangestat (from SSC) to perform an ordinary least squares regression over a rolling window of time.

[Robson Glasscock]

The gaps are caused because the format of the date you pulled directly from CRSP is not automatically marked or recognized by Stata as monthly data. Stata is telling you it is treating the data as daily in nature via the "delta: one day."

format date %tm
tsset permno date

will give you a delta of one month rather than one day, and this should take care of the gaps.

Simon: rf is likely the risk-free return, and when CAPM is estimated it is not uncommon for the risk-free rate to be subtracted.

[Simon Vasquez]

Thank you for the replies.

@Robson

I tried your suggestion:

- format date %tm -

Indeed, when I use

- xtset -

Stata recognizes that delta is 1 month instead of 1 day.


However, the -rollreg- command still does not work, the error message is the same as in Maximilian´s first post.


Surprisingly, the „number of gaps in sample“ (part of the error message as in the first post) is exactly the same whether I apply

- format date %tm -

or not!?

Any clue how this could be explained?

[Nick Cox]

Changing the format alone is probably not enough.

You haven't shown us your data: do please back up and read the FAQ Advice, as you are prompted to do every time you post. Data examples with dataex (SSC) save your time and ours.

But I guess that you have dates like this: They're daily dates for the ends of months.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float date
20850
20878
end
format %td date

If you format those gappy dates as months, the numeric values remain the same. You've just made the data crazier. Your new dates are say 20000 months from the beginning of 1960. You said through the format statement, "Oh, these dates are really monthly dates".

. format date %tm

. l

     +---------+
     |    date |
     |---------|
  1. |  3697m7 |
  2. | 3699m11 |
     +---------+

What I guess you need is to convert the daily dates to monthly dates with a function made for that purpose.

. gen mdate = mofd(date)

. format mdate %tm

. l

     +------------------+
     |    date    mdate |
     |------------------|
  1. |  3697m7   2017m1 |
  2. | 3699m11   2017m2 |
     +------------------+

In general, you can't hope to deal with dates in Stata unless you study the documentation for how Stata deals with dates.

help datetime

.
All guesswork, except that I am certain that changing a display format won't map gappy dates to ungappy dates, as the underlying numeric values remain the same.

[Robson Glasscock]

No, I'm not sure why. I have used -rolling- to estimate beta but never -rollreg- (available from the SSC). When I saw that monthly data was set up as daily I thought the error was driven by this mistake.

[Robson Glasscock]

As Nick suggests, this is the code I used to estimate beta with -rolling-.

gen monthyear= mofd(date)
xtset permno monthyear

rolling _b _se, window(60) saving(c:\whatever, replace): reg ret vwretd

I was trying to edit my prior response to fit this in, but ended up not being able to get the code delimiters into the edit. Apologies for the double replies.

[Robert Picard]

And let me repeat my recommendation to use rangestat (from SSC) to do an OLS over a rolling window of time. The help file has a fully worked out example with examples that you can run from the help file. The help file also explains that you can calculate the results for any individual observation using a standard Stata regress call. Here's the first part of the example that demonstrates the technique:

webuse grunfeld, clear

* calculate the expected results for observation 15
regress invest mvalue kstock if inrange(year, year[15]-6, year[15]) ///
    & company == company[15]

* calculate the expected results for observation 40
regress invest mvalue kstock if inrange(year, year[40]-6, year[40]) ///
    & company == company[40]

The above example if for a 7 year window that includes the current observation. I'm pretty sure that a gap problem in the date variable would show up if Simon went through a similar exercise.

[Robson Glasscock]

But I guess that you have dates like this: They're daily dates for the ends of months.

Nick's guesswork about the dates is right. This is data downloaded directly from CRSP in .dta format. The monthly CRSP data contains end-of-month, daily dates.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double permno long date str8 ticker double(ret retx vwretd vwretx)
49154 10255 "DH"     .2624434530735016    .2624434530735016  .04488041   .04304757
64282 10255 "LTD"  .050724636763334274  .050724636763334274  .04488041   .04304757
62770 10255 "ASO"    .1135135143995285    .1135135143995285  .04488041   .04304757
49154 10286 "DH"    .11842294037342072    .1111111119389534  .05169475    .0459987
64282 10286 "LTD"   .24137930572032928   .24137930572032928  .05169475    .0459987
62770 10286 "ASO"  .019417475908994675  .019417475908994675  .05169475    .0459987
62770 10317 "ASO"    -.040571428835392  -.05238095298409462 -.01659759  -.01927279
64282 10317 "LTD"  -.13066667318344116  -.13333334028720856 -.01659759  -.01927279
49154 10317 "DH"  -.022580645978450775 -.022580645978450775 -.01659759  -.01927279
64282 10346 "LTD" -.025641025975346565 -.025641025975346565  .01099083  .009188475
62770 10346 "ASO"                    0                    0  .01099083  .009188475
49154 10346 "DH"  -.059405941516160965 -.059405941516160965  .01099083  .009188475
49154 10378 "DH"  -.020912280306220055 -.028070176020264626 .000447785 -.003858654
62770 10378 "ASO"  -.08542713522911072  -.08542713522911072 .000447785 -.003858654
end
format %d date

Robert: I'll be sure to check out your recommendation for the future.

Nick: Thanks for the correction and explanation about the dates. I used the same function when I actually estimated beta, but that was a while ago and when I posted I thought I remembered handling this with formatting. I tried to halfway check my advice before posting by changing the format before the -xtset-. Once I saw the delta was changed to one month, I thought I was good to go giving the advice and also thought my memory was right about how I solved the problem. What I should have done was go back to the actual .do files I used for that project to see what I actually did rather than relying on memory. Or perhaps re-read Stata's documentation, the FAQ, Stata Journal articles, etc. about dates.

Maximilian, Simon, and everyone else: I'm very sorry for the wrong suggestion on how to handle the problem.

[Nick Cox]

Robson: I've posted a lot of mistaken posts here!

It's all too easy to think that changing the display format does more than it does.

A top tip here, and elsewhere, with dates is to check out with display and little examples where you can think it through that what you see really is what you want.

. local today = mdy(5, 18, 2017)

. di `today'
20957

. di %td  `today'
18may2017

* that is right!

. di %tm  `today'
 3706m6

* that is quite wrong!!!

[Simon Vasquez]

Nick, Robert, Robson, thank you very much for your replies. I willl try your suggestions

[Simon Vasquez]

Robert, your suggestion on - rangestat - worked out perfectly! Thank you!
Nick, the adjustment of the time variable worked. After the change - rollreg - could be used. However, - rangestat - is by far more efficient

Thank you all for your help!!