The trading journal template: columns
Put these headers in row 1 and start logging trades from row 2. The letters match the formulas further down.
| Column | Header | What to enter |
|---|---|---|
| A | Date | Close date of the trade |
| B | Time | Entry time (use one time zone throughout) |
| C | Pair | EUR/USD, USD/JPY, XAU/USD... |
| D | Direction | Buy or Sell (dropdown) |
| E | Lots | 0.10, 0.25... |
| F | Entry | Entry price |
| G | Stop | Stop-loss price at entry |
| H | Target | Planned take-profit price |
| I | Exit | Actual exit price |
| J | Pips | Formula (below) |
| K | P&L | Result in account currency, from your platform |
| L | Risk | Amount you would have lost at the stop |
| M | R | Formula: result as a multiple of risk |
| N | Setup | Short name for the reason, from a fixed list |
| O | Plan followed | Y or N (dropdown) |
| P | Emotion | Calm, excited, nervous, anxious... (dropdown) |
| Q | Notes | One or two lines |
Take the P&L figure from your platform instead of calculating it. Swaps, commissions and pip values on cross pairs make hand calculations drift.
SessionX keeps the same core fields (pair, lot size, result, emotions, note) and works out win rate and pair and emotion breakdowns without formulas. It can also export to CSV that opens in Excel. Get it on Google Play
Sample rows
Three example trades on a USD account, showing the main columns. The amounts are for illustration only.
| Pair | Dir. | Lots | Entry / Stop / Target | Exit | Pips | P&L | Risk | R | Plan |
|---|---|---|---|---|---|---|---|---|---|
| EUR/USD | Buy | 0.20 | 1.0840 / 1.0820 / 1.0880 | 1.0880 | +40 | +$80 | $40 | 2.0 | Y |
| GBP/USD | Sell | 0.10 | 1.2710 / 1.2735 / 1.2660 | 1.2735 | -25 | -$25 | $25 | -1.0 | N |
| USD/JPY | Buy | 0.10 | 150.20 / 149.90 / 150.80 | 150.50 | +30 | about +$20 | about $20 | 1.0 | Y |
On USD/JPY the value of a pip in dollars depends on the exchange rate, which is why the figures are approximate. This is the reason to copy P&L from your platform.
Formulas for Excel and Google Sheets
Row formulas go in row 2 and are copied down. Summary formulas can sit on a separate tab or above the headers. The range 2:1000 leaves room for a few hundred trades.
| What | Formula |
|---|---|
| Pips (J2) | =IF(D2="Buy",I2-F2,F2-I2)*IF(RIGHT(C2,3)="JPY",100,10000) |
| R multiple (M2) | =IF(L2=0,"",K2/L2) |
| Win rate | =COUNTIF(K2:K1000,">0")/COUNT(K2:K1000) |
| Average win | =AVERAGEIF(K2:K1000,">0") |
| Average loss | =AVERAGEIF(K2:K1000,"<0") |
| Expectancy per trade | =AVERAGE(K2:K1000) |
| Profit factor | =SUMIF(K2:K1000,">0")/ABS(SUMIF(K2:K1000,"<0")) |
| Cost of broken rules | =SUMIF(O2:O1000,"N",K2:K1000) |
The pips formula assumes standard four-decimal pairs and yen pairs. Gold and indices use different point sizes, so enter pips for those by hand or leave the column blank. Format the win rate cell as a percentage.
The last formula is often the most useful one. It adds up the result of every trade where you marked "N" for plan followed, so you can see in money what breaking your rules has cost. For the reasoning behind expectancy and break-even win rate, see trading expectancy.
Setting up the trade log spreadsheet
- Freeze row 1 so the headers stay visible (View > Freeze in both Excel and Google Sheets).
- Add dropdowns for Direction, Plan followed, Setup and Emotion. In Excel use Data > Data Validation > List. In Google Sheets use Data > Data validation > Dropdown. Fixed lists keep the spelling consistent, so COUNTIF and filters work.
- Colour the P&L column with conditional formatting: green above 0, red below 0.
- Add a filter to row 1. Filtering by pair, setup or emotion is how you answer questions like "do I lose more on Mondays?"
- Start a new tab each month, or keep one long sheet and add a Month column. One long sheet makes all-time statistics easier.
A printable or PDF version works too if you prefer paper. Use the same fields as columns on a ruled page and copy the totals into a spreadsheet once a month.
Template or app: what changes
A spreadsheet is flexible and free to change. The cost is upkeep: formulas break when rows are inserted, dropdowns get bypassed, and logging on a phone is slow. If you find you log trades days late, the format is getting in the way and a quicker method is worth trying.
Log trades in seconds instead of rows
SessionX records each trade from your phone, imports MT5 history, and turns the log into a monthly P&L calendar.
Get it on Google PlayiPhone version coming soon
More guides
- How to Keep a Trading Journal You Will Actually ReviewHow to keep a trading journal for forex: what to write for each trade, when to log it, and a weekly and monthly review routine that turns entries into rules.
- Trading Expectancy: Formula, Calculator and Win RateTrading expectancy = (win rate x average win) - (loss rate x average loss). Calculator, examples, break-even win rate by risk/reward, and reading it from a journal.
- Forex Position Size Calculator and Lot Size FormulaWork out forex lot size from account balance, risk percent, stop-loss in pips and pip value. Calculator, worked examples, pip value per lot, and why 1% risk adds up.
- Revenge Trading: Meaning, Warning Signs and How to StopRevenge trading means trading to win back a loss straight away. How to spot it in your trading journal, and the rules that stop it: loss limits, cooldowns, fixed size.
Published by Zoranta, the developer of SessionX.