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.
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
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")
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With