2

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
 .....
Nick Cox
  • 35,529
  • 6
  • 31
  • 47
Starelite
  • 43
  • 6

2 Answers2

1

My approach would be

  1. generate a new data set shifted by five minutes
  2. append this shifter data set
  3. find closest before and after observations to your five minute delta
  4. use some criteria to pick the better of these two values

You 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.

clear
version 11.2
set seed 2001

* generate some data
set obs 100000
generate double dt = ///
    tc(02dec2012 09:00:00.000) + 1000*_n + int(100*rnormal())
format dt %tcDDmonCCYY_HH:MM:SS.sss
sort dt
generate midpt = 100
replace midpt = ///
    round(midpt[_n - 1] + 0.1*rnormal(), 0.005) if (_n != 1)

* add back future midpts
preserve
tempfile future
rename midpt fmidpt
rename dt fdt
generate double dt = fdt - tc(00:05:00.000)
save `future'
restore
append using `future'

* generate midpoints before and after 5 minutes in the future
sort dt
foreach v of varlist fdt fmidpt {
    clonevar `v'_b = `v'
    replace `v'_b = `v'_b[_n - 1] if missing(`v'_b)
}

gsort -dt
foreach v of varlist fdt fmidpt {
    clonevar `v'_a = `v'
    replace `v'_a = `v'_a[_n - 1] if missing(`v'_a)
}

format fdt* %tcDDmonCCYY_HH:MM:SS.sss

* use some algorithm to pick correct value
sort dt    
generate choose_b = ///
    ((dt + tc(00:05:00.000)) - fdt_b) < (fdt_a - (dt + tc(00:05:00.000))) 
generate fdt_c = cond(choose_b, fdt_b, fdt_a)
generate fmidpt_c = cond(choose_b, fmidpt_b, fmidpt_a)
format fdt_c %tcDDmonCCYY_HH:MM:SS.sss
Richard Herron
  • 9,760
  • 12
  • 69
  • 116
  • Thank you for your reply Richard. Some trades/quotes happen at the "same" time, they have exactly the same time in miliseconds. However, the dataset is in chronological order, see the small piece of dataset in the question. I wrote some very time consuming code to process the data per observation. I'll post it below, maybe you have some comments to speed it all up a bit. – Starelite Jan 17 '13 at 07:15
1
// Construct a variable to look for in the dataset
gen double midpoint_5 = (datetime + 5*60000)    
format midpoint_5 %tcNN/DD/CCYY_HH:MM:SS.sss

// will contain the closest observation number and midpoint 5 minutes a head
gen _t = .
gen double midpoint_at5 = . 

// How many observations in the sample?
local N = _N

// We will use these variables to skip some observations in the loop
egen obs_in_minute = count(minutes_filter), by(minutes_filter)
egen max_obs_in_minute = max(obs_in_minute)
set more off 

// For each observation
forvalues i = 1/`N' {

    // If it is a trade
    if type[`i'] == "Trade" {

        // Set the time to lookup in the data
        local lookup = midpoint_5[`i']

        // The time should be between the min and max(*5)
        local min = `i' + obs_in_minute[`i'] // this might cause errors
        local max = `i' + max_obs_in_minute[`i']*5

        // For each of these observations
        forvalues j = `min'/`max' {

            // Check if the lookup date is smaller than the datetime of the observation
            if `lookup' < datetime[`j'] {

                // Set the observation ID at the lookup ID 1 observation before
                quietly replace _t = `j'-1 in `i'
                // Set the midpoint at the lookup ID 1 observation before
                quietly replace midpoint_at5 = midpoint[`j'-1] in `i'

                // We have found the closest 5th min ahead... now stop loop and continue to next observation.
                continue, break
            }
        }

        // This is to indicate where we are in the loop
        display "`i'/`N'"
    }
}
Starelite
  • 43
  • 6
  • I imagine this is very, very slow. I _think_ this should provide roughly the same results as my code, but it looks like yours always take the first midpoint after five minutes, while mine finds the closest to five minutes, before or after (depending on how deep is your book, this could make a difference). Aside from speed and taking first after versus closest, the question I have is how to select among several different midpoints five minutes in the future -- it seems you need some additional criteria to make this repeatable (e.g., highest volume). – Richard Herron Jan 17 '13 at 09:57
  • With the time consuming method, the loop will select the previous observations and quit if he finds an observation with a higher time. This will cancel out any problems with duplicates and you will always get the midpoint closest to the time we need. It is taking around 20 minutes to process 2 million observations. We will manage this way for now, as we only need to proces 10 shares. Thanks a lot for your input! – Starelite Jan 17 '13 at 11:08
  • But if there are five midpoints at a given time, then what is the "closest"? For repeat-ability, maybe you should take the highest volume? In any case, I think yours takes the "first after", which isn't necessarily the "closest". I think my code should be on the order of seconds, and about half that if you do want "first after" instead of "closest" (requires modification). – Richard Herron Jan 17 '13 at 11:31