kdb/q -- cumulative sum by symbol, but with a cap

Question

For example in the below table, I want to run the cumulative sum on "val" column of the table, grouped by the symbol column. but I want to cap the cumulative sum by the value in the "cap" column. If the cumulative sum exceeds the cap, I just cap it at that value. And for the next value, I will add it on up of the capped value:

Example (and better format in picture). I am given input date, sym, val, cap, I want to produce the output in "cumval" column.

date        sym     val  cap cumval
-----------------------------------
2020.01.01  AAPL    100  200 100
2020.01.02  AAPL    100  200 200
2020.01.03  AAPL    100  200 200
2020.01.04  AAPL    -100 200 100
2020.01.01  MSFT    100  300 100
2020.01.02  MSFT    100  300 200
2020.01.03  MSFT    100  300 300
2020.01.04  MSFT    100  400 400

Answer 1

You'll need to use a custom accumulate to achieve your result. The built in sums function uses the binary accumulator \. To add the cap logic you'll need to use a ternary scan accumulator. The following will work {z&x+y}\. The first parameter is the initial value, zero in your case. The second parameter are the values being accumulated, and the third parameter are the cap values.

q)show t:([]date:2020.01.01 2020.01.02 2020.01.03 2020.01.04 2020.01.01 2020.01.02 2020.01.03 2020.01.04;sym:`AAPL`AAPL`AAPL`AAPL`MSFT`MSFT`MSFT`MSFT;val:100 100 100 -100 100 100 100 100;cap:200 200 200 200 300 300 300 400)
date       sym  val  cap
------------------------
2020.01.01 AAPL 100  200
2020.01.02 AAPL 100  200
2020.01.03 AAPL 100  200
2020.01.04 AAPL -100 200
2020.01.01 MSFT 100  300
2020.01.02 MSFT 100  300
2020.01.03 MSFT 100  300
2020.01.04 MSFT 100  400
q)update cumval:{z&x+y}\[0;val;cap] by sym from t
date       sym  val  cap cumval
-------------------------------
2020.01.01 AAPL 100  200 100
2020.01.02 AAPL 100  200 200
2020.01.03 AAPL 100  200 200
2020.01.04 AAPL -100 200 100
2020.01.01 MSFT 100  300 100
2020.01.02 MSFT 100  300 200
2020.01.03 MSFT 100  300 300
2020.01.04 MSFT 100  400 400