Rolling five minutes (panel data)

[Olena Onishchenko]

Deadt Stata Users

I am seeking your advice to code rolling 5 minutes window for panel data:

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 code double tradetime float midprice
"0015  " 32402000 9.139999
"0015  " 36168000     9.16
"0015  " 40098000    9.145
"0015  " 41703000     9.16
"0015  " 44210000    9.145
"0015  " 44330000     9.16
"0050  " 32400000   58.725
"0050  " 32476000   58.725
"0050  " 32536000   58.725
"0050  " 32581000   58.725
"0050  " 32611000   58.725
"0050  " 32626000   58.725
"0050  " 32641000   58.725
"0050  " 32686000   58.725
"0050  " 32731000   58.725
"0050  " 32761000   58.725
"0050  " 32776000   58.725
"0050  " 32806000   58.725
"0050  " 32821000   58.725
"0050  " 32851000   58.725
"0050  " 32866000   58.725
"0050  " 32881000   58.725
"0050  " 32896000   58.725
"0050  " 32911000   58.725
"0050  " 32926000   58.675
"0050  " 32956000   58.675
"9958  " 32417000   14.675
"9958  " 32442000   14.625
"9958  " 32459000   14.675
"9958  " 32486000   14.675
"9958  " 32500000     14.7
"9958  " 32513000     14.7
"9958  " 32595000   14.725
"9958  " 32607000   14.725
"9958  " 32636000   14.725
"9958  " 32665000   14.725
"9958  " 32680000   14.675
"9958  " 32708000   14.675
"9958  " 32759000   14.725
"9958  " 32772000   14.725
"9958  " 32785000   14.725
"9958  " 32830000   14.725
"9958  " 32846000   14.725
"9958  " 32860000   14.725
"9958  " 32894000   14.725
"9958  " 32905000   14.725
"9958  " 32918000   14.675
"9958  " 32931000   14.725
"9958  " 32961000   14.725
"9958  " 33072000   14.725
"9958  " 33087000   14.725
"9958  " 33102000   14.725
"9958  " 33130000   14.675
"9958  " 33192000   14.675
"9958  " 33221000   14.675
"9958  " 33237000   14.675
"9958  " 33282000   14.675
"9958  " 33357000   14.675
"9958  " 33373000   14.725
"9958  " 33387000     14.7
"9958  " 33418000   14.675
"9958  " 33432000   14.725
"9958  " 33523000   14.725
"9958  " 33552000   14.725
"9958  " 33670000   14.725
"9958  " 33715000   14.725
"9958  " 33731000   14.725
"9958  " 33745000   14.725
"9958  " 33777000   14.725
"9958  " 33824000   14.725
"9958  " 33882000   14.725
"9958  " 33912000   14.725
"9958  " 33941000   14.725
"9958  " 33957000   14.725
"9958  " 33972000   14.725
"9958  " 33985000   14.675
"9958  " 34021000   14.675
"9958  " 34060000   14.725
"9958  " 34073000   14.725
"9958  " 34137000   14.725
"9958  " 34240000   14.725
"9958  " 34260000   14.725
"9958  " 34270000   14.675
"9958  " 34287000   14.725
"9958  " 34336000   14.725
"9958  " 34345000   14.675
"9958  " 34390000   14.675
"9958  " 34420000   14.675
"9958  " 34450000   14.675
"9958  " 34468000   14.725
"9958  " 34480000   14.725
"9958  " 34497000   14.675
"9958  " 34540000   14.675
"9958  " 34571000   14.675
"9958  " 34634000   14.675
"9958  " 34658000   14.675
"9958  " 34676000   14.675
"9958  " 34690000   14.675
"9958  " 34707000   14.675
"9958  " 34735000   14.675
end
format %tc_HH:MM:SS tradetime

------------------ copy up to and including the previous line ------------------

I need to calculate midprice for 5-mins rolling window for each code. I have tried `~rangestat~ function but I am not sure how to specify the interval of 5 minutes for stata time variable.

rangestat (first) midprice, interval(tradetime 0 5) by(code)

Any idea how to make it work?

Thanks.

Regards,
Olena

[Clyde Schechter]

The key here is that Stata clock variables give times in milliseconds. So five minutes is the same as 5*60*1000 milliseconds. But you don't have to think that through, as Stata has a convenience function -msofminutes()- that does that calculation for you:

rangestat (first) midprice, interval(tradetime 0 `=msofminutes(5)') by(code)

Note: This code produces a forward rolling window of five minutes, and uses the initial (in current sort order) value of midprice as the outcome. And since the first value of anything in the next five minutes is just the current value, this is just an overly complicated way of copying the tradetime variable.

Perhaps you really want a backwards rolling window (i.e. the first price in the preceding five minutes). That code would be:

rangestat (first) midprice, interval(tradetime  -`=msofminutes(5)' 0) by(code)

And, of course if you wanted, instead of the first price in the window, the mean or median price in the window, you would just change -(first)- to -(mean)- or -(median)-, respectively.

[Olena Onishchenko]

Clyde, thanks it works. I needed forward rollling 5 minutes midprice.

Regards,
Olena

[Nick Cox]

This is just to document that rangestat is a command (*) from SSC. Everyone is asked to make clear the provenance of community-contributed commands they refer to.

(*) not a function!

[Olena Onishchenko]

Clyde

I have been running the following code : rangestat (last) midprice, interval(tradetime 0 `=msofminutes(5)') by(code)

It generated the following output:

Unfortunately, it does not strictly take a price 5 minutes later. For example, the 9:30:06 midprice is 2.29; the 5 minutes later price (09:35:10) must be 2.3. But the formula retrieved 2.34 instead, which is, the last value within the 5 mins interval at 09:33:43.

How shall I adjust the code to calculate the 5 minutes rolling forward midprice strictly?

Thanks.

[Clyde Schechter]

the 9:30:06 midprice is 2.29; the 5 minutes later price (09:35:10) must be 2.3.

But 9:35:10 is not within 5 minutes of 9:30:06, it comes 4 seconds too late! So Stata is doing exactly what the command asks: finding the last price that falls within five minutes. Evidently, you want something else that you refer to as the "5 minutes rolling forward," but I don't know what you mean by that, if not what you are currently getting. Do you really mean a 6 minutes rolling forward window, or a 5 minutes 30 seconds rolling forward window, to capture prices occurring a little more than 5 minutes forward? Please explain.

[Olena Onishchenko]

Clyde

I need rolling midprice strictly after 5 mins 00 seconds.

Thanks.

Regards,
Olena

[Clyde Schechter]

If you can loosen it to allow exactly at or after 5 mins 00 seconds, then it can be done as:

rangestat (first) midprice, interval(tradetime `=msofminutes(5)' .) by(code)

If you cannot loosen it to allow exactly at, and it must be strictly after 5 mins 00 seconds, then it would be:

rangestat (first) midprice, interval(tradetime `=msofminutes(5)'+1 .) by(code)

[Olena Onishchenko]

Clyde

Thanks. I have tired the code and got an error:

rangestat (first) midprice, interval(tradetime `=msofminutes(5)'+1 .) by(code)

was expecting a numeric variable, a number, or a system missing value for the interval low: 300000+1
r(198);

[Clyde Schechter]

Oh, yes, sorry:

rangestat (first) midprice, interval(tradetime ``=msofminutes(5)'+1' .) by(code)

[Olena Onishchenko]

Clyde

Still does not work same error:

rangestat (first) midprice, interval(tradetime ``=msofminutes(5)'+1' .) by(code)

was expecting a numeric variable, a number, or a system missing value for the interval high:
r(198);

[Clyde Schechter]

My apologies. This time I've tested it:

rangestat (first) midprice, interval(tradetime `=msofminutes(5)+1' .) by(code)