I have the following trade table:
time ticker side price qty
--------------------------
2018.01.01T13:00:20 AAPL BUY 10.0 100
2018.01.01T13:01:30 AAPL SELL 12.0 300
2018.01.01T13:01:45 AAPL BUY 11.0 500
2018.01.01T13:02:13 AAPL BUY 10.5 100
2018.01.01T13:05:00 AAPL SELL 13.0 200
I need a rolling window function with a lookback of 1 minute to seperate the buy/sells of a stock price
time ticker BUYs SELLs TOTAL
--------------------------------
2018.01.01T13:00:20 AAPL 1 0 1
2018.01.01T13:01:30 AAPL 0 1 1
2018.01.01T13:01:45 AAPL 1 1 2
2018.01.01T13:02:13 AAPL 1 1 2
2018.01.01T13:05:00 AAPL 0 1 1
I have decided on using the "wj" function, because the rolling function suit my purpose. However I can't get it to work:
w: -00:01 00:00 +:/ select time from table
wj[w;'ticker'time;table;(table;(count;ticker);(count;ticker))]
So at least I want the count every buy/sell first then group them later. But I cannot even get the initial query to run without getting a type error.
Can someone point me in the right direction?
Additional Question
I know would have to perform a rolling sum/count over several accounts which is not known until runtime.
time ticker side price qty account
----------------------------------
2018.01.01T13:00:20 AAPL BUY 10.0 100 ACCT123
2018.01.01T13:01:30 AAPL SELL 12.0 300 ACCT456
2018.01.01T13:01:45 AAPL BUY 11.0 500 ACCT789
2018.01.01T13:02:13 AAPL BUY 10.5 100 ERRORACCT123
2018.01.01T13:05:00 AAPL SELL 13.0 200 TESTACCT123
I know I can pivot the table to:
time ticker side price qty ACCT123 ACCT456 ACC789 ERRORACCT123 TESTACCT23
---------------------------------
but can I using the rolling function to sum the sizes in a 1 minute lookback period?
The window w is required to be a pair of lists:
w: -00:01 00:00 +\: exec time from t
You'll also need to use wj1 as you only want to consider rows on or after entry to the window.
http://code.kx.com/q/ref/joins/#wj-wj1-window-join
q)table,'exec side from wj1[w;`ticker`time;table;(table;({`BUY`SELL!count each (group x)`BUY`SELL};`side))]
The monadic lambda:
{`BUY`SELL!count each (group x)`BUY`SELL}
Uses group to return the indices of BUY and SELL values and also ensures that BUY and SELL are present in all keys.
exec creates a table:
q)exec side from wj1[w;`ticker`time;table;(table;({{`BUY`SELL!count each x`BUY`SELL}group x};`side))]
BUY SELL
--------
1 0
0 1
1 1
2 1
0 1
And then we use join each to get the final result:
q)update TOTAL:BUY+SELL from table,'exec side from wj1[w;`ticker`time;table;(table;({`BUY`SELL!count each (group x)`BUY`SELL};`side))]
time ticker side price qty BUY SELL TOTAL
------------------------------------------------------------------
2018.01.01D13:00:20.000000000 AAPL BUY 10 100 1 0 1
2018.01.01D13:01:30.000000000 AAPL SELL 12 300 0 1 1
2018.01.01D13:01:45.000000000 AAPL BUY 11 500 1 1 2
2018.01.01D13:02:13.000000000 AAPL BUY 10.5 100 2 1 3
2018.01.01D13:05:00.000000000 AAPL SELL 13 200 0 1 1
For summing quantities depending on side it is easier to the following: First update two new columns using vector conditional and then sum these using wj1.
http://code.kx.com/q/ref/lists/#vector-conditional
q)wj1[w;`ticker`time;table;(update BUYQUANTITY:?[`BUY=side;qty;0],SELLQUANTITY:?[`SELL=side;qty;0]from table;(sum;`BUYQUANTITY);(sum;`SELLQUANTITY))]
time ticker side price qty BUYQUANTITY SELLQUANTITY
----------------------------------------------------------------------------
2018.01.01D13:00:20.000000000 AAPL BUY 10 100 100 0
2018.01.01D13:01:30.000000000 AAPL SELL 12 300 0 300
2018.01.01D13:01:45.000000000 AAPL BUY 11 500 500 300
2018.01.01D13:02:13.000000000 AAPL BUY 10.5 100 600 300
2018.01.01D13:05:00.000000000 AAPL SELL 13 200 0 200