Rolling estimation with intra-day data

[Zain Mian]

Hi,
I posted a similar problem earlier in the day but I felt it was a bit confusing and unfortunately I cannot delete it, so I am pasting the revised data again. Apologies for the duplication. I have the following sample of intra-day data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id long date double(time price)
1 21823           56100000    102.387
1 21824           33596000    102.288
1 21824           47710000    102.397
1 21825           50009000    102.742
1 21825           60499000 102.702736
1 21826           12119000    102.582
1 21826           32789000    102.565
1 21826           43951000    102.609
1 21826           54412000    102.617
1 21826           54412000  102.55451
1 21829           37797000    102.545
1 21829           45684000    102.522
1 21830           44628000    102.536
1 21831           35067000    102.645
1 21831           37511000    102.542
1 21831           48165000    102.174
1 21831           48165000    102.474
1 21831           64399000   102.4974
1 21831           64399000   102.4974
1 21832           36633000    102.355
1 21832           57553000    102.299
1 21832           57995000 102.338645
1 21833           40237000    102.319
1 21836           57954000 102.080452
1 21837           30188000    102.332
1 21837 32455999.999999996    102.268
1 21837           44701000    102.073
1 21838 32155999.999999996    102.259
1 21838           33861000    102.403
1 21838           33861000    102.403
1 21838           33861000    102.403
1 21839           40823000    102.237
1 21839           40823000    102.237
1 21840           10127000    102.424
1 21840           28777000    102.423
1 21840           42387000    102.453
1 21840           42387000    102.453
1 21843           36383000     102.36
1 21843           42529000    102.327
1 21843           44051000     102.39
1 21843           46214000    102.356
1 21843           46214000    102.206
1 21843           46214000    102.206
1 21843           47160000    102.287
1 21843           47160000    102.137
1 21843           47160000    102.137
1 21843           54017000    102.389
1 21844           42487000    102.378
1 21844           51397000    102.388
1 21844           56163000    102.403
1 21845           31133000    102.336
1 21845           39218000     102.36
1 21845           40921000    102.353
1 21845           57404000    102.357
1 21846           33927000    102.367
1 21846           33927000 102.315821
1 21846           37407000    102.452
1 21846           37795000    102.537
1 21846           40807000    102.437
1 21846           40807000    102.437
1 21846           43021000    102.497
1 21847           21949000    102.467
1 21847           29852000    102.238
1 21850 32089000.000000004    102.146
1 21850           33233000    102.087
1 21851           35148000    102.856
1 21851           35149000    102.189
1 21851           37061000    102.181
1 21851           41673000    102.189
1 21851           63717000    102.268
1 21852 31804000.000000004    102.134
1 21852           31821000    102.095
1 21852           38297000    102.235
1 21852           38310000    102.235
1 21852           40491000    102.155
1 21852           55524000    102.261
1 21853           34598000    102.172
1 21853           34598000    102.272
1 21853           42468000    102.357
1 21854           49875000  102.38718
1 21854           49875000  102.35594
1 21857           35252000    102.308
1 21857           53270000    102.244
1 21858           36819000    102.148
1 21858           40646000    102.207
1 21858           55883000    102.214
1 21858           59147000 102.186285
1 21859           36765000    102.232
1 21859           37575000    102.278
1 21859           39973000    102.165
1 21859           51312000    102.197
1 21860           40842000     102.07
1 21861           54688000   101.9095
1 21861           54688000   102.0095
1 21865           36402000    102.038
1 21865           43839000    101.891
1 21865           43839000    101.954
1 21865           43978000    101.948
1 21865           52278000    102.017
1 21865           58523000    101.968
end
format %td date
format %tcHH:MM:SS time

In the actual data I have many more ids. I would like to do a rolling estimation (e.g. 'total of price variable') using a window of e.g. 10 days by id. I would like to perform this estimation by id and day i.e. for every day I would like to estimate the total of price over next 10 days. Starting on with 1st October do the 'total' for the next 10 days i.e. till 10th. Then from 2nd October do the total over next 10 days and so on. Since the data is intra-day and within a day I have more than one observation, I am not sure how to properly define a window so that it rolls over the 10 days. Would appreciate if someone could help in finding the solution to the problem. Thank You.

[Attaullah Shah]

If I have correctly understood your description of the problem, then the following code will do the task. I am using version 5.0 of asrol, that has a flexible window. This version will be soon sent to the SSC. Fore more on asrol, you can find details here.

The new version of asdoc can be installed from my site. Copy and paste the following line in Stata and press enter.

net install asrol, from(http://fintechprofessor.com) replace

Please note that the above line has to be copied in full. After installation of the new version, then restart Stata.

bys id : asrol price, window(date 0 9) stat(sum) gen(total)

And the result

list in 1/20

    +---------------------------------------------------+
     | id        date       time       price       total |
     |---------------------------------------------------|
  1. |  1   01oct2019   15:35:00     102.387   2254.8697 |
  2. |  1   02oct2019   09:19:56     102.288   2254.8017 |
  3. |  1   02oct2019   13:15:10     102.397   2254.8017 |
  4. |  1   03oct2019   13:53:29     102.742   2050.1167 |
  5. |  1   03oct2019   16:48:19   102.70274   2050.1167 |
     |---------------------------------------------------|
  6. |  1   04oct2019   03:21:59     102.582    1844.672 |
  7. |  1   04oct2019   09:06:29     102.565    1844.672 |
  8. |  1   04oct2019   12:12:31     102.609    1844.672 |
  9. |  1   04oct2019   15:06:52     102.617    1844.672 |
 10. |  1   04oct2019   15:06:52   102.55451    1844.672 |
     |---------------------------------------------------|
 11. |  1   07oct2019   10:29:57     102.545   2149.9659 |
 12. |  1   07oct2019   12:41:24     102.522   2149.9659 |
 13. |  1   08oct2019   12:23:48     102.536   2149.3729 |
 14. |  1   09oct2019   09:44:27     102.645   2456.5899 |
 15. |  1   09oct2019   10:25:11     102.542   2456.5899 |
     |---------------------------------------------------|
 16. |  1   09oct2019   13:22:45     102.174   2456.5899 |
 17. |  1   09oct2019   13:22:45     102.474   2456.5899 |
 18. |  1   09oct2019   17:53:19    102.4974   2456.5899 |
 19. |  1   09oct2019   17:53:19    102.4974   2456.5899 |
 20. |  1   10oct2019   10:10:33     102.355   1841.7601 |
     +---------------------------------------------------+

.