In this tutorial on trade analysis we’re going to have a look at how you can analyze your own trading results with Excel / Google Sheets for a better trading performance. We’re going to run through some very basic tables, charts and graphs that you can easily put together yourself. The gathered data will give you invaluable information and insights into your own trading. This can help you see how you’re doing compared to previous and future time periods.
- Why professional traders analyze their trading results
- Creating a spreadsheet for trade analysis
- Tracking the daily profit high as important metric
- Using basic formulas to analyze your trading results
- Tracking average trading results in your spreadsheet
- Using graphs and charts to gather additional trading insights
- Utilizing data to analyze your trading strategy
- Track your trading results from the very beginning
Why professional traders analyze their trading results
Every trader goes through cycles. You’ll be on top of the world one minute, you’ll be at the bottom the next. It’s very important to be able to manage your emotions but also to be able to see when your emotions are coming. You need to realize when your trading is affected by your emotions. Valuable insights like knowing that you’re trading very well and being able to act accordingly (increasing position size, taking the extra bit of risk, etc.) along with knowing that you’re trading poorly (reducing position size, reducing risk, etc.) can all be gained from good trade analysis.
However, as with many things in trading, nobody is going to do the work for you. You have to do the trade analysis yourself by utilizing Excel or other tools, applications and trading software. Using a software tool for trade analysis usually is a lot easier, faster and more powerful than writing it down on a piece of paper. Keeping your notebook as a trader is important for its own rights so you can write down your observations throughout the day. However, for trade analysis Excel or similar tools are the only way to go and that’s what we’re going to be using in this tutorial.
My own background as trader at a proprietary firm
My own background is in trading and trading futures professionally with a proprietary firm. Self analysis was something that was taught to us from the very first day when you arrived. When you’re starting off as a trader, be it with a firm or personally, you’re likely going to have a bad period. Otherwise you can consider yourself extremely lucky. No matter your style of trading, you’re usually going to have more losers than winners and you’re going to have bigger losers than winners. It’s important to know when that starts to even out and when you start to take off.
Creating a spreadsheet for trade analysis
Having your own analysis is the only way to figure out where you’re losing money in order to become profitable. The spreadsheet that we’re going to use here is a kind of basic version of what we would have been using at the prop firm I’ve worked for. I’m going to run you through it now. It’s very standard, we’re looking at one week worth of trading, Monday till Friday in August. It’s of course totally up to yourself, but from my trading experience and the way that I used to trade when I was with the prop firm, we used to trade in the morning session and the evening session.
These trading sessions were essentially different markets. You might be trading the same financial instrument but the style of trading that would go on in the first versus the second session was very different. You had to adjust your own trading plan and risk management accordingly. In the spreadsheet I’ve split out the sessions into AM (morning) and PM (evening). If you want to split into open and close, three sessions or whatever works for your own trading style, you can do that accordingly. This is just the base that we’re going to be working from for this tutorial.
I’ve taken three main products, the SPY ETF, the Nasdaq QQQ ETF and oil. Again, you might be trading multiple products. I myself was trading upwards of 15 or 20 financial instruments so the spreadsheet was absolutely massive. We’re going to keep it simple here. We’re going to work from three fairly popular products that everybody will know and lots of people will have experience in. So now we have the products that we’re going to track, the sessions we’re going to track and we have the totals for each day coming up at the right side of the spreadsheet.
Tracking the daily profit high as important metric
The profit high of each day is a very important metric. It’s another one that was absolutely essential for us to track. It’s all well and good knowing that you made $500 or $5,000 on a certain day. However, what was the high point of your trading day? Were you at $1,000, were you at $10,000? Being able to know how much you then take home from the high is very important as a professional trader. That’s why we track it in the F column of the spreadsheet.
Ok, here’s our trading week filled in so we have our data for the morning sessions across SPY, Nasdaq and oil. We have our evening sessions tracked for all five trading days of the week, giving us our total for each product. In SPY this week we made $980, in the Nasdaq we made just under $1,900 and in oil we made just over $1,000. The total for each trading day combined gives us a weekly P&L of $3,920.
Using basic formulas to analyze your trading results
For those of you that are new to Excel or complete beginners, creating such a spreadsheet for trade analysis isn’t tough once you get stuck into it. Almost everybody is going to have a Google account. Google Drive is going to allow you to have a Google Sheet, which is what we’re working with here. It will have the exact same functionality as Microsoft Excel in the Office Suite. I’m just going to run you through the formulas very quickly, just so you know exactly what you’re going to be working with in your spreadsheet.
The first cell of the Total column, which is kind of a merged E4 and E5, we’re working off an equal sum B4 to D5. We’re equaling sum the first both sessions for the three products. We’re doing the same for each trading day in the spreadsheet. The totals for the products are working off a sum again. We’re just going vertically now as we’re going after B column, after C column and after D column.
Tracking the percentage of the daily profit high
We’ve now also populated in our high of the day figures in the F column. We’re seeing on Monday we hit a profit high of $850 and took home $755. On Tuesday at a high point we’ve made $900 but we took home only $685 and so on. We’re tracking the percentage take home then based on the high in the G column. Obviously, Thursday was our best trading day in terms of how efficient we were. We finish at the profit high which is always what you want to be aiming for as a trader. The preceding day was when we took home the smallest percentage of what we had made that day.
The percentage of the daily profit high is a very important figure to track. You are more likely to see when you’re starting to hit a bad run, when you start giving up more and more of your daily high. If you start seeing this percentage figure dropping as you go through the various days or weeks, depending on what your time frame is, you should be starting to understand that even though you might not realized it, you’re giving up a lot of your daily or weekly P&L.
This is likely going to have a negative effect on your trading performance. It’s probably going to manifest itself into taking on more risk. You’re starting to lose control over your trading and you drive home bigger losers. It can potentially result in a complete disintegration of your entire trading plan. One of the big selling points for doing your own self analysis is being able to realize when a bad run is starting in order to act accordingly. Tracking your high and the percentage take home at the high is one of the best metrics to get a view on that.
Tracking average trading results in your spreadsheet
Now we’ve brought in our averages on rows 22 and 23. Just like we were using the equal sum functionality, here we’re going to be using the equals average. It’s a very same principle, we’re averaging all the selected cells using commas to separate them. What I’m trying to do is get a sense of in-depth view and a snapshot at the same time of what I’m getting in my morning and evening trading sessions for each product.
For example across the week in SPY, I know in the mornings I was pulling in on average $311. I know on average in the evenings in SPY I was losing $115. You have the same overview for the other two products. I was making a small amount in Nasdaq in the mornings, doing fairly well in the evening sessions. My morning and evening trading sessions in oil were fairly similar. The strength of tracking your average trading results in your spreadsheet is that you get to see figures and trends that aren’t immediately obvious. You’ll get to see exactly what you’re doing and what each morning and evening trading session means to you and your P&L.
Let’s look a little deeper into the formula we’re using for those of you that are getting started with Excel and spreadsheets. Because we’re working off the average for SPY, we simply selected the cells that are related to SPY in the morning. In this instance these are B4, B7, B10, B13 and B16, using a comma to separate each of them in the equation. We did the same for the evening trading session and then for the other products at the column across on the same rows. That way you’ll get the averages for each of the financial instruments.
Using graphs and charts to gather additional trading insights
As you track each trading session and each day, you start getting a small bit of insights through your averages per session, per day and per high. You start to get the finesse once you start pulling that in two graphs and charts like we’ve done in our example at the right hand side of the spreadsheet. In the table we’ve tracked our morning session total and evening session total by just using the sum function and the appropriate cells with a comma in between.
We then pulled up a chart or graph based on this table and the columns that we see are broken down into morning and evening session. We can see straight away that in SPY we’re making decent cash in the morning. However, we’re losing in the evening trading session. In Nasdaq we’re making good cash in the evening. However, very little cash being made in the morning. Oil seems to be split fairly close to 50/50.
Utilizing data to analyze your trading strategy
As you become more aware of your own trading styles, habits and performance, the insights you might start to pick up from a chart based on your trading results would be like: “What’s going on that I’m making decent cash in SPY in the mornings, but I’m actually losing the evenings compared to Nasdaq where it’s almost vice-versa. I’m making very good cash in the evenings but not very much in the morning.” What’s the difference in SPY? Is SPY moving aggressively in the evening time versus the morning? Are you taking on too much risk and need to adjust your risk management?
Do you need to have a separate plan for trading SPY in the evening time to avoid the losers? Likewise, why are you making cash in the Nasdaq on the evening, but not in the morning? Is it more inline with your own trading style in the evening and can you run your winners more? Is there too much volume in the morning? Are you being shaken out? Are you taking only very small winners? What’s the big difference between the two sessions and the two products?
In oil you’re making good cash in both sessions. However, only about half as much as you’re making in the other two products. Obviously you’re in touch with the market, but maybe you’re not taking on enough risk. Maybe you shouldn’t be afraid of taking on additional bit of size on oil in both sessions and allowing it to run a little bit more.
Focus on profitable trading sessions
Insights on your trading are very useful and you can make adjustments immediately. For example if you see that you’re losing money in SPY in the evening time, who says that you can’t stop trading SPY in the evening? Let’s not be afraid to sit that one out and let’s see the difference it might make to your P&L. We’re getting about a 15-16% jump in P&L immediately and there’s no telling the difference that the evening session might have in the other products.
Losing all the time in SPY in the evening might still be holding us back from Nasdaq in the evening, even though we’re making money there. So without the losing trades in SPY, the percentage and size of winners might go up in both the Nasdaq and oil. Not only are we not losing money, but we might start making more money in the other products as a result of the benefit to our trading psychology. Not having to deal with the losers in SPY comes on top of the already realized extra money we’re going to have from not giving back the morning sessions.
Track your trading results from the very beginning
It’s so important to track your trading performance from the very beginning. This is crucial as it will stand to you as you grow in size, the amount of financial instruments traded and in your daily P&L. We’re going to look at my own spreadsheet from when I started off trading with the prop firm. This is one of my first months live and you can see I was already trading multiple products, broken down across the top.
Just like we worked out in the previous example, I split it off by day and into a morning and evening trading session. There must be 30-40 products in the spreadsheet that I was dipping into. Not everyone everyday, but I was always looking at their price movements and chart patterns as I had a couple of screens up. I was also tracking my up days. In the spreadsheet you can see that I was taking 10 up days vs. 4 down days.
You can also track your own weekly targets just like I did. That helped me knowing where I was, what else was required and how many days are left in the month to achieve what’s needed. You can trend your overall trading performance against a split of products and sessions. In the spreadsheet you can see my cumulative P&L, as well as the cumulative P&L of the morning and evening trading sessions. You can see that on 21st December I had a very bad evening session that day versus the big jump that I had at the 4th/5th of December at a very strong morning session.
I was trading many different sectors so I brought them down, again looking at the morning and evening trading sessions. In total I was then able to see that my P&L for that month 70% was coming from the morning and 30% was coming from the evening trading sessions. Was I taking on not enough risk in the evening or was I giving back as I was having losers? These are all valuable insights that could be gained from a spreadsheet like this.
Conclusion: Trade analysis can help improve your trading results
Ok, you’ve learned why it’s so valuable to start off with analyzing your trading performance from the very beginning. Knowing where you’re making money and knowing where you’re losing is crucial for your long-term trading success. You can then adjust your trading plan and habits accordingly to optimize your trading results. What are your thoughts on trade analysis? Do you use a spreadsheet for trading performance analysis as well? What are you doing differently? Please let us know your feedback and thoughts on trade analysis in the comments below.