CODEWITHSUNDEEP
kdb+
user1167650
user1167650

Reputation: 3207

kdb/q building NBBO from TAQ data

I have a table with bid/ask for for every stock/venue. Something like:

taq:`time xasc ([] time:10:00:00+(100?1000);bid:30+(100?20)%30;ask:30.8+(100?20)%30;stock:100?`STOCK1`STOCK2;exhcnage:100?`NYSE`NASDAQ)

How can I get the best/bid offer from all exchanges as of a time (in one minute buckets) for every stock?

My initial thought is to build a table that has a row for every minute/exchange/stock and do a asof join on the taq data. However, it sounds to me this is a brute force solution - since this is a solved problem, i figured i'd ask if there is a better way.

Upvotes: 3

Views: 677

Answers (1)

jgleeson
jgleeson

Reputation: 955

select max bid, min ask by stock,1+minute from 0!select by 1 xbar time.minute,stock,exchange from taq

This will give you the max bid, min ask across exchanges at the 1-minute interval in column minute.

The only tricky thing is the select by 1 xbar time.minute. When you do select by with no aggregation, it will just return the last row. So really what this means is select last time, last bid, last ask .... by 1 xbar time.minute etc.

So after we get the last values by minute and exchange, we just get the min/max across exchanges for that minute.

Upvotes: 4

Related Questions