Know Your Numbers: Quantitative Analysis Techniques for Excel-Based Trading Journals

A tool for building conviction, maintaining discipline, finding your edge, and more

In the closing remarks of my 18 May 2023 Qullamaggie stream notes, I mentioned that I’d developed some quantitative analysis techniques for Excel, and asked whether people were interested to learn more about them. It seems the answer is yes, so I’m using my holiday — while I’m not available to write about current Qullamaggie streams or other current content — to write about these more timeless techniques, and how they came about.

The importance of journaling

You don’t have to look particularly hard to come across the lesson of how important journaling your trades is.

David Ryan

For example, David Ryan frequently discusses how, in his early days (1983–84), he spent an entire weekend studying every trade — and mistake — he made over the course of about a year, when his account went from $52,000 to $16,000. Through that exercise, he realised what he was doing wrong: trading too aggressively for that market environment, and buying stocks that were too extended.

Feeling confident after his studies and determined to remain disciplined, he entered the 1985 US Investing Championship and won with a 161% return. The rest is history.

In short, analysing his trades marked the turning point in his trading career.

Mark Minervini

Mark Minervini’s book Think & Trade Like a Champion has an entire chapter called “Know the truth about your trading”, where the following is printed in bold:

“By keeping track of your results, you will gain insight into yourself and your trading that no book, seminar, indicator, or system could ever tell you.”

As Mark pointed out two sentences later: “Your results are your personal truth”. If you know how you are really doing — where you are doing well, and where you are not — you know what to focus on when trying to improve yourself. He also talks about letting your spreadsheet (journal) make its way into your trading. If you’re sitting on a big loser, remind yourself that you’re going to have to log that loser, and that it’s going to hurt your average loss and bottom line.

But the flip side is true too: if your average win is 10%, and you’re sitting on a 30% paper profit, consider whether it’s time to take those profits, since it’s a good idea to “be mindful of the reality of your own math” (to quote Mark again). Do you want to risk that 30% dropping back to your average 10% (if not lower), or do you want to take that profit and boost your average gain?

Yes, we’re traders. We trade the charts and price action, not our P&L. Nevertheless, our real results matter — to our state of mind, if nothing else. To be successful at trading, you have to trade with confidence and conviction. If you’re still a struggling trader, I’d argue that consistency is a good thing to aim for to build that confidence — on top of regularly studying charts, of course.

Tom Dante

But my real inspiration for this — the person that really made the difference in getting me to journal consistently, analyse it effectively and truly benefit from that exercise was a British trader some of my readers may have never even heard of: Tom Dante.

I first learned about Tom via the Chat With Traders podcast, where he talks about his background in more detail, if you’re interested. If not, then let me at least say that he’s incredibly frank, has a refreshingly no-nonsense attitude and, interestingly, trades completely different markets to myself (and probably most other Qullamaggie students too).

I highlight that last point as interesting, because in spite of our totally different styles, I learned some very valuable lessons from Tom that I did not learn anywhere else. I can honestly say that coming across Tom marked a major turning point in my trading journey, making things click that the likes of David Ryan, Mark Minervini and even Kristjan Kullamägi couldn’t — at least, for me. All this stack can do is speak for myself, and hope that it introduces a tool that at least a few of you will find helpful.

Play to your strengths

We all have different personalities, and must adapt our trading styles to them. This business is hard enough as it is — don’t make it any harder by forcing yourself to become someone you’re not, or to adopt a style that fails to take advantage of your natural strengths and aptitudes.

That said, like it or not, trading isn’t a hard science. Certainly reading and interpreting charts — a critical part of trading — is more art than science. If you want to become a good trader, you have to spend many, many hours studying hundreds or even thousands of charts.

Yet my personality is such that I like to see the hard figures before I truly, deeply have faith in something — particularly during the times when the going gets tough, as it inevitably will for anything that’s worth pursuing. If I have the hard numbers for something, and they correlate with what I instinctively already sensed, I’ll trust that instinct more, as that gives me confirmation I’m not suffering from, for example, recency bias.

However, I wasn’t really sure how to obtain those hard numbers, or even what I was actually looking for, beyond the basics like average P&L and strike rates (batting averages) — the metrics that Mark Minervini details in his book. Until I watched this video by Tom Dante:

Now, to be clear, I’m not saying you should forget about charts. Absolutely not. But what I am trying to do is introduce a new tool to your toolbox that made a huge difference to me, and may help you too, particularly if you’re like me — a fan of hard numbers and statistics.

I think it’s also worth adding that if you’re struggling despite having already put in the hard work of studying thousands of charts, analysing your journal is probably the next place to look.

Key points covered in Tom’s video

Always bear in mind that the purpose of a trading journal is to help you improve, since:

• Trading problems don’t solve themselves; and

• You never stop learning, especially when it comes to trading.

At this point, you’re probably thinking: ‘yeah, no kidding’.

Yes, most traders know the above. The problem is that they mostly don’t act on that knowledge. Many traders don’t even religiously journal. But even if they do, as Tom puts it (7:45):

“There is a […] huge f**king difference between recording and analysing your trades. Some people spend all their time writing down their trades […] in a book or in a blog or in an Excel sheet or whatever — and then, believe it or not, they don’t actually review them.

“If you have a record, and you don’t look at it frequently, you might as well not have one. It’s not a list of trades to stick in a bleeding folder on your computer to read back on it one day like a f**king diary. It’s there for one reason and one reason only, which is to make you a better trader.

“If you’re winning, it’s there to make you more money, and if you’re losing, it’s there to turn it around, and make sure that you can perform and start making money.”

(Yes, Tom does swear a lot.)

Three big questions

With that in mind, Tom puts to you three questions you should seek to answer when reviewing your journal:

1. How can I make more money from my winners?

2. How can I lose less money on my losers?

3. How can I generate more winning ideas?

Now, my own eye-opening moments came from focusing on the first two questions — especially the second question, due to a combination of the market environment we were in (a lot of volatility, with breakouts typically failing) and the simple fact that protecting your capital is perhaps the most important aspect of trading.

To be clear, there’s nothing wrong with the third question, especially if, like Tom, you trade on a lower time frame than I do, which automatically means looking for new trades much more frequently. But, for the moment, I prefer to just get really good at one or two setups, then only execute when I see those setups appear.

Suggestions for journal analysis

In his video, Tom provides several suggestions for concrete things to look at, including:

• Do you make more money by actively managing your trades than you do by walking away from them?

• Particularly if you trade without targets, how much of the opportunity are you capturing, and how can you capture more of it? What trade management techniques may make you more money, or help you lose less of it?

• What is your maximum adverse excursion (MAE)? In other words, how far do winning trades move against you before turning profitable? If they don’t move far offside at all, could you tighten your stops? But if they do move against you quite significantly before turning into winning trades, that may suggest you need to improve your entries.

• If you trade different strategies/multiple setups, is one making you more money than another? If so, how can you position size accordingly?

• If you are already varying your position sizes, what happens to your results if you don’t?

• Are there any recurring patterns in your losers? For instance, on balance, do you lose money when you trade gap ups? Or with certain size gaps, say >20%?

• Do you do better at certain times of day than at others? For instance, do the trades you place within the first hour of the open do better than trades you enter later during the day? (If so, consider trading just at the times you do well at, or at least trading bigger size at those times than later in the day.)

• Do you do better trading certain days of the week than others? Or trade better certain months than others? What about during earnings season vs when earnings are quiet?

• Are there particular markets/sectors/industries/market caps/etc. you do better in than others?

(I have padded these out a bit, and adapted some a little to Kristjan’s style of trading. But they’re all based on Tom’s ideas.)

This list gives you an idea of the types of questions to ask yourself, and the sorts of data you should seek to collect. When coming up with questions, consider where you think you might be going wrong, then confirm (or refute) it with hard data. You can also look at what other traders claim, and check whether they’re correct — or at least, if what they say applies to your trading too.

Of course, to really benefit from this exercise, you need to draw some sort of conclusions and incorporate what you’ve learned into your trading.

A fundamental issue to many trading problems

Before I get into specific Excel techniques, I really want to share another quote from Tom — the one that struck perhaps the biggest chord with me when I first watched the video, and had a lasting impact on me. I really feel Tom hit the nail on the head with this one.

For context, he said the below in relation to the question about active management vs walking away, but I feel that the principle can be applied to an awful lot of trading problems (13:39):

“Getting out early is just one of those potential problems where people always seek the easy way out: to blame their psychology, and they start saying s**t like ‘oh, I’m so psychologically sc**wed up, I keep coming out my trades so early ‘cause I get scared, I get impatient’, ‘oh f**k, I better read Trading in the Zone again’.

“The reason you came out early is ‘cause you do not categorically know, over a large sample size, that holding is the right thing to do, right? [Emphasis added]

“If you know that, and you’re getting out too early, you don’t need a f**king psychologist — it’s quite simple: you’re an idiot.”

The bold text is — to me — the really important bit. At the time, I didn’t know whether moving up my stops was the right thing to do, or whether focusing on higher ADR stocks actually made me more money, or whether getting back into trades actually paid off… I could make a really long list here. But the point is this: I knew what all the great traders and teachers were recommending, sure — what trading ‘best practice’ was, if you will — but I hadn’t a clue whether that really worked for me.

I do need to caveat the above paragraph with this: the answers to the questions I raised will very likely differ depending on the market conditions at the time. Nevertheless, I didn’t even have categorical answers, based on my own hard data, for trades limited to a certain period, never mind for my trading overall.

Quantitative analysis techniques for Excel

So, finally, some actual techniques and formulas. And by the way, these are completely my own — Tom doesn’t talk about this at all, and I don’t know of anyone else discussing this sort of thing, either.

I don’t want this post to get too long — I can always elaborate in a future post if there’s demand — so will only give a few examples, just so you can get the idea. The exact data points to look into will (or should) be different for everyone, anyway. But I will address a couple of topics I think not uncommon for Qullamaggie students, in the hope that they will resonate with you better.

Just a reminder: you do need a decent sample size to be able to do this type of analysis.

Overtrading

Kristjan regularly says that he’s trying to trade less. Plenty of other market wizards are also constantly warning about the dangers of overtrading.

It’s quite a strange concept, when you think about it. We (probably) all know that it’s hurting our bottom line, yet are often inclined to do it — even though it actually takes more effort than trading less.

The table below is what helped me, using the data I was already collecting in Excel. Which, by the way, is data I think everyone should already be collecting, meaning that you can apply these formulas pretty quickly to your own spreadsheet.

(Not my real numbers, but this does reflect the general pattern I saw when I first looked at these numbers. And for clarity: ‘0 trades’ meant I was holding positions on those days, but didn’t place any new trades.)

Data you need

1. Day-to-day account % changes. This is easy to get if you’re already tracking your day-to-day capital changes.

2. No. of trades placed every day. If you aren’t already tracking this information, just apply a COUNTIFS formula, where you count the number of times the entry date in question appears (assuming you log your trades in Excel as one row per trade).

Formulas for this calculation

Column B

B2 uses this formula:
=AVERAGEIFS(J:J,K:K,0,F:F,"<>EVEN")

Column J refers to the column of the day-to-day account % changes, column K to the no. of trades placed for the day, and column F distinguishes days with open positions from days where I was 100% in cash, returning either “PROFIT” or “LOSS” from open-position days, and “EVEN” if I placed 0 new trades and my account % change was 0.00%.

You can copy and paste the B2 formula into cells B3–B7, then manually adjust the “0” accordingly.

Column C

C2 uses this formula: =COUNTIFS(F:F,"PROFIT",K:K,0)/(COUNTIFS(F:F,"PROFIT",K:K,0)+COUNTIFS(F:F,"LOSS",K:K,0))

You can copy and paste the C2 formula into cells C3–C7, then manually adjust the “0” accordingly.

Variations

Zoom out a little and track this data week-to-week, track this information in $ rather than %, or look up the total (rather than average) impact in % or $.

How to incorporate this into your trading

So how can this be another tool in your toolbox, besides making you aware of your real numbers?

Well, my suggestion is that you print your table, and stick it somewhere in your office where you can clearly see it — above your monitor, for example. Then, every time you’re tempted to overtrade (since we usually know that we’re doing it, but press ahead anyway), look at your printed numbers. Is placing that extra trade — particularly when it’s badly thought-through — really a good idea?

If you’re into trading psychology, I’d argue that this is a rather practical way of addressing that.

Market caps

Kristjan regularly recommends that traders with small accounts look towards smaller-cap stocks, but I’ve heard many other good traders (Oliver Kell, for example) say how they don’t tend to do too well on those due to their volatility.

So, I figured it was worth checking this on my own trades (fortunately, I had already been tracking market cap in my trade log, but if you aren’t already, it’s pretty straightforward to add — just throw up a new column and input the data). Having not had an intuition about this one way or another, I wasn’t expecting to find much. I was wrong.

The below are my true figures at that time (years ago now), when I was consistently losing (though I’m proud to say I’ve managed my risk from a very early stage, so I’ve never blown up). Forgive me for being uncomfortable about sharing $ amounts in public.

What I observed:

• I do indeed make higher gains, both in % and $ terms, in micro and small caps. However…

• My losses, again in both % and $ terms, were much higher too. Combined with a poor strike rate, the net result was a negative P&L.

• My performance in mid and mega caps wasn’t great either.

• Most importantly: I was profitable — comfortably profitable, in fact — in just one market cap category: large caps.

The next question to ask is: was this the result of the market environment at the time?

Well, partly. It was a time when large caps performed better than smaller ones, but it was also a period when mega caps did well, and I was rubbish at those. Moreover, I had data for periods when large caps weren’t outperforming small caps as much, which I checked separately and got the same pattern for.

As to why this is — I suspect it’s to do with simply being better with trades where I can place tight stops. I’ll be honest: I am trying to get better at the smaller caps, but am careful to size smaller on those than when trading large caps. I hardly touch mega caps anymore.

Of course, this is just what I found out about my trading. You may find something different, using the formulas outlined below.

Formulas for this calculation

You should already be tracking metrics like average gain, average loss, strike rate, and all the other metrics covered in the table above. Take those formulas you already have, and add conditions to them. So, for example, if your general formula for your average gain is this:

=AVERAGEIFS(DP:DP,DO:DO,"PROFIT")

Then the formula in B2 would become:

=AVERAGEIFS(DP:DP,DO:DO,"PROFIT",XX:XX,A2)

Where column DP is your % P&L, column DO returns “PROFIT” or “LOSS”, and column XX specifies the cap size (in this case, “Micro”).

Variations

You can pretty much apply this formula to any sort of ‘inherent’ stock variable, e.g. price, ADR, sectors or industries, and so on. Other variables, like the time of day, day of week or month entered, as well as the setup traded, work too. There truly are endless variations.

How to incorporate this into your trading

I’ve pretty much answered this with my own case study: where you see you perform better in one category over another, take advantage of that! Trade more in that category, trade less in the others, and if you do trade in the others, trade smaller until you see evidence of improvement in those.

Stuck for ideas? Try the demon finder.

If you’d like to try my techniques but aren’t sure what questions you want to answer, Tom Dante’s demon finder may help:

This is a very simple spreadsheet that is very effective at identifying your worst ‘demons’, which in turn tells you which trading problems you need to resolve first. You can download it for free on Tom’s website and, if you like, change the default demons.

Why I think manual data entry is better

One final thing: I’m all for using Excel formulas to speed up calculations, but I do believe that manual data entry is the way to go. Why? Well, before I religiously recorded all my trades, I lacked discipline. I took stupid, unplanned trades that, of course, lost me money, particularly over time.

How did I fix that? By reminding myself that if I take a stupid trade — one that I knew I’d regret later — I’d have to not only manually input it into my journal, but also look at it every time I reviewed my trades. After doing that a couple of times, I found that process so painful, I just stopped doing it to avoid having to repeatedly go through it. Plus, I could see the obvious negative impact on my bottom line.

Had I used some type of software where you can just import your trades, I may well have got there too, but I suspect it’d taken me longer — and cost me more money.

Conclusion

Of course, all the above is just what helped me. I can’t speak for any of you, but I hope it’ll be of use to at least some of you. If you have any questions, please do leave a comment below, contact me on Twitter or send me an email at kayklingson@yahoo.com. I’ll do my best to help (but possibly not right away, as I’m on holiday).

More content like this

All my quantitative-analysis-related posts are available here. If you’re more interested in my Qullamaggie notes, they’re all available here.