Advertisement IC Markets

Analyzing Your Trades With a Pivot Table

Document your personal trading journey. Track daily equity curves, review winning and losing streaks, share trade screenshots, and get constructive feedback.
Post Reply
Fairman
Posts: 6955
Joined: Tue Jul 21, 2026 7:11 am
Location: Abuja

Analyzing Your Trades With a Pivot Table

Post by 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.
It’s Fairman :geek:
Recommended broker for automated trading & scalping IC Markets
Fairman
Posts: 6955
Joined: Tue Jul 21, 2026 7:11 am
Location: Abuja

Re: Analyzing Your Trades With a Pivot Table

Post by 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.
It’s Fairman :geek:
Fairman
Posts: 6955
Joined: Tue Jul 21, 2026 7:11 am
Location: Abuja

Re: Analyzing Your Trades With a Pivot Table

Post by 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.
It’s Fairman :geek:
FXS Prop Desk
Posts: 1689
Joined: Wed Oct 07, 2026 9:52 am

Re: Analyzing Your Trades With a Pivot Table

Post by FXS Prop Desk »

Add a column for "rule followed: yes/no" to the journal. In the pivot table, this lets you compare trades taken by the plan with trades that broke it, which is often the most revealing analysis.

Updating the pivot table weekly or monthly keeps the analysis current and helps spot changes in performance quickly.

Filtering the same table by month also shows whether rule-breaking trades become more frequent at certain times, such as after losing weeks, which points to a fixable pattern.
FXS Official Team | The Idea Lab & Strategy
Shadow Trader
Posts: 5594
Joined: Wed Sep 09, 2026 5:19 pm

Re: Analyzing Your Trades With a Pivot Table

Post by Shadow Trader »

Before trusting any cell, it is worth checking how much of its average comes from one trade. Averages in R are easily dragged by a single runner, and a pivot table shows the mean without any warning.
img_13434_r1.png
img_13434_r1.png (67.89 KiB) Viewed 180 times
In the made up pivot on the chart, the pullback setup in New York shows plus 0.22R over 26 trades, which looks like a keeper. Take out its best trade, a 6R runner, and the other 25 average about minus 0.01R, so the whole cell rests on one day.

Two extra value fields catch this quickly. Put the max of R next to the average, plus the median in Google Sheets, and any cell where they disagree badly needs a closer look.

The minimum sample idea also works better as a rule inside the sheet than as a habit. Conditional formatting that greys out any cell under 20 trades stops the eye landing on the exciting plus 0.85R in a cell of six.

Splitting the same pivot by quarter helps too. A cell can look fine over a year and still have done all its work in one month.

Does anyone keep a column for the planned target, so runners beyond it can be flagged? That would separate a good exit plan from a lucky afternoon.
Shadow Trader
Posts: 5594
Joined: Wed Sep 09, 2026 5:19 pm

Re: Analyzing Your Trades With a Pivot Table

Post by Shadow Trader »

The rule followed column gets more useful if the no rows also say which rule was broken. A pivot with the broken rule as rows might show early entries averaging about minus 0.3R while widened stops average minus 1.4R, and those call for very different fixes.

There is a quieter trap in how R itself is recorded. If a trade starts with a 10 pip stop that later gets tightened to 5 and is hit, the loss is minus 0.5R measured from the original stop but minus 1R from the final one, and mixing both habits in one column makes every average slightly wrong.

Fixing R to the initial stop on every row keeps the units comparable. A separate column for the final stop distance then lets the pivot show what the stop management itself is doing.

Gross and net need the same care. If costs sit in their own column as the opening post suggests, one pivot of average R before costs and one after shows which setups only work on paper, and the gap tends to be widest on the shortest scalps.

Does anyone pivot by the day a trade closed rather than the day it opened? Trades held overnight land in different cells depending on which date gets used.
Shadow Trader
Posts: 5594
Joined: Wed Sep 09, 2026 5:19 pm

Re: Analyzing Your Trades With a Pivot Table

Post by Shadow Trader »

Breaking results into hourly or session buckets is a common way to hunt for the best time to trade. The weakness is arithmetic: twenty-four buckets cut a modest journal into slices of a handful of trades each, and at that size, striking differences appear by chance alone.

Several habits make the table more honest. First, start with coarse blocks such as Asian, London, overlap, and late New York, and split further only when the data is thick enough. Second, report the number of trades beside every figure, so that a seventy percent hit rate on nine trades is visibly fragile. Third, compare expectancy in R rather than win rate alone, since a bucket can win often and still lose money after costs.

Costs deserve their own column. Spreads widen around rollover and thin hours, so a bucket that looks mediocre may reflect execution rather than signal quality. Market conditions also differ by weekday and by season, which means a pattern found in one quarter may fade in the next.

A practical test is to split the history in half and check whether the best and worst buckets from the first half stay the same in the second. If they swap places, the table was mostly noise.
Post Reply