How to Keep a Stock Trading Journal in a Spreadsheet

Active trading produces a stream of decisions that a brokerage statement flattens into a single number. The statement shows what the account is worth today. It is far quieter on the questions that shape how someone trades over time: how often the trades close green, how big the winners are next to the losers, which tickers have been kind and which have been costly, whether one setup is carrying the whole account, and how deep the worst stretch got along the way. Those answers are all latent in the same handful of facts you already know for every trade, and a spreadsheet is well suited to pulling them out.

Before any of that, a plain statement of what this is and is not. A trading journal records what already happened. It is a bookkeeping tool for closed trades, not a signal generator, a strategy, or a forecast. Nothing in it predicts the next trade or suggests one, and trading stocks carries a real risk of loss regardless of how carefully the past is logged. What the journal does is turn a pile of fills into a readable record, so the numbers describe your own history rather than a hunch about it.

The structure comes down to one thing done well: a single trade log, entered once, feeding every other view by formula. Get that right and the per-ticker summary, the monthly totals, the strategy breakdown, the risk view, and a headline dashboard all fall out without a second round of typing. The examples below come from our Stock Trading Journal Ultimate Spreadsheet Template ($29), which ships the whole thing ready-made for Excel and Google Sheets. The layout is reproducible by hand if you would rather build your own trading journal spreadsheet from scratch.

What a stock trading journal spreadsheet has to hold

Strip away the platform chrome and there are only a few kinds of data behind a trading record:

1. Per-trade records. The date, ticker, side, entry price, exit price, share count, strategy, and a note for every completed trade. This is the raw material for everything else.
2. Categories that group the trades. The ticker and the strategy label, which are the two axes every summary sheet slices the log along.
3. A few risk assumptions. The win and loss percentages and a starting capital figure that the risk view uses to frame the results.
4. Derived views. Total P&L, win rate, average win and loss, per-ticker and per-strategy totals, monthly buckets, drawdown, and the dashboard KPIs. These are calculations, not entries. In a well-built sheet, nothing here is ever typed.

The journal gives each of these its own sheet: Trade Log for the trades, Performance Summary for the per-ticker view, Monthly P&L for the calendar view, Strategy Analysis for the per-setup view, Risk Analysis for drawdown and sensitivity, and a Dashboard on top, with a How to Use sheet carrying the instructions. The Trade Log is the only sheet where trades get typed. The others take at most a small setting, a journal year, a ticker label, the risk assumptions and a starting capital figure, and read everything else from the log.

The reason to prefer that structure over a page of standalone tables is drift. When two summaries hold the same fact independently, the day one of them is updated and the other is not, they disagree, and a journal that quietly disagrees with itself is worse than no journal at all. Reading everything off one log removes the possibility: the ticker view, the monthly view, the strategy view, and the dashboard are not copies of the trade rows, they are functions of them, so they move the instant the source does. That single idea is what the rest of this walkthrough is about, sheet by sheet.

Start with the trades: the Trade Log

The Trade Log is the only place a trade is ever typed, and it is where every other number in the workbook begins. Each row is one completed trade, laid out across ten columns. Eight are entries and two calculate.

The entry columns are Date, Ticker, Side, Entry Price, Exit Price, and Shares, with Strategy and a free-text Notes column sitting to the right of the two calculated ones. Side is a dropdown with two values, Long and Short. Strategy is a dropdown too, listing Momentum, Swing, Breakout, Reversal, Earnings Play, and Other, which matters more than it looks, because the strategy sheet later groups on the exact text in this column, and a free-typed label that does not match spelling for spelling would fall out of its bucket.

Two columns are formulas:

• P&L = for a Long trade, (exit price - entry price) × shares; for a Short trade, (entry price - exit price) × shares
• % Return = the same difference expressed over the entry price

That single P&L formula is what lets one log hold both directions of trade without a second column or a sign convention to remember. A Long trade makes money when the price rises after entry. A Short trade makes money when the price falls after entry, and the formula flips the subtraction so a decline registers as a gain. Investor.gov’s plain-language explainer on long and short positions describes the same two directions the dropdown encodes.

One row from the sample log makes the flow concrete. The first trade is a Long on AAPL from June 5, entered at $182.50 and exited at $194.80 across 100 shares. The P&L is (194.80 - 182.50) × 100, which lands at $1,230, and the % Return reads 6.7%. A Short a week later shows the other direction: a Short on TSLA entered at $412 and covered at $388.50 across 30 shares books (412 - 388.50) × 30, or $705, at a 5.7% return, because the price fell after the short was opened. The render displays prices rounded to whole numbers to save width, so 182.5 shows as 183, while the underlying cells keep the decimals the formulas use.

Two design details are worth copying into any hand-built version. First, the spare rows are pre-wired. The Trade Log carries the P&L and % Return formulas down the full sheet and states support for up to 200 trades, so the next trade goes on the first free row and the totals update with no formula to drag and no range to extend. Second, everything downstream reads this one log. The totals row at the bottom sums the P&L column and computes the win rate as the count of positive trades over the total count. Across the 28 sample trades that comes to $11,299 of net P&L and a 75% win rate, meaning 21 of the 28 closed above zero. Those two figures are the ones every other sheet is built to explain from a different angle.

What the log does not do is fill itself. There is no connection to a broker and no fill import, so every trade is a handful of typed cells after you close it. For a few trades a week that is a couple of minutes. For someone running dozens of trades a day it is real work, which is what execution-platform analytics exist to remove. The tradeoff is automation against control. A connected tool suits traders who want the data to arrive on its own. A spreadsheet suits traders who want to read every formula, change any of them, add a strategy label the software never heard of, and keep the file on their own machine.

Read the log by ticker: the Performance Summary

The Performance Summary groups the same trades by symbol. Every cell is computed; nothing on the sheet is typed except the ticker labels themselves, and even those drive their own rows. For each ticker it shows four numbers: total P&L, number of trades, number of wins, and win rate.

The sample log spreads 28 trades across 13 tickers, and the spread is where the sheet earns its place. The largest contributor by P&L is NVDA at $2,614 across three trades, followed by AAPL at $2,347 across three, both computed straight from the log. One ticker sits in the red: AMD shows -$1,045 across two trades, a 0% win rate for the pair, and it is the only symbol in the sample that lost money. Reading that off a brokerage app would mean scrolling a fill history; here it is one row. The bottom ALL TICKERS row reconciles to the same $11,299 total, 28 trades, 21 wins, and 75% win rate the Trade Log reports, which is the built-in check that the per-ticker view has not drifted from its source.

Below the ticker table sits a Key Metrics block that lifts six headline figures out of the log: total P&L of $11,299, a 75% win rate, an average win of $660, an average loss of -$367, a best trade of $1,512, and 28 total trades. The average win and average loss are worth a second look together. The winners in the sample average roughly $660 while the losers average roughly $367, so an average winner is around 1.8 times the size of an average loser. That relationship, sometimes called the win-to-loss ratio, is a different lens on results than the win rate, because a record can carry a high win rate with small wins and large losses, or the reverse. The sheet reports both so neither one tells the story alone.

The ticker table has spare rows too. The How to Use sheet notes that the sample fills 13 rows and that typing any other symbol into a blank row below them pulls its P&L, trade count, wins, and win rate automatically. As with the log, the structure does the extending, not the user.

Read the log by month: the Monthly P&L

The Monthly P&L sheet rolls the same trades into calendar buckets, January through December, split by strategy. A Journal Year cell at the top scopes it: set the year, and every month bucket follows the trades whose dates fall inside that year. Every figure below is computed from the Trade Log, none of it typed.

The sample trades all fall in a single summer window, so the grid concentrates the activity into three columns. June totals $3,164, July $3,960, and August $4,175, and the nine other months sit at zero because no sample trade dated into them. The strategy rows inside each month show what drove it: Momentum contributes across all three months and totals $5,901 for the year, while Breakout runs negative in June and July before turning positive in August. A Year Summary block at the bottom distills the grid to four statistics: a year-to-date P&L of $11,299, a best month of $4,175, an average monthly P&L of $3,766, and a count of three profitable months. The average is taken over the months that carried trades, which is why $11,299 across the three active months reads as roughly $3,766 rather than being diluted by the empty ones.

The calendar view is the one that surfaces streaks and gaps that a single running total hides. A quarter that nets out flat can still hide a strong month and a weak one, and seeing them side by side is the point of bucketing the log this way rather than reading one cumulative figure.

Read the log by setup: the Strategy Analysis

Where the Performance Summary slices by ticker, the Strategy Analysis slices by the label in the Strategy column. The sheet recognizes five named setups, Momentum, Swing, Breakout, Reversal, and Earnings Play, plus an Other bucket that catches any label that does not match one of the five. For each it computes total P&L, trade count, wins, win rate, and an average percentage return per trade.

In the sample, Momentum carries the record. Its eight trades all closed green, for a 100% win rate, $5,901 of P&L, and a 5.2% average return, which is a bit over half of the $11,299 total from a single setup. Reversal shows five trades, all winners, at $3,015 and a 5.2% average return. Swing is the busiest mixed bag, seven trades at a 57.1% win rate for $1,367. Breakout is the one setup that lost money in the sample, one win in five trades for -$421 and a -1.2% average return. An ALL STRATEGIES row reconciles to $11,299 across 28 trades and 21 wins, the same totals every other sheet reaches. Below the table, a Strategy Insights block names the extremes outright: best by P&L is Momentum at $5,901, worst by P&L is Breakout at -$421, and highest win rate is Momentum at 100%.

A word of care about how to read that. These figures describe this particular sample log and nothing beyond it. A setup that shows a 100% win rate across eight recorded trades is a fact about eight trades, not evidence that it will keep winning, and a small sample can flatter or punish any label by chance. What the sheet does is make your own history legible so you can see how each label you use has actually landed. It does not rank the setups for you or suggest trading one over another, and it makes no claim about what any of them will do next.

Frame the results against risk: the Risk Analysis

The last analysis sheet turns from returns to risk, and it does two separate things that are worth keeping apart.

The top half is a sensitivity grid. It takes three assumptions, an 8% average win as a share of position size, a 5% average loss, and a 20-trade run, then lays out the expected P&L those assumptions produce across a matrix of win rates and position sizes. Win rates run down the side from 40% to 80%, position sizes run across the top from $500 to $50,000, and each cell is the arithmetic of the two. At a 50% win rate on a $5,000 position, the grid reads $1,500; at a 60% win rate on a $10,000 position it reads $5,600; at an 80% win rate on a $50,000 position it reaches $54,000. This grid is self-contained. It runs off its own assumptions, not off your Trade Log, so the numbers do not move when you log a trade. It is a way to see how expected results scale with size and hit rate in the abstract, and it is explicitly a set of assumptions rather than a projection of any real account.

The bottom half is the part that reads your actual log. It walks the running P&L of the logged trades and reports the deepest pullback along the way. In the sample, against a starting capital of $50,000 that you set yourself, the log’s $11,299 of P&L came with a maximum drawdown of just -$550, which works out to -4.9% of the running P&L peak. The largest single losing trade was also -$550, and the longest streak of consecutive losing trades was one. Read together, those figures say the sample path never gave back much before recovering, because no two losers landed back to back. That is a description of one particular sequence of trades, not a promise about volatility, and a different log with the same total P&L could show a far deeper drawdown. The starting-capital cell is a plain input, and a Portfolio Return % line restates the logged P&L against whatever account size you enter, reading 22.6% at $50,000. The drawdown percentage is measured against the running P&L peak instead, so it stays put when the account size changes.

The dashboard: six numbers and the setups behind them

With the Trade Log filled, the Dashboard on top computes the record at a glance. Six KPI tiles carry the headline figures, all read from the log: a total P&L of $11,299, a 75% win rate, 28 total trades, an average win of $660, an average loss of -$367, and a best trade of $1,512. The best-trade tile points at a single row, an NVDA Long from early August entered at $112 and exited at $124.60 across 120 shares, which booked $1,512 at an 11.3% return and stands as the largest single winner in the sample.

Below the tiles, a Strategy Breakdown table repeats the per-setup view, and under it sits a pie chart titled P&L by Strategy. The chart sizes each slice by the magnitude of a strategy’s P&L rather than its direction, so it shows which setups carried the most weight while the sign stays in the table above, where Breakout reads -$421. A currency selector next to the title relabels every money column and KPI across the workbook, with 35 symbols to choose from. It relabels only, with no conversion of the underlying numbers, so switching the symbol changes the labels without touching the values.

The point of the dashboard is the same as the point of the whole file: one screen that answers the questions a fill history buries. It shows what the account did, how often it did it, and which setups and symbols were behind the result, all traced back to the one log that everything else reads.

Excel or Google Sheets for a trading journal

The journal is an .xlsx file built on standard formulas, with no macros and no add-ons, so it runs the same way in Microsoft Excel, in Google Sheets after upload, and in LibreOffice Calc. The aggregation leans on SUMIF, COUNTIF, and their multi-condition cousins, all of which have worked in Excel since the 2007 release and behave identically in Sheets. Sheets suits traders who log from a phone or want the file reachable from any browser; Excel suits those who prefer a local file and heavier keyboard work. The structure described here is equally buildable in either, and the download opens in all three without a conversion step.

Where the numbers go at tax time

For US filers, a trading record maps onto tax reporting, though the exact path depends on how the activity is classified. The IRS distinguishes between an investor and a trader in securities, a line that turns on the nature, frequency, and intent of the activity rather than on any single trade. IRS Topic no. 429 sets out that most people report gains and losses on Schedule D and Form 8949 and are subject to the wash-sale rules, while those who qualify as traders have additional choices such as a mark-to-market election under section 475(f). Which path applies is a determination for a tax professional, not a spreadsheet. What the journal does is get you there with the raw material already gathered: every trade dated, priced, and totaled, so filing prep becomes a copy job rather than a reconstruction from broker emails.

Which journal fits which trader

• The free Stock Trading Journal is a single trade log and nothing more, which fits someone who wants to start recording trades before deciding whether the analysis sheets are worth it.
• The Stock Trading Journal Essentials ($19) logs each buy and sell leg against a trade ID with a fees column, and adds a Dashboard reporting total P&L, win rate, and the counts of closed, winning, and losing trades, without the deeper per-ticker, monthly, strategy, and risk sheets.
• The Stock Trading Journal Ultimate Spreadsheet Template ($29) is the workbook this walkthrough follows in full: the trade log, the per-ticker summary, the monthly calendar, the strategy breakdown, the risk view, and the six-metric dashboard, all reading from one log.
• The Options Trading Journal Ultimate Spreadsheet Template ($29) applies the same one-log structure to options, swapping the stock fields for contract details, an expiration calendar, and a Greeks tracker for traders working that instrument instead of, or alongside, shares.

Related

• How to Track an Investment Portfolio in a Spreadsheet - the buy-and-hold counterpart, where one holdings list drives allocation, dividends, and returns
• How to Track Investment Returns in Google Sheets (TWR vs MWR) - the two ways to measure a return once deposits and withdrawals are in the mix
• Google Sheets vs Excel for Investment Tracking - how the same tracking structure behaves across the two platforms