A walk though of my Trade Journal

I got these tweets during the past week regarding my trade journal.

This post, is going to take a look at what data I collect and how I organise it.

The purpose of a trade journal is to provide data to answer questions, therefore the data collected will depend on what the questions are.

My trade journal does vary depending on what questions I am trying to answer but I will focus on the main areas.

Obviously some of the data I am tracking may not be relative to questions other traders are asking of their own performance.

If a trader is just starting on the process of keeping a journal then this is very likely over the top for their needs.

But I will write an article on how to start a journal and what I would consider the very basics to get started with.

This journal was made in excel and started very simply and has morphed over many versions and a period of 2 years. Google has been a great resource in learning how to make this journal.

I am not going to cover how to generate the formulas and the pivot tables etc which make up the engine to this journal.

If people are interested then I will produce an article on how to approach this with links to good resources for learning how to write an Excel trade journal.

Main Trade Sheet

I have had to split this into 2 sections as the image will not fit neatly in the post.

Trade Journal

1. Trade number. Starts at 1. Enables me to easier see how many trades that are making up the sample size.

2. Date of trade. As I am a day trader and keep no positions over night, I have no need to have a date of exit. But if you hold overnight then you will need an exit date.

3 and 4. Time in and out. This allows me to monitor my performance at certain periods in the day and how long each trade is.

5. I trade 2 markets. So I have a column to indicate which markets this trade is.

6. Com. How much the commission / cost for this trade is.

7. Setup. I have 4 primary setups so I split them up plus a bonus setup which is an error trade. I have this so that I can monitor what errors I make and how much error trades are costing me.

8. Entry. I have 2 types of entry, blind limit order off the level and a trigger setup. Again I enter this so I can monitor which is performing well or badly.

9. L/S. Long or short. I use 1 for long and -1 for short. If you are writing your own excel journal, is a lot easier to use binary choices when it comes to composing logical statements.

10. #. No of contracts.

11. Price in. Entry price

12. Initial stop. Stop price

13. SCO. The number of contracts out at first exit which I call the scale out.

14. SCO.price. The exit price of the scale out.

15. P1. The number of contracts at second exit which I call Target 1.

16. Price Out 1. The price at the second exit.

17. MFE. The most favourable price the trade reached without hitting my stop. I define this as the next pivot high (for longs) or pivot low (for shorts) on the relative timeframe. Trading the 15min then the next 15min high or low, trading the 5min then the next 5min high or low. It does not matter whether I am in still in the trade or not. I track this as it enables to analyse my exits stats against the potential in the trade. Which in turn allows me to make decision whether I should go for bigger or small target.

18. MFE.T The time when price reached it MFE. This allows me to look at how long these set ups play out.

19. MAE. For winners this tracks how far price went against me, so I can analyse my stop size. For example my scalp trade used to have a stop of 7 ticks, but by analysing my MAE, I found that on my scalp that if price rarely went more than 2 ticks against me and that the SD of my winning MAE was 4 ticks. So now my average stop size is 5 ticks. So on every losing trade I now save 2 ticks.

20. SCO. Did the trade hit the scale out target. A binary choice of 1 yes and 0 no.

21. T.15. 15min trend.

22. T.60 60min trend.

Trade Journal

1 to 8 is a Trade Grading System. I have implement this after reading this excellent post from BreakingOutBad.

9. Log.Stat. This is a binary choice of 1 or 0 to indicate whether I have read my logical statements after each type of trade. This helps keep me focused.

10. Primary/ Secondary. Sometimes I get secondary setups after I have already got a winner off a level, and I have noticed that these tend to run longer and give bigger winners. So I have added a column so that I can collect data to see if this proves my assumption right or wrong.

11. SC/IDS Whether the trade is a scalp or intraday swing. I have clear rules defining which is which.

I also have a copy of this trade journal in a separate sheet which I enter trades I missed. As one of the questions I am trying to answer at the moment is “What effect does no fill or missed trades, have on my win rate and performance?”

Reading and making use of all this data.

Sorting the data into a readable and useful format is the next step and the key to this is pivot tables and slicers.

Below is my main stat page, which is a pivot table and a bunch of slicers.

Trade journal

A. All these are slicers, which enables me to filter the data in the main table by time, day, week, month and all of the columns in my journal.

My main pivot table covers

1. The various setups

2. The number of trades this setup produces.

3. Win rate.

4. Loss rate.

5. Break even rate.

6. Total gain compared to amount risked.

7. Total gain in money for this setup

8. Expectancy in Risk terms

9. Expectancy in Ticks.

10. Expectancy in ticks expressed over a 100 trades. This is something fairly recent I added. To help me relate this to actual trading decisions.

11. Average win size in ticks

12. Average Loss in ticks.

13. Total commissions for this setup.

14. The average size in ticks of the scale out position.

15. The number of times this setup hits the scale out exit.

16. The average size of the stop in ticks.

17. The average size of the second exit in ticks.

I also have a second pivot table covering the same information for the missed trades on a different tab.

On separate tab I have an expectancy calculator so that I can play around with new targets and stops of step ups, so I can see what the potential return is compared to my current return of these setups.

Maximum Favourable Excursion Data

This is an important data sheet for me. This is constructed using pivot tables and slicers. On an important note, MFE is always reduced by 1 tick to ensure that the MFE is recording a price that it is possible to exit at.

It takes each trade and plots the MFA (B) and MAE(C) in an histogram, which then can be filtered by the slicers (A) to show the types of trade that I am interested in.

It allows me to look at where price clusters for a setup and then I can compare this to my actual targets, to see whether they are too ambitious or am I leaving potential too much money on the table.

I also have a sheet where I compare my actual exits to the MFE on a histogram.

Again this is a pivot table with slicers.

My maths skills are pretty poor, which I have worked on to improve but there is a nagging in question I cannot answer. Maybe someone reading this can give me some help in the comments or twitter.

For example, If my scalp win rate is 68% with an average win of 6 ticks. If I increase the average target to 7 ticks, as the sheet above shows that 20% of my winning scalp trades go 1 tick further. The question is would this reduce the win rate by 20% and increase my average win to 7 ticks. Therefore the new win rate be 54.4% and average win 7 ticks.

Trade matrix

This is also a pivot table with slicers.

It records the number of trades, the win rate, loss rate, expectancy, the average length of time it takes to hit the MFE (A.SW) and the average time in trade (A.TIT).

This data is grouped into hourly slots and into days.

These allows me to see if there was any particular part of the day or a particular day where I underperformed, which then can be filter to show what I want.

One downside to this chart is that as it grouped into many different sections is getting a big enough sample size for the questions to become relevant.

Grading

Another stat I keep track off is the average Grade for the trades per day and per week.

Again this is created by a pivot table.

I will add slicers to this to see if the grading is effected by time of day, day, type of setup etc. But I have not got around to adding this to the sheet.

I also have a couple of graphs showing the equity curve and the expectancy curve, but I find these are my least most useful tabs.

Summary

My trading journal has taken a couple of years over numerous versions to get to this stage.

To get this sort of information from your trading, you do not need to make your own spreadsheet.

Check out my review of Edgewonk here.

And the new version Edgewonk 2 is coming soon.

I personally think it is a great piece of software. There are a couple of main reasons why I do not use it.

I can enter my trades a lot quicker in my journal as each trade is one line. In Edgewonk each exit has to be a separate entry.

Edgewonk does not cover the MFE/MAE stats in as much depth as I do. Which is crucial in my opinion in optimising targets and stops for each setup.

As far as I know these are being addressed in Edgewonk 2.0

If they are I will probably switch from my own journal to theirs.

9 thoughts on “A walk though of my Trade Journal

  • 04/09/2016 at 16:47
    Permalink

    Fantastic, thanks for sharing. Have to take some time to study this in detail. There is a lot of material here.
    I read your article “Weekly Trading Review – Being honest or what the stats do not always tell us.”
    You mentioned “I was doing visualisation exercises just before bed. Focusing on how I was going to approach the next day”
    I used to do something like this for a while. It resulted in suffering through nightmares and waking up in panic. I switched to more relaxing pre-sleep approaches like reading some hilarious fin tweets.

    Frank

    Reply
    • 04/09/2016 at 17:54
      Permalink

      Thanks for the comments. My visualisation at the end of the day is more to ensure that I start the day focused and with a positive attitude.

      I do this because I found that a lot of times that I would go to be bed with the attitude that the charts looks shit and there probably very few setups or I am up for the week so I don’t want to lose my profits. This always result in over cautious trading.

      So my visualisation is not very intense.

      All my nightmares involve me forgetting some basic technique in the middle of a yacht race.

      Good luck next week.

      Adey

      Reply
  • 04/09/2016 at 19:47
    Permalink

    “I do this because I found that a lot of times that I would go to be bed with the attitude that the charts looks shit and there probably very few setups or I am up for the week so I don’t want to lose my profits. This always result in over cautious trading.” > Focus on the signals and get out of the way of the math. I’m quite sure your math isn’t about any one trade or one week.

    Reply
    • 05/09/2016 at 09:45
      Permalink

      Thanks for commenting. You are right, it sometimes easy to get bogged down into the detail.

      Have a good week.

      Adey

      Reply
  • 05/09/2016 at 01:11
    Permalink

    Massive article Adey. I am filing this away to study in more detail later. I run a lot of stats also not as good as what you are doing here. You mentioned possibly doing another article later with the links to more info about about the formulas plus pivot tables. That would be very valuable if you can do it. I also have Edgewonk but then do my own spreadsheets separately as well.

    I am not sure I can answer the question about the average win moving from 6 to 7 ticks. “Average” win and loss sort of confuses me also. I would probably just manually work it out by running one batch both ways and then comparing.

    Anyone interested in practical no BS info on visualization and others could try this book. “Unbeatable Mind” by Mark Devine. A former navy seal commander. All about thought / emotion control from a navy seals perspective. He talks about breath control, mental models, meditation (concentration practice), visualization (winning first before going into battle), planning etc. A good book for traders.

    Reply
    • 05/09/2016 at 09:51
      Permalink

      Thanks for commenting.

      I will do an article about excel formulas / pivot tables and resources etc. This will probably be next weekend or the weekend after. Have to fit in around my own reviews,research and deliberate practice.

      Thanks for the book recommendation. I have added it to my reading list.

      Have a good week.

      Adey

      Reply
  • 05/09/2016 at 15:34
    Permalink

    Excellent post, as always. Thanks. I studied it carefully and appreciate very much your effort. The idea of having a separate journal for missed/unfilled trades is simple but great!
    Thanks.
    Hana

    Reply
    • 05/09/2016 at 15:59
      Permalink

      Thanks Hana.

      I also using the missed trade sheet to record trades I did not take because of News due out. When I get a big enough sample size I then can make a decision if I should trade over the news (does not include NFP and other major news events).

      I hope you are well.

      Adey

      Reply

Leave a Reply

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