How to build an Excel trading journal

An Excel trading journal needs three parts: an untouched import sheet, one structured trade table, and one review sheet. Keep broker data separate from your notes. Import net P&L instead of applying one generic formula to every instrument. Add only the calculated fields that you can verify. Review the same small set of questions each week.

This guide builds that workbook from an empty file. It also gives you a test for when spreadsheet maintenance costs more than a dedicated journal saves.

The workbook structure

Create these sheets before you add formulas:

The separation protects provenance. You can see which values came from the source, which values Excel calculated, and which values you entered during review.

Step 1: Import the source file

Export a CSV from your broker or current journal. Keep a copy of that file outside the workbook. Give each export a date in its file name so you can trace a row back to its source.

Excel can open a CSV directly or import a text or CSV file into an existing worksheet. Microsoft's current CSV import instructions describe both paths. Import the file into Raw import.

After import, check these items before you continue:

• The row count matches the source file.
• Entry and exit values remain separate.
• Quantity keeps its sign or direction field.
• Fees and net P&L remain distinct when the source provides both.
• The P&L currency remains attached to each row.
• Timestamp text remains unchanged until you confirm how Excel has read it.

Do not correct source values in this sheet. Record a correction in the normalized table or replace the source export. This keeps the original record available when a total does not reconcile.

Step 2: Build one trade table

Create an Excel table on Trades. Use one row for one closed trade. If the source gives you one row per execution, group those executions into trades before you compare trade-level results.

Start with this field set:

Your export may use different labels. Map by meaning, not by column position. Never assume that two brokers use the same sign, fee, trade-grouping, or symbol rules.

Step 3: Keep currencies separate

Do not sum P&L values from different currencies into one total. A number without its currency is incomplete.

Use one of these workbook rules:

• Filter the review to one account currency.
• Create one summary table per currency.
• Add a separate conversion process with a recorded rate source and rate date.

This guide does not define a currency-conversion method. If you add one, keep the source amount, source currency, conversion rate, rate date, and converted amount in separate fields.

Step 4: Add controlled inputs

Put your allowed review labels on Lists. Use data validation on the matching columns in Trades.

A small list is easier to review than free text. Start with:

• Setup: your current setup names.
• Followed plan: Yes or No.
• Deviation: a short list of your actual rule categories, plus None.
• Reviewed: Yes or No.

Keep the note field open, but ask one stable question: What fact should affect the next review? A loss does not need an emotional label. It needs the same process check as a win or a breakeven trade.

Step 5: Add only verifiable calculations

An Excel table can apply one calculated-column formula to all current rows and new rows. Microsoft's current calculated-column guide explains this table behavior.

Useful low-risk calculated fields include:

Do not use one price-difference formula as universal P&L. Instruments, contract values, fees, partial fills, and broker conventions differ. Prefer the imported net result. Reconcile it against the source statement before you use it in a review.

Treat elapsed time as optional. Add a duration field only after you confirm that Excel reads the entry and exit timestamps consistently. Keep the original timestamp fields beside any calculated value.

Step 6: Validate the workbook

Use a small reconciliation batch before you import your full history. Pick trades that cover the conditions present in your own data, such as long and short trades, multiple executions, fees, and more than one account currency.

For each sample trade, compare the workbook with the source record:

1. Confirm the trade ID and account.
2. Confirm the symbol, direction, and quantity.
3. Confirm the entry and exit values.
4. Confirm gross P&L, fees, net P&L, and currency.
5. Confirm that the trade appears once.
6. Confirm that an incomplete review remains visible as incomplete.

Stop if the workbook does not reconcile. Fix the mapping rule. Do not overwrite one row only, because the same mismatch can affect the rest of the import.

Build the weekly review

The Review sheet should answer a fixed set of questions. Use filters, pivot tables, or formulas that read from the Trades table.

Review one currency and one defined period at a time. Then scan in this order:

1. Completeness: Which rows have a missing ID, currency, setup, or review state?
2. Process: How many trades followed the plan for each setup?
3. Deviations: Which rule categories appear most often?
4. Results: What are trade count and net P&L for each setup and currency?
5. Evidence: Which source rows need a manual reconciliation?
6. Decision: What one rule, setup definition, or data field needs a change before the next review?

Keep trade count next to every total. A result from a small group needs that context. Keep process fields next to P&L, but do not use P&L alone to label a decision as correct or incorrect.

A repeatable import routine

Use the same sequence for every new batch:

1. Save the original export.
2. Check the export period and account scope.
3. Replace or append data in Raw import according to one written rule.
4. Map source rows into Trades.
5. Check duplicate trade IDs.
6. Reconcile a sample with the source.
7. Complete the user-entered review fields.
8. Refresh the Review sheet.

Write this sequence at the top of the workbook. If you change a mapping or formula, record the change date and check the affected history again.

When Excel is still enough

Excel remains a practical journal when all of these statements are true:

• One import format covers your current workflow.
• You can reconcile each import without repeated manual repair.
• Your formulas work across the instruments and accounts in the review.
• Currency boundaries remain visible.
• You complete review fields consistently.
• Your filters and summary tables answer the weekly questions.
• Another person can understand the workbook rules without asking you to reconstruct them.

The workbook is not failing because it is manual. It is failing when manual work changes the data, hides provenance, or prevents the review.

When to move to dedicated journal software

Consider dedicated software when one or more of these conditions persist:

• Import cleanup takes longer than the review.
• Broker formats require different mapping rules.
• Partial executions are difficult to group consistently.
• Formula changes require repeated repairs to old rows.
• Account and currency filters are easy to apply incorrectly.
• Review fields and calculations live in disconnected files.
• You cannot trace a displayed result to imported, calculated, or user-entered data.
• You skip reviews because preparing the workbook takes too long.

This is an operating-cost decision. Dedicated software should reduce data preparation and keep review scope consistent. It does not guarantee better trading performance or a specific trading result. Read the Tradeways risk disclosure before you use journal analysis to support a trading decision.

Tradeways can export trade records as CSV with executions, levels, adjustments, tags, strategies, and custom fields. Its connected filters can also apply across trade logs, reviews, dashboards, and most widgets. You can inspect the Tradeways journal when you need that connected workflow. Keep your spreadsheet export as an independent record and verify any migration before you rely on the new totals.

Final workbook check

Before the first weekly review, confirm this list:

• The raw import remains unchanged.
• Every trade has a stable ID.
• Every monetary value has a currency.
• Imported, calculated, and user-entered fields are distinct.
• Net P&L reconciles with the source for the sample batch.
• Duplicate and incomplete rows stay visible.
• Setup and deviation labels come from controlled lists.
• The review uses one period and one currency scope at a time.
• The import routine is written in the workbook.
• Formula and mapping changes have a recorded date.

If all ten checks pass, the workbook is ready for repeat use. If they do not, repair the data path before you add more metrics.