rangestat: An interval issue on Fridays

[Lisa Wilson]

I need to create a value-weighted average over a 2-day window, today and the day after.

The variable is "profit" and the weights are "lme". The resulting value-weighted variable is "INDEX_2d"

I used the following code:

gen double wm_num = profit * lme
rangestat (mean) mean=profit (sum) wm_denom=lme (sum) wm_numer=wm_num, int(date 0 1)  
gen double INDEX_2d = wm_numer/wm_denom

rangestat will measure the variable INDEX_2d here using 2 days if these are two subsequent days in my data. The problem I am facing is that the variable INDEX_2d will not be calculated for 2 days on most Fridays as I do not have observations on Saturdays in most years in my sample.

My question:
1-How can I adjust the code so that INDEX_2d calculated on Fridays will be measured over the values of "Friday and Monday in the next week" if there are no observations on Saturday?

An example of the data is below (note that day_of_week is the day of week variable created using dow() in Stata":

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(day_of_week profit lme)
4 .0022047890722101406  711.9156372038269
4 .0022047890722101406  543.3194490831374
4 .0022047890722101406  7341.637578514405
4 .0022047890722101406 398.12341038471277
4 .0022047890722101406 183.10185659065246
4 .0022047890722101406 20.565780587406152
4 .0022047890722101406 1266.9609187219248
2   .07601980404716957  3618.476323240204
2   .07601980404716957 3013.7244912120805
2   .07601980404716957  727.2898823281866
2   .07601980404716957 146.46254176231378
2   .07601980404716957 46549.321507796645
2   .07601980404716957   831.575259840698
2   .07601980404716957  613.6025419473262
2   .07601980404716957  766.2193993235778
2   .07601980404716957  9449.638920065481
2   .07601980404716957  2681.182487020269
3  .012440886544283988 175.48500537872314
3  .012440886544283988   31.1685009598732
3  .012440886544283988 10.159600287342073
3  .012440886544283988  51.47269101625443
3  .012440886544283988  431.4772886264036
3  .012440886544283988  10.58813966308594
3  .012440886544283988  7.682400300598147
3  .012440886544283988  392.2163397516251
3  .012440886544283988   90.3589097388649
3  .012440886544283988  999.4461630648038
3  .012440886544283988  100.0057583646776
3  .012440886544283988  29.64872960342882
3  .012440886544283988  6005.390086240077
3  .012440886544283988   2209.55931459961
3  .012440886544283988 143.94519865946768
3  .012440886544283988  8945.173672529752
3  .012440886544283988 185.72652167747492
3  .012440886544283988 290.90629080173494
3  .012440886544283988 109.52676824779337
4 -.061068619779393166  4352.040138778684
4 -.061068619779393166  5.688039960107801
4 -.061068619779393166  18.82560092010499
4 -.061068619779393166  97.34000205993652
4 -.061068619779393166 509.32002449035645
4 -.061068619779393166  51.61805962509152
4 -.061068619779393166  63796.92068481445
4 -.061068619779393166   298.885453196106
1 -.023447644189903524  71.01658210460664
1 -.023447644189903524  4.025501194065669
1 -.023447644189903524  90.68687862693787
1 -.023447644189903524 136.74045178251254
1 -.023447644189903524  55.48939808902742
1 -.023447644189903524  63.97050154209137
1 -.023447644189903524 222.71483975378032
1 -.023447644189903524  28.86320038490294
1 -.023447644189903524 15.527450164842605
1 -.023447644189903524 181.33850288391113
1 -.023447644189903524  48.07600021362305
1 -.023447644189903524 240.32055972404487
1 -.023447644189903524  15.86815940555573
1 -.023447644189903524  74.95950444030768
1 -.023447644189903524 433.98721329002365
1 -.023447644189903524  17610.71413934324
1 -.023447644189903524  32.81908027641293
2   -.2161305421145078   1959.90388602257
2   -.2161305421145078  3537.586578459479
2   -.2161305421145078  665.7957794902031
2   -.2161305421145078 1373.5815437182246
2   -.2161305421145078 492.47760495414695
2   -.2161305421145078 63.325501799583435
2   -.2161305421145078  7344.087586425478
2   -.2161305421145078 12.264480772991192
2   -.2161305421145078  60.80117278902435
2   -.2161305421145078  670.2494553552242
2   -.2161305421145078 177.12407360450743
2   -.2161305421145078  148.0038986526488
2   -.2161305421145078   9471.08540616883
2   -.2161305421145078 1085.5815153051008
2   -.2161305421145078  1768.925487185974
3  .015310256189018498 153806.11037007533
3  .015310256189018498  159.8470020561217
3  .015310256189018498 12129.189087381586
3  .015310256189018498   554.488140872345
3  .015310256189018498  62590.77861785889
3  .015310256189018498  36839.00133413728
3  .015310256189018498  418.5895964187621
3  .015310256189018498 1194.9026238739025
3  .015310256189018498  11130.27061756805
3  .015310256189018498   756.938714970398
3  .015310256189018498  1769.112088676833
3  .015310256189018498  874.5053886123642
3  .015310256189018498  86.91149568557739
3  .015310256189018498  64486.60707391659
3  .015310256189018498 26433.892579855165
3  .015310256189018498  5942.193603992462
3  .015310256189018498  82.04199955034255
3  .015310256189018498   515.430379104233
3  .015310256189018498 172.92013699302697
3  .015310256189018498  1063.088165829773
3  .015310256189018498 166.66747447372472
3  .015310256189018498  8031.518099713139
3  .015310256189018498 30087.831573486328
3  .015310256189018498 1251.4882093553751
end

[Nick Cox]

The "issue" is that you don't have a time or sequence variable fit for your purpose.

Your data example doesn't include date and I don't understand how your data is structured.

But you need to set up a variable that is a counter over days when you do have data. That could be done in principle with a business calendar as supported by Stata, but if you have data on Saturdays in some years but not others that could be complicated.

In a simple example we might have gaps or missing values or both like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input float(date whatever)
22982 .
22984 12
22985 14
22986 13
22987 12
22988 13 
22989 .
22990 15
22991 16 
end
format %td date

sort date 
gen counter = cond(missing(whatever), ., sum(!missing(whatever)))

list, sep(0)

     +--------------------------------+
     |      date   whatever   counter |
     |--------------------------------|
  1. | 03dec2022          .         . |
  2. | 05dec2022         12         1 |
  3. | 06dec2022         14         2 |
  4. | 07dec2022         13         3 |
  5. | 08dec2022         12         4 |
  6. | 09dec2022         13         5 |
  7. | 10dec2022          .         . |
  8. | 11dec2022         15         6 |
  9. | 12dec2022         16         7 |
     +--------------------------------+

The counter blithely ignores gaps (data are absent) and missing values and gives you a handle for getting the next day with data and so forth.

If you have multiple observations for each day, the extension is I think just (assuming again sort date)

gen counter = cond(missing(whatever), ., sum(!missing(whatever) & date != date[_n-1]))

[Lisa Wilson]

I do have several observations per day (which are used to calculate the value-weighted average over two days). There are gaps in the data as not every day there will be firms announcing profits.

I am not sure if creating a business calendar date would solve the problem, as in this case, the 2-day average will contain observations that are not on subsequent days due to the gaps in the data. On the other hand, rangestat is not creating a problem now with gaps as it does include only one observation in the window when the next day is a gap which is what I want, but it has only a problem on Fridays as it does not include Mondays in the 2-day window of Fridays.

I include the date variable now and I look forward to getting more help, thank you. Note: gvkey is the firm identifier.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double date long gvkey double(day_of_week profit lme)
4645  5071 2    .008553334208822174 1007.3752477169037
4645  2434 2    .006736078562634814 29.900749683380127
4645  1767 2    .013558047234039267  32.31474930047989
4645  7840 2     .03918512445794397 1321.6162633895874
4645  5063 2    .015717457397194063 168.88387175367825
4645  4475 2    .008249621331915763  174.5697522163391
4648  6486 5 -.00014569620662492844   53.6685004234314
4648  7773 5  -.0029852514160529676  389.4827415943146
4648  7838 5     .07099946053075414  38.04750108718872
4648  9000 5     .08125537540497077  8.568750321865082
4648  6646 5     .01404209192593126  67.54674857854843
4648  6513 5    .017104601243681967 34.232624500989914
4651  9761 1     .01150548954583995          766.40625
4651  5261 1     -.0062489720730061 134.52600288391113
4651 10060 1    .005889280437825263 17.820249319076538
4651  2102 1    .008890100015396524 1420.7999682426453
4651  9295 1    .005042121054182163   66.6425022482872
4658  3494 1    .001977786652428535 19.002374969422817
4658  5781 1    -.02491301551648868 22.978999853134155
4658  8479 1    .007235781944887577 1940.2803263664246
4658  9359 1   .0007289330027182721  891.1289687156677
4658  8359 1    .006449645744208382  54.11437377333641
4662  5840 5    .014203686362630263  5.640749864280224
4662  2562 5    .017694810477147774  740.2187585830688
4662  6131 5    .019280978931951187 12.359999656677246
4662  4290 5   -.022082019492787038  1.823250062763691
4662  5574 5   -.011638591510706965 15.797749280929565
4662 11535 5   .0011479495505077072  693.4486512184521
4662  3613 5    .012020362253443766 104.13600540161133
4666  9381 2    .023999933774012525  575.8994863033295
4666  3497 2    .035919590376561186  94.96700429916382
4666  5794 2    .002576931822912932 194.54900747537613
4666  5643 2    .012665895591723788 325.51998138427734
4666  6104 2     .02023151218699857 1659.2267227172852
4666 10820 2     .03780654113447124 11.000749632716179
4666  9514 2    .021678023940146042  471.0434808731079
4666  1218 2    .036754005440941206  6.362250164151192
4666 11008 2    .002030947718276587  8.086499691009521
4666  8215 2     .00180718359678201  692.5949907302856
4666  8219 2   .0030385596551300203  57.41999834775925
4666  1300 2    .011950583955880596  812.9019927978516
4666  6920 2      .0053026706119143 167.79649543762207
4666  9670 2   -.008421322859921617 313.17211812734604
4667 13961 3   .0029496623861764606 234.18336975574493
4667  6701 3    .002088344898904821  433.7340009212494
4667  6977 3    .002781789952028577 123.87200164794922
4667  6066 3    .022842063272725095 45464.888253416866
4667 10301 3     .00744617738926273  886.7949962615967
4667  1812 3    .010152935971084586  45.03712600469589
4667  2255 3     .01320475806771521 1207.4549922943115
4667 11436 3   .0041655990062723575 4434.4841384887695
4667  3883 3     .06072051904321694 53.969998598098755
4667  7595 3    .006914432954956884   8.10299988090992
4667 10541 3   -.013122721504470835 102.68876486921727
4667  5235 3   -.006401344521047081 145.41299653053284
4667  4218 3    .004217251523959407  181.6978806257248
4667  2529 3    .006054733064497763 1478.2424783706665
4667  9943 3    .002844318565968105 14.924249544739723
4667  4145 3  .00016045736119834925              98.75
4667  8972 3   .0019043167738808423  602.4814920425415
4668  6195 4     .00608802243031682  177.3105046749115
4668  2444 4     .01390812479449331  971.2727074623108
4668  4470 4    .010716504418503501  375.2725148200989
4668  3619 4    .007383739140611017  461.4692635536194
4668  4545 4   -.001832497559175247  48.91250205039978
4668  3480 4      .0946312422029165 1050.2510061264038
4668  1638 4   .0007084523483362828 226.42724704742432
4668  4194 4     .03405541062182312  21392.41290473938
4668 11464 4    .055193886419224215 23.966625452041626
4668  1221 4   -.006455517764288553  360.0640106201172
4668  8657 4    -.03868863422454723  4187.737655639648
4668 10855 4    .027774835703981187  569.7230002880096
4668  5754 4     .01958339874230098   602.474393248558
4668  7248 4     .01908822439916172  29.33124914765358
4668  7687 4    .009839458237917542  4.697000026702881
4668  3170 4    .008671548067309097 1061.6182166337967
4668  9114 4    .019090568922826016  284.2454881668091
4668  9113 4  -.0071228709342835705 3030.3031158447266
4668  6435 4    .022965975239203903   741.571855545044
4668 10703 4    .004109801758351824   16.4474995136261
4669 10007 5    .027371504036726627 11.088124953210354
4669  4186 5     .01620449586779198 11.359999746084213
4669  1608 5    .034638301049813046 1207.0660381317139
4669 10676 5   -.024480867904466717  48.65625128149986
4669  6730 5    .042699515039149115   4550.12926864624
4669  6670 5    .022884731704759506  130.6622496843338
4669  8692 5    .012171328890302183  206.1101240515709
4669  9965 5    .008483084177262153  866.6267776489258
4669  1414 5    .000427635796554444   548.000391960144
4669  4628 5  .00001881413896739788   73.2686265707016
4669  8345 5  -.0008873132647786467 2.4042500369250774
4669  4988 5    .004759744397544378  801.6909748153921
4669  4707 5    .018627452555870592  7.029999911785126
4669  9216 5    .015014648139072065 22.499999403953552
4669  8889 5    .009312608966016623 2512.9575061798096
4669  1150 5     .02384970176085895  19.65399932861328
4669  6495 5    .010315014926325184 199.09174168109894
4669  9204 5     .00231419665269935 222.65125393867493
4669  5492 5    .004205981954381782   344.921489238739
4672  4600 1    .004299056185619604  162.9979968070984
end
format %td date

[Nick Cox]

Sorry, but I don't know why you seek more help as I believe that my code in #3 gives you a direction. It is in effect an attempt to create a business calendar directly from the data, rather than from rules about weekends, holidays and so forth.

[Lisa Wilson]

Sorry Nick, I am not sure if I followed the thought in #2 but I tried to go around the issue now building on your suggestion of a business calendar:

gen dayweek=dow(date)

** First: create the value-weighted aggregate based on the date variable that has gaps:

gen double wm_num = profit * lme
rangestat (mean) mean=profit (sum) wm_denom=lme (sum) wm_numer=wm_num, int(date 0 1)  
gen double vwINDEX_2d = wm_numer/wm_denom

drop wm_num mean wm_denom wm_numer


** Second: create the value-weighted aggregate based on a business calendar date that handles the gaps in data:

bcal create buscal, from(date)  maxgap(20) generate (business_date) replace

gen double wm_num = profit * lme
rangestat (mean) mean=profit (sum) wm_denom=lme (sum) wm_numer=wm_num, int(business_date 0 1)  
gen double temp = wm_numer/wm_denom

drop wm_num mean wm_denom wm_numer


**Third: create the time series data:

duplicates drop date vwINDEX_2d, force

sort date
tsset date


** Fourth: create the final index variable
replace vwINDEX_2d=temp if dayweek==5 & dayweek[_n+1]==1 & date[_n+1]==date+3 // to ensure that the next obs is from Monday next week

replace vwINDEX_2d=temp if dayweek==5 & dayweek[_n+1]==6 & date[_n+1]==date+1 // to take the saturday announcement

replace vwINDEX_2d=temp if dayweek==5 & dayweek[_n+1]==0 & date[_n+1]==date+2 // to take the sundy annoncement

sort date
tsset date

Does this make sense now? It seems to be solving the problem but it will be very helpful if I hear from you as well, thanks.



Here is the resulting time series data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(vwINDEX_2d temp date dayweek)
   .023819486860692986    .020714081111072324 4645 2
   .008276353315079678    .008276353315079678 4648 5
   .008747941014734665    .006665615849158878 4651 1
   .004954096903788404    .006608251463910178 4658 1
    .00968596111821619    .013058154211616917 4662 5
   .019203018961994653    .019203018961994653 4666 2
   .019962905930565548    .019962905930565548 4667 3
    .02122326373492468     .02122326373492468 4668 4
   .018880957854009523    .018880957854009523 4669 5
   .013829289660204601    .013829289660204601 4672 1
   .012048459895564313    .012048459895564313 4673 2
   .010628287163097567    .010628287163097567 4674 3
   .010253277681809897    .010253277681809897 4675 4
   .015298024663255866    .015298024663255866 4676 5
   .009738519571248161    .009738519571248161 4679 1
   .008214972513844215    .008214972513844215 4680 2
   .009961057137665133    .009961057137665133 4681 3
   .007120327265574435    .007120327265574435 4682 4
-.00024152857424430016 -.00024152857424430016 4683 5
-.00035339587893470724 -.00035339587893470724 4686 1
   .016117216334602315    .016117216334602315 4687 2
    .02030475071897144     .02030475071897144 4688 3
   .024445526923828814    .024445526923828814 4689 4
   .021944419952852068    .021944419952852068 4690 5
   .014082091647454366    .014082091647454366 4693 1
   .011967512450016954    .011967512450016954 4694 2
   .011279525730693886    .011279525730693886 4695 3
   .006312583540680615    .006312583540680615 4696 4
 -.0023495725632511626  -.0023495725632511626 4697 5
  -.004949594657646859   -.004949594657646859 4700 1
   -.00401458898475398    -.00401458898475398 4701 2
   -.00300700512961293    -.00300700512961293 4702 3
  .0004976083764604453   .0004976083764604453 4703 4
   .006718022768525806    .006718022768525806 4704 5
   .011985612473767232    .011985612473767232 4707 1
   .026451804825207433    .026451804825207433 4708 2
   .016970558520386068   -.012197632388398353 4709 3
   -.03723072295472029    -.03723072295472029 4714 1
    .03404766338533347     .03404766338533347 4715 2
   .012822679792160984    .012822679792160984 4716 3
   .018288907446589602    .018288907446589602 4717 4
  -.020074022788959763   -.020074022788959763 4718 5
  -.024428084567619572   -.024428084567619572 4721 1
    .02621746666702391     .02621746666702391 4722 2
   .020632020003452874    .020632020003452874 4723 3
   .012034304164130786    .009895965441103865 4724 4
 -.0015420839218065192  -.0011186700577465944 4728 1
   .004122733947621639    .004122733947621639 4730 3
-.00036551792416704643 -.00036551792416704643 4731 4
  -.010394306953046599   -.010394306953046599 4732 5
   .017383944400439062    .017383944400439062 4735 1
   .024018319069688866    .024018319069688866 4736 2
   .024605008945119883    .024605008945119883 4737 3
  .0012387331882526614   .0012387331882526614 4738 4
  -.006171018981753563   .0029551624626389028 4739 5
   .016212911045692148    .013234508738929479 4743 2
   .008456108745511335    .015160327816023838 4758 3
   .014967032979253782    .014967032979253782 4763 1
   .016997794401046082    .016997794401046082 4764 2
    .01651889576354243     .01651889576354243 4765 3
   .013838099655466762    .013838099655466762 4766 4
   .016085346859910886    .016085346859910886 4767 5
   .014292630151881773    .014292630151881773 4770 1
   .011308746893378444    .011308746893378444 4771 2
   .006705056302205241    .010408732538424543 4772 3
   .012097432970625878    .012097432970625878 4774 5
   .007725715495237827    .007725715495237827 4777 1
   .006792901460185686    .006792901460185686 4778 2
   .011934893156558008    .011934893156558008 4779 3
   .022957317320406973    .022957317320406973 4780 4
   .029092913632837544    .029092913632837544 4781 5
   .017746387609553067    .017746387609553067 4784 1
   .013374049221381409    .013374049221381409 4785 2
    .01684848461124873     .01684848461124873 4786 3
    .01089630081791271     .01089630081791271 4787 4
   .008974546359575314    .008974546359575314 4788 5
   .011385224692259667    .011385224692259667 4791 1
   .012087061893277939    .012087061893277939 4792 2
    .01660657879564204     .01660657879564204 4793 3
    .01735919901849485     .01735919901849485 4794 4
   .007900688207654799    .012322633964236114 4795 5
   .009388671297382595    .009388671297382595 4799 2
   .028483516980363617    .028483516980363617 4800 3
     .0359481457736851      .0359481457736851 4801 4
  -.009102280367165592   -.009102280367165592 4802 5
   .009669649786366795    .009669649786366795 4805 1
   .017632692485699767    .017632692485699767 4806 2
   .014842651751006384    .014842651751006384 4807 3
   .025090001163172608    .025090001163172608 4808 4
     .0124675381101691      .0124675381101691 4809 5
   .012568063814348357    .012568063814348357 4812 1
     .0145975265351455      .0145975265351455 4813 2
   .008865607094745718    .008865607094745718 4814 3
    .00715505036437374     .00715505036437374 4815 4
   .010883675610529317    .010883675610529317 4816 5
   .010257409707454623    .010257409707454623 4819 1
    .01045381827277273     .01045381827277273 4820 2
   .009786372923407134    .009786372923407134 4821 3
   .006695423098346864    .006695423098346864 4822 4
   .009680966105460811    .009680966105460811 4823 5
end
format %td date

[Nick Cox]

I bow out here because I don't use business calendars myself and so can't advise on their detailed application. From my perspective my approach in #2 was simpler but naturally you can follow your own path.

[Lisa Wilson]

Thanks a lot Nick.

OK then would really appreciate if someone can advise further!

I look forward to getting more help

[Lisa Wilson]

This has been a while and I am really keen to get some help.
I thought to bring this up.
look forward to getting a response

[Lisa Wilson]

This has been a while and I am really keen to get some help.
I thought to bring this up.
look forward to a response