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:

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:

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!