I recently released a downloadable Excel trading journal template. I highly recommend using my or your own Excel spreadsheet to track your trades as tracking trades is essential to your success as a trader. This article will explain you all the intricacies of my Excel trading journal template.
In this guide you will learn:
- Entering trades
- Using the trading journal for options trading
- The setup of the template
- General Tips
- Customizing your trading journal
- FAQs
- Final Remarks
.
1. How to use the Excel trading journal template – Entering trades
Let’s start with the fields that you have to fill out to register your trades. There are nine different fields that always should be filled out and two additional fields that I recommend filling out as well. I will now walk you through each of these inputfields:
- Entry Date: The first input field is the entry date. Here you have to enter the date you opened your position (dd/mm/yyyy).
- Entry Time:Here you enter the time of the day when you put on the trade (hh/mm/ss).
- Exit Date:The date of closing the position(dd/mm/yyyy).
- Exit Time: The time of day when closing the position(hh/mm/ss).
- Entry Price:This is where you enter the entry price of your position.
- Exit Price:This is where you enter the exit price of your position.
- Size: Here you should enter the size of your position (e.g. number of shares).
- Commissions:This field is for the commissions that you had to pay for the trade.
- Ticker: This is where you should input the ticker of the asset that you traded.
- Notes:Here you can enter notes about the trade. You could leave this field blank and the spreadsheet should still work. However, I highly recommend filling it out!
- Lessons: This is where you can add lessons that you learned from this trade.You could leave this field blank and the spreadsheet should still work. However, I highly recommend filling it out!
.
Other Fields:
Besides the just mentioned fields, there are a few other fields that should NOT be filled out manually. These fields will be filled out automatically after you fill out all the other cells. The fields that I am talking about are:
- Time/Trade:This shows you how long you were in a trade (in dd/hh/mm/ss). Don’t try to edit this!
- P&L:This shows the net Profit and Loss of your trade. Don’t try to edit this!
- %:This shows you the percentage gain/loss on your trade. Don’t try to edit this!
Generally, you should not fill out any fields except for the above-mentioned ones. Everything else will be done automatically!
The following animation shows how the different fields should be filled out:
.
Finding the information:
You likely won’t be able to remember all of the necessary stats to fill out all the fields. However, your broker platform will have all of the necessary information. So all you have to do is go your broker platform’s trade history and look at the stats of your trades and then copy those to the spreadsheet.
Here is an example of the history tab inside of the broker platform tastyworks. It has all the required stats directly on one page.
- Entry Date: 16/10/18
- Entry Time: 15:55
- Exit Date: 16/10/18
- Exit Time: 16:04
- Entry Price: 1.17
- Exit Price: 1.07
- Size: 400
- Commissions (+Fees):5.71
- Ticker:AGRX
.
2. Using the Excel Trading Journal Template for options trading:
As you probably know, my Excel trading spreadsheet can also be used for options trading. In fact, the last options trading section is specifically designed to keep track of options trades. As options work slightly different than stocks or other similar assets, I want to walk you through entering options trades now.
By default, the options trading section is divided into two subsections, namely short premium and long premium (the names can be customized, but more on that further down). Before entering an options trade, you should identify in which of these two categories it belongs:
Short Premium: An options trade belongs into this category when the position is a short strategy. In other words, the goal of the position was to sell it at a high price and buy it back at a lower price. Here are some examples of short strategies that belong into this category: short strangles, short straddles, short iron condors, credit spreads, short calls, short puts…
Long Premium:This is basically just the opposite of short premium. So long options trades belong into the long premium subsection. The goal of these trades should have been to buy them at a low price and sell them at a higher price. Here are a few examples: long straddles, long strangles, long iron condors, long calls, long puts, debit spreads…
If you notice that the profit on an option trade is shown as a loss (or vice versa) after entering the trade, you probably entered it into the wrong category. So just double check the P&L if you are unsure about where a trade belongs.
Now I will walk you through each field that you have to enter for options trades:
- Entry Date:Here you should just enter the date of opening the position (dd/mm/yy).
- Entry Time:This is where the time of the entry belongs (hh/mm/ss).
- Exit Date:This is the date of closing the position (dd/mm/yy).
- Exit Time:Here you should input the time of day when closing the position (hh/mm/ss).
- Entry Price:Here you enter the net price of the entire options position when opening it. So if a position has multiple legs, you just enter the net price.
- Exit Price:Here you enter the net price of the entire options position when closing it. So if a position has multiple legs, you still just enter the net price.
- Size:This is where you enter the number of options contracts traded. If you traded 1 contract of a multi-leg strategy, don’t multiply the number of legs by the number of contracts. In that case, simply enter 1.
- Commissions:Here you should enter the total commissions paid for the trade.
- Strategy: This is where you enter the option strategy that was traded. E.g. long iron condor, short iron condor, long straddle, short straddle, long strangle, short strangle, long call, short call…
- Ticker: Here you enter the ticker symbol of the asset that you traded the options on.
- Notes:Here you can enter notes about the trade. You could leave this field blank and the spreadsheet should still work. However, I highly recommend filling it out!
- Lessons: This is where you can add lessons that you learned from this trade.You could leave this field blank and the spreadsheet should still work. But once again, I highly recommend filling it out!
.
An Example Options Trade
Let me give you an example of how to enter an options trade. This is the example options trade:
This trade was an iron condor on EWZ that I made a while ago. As you might or might not know, iron condors are a four-legged options strategy. This was a short iron condor which means that it belongs into the ‘short premium’ section. All of the stats that are required to enter can be copied directly from the image above (except for commissions which were $5.09).
- Entry Date:31/08/18
- Entry Time:15:41
- Exit Date:24/09/18
- Exit Time:20:18
- Entry Price:0.81
- Exit Price:0.46
- Size:1
- Commissions:5.09
- Strategy:Short Iron Condor
- Ticker: EWZ
.
This is how it looks inside the Excel trading journal template:
.
How to track Adjustments/Rolling
Here is what I recommend for adjusting and rolling trades:
One way to treat adjustments would be to treat all adjustments as one trade because all adjustments should belong to the same trade. If you want to do it like this, you just have to enter the net entry price into the entry price field and the net exit price (with all the adjustments factored in) into the exit price field. Furthermore, I recommend writing the date on which you close the last adjusted position into the exit date field. Then you can add some details about the adjustment process into the Notes section for each trade.
For instance, if you open a trade for a credit of $2, you should enter $2 as the entry price into the short premium section. Let’s say you later adjust this position and collect an additional $0.4 credit. Shortly, after that you close the position for a debit of $1. Now you, could enter $0.6 as the exit price as you collected $0.4 during the adjustment process.
A great alternative to this would be to treat each adjustment as a new trade (this is easiest for rolling). When you roll, you close the entire old position and open a new one in a later expiration cycle. Each of these rolls can easily be treated as seperate trades. I just recommend marking somewhere that these trades belong together.
.
3. The setup of the template
The three sections – where to enter which trades
The top of the entire excel trading journal template is divided into three main categories: long setups, short setups and options trades. Each of these main categories is divided into two additional subcategories. So in total, one spreadsheet allows you to track six different trading strategies simultaneously.
Now I want to explain where you should enter different trades. First of all, let’s start with the most common ones, namely long setups. ‘Long setups’ is the category for long trades. In other words, this is where you enter long stock/forex/ETF… trades. So all normal buying strategies can be entered here. Here is an example of a trade that would be entered into the ‘long setups’ section:
You buy 100 shares of XYZ at $58.30 and sell those 100 shares of XYZ at$60.55 three days later.
The ‘short setups’ section is for shorting(selling to open). So if you short stocks or other assets, this is where you should enter these trades.Here is an example of such a trade:
You short 1200 shares of ABC at $11.02 at 9:46. At 14:23, you buy back the 1200 shares of ABC for $10.33.
Last but not least, the options trading section is for options trades. Let me give you an example of an options trade:
At the beginning of July, you sell a strangle on XYZ and collect $450 for it. One month later, you buy back the strangle for $227.
.
How the spreadsheet works – 3 sheets
If you already own a copy of the Excel trading journal template, you probably have noticed that it consists of three sheets: ‘Main’, ‘Data’ and ‘Sort’. At the bottom of the template, you can switch between these three sheets. Here is a screenshot of where you can switch between them.
.
Main
The ‘Main’ sheet is the one that you will be looking at 99.9% of the time. This is where you enter all your trades and where all the data is displayed in different tables, charts etc. So everything, you need is on this sheet.
Data
There is really no reason for you to ever look at the ‘Data’ sheet. It only exists to track different data and keep track of the results of different calculations. Do not try to edit this! Otherwise, the entire spreadsheet might stop working. The ‘Data’ sheet can be seen as the backend of the Excel trading journal template.
Sort
Last but not least, we have the ‘Sort’ sheet. This sheet keeps track of the data for the total P&L graph on the ‘Main’ sheet.Once in a while, the total P&L chart might lose track of the chronology of some trades. To fix this, the ‘Sort’ sheet exists. It requires a very simple action to fix the small error. All you have to do is go to the ‘Sort’ sheet by clicking on ‘Sort’ at the bottom of the template.
This is how the ‘Sort’ sheet looks:
To fix the small error, you simply have to click on the big ‘Click HER!’ cell and then click on the sort by ‘A to Z’ button under the Data tab in the menu bar. After doing that, the mistake should be fixed.
As you can see on the image, I recommend doing this once in a while to correct the chronology on the total P&L chart.
.
4. General Tips
1.
Try to always fill out these cells when entering a new trade: entry date/time, exit date/time, Entry P., Exit P., Size, Com., Ticker. Otherwise, certain things might not work.
2.
I can’t recommend editing any formulas because this will usually start a chain reaction which will lead to many errors.
3.
Enter the trades chronologically.Some things might not work if the exit dates aren’t in chronological order.
4.
The standard contract size for options is 100. This is automatically calculated in the options trading section. So if you traded 1 contract, don’t enter 100. Just enter 1.
5.
If a cell has a small red triangle in the upper right corner, hover over it to read my comment. My comments usually are brief explanations of different things.
6.
If a cell looks like this: “######”, it means that the cell isn’t big enough to display a number. To fix this, either change how the number is rounded (under the “Number” section in the menu bar) or increase the width of that column.
.
5. Customizing your Excel trading journal
As the trading journal is inside Excel and not in a separate trading journal software, the customization possibilities are nearly endless. With that being said, there are some things that you should not customize.
Here are a few things that you should not customize:
- Any formulas
- Anything on the ‘Data’ sheet
- Anything on the ‘Sort’ sheet
Besides this, you can customize almost anything. For instance, you can rename all the strategies at the top of the template and they will automatically be renamed everywhere else. So make sure to rename them at the top and not anywhere else (if you want to rename them).
Furthermore, you can customize, the fonts, font sizes, font colors, the layout of the template, sizes of the different charts, rows, columns, add additional things, add more sheets and much more. But note if you don’t want to customize the template, there is absolutely no need to do so.
.
Haven’t gotten your copy of the Excel Trading Journal Template yet? Grab one now!
.
6. Frequently Asked Questions
Can you track more than six different strategies at once?
Yes, it is possible to track more than six different strategies at once. Every copy of my Excel trading journal template allows you to track six different trading strategies. So if you need more than six, simply start a new template to track the other strategies!
What should you do if your trading journal is completely filled out?
If this is the case, you can simply start a new trading journal that is completely empty. This will allow you to have much more space for more trades. Generally, I recommend starting new trading journals relatively regularly. For instance, I plan on starting fresh at the beginning of every year. Then I have an in-depth analysis of my trading on a year to year basis. However, depending on how much you trade, you could also do this on a monthly basis. If you only make a few trades per year, you could also start fresh every few years instead of every year.
.
Do you have any questions that weren’t answered in this guide?Then please let me know in the comment section below!
.
7. Final Remarks
I truly hope that this guide will clarify any confusion about how to use different features of the Excel trading journal template. A lot of the things explained in this guide are also explained directly on the template (in form of comments) so that you don’t always have to go to this guide if you are unsure about something.
If you have Excel specific questions, I highly recommend looking at suggestions inside of Excel because Excel has a very big and helpful database of explanations and help. Furthermore, small errors can often be fixed by a quick Google search. Otherwise, always feel free to contact me!
If you have a question which is not answered here, please let me know either in the comment section below or contact me personally!
Besides questions, I am also more than happy to get feedback on the template. For instance, if you don’t like specific aspects of it or even have recommendations for features that should be added, please let me know and I might create a new template with the requested features.
I also recommend checking out my guide on how to track and analyze your personal trading data(with the help of my Excel Trading Journal Template).
I can always be reached through the following email address:Louis@TradeOptionsWithMe.com
.
Happy Journaling!
