Crunching Your Numbers: Basic Principles and MAE
How you can improve your results by reviewing your journal
Recently, I received an email from a reader who wants to get better at trading and is prepared to put in the work, but isn’t sure how to do it.
I’m always happy to make suggestions when someone gets stuck, but cannot do the work for you. This isn’t just a matter of principle, but also a simple fact: to be profitable, you must trade with conviction. To have conviction, you need to put in some serious work — look at the example Qullamaggie sets.
I don’t doubt that much of this work needs to be in the form of studying thousands of charts, but I’m also a big believer in that knowing the hard numbers about your own trading will massively help you too. In fact, I believe this to be one of the best return-on-investment ways to study and improve. If you know the details of your true performance, you know exactly where your strengths lie and what you need to work on.
I discussed quantitative analysis of Excel-based trading journals in more detail here, which is the stack this reader (‘Trader X’) was replying to. With their permission, I’m going to share and respond to a part of their email here, because I think it may benefit others too.
If you have any questions, feel free to leave a comment below! You can also message me on Twitter or email me at kayklingson@yahoo.com.
To receive future posts like this straight to your inbox, why not subscribe?
Alternatively, feel free to explore my full archive first.
About my Excel skills
Before we get to Trader X’s email, I just want to make clear that my Excel skills are only ‘good enough’.
When I started my quantitative analysis journey, inspired by Tom Dante, I essentially developed my Excel skills as I went along. Starting from knowing almost nothing about Excel, I simply experimented and/or looked up stuff whenever I was figuring out how to calculate the answer to some hypothesis or question I had. In that process, I’ve never resorted to macros, because I’ve always been able to get the answers I wanted without them.
In any case, my limited Excel abilities has a huge advantage for you: there are no steep technical learning curves if you want to adopt my Excel techniques.
Trader X’s email
Hi Kay - wondering if you have some advice about investigating my trades, and in particular the three questions from Tom Dante [here].
I’ve been using an online journal [that] has some lovely and quick stats, easy to change time periods and things, but I don’t think it’ll let me analyse the way I’d like…
[…]
I started popping trades in a spreadsheet, but frankly unsure what to track…
Basics like Entry date, price, quantity, stop loss
Stock info like ADR, avg. vol, $ vol, float (do these make a difference?) - sector?
Other stuff like MAE %, market indicator? (I’m currently using MMTW 10>20MA)
The exits I actually want to test
Should I have taken it? (Based on market indictor, was it a good setup on hindsight?)
A little paralysed by info overload, fear of amount of info being tracked and formulas to work out 😋
[…]
Getting started
The incremental approach
You’re starting in the right place: inputting your trades/data into a spreadsheet. But when you’re new to anything, it’s good to focus on consistency and incremental improvement over trying to do everything at once. This makes it more challenging to maintain as a new habit. Furthermore, as you’ve found yourself, it can intimidate or paralyse you.
Compare this to fitness: most people really should be walking at least 10,000 steps a day, but if they’re currently struggling to even hit 2,000 a day, it’s probably better for them to first aim for 5,000 as their daily target, then build it up in 1,000-step increments until they hit 10,000 a day. Such a gradual approach is far less intimidating and much more likely to be successful than to, figuratively speaking, jump in at the deep end.
For traders, Lance Beggs’ straightforward approach to continual improvement can work very well.
Start simple
I think it also wise to keep things simple and focused to begin with. In the early days, make sure you record every trade, but keep the amount of data you capture for each trade manageable. That ensures you really do capture all your trades. Over time, I’ve added columns to my spreadsheet — you can do the same. There are also columns that I no longer use because that data has served its purpose, or turned out not very useful to me.
Remember that your journal doesn’t need to be perfect. It’s simply there to help you review your trades. The goal is to constantly improve, however small those improvements are. That said, you do need to know where you need to improve. Your journal can help guide those efforts, but before you get too technical, it’s good to start with the basics.
The above is an extract from Trader X’s spreadsheet (‘Spreadsheet X’), which they permitted me to share. For context, the ‘target’ column is just 1x ATR, as opposed to a real target, and Trader X sets limit orders with stop losses attached to them, as they can’t watch the market at the open, but move their stop to the low of the day once they get a chance.
And just to pre-empt comments about that they could just excuse themselves to the bathroom or an equivalent — you really can’t in certain professions. Just among the traders who read this Substack, I know they include pilots, surgeons and teachers, to name but three professions in which you can’t just drop your work at will. Trader X has one such profession.
Missing columns
The above screenshot is not the full Spreadsheet X — it has more rows, as well as more columns, which cover stock data, market environment and exit strategies. All good data to collect and analyse! But it isn’t where I’d start.
First, you need columns for the following:
P&L (%)
P&L ($)
Win/breakeven/loss
You’ll need this data to be able to come up with answers to some of the more advanced questions you’re asking. Furthermore, you can use this data directly to gain valuable insights into your own trading, by using them to calculate average wins and losses (in both % and $ terms) as well as your strike rate. This then allows you to ask questions such as:
How does your average % win compare to your average % loss?
How does your average $ win compare to your average $ loss?
How do your % figures compare against your $ figures? For example, if your average win exceeds your average loss in % terms but not in $ terms, that tells you that your position sizing is working against you.
Collecting the basic data first enables you to gain extremely valuable insights into the simple things as soon as possible — the things that are straightforward to implement or fix, and can hugely improve your bottom line. I’ll give a concrete example based on Spreadsheet X later.
Excel formulas: P&L
Let’s start with adding the extra columns (L–N) I mentioned:
These are the formulas I used for row 3:
L3 (P&L $): =(J3-G3)*C3
M3 (P&L %): =L3/(G3*C3)
N3 (W/E/L): =IF(M3=0,"EVEN",IF(M3>0,"WIN",IF(M3<0,"LOSS",)))
An alternative for N3 (this treats P&L between -0.2% and 0.2% as breakeven): =IF(AND(M3<0.002,M3>-0.002),"EVEN",IF(M3>0.002,"WIN",IF(M3<-0.002,"LOSS",)))
Then copy and paste these formulas into the other rows.
Excel formulas: average win, average loss and strike rate
To figure out your average win and loss in both $ and % terms, use the following formulas (for context, Spreadsheet X goes up to row 41):
Average win (%): =AVERAGEIFS(M3:M41,N3:N41,"WIN")
Average win ($): =AVERAGEIFS(L3:L41,N3:N41,"WIN")
Average loss (%): =AVERAGEIFS(M3:M41,N3:N41,"LOSS")
Average loss ($): =AVERAGEIFS(L3:L41,N3:N41,"LOSS")
To work out your strike rate, use this formula:
=COUNTIFS(N3:N41,"WIN")/COUNTA(N3:N41)
How Trader X can improve
For Spreadsheet X, the above formulas return the following numbers:
Average win (%): 6.32%
Average win ($): $10.61
Average loss (%): -5.37%
Average loss ($): -$12.10
Strike rate: 23.08%
Aside from this having a negative expectancy (which Trader X knows, and I respect how they are obviously putting in the work to improve themselves), there are some other things that immediately jump out at me:
Their average win is only slightly larger than their average loss in % terms.
Their strike rate is low. That’s not necessarily an issue in itself, but combined with their current R:R ratio (based on their average win/loss figures), that does become a problem.
Although their average win exceeds their average loss in % terms, it doesn’t in $ terms. So the way they’re sizing their positions is not working in their favour.
Taking responsibility and enjoying the process
I don’t think numbers like the above are all that uncommon for losing traders. Unfortunately, I’m under the impression that it is rare for such traders to actually face their numbers — and with it, the hard truth — and do something about them.
But really: if you truly take responsibility for your trading, and tell yourself that you can achieve great results if you just show up every day and work hard, you’d be surprised at just how quick and much you improve. Consistency and smart, hard work go a long way in many activities, trading included. You will also massively help yourself, especially in the long run, if you can find a way of enjoying that work.
I actually like crunching my numbers. It’s my good fortune that I’ve always liked maths and puzzles, but what really helped me get hooked on rigorously studying my journal was making some early discoveries that permanently changed my trading and results — most notably, my MAE. More on that later.
Essentially, having experienced the thrill, the eye-opening moment of seeing my own numbers and how simple the necessary changes were to become profitable, I’ve been committed to journal analysis ever since. Essentially, it becomes a cycle:
Dive in.
See early results.
Motivation to dive in some more.
See more results.
Continue diving in, believing this will further improve results.
Etc.
I’ve experienced a similar cycle with other activities I pushed myself in. So I really don’t believe talent is at play here, just hard work, consistency and enjoyment of the process. Positive feedback — e.g. from your journal — will really help with that last point.
Control the downside
Back to Trader X. Focusing on item 1 from the list of things that jumped out at me from Spreadsheet X, there are realistically only two things Trader X — and anyone else with similar results — can do: win more or lose less (in % terms). Technically, you can also aim to do both, but in the spirit of incremental improvement, I think it better to concentrate on one of them first.
Unless your losses are already tiny (in % terms), I strongly recommend you focus on losing less first. Why? Because it’s much easier to control the downside than the upside. Furthermore, to quote Paul Tudor Jones:
“I’m always thinking about losing money as opposed to making money. Don’t focus on making money, focus on protecting what you have.”
The idea is that if you make sure you don’t lose money, you’ll eventually make money. Thus my focus on controlling the downside: if you’re a struggling trader, finding ways to lose less money should be your priority.
With that in mind, I strongly recommend you introduce a maximum stop (in % terms) into your trading. Maths play a part in this, since losses work geometrically against you, but deciding your cut-off based on that alone can seem a bit arbitrary.
MAE
My suggestion is that you look towards your maximum adverse excursion (MAE) for determining your maximum stop. As an alternative, you could look at setting a hard cut-off at half your average gain, but using your MAE makes more sense to me, as this is a far less arbitrary approach. A fixed proportion of your average profit doesn’t account for how your stocks move, and may not even make sense for your strike rate.
What is the MAE?
In short, it is the % that your winners move against you before they turn into winners. For example, I bought $NVDA at $431.19 at 9:34 EST on 12 July 2023. The furthest the stock moved against me was $429.44, which makes for an MAE of 0.4%. My stop loss was at $428.65 (0.6%).
Why calculate it?
To me, there are two big benefits to calculating your MAE:
It tells you how wide a stop you really need. If your winners never move against you further than 2%, for example, why set >3% stops?
If it turns out you have a very large MAE, that tells you that you need to work on your entries.
When I looked into my own MAE for the first time, I was surprised to discover that I hadn’t had a single winner or possible winner (stocks that gained at least 2R before stopping me out at breakeven or worse) move against me further than 2%. This was irrespective of ADR or setup. And most winners moved <1% against me.
I’ve never set a wide stop since. And nowadays, I usually set a <1% stop, which enables me to trade with big size while managing my risk. This also makes it easy to get good R:R trades.
There are people who find this crazy. And maybe it is, for them. But this is what my own hard numbers told me, and when I acted on that information, I immediately saw my results improve. Moreover, I didn’t find myself getting constantly stopped out of winners due to this change. If I did, I’d absolutely be adjusting my rules, ensuring I improve my entries and/or widen my stops.
This is what I like about analysing your own journal. It gives you a level of insight into your trading and strengths that no book or video can. Resources such as this Substack can help give you ideas and teach fundamental principles (or so I hope), but they can’t tell you what your edge is — you’ll need to get that information from your journal.
My strength is picking good entry spots, especially on slower names. But I didn’t even know this about myself until I took a hard look at my journal. And I’m still constantly analysing it, trying to find ways of improving my exits, which is the main thing I’m currently focusing on. Sometimes, I also uncover things I wasn’t even looking for, like the discovery I shared in this recent tweet.
Relating this to Spreadsheet X
To bring this back to Trader X, I strongly recommend you look into your MAE, with a view to determining your maximum stop in a non-arbitrary way.
If this is already at a reasonable level, you can directly use your MAE as your cut-off. Otherwise, take a closer look at your winners, and what patterns your MAE may have within them. Do you generally have a low MAE but have a few outliers? What do they have in common — for example, are they a particular setup, ADR, market cap or industry?
Whatever pattern you uncover, use it as the starting point for a deeper investigation. For example, if you trade high tight flags (HTFs) and episodic pivots (EPs), and based on your MAE need a wide stop for EPs but not HTFs, what is your overall expectancy when broken down per setup? If you discover that you have a better expectancy on HTFs than on EPs, perhaps trade HTFs only, using your MAE as your maximum stop. This has many benefits:
Smaller losers, which — particularly when paired with a low strike rate — massively improves your bottom line.
Better risk-to-reward ratios.
Opportunity for bigger positions.
Improved confidence — you’ll be trading while knowing, not thinking or hoping, that the numbers are on your side.
Carving out your niche — in my example of trading HTFs only, this makes it much easier to master that setup (compared to trading multiple setups). Remember: you only need to master one setup to be successful in the markets.
If you find no pattern, then you need to work on your entry techniques. Why is it that you need such a wide stop? Is it due to stock selection or buying extended stocks, for example?
How to calculate your MAE
First, filter for your winners (column N in the screenshot below). Then create two new columns: one for manually entering the worst price (i.e. the price furthest from your entry in the wrong direction), and one for a formula calculating your MAE in % terms.
I’ve done this for the first few winners in Spreadsheet X — see the screenshot above. To go through each new column (O–Q) individually:
Column O/‘worst price’: manually enter the furthest the price moved against you. Revisit the charts to get this information, which you can combine with generally reviewing your trades — two birds with one stone! (For Trader X’s benefit: I guessed your intraday entry and worst price based on the 10-minute charts.)
Column P/‘MAE (%)’: =(G8-O8)/G8. (For row 8; copy and paste to the other rows.)
Column Q/‘stop (%)’: =(G8-H8)/G8. (For row 8; copy and paste to the other rows.) This is another column that really should already be in your spreadsheet. I added it here for easy side-by-side comparison of the actual stop against the MAE.
Obviously, the data set I’ve been given is pretty small, and I only looked at the first three winners. However, if the pattern the above screenshot shows holds up for Trader X’s trading in general, this means that they can trade with much tighter stops than they currently are.
And the impact is significant: if their maximum stop had been 4%, Trader X’s overall performance across the 39 trades shared with me would’ve improved by 33%. I’d call that a good result, particularly considering how simple this metric is. It really doesn’t take long to check your MAE, yet doing so — and actually acting on the information you obtain — can massively boost your performance as well as your confidence.
Going deeper into the numbers
Let’s say you wanted to look at your numbers — including your MAE — by setup:
Clearly, to answer any such questions, you need to add a column for your setup (column R in the screenshot above). I haven’t gone through all of the trades or asked Trader X what setups they trade, I just completed the column with either ‘EP’ or ‘HTF’ at random.
After entering this data, you can start to crunch some numbers. I created table O43:V45 to help you get the idea, but you need to decide for yourself what numbers you might want to look at. For clarity: up until this point, I have been using Trader X’s real numbers, but not for this table because they haven’t shared the necessary data with me.
But suppose these were Trader X’s real numbers. I’d draw two big conclusions from this:
I’d set a maximum stop of 5% or less.
I’d only trade HTFs.
Again, this type of analysis is pretty simple to do, but can give you an incredible amount of insight into your own trading. If you wanted to recreate table O43:V45, you need the following formulas for the HTF row (substitute ‘O44’ for ‘O45’ to get this data for the EP row).
Avg win ($): =AVERAGEIFS(L3:L41,N3:N41,"WIN",R3:R41,O44)
Avg win (%): =AVERAGEIFS(M3:M41,N3:N41,"WIN",R3:R41,O44)
Avg loss ($): =AVERAGEIFS(L3:L41,N3:N41,"LOSS",R3:R41,O44)
Avg loss (%): =AVERAGEIFS(M3:M41,N3:N41,"LOSS",R3:R41,O44)
Lrgst MAE (%): =LARGE(IF(R3:R41=O44,P3:P41),1) — use Ctrl + Shift + Enter
Avg MAE (%): =AVERAGEIFS(P3:P41,R3:R41,O44)
Total P&L ($): =SUMIFS(L3:L41,R3:R41,O44)
Of course, you can look into whatever data you want (e.g. adding strike rates and R:R), broken down by setup, ADR, market cap, etc. The variations really are endless. But make sure you start with the basics, see what you uncover from any qualitative analysis (by looking through your trades again while you’re adding new columns, for example), then confirm it quantitatively.
Trader X, I noticed that your two biggest winners (by some distance) both have a very high ADR, and barely moved against you at all. So if I were you, I’d create a new column for ADR, then look into your MAE and other metrics by ADR range (e.g. <5%, 5–10%, 10–15% and >15%). If you can trade really volatile stocks with really tight stops, that could be one heck of an edge.
But this is only a qualitative observation on very limited data, and about someone else’s trades to boot — you’ll need to confirm this quantitatively for yourself with much more data. Remember: the larger your data set, the more reliable your findings.
High-level guidelines
I truly do believe that every trader should keep a journal and regularly review it. Taking a hard look at your numbers marked a major turning point for numerous traders, myself included. And you really don’t need to be very skilled at Excel, or even come up with overly complicated questions, to end up with useful insights that can massively improve your performance.
Put differently, I think doing (at a minimum) basic analysis of your journal gives you an incredible return on investment, and makes sure that every trade you place, winner or loser, directly contributes to your education and development as a trader. It ensures you aren’t just placing more and more trades, without really knowing what actually works, and what doesn’t.
That said, there are some things to bear in mind:
Focus on frequent, incremental improvements, rather than occasional ‘sprints’.
Make sure you’re collecting and analysing your basic data first:
Entry price and date
Exit price and date
Ticker and setup
Stop price and %
P&L in $ and %
Target price and %
R:R (plan and realised)
Position size
Formulate the questions you’re seeking to answer based on what you’ve instinctively sensed about your own trading, e.g. through qualitative analysis.
Tackle one question at a time — remember: seek incremental improvements!
To also keep the amount of data you’re gathering manageable, determine exactly what data you need to answer your question, and collect that data only. You can always add more later, and remove or stop collecting what you no longer need because you’ve already answered the question(s) associated with it.
If you’re feeling intimidated, just make a start with the basic stuff. You can always add things later. Again: your journal is just there to help guide your improvement actions. Not to ensure perfect trading. Think of it as a risk assessment: that too is only really intended to guide your mitigations/responsive measures. In itself, it doesn’t ensure health and safety, or security, or whatever the context is — but without it, you couldn’t take efficient actions. That’s pretty much how I regard journal analysis.
If at all possible, make the process enjoyable for yourself.
Insight from Qullamaggie’s Chat With Traders interview: journaling in a smart way
As you’ve probably already seen, I recently published notes on Qullamaggie’s Chat With Traders interview in two parts: breakout mechanics and psychology/building conviction. However, I left out one section that I felt didn’t quite fit in either part, but does fit here.
The host asked Kristjan at 52:56 about his ratio of average win to average loss, considering his low strike rate. Kristjan says that he doesn’t know the specific numbers, because he gave up looking at the “micro stuff” in his trading years ago — he just knows that perhaps 20% of his trades make up the majority of his profits. He goes for ‘home runs’.
Kristjan explained at 53:45:
“I think if you get the big stuff right, you don’t have to worry about the little stuff.”
Despite being one of those people that does spend a lot of time looking at the “micro stuff”, I do get where Kristjan is coming from — I used to diligently track the MAE on my actual and missed winners, for example, but don’t anymore.
As discussed earlier, tracking the MAE was crucial to identifying my biggest edge — being very good at my entries. My winners hardly moved against me at all. This made me realise that I can place very tight stops, and can do so with confidence, which gives me very good R:R. It also means I get stopped out of losers early, which allows me to quickly roll that money into something else.
I have not tracked my MAE recently, however. I don’t really have to, because I still place really tight stops that don’t get triggered on trades that work in hindsight. On my last 10 swing trades at the time of writing, my stops ranged from 0.4–1.2% (0.7% average) with a 43% strike rate (3 winners; 3 breakeven trades; 4 losers). So long as my numbers remain like this, I’m not too bothered about my MAE, and rather focus my efforts on improving my weaker areas, like selling.
The point of journaling and analysing your numbers is figuring out how you can improve. At the very least, identifying where you need to improve, even if you don’t immediately see how to do it. It isn’t an activity where you mindlessly fill in numbers you don’t really need. Your time is a precious commodity. Use it wisely!
Conclusion
I hope this stack was helpful to you, especially if you want to analyse your journal but are unsure how to approach the task. Again, feel free to leave a comment below, contact me on Twitter or email me at kayklingson@yahoo.com if you have any questions or feedback.
Also let me know whether I should do more stacks like this. For example, I could explain how you can check whether moving up your stops or other types of trade management techniques improve your bottom line or not.
More content like this
All my quantitative analysis articles are available here.
All my Qullamaggie notes, including the Chat With Traders interview notes, are available here.
The Trading Resource Hub’s full archive is here.
Another awsome insights