Skip to content
Skills Library
Finance & accounting · Building & coding

backtesting-py-oracle

/backtesting-py-oracle

backtesting.py configuration for SQL oracle validation and range bar pattern backtesting. Use when running backtesting.py against.

Repository
cc-skills ↗
Licence
MIT
Last repository change
25.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
2
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 25.10.2026, without references to retired models or APIs.

Licence

MIT, open: use and modify while retaining author attribution.

What it does and when to use it

backtesting.py configuration for SQL oracle validation and range bar pattern backtesting. Use when running backtesting.py against.

Original author description

backtesting.py configuration for SQL oracle validation and range bar pattern backtesting. Use when running backtesting.py against.

How it works

  1. Critical Configuration (NEVER omit)
  2. Anti-Patterns (Ordered by Severity)
  3. 5-Gate Oracle Validation Framework
  4. Strategy Architecture: Single vs Multi-Position
  5. Data Loading
  6. HTML Equity Plot: Y-Axis Auto-Fit on Zoom (BP-07 to BP-10)
  7. Project Artifacts (rangebar-patterns repo)
  8. Post-Execution Reflection
Original SKILL.md headings · 319 lines, 1,677 words
SKILL.md preview
---
name: backtesting-py-oracle
description: backtesting.py configuration for SQL oracle validation and range bar pattern backtesting. Use when running backtesting.py against.
allowed-tools: Read, Bash, Grep, Glob
---

# backtesting.py Oracle Validation for Range Bar Patterns

Configuration and anti-patterns for using backtesting.py to validate ClickHouse SQL sweep results. Ensures bit-atomic replicability between SQL and Python trade evaluation.

**Companion skills**: `clickhouse-antipatterns` (SQL correctness, AP-16) | `sweep-methodology` (sweep design) | `rangebar-eval-metrics` (evaluation metrics)

**Validated**: Gen600 oracle verification (2026-02-12) — 3 assets, 5 gates, ALL PASS.

---

> **Self-Evolving Skill**: This skill improves through use. If instructions are wrong, parameters drifted, or a workaround was needed — fix this file immediately, don't defer. Only update for real, reproducible issues.

## Critical Configuration (NEVER omit)

```python
from backtesting import Backtest

bt = Backtest(
    df,
    Strategy,
    cash=100_000,
    commission=0,
    hedging=True,           # REQUIRED: Multiple concurrent positions
    exclusive_orders=False,  # REQUIRED: Don't auto-close on new signal
)
```

**Why**: SQL evaluates each signal independently (overlapping trades allowed). Without `hedging=True`, backtesting.py skips signals while a position is open, producing fewer trades than SQL. This was discovered when SOLUSDT produced 105 Python trades vs 121 SQL trades — 16 signals were silently skipped.

---

## Anti-Patterns (Ordered by Severity)

### BP-01: Missing Multi-Position Mode (CRITICAL)

**Symptom**: Python produces fewer trades than SQL. Gate 1 (signal count) fails.

**Root Cause**: Default `exclusive_orders=True` prevents opening new positions while one is active.

**Fix**: Always use `hedging=True, exclusive_orders=False`.

### BP-02: ExitTime Sort Order (CRITICAL)

**Symptom**: Entry prices appear mismatched (Gate 3 fails) even though both SQL and Python use the same price source.

**Root Cause**: `stats._trades` is sorted by ExitTime, not EntryTime. When overlapping trades exit in a different order than they entered, trade[i] no longer maps to signal[i].

**Fix**:

```python
trades = stats._trades.sort_values("EntryTime").reset_index(drop=True)
```

### BP-03: NaN Poisoning in Rolling Quantile (CRITICAL)

**Symptom**: Cross-asset tests fail with far fewer Python trades. Feature quantile becomes NaN and propagates forward indefinitely.

**Root Cause**: `np.percentile` with NaN inputs returns NaN. If even one NaN feature value enters the rolling window, all subsequent quantiles become NaN, making all subsequent filter comparisons fail.

**Fix**: Skip NaN values when building the signal window:

```python
def _rolling_quantile_on_signals(feature_arr, is_signal_arr, quantile_pct, window=1000):
    result = np.full(len(feature_arr), np.nan)
    signal_values = []
    for i in range(len(feature_arr)):
        if is_signal_arr[i]:
            if len(signal_values) > 0:
                window_data = signal_values[-window:]
                result[i] = np.percentile(window_data, quantile_pct * 100)
            # Only append non-NaN values (matches SQL quantileExactExclusive NULL handling)
            if not np.isnan(feature_arr[i]):
                signal_values.append(feature_arr[i])
    return result
```

### BP-04: Data Range Mismatch (MODERATE)

**Symptom**: Different signal counts between SQL and Python for assets with early data (BNB, XRP).

**Root Cause**: `load_range_bars()` defaults to `start='2020-01-01'` but SQL has no lower bound.

**Fix**: Always pass `start='2017-01-01'` to cover all available data.

### BP-05: Margin Exhaustion with Overlapping Positions (MODERATE)

**Symptom**: Orders canceled with insufficient margin. Fewer trades than expected.

**Root Cause**: With `hedging=True` and default full-equity sizing, overlapping positions exhaust available margin.

**Fix**: Use fixed fractional sizing:

```python
self.buy(size=0.01)  # 1% equity per trade
```

### BP-06: Signal Timestamp vs Entry Timestamp (LOW)

**Symptom**: Gate 2 (timestamp match) fails because SQL uses signal bar timestamps while Python uses entry bar timestamps.

**Root Cause**: SQL outputs the signal detection bar's `timestamp_ms`. Python's `EntryTime` is the fill bar (next bar after signal). These differ by 1 bar.

**Fix**: Record signal bar timestamps in the strategy's `next()` method:

```python
# Before calling self.buy()
self._signal_timestamps.append(int(self.data.index[-1].timestamp() * 1000))
```

---

## 5-Gate Oracle Validation Framework

| Gate | Metric          | Threshold | What it catches                      |
| ---- | --------------- | --------- | ------------------------------------ |
| 1    | Signal Count    | <5% diff  | Missing signals, filter misalignment |
| 2    | Timestamp Match | >95%      | Timing offset, warmup differences    |
| 3    | Entry Price     | >95%      | Price source mismatch, sort ordering |
| 4    | Exit Type       | >90%      | Barrier logic differences            |
| 5    | Kelly Fraction  | <0.02     | Aggregate outcome alignment          |

**Expected residual**: 1-2 exit type mismatches per asset at TIME barrier boundary (bar 50). SQL uses `fwd_closes[max_bars]`, backtesting.py closes at current bar price. Impact on Kelly < 0.006.

---

## Strategy Architecture: Single vs Multi-Position

| Mode            | Constructor                            | Use Case              | Position Sizing                |
| --------------- | -------------------------------------- | --------------------- | ------------------------------ |
| Single-position | `hedging=False` (default)              | Champion 1-bar hold   | Full equity                    |
| Multi-position  | `hedging=True, exclusive_orders=False` | SQL oracle validation | Fixed fractional (`size=0.01`) |

### Multi-Position Strategy Template

```python
class Gen600Strategy(Strategy):
    def next(self):
        current_bar = len(self.data) - 1

        # 1. Register newly filled trades and set barriers
        for trade in self.trades:
            tid = id(trade)
            if tid not in self._known_trades:
                self._known_trades.add(tid)
                self._trade_entry_bar[tid] = current_bar
                actual_entry = trade.entry_price
                if self.tp_mult > 0:
                    trade.tp = actual_entry * (1.0 + self.tp_mult * self.threshold_pct)
                if self.sl_mult > 0:
                    trade.sl = actual_entry * (1.0 - self.sl_mult * self.threshold_pct)

        # 2. Check time barrier for each open trade
        for trade in list(self.trades):
            tid = id(trade)
            entry_bar = self._trade_entry_bar.get(tid, current_bar)
            if self.max_bars > 0 and (current_bar - entry_bar) >= self.max_bars:
                trade.close()
                self._trade_entry_bar.pop(tid, None)

        # 3. Check for new signal (no position guard — overlapping allowed)
        if self._is_signal[current_bar]:
            self.buy(size=0.01)
```

---

## Data Loading

```python
from data_loader import load_range_bars

df = load_range_bars(
    symbol="SOLUSDT",
    threshold=1000,
    start="2017-01-01",      # Cover all available data
    end="2025-02-05",        # Match SQL cutoff
    extra_columns=["volume_per_trade", "lookback_price_range"],  # Gen600 features
)
```

---

## HTML Equity Plot: Y-Axis Auto-Fit on Zoom (BP-07 to BP-10)

backtesting.py generates Bokeh HTML plots via `bt.plot()`. By default, the y-axis is fixed — zooming on the x-axis does NOT rescale the y-axis to fit visible data. This makes it impossible to inspect zoomed-in regions of equity curves that span 4+ orders of magnitude.

**Reference implementation**: `scripts/gen800/plotting.py` in example-org/research-repo.

### BP-07: NEVER Use LogScale for Equity (CRITICAL)

**Symptom**: Equity panel y-axis doesn't auto-fit on zoom. JS callbacks fail silently.

**Root Cause**: Bokeh's `LogScale` breaks `CustomJS` y-range callbacks in multi-panel linked x_range layouts. The `js_on_change` callback fires but `y_range.start`/`y_range.end` assignments are ignored by the LogScale renderer.

**Proven via POC**: LogScale + JS callback works in single-panel mode but fails when 5 panels share a linked x_range.

**Fix**: Transform equity data to `log10()` in Python, display on **linear** scale with a `CustomJSTickFormatter` that shows `10^tick` as readable values:

```python
import numpy as np
from bokeh.models import CustomJSTickFormatter, Range1d

# Transform data
raw = np.asarray(src.data["equity"], dtype=float)
raw = np.where(raw > 0, raw, 1e-10)
src.data["equity"] = np.log10(raw).tolist()

# Custom tick formatter (shows 1%, 100%, 10K%, 1M%)
child.yaxis[0].formatter = CustomJSTickFormatter(code="""
    const v = Math.pow(10, tick);
    if (v >= 1e6) return (v/1e6).toFixed(1) + 'M%';
    if (v >= 1e3) return (v/1e3).toFixed(0) + 'K%';
    if (v >= 1) return v.toFixed(0) + '%';
    return v.toFixed(2) + '%';
""")

# MUST set initial y_range explicitly (backtesting.py leaves it as NaN)
valid = eq[np.isfinite(eq)]
pad = (valid.max() - valid.min()) * 0.05
child.y_range = Range1d(start=valid.min() - pad, end=valid.max() + pad)
```

### BP-08: Panel-Aware Column Matching (CRITICAL)

**Symptom**: Drawdown/P&L panels go blank when zooming. Shows millions of percent.

**Root Cause**: Multiple panels share the same `ColumnDataSource` (backtesting.py optimization). The Equity panel's Line renderer and the Drawdown panel's Line renderer both reference a source containing `equity`, `drawdown`, `High`, `Low`, etc. A naive "find first y column" approach picks `equity` for the Drawdown panel.

**Fix**: Match by `panel_label` FIRST, then by column name:

```python
panel_label = child.yaxis[0].axis_label or ""

# Label-first matching (panels share data sources!)
if "Drawdown" in panel_label and "drawdown" in d:
    hi_col = lo_col = "drawdown"
elif "Equity" in panel_label and "equity" in d:
    hi_col = lo_col = "equity"
elif "High" in d and "Low" in d:  # OHLC
    hi_col, lo_col = "High", "Low"
```

### BP-09: JS Callback Pattern for Y-Axis Auto-Fit (PROVEN)

Attach a `CustomJS` callback to `x_range.js_on_change` for each panel. The callback scans visible data and sets `y_range` directly:

```python
from bokeh.models import CustomJS

cb = CustomJS(
    args={"source": src, "yr": child.y_range,
          "x_col": "index", "y_col": "equity"},
    code="""
    const xs = source.data[x_col];
    const ys = source.data[y_col];
    const x0 = cb_obj.start, x1 = cb_obj.end;
    let lo = Infinity, hi = -Infinity;
    for (let i = 0; i < xs.length; i++) {
        if (xs[i] >= x0 && xs[i] <= x1 && isFinite(ys[i])) {
            if (ys[i] < lo) lo = ys[i];
            if (ys[i] > hi) hi = ys[i];
        }
    }
    if (isFinite(lo) && isFinite(hi) && lo < hi) {
        const pad = (hi - lo) * 0.05 || 0.001;
        yr.start = lo - pad;
        yr.end = hi + pad;
    }
    """,
)
child.x_range.js_on_change("start", cb)
child.x_range.js_on_change("end", cb)
```

### BP-10: DataRange1d(only_visible=True) Is Unreliable

**Symptom**: `DataRange1d(only_visible=True)` works for simple cases but fails with:

- LogScale panels (y-range computed in wrong space)
- VBar renderers (candlesticks — bounds not tracked correctly)
- Shared data sources across panels

**Fix**: Always use the JS callback pattern (BP-09) instead of `DataRange1d(only_visible=True)`.

### Panel Data Source Reference (backtesting.py internals)

| Panel    | Label             | Renderer                             | Y Column         | Source                |
| -------- | ----------------- | ------------------------------------ | ---------------- | --------------------- |
| Equity   | `"Equity"`        | Patch (`equity_dd`), Line (`equity`) | `equity`         | Shared OHLC source    |
| Drawdown | `"Drawdown"`      | Line (`drawdown`), Scatter (peak)    | `drawdown`       | Shared OHLC source    |
| P/L      | `"Profit / Loss"` | Scatter (`returns`), MultiLine       | `y` or `returns` | Separate trade source |
| OHLC     | `""` (no label)   | Segment, VBar                        | `High`/`Low`     | Shared OHLC source    |
| Volume   | `"Volume"`        | VBar                                 | `Volume` (top)   | Shared OHLC source    |

---

## Project Artifacts (rangebar-patterns repo)

| Artifact                    | Path                                              |
| --------------------------- | ------------------------------------------------- |
| Oracle comparison script    | `scripts/gen600_oracle_compare.py`                |
| Gen600 strategy (reference) | `backtest/backtesting_py/gen600_strategy.py`      |
| SQL oracle query template   | `sql/gen600_oracle_trades.sql`                    |
| Oracle validation findings  | `findings/2026-02-12-gen600-oracle-validation.md` |
| Backtest CLAUDE.md          | `backtest/CLAUDE.md`                              |
| ClickHouse AP-16            | `.claude/skills/clickhouse-antipatterns/SKILL.md` |
| Fork source                 | `~/fork-tools/backtesting.py/`                    |

## Post-Execution Reflection

After this skill completes, check before closing:

1. **Did the command succeed?** — If not, fix the instruction or error table that caused the failure.
2. **Did parameters or output change?** — If the underlying tool's interface drifted, update Usage examples and Parameters table to match.
3. **Was a workaround needed?** — If you had to improvise (different flags, extra steps), update this SKILL.md so the next invocation doesn't need the same workaround.

Only update if the issue is real and reproducible — not speculative.
Files in this package (2)
  • SKILL.md 13 KB
  • references/evolution-log.md 1 KB

Versions

The database has no other skills named backtesting-py-oracle.

Database snapshot: 13.11.2026 · package version 8f39ab7593f9 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 1 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 90backtesting-frameworks
    Apply backtesting frameworks in reproducible local data workflows with version-aware APIs, explicit assumptions, and validation. Use when the user chooses backtesting frameworks or its strengths fit the task.
  • 100backtesting-frameworks
    Build robust backtesting systems for trading strategies with proper handling of look-ahead bias, survivorship bias, and transaction costs. Use when developing trading algorithms, validating strategies, or building backtesting infrastructure.
  • 100backtesting-frameworks
    Build robust backtesting systems for trading strategies with proper handling of look-ahead bias, survivorship bias, and transaction costs. Use when developing trading algorithms, validating strateg...
  • 100backtesting-configuration
    Configures TimeSeriesFold parameters for backtesting based on deployment scenarios. Maps business requirements (retraining frequency, forecast horizon, data budget) to cross-validation strategy parameters. Use when the user describes how they plan to deploy or evaluate a model.
  • 90risk-model-backtesting-against-realized-outcomes
    Use when you hold the raw dated series of realised P&L against forecast VaR and need an auditable validation verdict: exception counting plus ordering-sensitive tests. For an already-counted pair see real-time-var-backtesting-kupiec-test.
  • 100backtesting-configuration
    Configures TimeSeriesFold parameters for backtesting based on deployment scenarios. Maps business requirements (retraining frequency, forecast horizon, data budget) to cross-validation strategy parameters. Use when the user describes how they plan to deploy or evaluate a model.

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.