How to join two KDB tables using the "closest" timestamp instead of "asof" timestamp?

user_459975 0
Viewed 251

I am trying to join two tables in a manner very similar to an "as of" join, except instead of choosing the row with the last timestamp to join onto (assuming they are sorted in time order), I want to join with the closest timestamp. For example:

q)t: ([]time:10:00:06 10:00:03 10:00:04;sym:`msft`ibm`ge;qty:100 200 150)
q)t
time     sym  qty
-----------------
10:00:06 msft 100
10:00:03 ibm  200
10:00:04 ge   150

q)q: ([]time:10:00:00 10:00:00 10:00:02 10:00:07 10:02:00;sym:`ibm`msft`msft`msft`ibm;px:100 99 101 102 98 )
q)q
time     sym  px
-----------------
10:00:00 ibm  100
10:00:00 msft 99
10:00:02 msft 101
10:00:07 msft 102
10:02:00 ibm  98

Standard as-of join:

q)aj[`sym`time;t;q]
time     sym  qty px
---------------------
10:00:06 msft 100 101 //10:00:02 is closest timestamp that is not greater than 10:00:06, so that px is chosen
10:00:03 ibm  200 100  
10:00:04 ge   150

The thing is, for msft, since 10:00:07 is closer than 10:00:02 to the original microsoft timestamp, even though it's greater than the original msft timestamp, what I want ideally is is:

q)closest_join[`sym`time;t;q]
time     sym  qty px
---------------------
10:00:06 msft 100 102  //10:00:07 is +1second, 10:00:02 is -6second, so I want it to use 10:00:07
10:00:03 ibm  200 100
10:00:04 ge   150

How would you do this? Note it must work for multiple "msft" rows in the source table similar to how aj does.

kdb database
1 Answers

I don't think there's any clean way to do this but you can do it using the following:

  1. Create a custom aj which uses binr (bin from the right)
/for kdb v4.0
k).Q.ajf0r:{[f;g;x;y;z]x,:();z:0!z;d:$[g;x_z;z];g:(:;^)f;f:(,;^)f;$[&/j:-1<i:(x#z)binr x#y;f'[y;d i];+.[+.Q.ff[y]d;(!+d;j);g;.+d i j:&j]]}
k)ajr:{.Q.ft[.Q.ajf0r[0;1;x;;z]]y}

/for kdb v3.5
k)ajr:{.Q.ft[{d:x_z;$[&/j:-1<i:(x#z)binr x#y;y,'d i;+.[+.Q.ff[y]d;(!+d;j);:;.+d i j:&j]]}[x,();;0!z]]y}
  1. Do a standard aj
q)show a:aj[`sym`time;t;update qtime:time from q]
time     sym  qty px  qtime
------------------------------
10:00:06 msft 100 101 10:00:02
10:00:03 ibm  200 100 10:00:00
10:00:04 ge   150
  1. Do the custom aj
q)show b:ajr[`sym`time;t;update qtime:time from q]
time     sym  qty px  qtime
------------------------------
10:00:06 msft 100 102 10:00:07
10:00:03 ibm  200 98  10:02:00
10:00:04 ge   150
  1. Blend the two based on which time gap is smaller
q){x,y(>). abs y[`time]-y`qtime}/[0#a;flip(a;b)]
time     sym  qty px  qtime
------------------------------
10:00:06 msft 100 102 10:00:07
10:00:03 ibm  200 100 10:00:00
10:00:04 ge   150

Be aware however, that joining "future" quotes to trades is generally a bad idea in quant/finance (aka look-ahead bias)

user_38956970
Related