Loop with EACH in Q KDB+

Question

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.

score 2 · Accepted Answer · answered Nov 12 '16 at 12:44

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

Great, thanks! Also maybe You know how to do multiple loops, e.g. to make the same calculations only for a subset of ISIN's (US0378331005, US5949181045)? And also, how to show 0 if there is no sum quantity at some minQty's (it would be a case when minQty becomes bigger and bigger and there are no quantities to sum)? Thanks again! — Linas, Nov 12 '16 at 13:57

score 2 · Answer 2 · edited Dec 12 '17 at 20:16

2

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")

edited Dec 12 '17 at 20:16

Thomas Smyth - Treliant

4,993
6
25
36

answered Nov 13 '16 at 19:12

Rahul

3,914
1
14
25

Thank You for the answer. But maybe You could also answer to the second part of the question, i.e. how to do multiple loops, e.g. how to make the same calculations only for a subset of ISIN's (US0378331005, US5949181045)? – Linas Nov 15 '16 at 21:23
1

You can pass ISIN list as second argument (let say in 'lstIsin') and use that in where clause to filter. Like -> where isin in lstIsin,quantity>minQty . Does that answer your question? – Rahul Nov 16 '16 at 10:33
It helped. But I was wondering if this code (where isin in IstIsin) could help to automatically load my trade databases of ISIN's (hundreds of partitions) and do the calculation: t_US0378331005: value `:C:/q/US0378331005/; t_US5949181045: value `:C:/q/US5949181045/; and hundreds more. Then I need to perform the calculations for EACH of db: s_US0378331005:{[mqty]`mqty xasc ungroup select mqty,quantity:sum @/:quantity where each quantity>/:mqty from t_US0378331005}; s_US5949181045:{[mqty]`mqty xasc ungroup select mqty,quantity:sum @/:quantity where each quantity>/:mqty from t_US5949181045}; etc. – Linas Nov 16 '16 at 22:47
1

I could write these codes (hundreds of them) for each ISIN trade database line by line, but thought maybe it is possible to write a code to load and due the calculations automatically? Maybe the command EACH could help in this example? Thanks for help! – Linas Nov 16 '16 at 22:48
1

Looks like you have partitioned your database on ISIN. Problem with that approach is that KDB will not recognize it as partitioned dir as only int values are allowed in partition key domain. That means you cant use 'select by' directly and you have to manually load the files & do calculations. I have edited my answer to cover this. – Rahul Nov 17 '16 at 15:17

Loop with EACH in Q KDB+

2 Answers2