Know your room left before every trade: a daily loss limit tracker in a spreadsheet

AI-Assisted

By Richard C, AIL Studio

Most prop traders know their daily loss limit. Fewer know, at 1:30 PM after three trades, exactly how much of it is left, and whether the stop on the next trade still fits inside it. That's the moment the limit gets broken: not on one huge loss, but on a normal-sized trade taken with too little room left.

This post builds a small log that answers two questions after every trade: how much room is left today, and can I take the next trade at its planned stop?

Example values only. The 2,000 limit below is made up. Firms measure daily loss differently (from start-of-day balance or equity, with or without open P&L, with different reset times). Check your own firm's written rules and change the setup to match.

1. Set up the log

One row per closed trade. Columns:

• A Date, B Time (a real time value, not text)
• C Trade, D Realized P&L
• E Day P&L so far, F Room left, H Status (formulas)
• G Next stop risk: what you'd lose if the next trade hits its stop

Put the settings to the side: K1 = daily loss limit (example: 2000), K2 = caution level as a share of the limit (example: 25%).

2. Running day P&L

In E2, then fill down:

=SUMIFS($D$2:$D$100,$A$2:$A$100,A2,$B$2:$B$100,"<="&B2)

It adds every trade on the same date up to and including this one. It resets each new date, and the order of rows doesn't matter because it filters by time.

Keep B as real times. If you type times as text, the "<="&B2 comparison stops working and every row shows 0.

3. Room left

In F2:

=$K$1+E2

This assumes a fixed limit measured from start-of-day balance, so a winning trade adds room (2,350 after the first trade above). If your firm doesn't work that way, this is the cell to change.

4. Status: can I take the next trade?

In H2:

=IF(F2<=0,"STOP: limit hit",IF(G2>F2,"Skip: stop too big",IF(F2<$K$2*$K$1,"Caution","OK")))

In order:

1. Room at or below zero: the limit is gone.
2. The next stop risk is bigger than the room left: a single stop-out would break the limit, so skip it or size down.
3. Room under 25% of the limit: caution.
4. Otherwise, OK.

In the example, day one ends with 400 of room left and a planned 500 stop. The sheet says skip, and that's the trade that would have broken the limit. On day two nobody checked, and the last trade takes room to -50.

Add conditional formatting so "Skip" and "STOP" stand out at a glance.

5. One row per day

On a second sheet, list each date once:

=SORT(UNIQUE(FILTER(Log!A2:A100,Log!A2:A100<>"")))

Then, for each date in A2:

• Day P&L: =SUMIFS(Log!$D$2:$D$100,Log!$A$2:$A$100,A2)
• Worst point: =MINIFS(Log!$E$2:$E$100,Log!$A$2:$A$100,A2)
• Lowest room: =Log!$K$1+C2
• Limit hit?: =IF(D2<=0,"Yes","No")

"Worst point" matters more than the day's close. A day can dip below the limit and then recover, and some firms count the dip; check your firm's rules. This sheet only sees closed trades, though. If your firm counts open P&L, a dip while a trade was open won't show up here.

6. Use it before you click

The routine is short:

1. Log the trade the moment it closes.
2. Before the next entry, type its stop risk in G.
3. If H doesn't say OK, don't place the trade as planned.

SORT, UNIQUE, FILTER and MINIFS need Excel 365 or 2021, or Google Sheets. Not tested there, so check them once in your own copy.

If you'd rather start from a ready-made log that tracks your trades and stats, the Trader's Edge Journal is ours.