kdb 2-way asof join (merge bid/ask tables)

Question

I used the schema defined in http://code.kx.com/svn/kx/kdb+/tick/tba.q:

trade:bid:ask:([]time:timespan$();sym:symbol$();price:float$();size:int$())

I want to join bid and ask tables into a single quote table but can't figure out how to do it. aj[] uses only one of the table's time column but I need both time columns combined.

E.g.

bid

time     size price
----------------
10:00:00 10 0.05
10:00:03 10 0.06
10:00:06 20 0.06

ask

time     size price
----------------
10:00:00 10 0.06
10:00:02 10 0.07
10:00:04 20 0.07

The result should be:

time      bs bprc  as aprc
--------------------------
10:00:00  10 0.05  10 0.06
10:00:02  10 0.05  10 0.07
10:00:03  10 0.06  10 0.07
10:00:04  10 0.06  20 0.07
10:00:06  20 0.06  20 0.07

Is anyone using this tba schema? It is much more compact and saves on disk space significantly but I see that it's not really flexible with regards to queries. If I wanted to get an average spread per day then how I would go about it?

Thank you!

Answer 1

You don't need aj to achieve what you want. uj combined with fills will do the job:

b: 1!`time`sym`bs`bprc xcol bid / convert bid to a keyed table
a: 1!`time`sym`as`aprc xcol ask / convert ask to a keyed table
ba: `time xasc b uj a / join them and sort by time
/ forward fill the missing prices
select time, sym, fills bs, fills bprc, fills as, fills aprc from ba

Hope this helps.

Update. If you need to support multiple symbols then you should group by time and sym as @user2393012 suggested:

b: 2!`time`sym`bs`bprc xcol bid / convert bid to a keyed table
a: 2!`time`sym`as`aprc xcol ask / convert ask to a keyed table

and then:

ba: `time xasc b uj a / no change here
ungroup select time, sym, fills bs, fills bprc, fills as, fills aprc by sym from ba

The idea is that you apply fills to values within each group only, preventing prices from leaking outside symbol boundaries.

Answer 2

You can use a union join to accomplish this.

q)bid
time                 sym  bsize bprice
--------------------------------------
0D10:00:00.000000000 AAPL 10    0.05
0D10:00:03.000000000 AAPL 10    0.06
0D10:00:06.000000000 AAPL 10    0.06

q)ask
time                 sym  asize aprice
--------------------------------------
0D10:00:00.000000000 AAPL 10    0.06
0D10:00:02.000000000 AAPL 10    0.07
0D10:00:04.000000000 AAPL 10    0.07

q)0!update bsize:fills bsize, bprice:fills bprice, asize:fills asize, aprice:fills aprice from `time xasc (2!bid) uj (2!ask)

time                 sym  bsize bprice asize aprice
---------------------------------------------------
0D10:00:00.000000000 AAPL 10    0.05   10    0.06
0D10:00:02.000000000 AAPL 10    0.05   10    0.07
0D10:00:03.000000000 AAPL 10    0.06   10    0.07
0D10:00:04.000000000 AAPL 10    0.06   10    0.07
0D10:00:06.000000000 AAPL 10    0.06   10    0.07 

UPDATE

As the uj is on tables keyed by time and sym so the union join works with sym column too