Search code examples
kdb

Pulling nbbo data from KDB server

I have a table of tickers with date, start time, end time for which i need to pull nbbo from the nbbo table on server. Eg

Date         starttime    endtime      sym
2014.05.01  15:10:38.000  15:10:58.000 KT
2014.05.01  15:15:53.000  15:16:23.000 IBM
2014.05.01  15:37:39.000  15:37:59.000 AAPL

Ideally, i will open a handle to the server and pull the data for each row by passing into this function:getnnbo and calling it as: getnnbo[KT;2014.05.01;15:10:38.000; 15:10:58.000]` where function is defined as

getnbbo:{[sym;dt;starttime;endtime]:h1"select 0.5*(first bid + first ask) from nbbo where date=",string[dt],",sym =`",string[sym], ",linetime within (",string[starttime],",",string[endtime],")"}

This function works when i call it as getnnbo[KT;2014.05.01;15:10:38.000; 15:10:58.000]` but i am not sure how to do it for each row in the table as i have to work with a handle to the server.

Solution

  • I would suggest instead of making request for each row on server, send full table (or part if it's huge) at once and fetch the data in one request from server. That should speed up the process because of less requests and server will also take less time in generating the result as generally doing calculation on list is faster than individual row.

    So your function should be something like below (just an example). You can optimize/modify it according to your need. I assume that your ticker table has one only entry for each (date;sym) combination so that it can serve as primary key.

       getData:{[tbl] select 0.5*first[bid]+first[ask] from (nbbo ij `date`sym xkey tbl) where linetime within (st;et) }

    Then run it on server using server handle. 'tbl' is your input table with tickers.

       handle(getData;tbl)

    Edit After ma3266 comment:

    I am assuming following two conditions:

    a) nbbo table is partitioned on server

    b) Your input tbl has date and symbol column names same as in nbbo. Otherwise you can use 'xcol' to rename them.

         getData:{tbl:0!tbl; tempNbbo:0!select from nbbo where date in tbl`date,sym in tbl`sym ; select 0.5*first[bid]+first[ask] from (tempNbbo ij `date`sym xkey tbl) where linetime within (starttime;endtime)}

    But this can lead to heavy memory usage if your input table has data for many dates. In that case you can use following function which will break input table by date.

       getData:{tbl:0!`date xasc tbl; raze { tempNbbo:0!select from nbbo where date in x`date,sym in x`sym ; select 0.5*first[bid]+first[ask] from (tempNbbo ij `date`sym xkey x) where linetime within (starttime;endtime)} each (where differ tbl`date) cut tbl}

    In both function, I am first removing primary key attribute from input table.

    Also, check column types of nbbo and your input table. That might be giving you type error. This query is working fine for me.