Rolling Firm Specific Regression Daily

[Vince Williams]

Dear Stata community:
I am interested in obtaining the adjusted R2, beta and the number of observations from a rolling firm specific regression with daily stock returns on daily index for the past 20 days with a rolling window of 5 days. I know how to do this for every month with rangestat:

bysort permno  ym: generate time = _n
egen groupvar = group(id ym)
g ym = ym(year(date),quarter(date))
format ym %tm
rangestat (reg) ret index_ret, interval(groupvar 0 0)

However, I am interested in doing the regression on a rolling daily window. How can I achieve that with a loop? I am including data for one stock

* Example generated by -dataex-. For more info, type help dataex
clear
input long id int date double ret float index_ret
14328 22221   -.0889829695224762   .001803953
14328 22222   .08837200701236725   .026489234
14328 22223 -.012820500880479813   .004759688
14328 22224    .0649351105093956   .018437335
14328 22225  .012195110321044922   -.00597336
14328 22228    .5140562057495117   .006632483
14328 22229  -.06896551698446274   .018172745
14328 22230   -.1082620695233345   .021329505
14328 22231 -.060702890157699585  -.005627702
14328 22232    .0102040721103549   .012056893
14328 22235   .04713800176978111   .032979846
14328 22236   -.0418006032705307   .028719326
14328 22237   .09395971894264221  -.000552742
14328 22238 -.021472372114658356   .013957879
14328 22239  .050156690180301666   .014596867
14328 22242   .13731345534324646   .032713316
14328 22243    .2020997405052185   .006492325
14328 22244 -.019650688394904137    .02114875
14328 22246 -.008908677846193314    .03774142
14328 22249  -.04044939950108528   .003200377
14328 22250 -.028103018179535866    .02749086
14328 22251   .04096387326717377 -.0006914838
14328 22252   -.1597222238779068  -.005501994
14328 22253  -.03305788338184357    .05205428
14328 22256  .014245000667870045   -.03006176
14328 22257    .1179775521159172     .1711087
14328 22258  -.03015078417956829  -.008148257
14328 22259  .059585560113191605   .012190343
14328 22260 -.041564811021089554   .003411108
14328 22263  -.18622449040412903 -.0008077892
14328 22264  -.10344832390546799   .010408126
14328 22265  -.02797200158238411  -.011401697
14328 22266   .02517983317375183   .017714906
14328 22267 -.017543843016028404   .001188701
14328 22270 -.042857103049755096  -.006062881
14328 22271  -.03358214721083641   .008756713
14328 22272 -.011583001352846622    .02997179
14328 22273 -.019531231373548508   .004895117
14328 22277  -.04780871793627739   -.01166837
14328 22278 -.041841063648462296  -.011195646
14328 22279   -.0567685067653656    .02688652
14328 22280 -.018518609926104546   -.02967245
14328 22284  -.05188674479722977   .027518934
14328 22285 -.014925358816981316    .04636436
14328 22286  .015151500701904297 .00018430663
14328 22287  .019900478422641754    .03052653
14328 22288   .04390251263976097   .007525986
14328 22291  .028037354350090027     .0802288
14328 22292    .0409090518951416     .0412015
14328 22293  -.04803488776087761   .024360437
14328 22294                    0    .06140633
14328 22295   .06880727410316467  -.014240673
14328 22299   .31330475211143494    .04185703
14328 22300 -.029411736875772476  -.008406159
14328 22301  .003367000026628375   .013295086
14328 22302    .1778523325920105   .064392425
14328 22305   .25925928354263306     .1391388
14328 22306   .12217193841934204    .09991153
14328 22307    3.012096643447876     .4485748
14328 22308   -.5663316249847412   -.05006109
14328 22309    .5365005731582642    .15863185
14328 22312 .0030165882781147957   .018297127
14328 22313  -.41203007102012634    -.1268997
14328 22314   .14705882966518402    .04247279
14328 22315  -.20958751440048218    -.0275064
14328 22316  -.03667140007019043   .034286346
14328 22319  -.09516838937997818   .063832745
14328 22320   -.1100323349237442   -.00651125
14328 22321   .05454548820853233    .01131396
14328 22322  -.03275863081216812   .012458925
14328 22323 -.003565058810636401   .006139165
14328 22327   .01073344238102436  -.014631206
14328 22328 -.017699098214507103   -.02176516
14328 22329 -.007207199931144714   -.03160319
14328 22330    .0344826802611351   .009863298
14328 22333   .14912287890911102  -.018951567
14328 22334    .1755724549293518  -.073093235
14328 22335   .18051953613758087    .11123592
14328 22336   -.0880088210105896  -.020706004
14328 22337  -.03377560153603554   -.02613215
14328 22340   .14606742560863495    .04919149
14328 22341  -.02723311446607113  -.018649874
14328 22342   -.0391937717795372   -.03523188
14328 22343  -.06410259008407593   -.04869925
14328 22344 .0024907172191888094   .007939019
14328 22347    .1540372371673584    .06883222
14328 22348    .1302475780248642    .05482205
14328 22349 -.061904724687337875    .04728695
14328 22350  .043654754757881165    .03871811
14328 22351   .08560312539339066   .030587496
14328 22354   .25806453824043274   .014233314
14328 22355  -.07264953851699829   -.03712335
14328 22356   .04147465154528618    .01224215
14328 22357   .03244834393262863   -.03881953
14328 22358 -.004999978002160788   .019755477
14328 22361  -.10337404906749725   -.01357413
14328 22362  -.14651721715927124   -.05685976
14328 22363  -.15384609997272491   -.07915107
14328 22364   .21286019682884216    .08334921
14328 22365  -.06398536264896393   -.02163259
end
format %td date

[Nick Cox]

rangestat is from SSC, as you are asked to explain (FAQ Advice #12).

The code is very confused. It uses a ym variable you have not generated yet. It generates a monthly date from year and quarter information, so all such dates are the equivalent of months January to April.

This is a (much) simpler way to get regressions for each month.

.

 gen ym = mofd(date)

. format ym %tm

. rangestat (reg) ret index_ret, interval(ym 0 0) by(id)

So each regression is based on about 20 observations, given weekends and holidays.

A rolling window for the previous 20 days could be

. rangestat (reg) ret index_ret, interval(date -20 -1) by(id)

making sure that you rename any previous result variables first. The recipe varies according to a different number of days. However, I do not know what "for the past 20 days with a rolling window of 5 days" means.

[Vince Williams]

Thank you so much sorry about the reply. Sorry I made a mistake about ym. It should be g ym = ym(year(date),month(date)).

[Nick Cox]

OK, but mofd() gets you with one function call, not three.

[Joy Junial]

Dear STATA
Please help me. I am using STATA 16. But I have an old problem with the date format from excel imported file.
1. In excel my date variable is date_id.
2. In excel the date format is annual but presented (as daily): [31 Dec 1999, 31 Dec 2000, 31 Dec 2001, ... 31 Dec 2019] - I thought this will simplify my life to replicate Nick's old solution to similar problem.
3. My stata syntax to format is:
gen dateid=date(date_id,"DMY")
format date_id %td

4. The error I get is "type mismatch".

[Joy Junial]

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input int date_id
14609
14975
15340
15705
16070
16436
16801
17166
17531
17897
end
format %td date_id

------------------ copy up to and including the previous line ------------------

[Joy Junial]

date_id

31 December 1999

31 December 2000

31 December 2001

31 December 2002

31 December 2003

31 December 2004

31 December 2005

31 December 2006

31 December 2007

31 December 2008

[Joy Junial]

The last post is the Excel Example. Am reading Q&A - learning how to float compliant query/post.

[Joy Junial]

Listed 10 out of 713 observations

. format date_id %td

. list date_id in 1/10

+-----------+
| date_id |
|-----------|
1. | 31dec1999 |
2. | 31dec2000 |
3. | 31dec2001 |
4. | 31dec2002 |
5. | 31dec2003 |
|-----------|
6. | 31dec2004 |
7. | 31dec2005 |
8. | 31dec2006 |
9. | 31dec2007 |
10. | 31dec2008 |
+-----------+
It looks like STATA can read my data. But I am a bit puzzled but the "gap" thing, see below.

[Joy Junial]

. tsset firm_id date_id, yearly

Variable date_id had been formatted %td (a daily period),
and you asked for a yearly period. Are you sure that is
what you want? It has been done; date_id is now formatted %ty.

panel variable: firm_id (unbalanced)
time variable: date_id, 1.5e+04 to 2.2e+04, but with gaps
delta: 1 year

[Joy Junial]

Since my original questions I have been reading STATA LIST's Q&A (explains sequential posting in parts). And, I have been thinking through my problem (explains questions below). I trust that my question is clear.
It looks like STATA corrected my error by with annual format, "%ty".
I have 3 questions:
1. Since data recognized date_id as integer, "int", was there any need to format (or do anything) with date_id?
2. In my last post, date_id 's date range: looks funny, "1.5e+04 to 2.2e+04,".
3. Why is STATA saying "but with gaps"? My date_id variable does not have gaps.
a) Is the "unbalanced" panel has something to do with this?
b) Is this to do with leap year? Thinking wild.
Please help.

[Andrew Musau]

You actually need to create a year variable.

gen year= year(date_id)
xtset firm_id year

[Joy Junial]

. gen year_id = year(date_id)

. list date_id year_id in 1/10

+---------------------+
| date_id year_id |
|---------------------|
1. | 31dec1999 1999 |
2. | 31dec2000 2000 |
3. | 31dec2001 2001 |
4. | 31dec2002 2002 |
5. | 31dec2003 2003 |
|---------------------|
6. | 31dec2004 2004 |
7. | 31dec2005 2005 |
8. | 31dec2006 2006 |
9. | 31dec2007 2007 |
10. | 31dec2008 2008 |
+---------------------+

. xtset firm_id year_id, yearly
panel variable: firm_id (unbalanced)
time variable: year_id, 1999 to 2019, but with a gap
delta: 1 year
============================
Thanks, Andrew. Done as advised, look right.

What about Q3, above?

[Joy Junial]

If I may clarify Q3: Why am I still getting ,"but with a gap"?
Is it because I have unbalanced panel?

[Andrew Musau]

Yes. There is at least one missing observation (year) for a firm. Possibly multiple missing observations for multiple firms. See the output of

xtset firm_id year_id
xtdescribe