Consider the following table:
Id Verb Qty Price
`1 Buy 6 10.0
`2 Sell 5 11.0
`3 Buy 4 10.0
`4 Sell 3 11.0
`5 Sell 8 9.0
`6 Buy 1 8.0
etc...
What I would like is to associate a PNL with each transaction, computed on a FIFO (first-in-first-out basis). Thus, for Id=`1, I want the PNL to be -6*(10.0) +5*(11.0) + 1*(11.0) = +$6.00, for Id=`3, Pnl is -4*(10.0)+2*(11.0)+(2*9.0) = $0, etc.
In layman's terms, For the first buy-order of size 6, I want to offset this by the first 6 sells, and for the second buy-order of size 4, offset this with the subsequent 4 sells that have not been included in the pnl computation for the buy-6 order.
Any advice?
A similar approach to JPC, but keeping things tabular:
q)tab:([] Id:`1`2`3`4`5`6;Verb:`Buy`Sell`Buy`Sell`Sell`Buy;Qty:6 5 4 3 8 1;Price:10.0 11.0 10.0 11.0 9.0 8.0)
q)tab
Id Verb Qty Price
-----------------
1 Buy 6 10
2 Sell 5 11
3 Buy 4 10
4 Sell 3 11
5 Sell 8 9
6 Buy 1 8
pnlinfo:{[x;y]
b:exec first'[(Qty;Price)] from x where Id=y;
r:exec (remQty;fifo[remQty;b 0];Price) from x where Verb=`Sell;
x:update remQty:r 1 from x where Verb=`Sell;
update pnl:neg[(*) . b]+sum[r[2]*r[0]-r[1]] from x where Id=y
};
fifo:{x-deltas y&sums x};
pnlinfo/[update remQty:Qty from tab where Verb=`Sell;exec Id from tab where Verb=`Buy]
Id Verb Qty Price remQty pnl
----------------------------
1 Buy 6 10 6
2 Sell 5 11 0
3 Buy 4 10 0
4 Sell 3 11 0
5 Sell 8 9 5
6 Buy 1 8 1
Assumes that Buys will be offset against previous sells as well as future sells.
You could also in theory use other distributions such as
lifo:{x-reverse deltas y&sums reverse x}
but I haven't tested that.