1
$\begingroup$

A very common task is stock portfolio evaluation is to calculate the average price of stock holdings for a sequence of trades and from there to calculate the (realized) profit or loss. Obviously one can easily accomplish the task using Do loops, but that's a very inefficient (and inelegant) approach to take in Mathematica.

I am looking for an elegant, efficient solution that will likely make use of functions like PairFoldList and/or SequenceFoldList.

Here is a simple, toy example shown as an Excel table for simplicity:

enter image description here

There are five transactions in total. In the first three transactions a total of 625 shares are acquired at three different prices. The total value of the portfolio is calculated and the average price is simply this value divided by the total number of shares in the portfolio (e.g. $1062.50 / 635 = $1.70 after the third transaction).

In the fourth transaction we dispose of 400 shares of the portfolio and we now calculate a P&L, as this is a closing transaction, using the average price: 400 * ($2.50 - $1.70) = $320.

Note that the average price remains at its previous value (i.e $1.70).

If the fifth transaction the remainder of the shares (225) are sold giving a profit of 225 * ($3.00 - $1.70) = $292.50.

The total PL from all transactions in this example is $612.50.

I am looking for two functions, the first that will generate a list of average prices { $1.00, $1.32, $1.70, $1.70, $0 } and the second a list of realized PLs: { $0, $0, $0, $320, $292.50 }.

As I say, doing this with loops is trivial, but can be very slow if the number of transactions is large.

Important note: it is vital to take account of the possibility of going short. So, for instance, if in the fifth transaction we had sold 500 shares, instead of just the remaining 225 shares in the portfolio, we would now be net short 275 shares and the table would read as follows:

enter image description here

Note that: (i) the realized P&L remains unchanged (ii) the Av Price for the fifth transaction is no longer 0 (or #DIV/0! in Excel), but $3.00, the price at which the excess of 275 shares were sold short.

In other words, a change of sign on the portfolio value restarts the computation of the average price sequence.

Not an easy challenge!

$\endgroup$
2
$\begingroup$

My approach to this would be to create two different versions of the function that take occur when either a buy or sell occurs.

We'll begin by defining the trading instructions as a list of pairs of prices and trade quantities:

trades = {{1.00, 100}, {1.50, 175}, {2.00, 350}, {2.50, -400}, {3.00, -225}};
tradesShort = {{1.00, 100}, {1.50, 175}, {2.00, 350}, {2.50, -400}, {3.00, -500}};

(the "trade #" column is superfluous, as this is the same as the index of each pair in the list):

The two functions are:

(*buy*)
processTrade[{totalQty_, totalValue_, avgPrice_, PL_}, {price_, qty_?Positive}] := With[
  {tradeValue = price*qty, newTotalQty = totalQty + qty},
  If[newTotalQty != 0.,
   {newTotalQty, totalValue + tradeValue, (totalQty*avgPrice + tradeValue)/newTotalQty, 0},
   {newTotalQty, totalValue + tradeValue, 0, 0}(*discard average price if no shares remain*)
   ]
  ]

(*sell*)
processTrade[{totalQty_, totalValue_, avgPrice_, PL_}, {price_, qty_?Negative}] := With[
  {realizedPL = Min[-qty, totalQty]*(price - avgPrice),
   newTotalQty = totalQty + qty},
  If[newTotalQty > 0,
   {newTotalQty, newTotalQty*avgPrice, avgPrice, realizedPL}, (*ordinary sale*)
   {newTotalQty, newTotalQty*price, price, realizedPL} (*short sale*)
   ]
  ]

In each case, the first argument is a list whose elements describe the current state of the system (as input) and gets returned describing the outcome after the trade occurs. Profits and losses are only realized at sale, and the new average price will vary depending on if a short sale has occurred, so the logic is a bit more complicated (we can only realize a profit/loss on the shares we have in hand, hence the Min and if the new total quantity of shares is negative, then the average price has to get reset).

(Alternatively, one could create two versions of the "sale" function, and use a Condition to check for whether a short sale has occurred or not, but I don't think that would simplify the logic at all.)

Folding these over the example trade data provided in the question seems to result in the desired behavior:

FoldList[processTrade, {0, 0, 0, 0}, trades]
(* {{0, 0, 0, 0}, {100, 100., 1., 0}, {275, 362.5, 1.31818, 0}, {625, 
  1062.5, 1.7, 0}, {225, 382.5, 1.7, 320.}, {0, 0., 3., 292.5}} *)

FoldList[processTrade, {0, 0, 0, 0}, tradesShort]
(* {{0, 0, 0, 0}, {100, 100., 1., 0}, {275, 362.5, 1.31818, 0}, {625, 
  1062.5, 1.7, 0}, {225, 382.5, 1.7, 320.}, {-275, -825., 3., 292.5}} *)

(Output of the initial state value, {0, 0, 0, 0} can be omitted by applying Rest, if so desired.)

$\endgroup$
6
  • $\begingroup$ Brilliant! And its almost bullet-proof. However, if we start with a short sale and then reverse it, the FoldList blows up. Try tradesShort = {{1.00, -100},{1.50, 100},.....} to see what I mean $\endgroup$ May 8 '21 at 16:24
  • $\begingroup$ I clearly don't understand enough about short sales (I'm just a simple buy-and-hold Boglehead). What would the example look like? $\endgroup$ May 8 '21 at 16:28
  • $\begingroup$ Hi Joshua, no you have nailed it almost completely, first time! It’s just an edge case. It’s the same example as before using trades or tradesShort, except that the first two elements in the list are now {{1.00, -100}, {1.50, 100}}. In other words, we now begin with a short sale of 100 shares, followed immediately by a repurchase of 100 shares, taking us back to a neutral position with zero holdings. That scenario is the only one I have found so far that causes any issues for your algorithm, which is around 100x faster than an equivalent Do loop. $\endgroup$ May 8 '21 at 21:22
  • $\begingroup$ Aha. I think I see what you mean. There's a need to check (in the "buy" version) if newTotalQty != 0 to avoid this type of division by zero error. Updated above. Still unclear about the correct way to handle PL when one buys to fill a short sale (it's assumed to be zero when one buys), but intuitively one is out $50.... $\endgroup$ May 9 '21 at 0:29
  • $\begingroup$ I ran some more tests and unfortunately there seems to be a problem, specifically in the way the algorithm handles short trades. I dont think there is a reason to treat sells differently from buys, as the algo does as currently forumulated. The treatment should be identical for buys and sells. What should trigger a P&L calculation is not the sign of the trade quantity, (qty_?Positive , or qty_?Negative), but rather a difference in sign between qty and the sign of the totalQty. $\endgroup$ May 9 '21 at 11:58
1
$\begingroup$

So Joshua Schrier offered an elegant and efficient algorithm (see above) that works well for a typical stock portfolio in which one is sequentially buying and selling stocks. However, it needed generalising to cover cases in which one is going short as well as long and also to take account of point value. These issues arise in e.g. futures trading where one can just as easily sell a contract as buy it, and in which the point value of the contract is typically something other than $1 (For example 50 for ES futures.)

My version of Joshua's algorithm handles both of these cases and computes both realized and unrealized P&L for a series of long and short trades. In the end I was obliged to abandon the slightly more efficient formulation using With, in favor of Module, mainly because I couldnt figure out how to handle defining and using the local variables simultaneously (since several of them are defined in terms of the others). But the degredation in performance in negligible, and its still 10x faster than the equivalent Do loop version.

CalculateTradePL[{totalQty, totalValue, avgPrice, PL}, {price_, 
   qty_}] := 
 Module[{newTotalQty, tradeValue, newavgPrice, newTotalValue, newPL},
  newTotalQty = totalQty + qty;
  tradeValue = 
   If[Sign[qty] == Sign[totalQty] || avgPrice == 0, price*qty, 
    If[Sign[totalQty + qty] == Sign[totalQty], avgPrice*qty, 
     price*(totalQty + qty)]];
  newTotalValue = 
   If[Sign[totalQty] == Sign[newTotalQty], totalValue + tradeValue, 
    newTotalQty*price];
  newavgPrice = 
   If[Sign[totalQty + qty] == 
     Sign[totalQty], (totalQty*avgPrice + tradeValue)/newTotalQty, 
    price];
  newPL = totalQty *( newavgPrice - avgPrice);
  {newTotalQty, newTotalValue, newavgPrice, newPL}]
CalculatePLFromTrades[tradeList, pointValue : 1] := 
 Module[{TradePL = 
    Rest@FoldList[CalculateTradePL, {0, 0, 0, 0}, tradeList]},
  TradePL[[All, 4]] = TradePL[[All, 4]] * pointValue;
  TradePL]

 
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.