Analyzing Your Trades With a Pivot Table

[Fairman]

Analyzing Your Trades With a Pivot Table

Once you have a journal with consistent fields, a pivot table turns it into answers. The tool exists in all major spreadsheet programs, and the skill is quick to learn.

Start with a clean table: one row per trade, columns for date, pair, session, setup, direction, result in R, costs in R, and flags. Make sure categories are spelled consistently.

Create a pivot table with a category as rows, such as setup, and values such as the count of trades, the sum of R, and the average R. You instantly see which setups produce what.

Add a second dimension as columns, such as session, to see setup performance by session. Or use the weekday for a calendar view.

Calculate win rate by adding a column that marks wins as one and losses as zero, and averaging it in the pivot.

Filter by flags. Compare clean trades with flagged ones to measure the cost of rule violations.

Be mindful of sample sizes. A cell with five trades is an anecdote. Combine categories until each group has at least twenty or thirty trades, before drawing conclusions.

Keep a record of the questions you ask and the answers, so that you can see whether patterns persist across periods.

Refresh the data and review monthly.

Pivot analysis costs little, and surfaces patterns that memory and impressions miss.

Practical step: create a pivot table of average R by setup and by session from your journal.

[Fairman]

Consistent spelling is the hidden requirement. One typo in a setup name and the pivot table shows it as a separate setup with three trades. Use a dropdown list for every category column: setup, pair, session and flags. In Google Sheets or Excel, data validation does it in a minute. It also stops gradual drift where London becomes LDN and then London open over a few months. If your journal already has messy entries, clean them once with find and replace before building the pivot, then lock the list. Whitespace at the end of a word causes the same problem and is harder to spot, so trim those too.

[Fairman]

Average R by setup and session is a great first table. Most people find one combination doing most of the work and one quietly losing. For example, breakout trades in the first two hours after the London open at 8am Lagos might average plus 0.4R, while the same setup in the late New York session averages minus 0.2R. That's an easy change: stop taking that setup in that session. Check the trade count in each cell before acting, though. If the losing combination has only eight trades, keep logging. Around 30 trades per cell is where I start trusting the difference enough to change the plan.