Message Boards Message Boards


Trading strategy backtesting: calculating trade profit and loss

Posted 4 months ago
4 Replies
11 Total Likes

This is a snippet from a strategy backtesting system that I am currently building in Mathematica.

One of the challenges when building systems in WL is to avoid looping wherever possible. This can usually be accomplished with some thought, and the efficiency gains can be significant. But it can be challenging to get one's head around the appropriate construct using functions like FoldList, etc, especially as there are often edge cases to be taken into consideration.

A case in point is the issue of calculating the profit and loss from individual trades in a trading strategy. The starting point is to come up with a FoldList compatible function that does the necessary calculations:

CalculateRealizedTradePL[{totalQty_, totalValue_, avgPrice_, PL_, 
totalPL_}, {qprice_, qty_}] := 
Module[{newTotalPL = totalPL, price = QuantityMagnitude[qprice], 
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, 
newavgPrice = 
If[Sign[totalQty + qty] == 
Sign[totalQty], (totalQty*avgPrice + tradeValue)/newTotalQty, 
newPL = 
If[(Sign[qty] == Sign[totalQty] ), 0, 
totalQty*(price - avgPrice)];
newTotalPL = newTotalPL + newPL;
{newTotalQty, newTotalValue, newavgPrice, newPL, newTotalPL}]

Trade P&L is calculated on an average cost basis (as opposed to FIFO or LIFO).

Note that the functions handle both regular long-only trading strategies and short-sale strategies, in which (in the case of equities), we have to borrow the underlying stock to sell it short. Also, the pointValue argument enables us to apply the functions to trades in instruments such as futures for which, unlike stocks, the value of a 1 point move is typically larger than $1 (e.g. $50 for the ES S&P 500 mini futures contract).

We then apply the function in two flavors, to accommodate both standard numerical arrays and timeseries (associations would be another good alternative):

CalculateRealizedPLFromTrades[tradeList_?ArrayQ, pointValue_ : 1] := 
Module[{tradePL = 
Rest@FoldList[CalculateRealizedTradePL, {0, 0, 0, 0, 0}, 
tradePL[[All, 4 ;; 5]] = tradePL[[All, 4 ;; 5]]*pointValue;
CalculateRealizedPLFromTrades[tsTradeList_, pointValue_ : 1] := 
Module[{tsTradePL = 
Rest@FoldList[CalculateRealizedTradePL, {0, 0, 0, 0, 0}, 
tsTradePL[[All, 4 ;; 5]] = tsTradePL[[All, 4 ;; 5]]*pointValue;
tsTradePL[[All, 2 ;;]] = 
Quantity[tsTradePL[[All, 2 ;;]], "US Dollars"];
tsTradePL = 
Join[Transpose@tsTradeList["Values"], Transpose@tsTradePL], 

These functions run around 10x faster that the equivalent functions that use Do loops (without parallelization or compilation, admittedly)

Let's see how they work with an example:

tsAAPL = FinancialData["AAPL", "Close", {2020, 1, 2}]    

enter image description here

Next, we'll generate a series of random trades using the AAPL time series, as follows (we also take the opportunity to convert the list of trades into a time series, tsTrades):

trades = Transpose@
         20]]]], {RandomChoice[{-100, 100}, 20]}];
trades // TableForm

enter image description here

We are now ready to apply our Trade P&L calculation function, first to the list of trades in array form:

Flatten[#] & /@ 
CalculateRealizedPLFromTrades[trades[[All, 2 ;; 3]]]], 2], 
TableHeadings -> {{}, {"Date", "Price", "Quantity", "Total Qty", 
"Position Value", "Average Price", "P&L", "Total PL"}}]

enter image description here

The timeseries version of the function provides the output as a timeseries object in Quantity["US Dollars"] format and, of course, can be plotted immediately with DateListPlot (it is also convenient for other reasons, as the complete backtest system is built around timeseries objects):

tsTradePL = CalculateRealizedPLFromTrades[tsTrades]

enter image description here

So far so good - but this only covers calculation of the realized profit and loss. I will leave the calculation of unrealized gains and losses for another post.

4 Replies

enter image description here -- you have earned Featured Contributor Badge enter image description here Your exceptional post has been selected for our editorial column Staff Picks and Your Profile is now distinguished by a Featured Contributor Badge and is displayed on the Featured Contributor Board. Thank you!

This is interesting but I am suspicious of Do loops being an order of magnitude slower for this purpose.

(1) Did you ascertain that both give the same result? (I assume so.)

(2) Did you do anything to locate possible bottlenecks? This is admittedly not so easy. I usually do pedestrian things like add Print and Timing in various places. These are blunt tools but nevertheless can be effective. I have learned that often enough the problem is not what or where I would have expected.

Hi Daniel,

(1) yes, I cross-checked the results for several examples , in both Mathematica and Excel.

(2) No I did not investigate bottlenecks, other than to identify the Do loop version of this P&L calculation function as the chief bottleneck in a larger program. As I wrote, it could well be that the code could have been streamlined, or accelerated with compilation or parallelization.

But I can report that the 10x speedup is indeed accurate, for the large dataset of trade transactions I am evaluating. Initially, the performance improvement was on the order of 100x. But then I had to add some quite messy WL code to handle various "edge" cases, which slowed it down considerably. Still, the end result represents a significant speed improvement.

I was rather surprised by this as I realize WR has put a great deal of effort into speeding up the procedural functionality in WL code. But, in any case, I think that FoldList application is better aligned with the WL paradigm and modern programming techniques.

I will say, however, that it usually takes me a long time to figure out the code logic when applying functions like FoldList, SequenceFoldList, etc. I suspect this is just because my early programming experience was in Fortran, Algol and C. Mathematica users unburdened by such legacy languages and concepts are likely to a great deal faster than me!

Hi Jonathan - I've been working in a similar vein for some time. I appreciate your approach with FoldList and, like you, have struggled to put the parts together - so use loops everywhere. Your post helped me see how much of this simple trade accounting could be done in a FoldList and so I've merged some of yours with some of mine. In particular I've added more P&L accounting and expanded the step-by-step (across the table) so a user can start at the left, read numbers while doing arithmetic in their head, and confirm the results. Mined you, I've not spent much time auditing so there could be scenarios that need rework. In the code I've included comments regarding my preferences and various assumptions being make for these calculations.

Needless to say a full on trade accounting module is not a small project.

Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
or Discard

Group Abstract Group Abstract