Most trading journals I've seen in Excel go one of two ways. Either they die after three weeks, or they turn into a giant mess that's impossible to pull answers out of.
Both usually come from the same mistake: the sheet was set up for writing things down, not for reading them back later. Here's how I'd set one up so it still works when you've got 500 trades in it.
The one rule: one row per trade
Every trade gets exactly one row. The entry and the exit go on the same line, along with what the trade made or lost. That's it. Almost every problem with a journal comes from breaking this rule.
| Date | Entry | Exit | P/L |
|---|---|---|---|
| Mar 2 | 190.00 | 195.50 | 55.00 |
| Mar 4 | 201.20 | 199.80 | -14.00 |
| Date | Side | Price |
|---|---|---|
| Mar 2 | Buy | 190.00 |
| Mar 2 | Sell | 195.50 |
| Mar 4 | Buy | 201.20 |
Made-up example trades.
The second layout is what a lot of account statements look like. It's fine for a statement. For a journal it's a headache, because every question you ask later means pairing up buys and sells first.
The columns worth having
You don't need 30 columns. Start small. You can always add more on the right later.
Date and Profit/Loss ($). With just these two you can already see your equity curve, your losing streaks and your drawdowns.
Symbol, Long/Short, Quantity, Entry Price, Exit Price, Stop Price, Fees. These let the sheet do the math for you, and the stop price lets you measure trades in R (more on that below).
Setup, Session, Entry Time, Notes, plus a couple of your own tags like "news day" or "tired". This is where the good answers end up coming from.
Let the sheet do the math
Say your columns go like this: C Long/Short, D Quantity, E Entry, F Exit, G Stop, H Fees. Then:
- Profit/Loss in I2:
=IF(C2="Short", E2-F2, F2-E2)*D2-H2 - Result in R in J2:
=IF(C2="Short", E2-F2, F2-E2)/ABS(E2-G2) - Copy both down as you add trades.
R just means "how many of my planned losses was this?" If you risked $100 and made $250, that's +2.5R. It's handy because it doesn't change when your size changes, so a trade from last year and a trade from today mean the same thing.
Futures: multiply the Profit/Loss formula by the dollar value of one point for that contract. The R formula doesn't need it.
Four habits that keep it useful
Then actually read it back
Once a month, make a pivot table with Setup as rows and Profit/Loss summed as values. Add a count of trades next to it. A setup with 6 trades doesn't tell you much yet, so don't read too much into it.
If you want somewhere to start, the losing streak check and the drawdown check both work off a journal set up like this.
This article is about record keeping, not trading advice.