rangestat - interval end

[Felix Schrock]

I have a panel data set of daily price changes (px_change) for different bonds (isin). I would like to generate an "include" indicator, based on the argument, that for each bond there are at least 4 "px_change" observations within a 21-time step period (period: ret_day). The interval should start with the first time step and end with the 21st; the following interval should be 22nd time step until the 43rd. The problem with my code is that once I get to the "end" of observation period of a given bond (isin); the interval gets smaller after the 20th time step to the end of the bond. i.e. has_px_change_sum gets smaller with each time step.

How can I fix the interval in a way that it always contains at 21 time steps and once the following interval has less than 21 time steps, it deletes the last <20 observations. The result should be clean intervals of 21 time steps for a given isin.

px_change	isin	ret_day	has_px_change	has_px_change_sum	include
.00185404	XS0295018070	1283	1	21	1
.00185687	XS0295018070	1284	1	21	1
.0156	XS0295018070	1285	1	20	1
0	XS0295018070	1286	0	19	1
...	XS0295018070	1287	1	18	1
...	XS0295018070	1288	1	17	1
.0015687	XS0295018070	1289	1	16	1
.001887	XS0295018070	1290	1	15	1
.00187	XS0295018070	1291	1	14	1
.001687	XS0295018070	1292	1	13	1
.00185687	XS0295018070	1293	1	12	1
.001887	XS0295018070	1294	1	11	1
...	XS0295018070	1295	1	10	1
.00185687	XS0295018070	1296	1	9	1
0.00185687	XS0295018070	1297	1	8	1
.00185687	XS0295018070	1298	1	7	1
0	XS0295018070	1299	0	6	1
.00185687	XS0295018070	1300	1	5	1
0	XS0295018070	1301	0	4	0
.0580	XS0295018070	1302	1	3	0
...	XS0295018070	1303	1	2	0
.1248	XS0295018070	1304	1	1	0

isid isin date, sort
egen ret_day = group(isin date)
gen byte has_px_change = px_change != 0 & !missing(px_change)
rangestat (sum) has_px_change , by(isin) interval(ret_day 0 20)

gen byte include = has_px_change_sum > 4 & !missing(has_px_change_sum)

* Example generated by -dataex-. To install: ssc install dataex
clear
input float monthly int date double(px_last px_change) long isin float ret_day byte has_px_change double has_px_change_sum byte include
633 19297 118.441    .005740245403982524 1   1 1 21 1
634 19298 118.631    .001604174230207426 1   2 1 21 1
634 19299 119.084   .0038185634446308548 1   3 1 21 1
634 19302 119.384   .0025192301232743034 1   4 1 21 1
634 19303 119.031  -.0029568451383769553 1   5 1 21 1
634 19304 120.307    .010719896497551026 1   6 1 21 1
634 19305  120.26  -.0003906672097217705 1   7 1 21 1
634 19306 120.635    .003118243805088974 1   8 1 21 1
634 19309 120.404   -.001914867161271677 1   9 1 21 1
634 19310 120.657   .0021012590943822475 1  10 1 21 1
634 19311 120.453  -.0016907431810835138 1  11 1 21 1
634 19312 120.056  -.0032958913435116232 1  12 1 21 1
634 19313 120.032 -.00019990671020191336 1  13 1 21 1
634 19316 119.707   -.002707611303652383 1  14 1 21 1
634 19317 118.721    -.00823677813327533 1  15 1 21 1
634 19318 118.336   -.003242897212793062 1  16 1 21 1
634 19319 118.115   -.001867563547863741 1  17 1 21 1
634 19320 117.727    -.00328493417432156 1  18 1 21 1
634 19323 118.413    .005827040525962547 1  19 1 21 1
634 19324 118.339  -.0006249313842229998 1  20 1 21 1
634 19325 119.377    .008771410946518026 1  21 1 21 1
634 19326 119.304  -.0006115080794457333 1  22 1 21 1
634 19327 118.943  -.0030258834573862084 1  23 1 21 1
635 19330 118.517  -.0035815474639113018 1  24 1 21 1
635 19331  119.08    .004750373364158749 1  25 1 21 1
635 19332 119.903    .006911320120927171 1  26 1 21 1
635 19333  120.47    .004728822464825676 1  27 1 21 1
635 19334  120.02   -.003735369801610383 1  28 1 21 1
635 19337 120.024  .00003332777870359014 1  29 1 21 1
635 19338 119.741  -.0023578617609811476 1  30 1 21 1
635 19339  119.73 -.00009186494183275302 1  31 1 21 1
635 19340 119.663  -.0005595924162700015 1  32 1 21 1
635 19341 119.927    .002206195733016973 1  33 1 21 1
635 19344 119.584   -.002860073211203511 1  34 1 21 1
635 19345  118.95   -.005301712603692804 1  35 1 21 1
635 19346 118.501  -.0037746952501050697 1  36 1 21 1
635 19347 119.359    .007240445228310224 1  37 1 21 1
635 19348 119.826    .003912566291607661 1  38 1 21 1
635 19351 119.666  -.0013352694740707074 1  39 1 21 1
635 19352 119.693   .0002256279979275736 1  40 1 21 1
635 19353  119.77   .0006433124744136932 1  41 1 21 1
635 19354 121.604    .015312682641730009 1  42 1 21 1
635 19355  121.64   .0002960428933258887 1  43 1 21 1
635 19358 121.467  -.0014222295297599624 1  44 1 21 1
636 19359 121.544   .0006339170309631273 1  45 1 21 1
636 19360 118.869   -.022008490752320125 1  46 1 21 1
636 19361 117.816   -.008858491280316965 1  47 1 21 1
636 19362 116.546   -.010779520608406295 1  48 1 21 1
636 19365 117.272    .006229300018876658 1  49 1 21 1
636 19366 117.858    .004996930213520691 1  50 1 21 1
636 19367 118.082   .0019005922381169673 1  51 1 21 1
636 19368 116.751   -.011271828051692797 1  52 1 21 1
636 19369  117.17    .003588834356879144 1  53 1 21 1
636 19372 117.738    .004847657250149337 1  54 1 21 1
636 19373 118.432    .005894443595101009 1  55 1 21 1
636 19374 118.682   .0021109159686571195 1  56 1 21 1
636 19375 118.064  -.0052071923290811515 1  57 1 21 1
636 19376 119.594    .012959073045128076 1  58 1 21 1
636 19379 118.983   -.005108951954111327 1  59 1 21 1
636 19380 118.926 -.00047906003378635735 1  60 1 21 1
636 19381 119.129    .001706943813800203 1  61 1 21 1
636 19382 118.291  -.0070343912901141455 1  62 1 21 1
636 19383  117.54   -.006348750116238687 1  63 1 21 1
636 19386 117.039   -.004262378764675895 1  64 1 21 1
636 19387  117.15   .0009484018147797248 1  65 1 21 1
636 19388 117.203    .000452411438326908 1  66 1 21 1
636 19389 117.798   .0050766618601912825 1  67 1 21 1
637 19390 117.608  -.0016129306100273156 1  68 1 21 1
637 19393 118.676    .009081014896945768 1  69 1 21 1
637 19394 117.896   -.006572516768344072 1  70 1 21 1
637 19395 118.006   .0009330257175815925 1  71 1 21 1
637 19396 118.157   .0012795959527481336 1  72 1 21 1
637 19397 118.201  .00037238589334526883 1  73 1 21 1
637 19400  118.01   -.001615891574521267 1  74 1 21 1
637 19401 117.617   -.003330226252012547 1  75 1 21 1
637 19402 116.889   -.006189581438057497 1  76 1 21 1
637 19403 117.231   .0029258527320791413 1  77 1 21 1
637 19404 117.167  -.0005459306838634231 1  78 1 21 1
637 19407 117.562    .003371256411788268 1  79 1 21 1
637 19408 117.531 -.00026369064833867834 1  80 1 21 1
637 19409 116.856   -.005743165632896949 1  81 1 21 1
637 19410 118.089    .010551447935921169 1  82 1 21 1
637 19411 118.018  -.0006012414365436066 1  83 1 21 1
637 19414  118.05   .0002711450795641044 1  84 1 21 1
637 19415 119.652    .013570520965692535 1  85 1 21 1
637 19416 119.763    .000927690301875474 1  86 1 21 1
637 19417   119.7  -.0005260389268806091 1  87 1 21 1
638 19418 120.583    .007376775271512076 1  88 1 21 1
638 19421  120.38   -.001683487722149913 1  89 1 20 1
638 19422 119.696   -.005682006977903286 1  90 1 20 1
638 19423 119.492  -.0017043176045982615 1  91 1 20 1
638 19424 119.096    -.00331402939108895 1  92 1 20 1
638 19425 118.801  -.0024769933499026136 1  93 1 20 1
638 19428 118.414  -.0032575483371352133 1  94 1 20 1
638 19429 119.042   .0053034269596500426 1  95 1 20 1
638 19430 119.052  .00008400396498719036 1  96 1 20 1
638 19431 119.078   .0002183919631757236 1  97 1 20 1
638 19432 119.231    .001284872100639846 1  98 1 20 1
638 19435 120.085    .007162566782128802 1  99 1 20 1
638 19436 121.931    .015372444518466118 1 100 1 20 1
end
format %tm monthly
format %td date
label values isin isin
label def isin 1 "XS0295018070", modify

Thanks!

[Amin Sofla]

sorry; no time to solve it; however, I think that it would be easier if you use rangerun .

ssc install rangerun
help rangerun

.

[Felix Schrock]

Hi Amin, thanks for the suggestion. Unfortunately, I am not familiar with programing the "program" in rangerun. Does anyone have suggestions for a solution with rangestat?

[Jorrit Gosens]

When I read the line

How can I fix the interval in a way that it always contains at 21 time steps and once the following interval has less than 21 time steps, it deletes the last <20 observations. The result should be clean intervals of 21 time steps for a given isin.

All I can think of is simply

drop if has_px_change_sum<21

If this is not what you want, please explain a bit more, preferably with a data example that includes problematic observations, and a description of what you'd like done to them

[Felix Schrock]

Thanks Jerrit, Thanks for the feedback. I was thinking about a similiar approach earlier. But one line of code was at the wrong line. Your code is not fitting my data, as the has_px_change_sum can and must vary only due to the data, and not the interval length of the stata program. Now, I used the rangestat(count) to generate a "help variable" to count the interval length and then delete the too short intervals, independet of the sum of observations. Maybe not elegant, but it seems to work.

rangestat (sum) has_px_change , by(isin) interval(ret_day 0 20)
rangestat (count) ret_day, by(isin) interval(ret_day 0 20)
drop if ret_day_count != 21
drop ret_day_count