Advertisement IC Markets

Spreadsheet Journal Formulas That Do the Math for You

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

Spreadsheet Journal Formulas That Do the Math for You

Post by Fairman »

Spreadsheet Journal Formulas That Do the Math for You

A spreadsheet journal becomes powerful when it calculates your statistics automatically. You don't need advanced skills, just a handful of simple formulas.

Suggested columns: Date, Pair, Direction, Setup, Risk %, Result in R, Rules followed (Yes/No), Emotion, Notes.

Useful formulas (examples using column F for Result in R and column G for Rules followed):

Total trades: count the entries in the R column.

Total R: sum the R column.

Average R per trade (expectancy): total R divided by the number of trades.

Win rate: count of trades with R above zero, divided by total trades.

Average win: average of the positive R values.

Average loss: average of the negative R values.

Rule-following percentage: count of "Yes" entries divided by total trades.

R by setup type: use a conditional sum or a pivot table with Setup as the row and R as the value.

Running equity curve: a cumulative sum column, adding each trade's R to the previous total.

Tips:

- Use a chart to plot the equity curve
- Add conditional formatting to highlight rule violations in red
- Use pivot tables to analyze by day of week, session, or emotion
- Keep one summary tab for monthly numbers

You don't need perfection. Even a basic version gives clarity.

Numbers reveal truths that feelings hide. Let your spreadsheet tell you what your memory can't.
It’s Fairman :geek:
Recommended broker for automated trading & scalping IC Markets
LondonNewsTrader
Posts: 421
Joined: Mon Sep 21, 2026 9:30 am

Re: Spreadsheet Journal Formulas That Do the Math for You

Post by LondonNewsTrader »

Useful list. Two formulas I'd add to it.

Average R on rule-following trades versus rule-breaking ones. With F as R and G as Yes/No, something like =AVERAGEIF(G:G,"Yes",F:F) and the same with "No". In most journals I've seen, the gap between those two numbers is the clearest evidence for sticking to the plan.

A news flag column. Mark each trade with whether a red-flag release fell within, say, 30 minutes of entry or during the holding period, then use AVERAGEIF on that column too. When I did this on my own records, the event trades had a noticeably larger average loss because of slippage, even though the setup was the same.

One small warning on the win rate formula: decide in advance how break-even trades are counted. If 0R trades count as losses in one month and are excluded in another, the numbers aren't comparable.
Fairman
Posts: 6955
Joined: Tue Jul 21, 2026 7:11 am
Location: Abuja

Re: Spreadsheet Journal Formulas That Do the Math for You

Post by Fairman »

Example of a formula I actually use. If R results are in column F, average R is just AVERAGE(F2:F200) and win rate is COUNTIF(F2:F200,">0")/COUNT(F2:F200). Add a rule followed column with Y or N and you can split results with AVERAGEIF, something like AVERAGEIF(G2:G200,"Y",F2:F200) for the trades where you followed the plan and the same with "N" for the rest. That last comparison is the most revealing number in the whole sheet. For most people the rule following trades come out clearly better, and seeing it in your own data does more for discipline than any amount of reading about psychology.
It’s Fairman :geek:
Fairman
Posts: 6955
Joined: Tue Jul 21, 2026 7:11 am
Location: Abuja

Re: Spreadsheet Journal Formulas That Do the Math for You

Post by Fairman »

A small caution: double-check your formulas on a few trades by hand. A wrong cell reference can quietly give you a win rate that looks great but isn't real. I found one of mine was skipping a whole month for weeks before I noticed. The usual cause is a fixed range like F2:F200 that stops counting once the journal grows past row 200. Using whole columns, like F:F, avoids that in most formulas. Another trick is a check cell that counts total trades two different ways, and turns red if the numbers disagree. It takes two minutes to set up and catches most errors.
It’s Fairman :geek:
Shadow Trader
Posts: 5594
Joined: Wed Sep 09, 2026 5:19 pm

Re: Spreadsheet Journal Formulas That Do the Math for You

Post by Shadow Trader »

Two things the averages never show are how deep the equity fell and how long the worst run of losers was. Both need one helper column each, and they fill down like the rest.

With R in column F from row 2, a running total goes in H, with =F2 in H2 and =H2+F3 in H3. The peak so far goes in I, with =MAX(0,H2) in I2 and =MAX(I2,H3) in I3, and the drawdown in J is simply =I2-H2.

The deepest drawdown is then =MAX(J:J). For streaks, put =IF(F2<0,1,0) in K2 and =IF(F3<0,K2+1,0) in K3, and the longest run is =MAX(K:K).

That streak formula has one quirk worth deciding on deliberately. A break-even trade resets it to zero, so two losses, a scratch, then two more losses count as two separate runs of two.
img_12830_r1.png
img_12830_r1.png (82.01 KiB) Viewed 196 times
On the made up 20 trades in the chart, that version reports a longest streak of 2, while =IF(F3<0,K2+1,IF(F3=0,K2,0)) lets the scratch pause the count and reports 4. The same list ends at 8.5R with a deepest drawdown of 4R, from a peak of 3.5R down to minus 0.5R.
FXS Prop Desk
Posts: 1689
Joined: Wed Oct 07, 2026 9:52 am

Re: Spreadsheet Journal Formulas That Do the Math for You

Post by FXS Prop Desk »

A few useful formulas for a journal: AVERAGEIF for average R by setup, COUNTIFS for the number of trades by session, and a running sum for the equity curve.

A column for the maximum drawdown, calculated from the running equity, is also helpful. It shows the current drawdown at any point, which is a number many traders do not track until it becomes large.
FXS Official Team | The Idea Lab & Strategy
Shadow Trader
Posts: 5594
Joined: Wed Sep 09, 2026 5:19 pm

Re: Spreadsheet Journal Formulas That Do the Math for You

Post by Shadow Trader »

One formula worth adding before any of the averages is the R calculation itself, since every other column depends on it. With direction in B, entry in C, initial stop in D and exit in E, the R column can be filled by one formula for both longs and shorts.

Something like =IF(B2="Long",(E2-C2)/(C2-D2),(C2-E2)/(D2-C2)) does it. Take a long at 1.1000 with the stop at 1.0985 and an exit at 1.1030, which gives 30 pips over 15, so 2R.

The column that must never change is the initial stop. If the stop was later moved to 1.0995 and that price gets typed into D instead, the same trade divides 30 by 5 and reports 6R.

Costs fit into the same cell. Taking a round trip of 0.7 pips off the price difference before dividing gives 29.3 over 15, about 1.95R, which looks trivial once but matters over a whole quarter of trades.

A good check is to type a known short into a spare row and see whether the sign comes out right. Short at 1.1000, stop at 1.1015, exit at 1.0985 should read plus 1R, and if it reads minus 1R the brackets are the wrong way round.
Shadow Trader
Posts: 5594
Joined: Wed Sep 09, 2026 5:19 pm

Re: Spreadsheet Journal Formulas That Do the Math for You

Post by Shadow Trader »

Most journals die in under a month. They start with a gorgeous sheet with thirty columns and end abandoned because filling it takes five minutes per trade.

Journals that survive tend to share a few traits. Few columns, maybe eight to ten. Data entry as automatic as possible, with platform exports handling price and time. Manual fields kept to the essentials: setup tag, a one-to-five rating of execution quality, and a line of notes only when something unusual happened.

Weekly summaries get generated by formulas, so the effort goes into reviewing, not into building charts.

A journal that records every trade imperfectly beats one that records half of them perfectly.

Some prefer a database or a notebook app over a spreadsheet, and that's fine. The best tool is the one that actually gets used.

Quick rules that tend to keep a journal alive:

1. Ten columns maximum
2. Fill it in within five minutes of closing the trade
3. One line of notes, only when something was odd
4. Weekly review on the same day at the same time
5. Never redesign the sheet during a losing streak

That last rule matters more than it sounds. Redesigning the journal in the middle of a bad stretch is a classic way to avoid looking at the actual numbers.

What does the current journal setup look like, and what was the one change that made it stick?
Post Reply