Excel as a Trading Laboratory

Introduction

Before you write your first class in Python, your best friend is Excel or Google Sheets. Why? Because in Excel you see every trade, every R-multiple, every formula. You are not hiding behind a library. A systematic trading system requires an audit, and an audit is easiest to do in a spreadsheet.

In this lesson you will turn a TradingView export into a real laboratory: you will calculate expectancy in R, Profit Factor, SQN, Max Drawdown, stability tests, Monte Carlo in Excel. This is the bridge between Pine Script and Python. If you cannot calculate a system in Excel, you will not understand what Python computes underneath.

Laboratory structure - one sheet is one source of truth

Create a workbook lab_system_v01.xlsx with 4 tabs:

Formulas always open, never hard-coded numbers.

Base formulas in Trades_R:

R-multiple = Net_PnL / Initial_risk
Where Initial_risk = Entry - SL in $ * position size. If you have no SL from TV, use ATR*mult as a proxy.

Net_PnL = Gross_PnL - Commission - Estimated_slippage

Win = IF(R>0,1,0)

Metrics you have to calculate - formulas ready to copy

Assume column D is the R-multiple, 2:1000 is the trade range, $ is net PnL.

Number of trades:
N = COUNTA(D2:D1000)

WinRate:
=COUNTIF(D2:D1000,">0")/N

AvgWin_R:
=AVERAGEIF(D2:D1000,">0")

AvgLoss_R:
=ABS(AVERAGEIF(D2:D1000,"<0"))

Expectancy EV in R:
= (WinRate*AvgWin_R) - ((1-WinRate)*AvgLoss_R)

Profit Factor:
=SUMIF(D2:D1000,">0")/ABS(SUMIF(D2:D1000,"<0"))

Payoff Ratio:
=AvgWin_R / AvgLoss_R

SQN (Van Tharp):
= SQRT(N) * (AVERAGE(D2:D1000)/STDEV(D2:D1000))

Z-Score for the series (do wins cluster into streaks - detects dependency):
You need a W streak column. The simplified formula in Excel requires counting runs. Better in Python, but in Excel you can use:
= (2*WinCount*LossCount / N) -> expected runs, and compare them with the actual ones

Max Drawdown and Time to Recovery - Excel does not lie

Most platforms calculate MaxDD incorrectly (intraday vs close). Calculate it yourself:

Step 1: cumulative Equity Curve
E2 = D2 (the first R)
E3 = E2 + D3 and so on = SUM(D$2:D3)

Step 2: Running Peak
P2 = MAX(E$2:E2)

Step 3: Drawdown
DD2 = E2 - P2 (negative or 0)

Step 4: MaxDD = MIN(DD:D)
Step 5: Count the bars in DD > 5R - that is the pain time

MaxDD formula in Excel:
Equity in column E, Peak in F, DD in G
F2 = MAX(E$2:E2)
G2 = E2 - F2
MaxDD = MIN(G:G)
MaxDD_duration = COUNTIF(G:G,"<-5") - an assessment of whether you can take it psychologically

If your system has a MaxDD of 25R and you risk 1% per trade = a 25% account drawdown. Are you psychologically ready for that? That is a question from Excel, not from theory.

Monte Carlo in Excel - 30 minutes that saves an account

The simplest Monte Carlo: you shuffle the order of trades 500 times and look at the distribution of final equity and MaxDD. It teaches you that the same EV can produce 10 different equity curves.

Excel Monte Carlo (no VBA):

1. In the MonteCarlo tab you have a column with a random index: =RANDBETWEEN(1,N) for 500 rows = one simulation
2. Pull the R for that index: =INDEX(Trades_R!D$2:D$1000, random_index)
3. Sum the equity, calculate MaxDD for that simulation as above

Repeat 500 times (rows are simulations, columns are cumulative equity after N trades)

Or use a Data Table: 
=TABLE(, ) with randomness - in Google Sheets use 500 rows and RANDBETWEEN

Better still: use simple VBA or do it in Python in lesson 7, but you will understand the logic in Excel.

Optimisation in Excel - how not to cheat yourself

Use Solver for only 1-2 parameters and only with OOS. Example: you optimise the ATR mult for the SL (1.0-3.0).

Step: 
IS = the first 70% of trades chronologically
OOS = the last 30%
Optimise EV in R on IS by changing the ATR mult
Check EV on OOS - if it drops >40% -> overfit

Never optimise more than 2 parameters at once in Excel. That leads to surface fitting.

When the plan says do not trade

1. When you do not have a Trades_R sheet: You trade off an equity curve from the broker that mixes PLN, USD and different position sizes. That is not a measurement.
2. When you have not calculated MaxDD in R and in time (days/trades in drawdown): You do not know what you are signing up for psychologically.
3. When Monte Carlo shows a 20% chance of -20R and you risk 2% per trade: You have 40% of the account at risk in the worst-case scenario. Stop.
4. When PF drops below 1.2 after adding 50% in costs: The system is too fragile for real conditions.
5. When N < 50 in the sample: Excel will show you SQN and PF but that is noise. Do not make trading decisions on N<100.

The mistakes that cost the most

1. Counting in $ instead of in R. The account grows from 10k to 12k but you risked 500 once and 50 another time. $ is not comparable. R is.
2. Averaging down on losses - “Excel can take it”. Excel lets you move the SL in a backtest. The market does not.
3. Ignoring time in the market. A system that holds a position for 30 days on average versus 30 minutes has a different swap cost and a different time MaxDD. Calculate Avg Bars Held.
4. No regime tagging. A Regime column (trend / range / high vol) lets you see where the system earns and where it loses. Without it you optimise the average of two different markets.
5. Saving only the winners. You export only the closed profitable trades from the broker. You skip the losses because “that was a mistake”. That is survivorship in your own journal.

Measurable exercise

Goal: Build a laboratory for any system and run an audit.

Task, 2h:

1. Take the CSV export from lesson 2 (min 100 trades) or generate a random file from a demo broker.
2. Build the 4 tabs as above. Calculate in Metrics:

N, WinRate, EV, PF, SQN, MaxDD_R, MaxDD_duration, AvgTrade_R
Cost x1 and x2 - two rows of metrics

3. Add a Regime column: if ATR 14 > SMA ATR 100 -> HighVol else LowVol. Calculate EV separately for HighVol and LowVol.

=IF(ATR> SMA_ATR, "HighVol","LowVol")
EV_HV = AVERAGEIF(Regime,"HighVol",R_range)

4. Answer: Do EV LowVol and HighVol differ by >50%? If so, consider a regime filter.

5. Do a mini Monte Carlo of 100 reshuffle simulations (RANDBETWEEN) and record the 95th percentile MaxDD.

If the 95% MaxDD > 2 * your tolerance - cut your risk by 50%.

Test yourself

1. Why do you have to calculate in R-multiple instead of in PLN/USD?
2. Calculate MaxDD: trades R = [+1, +2, -1, -3, -2, +4]. What is the equity curve and what is the MaxDD?
3. What is Profit Factor and why is a PF of 1.1 after costs too little for real trading?
4. What 4 tabs should your laboratory sheet have and what does each one contain?
5. You have PF 1.5 on IS and 0.9 on OOS. What does that say about your system?

Common questions

Excel vs Google Sheets - which one to choose? Excel has Solver, Power Query, VBA and is faster for 10k rows. Sheets has better sharing and GOOGLEFINANCE. For a laboratory of 100-5000 trades both are enough. Choose the one you will actually open.

Do I need to know VBA? Not to start with. RANDBETWEEN + INDEX are enough for 100-500 Monte Carlo simulations. VBA speeds it up to 10k simulations, but that is already Python territory in lessons 5 and 7.

Where do I get MFE/MAE in Excel? You cannot export them directly from TradingView. In Python you calculate them easily: max profit during the life of a trade vs close. In Excel approximate them: High-Low of the bar for MFE if you have 1min data.

How many systems can I keep in one file? One file = one system = one logic. Do not mix 3 systems in one sheet because you will mix up PF and MaxDD. Create versions: v01, v02_filtered and so on.

Can I use Excel for a live forward test? Yes, via DDE or a broker API and Power Query. But that is a crippled bot. Use Excel for auditing, not for execution. Execution is Python / the broker API from lesson 6.

---