A useful spreadsheet separates source records from calculations and summaries. This article builds a hypothetical position-size calculation, a reproducible five-trade journal and dashboard checks. Formula examples use English Excel names, commas between arguments and a decimal point; settings and available functions can differ. A spreadsheet’s transparency does not validate its inputs or establish superiority to every paid tool.

Keep the source import separate

Keep a raw Import sheet, a Calculator, a Journal and a Dashboard. Preserve original fills, costs and identifiers; group partial operations under a stated idea definition. Check timestamps, account currency, decimal separators and fee signs after import. A repeated source load must not become a second trading result, and a value already net of costs must not have the same costs deducted again. A good-looking chart cannot resolve an inconsistent ledger.

A spreadsheet can calculate many trading metrics, but SUMIF, COUNTIF and MIN alone do not cover every measure. Monthly filtering requires appropriate date criteria; drawdown requires a running peak as well as equity. Variability, ratios and uncertainty need additional formulas. Start with transparent calculations and verify them against the underlying trades.

Position sizing with a stated lot step

Choose the planned price-loss budget, stop distance and pip value in account currency per lot, plus the permitted lot step. Values must match the product and current conversion convention; an exit exchange rate not yet known cannot be an input to an entry-time calculator. With positive valid inputs, raw lots = budget / (stop pips × account-currency pip value per lot). Round down to the step and separately check minimum/maximum volume and margin. Fees or worse fills can make actual loss larger, and no fixed risk percentage is mandatory.

In a hypothetical USD account with equity 10,000, chosen risk 1%, a 25-pip EUR/USD stop and EUR 100,000 per lot, a 0.0001 pip is USD 10 per lot. The pre-cost budget is USD 100 and raw size 100 / (25 × 10) = 0.40 lot. In the separate PL version’s example, equity PLN 20,000 and an assumed USD/PLN conversion of 4.00 give PLN 40 per pip per lot: PLN 200 / (25 × 40) = 0.20 lot. These are different explicit account examples, not a current exchange-rate claim. With B2 equity, B3 risk stored as 1%, B4 stop pips, B5 account-currency pip value per lot and B6 lot step 0.01, the positive-input formula is =FLOOR(B2*B3/(B4*B5),B6). Validate inputs before evaluating it. The related one-percent sizing example uses a chosen convention.

A trade journal built for honest review

Define fields for the questions you need: timestamps, instrument, direction, setup, planned exits, actual fills, contract quantity, net result and initial-risk reference. Some are categories or dates, not quantitative measurements. Net R equals net result divided by positive valid initial planned risk; do not rewrite that denominator after moving a stop. Preserve account and idea IDs in the source record. Correlation, open exposure and intratrade equity drawdown require additional time series; a closed-trade row does not supply them automatically.

Add MAE and MFE as quantitative fields, expressed consistently in pips, money or R, plus a qualitative post-trade observation. Record how excursions are measured and whether prices include the spread. These fields describe the path while the trade was open; they do not by themselves prove an optimal stop or target.

A number stored as text can disappear from a sum

After importing data, count numerical values and sample their types. A cell displaying 200 is not enough to ensure correct range summation.

Scroll sideways to see the full diagram

A number stored as text can disappear from a sumOur range B3:B5 contains numeric 100 in B3, text 200 in B4 and numeric −50 in B5. A range sum ignores the text, returning 50, and the numeric-cell count is 2. After deliberately converting B4 to numeric 200, the same range sums to 250 and the count becomes 3. The diagram separates the displayed value from its type. Changing appearance or formatting is not enough; check the value type. All amounts are fictional PLN, without formula errors or empty cells.CHECK THE TYPE, NOT JUST APPEARANCEOur range B3:B5 • amounts in PLNCellDisplayValue typeB3100numberB4200textB5−50numberSUM(B3:B5) = 50 PLNCOUNT(B3:B5) = 2After converting B4 to a number:SUM(B3:B5) = 250 PLNCOUNT(B3:B5) = 3

Assumptions: Only the three specified fictional PLN values, with one intentionally stored as text. No blanks, dates or formula errors. The range remains B3: B5; only B4's type changes. This does not assess an actual journal.

Our range has three non-empty cells. B3 contains numeric 100, B4 intentionally contains text 200, and B5 contains numeric −50. The range formula =SUM(B3:B5) returns 50 PLN, while =COUNT(B3:B5) returns 2.

After checking the source, deliberately convert B4 to numeric 200. The same range then gives 100 + 200 − 50 = 250 PLN and contains three numbers. Changing formatting or text alignment is not a type check. Keep a raw-import copy and inspect the conversion, especially minus signs and decimal separators.

Microsoft — SUM ignores text in a range and Microsoft — COUNT counts numerical values in references explain the functions used here. This concerns a reference to a cell range; rules for text entered directly as function arguments can differ.

The displayed 200 is excluded from the sum while it remains text.

The monthly dashboard — SUMIF, COUNTIF and MIN

For a monthly result, filter dates from the month’s first day inclusive to the following month’s first day exclusive. In Excel, SUMIFS can sum the result column using both date conditions. Comparing a raw date to a month number will not select the month correctly, and grouping by month number alone can combine different years.

Count positive net outcomes over all valid closed trades in the same selected period, including zeros in the denominator. Missing initial risk need not erase a valid monetary outcome, but it makes that row’s R unavailable; report both sample counts. Profit factor uses summed positive net outcomes divided by the absolute negative sum and is unavailable without losses. Mean R averages valid individual R values; see the expectancy formula. Format a win-rate ratio as a percentage rather than multiplying again by 100.

Use separate running-balance, running-peak and drawdown columns. Include the initial capital before the first outcome in the peak. Monetary drawdown is balance − peak; a signed percentage is balance / peak − 1 with a positive peak. MIN over the corresponding drawdown column finds the deepest recorded decline in that measure. Closed-trade balance is not intratrade equity: floating losses between closes can give a deeper equity drawdown. Deposits or withdrawals require an adjusted method.

For a complete hypothetical check, use A dates, B net PLN results, C initial PLN risk, D R, E balance, F peak and G signed percentage drawdown. Set E2 = 10,000, F2 = E2 and G2 = 0; row 2 is the initial state, not a trade. Enter rows 3–7 with dates 2026-09-01, 02, 03, 04 and 07, outcomes −100, 200, −50, 0 and 150, and risk 100 on every row. Dates are spreadsheet dates and all amounts numeric. There are no open positions or cash flows.

D3: =IF(C3>0,B3/C3,"")
E3: =E2+B3
F3: =MAX($E$2:E3)
G3: =E3/F3-1

Copy those formulas through row 7 and format G as a percentage. Final balance is PLN 10,200; the deepest recorded drawdown is −1% after the first loss. Excluding the initial balance would miss that decline.

Net: =SUM(B3:B7)
Win rate: =COUNTIF(B3:B7,">0")/COUNT(B3:B7)
Mean R: =AVERAGE(D3:D7)
Drawdown: =MIN(G2:G7)
Profit factor: =SUMIF(B3:B7,">0",B3:B7)/(-SUMIF(B3:B7,"<0",B3:B7))

The valid fixed sample gives PLN 200, 40% wins, +0.40R mean and profit factor 350 / 150 ≈ 2.33. Add empty-sample and zero-loss checks before general use; do not replace unavailable ratios with zero. For September, enter L2 as 2026-09-01 and L3 as 2026-10-01 and use =SUMIFS(B3:B7,A3:A7,">="&L2,A3:A7,"<"&L3). This includes final-day times and excludes the next month; apply the same period to the other metrics.

A hypothetical month on the dashboard

Suppose 28 trades use fixed initial risk of $100 each and their net results sum to $340. The monthly return relative to $10,000 starting capital is 3.4 percent, and mean R is $340 ÷ (28 × $100) = 0.12142857R. If 13 are winners, win rate is 13 ÷ 28 = 46.4286 percent. Profit factor and maximum drawdown cannot be derived from these totals alone; calculate them from gross profits, gross losses and the ordered equity path. If risk varies by trade, calculate each R-multiple before averaging.

Record results and planned risk consistently and check join cardinality before summing. A hypothetical idea has price result PLN 100 and three separate valid fee records −2, −3 and −1. Expanding the one-to-many join repeats 100 three times, giving row totals 98, 97, 99 and sum 294. The correct idea net is 100 − 2 − 3 − 1 = 94. Distinct fee IDs do not fix repeated parent amounts. Aggregate fees by idea to −6 before joining once, then compare the row count and net total. This is a calculation check, not an observed trading result.

What to do tomorrow

  1. Separate raw Import, Calculator, Journal and Dashboard. A borrowed template can be useful if its field definitions and formulas are actually checked; building every header by hand is not the only way to understand it.
  2. Use a hypothetical known case first, including invalid inputs and lot-step/minimum limits. Agreement on one case does not test every currency, product or execution cost.
  3. Reconcile source operations, grouping, net costs and initial risk before calculating metrics. Mark unavailable context explicitly and show valid counts for each measure instead of inventing missing data.
  4. Build the five dashboard formulas on the third tab — conditional sum for the monthly result, conditional count for the win rate, two conditional sums for profit factor, the average of the R column and the MIN function for drawdown — and check that every number is consistent with the raw data in the journal before you start using them to make decisions.
  5. Review the journal at a cadence you can maintain. Preserve formula and data changes separately from strategy changes; a corrected measurement is not evidence that the strategy improved.