Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Loop with EACH in Q KDB+

I've stock trade database (name "TRADES") and I'm trying for a long time to make a simple loop (with function EACH) which would sum all quantities that are above a pre-defined quantity threshold for every ISIN. The data looks like this:

q) select TIME, PRICE, QUANTITY from TRADES where ISIN=`IT123

TIME    PRICE QUANTITY
8:58:05 9.47    66
9:00:09 9.47    55
9:00:56 9.48    107
9:01:06 9.49    7
9:01:33 9.50    9
9:03:11 9.07    200
9:06:27 9.07    100
9:07:46 9.12    65...

At first, I try this code for one ISIN:

q) myquant: ([] qu: 1 + til 100) //pre-define quantities from 1 to 100

q) f:{[x] (select sum QUANTITY from TRADES where ISIN=`IT123, QUANTITY> x)}

q) f each myquant.qu //use function EACH for all x that are in myquant

And then I get some hieroglyphs... Maybe it is because the list is wrongly specified?

I also need to do these calculations not just for one, but for all ISIN's that I have in the database (i.e. "EACH distinct ISIN").

Thanks very much for the help in advance.

like image 838
Linas Avatar asked Oct 15 '25 15:10

Linas


2 Answers

Given some trades:

q)show trades:flip`isin`time`price`quantity!100?/:(`US5949181045`US38259P5089`US0378331005;24t;100f;100)
    isin         time         price    quantity
    -------------------------------------------
    US5949181045 18:45:28.487 60.91539 12
    US5949181045 04:35:02.468 98.30794 62
    US0378331005 23:39:20.774 75.43122 18
    US38259P5089 09:37:08.266 38.13679 37
    US0378331005 12:09:01.385 3.112646 17
    ..

For minQty ranging from 0 to 100:

q)raze {[minQty] select sum quantity by isin,minQty:minQty  from trades where quantity>minQty} each til 100
    isin         minQty| quantity
    -------------------| --------
    US0378331005 0     | 1537
    US38259P5089 0     | 1767
    US5949181045 0     | 1435
    US0378331005 1     | 1537
    US38259P5089 1     | 1767
    ..

Result gives the sum quantity for each isin where the quantity is > the given minQty

like image 178
MdSalih Avatar answered Oct 18 '25 15:10

MdSalih


Couple of options:

Sol1:

  q) s1:{[mqty]`mqty xasc ungroup select mqty,quantity:sum @/:quantity where each quantity>/:mqty by isin from trades}

  q) s1 myquant.qu

Sol2:

Extending MdSalih solution to handle scenario when no row matches the quantity condition

  q) s2:{a:ungroup select minQty:x ,quantity:0 by isin from trades;
         b:raze {[minQty] select sum quantity by isin,minQty:minQty from trades where quantity>minQty} each x;
         `minQty xasc a lj b }


  q)s2 myquant.qu

EDIT (For ISIN partitioned dir):

If you have ISIN as partition key which is not int (as it looks like from your example in comments) then kdb will not recognize it as a partitioned database.

More details on : http://code.kx.com/q4m3/14_Introduction_to_Kdb+/#1432-partition-domain

You'll have to manually load columns/tables and do calculations. One solution is:

   q) src_path:"C:\q\"
   q) raze {[isin;mqty]a:get hsym `$src_path,isin,"\trades\quantity";flip `isin`mqty`quantity!count[mqty]#/:(enlist isin;mqty;sum @/:a where each a>/:(),mqty)}[;myquant.qu] each ("ISIN01";"ISIN02")
like image 21
Rahul Avatar answered Oct 18 '25 16:10

Rahul



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!