Market Beta using rangestat - rolling regression problem

[Nikos Georgiadis]

Hello!!

I have a panel dataset which consists of the following variables:
ddate=daily date, mdate=monthly date, stockName= stock Id, dExReturn= each stock's daily excess return and mktexcess= market's portfolio excess return.

I would like to calculate the the monthly market beta for each stock, using daily data in a rolling window of 12 months for each stock(months t-11 through t, inclusive) with a minimum requirement of 200 valid daily return observations during the calculation period.
I use the following command :

xtset stockName ddate
rangestat (reg) dExReturn mktexcess if [_n]>=200 , interval(mdate -11 0) by(stockName ddate)
but i get the following error: no result for all obs: reg dExReturn mktexcess
If i use rangestat (reg) dExReturn mktexcess if [_n]>=200 , interval(mdate -11 0) by(stockName mdate), I get very few Nobs for my regression (22-25 in each case). Actually the program seems like estimating betas in a 12 month period using a rolling window of 1 month instead of a 12month one. Am I doing something wrong? if not, does anyone know how can I overcome this problem in the first regression?
I read somewhere that _n maybe is not appropriate for counting how many observations are being used. If this is true, what can i do in order to specify the number of observations used in a regression or in any stata operation/calculation in general?

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(ddate mdate) int stockName float(mktexcess dExReturn)
13030 428 1 -1.29          .
13081 429 1   .52          .
13065 429 1  -.99          .
13068 429 1   .94          .
13060 429 1   .32          .
13083 429 1   .15          .
13067 429 1   .42          .
13079 429 1 -2.66          .
13069 429 1  -.59          .
13075 429 1  -.56          .
13062 429 1  -.47          .
13061 429 1   -.3          .
13073 429 1  -.89          .
13066 429 1  -1.1          .
13058 429 1  -.76          .
13059 429 1   -.7          .
13086 429 1  -.04          .
13082 429 1  2.31          .
13076 429 1 -2.79          .
13072 429 1    -2          .
13074 429 1  1.56          .
13087 429 1  1.66          .
13080 429 1  1.45          .
13116 430 1 -1.31 -.17647076
13117 430 1  -.02  -.1684661
13090 430 1  -.78          .
13104 430 1   .16          .
13111 430 1  -.74          .
13093 430 1  -.94          .
13110 430 1  -.68          .
13088 430 1  -.02          .
13114 430 1   .49          .
13107 430 1   -.4          .
13100 430 1  -.36          .
13108 430 1   .03          .
13103 430 1  1.05          .
13094 430 1   .22          .
13101 430 1  -.52          .
13097 430 1  -.82          .
13115 430 1 -1.23          .
13102 430 1   .74          .
13095 430 1  1.08          .
13096 430 1 -2.14          .
13109 430 1   .03          .
13089 430 1   .73          .
13118 431 1  -.23 -4.1254034
13121 431 1  -.79 -2.2510245
13138 431 1  1.28  -1.452436
13145 431 1    .1 -.40043905
13144 431 1  2.05 -.26010343
13124 431 1  1.11 -.25598717
13130 431 1  -.58  -.1774432
13142 431 1   .04       -.02
13135 431 1  -.93  .06097699
13125 431 1  -.01  .08137292
13143 431 1   .09   .2440338
13128 431 1   .51    .461053
13146 431 1   .19   .5333526
13132 431 1  2.27  1.1266963
13139 431 1   .46  1.1347535
13131 431 1    .4    1.30791
13129 431 1   .88  1.3406608
13122 431 1   -.2  1.8126007
13136 431 1   .07  2.0351832
13137 431 1 -1.33  2.3743525
13123 431 1  1.69   2.636169
13174 432 1 -1.37  -2.591869
13163 432 1 -1.76 -2.5359194
13170 432 1  -1.5 -1.9956445
13152 432 1 -1.41 -1.0611887
13158 432 1  -.79  -.8844621
13153 432 1  -.94  -.6853262
13173 432 1  -.84  -.4321569
13165 432 1  -.24  -.3024822
13171 432 1   .17  -.1687522
13159 432 1  -.23 -.14132519
13149 432 1  -.02       -.02
13160 432 1  -.09  .16220886
13151 432 1  -.11   .1717619
13179 432 1  1.11  .18535297
13150 432 1  1.84  1.0263423
13172 432 1   .35  1.1791663
13177 432 1   .62  1.6715692
13178 432 1  1.49  1.6881968
13157 432 1   .25  1.7160083
13164 432 1  1.36   1.799027
13156 432 1   .87  1.8180068
13166 432 1  2.15  2.1544096
13167 432 1  2.55   2.767562
13193 433 1 -4.33  -5.159505
13191 433 1 -2.61  -2.631725
13194 433 1  -.93 -1.8937757
13198 433 1 -1.58   -1.64815
13195 433 1  -1.5 -1.3744533
13208 433 1  -.56 -1.3032315
13205 433 1 -1.03 -1.0833682
13184 433 1  -.52  -.9121054
13199 433 1  -.76  -.7140765
13186 433 1  -.06  -.1472033
13188 433 1    .3   .1969484
end
format %td ddate
format %tm mdate

[Nick Cox]

Asked and answered on Reddit. https://www.reddit.com/r/stata/comme...ssion_problem/

Please note our policy on cross-posting, which is that you are asked to tell us about it. https://www.statalist.org/forums/help#crossposting

[Nikos Georgiadis]

Excuse me for that Mr.Cox. I just wanted to ensure that the answer i got was right from a more official source. Thank you very much!!

[Ammar Azhari Ahmed]

Nick Cox Hi Nick, as a follow up to this thread, I have used the following:

rangestat (reg) wkreturn SPRETURN, interval(date -260 0) by(CompDSCode)

wkreturn is the weekly returns for 500 companies all in the same variable each with its own unique identifier (CompDSCode). date is my date variable of weeks over 35 years.

If it makes a difference, my date variable goes from year 1 to year 35 then repeats for the next company in the list of (CompDSCode) and so on for all 500 companies. The same is true for SPRETURN which is the market return.

I am trying to generate a rolling beta based on the preceding 260 weekly observations (5 years). However, the regression keeps using 38 observations for each estimate, rather than the specified 260 weeks. Ultimately, I would like to select 1 beta for each company year based on the fiscal year end date, so that I have 30 betas for each company.

Thanks very much in advance for all your kind contributions.

Ammar.

Example generated by -dataex-. For more info, type help dataex
clear
input str6 CompDSCode str5 Code str8 FISYREND float date double(wkreturn SPRETURN reg_nobs reg_r2 reg_adj_r2 b_SPRETURN b_cons se_SPRETURN se_cons)
"130062" "WKRET" "ENDDEC" 9139 . -.019624456133009963 . . . . . . .
"130062" "WKRET" "ENDDEC" 9146 . .04074637729461506 . . . . . . .
"130062" "WKRET" "ENDDEC" 9153 . .026973248648043888 . . . . . . .
"130062" "WKRET" "ENDDEC" 9160 . .020865810871419255 . . . . . . .
"130062" "WKRET" "ENDDEC" 9167 . .00794912326613995 . . . . . . .
"130062" "WKRET" "ENDDEC" 9174 . -.0002768779262919071 . . . . . . .
"130062" "WKRET" "ENDDEC" 9181 . .004255443156654148 . . . . . . .
"130062" "WKRET" "ENDDEC" 9188 . -.0008827586780146953 . . . . . . .
"130062" "WKRET" "ENDDEC" 9195 . .005833808510044228 . . . . . . .
"130062" "WKRET" "ENDDEC" 9202 . -.01420344969690319 . . . . . . .
"130062" "WKRET" "ENDDEC" 9209 . -.0006681514725181849 . . . . . . .
"130062" "WKRET" "ENDDEC" 9216 . -.006201656885204903 . . . . . . .
"130062" "WKRET" "ENDDEC" 9223 . .011700601505229216 . . . . . . .
"130062" "WKRET" "ENDDEC" 9230 . -.012934338763558694 . . . . . . .
"130062" "WKRET" "ENDDEC" 9237 . .016638763423870963 . . . . . . .
"130062" "WKRET" "ENDDEC" 9244 . .0037457353479306314 . . . . . . .
"130062" "WKRET" "ENDDEC" 9251 . -.0113351687797042 . . . . . . .
"130062" "WKRET" "ENDDEC" 9258 . .005158224365973264 . . . . . . .

[Clyde Schechter]

Your date variable is not as advertised. It is not a weekly date variable. If it were, it would refer to dates in years 2135 and beyond.

What you have is a daily date variable that is spaced 7 days apart. Now, the -interval option in -rangestat-, meanwhile, neither knows nor cares whether it is monthly or weekly or daily, or even not a date at all. It only cares about the numerical values. So -interval date -260 0- is understood to be a range of values between the current value of date and that value reduced numerically by 260--in whatever the units of date happen to be. Since the units of date here are days, you are getting 260 days worth of observations included, which is, 37 weeks and 1 day.

So you need to convert your desired 260 weeks into 260*7 = 1820 days in your command to get what you want:

rangestat (reg) wkreturn SPRETURN, interval(date -1820 0) by(CompDSCode)

[Ammar Azhari Ahmed]

Clyde,

Thank you so much for your prompt response. This now works. Although it does use over 260 observations at points (up to 264). While this won't be too much of an issue, I would still be curious to learn why it's doing that and how to get it to exactly 260.

Thanks very much, your help is very kind.

Ammar.

[Clyde Schechter]

Again, you are either misunderstanding your data or misunderstanding how the -interval()- option works, or perhaps both.

The -interval()- option will cause Stata to take into consideration all and only the observations whose date variable takes on a numeric value between the current observation's date value and that value minus 260, inclusive (among those that match it on CompDSCode). That's a total of 261 different dates. It is the dates that determine what is included: it does not count the number of observations that results in. The number of observations that corresponds to depends on how the date variable is distributed in your data. If you had a daily date every day and no duplicates of the date variable, then that would give you exactly 261 observations all the time. (Well, not all the time, because for the earliest 260 dates there wouldn't be enough observations going back in time to fill that up.) The fact that you are getting more than 261 in some instances ("up to 264") tells me that you have some duplicate dates in your data. If you think your data are supposed to be true panel data uniquely identified by CompDSCode and date, then something has gone wrong with your data management. Run -duplicates report CompDSCode date- to get a count on the number of such observations, and

duplicates tag CompDSCode date, gen(flag)
browse if flag

to see the actual observations causing the problem.

Once you find these surplus observations you will have to figure out how to fix that problem. Even if it is just obvious that they are complete duplicates and -duplicates drop- will eliminate them, you should still review the data management that led to the creation of the data set, because the occurrence of purely duplicate observations usually is a sign that errors were made, and if you have stumbled upon one error like this there may well be others lurking in your data as well.

[Ammar Azhari Ahmed]

Clyde,

Thanks very much, thats cleared everything up.

Very grateful for your kind assistance.

Regards,

Ammar.