Estimating beta with rolling standard deviation and rolling correlation

[Sebastian Sigl]

Hello everybody,

for my master thesis I replicate a scientific paper. I got the following task:

We estimate pre-ranking betas from rolling regressions of excess returns onmarket excess returns.Whenever possible, we use daily data,rather than Monthly data,as the accuracy of covariance estimation improves with the sample frequency(Merton,1980).

We estimat volatilities and correlations separately for two reasons.First, we use a one-year rolling Standard Deviation for volatilities and a five-year horizon for the correlation. Second, we use one-daylog returns to estimate volatilities and overlapping three-day log return for correlation to control for non synchronous trading(which affects only correlations).

The calculations are based on daily Returns. My approach looked like this (Jahr=Year):

rangestat (sd) r_l, interval(Jahr -1 0) by(business)

rangestat (sd) r_il_totm, interval(Jahr -1 0) by(business)

rangestat (corr) r_l_sum r_il_totm_sum, interval(Jahr -4 0) by(business) (r_sum equals the overlapping log Returns)
by business: gen b_=corr_x*(sd_/sd_totm)

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id date2 Jahr Monat r_l r_il_totm) double(r_l_sum r_il_totm_sum sd_ sd_totm r_il_totm_sd) float b_
 1 0 1989 12           .            .                    0                    0                   .                   .                   .        .
 2 1 1990  1   .01092663   .016545914  .010926631279289722  .016545914113521576 .012027065923998433 .009804939291130532 .009824321080103091 .9354857
 3 2 1990  1  .010813636 -.0006053161  .021740267053246498  .015940597979351878 .012027065923998433 .009804939291130532 .009824321080103091 .9354857
 4 3 1990  1  .007646538  -.007902635  .029386804904788733  .008037962717935443 .012027065923998433 .009804939291130532 .009824321080103091 .9354857
 5 4 1990  1 -.016907455   -.00959534 .0015527182258665562 -.018103292444720864 .012027065923998433 .009804939291130532 .009824321080103091 .9354857
 6 5 1990  1  .026013814   .003943066   .01675289636477828 -.013554910197854042 .012027065923998433 .009804939291130532 .009824321080103091 .9354857
 7 6 1990  1           0  -.011328274  .009106358513236046 -.016980549320578575 .012027065923998433 .009804939291130532 .009824321080103091 .9354857
 8 7 1990  1 -.006059999  -.007035573  .019953815266489983 -.014420781284570694 .012027065923998433 .009804939291130532 .009824321080103091 .9354857
 9 8 1990  1  .009078786  .0031941284  .003018787130713463 -.015169719001278281 .012027065923998433 .009804939291130532 .009824321080103091 .9354857
10 9 1990  1 -.033692833  -.024339866 -.030674045905470848 -.028181310510262847 .012027065923998433 .009804939291130532 .009824321080103091 .9354857
end
format %tbNYSE date2

The sd,corr and beta for each Business changes every year. For the paper I Need to rank the betas of the stocks every month and put them in a certain portfolio, which makes not much sense if I have a beta which is changing its value just every year. My supervisor told me I have to calculate betas "daily". Do you guys have any idea what I am doing wrong/don't recognize.

Thank you in Advance!

[Sebastian Sigl]

I've tried to calculate it based on my time-identifier date2. To get 5 years I have to take the last 1266 observations. This gives me sd for everyday, nevertheless it seems wrong and i don't understand the exact difference between (Jahr -4 0) and (date2 -1266 0).

rangestat (sd) r_l, interval(date2 -1266 0) by(business)

* Example generated by -dataex-. To install: ssc install dataex
clear
input double r_l_sd
                    .
                    .
.00007989988806452737
 .0018620011694026142
  .013437807831550228
  .015500252959863087
  .014215640365057096
    .0138071647730137
  .012879212338657848
   .01769511884821062
end

[Sebastian Sigl]

*In my second post I've tried to calculate the sd with Five-year horizon and it provides me with a daily changing value.

[Clyde Schechter]

I've tried to calculate it based on my time-identifier date2. To get 5 years I have to take the last 1266 observations. This gives me sd for everyday, nevertheless it seems wrong and i don't understand the exact difference between (Jahr -4 0) and (date2 -1266 0).

Suppose the date for an observation is 30 June 2019. interval(Jahr -4 0) looks only at the year and gives you a calculation based on any observations between 1 Jan 2015 and 31 Dec 2019. In fact, it will give you the exact same result for any observation whose date is anywhere in calendar year 2019.
By contrast, interval(date2 -1266 0) looks at the variable date2, which is a daily date in your business calendar, and it looks back 1266 business days. I'll take your word for it that this corresponds to 4 business years. So that would mean that the calculations for an observation with date2 = 20 Jun 2019 will get calculations based on everything from 20 Jun 2015 through 20 Jun 2019. Consequently, every value of date2 has the potential to get a different result.

In what sense does it seem wrong?

[Sebastian Sigl]

Thank you for your response Mr.Schechter.

Alright, therefore working with the date2 variable which is daily is the right choice for my purposes.
1266 equals (5 years- 1 day). As date2 is in a daily Format, in order to to get a five year horizon, I have to look at how many observations of date2 equal five years and subtract -1 ay bc. of the current one.

I haven't quite figured out before why the two intervals gave me different results and it didn't seemed Logical for me. I understand it now, thank you again.