Hi all, for historical data, say I use “aj” to join the trade and quote data, then I have the current quote per trade happening. Then if I like to see when is the last change of quote price, or when is the next change to quote price, what I know is loop through all records and then select max / min time with quote price difference. Just wonder if there better way than looping it?
forgive my ignorance, but would your joined data look something like:
q)(time:01:01:01+til 6;quote:1.1 1.1 1.1 1.1 1.2 1.2;size:0N 0N 1 0N 0N 0N;price:0n 0n 1.1 0n 0n 0n)
time quote size price
-------------------------
01:01:01 1.1
01:01:02 1.1
01:01:03 1.1 1 1.1
01:01:04 1.1
01:01:05 1.2
01:01:06 1.2
?
ta, jack
Hi Carfield,
You could use a wj (http://code.kx.com/wiki/Reference/wj)
to give a straightfoward approximation. It assumes a liquid
market, but you can adjust the window interval to suit.
/ Create some sample data
trade:(sym:3#ibm;time:10:01:01 10:01:04 10:01:08;price:100 101 105)<br> quote:([]sym:9#ibm;time:10:01:01+til 9;ask:101 103 103 104 104
107 108 107 108;bid:98 99 102 103 103 104 106 106 107)
/ the common columns to join on
f:symtime
/ Assuming a liquid market, create a window looking +/- 5s
w:-5 5+:t.time
q)w
10:00:56 10:00:59 10:01:03
10:01:06 10:01:09 10:01:13
/ Create lastask and lastbid columns in the quote table
quote:symtimefirstaskfirstbidlastasklastbid
xcol update lastask:ask,lastbid:bid from quote
/ Window join and use first/last as the aggregation functions
q) tradewindow:
wj[w;f;trade;(quote;(first;firstask);(first;firstbid);(last;lastask);(last;lastbid))]
q) tradewindow
sym time price firstask firstbid lastask lastbid
----------------------------------------------------
ibm 10:01:01 100 101 98 107 104
ibm 10:01:04 101 101 98 108 107
ibm 10:01:08 105 103 102 108 107
This example is with a single sym, but you could extend it by
looping through each sym.
Thanks,
Matthew
AquaQ Analytics
Thanks Matt, but I don’t what is the time windows that there will be some change, it may not +/-5s, then what can I do for this?
Hi Jack, thanks for take a look about this, Matt have post what the tables look like, will you have any idea about this?
Hi Carfield,
My previous response should have read that the wj approach will
work as-is for multiple syms, but the example was with a single
sym for clarity. SInce the wj solution isn’t accurate enough, it’s
possible to use a binary search to find the exact solution,
however this only works for a single sym and would require looping
to extend to multiple syms.
lastchange:{[s; q; t]
t: select from t where sym=s;
q: select from q where sym=s, (differ bid) or differ
ask;
/ binary search to find the indices
ind:q[time] bin t[time];
/ ind-1 returns the previous index
update prevquotechange:q[time] ind - 1 from t</tt><tt><br> </tt><tt> }</tt><br> </big><br> I've created some more realistic trades and quotes tables for this example, selecting here for a single sym, to compare to the function's output:<br> <big><tt><br> q)select from quotes where sym=GOOG
time sym src bid ask bsize asize
--------------------------------------------------------------
2016.04.14D08:01:35.773000000 GOOG L 41.33 41.36 7500 1500
2016.04.14D08:04:59.757000000 GOOG O 41.35 41.38 9000 3000
2016.04.14D08:06:13.761000000 GOOG N 41.32 41.36 8500 5500
2016.04.14D08:07:10.517000000 GOOG N 41.3 41.34 5500 9000
2016.04.14D08:19:14.367000000 GOOG L 41.33 41.34 4500 5500
2016.04.14D08:24:24.243000000 GOOG O 41.33 41.37 6500 2000
…
q)select from trades where sym=GOOG<br> time sym src price size<br> -------------------------------------------------<br> 2016.04.14D08:11:51.270000000 GOOG N 41.34 3256<br> 2016.04.14D08:12:39.851000000 GOOG N 41.3 4660<br> 2016.04.14D08:16:31.751000000 GOOG N 41.3 5068<br> 2016.04.14D08:18:57.170000000 GOOG N 41.3 1530<br> 2016.04.14D08:20:17.805000000 GOOG L 41.33 4449<br> 2016.04.14D08:21:17.502000000 GOOG L 41.34 666<br> 2016.04.14D08:21:54.437000000 GOOG L 41.33 3557<br> <br> </tt><tt></tt><br> <tt>q)lastchange[GOOG;quotes;trades]
time sym src price size
prevquotechange
-------------------------------------------------------------------------------
2016.04.14D08:11:51.270000000 GOOG N 41.34 3256
2016.04.14D08:06:13.761000000
2016.04.14D08:12:39.851000000 GOOG N 41.3 4660
2016.04.14D08:06:13.761000000
2016.04.14D08:16:31.751000000 GOOG N 41.3 5068
2016.04.14D08:06:13.761000000
2016.04.14D08:18:57.170000000 GOOG N 41.3 1530
2016.04.14D08:06:13.761000000
2016.04.14D08:20:17.805000000 GOOG L 41.33 4449
2016.04.14D08:07:10.517000000
2016.04.14D08:21:17.502000000 GOOG L 41.34 666
2016.04.14D08:07:10.517000000
2016.04.14D08:21:54.437000000 GOOG L 41.33 3557
2016.04.14D08:07:10.517000000
The function returns the time at which the previous quote changed
and could similarly be extended to the price as well.
Thanks,
Matthew
AquaQ Analytics
Great, thanks a lot Matt, just to double confirm, if I like to do next change, this is what I need to do, right?
nextchange:{[s; q; t]
t: select from t where syms=s;
q: select from q where sym=s, (differ bid) or differ ask;
ind:t[time] bin q[time];
update nextquotechange:q[`time] ind + 1 from t
}
Hi Carfield,
That’s exactly right: ind-1 for the previous, ind+1 for the next.
If we take a look at some of the intermediate outputs of the
nextchange function:
/ Trades table where
sym=GOOG</tt><tt><br> </tt><tt>q)t</tt><tt><br> </tt><tt>time sym src price size</tt><tt><br> </tt><tt>-------------------------------------------------</tt><tt><br> </tt><tt><b>2016.04.14D08:11:51.270000000</b> GOOG N 41.34 3256</tt><tt><br> </tt><tt>2016.04.14D08:12:39.851000000 GOOG N 41.3 4660</tt><tt><br> </tt><tt>2016.04.14D08:16:31.751000000 GOOG N 41.3 5068</tt><tt><br> </tt><tt>2016.04.14D08:18:57.170000000 GOOG N 41.3 1530</tt><tt><br> </tt><tt><u>2016.04.14D08:20:17.805000000</u> GOOG L 41.33 4449</tt><tt><br> </tt><tt>2016.04.14D08:21:17.502000000 GOOG L 41.34 666</tt><tt><br> </tt><tt>2016.04.14D08:21:54.437000000 GOOG L 41.33 3557</tt><tt><br> </tt></big></font><br> <font size='"-1"'><big><tt><font size='"-1"'><big>/ Quotes table where sym=GOOG
q)q
time sym src bid ask
bsize asize
--------------------------------------------------------------
2016.04.14D08:01:35.773000000 GOOG L 41.33 41.36
7500 1500
2016.04.14D08:04:59.757000000 GOOG O 41.35 41.38
9000 3000
2016.04.14D08:06:13.761000000 GOOG N 41.32 41.36
8500 5500
2016.04.14D08:07:10.517000000 GOOG N 41.3
41.34 5500 9000
2016.04.14D08:19:14.367000000 GOOG L 41.33
41.34 4500 5500
2016.04.14D08:24:24.243000000 GOOG O 41.33 41.37
6500 2000
2016.04.14D08:27:50.350000000 GOOG L 41.32 41.35
2500 3500
2016.04.14D08:33:19.877000000 GOOG L 41.33 41.36
3000 6000
The first trade was at 08:11:51.270000000,
which bins into the 3rd index of table q (bolded).
The fifth trade bins into the 4th index (underlined).
q)ind:q[time] bin t[time]
q)
q)ind
3 3 3 3 4 4 4 6 8 8 9 9 9 10 14 15 15
15 19 19 19 20 …
q)ind+1
4 4 4 4 5 5 5 7 9 9 10 10 10 11 15 16 16 16 20 20 20
…
q)ind-1
2 2 2 2 3 3 3 5 7 7
8 8 8 9 13 14 14 14 18 18 18 19 20 …
/ Indexing into q at the (n+1)th
index and taking the time column
update nextquotechange:q[`time] ind
- 1 from t
Hope this helps!
Thanks,
Matthew
AquaQ Analytics