How to create a trading journal
To create a trading journal, make a spreadsheet with one row per trade and columns for the date, ticker, long or short, quantity, entry price, exit price, stop loss, target, profit or loss, the reason for the trade, and notes on how you felt and what you learned. Fill in a row every time you open and close a trade, then review the journal weekly or monthly to find the setups and habits that make or lose you money. Excel works well for this, and the MarketXLS add-in can fill in current and historical prices for each ticker.
Try it in your own spreadsheet
Get the free MarketXLS sample workbook (.xlsx).
No account needed. MarketXLS live data requires a paid plan. Preview workbook
Explore more videos
What is a trading journal?
A trading journal is a record of every trade you make and why you made it. It lets you measure performance, spot patterns, and improve strategies based on your own results rather than memory. Traders note entry and exit points, trade size, and the reason for each trade, then review both winning and losing trades. Writing down the reason before you trade also helps you keep discipline and reduce emotional trading.
What to include in a trading journal
Each trading journal entry should include the date and time, the asset, the position size, the entry and exit points, and the reason for entering. Add the strategy used and the market conditions for context. Record your emotions and thoughts for each trade, since they can reveal psychological patterns. Finish each entry with the outcome and any lesson learned.
Can a trading journal improve trading performance?
A trading journal can improve trading performance by showing you patterns and repeated mistakes that are hard to see trade by trade. Reviewing entries shows which setups work and which do not, and it encourages you to follow a consistent plan. The journal does not improve results on its own; the value comes from reviewing it and changing your approach based on what you find.
Which metrics to track in a trading journal
Track five groups of metrics:
- Trade details
- Entry and exit dates and times
- Asset traded (stock, forex pair, cryptocurrency, etc.)
- Position size
- Entry and exit prices
- Type of trade (long or short)
- Financial metrics
- Profit or loss per trade
- Total profit or loss over time
- Win rate (percentage of winning trades)
- Risk/reward ratio
- Return on investment (ROI)
- Trade rationale
- Reason for entering the trade
- Strategy or setup used
- Mistakes and lessons learned
- Errors in execution
- Deviations from the trading plan
- Insights gained
- Cumulative performance
- Running total of profits and losses
- Equity curve
How to create a trading journal in Excel with MarketXLS
A trading journal in Excel with MarketXLS is a normal spreadsheet where MarketXLS formulas fill in prices so you type less. The steps:
Step 1: Set up your spreadsheet
1. Create a new workbook in Excel for the journal.
2. Set up your columns. A typical layout:
- Date of Trade
- Ticker Symbol
- Buy/Sell
- Quantity
- Purchase Price
- Sale Price
- Stop Loss
- Target Price
- Profit/Loss
- Notes
Step 2: Pull market data
1. Install MarketXLS. Install the MarketXLS add-in for Excel. On Excel for Mac or Excel for the web, use the Microsoft 365 add-in, where formulas take the mxls. prefix (for example =mxls.Last("AAPL")).
2. Pull historical prices. Use =Close_Historical("AAPL", "2024-08-01") to get a ticker's closing price on a given date, for example to check the price on the day you entered a trade.
Step 3: Automate price updates
1. Use MarketXLS functions. =Last("AAPL") returns the current price of a stock, so open positions show an unrealized profit or loss. Quotes are 15-minute delayed on the Standard plan and real-time on Advanced and Business.
2. Set alerts with conditional formatting. Highlight rows where the current price has hit the stop loss or target price.
Step 4: Analyze your performance
1. Technical indicators. MarketXLS functions such as =RSI("AAPL") and =SimpleMovingAverage("AAPL", 50) let you record the technical context at the time of each trade.
2. Profit and loss. Add a formula in each row that calculates profit or loss from quantity, purchase price, and sale price (or the current price for open trades).
3. Visualization. Chart your cumulative profit and loss (equity curve) with standard Excel charts to see performance over time.
Step 5: Maintain and review your journal
1. Update regularly. Enter each trade when you open and close it.
2. Review and adjust. Periodically review your trades to find patterns or areas for improvement.
Step 6: Start from a template
MarketXLS offers downloadable templates you can adapt to your own journal layout.
For help setting up the journal, you can book a MarketXLS demo.
Summary
A trading journal records each trade's date, asset, position size, entry and exit, strategy, market conditions, emotions, and outcome. Reviewing it regularly shows which setups and habits help or hurt your results. You can build one in Excel with MarketXLS formulas like =Last() and =Close_Historical() filling in prices, and keep it current by entering every trade when it happens.
