Options Trading Log Excel Template Free: Track Every Trade and Your Real Win Rate (2026)

This free options trading log is an Excel workbook where each options trade is one row: you enter the date, ticker, strategy, strike, expiry, contracts, entry premium, and exit premium, and the sheet calculates realized profit or loss, your win rate, average win and loss, and P/L by strategy (covered calls, cash-secured puts, spreads, long options). The math uses standard Excel formulas, so the log works without any add-in. With MarketXLS installed, extra columns pull the underlying's price with =QM_Last("AAPL") and its 30-day volatility with =StockVolatilityThirtyDays("AAPL"). Both a pre-filled sample and a blank template are free to download below.

A broker statement records fills, but it does not tell you whether your cash-secured puts beat your long calls, how your average loss compares to your average win, or how much capital you have at risk right now. A trade log answers those questions because every rollup is a formula you can read.

Excel options trading log vs broker statements and web journals

Before you build anything, it helps to see why a spreadsheet beats the alternatives for serious record keeping. The table below is the quick summary of what a dedicated Excel options journal gives you that a broker export or a browser app does not.

The point is not that broker tools are useless. They are the system of record for fills and taxes. The point is that they were never designed to answer the questions a trader actually cares about: what is working, what is not, and how much am I risking to find out. A spreadsheet answers those questions because you define the math, and MarketXLS keeps the market data flowing so the log does not go stale the day after you build it.

Why keep an options trade journal

A journal shows you your own process: which strategies work for you and which do not. When implied volatility is compressed and premium sellers crowd into cash-secured puts and covered calls, your log tells you whether those income trades are actually clearing after the occasional assignment. When a name runs into earnings and you buy a long call, your log tells you whether you take profit before the volatility crush or hold too long out of hope.

None of this is investment advice, and the template makes no prediction about any ticker. It is a record-keeping and educational tool. The value is entirely in the discipline of writing down what you did and letting the arithmetic be honest with you. A trader who logs 40 trades and discovers a 35 percent win rate on long puts but a 78 percent win rate on cash-secured puts has learned something no market forecast could tell them.

What Goes Into an Options Trade Log

A good options journal captures enough to reconstruct the trade and score it, without so many fields that you stop filling it in. The core columns in this template are:

• Date opened so you can sort by time and measure holding periods
• Ticker so the log can pull live data for the underlying
• Strategy such as covered call, cash-secured put, bull put spread, long call, long put, or iron condor
• Type and direction meaning call or put, and whether you are long (debit) or short (credit)
• Strike and expiry so the position is fully specified
• Contracts because P/L scales by 100 shares per contract
• Entry premium and exit premium the two numbers that drive realized profit
• Status open, closed, expired, or assigned
• Realized P/L computed automatically from direction, entry, exit, and contracts

That last column is where the arithmetic lives. For a single-leg options trade, realized profit and loss is:

Realized P/L = IF(Direction = "Long", 1, -1) * (Exit Premium - Entry Premium) * 100 * Contracts

When you are long an option, you profit when the exit premium is higher than the entry premium. When you are short (you sold to open and collected a credit), you profit when the option decays and you buy it back cheaper, so the sign flips. Multiplying by 100 accounts for the standard contract multiplier, and multiplying by contracts scales the whole thing to your position size. Spreads are logged as the net credit or debit, so the same formula still applies at the position level.

The MarketXLS Formulas That Keep the Log Alive

The difference between a dead spreadsheet and a living one is where the market data comes from. If you retype prices by hand, the log is stale before you finish. MarketXLS turns each data point into a formula that refreshes on demand inside Excel. Every formula below was verified against the MarketXLS function documentation before it went into the template.

For the live price of any underlying you have traded, the log uses:

=QM_Last("AAPL")

Formula documentation: QM_Last

QM_Last returns the last traded price on demand from QuoteMedia (a snapshot that updates when Excel recalculates), and it accepts a cell reference so the whole column can read the ticker you typed. On the Standard plan, US stock prices are 15-minute delayed; Advanced and Business are real-time. It also accepts an option contract symbol, so you can track the last price of an open position. Build the symbol with OptionSymbol rather than typing it:

=QM_Last(OptionSymbol("AAPL",DATE(2026,10,16),"C",220))

Formula documentation: QM_Last, OptionSymbol

Options data is end-of-day on the Standard plan and real-time on Advanced and Business. In the Microsoft 365 add-in (Excel for Mac and the web), add the mxls. prefix, for example =mxls.QM_Last("AAPL").

To frame how much a name typically moves, the log pulls 30-day realized volatility:

=StockVolatilityThirtyDays("AAPL")

Formula documentation: StockVolatilityThirtyDays

This returns volatility as a decimal, so 0.0212 means 2.12 percent. Comparing that realized figure to the implied volatility you paid or collected is one of the most useful habits a premium trader can build, and having it beside every trade makes the comparison automatic.

The underlying watchlist and open-positions sheets add a few more verified formulas so you can see trend and risk at a glance:

=SimpleMovingAverage("AAPL", "50")   ' 50-day simple moving average
=RelativeStrengthIndex("AAPL", "14") ' 14-day RSI momentum
=Beta("AAPL")                        ' Beta versus the market
=DividendYield("AAPL")               ' Trailing dividend yield

Formula documentation: SimpleMovingAverage, RelativeStrengthIndex, Beta, DividendYield

And when you want to see which contracts are actually liquid before you plan a trade, the strategy sheet references the active option chain and the Greeks:

=QM_GetOptionChainActive("SPY")        ' Chain with zero-volume contracts excluded
=QM_GetOptionQuotesAndGreeks("NVDA")   ' Chain with delta, gamma, theta, vega, rho

Formula documentation: QM_GetOptionChainActive, QM_GetOptionQuotesAndGreeks

Because these are formulas rather than pasted values, you do not retype data. Open the workbook, refresh, and the underlying prices, volatility, RSI, beta, and yields update.

How the Template Is Built, Sheet by Sheet

The workbook comes in two files. The static sample is pre-filled with ten example trades and a formula-reference note so you can see exactly how the math flows before you touch anything. The live template is the working file: yellow input cells for your trades, and MarketXLS formulas everywhere the data can be fetched. Both share the same six-sheet structure.

1. How To Use

A short tutorial sheet that explains the workflow, marks which cells are inputs, and lists every MarketXLS function the workbook relies on with a plain-language description. Start here the first time you open it.

2. Trade Log

The heart of the journal. One row per trade, with yellow input cells for the date, ticker, strategy, type, direction, strike, expiry, contracts, entry premium, exit premium, and status. Two columns fill themselves from live formulas: the current underlying price via QM_Last and 30-day volatility via StockVolatilityThirtyDays. The realized P/L column applies the direction-aware formula above and totals at the bottom, with winners shaded green and losers shaded red so the ledger reads at a glance.

3. Performance Summary

This sheet is why you keep the log. It rolls up every closed trade into the numbers that matter:

• Total, closed, and open trade counts
• Winning and losing trade counts
• Win rate, calculated as winners divided by all decided trades
• Total realized P/L
• Average win and average loss
• Largest win and largest loss

Below that, a per-strategy table uses COUNTIF and SUMIF to break profit and loss out by strategy, so you can finally see whether your covered calls, your cash-secured puts, or your long options are carrying the account. A representative rollup formula looks like this:

=SUMIF('Trade Log'!$C$4:$C$28, "Cash-Secured Put", 'Trade Log'!$N$4:$N$28)

4. Strategy Playbook

A reference sheet describing the six strategies the log tracks: market view, structure, maximum profit source, maximum risk, and what to note when you log each one. It is education only, but it keeps the strategy names consistent so your SUMIF rollups stay clean. The live version also points at real chain and Greeks formulas so you can research a setup from the same sheet.

5. Open Positions

Enter your open trades and the sheet computes live notional exposure, pulls the current underlying price with QM_Last, and shows beta and 30-day volatility for each name. This is your at-a-glance capital-at-risk view, which matters most on the days you are tempted to add just one more position.

6. Underlying Watchlist

A live dashboard of every ticker you trade: last price, 50-day moving average, RSI, beta, 30-day volatility, dividend yield, and a simple trend flag that reads "Above 50 DMA" or "Below 50 DMA." It gives context to the names in your log without leaving the workbook.

A Worked Example

Say you sell two cash-secured puts on a large-cap name for a credit of 3.10 per contract and later buy them back for 0.40. You are short (you collected the credit), so the realized P/L is:

= -1 * (0.40 - 3.10) * 100 * 2
= -1 * (-2.70) * 100 * 2
= 540.00

You log the entry premium, the exit premium, the direction, and the contract count, and the template does the rest. The trade lands in the Performance Summary as a win, lifts your cash-secured put win rate, and adds 540 to your total realized P/L. Do this for every trade and, after 30 or 40 rows, the Performance Summary stops being a spreadsheet and starts being a mirror. That is the entire point of keeping the log.

Download the Templates

Download the templates:

• - Ten example trades pre-filled so you can see the math
• - Live-updating formulas, yellow input cells ready for your trades

Both files open in Excel. The live formulas refresh when you have MarketXLS installed and connected. You can learn more about MarketXLS and browse the formula library to extend the log with other data points.

Tips for Getting Real Value From Your Log

A journal only helps if you keep it honest and keep it current. A few habits make the difference:

• Log the trade when you open it, not from memory later. The details you forget are usually the ones that explain the loss.
• Record the strategy exactly as it appears in the playbook. Consistent names keep your SUMIF rollups accurate.
• Write a one-line note on why you entered. Over time, your notes become the most valuable column in the file.
• Review the Performance Summary monthly. Look at win rate by strategy, not just total P/L, because a high win rate on a strategy that produces small wins and large losses can still lose money.
• Refresh the workbook before you review it so the live price and volatility columns reflect the current market.

None of these turn the log into a profit machine. They turn it into an honest scoreboard, which is the only thing a scoreboard should be.

Frequently Asked Questions

Is this options trading log excel template really free? The static sample is free, and the standard Excel journal calculations work without MarketXLS. Refreshing the live data columns requires a paid MarketXLS subscription; see the MarketXLS pricing page.

Do I need MarketXLS to use the log? No. You can type prices in by hand and the realized P/L, win rate, and per-strategy rollups all still work because they are standard Excel formulas. MarketXLS simply automates the live price and volatility columns so you do not have to retype quotes.

Can it handle spreads and multi-leg trades? Yes. Log a spread or an iron condor at the position level using the net credit or debit as your entry and exit premium. The direction-aware P/L formula treats the net position correctly, and the notes column is there to record the individual legs.

How do I track an open position's current value? Point QM_Last at your contract's option symbol, for example =QM_Last(OptionSymbol("AAPL",DATE(2026,10,16),"C",220)), to pull its last traded price. Compare it to your entry premium to see the unrealized move while the trade is still open. Options prices are end-of-day on the Standard plan and real-time on Advanced and Business.

Will the win rate update automatically? Yes. The Performance Summary uses COUNTIF on the realized P/L column to count winners and losers, so the moment you fill in an exit premium and set the status to closed, the win rate and averages recalculate.

Can I add my own tickers and strategies? Absolutely. The ticker cells are yellow inputs that feed the live formulas, and you can extend the strategy list. Keep the strategy names consistent with the playbook so the grouped rollups stay accurate.

The Bottom Line

A trading journal costs nothing but the discipline to keep it. This options trading log turns scattered fills and half-remembered trades into one honest scoreboard: realized P/L per trade, a real win rate, average win versus average loss, and profit broken out by the strategy that produced it. With MarketXLS formulas filling the price and volatility columns, the only thing you have to supply is the record of what you did.

Download the free files above, log your next trade the moment you open it, and let the arithmetic be honest with you. To see how far the same live formulas can go across screening, options analysis, and portfolio tracking, explore MarketXLS or book a demo to see it working inside Excel.