Use this swing trading trade log to highlight weak areas in your trading. See areas for improvement. The formulas can even calculate your ideal position size for you.
A swing trading trade log is for tracking trades, including basics like entry and exit points, and profitability, but it can also track metrics such as win rate, risk/reward, expectancy, and efficiency. Keeping these statistics healthy is pivotal for trading success.
Below is a downloadable swing trading trade log, in Excel format, that you can use to start tracking your trades and statistics….and improving your trading!
Nearly all the statistical calculations are done for you. It’s easy. Just plug in a few key points about your trades, and the statistics (win rate, expectancy, even position size, and more) are calculated for you.
Types of Swing Trading Trade Logs
It is a useful practice to track your trades and statistics related to your trading.
Some people like to track their trades manually, in a spreadsheet or Google document.
Others prefer to use software to analyze their trades. There are a number of sites, like TradeMetria, TradersSync, TraderVue, and TradesViz, that allow you to upload your trade history (download it from your broker) and have all the data analyzed for win rate, risk/reward, profitability, and more.
This is a viable option…but isn’t without quirks. Not all brokers are supported. Typically you still need to manually import your trades. You can often only choose one default currency, which may affect results if you make trades in multiple markets (such as US and Canada). A number of the software-based trade logs don’t support markets outside the US, or functionality is limited.
These sites also charge a fee, typically $250 to $500 per year.
I tried a number of these sites and while they did provide some insight, I didn’t find them very functional or useful, especially since I trade stocks in different currencies and Canadian market functionality is typically limited. But that’s just me.
If you prefer the hands-on approach, the trade log below is for you. Loads of features, totally customizable, and minimal effort on your part.
Downloadable Swing Trading Trade Log in Excel for 2023
This Excel Trade Log is loaded with key metrics that we want to track in our trading.
If you want additional criteria included in the Log, drop a comment and I can possibly update the file to include it, or you can try adding it on your own.
The file has two pages/tabs, one for US dollar trades and one for Canadian dollar trades (change to another market if you like). Ideally, track US and Canadian trades separately; they involve different currencies and the markets may act differently. The Canadian market has less volume and more lower-priced stocks. By tracking separately you may find you trade better in a particular market, and thus may wish to focus more attention and capital there.
Here is the download for the Swing Trading Trade Log 2023. Below are all the criteria included and what they mean/how to use them.
Entry Date: The day the position is opened.
Closing Day: The day the position is closed.
Symbol: The stock symbol you are trading.
Starting Balance: The balance in the account at the time of the trade (all cash + current positions).
R=1% of SB: Automatically calculates 1% of the starting balance. R is how much we are willing to lose per trade. 1% of the account is the default and a commonly used percentage. If you want to use a different percentage, change the formula from =0.01xC5 to the percentage of your choosing, =0.005xC5 for example (for 0.5%). If you do this, update the column heading to reflect what you are risking. Read more about what “R” means here.
Win and Loss: If the trade is a win, put a “1” in the win column. If the trade is a loss, put a “1” in the loss column. This will allow the Win Rate to be calculated (shown along the top of the sheet).
Entry Price: Input the entry price of the trade.
Actual Exit Price: Input the actual exit price when the trade is finished.
Projected Target Price: This is your initial expected profit target for the trade, at the time you take the trade.
Initial SL Price: The price of your initial stop loss when you take the trade.
Potential R Risk: Automated calculation based on your risk and the amount of capital placed into the trade (discussed later) divided by your R calculation from earlier. This should always be around 1, as we want to be risking close to the same percentage of capital on each trade (unless your trading plan states otherwise).
Potential R Profit: Automated calculation based on the projected profit relative to R (from R=1% of SB column). Ideally, this is 2 or above.
Initial Risk %: Automated calculation using the Entry Price and Initial SL Price.
Potential % Profit: Automated calculation using Entry Price and Projected Target Price.
Profit/Loss %: Automated calculation using Entry Price and Actual Exit Price.
Amt. Traded: Input the amount of capital used for the trade. To determine how much capital to put into each trade, see Position Sizing for Stocks.
Profit $: Automated calculation for the dollar amount made or lost on the trade, based on Amt. Traded and Profit/Loss %.
Fees: Input your commission costs if applicable. Can delete this entire column if you don’t pay commissions. You can also delete the Net Profit column to the right of it.
Net Profit: Automatically calculated. It’s the Profit $ on the trade less commission fees.
Actual R Profit/Loss: Automated calculation that takes Profit $ divided by R (which is R=1% of SB)
Ending Balance: Automated calculation that takes your starting balance for this trade and adds your profit or subtracts your loss on the trade. This may not be accurate because trades don’t always close in the order they were taken. It is just a rough estimate of actual capital. If all trades are completed, and there are no outstanding positions, then it should be accurate.
Time in Trade (Days): Automatically calculated based on the opening and closing date of the trade. Useful for letting you know how long trades generally last, and therefore how many trades you can theoretically take in a month or year.
Efficiency: Input a score based on how well you followed your strategy. This has nothing to do with whether you won or lost. If it was a good trade based on a strategy outlined in your trading plan, and you risk 1R and lost 1R, that’s great. That is 100% efficiency, you followed the plan and did what you were supposed to.
If the strategy says to hold for a 4R profit and you chicken out and ended up only making 1R because you exited early (which isn’t part of the strategy), then your efficiency is only 25%.
If you were supposed to make 1R, but end up losing 1.5R, your efficiency is 50% (50% mistake). If you lost 1.2R instead of 1R, the efficiency is 80% (20% mistake).
Efficiency is a measure that shows how far our trades tend to deviate away from what we expect.
If we don’t have a trading plan or strategy we have tested and follow, then every trade is a mistake until we have a strategy, and therefore every trade is 0% efficiency (a mistake).
Swing Trading Trade Log Statistics
Along the top of the downloadable trade log are key statistics every trader should know. Many problems in trading relate to a risk/reward or win rate issue. Trading is all about finding the balance between these two factors.
Monthly or yearly profit is a function of position sizing, number of trades, reward/risk, and win rate. Nothing else.
Expectancy is expressed as R and automatically calculated as [(win rate X average profit) – (loss rate X average loss)]. Basically, when placing a trade, this is the expected R per trade (based on closed trades) when averaging all losses and profits. The higher the number the better.
Avg R Profit is automatically calculated based on closed profitable trades (pulls positive values from the Actual R Profit/Loss column).
Avg R Loss is automatically calculated based on closed losing trades (pulls negative values from the Actual R Profit/Loss column).
Average Reward/Risk is automatically calculated and is the Average R Profit divided by the Average R Loss. Ideally, this should be higher than 2, and ideally higher than 3, showing that the average profit is 3x bigger than the average loss.
Win Rate % is automatically calculated and is the percentage of winning trades out of all trades.
How To Use the Swing Trading Trade Log to Improve Your Trading
Almost all the work in the spreadsheet is done via formulas. All the statistics are automatically calculated. Just start inputting your trade data, and you’ll start to see areas that need work.
I discuss additional ways to use the spreadsheet, and ways you can use it to improve your trading, in this video.
Optional Additions to the Trading Log
A trading log is a great tool. Combine it with taking screenshots of your trades (after a trade is finished) with your comments and trade levels on the chart. Screenshots provide context on what the trade looked like which a trading log can’t do.
Save these screenshots in a folder, possibly by month or year. They provide a great learning tool; go through your prior trades/screenshots at regular intervals and check for areas of improvement, mistakes, ways to extract more profit from the trades, or ways to reduce losses/risk.
Tradingview also allows you to save your charts as a link (“copy link to chart image”). If you prefer this option, use the “Charts” column in the trade log and input the link to the chart for each trade. This way all the information is compiled in one place.
By taking screenshots, comments about the trade are typically right on the chart. That said, you could still add in additional “Comments” in the provided column.
For guidance on how to actually place trades, and explosive strategies that tend to capture 20%+gains within three weeks, start learning using my Complete Method Stock Swing Trading Course.
If You Have Partial Entries and Exits
If you have partial entries, just use your weighted average price as you add to the position. Input your initial position and size, and then continue to add to it. Your trading account should tell you what your average price is at any given time.
As for taking partial exits, for example exiting a portion of the position as the price moves up, you will need to do a weighted exit that is updated as the trade progresses. Or, your broker provides your average exit price once the trade is complete.
For multiple exits, you could leave the exit price column, but change the name to Total Exit or Final Exit Price.
To the left of it, add a few more columns:
- Exit 1
- Exit 2
And so on, depending in how many exits you usually have.
Record the first exit price when it occurs, and the weight.
- Weight is how many shares were exited out of the total. If 100 out of 300 were sold, the weight is .3333, for example.
For the second exit do the same.
If you exit everything at once, exit1 will have a weight of 1(100%).
Total Exit Price will need to be a formula =
(Exit1 x weight) + (Exit2 x weight)
This is optional. As indicated, once the trade is complete you will know your exit price and profit from your broker statements.
Correcting #DIV/0! Errors on the Sheet
I have dragged/copied formulas all the way to the bottom of each trade log sheet. This just lets you know which columns have formulas.
If you haven’t inputted any data on a row, this will create a #DIV/0! error because the formula is trying to calculate something that doesn’t exist.
The error doesn’t affect anything except the summary/totals at the bottom of the page.
The error will disappear when you input your trade data in the row.
BUT, if you want to see your summary/totals on the bottom of the page, the DIV errors will mess that up.
To remedy the issues, simply delete all rows with no trade data and DIV errors.
As you fill in the rows with trade data, drag the formula cells from above down to the row you are working in. This will fill in the formulas and allow your totals to be calculated at the bottom of the sheet.
By Cory Mitchell, CMT
Disclaimer: Nothing in this article is personal investment advice, or advice to buy or sell anything. Trading is risky and can result in substantial losses, even more than deposited if using leverage.