Calculate 12M Rolling Totals

[Timothy Krause]

Hi,

I haven't used Stata in about 4 years and I've never posted here before, so please excuse my rustiness and my "newbieness." I'm just trying to calculate rolling 12-month sums (of "logHPR_STK") by the categorical variable "newtick." I tried egen and rangestat (which is new to me) and tried what I found by searching and using the help functions in Stata. The time variable is "YR_MO" and the category is "newtick".

I've included a snippet of my data below for only 100 observations for just two categories of "newtick"

When I run the following code, it just generates an identical column to logHPR_STK and no sums. I would appreciate any help someone might provide.. Thanks, Tim

rangestat (sum) logHPR_STK, interval(YR_MO -11 0) by(newtick)


YR_MO RET_STK HPR_STK newtick logHPR_STK
20090800 .1059432 1.1059432 A .1006985
20090900 .08372273 1.0837227 A .0804021
20091000 -.11103126 .88896874 A -.1176932
20091100 .16895717 1.1689572 A .156112
20091200 .074343 1.074343 A .0717093
20100100 -.09784355 .90215645 A -.1029673
20100200 .12236883 1.1223688 A .1154415
20100300 .09313415 1.0931341 A .0890489
20100400 .05437624 1.0543762 A .0529494
20100500 -.10755648 .89244352 A -.113792
20100600 -.12144624 .87855376 A -.1294782
20100700 -.01758706 .98241294 A -.0177435
20100800 -.03437168 .96562832 A -.0349763
20100900 .23730069 1.2373007 A .2129322
20101000 .04285287 1.0428529 A .0419601
20101100 .00632187 1.0063219 A .006302
20101200 .18303826 1.1830383 A .1680859
20110100 .00965488 1.0096549 A .0096086
20110200 .00597657 1.0059766 A .0059588
20110300 .06416342 1.0641634 A .062189
20110400 .1145601 1.1145601 A .1084598
20110500 -.00080146 .99919854 A -.0008018
20110600 .02486468 1.0248647 A .0245606
20110700 -.17511252 .82488748 A -.1925083
20110800 -.12547441 .87452559 A -.1340737
20110900 -.15242742 .84757258 A -.1653788
20111000 .18623999 1.18624 A .1707886
20111100 .01159969 1.0115997 A .0115329
20111200 -.06853332 .93146668 A -.0709949
20120100 .21586032 1.2158603 A .1954519
20120200 .02707788 1.0270779 A .0267178
20120300 .022696 1.022696 A .0224423
20120400 -.05234775 .94765225 A -.0537677
20120500 -.03603605 .96396395 A -.0367014
20120600 -.03246429 .96753571 A -.0330029
20120700 -.02421001 .97578999 A -.0245079
20120800 -.02951165 .97048835 A -.0299559
20120900 .03740584 1.0374058 A .0367232
20121000 -.06397917 .93602083 A -.0661175
20121100 .06390662 1.0639066 A .0619476
20121200 .07182026 1.0718203 A .0693584
20130100 .09379581 1.0937958 A .089654
20130200 -.0736936 .9263064 A -.0765502
20130300 .01470592 1.0147059 A .0145988
20130400 -.01262813 .98737187 A -.0127085
20130500 .09676646 1.0967665 A .0923663
20130600 -.05654571 .94345429 A -.0582074
20130700 .04607112 1.0460711 A .0450414
20130800 .04270064 1.0427006 A .0418141
20130900 .10141511 1.1014151 A .0965958
20090800 .02721088 1.0272109 AA .0268472
20090900 .08879665 1.0887967 AA .0850731
20091000 -.05335364 .94664636 AA -.0548297
20091100 .01046702 1.010467 AA .0104126
20091200 .28753996 1.28754 AA .2527334
20100100 -.21029784 .78970216 AA -.2360994
20100200 .04713281 1.0471328 AA .0460558
20100300 .07067666 1.0706767 AA .0682908
20100400 -.05688199 .94311801 AA -.0585639
20100500 -.13104989 .86895011 AA -.1404696
20100600 -.13573882 .86426118 AA -.1458803
20100700 .11033794 1.1103379 AA .1046644
20100800 -.08258732 .91741268 AA -.0861979
20100900 .18522143 1.1852214 AA .1699296
20101000 .08505373 1.0850537 AA .0816295
20101100 .00114153 1.0011415 AA .0011409
20101200 .17257145 1.1725715 AA .1591992
20110100 .07667312 1.0766731 AA .0738758
20110200 .01870855 1.0187086 AA .0185357
20110300 .04807118 1.0480712 AA .0469515
20110400 -.03737259 .96262741 AA -.0380888
20110500 -.0094118 .9905882 AA -.0094564
20110600 -.05651397 .94348603 AA -.0581737
20110700 -.07124843 .92875157 AA -.073914
20110800 -.12898842 .87101158 AA -.1381
20110900 -.25234377 .74765623 AA -.290812
20111000 .12434698 1.124347 AA .1172024
20111100 -.06598511 .93401489 AA -.0682629
20111200 -.13672662 .86327338 AA -.1470239
20120100 .17456651 1.1745665 AA .1608991
20120200 .00393703 1.003937 AA .0039293
20120300 -.01474923 .98525077 AA -.0148591
20120400 -.02894221 .97105779 AA -.0293693
20120500 -.1181911 .8818089 AA -.1257799
20120600 .02339179 1.0233918 AA .0231224
20120700 -.03199997 .96800003 AA -.0325232
20120800 .01416767 1.0141677 AA .0140682
20120900 .03446251 1.0344625 AA .033882
20121000 -.02879727 .97120273 AA -.0292201
20121100 -.01866976 .98133024 AA -.0188462
20121200 .03210469 1.0321047 AA .0316001
20130100 .01843316 1.0184332 AA .0182653
20130200 -.03280539 .96719461 AA -.0333556
20130300 0 1 AA 0
20130400 -.00234747 .99765253 AA -.0023502
20130500 .00352941 1.0035294 AA .0035232
20130600 -.07999998 .92000002 AA -.0833816
20130700 .01662399 1.016624 AA .0164873
20130800 -.02767296 .97232704 AA -.0280631
20130900 .05454547 1.0545455 AA .0531098

[Clyde Schechter]

The problem is that your variable yr_mo may look like a year and month variable to your eyes, but to Stata it is just some 8 digit numbers. And when Stata appplies -rangestat- with an interval bounded by -11 and 0, it calculates things like 20090800-11 = 20090789, which does not correspond to any value of yr_mo in your data. In fact, the only thing between 0 and -11 that you can subtract from a value of yr_mo and come up with another value of yr_mo is 0, which is why the result you got is just a clone of loghpr_stk.

Once you convert yr_mo to a proper Stata monthly date variable, your -rangestat- command goes through perfectly:

* Example generated by -dataex-. For more info, type help dataex
clear
input long yr_mo float(ret_stk hpr_stk) str2 newtick float loghpr_stk
20090800   .1059432 1.1059432 "A"   .1006985
20090900  .08372273 1.0837227 "A"   .0804021
20091000 -.11103126  .8889688 "A"  -.1176932
20091100  .16895717 1.1689572 "A"    .156112
20091200    .074343  1.074343 "A"   .0717093
20100100 -.09784355  .9021565 "A"  -.1029673
20100200  .12236883 1.1223688 "A"   .1154415
20100300  .09313415  1.093134 "A"   .0890489
20100400  .05437624 1.0543762 "A"   .0529494
20100500 -.10755648  .8924435 "A"   -.113792
20100600 -.12144624  .8785537 "A"  -.1294782
20100700 -.01758706  .9824129 "A"  -.0177435
20100800 -.03437168  .9656283 "A"  -.0349763
20100900   .2373007 1.2373008 "A"   .2129322
20101000  .04285287 1.0428529 "A"   .0419601
20101100  .00632187 1.0063219 "A"    .006302
20101200  .18303826 1.1830384 "A"   .1680859
20110100  .00965488 1.0096549 "A"   .0096086
20110200  .00597657 1.0059766 "A"   .0059588
20110300  .06416342 1.0641634 "A"    .062189
20110400   .1145601 1.1145601 "A"   .1084598
20110500 -.00080146  .9991986 "A"  -.0008018
20110600  .02486468 1.0248647 "A"   .0245606
20110700  -.1751125  .8248875 "A"  -.1925083
20110800 -.12547441  .8745256 "A"  -.1340737
20110900  -.1524274  .8475726 "A"  -.1653788
20111000     .18624   1.18624 "A"   .1707886
20111100  .01159969 1.0115997 "A"   .0115329
20111200 -.06853332  .9314667 "A"  -.0709949
20120100   .2158603 1.2158602 "A"   .1954519
20120200  .02707788 1.0270779 "A"   .0267178
20120300    .022696  1.022696 "A"   .0224423
20120400 -.05234775  .9476522 "A"  -.0537677
20120500 -.03603605  .9639639 "A"  -.0367014
20120600 -.03246429  .9675357 "A"  -.0330029
20120700 -.02421001    .97579 "A"  -.0245079
20120800 -.02951165  .9704884 "A"  -.0299559
20120900  .03740584 1.0374058 "A"   .0367232
20121000 -.06397917  .9360209 "A"  -.0661175
20121100  .06390662 1.0639066 "A"   .0619476
20121200  .07182026 1.0718203 "A"   .0693584
20130100  .09379581 1.0937958 "A"    .089654
20130200  -.0736936  .9263064 "A"  -.0765502
20130300  .01470592 1.0147059 "A"   .0145988
20130400 -.01262813  .9873719 "A"  -.0127085
20130500  .09676646 1.0967665 "A"   .0923663
20130600 -.05654571  .9434543 "A"  -.0582074
20130700  .04607112  1.046071 "A"   .0450414
20130800  .04270064 1.0427006 "A"   .0418141
20130900  .10141511 1.1014152 "A"   .0965958
20090800  .02721088  1.027211 "AA"  .0268472
20090900  .08879665 1.0887967 "AA"  .0850731
20091000 -.05335364  .9466463 "AA" -.0548297
20091100  .01046702  1.010467 "AA"  .0104126
20091200  .28753996   1.28754 "AA"  .2527334
20100100 -.21029784  .7897022 "AA" -.2360994
20100200  .04713281 1.0471328 "AA"  .0460558
20100300  .07067666 1.0706767 "AA"  .0682908
20100400 -.05688199   .943118 "AA" -.0585639
20100500 -.13104989  .8689501 "AA" -.1404696
20100600 -.13573882  .8642612 "AA" -.1458803
20100700  .11033794 1.1103379 "AA"  .1046644
20100800 -.08258732  .9174127 "AA" -.0861979
20100900  .18522143 1.1852214 "AA"  .1699296
20101000  .08505373 1.0850537 "AA"  .0816295
20101100  .00114153 1.0011415 "AA"  .0011409
20101200  .17257145 1.1725715 "AA"  .1591992
20110100  .07667312 1.0766732 "AA"  .0738758
20110200  .01870855 1.0187086 "AA"  .0185357
20110300  .04807118 1.0480711 "AA"  .0469515
20110400 -.03737259  .9626274 "AA" -.0380888
20110500  -.0094118  .9905882 "AA" -.0094564
20110600 -.05651397   .943486 "AA" -.0581737
20110700 -.07124843  .9287516 "AA"  -.073914
20110800 -.12898842  .8710116 "AA"    -.1381
20110900 -.25234377  .7476562 "AA"  -.290812
20111000  .12434698  1.124347 "AA"  .1172024
20111100 -.06598511  .9340149 "AA" -.0682629
20111200 -.13672662  .8632734 "AA" -.1470239
20120100   .1745665 1.1745665 "AA"  .1608991
20120200  .00393703  1.003937 "AA"  .0039293
20120300 -.01474923  .9852508 "AA" -.0148591
20120400 -.02894221  .9710578 "AA" -.0293693
20120500  -.1181911  .8818089 "AA" -.1257799
20120600  .02339179 1.0233918 "AA"  .0231224
20120700 -.03199997  .9680001 "AA" -.0325232
20120800  .01416767 1.0141677 "AA"  .0140682
20120900  .03446251 1.0344625 "AA"   .033882
20121000 -.02879727  .9712027 "AA" -.0292201
20121100 -.01866976  .9813302 "AA" -.0188462
20121200  .03210469 1.0321047 "AA"  .0316001
20130100  .01843316 1.0184332 "AA"  .0182653
20130200 -.03280539  .9671946 "AA" -.0333556
20130300          0         1 "AA"         0
20130400 -.00234747  .9976525 "AA" -.0023502
20130500  .00352941 1.0035294 "AA"  .0035232
20130600 -.07999998       .92 "AA" -.0833816
20130700  .01662399  1.016624 "AA"  .0164873
20130800 -.02767296  .9723271 "AA" -.0280631
20130900  .05454547 1.0545455 "AA"  .0531098
end

//    CREATE A USABLE MONTHLY DATE VARIABLE
gen year = floor(yr_mo/10000)
gen month = floor(mod(yr_mo, 10000)/100)
gen mdate = ym(year, month)
assert missing(mdate) == missing(year, month)
format mdate %tm
drop year month yr_mo

rangestat (sum) loghpr_stk, interval(mdate -11 0) by(newtick) // N.B. UNCHANGED FROM YOUR ORIGINAL EXCEPT FOR NAME OF VARIABLE mdate

If you are going to be working with date variables in Stata going forward, you really should read -help datetime- and the associated chapters in the PDF manuals that come installed with Stata. It's a complicated topic, and you are unlikely to remember all of it. But you will get an understanding of the concepts underlying Stata's approach to dates and times, and familiarity with the underlying data, the display formatting options, and the functions that convert one type of date into another, and other utilities available. With a little practice, the basics of it will be second nature, and you will only need to refer to the help files and manuals from time to time for details about the less-used aspects when they arise.

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Timothy Krause]

Hi Clyde,

Thank you very much for your help with this specific issue as well as your suggestions to read the material on datetime and dataex. When I was a Ph.D. student, I new the datetime functionality pretty well, but that's been over ten years ago, so it seems I need a thorough review if I'm going to start working with panel data again.. As for dataex, I've never used it, but I will certainly read up on it now that I know that StataList is so very helpful!

Thanks again,

Tim