3

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?

Daniel Krizian
  • 4,586
  • 4
  • 38
  • 75
bigO6377
  • 1,256
  • 3
  • 14
  • 28

4 Answers4

2

Take data from your example:

txn:([] t: til 6; side:`Buy`Sell`Buy`Sell`Sell`Buy; qty:6 5 4 3 8 1; px: 10.0 11.0 10.0 11.0 9.0 8.0)

Best to maintain buys and sells transactions/fills separately in your database:

buys: select from txn where side=`Buy
sells: select from txn where side=`Sell

Functions we'll need [1]:

/ first-in first-out allocation of bid/buy and ask/sell fills
/ returns connectivity matrix of (b)id fills in rows and (a)sk fills in columns
fifo: {deltas each deltas sums[x] &\: sums[y]};

/ connectivity list from connectivity matrix
lm: {raze(til count x),''where each x};

/ realized profit & loss
rpnl: {[b;s]
    t: l,'f ./: l:lm (f:fifo[exec qty from b;exec qty from s])>0;
    pnl: (select bt:t, bqty:qty, bpx:px from b@t[;0]),'(select st:t, sqty:qty, spx:px from s@t[;1]),'([] qty: t[;2]);
    select tstamp: bt|st, rpnl:qty*spx-bpx from pnl
}

Run:

q)rpnl[buys;sells]
tstamp rpnl
-----------
1      5   
3      1   
3      2   
4      -2  
5      1

According to my timings, should be ~ 2x faster than the next best solution, since it's nicely vectorized.

Footnotes:

fifo function is a textbook example from Q for Mortals. In your case, it looks like this:

q)fifo[exec qty from buys;exec qty from sells]
5 1 0
0 2 2
0 0 1

lm function tells which buys and sell pairs were crossed (non-zero fills). More background here: [kdb+/q]: Convert adjacency matrix to adjacency list

q)lm fifo[exec qty from buys;exec qty from sells]>0
0 0
0 1
1 1
1 2
2 2

Cryptic first line of rpnl is then combination of the two concepts above:

q)t: l,'f ./: l:lm (f:fifo[exec qty from buys;exec qty from sells])>0;
0 0 5
0 1 1
1 1 2
1 2 2
2 2 1
Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
Daniel Krizian
  • 4,586
  • 4
  • 38
  • 75
1

Here is a first attempt to get the ball rolling. Not efficient.

q)t:([]id:1+til 6;v:`b`s`b`s`s`b;qty:6 5 4 3 8 1; px:10 11 10 11 9 8)
//how much of each sale offsets a given purchase
q)alloc:last each (enlist d`s){(fx-c;c:deltas y&sums fx:first x)}\(d:exec qty by v from t)`b
//revenues, ie allocated sale * appropriate price
q)revs:alloc*\:exec px from t where v=`s
q)(sum each revs)-exec qty*px from t where v=`b
6 0 1
JPC
  • 1,891
  • 13
  • 29
1

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.

terrylynch
  • 11,844
  • 13
  • 21
0

Slightly different approach without using over/scan(except in sums...).

Here we create a list of duplicated indices(one per unit Qty) of every Sell order and use cut to assign them to the appropriate Buy order, then we index into the Price of those Sells and find the difference with the Price of the appropriate Buy order.

This should scale with table size, but memory will blow up when Qty is large.

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)sideMap:`Buy`Sell!1 -1

q)update pnl:sum each neg Price - Price{sells:where neg 0&x; -1_(count[sells]&0,sums 0|x) _ sells}Qty*sideMap[Verb] from tab
Id Verb Qty Price 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     0  
6  Buy  1   8     1
user3576050
  • 161
  • 3