Home / Blog

How to build a trading journal in Google Sheets

Eighteen columns and five formulas, about 20 minutes to set up. It gives you R-multiple, profit factor and a real peak-to-trough drawdown from nothing but Google Sheets. Prefer not to type the formulas yourself? The same thing, already built and tested, is a free download on the ungated template page — no email, one click.

What a Sheets journal is good at, and what it isn't

A spreadsheet journal is manual: you type in every trade by hand, there is no import from a broker statement, and a position you scaled out of in three pieces has to be logged as three rows or averaged into one. What it is genuinely good at is forcing you to write down the setup and the mistake, if any, at the moment the trade closes, while it is still fresh, rather than reconstructing it from memory a week later. That habit is the actual value of a journal; the formulas below just make the habit produce numbers instead of a diary.

The 13 columns you type

One header row, one column each: Date closed, Symbol,Direction (Long or Short), Lot size, Entry,Stop, Exit, Risk ($), P&L ($),Setup, Mistake, Execution rating (1-5),Notes.

Two of these are worth a second look. Risk ($) is what you decided to lose on the trade before you entered it, in account currency — type it in when you place the trade, not afterward, since typing it in after you already know the result is how a journal quietly stops being honest. P&L ($) is the net result straight off your broker statement, commission and swap already included, so a trade that made $8 gross and paid $10 in costs goes in as −$2, because that is what it was.

The 5 columns that compute themselves

To the right of Notes, five more columns, each with one formula copied down every row. Row 2 is the first trade; adjust the letters if your own columns land somewhere else.

• R (R-multiple). =IFERROR(IF(H2=0,"",I2/H2),"") — P&L divided by planned risk. A trade risking $100 that made $250 is a +2.5R trade; one that lost $100 is exactly −1R. Dividing two already-signed dollar amounts avoids a trap: deriving R straight from entry, stop and exit prices instead needs a direction flip for short trades, and it is easy to get that flip backwards without noticing, since a wrong-signed formula still returns a plausible-looking number every time.
• Result. =IF(I2="","",IF(I2>0,"Win",IF(I2<0,"Loss","BE"))) — Win, Loss or breakeven, read off the net P&L.
• Equity. =IF(I2="","",Summary!$B$2+I2) for the first row, then =IF(I3="","",K2+I3) for every row after, where K is this Equity column itself — a running account balance starting from the opening balance on the Summary tab.
• Peak. =MAX($K$2:K2) — the highest the Equity column has reached so far, inclusive of the current row.
• Drawdown. =K2-L2 where L is the Peak column — how far below the high-water mark this row left you. It is zero or negative by construction; the most negative value in the whole column is your maximum drawdown.

Build the Summary tab

• Win rate. =COUNTIF(R:R,"Win")/COUNTA(R:R)
• Profit factor. =SUMIF(I:I,">0")/ABS(SUMIF(I:I,"<0")) — gross profit over gross loss.
• Average win. =AVERAGEIF(R:R,"Win",I:I)
• Average loss. =AVERAGEIF(R:R,"Loss",I:I)
• Expectancy in R. =AVERAGE(P:P) where P is the R column — the mean of every R-multiple you have logged is, by definition, your expectancy in R.
• Max drawdown. =MIN(N:N) where N is the Drawdown column.

Read these six together rather than one at a time. A high win rate with a profit factor under 1 means the wins are small and the occasional loss is large enough to erase several of them; a modest win rate with a profit factor comfortably above 1 usually means the opposite, a system that loses often in small amounts and makes it back in a few big trades.Expectancy in R is the one number that already blends win rate and payoff size into a single figure, which is exactly why it is worth checking after every batch of new trades rather than only at the end of a month.

Worked example: log one trade and check the numbers

Before trusting the sheet with real history, type in a single hypothetical row and confirm the formulas return what you expect. A full hypothetical 12-trade sample, invented to show the arithmetic, starting from a $10,000 opening balance:

R values in order: +2.5, −1.0, −1.0, +1.8, +2.0, −1.0, −1.1, +2.0, −0.95, +2.0, −1.05, +2.5.

Six wins, six losses. Gross profit $1,450, gross loss −$660, for a profit factor of 2.2. Average win $241.67, average loss −$110. Expectancy works out to +0.56R per trade — the mean of the twelve R values above, not a separately invented number. Equity climbs from $10,000 to a first peak of $10,250 after trade one, dips to $10,000 by trade three (a drawdown of −$250, the deepest point in the whole sample), then recovers and closes the run at $10,790, a net gain of $790. That −$250 figure, not the size of any single losing trade, is the maximum drawdown this run actually produced — the distinction below explains why that matters.

Google Sheets vs Excel

The formulas above work identically in Excel; ARRAYFORMULA and native sharing are the two real differences. Sheets shares by link with no file to email around and no version-conflict dialog when two people open it at once, which matters if a mentor or a prop-firm evaluator is meant to see the same live sheet you are updating. Excel pulls ahead once a sheet has thousands of rows and heavy pivot tables, since its calculation engine handles that scale more smoothly. For the row counts an individual trader's journal actually reaches, the choice comes down to which one you already have open, and the Excel version of this template covers the same ground for readers who do.

The two things people get wrong

Using the realised loss instead of the initial risk for R. R is supposed to measure the result against what you planned to lose, decided before the trade. Swap in whatever you actually lost instead and R stops meaning anything — a trade you meant to risk $100 on but panicked out of at −$40 was not "a good −0.4R trade," it was a $100-risk trade you exited early, and blending the two readings hides exactly the pattern a journal exists to surface.

Calling the biggest single loss "drawdown." Drawdown is a property of the whole equity curve, peak to trough, across a run of trades, not the size of any one loss. A single $500 loss inside an account that is still up overall is not a $500 drawdown; the worked example above shows a run with no single loss larger than $150 still producing a real $250 drawdown, because three losses landed close together before the next win arrived.

When a spreadsheet stops being the right answer

Past roughly a hundred trades, or the first time a position gets closed in partial pieces, a spreadsheet starts asking for judgment calls a formula cannot make on its own: which row does a partial close belong to, how do swap charges on a multi-day hold get split across entries, what happens when the same setup runs on two accounts at once. At that point, importing the statement your broker already produces, rather than retyping it, is the more honest use of the time. TapeSheet parses an MT4 or MT5 HTML statement entirely in your browser — nothing is uploaded, and you can confirm that yourself by opening DevTools → Network while you drop the file in — and computes the same R-multiple, profit factor and peak-to-trough drawdown this page just showed you how to build by hand.

Questions

The ungated template ·R-multiple ·Profit factor ·Expectancy ·Peak-to-trough drawdown ·R-multiple calculator ·Skip the typing

This is educational information about measuring your own trading, not financial advice. Trading carries risk of loss. Full risk disclaimer.