I got these tweets during the past week regarding my trade journal.
@adeyf69 I’ve been enjoying your posts on trading stats. I’m building my own Excel, do u have a screenshot of the complete stats you track?
— Fxsnowball (@fxsnowball) August 31, 2016
— Fxsnowball (@fxsnowball) August 31, 2016
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.
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.
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.
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.
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.
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.
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.