Seeking guidance for the interval specification of rangestat command

[Gupta Manish]

Dear Stata community,

I have stock returns file from CRSP where the date format follows business calendar (for e.g. 12Apr2019). For each stock (id), I want to compute the number of trading days, over non-missing observations, in last three years. Since number of trading days might differ over years, and also for different stocks, when new firms enter and/or some existing firms drop, the computations will differ for each id over the same period. Thus, I want to specify a 3-year range for each id over the trading days during the last three years (13Apr2016 to 12Apr2019).

I am not sure how to specify the rolling window for each id’s non-missing obs in the interval part of rangestat syntax:
rangestat (count) price, interval(date -xx 0) by(id)

I have been trying to find some guidance available online but I have not been able to find clear guidance since most intervals are illustrated within a few trading days, such as 3, 5, and 90) window.

Any guidance will be sincerely appreciated!

Thanks for reading.

Kind regards,
Manish

[Clyde Schechter]

Well, this is somewhat complicated, because the number of business dates can be different in different years, and just because 12 April 2019 is a business date, doesn't mean it will be in another year--in fact it won't be in 2020. So I think you have to do this by using regular calendar dates. Something like this (not tested):

gen cdate = dofb(date)
gen m = month(cdate)
gen d = day(cdate)
gen y = year(cdate)
gen window_start = mdy(m, d, y-3) if m != 2 | d != 29
replace window_start = mdy(3, 1, y-2) if m == 2 & d == 29
rangestat (count) price, interval(cdate window_start cdate) by(id)

[Gupta Manish]

Thank you very much, Clyde.

I guess it is dofc (not dofb) in your first command. Somehow, I am getting 0 for cdate for all observations. I am attaching a sample file for your consideration.

permno date ticker cusip bidlo askhi prc
78736 17jun1997 ANAFF 00002130 .3125 .40625 -.359375
78736 03nov1995 ANAFF 00002130 .40625 .75 .625
78736 01aug1995 ANAFF 00002130 .53125 .53125 .53125
78736 17mar1993 ANAFF 00002130 .6875 .9375 .875
78736 15may1995 ANAFF 00002130 .375 .375 .375
78736 24feb1995 ANAFF 00002130 .40625 .5 -.453125
78736 21apr1994 ANAFF 00002130 1.3125 1.5 1.375
78736 02may1995 ANAFF 00002130 .375 .4375 .375
78736 30jan1995 ANAFF 00002130 .375 .5625 -.46875
78736 20mar1997 ANAFF 00002130 .1875 .1875 .1875
78736 28apr1993 ANAFF 00002130 .75 .8125 .8125
78736 26oct1995 ANAFF 00002130 .25 .34375 -.296875
78736 27jan1993 ANAFF 00002130 .96875 1.0625 .96875
78736 11nov1993 ANAFF 00002130 2.40625 2.5625 2.4375
78736 02apr1993 ANAFF 00002130 .71875 .78125 .71875
78736 26mar1993 ANAFF 00002130 .875 .875 .875
78736 23dec1994 ANAFF 00002130 .5 .625 .5
78736 18may1995 ANAFF 00002130 .46875 .5 .5
78736 17nov1993 ANAFF 00002130 2.4375 3.125 3.125
78736 18dec1995 ANAFF 00002130 .34375 .40625 -.375
78736 17may1995 ANAFF 00002130 .375 .5 .5
78736 31mar1997 ANAFF 00002130 .15625 .21875 -.1875
78736 18aug1994 ANAFF 00002130 .875 .875 .875
78736 17feb1995 ANAFF 00002130 .40625 .53125 -.46875
78736 18feb1997 ANAFF 00002130
78736 01nov1996 ANAFF 00002130 .28125 .3125 .3125
78736 08feb1994 ANAFF 00002130 2 2.375 2.125
78736 10feb1997 ANAFF 00002130
78736 11feb1993 ANAFF 00002130 1.125 1.125 1.125
78736 26jan1993 ANAFF 00002130 1 1.1875 1
78736 29jan1997 ANAFF 00002130
78736 21oct1993 ANAFF 00002130 2.0625 2.25 2.125
78736 12sep1994 ANAFF 00002130 .75 .796875 .78125
78736 16sep1994 ANAFF 00002130 .71875 .75 .71875
78736 08jul1996 ANAFF 00002130 .6875 .78125 .703125
78736 24jan1996 ANAFF 00002130 .3125 .3125 .3125
78736 13oct1994 ANAFF 00002130 .625 .71875 .71875
78736 20may1996 ANAFF 00002130 .71875 .71875 .71875
78736 13may1997 ANAFF 00002130 .40625 .40625 .40625
78736 22jun1994 ANAFF 00002130 .875 .96875 .875
78736 28jul1993 ANAFF 00002130 3.1875 3.3125 3.3125
78736 04feb1997 ANAFF 00002130
78736 05apr1995 ANAFF 00002130 .375 .375 .375
78736 25sep1995 ANAFF 00002130 .375 .375 .375
78736 20nov1995 ANAFF 00002130 .4375 .5 .5
78736 30apr1996 ANAFF 00002130 .625 .75 .625
78736 20jun1994 ANAFF 00002130 .9375 .9375 .9375
78736 04aug1993 ANAFF 00002130 2.6875 3.0625 3
78736 31oct1994 ANAFF 00002130 .4375 .5625 .5625
78736 28jul1995 ANAFF 00002130 .53125 .53125 .53125
78736 26jan1996 ANAFF 00002130 .28125 .28125 .28125
78736 13jan1993 ANAFF 00002130 1.125 1.25 1.125
78736 06sep1994 ANAFF 00002130 .671875 .75 .6875
78736 01may1997 ANAFF 00002130 .5 .609375 .59375
78736 10aug1994 ANAFF 00002130 .84375 .90625 .84375
78736 04jun1997 ANAFF 00002130 .375 .40625 .40625
78736 22nov1993 ANAFF 00002130 3.0625 3.4375 3.25
78736 13nov1995 ANAFF 00002130 .3125 .375 .3125
78736 11apr1995 ANAFF 00002130 .375 .375 .375
78736 13aug1996 ANAFF 00002130 .71875 .8125 .71875
78736 13may1994 ANAFF 00002130 .9375 1.0625 1.0625
78736 01jun1994 ANAFF 00002130 .9375 1.125 .9375
78736 12may1997 ANAFF 00002130 .40625 .421875 .40625
78736 20nov1996 ANAFF 00002130 .25 .25 .25
78736 22sep1994 ANAFF 00002130 .6875 .6875 .6875
78736 18oct1993 ANAFF 00002130 2.21875 2.375 2.375
78736 20oct1993 ANAFF 00002130 2.125 2.3125 2.125
78736 23feb1993 ANAFF 00002130 .9375 .9375 .9375
78736 15jan1996 ANAFF 00002130 .25 .25 .25
78736 29nov1996 ANAFF 00002130 .28125 .375 -.328125
78736 19jul1996 ANAFF 00002130 .5 .625 .5625
78736 02nov1993 ANAFF 00002130 2.4375 2.6875 2.5625
78736 29feb1996 ANAFF 00002130 .390625 .390625 .390625
78736 22apr1993 ANAFF 00002130 .65625 .8125 -.734375
78736 24nov1993 ANAFF 00002130 3.0625 3.25 3.0625
78736 21feb1996 ANAFF 00002130 .3125 .3125 .3125
78736 14feb1995 ANAFF 00002130 .375 .5 .375
78736 22jan1996 ANAFF 00002130 .3125 .3125 .3125
78736 12jul1996 ANAFF 00002130 .65625 .65625 .65625
78736 29may1997 ANAFF 00002130 .375 .375 .375
78736 03aug1995 ANAFF 00002130 .53125 .53125 .53125
78736 18mar1997 ANAFF 00002130 .21875 .25 .21875
78736 11sep1996 ANAFF 00002130 .5625 .5625 .5625
78736 01oct1996 ANAFF 00002130 .375 .375 .375
78736 02feb1996 ANAFF 00002130 .34375 .34375 .34375
78736 14oct1996 ANAFF 00002130 .375 .4375 -.40625
78736 26nov1993 ANAFF 00002130 3.0625 3.1875 3.0625
78736 12jul1995 ANAFF 00002130 .53125 .5625 .53125
78736 18jul1994 ANAFF 00002130 .75 .875 .8125
78736 05may1995 ANAFF 00002130 .375 .46875 -.421875
78736 09jun1993 ANAFF 00002130 2.1875 2.375 2.28125
78736 13jan1995 ANAFF 00002130 .4375 .5625 .5
78736 10jun1996 ANAFF 00002130 .5 .53125 .5
78736 17jan1997 ANAFF 00002130 .15625 .21875 .21875
78736 01jun1993 ANAFF 00002130 .9375 1.1875 1.125
78736 23may1997 ANAFF 00002130 .46875 .53125 .53125
78736 16nov1995 ANAFF 00002130 .3125 .5 .375
78736 24sep1996 ANAFF 00002130 .4375 .4375 .4375
78736 04may1995 ANAFF 00002130 .375 .375 .375
78736 14sep1994 ANAFF 00002130 .75 .8125 .75


gen cdate = dofc(date)
gen m = month(cdate)
gen d = day(cdate)
gen y = year(cdate)
gen window_start = mdy(m, d, y-3) if m != 2 | d != 29
replace window_start = mdy(3, 1, y-2) if m == 2 & d == 29
rangestat (count) prc, interval(cdate window_start cdate) by(permno)

[Clyde Schechter]

No, it's not -dofc()-. That function converts datetime variables to dates. You said you have business dates. To convert business dates to calendar dates you use -dofb()-. The data example you post is not one I can illustrate this in because you posted a string variable for the date--and that's not useful for anything really. So you need to load up your business calendar and convert that string to the business dates you want.

But for the present purposes, what you really want to do is convert it to regular Stata dates, not business dates, because the business date is not going to be useful for this purpose. (You may well need the business dates for some other purpose later, but not for this.)

Working from the string variable you show the code would be

gen cdate = daily(date, "DMY")
assert missing(cdate) == missing(date)
// AND THE REST OF THE CODE STAYS THE SAME
gen m = month(cdate)
gen d = day(cdate)
gen y = year(cdate)
gen window_start = mdy(m, d, y-3) if m != 2 | d != 29
replace window_start = mdy(3, 1, y-2) if m == 2 & d == 29
rangestat (count) price, interval(cdate window_start cdate) by(id)

[Gupta Manish]

Thank you very much, Clyde!

Date and time still beat me so really sorry for the confusion.

I just ran the code and it seems to work fine.

One last question, I wanted to attach a small sample data file but the message window won't accept it. Is it due to some technical issue on my side or I am missing something.

Have a great weekend.

[Nick Cox]

I wanted to attach a small sample data file but the message window won't accept it. Is it due to some technical issue on my side or I am missing something.

We can't guess at technical issues on your side. But the FAQ Advice at #12 explains in detail how data examples should be given (and not given). See
http://www.statalist.org/forums/help#stata

It seems that you have yet to read that, despite 27 posts here. Among other details, it explains

* about giving information on community-contributed commands you mention (so, in particular, rangestat is from SSC)

* that data examples are much better given between CODE delimiters using dataex.

[Gupta Manish]

Dear Nick, I will sort this out next time. Thank you very much.