0

Given the following data spec:

q) ob
src sym  price   size  side   time                         
---------------------------------------------------------- 
 1   2    930700  439   -1    2020.06.20D00:00:00.053000000
 1   2    930708  444   -1    2020.06.20D00:00:00.054000000
 1   2    930739  817   1     2020.06.20D00:00:00.055000000
 1   2    930739  0     1     2020.06.20D00:00:00.056000000
...

whereby src represents the exchange, sym the symbol etc. where each row represents the instant state of the size (outstanding quantity of orders) at each price.

What is the best mechanism for which one might reconstruct the historic orderbook within a given window of for example 1000 ticks (per side) where the tick size is 1? (bottom bid price = top bid price - 1000;bottom ask price = top ask price + 1000) etc.

I have thought of doing the following, but have relented on the precursor that there should be a more formal/efficient mechanism of achieving this, which I hope to garner from your advice. The updates need not be aggregated in real time i.e. per event, and the following example assumes an aggregation window of 10 seconds.

// doesn't account for deleted levels (where size = 0) inefficient?
x:select last size by `src`sym`side`price 10 xbar `second$time from ob; // window by time
x:update fills size by `src`sym`side`price from x; // forward fill the sizes
x:delete from x where size=0;
x:delete from (select last size, last price, mnp:min price, mxp:max price by `src`sym`side`time) where $[side>0;price<(mxp-1000);price>(mnp+1000)]; // delete out of bounds asks and bids

This multi step approach seems inefficient, and as such I was hoping to gain insight from you on how to do this more effectively. I look forward to your responses. Thanks in advance.

James
  • 1,260
  • 13
  • 25

1 Answers1

3

I hope I have understood you correctly, but is this the kind of thing you are after?

select last fills size by src,sym,side,price,10 xbar `second$time from tab where size<>0,?[side=-1;price<(min price)+1000;price>(max price)-1000]

Here we are just using the where clause to remove the unwanted lines instead of separate delete statements.

matthewclark
  • 144
  • 3