Save backtest results to SQLite database for comparison. Trigger when: (1) tracking backtest history, (2) comparing model performance, (3) querying best backtests.
| Item | Details |
|---|---|
| Date | 2024-12-29 |
| Goal | Persist backtest results to SQLite for historical comparison |
| Environment | backtest/engine.py, db_manager.py, run_backtest.py |
| Status | Success |
Backtest results were only returned in-memory and never persisted. This made it impossible to:
CREATE TABLE IF NOT EXISTS backtest_results (
id INTEGER PRIMARY KEY AUTOINCREMENT,
symbol TEXT NOT NULL,
timeframe TEXT NOT NULL,
model_path TEXT,
run_timestamp INTEGER NOT NULL,
start_date INTEGER NOT NULL,
end_date INTEGER NOT NULL,
initial_capital REAL NOT NULL,
final_equity REAL NOT NULL,
total_return REAL NOT NULL,
sharpe_ratio REAL,
max_drawdown REAL,
win_rate REAL,
profit_factor REAL,
total_trades INTEGER,
avg_trade_pnl REAL,
config_json TEXT,
notes TEXT
);
CREATE INDEX IF NOT EXISTS idx_backtest_symbol ON backtest_results(symbol, timeframe);
CREATE INDEX IF NOT EXISTS idx_backtest_timestamp ON backtest_results(run_timestamp DESC);
def save_backtest_result(
self,
symbol: str,
timeframe: str,
initial_capital: float,
final_equity: float,
total_return: float,
max_drawdown: float,
total_trades: int,
win_rate: Optional[float] = None,
sharpe_ratio: Optional[float] = None,
profit_factor: Optional[float] = None,
avg_trade_pnl: Optional[float] = None,
model_path: Optional[str] = None,
start_date: Optional[datetime] = None,
end_date: Optional[datetime] = None,
config: Optional[dict] = None,
notes: Optional[str] = None
) -> int:
"""Save backtest results to database. Returns backtest ID."""
now = int(datetime.now(timezone.utc).timestamp())
start_ts = int(start_date.timestamp()) if start_date else now
end_ts = int(end_date.timestamp()) if end_date else now
config_json = json.dumps(config) if config else None
with self._get_connection() as conn:
cur = conn.execute("""
INSERT INTO backtest_results (
symbol, timeframe, model_path, run_timestamp,
start_date, end_date, initial_capital, final_equity,
total_return, sharpe_ratio, max_drawdown, win_rate,
profit_factor, total_trades, avg_trade_pnl, config_json, notes
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""", (...))
return cur.lastrowid
def save_to_db(
self,
db: Optional[Any] = None,
model_path: Optional[str] = None,
timeframe: str = "1Hour",
notes: Optional[str] = None,
) -> int:
"""Save backtest results to SQLite database."""
from ..data.db_manager import TradingDatabase
if db is None:
db = TradingDatabase()
# Extract dates from equity curve
start_date = self.equity_curve.index[0].to_pydatetime()
end_date = self.equity_curve.index[-1].to_pydatetime()
backtest_id = db.save_backtest_result(
symbol=self.symbol,
timeframe=timeframe,
initial_capital=self.config.initial_capital,
final_equity=self.equity_curve.iloc[-1],
total_return=self.metrics.total_return_pct,
max_drawdown=self.metrics.max_drawdown_pct,
total_trades=self.metrics.total_trades,
win_rate=self.metrics.win_rate,
sharpe_ratio=self.metrics.sharpe_ratio,
profit_factor=self.metrics.profit_factor,
avg_trade_pnl=self.metrics.avg_trade_pnl,
model_path=model_path,
start_date=start_date,
end_date=end_date,
config=self.config.to_dict(),
notes=notes,
)
return backtest_id
parser.add_argument(
'--save-to-db',
action='store_true',
help='Save backtest results to SQLite database'
)
# In run functions:
if save_to_db:
backtest_id = result.save_to_db(
model_path=model_path,
timeframe=timeframe,
)
logger.info(f"Saved backtest to database with id={backtest_id}")
def get_backtest_results(
self,
symbol: Optional[str] = None,
timeframe: Optional[str] = None,
limit: int = 100
) -> List[Dict]:
"""Get recent backtest results with optional filters."""
...
def get_best_backtest(
self, symbol: str, timeframe: str, metric: str = 'total_return'
) -> Optional[Dict]:
"""Get the best performing backtest for a symbol/timeframe."""
valid_metrics = ['total_return', 'sharpe_ratio', 'profit_factor', 'win_rate']
...
| Attempt | Why it Failed | Lesson Learned |
|---|---|---|
| Storing dates as strings | Query performance issues | Use Unix timestamps (INTEGER) |
| Storing full equity curve | Database bloat | Only store summary metrics |
| Lazy import at module level | Circular import errors | Use lazy import inside method |
| datetime.to_pydatetime() on Timestamp | Some indices aren't Timestamps | Check with hasattr() first |
# Table schema