Consider the table below:
| Id | Verb | Qty | Price |
|---|---|---|---|
| 1 | Buy | 6 | 10.0 |
| 2 | Sell | 5 | 11.0 |
| 3 | Buy | 4 | 10.0 |
| 4 | Sell | 3 | 12.0 |
| 5 | Sell | 8 | 9.0 |
| 6 | Buy | 7 | 8.0 |
I would like to compute the PnL in a FIFO way. For example for Id=1, PnL is -6*(10.0) +5*(11.0) + 1*(12.0) = +$7.00. For Id=5, this case is a bit different: our position is +2, and we will firstly fill this position(which will not take account into the PnL of Id=5), then we sell the remaining 6 assets. At Id=6, the -6 position is fulfilled and we get the PnL of Id=5 which is +6*(9.0)-6*(8.0)=+$6.00. Hence this table with PnL is what I want to have :
| Id | Verb | Qty | Price | PnL |
|---|---|---|---|---|
| 1 | Buy | 6 | 10.0 | 7.0 |
| 2 | Sell | 5 | 11.0 | 0.0 |
| 3 | Buy | 4 | 10.0 | 2.0 |
| 4 | Sell | 3 | 12.0 | 0.0 |
| 5 | Sell | 8 | 9.0 | 6.0 |
| 6 | Buy | 7 | 8.0 | 0.0(with 1 asset remaining) |
I have read this post and KDB: pnl in FIFO manner and https://code.kx.com/q4m3/1_Q_Shock_and_Awe/#114-example-fifo-allocation. But in their approach, they don't care about the order between buy orders and sell orders, which is not my case.
My idea is to firstly produce the FIFO allocation matrix where the dimension is the trades number:
| Id | 1 | 2 | 3 | 4 | 5 | 6 |
|---|---|---|---|---|---|---|
| 1 | 6 | 0 | 0 | 0 | 0 | 0 |
| 2 | 1 | 0 | 0 | 0 | 0 | 0 |
| 3 | 1 | 0 | 4 | 0 | 0 | 0 |
| 4 | 0 | 0 | 2 | 0 | 0 | 0 |
| 5 | 0 | 0 | 0 | 0 | -6 | 0 |
| 6 | 0 | 0 | 0 | 0 | 0 | 1 |
Then I compute the diff(price). The inner product of each column and diff(price) is PnL of each trade.
I am having trouble to implement this allocation matrix. Or any advice on solving this problem more directly?
Here's one approach. It's more convoluted than I'd like but it covers a lot of the intermediary steps and generates a type of allocation matrix as you suggested. There are likely edge-cases and tweaks needed but this should give you some ideas at least.
t:([]id:1+til 6;side:`b`s`b`s`s`b;qty:6 5 4 3 8 7;px:10 11 10 12 9 8f);
t:update pos:sums delta from update delta:qty*(1;-1)side=`s from t;
f:{signum[x]*x,{@[(-). z;x;:;abs[y]-sum z 1]}[y;x y]{(x;deltas y&sums x)}[abs where[signum[x]<>signum x y]#x;abs x y]};
t:update fifo:deltas[id!delta;f\[id!delta;id]] from t;
q)update pnl:sum each(id!px)*/:fifo from t
id side qty px delta pos fifo pnl
-----------------------------------------------------
1 b 6 10 6 6 1 2 3 4 5 6!-6 5 0 1 0 0 7
2 s 5 11 -5 1 1 2 3 4 5 6!0 0 0 0 0 0 0
3 b 4 10 4 5 1 2 3 4 5 6!0 0 -4 2 2 0 2
4 s 3 12 -3 2 1 2 3 4 5 6!0 0 0 0 0 0 0
5 s 8 9 -8 -6 1 2 3 4 5 6!0 0 0 0 6 -6 6
6 b 7 8 7 1 1 2 3 4 5 6!0 0 0 0 0 0 0