The Complete Excel Trading Journal Template Guide

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.

excel trading journal guide

In this guide you will learn:

  1. Entering trades
  2. Using the trading journal for options trading
  3. The setup of the template
  4. General Tips
  5. Customizing your trading journal
  6. FAQs
  7. 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 input fields:

  1. Entry Date:  The first input field is the entry date. Here you have to enter the date you opened your position (dd/mm/yyyy).
  2. Entry Time: Here you enter the time of the day when you put on the trade (hh/mm/ss).
  3. Exit Date: The date of closing the position (dd/mm/yyyy).
  4. Exit Time: The time of day when closing the position (hh/mm/ss).
  5. Entry Price: This is where you enter the entry price of your position.
  6. Exit Price: This is where you enter the exit price of your position.
  7. Size: Here you should enter the size of your position (e.g. number of shares).
  8. Commissions: This field is for the commissions that you had to pay for the trade.
  9. Ticker: This is where you should input the ticker of the asset that you traded.
  10. 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!
  11. 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:

  1. Time/Trade: This shows you how long you were in a trade (in dd/hh/mm/ss). Don’t try to edit this!
  2. P&L: This shows the net Profit and Loss of your trade. Don’t try to edit this!
  3. %: 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:

excel trading journal template gif

 

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.

trade example

  • 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:

  1. Entry Date: Here you should just enter the date of opening the position (dd/mm/yy).
  2. Entry Time: This is where the time of the entry belongs (hh/mm/ss).
  3. Exit Date: This is the date of closing the position (dd/mm/yy).
  4. Exit Time: Here you should input the time of day when closing the position (hh/mm/ss).
  5. 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.
  6. 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.
  7. 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.
  8. Commissions: Here you should enter the total commissions paid for the trade.
  9. 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…
  10. Ticker: Here you enter the ticker symbol of the asset that you traded the options on.
  11. 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!
  12. 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:

EWZ iron condor example

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:

EWZ trading journal entry

 


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.

Excel trading journal template

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:

sort sheet

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 can always be reached through the following email address: Louis@TradeOptionsWithMe.com

 

Happy Journaling!

9 Replies to “The Complete Excel Trading Journal Template Guide”

  1. Hi Louis,

    What a great article that covers how to use your excel trading journal. Everytime I got into trading, it I was a chaos so that it’s why I never lasted long enough. Putting it all in excel seems like a great way to keep track of everything especially profit and loss.

    What is your experience regardinng different apps and platforms for trading on the phone? Do you have any favourites?

    As sometime I can get little bit loss with so many new options today

    1. Hi Andrea,

      Thanks for the comment. I personally don’t really use trading apps a whole lot. The broker that I use is tastyworks and they have a mobile app. However, I barely use it. For all brokers I prefer the desktop platform because desktop platforms usually have more features and I like having more screen space. So I can’t really give you any recommendations. But nowadays, most brokers have mobile apps, so I’d rather focus on other aspects when choosing a broker. 

  2. This is a wonderful tool to keep track of ones trades and quite concise too!  It tracks how long you’ve had the trade running in operation and so you can see what is moving in the market trade and what is standing still.

    I think this is an excellent means of storing valuable data and I am an Excel fan because there is so much you can do with the software.  Charts, Graphs, Stats and whole lot more provide excellent tools for presentations and ease of use at a quick glance you can see how the trade is faring with the rest of the world.

    Wonderful training step by step, highly recommend using this Trading Journal Template to keep you streamlined!

    Thanks for sharing your valuable information with us!  Wishing you all the best in your future endeavours!

    Edu

    1. Thank you for the comment Edu. I completely agree with you that Excel is a great piece of software that has many purposes and many features. Furthermore, I really believe that it isn’t that hard to use.

  3. I’ve never been involved with trading and the reason I haven’t is because I’ve always been a huge skeptic of it. I’m never sure of whether this works or not. The template looks quite efficient and looks to be helpful – but the big question that always strikes me how easy is it to learn and implement. Do you think someone would need to have a background in finance or be tech savvy for this? 

    1. I don’t think it is necessary to have a background in finance or in anything really to become a successful trader. Trading is very difference from almost anything. Therefore, I truly believe that anybody can become a successful trader as long as he/she is willing to put in time and work! 

  4. Great guide Louis! Although I have learn to work in Excel in high school I really did not know that I can use it for trading options. You explained everything so well that it is impossible for me to make some mistake. I often use android apps for trading options but this is not bad to know.

Leave a Reply

Your email address will not be published. Required fields are marked *