Skip to main content
skill CX Codex

backtesting-py-oracle — Claude Code skill: what it does

backtesting-py-oracle configures backtesting.py to match ClickHouse SQL sweep results on range bars. Fixes trade count mismatches and timing discrepancies in multi-position testing.

MIT 135 installs ★ 3 updated 2026-10-25

Install into Claude Code · Skills CLI (Vercel) All methods and platforms ↓
npx skills add terrylica/cc-skills --skill backtesting-py-oracle -a claude-code
Open on GitHub

What backtesting-py-oracle does

The skill establishes exact configurations for backtesting.py to validate ClickHouse SQL sweep outputs. Its focus is achieving bit-atomic alignment between database queries and Python execution during range bar backtests.

It outlines six documented anti-patterns, labeled BP-01 through BP-06. Key fixes include setting hedging=True with exclusive_orders=False, re-sorting stats._trades by EntryTime instead of exit timestamps, dropping NaNs before computing rolling quantiles, and applying fractional sizing (size=0.01) to prevent margin depletion.

Accuracy is verified through a 5-gate framework checking signal counts, timestamps, entry prices, exit types, and Kelly fraction alignment. Without hedging=True, the engine drops concurrent entries; on SOLUSDT this silently skipped 16 signals out of 121.

What it helps with

  • Align backtesting.py trade records with ClickHouse SQL sweep results
  • Prevent dropped trade signals during open market positions
  • Fix trade dataframe sorting by entry timestamp instead of exit time
  • Avoid margin exhaustion when running concurrent positions

Example prompts for the agent

  • Check my backtesting.py strategy setup against the BP-01 multi-position requirements
  • Validate our range bar backtest results using the 5-gate framework
  • Update the rolling quantile function to skip NaN values properly

How to install backtesting-py-oracle

1Skills CLI (Vercel)

npx skills add terrylica/cc-skills --skill backtesting-py-oracle -a claude-code

2Manually: put the files in place

~/.claude/skills/backtesting-py-oracle/SKILL.md

Copy the whole skill folder; for one project use .claude/skills/backtesting-py-oracle/

Step by step, with commands for each platform: How to install skills in Claude Code: five ways

Questions about backtesting-py-oracle

How to install backtesting-py-oracle in Claude Code?

Run npx skills add terrylica/cc-skills --skill backtesting-py-oracle -a claude-code in your terminal.

What does backtesting-py-oracle do?

It standardizes backtesting.py parameters and execution rules so Python simulations replicate ClickHouse SQL trade signals without skipped orders.

Why does backtesting.py produce fewer trades than SQL?

The library defaults to single-position behavior; concurrent SQL signals require passing hedging=True and exclusive_orders=False to allow overlapping entries.

Source text

SKILL.md · MIT
---
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.

© terrylica · MIT · terrylica/cc-skills

Similar skills

All skills for Finance and business →

From the same set79

The whole set →
glossary-management glossary-management is a Claude Code skill that maintains a terminology glossary and connects it to Vale. It runs for glossary edits, term additions, validation, and sync requests. CX Codex +1 138installs gmail-access The gmail-access skill connects the agent to a Gmail command-line client so it can pull messages, search a mailbox, save what it finds and stage drafts. Handy when the thread you need sits next to the code you are working on. CX Codex +1 137installs skill-architecture skill-architecture guides skill creation and updates, YAML structure checks, and common troubleshooting. Task templates help you choose a workflow before editing files. CX Codex +1 137installs slash-command-factory slash-command-factory generates Claude Code slash command Markdown files with YAML frontmatter through a question workflow or ten presets. CX Codex +1 136installs doppler-secret-validation The doppler-secret-validation skill helps store secrets in Doppler, verify retrieval, and test API authentication. Use it to check a token before relying on it in a project. CX Codex +1 135installs link-validation link-validation runs an offline lychee pass over a project's markdown and reports broken relative links, root-relative paths and anchors. Handy right before you commit docs. CX Codex +1 135installs session-recovery session-recovery troubleshoots missing Claude Code sessions when claude -r reports no conversations found or files save to incorrect paths. CX Codex +1 135installs session-chronicle session-chronicle connects findings, decisions, and code changes to session UUIDs. Use it when you need to trace where work came from or record session provenance. CX Codex +1 135installs

Project sponsors

Companies that keep this analytics open