Record Keeping for Day Traders

Tracking Trades

A number of people have asked me how I keep track of my trades and how I use my trading records. Instead of answer each query I've decided to present the answer here in an article.

For my record keeping I always use Excel. You can use any spreadsheet but I choose Excel because it's already on my computer. The reason that I choose a spreadsheet over the custom record keeping software out there is because of its flexibility and power. You can do anything in Excel. It has its own built in programming language (VBA) which allows you to extend it as much as you want.

If you decide that you want to start tracking or calculating another aspect of your trading results then it usually takes no more than 5 minutes to add in this feature and often no more than 60 seconds.

The Template

This is a zip file holding a template of my trading spreadsheet with some dummy trades in it. If you don't already have a record keeping spreadsheet then this is not a bad place to start: TradeRecordsTemplate.zip

What to Collect

This is difficult. You really want to collect everything you possibly can but this is often impracticle. At the very least you want to collect the details that are laid out in the spreadsheet mentioned above. Here are the columns from the trade detail part of the spreadsheet:

You can also add columns that show your mood or feelings at the time of trade entry. I would suggest that you create a 2 or 3 letter or number code for each mood that you could be in or feeling that you could have which makes it easier when you use this data during your analysis phase.

Using the Information

Once you've collected enough information then you should use it to improve your trading. Enough information could be 10 to 20 trades if you trade only 1 strategy on 1 instrument. However, if you trade multiple strategies across multiple instruments then you may need to collect a lot more information.

Use the Data -> Auto Filter feature on Excel and select just the trades that you are interested in. Filter on different columns and see if you can pick up a pattern from when you were less or more profitable than usual. See if something obvious stands out. A particular strategy, time of day, day of week etc.

You can add columns to the spreadsheet which give you the day of week or time of day based on your entry or exit time for your strategy. This allows you to find times or days when you may be trading better or worse and you can use that information to improve your trading. For example, you may find that your results are better in the mornings. Question yourself why this is. Is it because you are concentrating more because you have just woken up and are fresh?

You can use the Data -> Sub Total... feature in Excel to group results by type - for example day of week. You can look at your Profit/Loss as totals or averages. You can also average or sum the number of contracts or number of trades that you execute on this basis.

Essentially what you are looking for is exactly the same as when you're looking for profitable trading strategies. You're looking for patterns of better or worse profitability. You are using this tool to analyze yourself in an objective manner. With these figures you will be able to tell where you excel and where you fail and this should help you become a more profitable trader.