Google Play

Trading Journal Template for Excel and Google Sheets

A working trading journal template needs one row per trade and about 17 columns: date, time, pair, direction, lot size, entry, stop, target, exit, pips, profit or loss, risk, R multiple, setup, plan followed, emotion and notes. A small summary block with six formulas then gives you win rate, average win and loss, expectancy and profit factor.

Below is the full column list, three sample rows, and formulas that work the same way in Excel and Google Sheets. Building it takes about ten minutes.

A spreadsheet-style trade log next to a monthly profit and loss calendar

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.

ColumnHeaderWhat to enter
ADateClose date of the trade
BTimeEntry time (use one time zone throughout)
CPairEUR/USD, USD/JPY, XAU/USD...
DDirectionBuy or Sell (dropdown)
ELots0.10, 0.25...
FEntryEntry price
GStopStop-loss price at entry
HTargetPlanned take-profit price
IExitActual exit price
JPipsFormula (below)
KP&LResult in account currency, from your platform
LRiskAmount you would have lost at the stop
MRFormula: result as a multiple of risk
NSetupShort name for the reason, from a fixed list
OPlan followedY or N (dropdown)
PEmotionCalm, excited, nervous, anxious... (dropdown)
QNotesOne 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

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.

PairDir.LotsEntry / Stop / TargetExitPipsP&LRiskRPlan
EUR/USDBuy0.201.0840 / 1.0820 / 1.08801.0880+40+$80$402.0Y
GBP/USDSell0.101.2710 / 1.2735 / 1.26601.2735-25-$25$25-1.0N
USD/JPYBuy0.10150.20 / 149.90 / 150.80150.50+30about +$20about $201.0Y

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.

WhatFormula
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

  1. Freeze row 1 so the headers stay visible (View > Freeze in both Excel and Google Sheets).
  2. 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.
  3. Colour the P&L column with conditional formatting: green above 0, red below 0.
  4. Add a filter to row 1. Filtering by pair, setup or emotion is how you answer questions like "do I lose more on Mondays?"
  5. 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.

Leveraged forex and CFD trading can produce losses that exceed your deposit. This template is a record-keeping tool and general information only, not financial advice. See the CFTC's forex fraud advisory on the risks of margin trading.
SessionX

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 Play

iPhone version coming soon

More guides

All guides

Sources

Published by Zoranta, the developer of SessionX.