A spreadsheet is the cheapest way to start a trading journal and, set up properly, it measures the same things as any tool: R, expectancy, profit factor, drawdown and results by setup. Here are the columns for futures, the formulas, the summary, the setup and hour tables and, in all honesty, where a spreadsheet stops being enough.
The structure: three blocks
A Journal sheet with one row per trade; a Specs sheet with symbol (A), dollars per point (B) and round-turn commission per contract (C); and a summary block to the right of the data. Point values are the exchange's: ES $50, MES $5, NQ $20, MNQ $2, CL $1,000, MCL $100, GC $100, MGC $10. The commission is yours: copy it from your broker's or prop firm's statement, exchange fees included, not from the marketing page.
In Excel, turn the journal range into a table (Insert → Table, or Ctrl+T) so every new row inherits the formulas. In Google Sheets, copy formulas down only as you add trades: an empty row with formulas counts as a $0 trade.
The columns, one by one
Twenty columns. Formula columns are never typed; the rest are, as soon as the trade closes. Why each field matters is covered in how to keep a futures trading journal.
| Col. | Field | Example | Type |
|---|---|---|---|
| A | Date | 09/15/2026 | Input |
| B | Account | PA-50K-1 | Input |
| C | Instrument | MES | Input |
| D | Side | Long | Long or Short |
| E | Contracts | 2 | Input |
| F | Entry time | 8:42 | Input |
| G | Entry price | 6,000.00 | Input |
| H | Exit time | 9:05 | Input |
| I | Exit price | 6,014.50 | Input |
| J | Initial stop | 5,990.00 | Input |
| K | $ per point | 5 | Formula |
| L | Risk $ | 100 | Formula |
| M | Gross P&L | 145 | Formula |
| N | Commissions | 1.20 | Formula |
| O | Net P&L | 143.80 | Formula |
| P | R | 1.44 | Formula |
| Q | Setup | ORB | Closed list |
| R | Grade | A | A+, A, B or C |
| S | Notes | Entry on the retest | Input |
| T | Screenshot | Link to the image | Input |
Setup and Grade need data validation with a list (Data → Data validation): if you can type "ORB", "orb" and "Opening range", the setup table will show three rows for one idea. How to define that list is covered in the guide to setups and expectancy. The grade scores execution, not outcome, and the stop is the initial one, even if you moved it later.
The formulas for each trade
Row 2 formulas, to copy down. They use English function names with commas; if your Excel or Sheets runs in another language, names are translated and, in decimal-comma locales, commas become semicolons.
| Col. | Formula | What it does |
|---|---|---|
| K | =VLOOKUP(C2,Specs!A:C,2,FALSE) | Dollars per point |
| L | =ABS(G2-J2)*K2*E2 | Risk to the initial stop |
| M | =IF(D2="Long",I2-G2,G2-I2)*K2*E2 | Gross P&L, either side |
| N | =E2*VLOOKUP(C2,Specs!A:C,3,FALSE) | Round-turn commissions |
| O | =M2-N2 | Net P&L |
| P | =IF(L2>0,O2/L2,"") | R multiple, blank without risk |
With the example row: the stop is 10 points away, so risk is 10 × 5 × 2 = $100; the exit is 14.5 points in your favor, 14.5 × 5 × 2 = $145 gross; with an example commission of $0.60 per contract, $1.20 in commissions and $143.80 net. R is 143.80 ÷ 100 = 1.44R. Always compute it from net P&L and the initial stop: with a stop moved afterwards, R values come out inflated and expectancy lies. How to read it is covered in the guide to R multiples.
The summary block
To the right of the data (say, columns Z and AA), one label and one formula per metric. They read whole columns, so they update on their own.
| Metric | Formula |
|---|---|
| Trades | =COUNT(O:O) |
| Win rate | =COUNTIF(O:O,">0")/COUNT(O:O) |
| Average win | =AVERAGEIF(O:O,">0") |
| Average loss | =AVERAGEIF(O:O,"<0") |
| Expectancy ($) | =AVERAGE(O:O) |
| Expectancy (R) | =AVERAGE(P:P) |
| Profit factor | =SUMIF(O:O,">0")/-SUMIF(O:O,"<0") |
| Maximum drawdown | =MIN(W:W) |
Maximum drawdown
The largest drop of the equity curve from a peak. It needs three helper columns, with trades sorted by exit time:
- U, running total:
=SUM($O$2:O2). The equity curve. - V, peak so far:
=MAX(0,MAX($U$2:U2)). The 0 is the starting point. - W, drawdown:
=U2-V2. Zero at the highs, negative in the dips.
The minimum of W is your maximum drawdown in dollars. If you trade at a prop firm, careful: it is measured on closed trades. Your firm's is not: under intraday trailing the threshold rises with floating peaks the sheet never sees, and under end-of-day it is measured on the session's closing balance. Column W describes your strategy; the real distance to liquidation comes from the firm's dashboard, as the guide to trailing drawdown explains.
By setup and by hour: pivot tables
The summary tells you whether you make money; the setup cut, with what. Create a pivot table (Insert → PivotTable in Excel, Insert → Pivot table in Sheets) with Setup in rows and, as values, the count of net P&L, the average of R and the sum of net P&L. With 1R = $100 on every trade:
| Setup | Trades | Win rate | Expectancy (R) | Net P&L |
|---|---|---|---|---|
| ORB | 42 | 52% | +0.31R | $1,302 |
| VWAP pullback | 35 | 46% | +0.12R | $420 |
| Midday fade | 23 | 39% | −0.28R | −$644 |
Add win rate per setup alongside with =COUNTIFS(Q:Q,"ORB",O:O,">0")/COUNTIF(Q:Q,"ORB"). The reading: the midday fade loses more than the pullback makes, though at 23 trades it is a suspicion, not a verdict. For the hour cut, add a column X with =HOUR(F2) and build a second pivot table with X in rows; for the weekday, =WEEKDAY(A2,2) returns 1 for Monday.
Where a spreadsheet falls short
What fails is not the formulas, it is the data fed into them:
- Typing. Twelve fields per trade: at eight trades a day, nearly a hundred cells. A mistyped price does not throw an error, it produces a false R. And the bad day is exactly the one you do not feel like filling in.
- Commissions. A flat rate drifts from the statement: exchange fees change and every firm and platform charges differently. For a micro scalper, that gap can flip the sign of expectancy.
- Multiple accounts. Copying into five accounts means five rows per trade, with different fills and commissions, summaries filtered by account and the note written five times.
- Prop firm drawdown. The firm measures its threshold under a different rule, by futures session day and, under intraday trailing, with peaks that never show up in your prices. Your sheet can say you have $1,500 of room when the dashboard says $900.
The fix for the first three is to stop typing. If you use NinjaTrader 8, the guide to exporting NinjaTrader trades shows how to get a CSV to paste into the sheet; from there you only add stop, setup, grade and notes.
Frequently asked questions
Should I download a ready-made template or build my own?
Build your own. With the columns and formulas in this guide it takes about twenty minutes, and in return you know what every cell computes and can adapt it to your contracts and commissions. Someone else's template with formulas you do not understand is the first reason people stop using it.
Does Google Sheets work, or do I need Excel?
Either works: every formula in this guide exists in both, and so do pivot tables. If your Excel or Sheets runs in another language, function names are translated and, in decimal-comma locales, arguments are separated by semicolons.
How do I log a trade with several exits?
As a single row with the exit price averaged by contracts: exit 2 contracts at 6,010 and 2 at 6,020 and the exit price is 6,015. Logging each exit as a separate trade inflates the trade count and distorts the win rate.
Next step
Build the sheet and log your next twenty trades without skipping any: with that sample the summary starts to say something. If working out each row's risk means doing math before every entry, the position size calculator does it for you. And when typing starts to cost you trades, try syncing your account in Jornalo: the Free plan needs no card.