I got high frequency data from a limit order book in Stata. Time does not have a regular interval, and some observations are at the same time (in milliseconds). For each observation I need to get the midpoint 5 minutes later in a separate column. So for observation 1 the midpoint would be 10.49, because the last midpoint closest to 09:05:02.579 would be 10.49.
How to do this in Stata?
datetime midpoint
12/02/2012 09:00:02.579 10.5125
12/02/2012 09:00:03.471 10.5125
12/02/2012 09:00:03.471 10.5125
12/02/2012 09:00:03.471 10.51
12/02/2012 09:00:03.471 10.51
12/02/2012 09:00:03.549 10.505
12/02/2012 09:00:03.549 10.5075
......
12/02/2012 09:04:59.785 10.495
12/02/2012 09:05:00.829 10.4925
12/02/2012 09:05:01.209 10.49
12/02/2012 09:05:03.057 10.4875
12/02/2012 09:05:05.055 10.485
.....
My approach would be
append
this shifter data setYou specified closest, but you might want to add some other criteria depending on your book. Also, you mentioned more than one value at a given ms tick, but without more information I'm not sure how to handle that. Do you want to combine those midpoints first? Or are they different stocks?
Here's some code that implements the basics of the approach above.