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.
Create these sheets before you add formulas:
| Sheet | Purpose | Editing rule |
|---|
Raw import | Holds the source export from your broker or journal | Do not rewrite imported values |
Trades | Holds one normalized row per closed trade | Map source fields and add review fields here |
Review | Holds checks, filters, and summary tables | Read from Trades; do not type trade results here |
Lists | Holds the allowed values for setup, adherence, and tags | Change a label in one place |
The separation protects provenance. You can see which values came from the source, which values Excel calculated, and which values you entered during review.
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.
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:
| Group | Field | Source | Required use |
|---|
| Identity | Trade ID | Imported | Prevent duplicate rows |
| Identity | Account | Imported | Keep accounts separate |
| Identity | Symbol | Imported | Filter comparable trades |
| Execution | Direction | Imported | Preserve long or short |
| Execution | Quantity | Imported | Preserve the traded size |
| Execution | Entry value | Imported | Reconcile the entry |
| Execution | Exit value | Imported | Reconcile the exit |
| Execution | Entry timestamp | Imported | Order and group trades |
| Execution | Exit timestamp | Imported | Order and group trades |
| Result | Gross P&L | Imported when available | Separate market result from costs |
| Result | Fees | Imported when available | Reconcile trading costs |
| Result | Net P&L | Imported | Review the recorded result |
| Result | Currency | Imported | Prevent mixed-currency totals |
| Context | Setup | User-entered list | Compare one defined setup |
| Context | Followed plan | User-entered yes or no | Separate process review from result |
| Context | Deviation | User-entered list | Name the rule that changed |
| Context | Note | User-entered text | Record one useful observation |
| Review | Reviewed | User-entered yes or no | Find incomplete rows |
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.
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.
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.
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:
| Field | Example rule | Check before use |
|---|
| Has source ID | =IF([@[Trade ID]]="","Missing","OK") | Every imported trade needs a stable source ID |
| Has currency | =IF([@Currency]="","Missing","OK") | Every monetary result needs a currency |
| Review complete | =IF([@Reviewed]="Yes","Complete","Open") | Use only the allowed review labels |
| Net reconciliation | =[@[Gross P&L]]-[@Fees] | Use only when the source defines fees with this sign convention |
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.
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:
- Confirm the trade ID and account.
- Confirm the symbol, direction, and quantity.
- Confirm the entry and exit values.
- Confirm gross P&L, fees, net P&L, and currency.
- Confirm that the trade appears once.
- 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.
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:
- Completeness: Which rows have a missing ID, currency, setup, or review state?
- Process: How many trades followed the plan for each setup?
- Deviations: Which rule categories appear most often?
- Results: What are trade count and net P&L for each setup and currency?
- Evidence: Which source rows need a manual reconciliation?
- 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.
Use the same sequence for every new batch:
- Save the original export.
- Check the export period and account scope.
- Replace or append data in
Raw import according to one written rule.
- Map source rows into
Trades.
- Check duplicate trade IDs.
- Reconcile a sample with the source.
- Complete the user-entered review fields.
- 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.
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.
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.
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.
