Trading Journal Excel: How to Build a Spreadsheet You Can Review

An Excel trading journal can start with a few columns: instrument, entry, exit, quantity and profit or loss. Those columns record the result, but they leave out much of what you need when reviewing a trade.

Which setup did you take? Was the quantity planned? Did charges turn a small gross profit into a loss? Why did you exit?

A useful spreadsheet keeps those answers beside the numbers. You can then filter your trades, compare setups and read the notes behind each result.

This guide explains how to create a trading journal in Excel, calculate results after charges and organise a weekly review. The calculations use clearly labelled fictional trades.

What should an Excel trading journal record?

A spreadsheet should help you connect results with decisions. Begin with execution details and the original plan, then add fields that make comparisons easier.

Use a separate status field if you want to record open positions. The formulas in this article are for closed trades; open positions should not enter the same performance totals.

Set up three sheets in your workbook

1. Sheet 1 · Trades

   One row per closed trade

2. Sheet 2 · Summary

   Calculated totals

3. Sheet 3 · Weekly Review

   Observations and actions

Sheet 1: Trades

Use this sheet for individual trade records. Keep field names consistent so formulas and filters work predictably.

Sheet 2: Summary

Use this sheet for totals calculated from completed records. Show the number of trades alongside performance figures so readers can see the sample size.

Sheet 3: Weekly Review

Record observations with supporting trade IDs. Add a specific change to evaluate during the next period.

For example, “review early exits” becomes more useful when it points to the trades where your execution differed from your written exit rule.

Create the columns in your Trades sheet

Select the range and format it as an Excel Table. Give it the name Trades if you use the summary formulas below.

Make data entry consistent

Use dropdowns for fields you will filter later. For example, choose one label for a setup instead of switching between “breakout,” “break out” and “breakout entry.”

Format dates as dates and prices as numbers. Enter quantities in actual units rather than mixing shares, lots and contracts in the same calculation.

Add your Excel trading journal formulas

These formulas assume one fully closed position with a single entry and exit price. For partial fills, partial exits or multi-leg strategies, use a separate execution ledger before summarising the trade.

Gross P&L

For a long position, the calculation is exit price minus entry price, multiplied by quantity. For a short position, the subtraction is reversed.

A blank result means required information is missing or the direction label does not match.

Net P&L after charges

Record charges using your transaction records. Enter zero explicitly when appropriate; a blank cell should indicate incomplete information.

Initial planned risk

This measures the distance from entry to the original stop, multiplied by quantity, before costs.

Check the stop’s placement separately. The absolute-value formula cannot determine whether it is on the correct side of the entry.

Net R-multiple

This expresses net P&L relative to initial planned price risk before costs. Label that convention in the workbook.

Actual losses may exceed planned risk because of execution prices, gaps or costs.

Some regional Excel settings require semicolons instead of commas. Test formulas in the workbook environment you intend to use.

Test the sheet with fictional trades

Before entering your records, test a profitable long trade, a losing long trade and a profitable short trade.

For DEMO-002:

Gross P&L = (₹177 − ₹180) × 50
          = −₹150

Net P&L   = −₹150 − ₹25
          = −₹175

For DEMO-003:

Gross P&L = (₹320 − ₹316) × 40
          = ₹160

Net P&L   = ₹160 − ₹30
          = ₹130

Check that incomplete entries remain blank. Also test an invalid direction and a zero-risk entry so they do not silently produce misleading figures.

In the workbook, allow positive quantities only, validate direction through a dropdown, flag stops on the wrong side of the entry and keep incomplete records out of summary calculations.

Build a small summary dashboard

Start with figures that help you review the records. You can add more calculations once you know what questions they answer. These formulas require an Excel Table named Trades with the exact column headings above.

Format win rate as a percentage. In this definition, break-even trades count as completed trades but not as winners.

For the three fictional trades, total net P&L is ₹362.40, with two winners and one loser. That small sample demonstrates the calculations; it cannot establish a trading strategy’s performance.

Show cumulative closed-trade P&L

A cumulative P&L chart shows how closed-trade results add up. It is different from account equity, which may include deposits, withdrawals and unrealised positions.

Compare results by setup

Use a PivotTable with setup as the row field and net P&L as a value. Add a trade count so the total has context.

A setup with one profitable trade has limited evidence behind it. Read the individual notes before drawing conclusions from a total.

Add a review of trading mistakes

Keep mistake labels specific enough to act on.

Leave the tag blank when no mistake is identified. Avoid forcing every losing trade into a mistake category.

The trading mistake tracker guide explains how to organise these observations.

Use the spreadsheet for a weekly review

Review completed trades over a consistent period. Check the data before interpreting the dashboard.

1. Reconcile executed prices, quantities and charges.
2. Read trades where execution differed from the original plan.
3. Compare setups while checking their sample sizes.
4. Look for repeated situations in the review notes.
5. Write one specific change to evaluate next week.

A pre-trade checklist can support the rules you decide to evaluate.

Common Excel trading journal errors

Mixing open and closed trades

Keep unrealised results separate from completed-trade summaries. Do not enter an estimated exit price as though the position has closed.

Ignoring charges

Keep gross and net P&L visible. A gross result can give a different impression once costs are included.

Overwriting the original stop

Record the original stop separately from later changes. Otherwise the risk calculation can change after you know the outcome.

Double-counting slippage

When P&L uses actual execution prices, those prices already affect the result. Track the difference from your planned price separately without subtracting it again.

Treating partial exits as one simple execution

If you enter or exit at different prices, record each execution. Aggregate quantities, costs and results before creating the trade-level summary.

Changing formula cells accidentally

Protect calculated columns and keep a backup. Test calculations again whenever you change the sheet’s structure.

When should you consider a trading journal app?

Excel gives you control over the structure. As your records grow, you may prefer a workflow that brings trade details and written reviews together without maintaining the workbook yourself.

Compare the features you actually need: entry workflow, mistake tracking, screenshots, review tools and export options. Check which features a product supports before deciding.

You can try the MyTradeDiary demo to see whether its workflow suits your routine.

Frequently asked questions

Start with a sheet you can maintain

Build the Trades sheet first and test its formulas. Record the original plan, complete the execution details and review the notes alongside the totals.

Once that routine works, add the summaries you need. A spreadsheet becomes useful when its records are reliable enough to support a decision.