RunDir Queries
The rundirs pseudo-database provides access to detailed backtest data stored in each completed MesoSim run's result directory. This includes event logs, position data, leg information, and performance metrics.
The processing of the files is done using DuckDB, a high-performance SQL engine optimized for analytical queries.
Directory Structure
Each MesoSim run creates a directory at /backtest-results/BACKTEST_ID containing several files:
/backtest-results/BACKTEST_ID/
├── BACKTEST_ID.backtest-analytics.html.gz # Strategy tearsheet
├── BACKTEST_ID.backtest-analytics.json # Strategy performance data
├── BACKTEST_ID.common-metrics.csv.gz # StrategyNAV and account level greeks
├── BACKTEST_ID.events.json.gz # MesoSim's event log, including all variables
├── BACKTEST_ID.external_data.zip # External data used in the run (if any)
├── BACKTEST_ID.job.json # The MesoSim Strategy Definition (v3)
├── BACKTEST_ID.legs.parquet # Leg greeks and IV data for Risk Graph display
└── BACKTEST_ID.positions.parquet # Position greeks and variables for Risk Graph display
File Schemas
positions.parquet
This file contains position-level data including Greeks and P&L information:
type encodings count avg size nulls min / max
At INT64 B BB_ 3603 3.00 B 0 "2022-06-16T15:45:00.000+0000" / "2025-03-21T16:00:00.000+0000"
PositionId INT32 B BB 3603 0.06 B 0 "0" / "92"
RealizedPnL INT64 B BB_ 3603 0.14 B 0 "0.00" / "2300.00"
PosPnL INT64 B BB_ 3603 2.23 B 0 "-13918.00" / "19890.50"
PosDelta INT64 B BB_ 3603 2.18 B 0 "-391.70" / "284.35"
PosGamma INT64 B BB_ 3603 1.21 B 0 "-201.30" / "2.95"
PosTheta INT64 B BB_ 3603 2.52 B 0 "-2175.25" / "7614.05"
PosVega INT64 B BB_ 3603 2.44 B 0 "0.00" / "5833.85"
PosWVega INT64 B BB_ 3603 2.54 B 0 "-717.65" / "7241.40"
UnderlyingPrice INT64 B BB_ 3603 2.19 B 0 "3507.09" / "6116.48"
VIXPrice INT64 B RB_ 3603 1.39 B 0 "11.82" / "53.19"
legs.parquet
This file contains leg-level data including contract details and implied volatility:
type encodings count avg size nulls min / max
At INT64 B BB_ 13812 0.88 B 0 "2022-06-16T15:45:00.000+0000" / "2025-03-21T16:00:00.000+0000"
PositionId INT32 B BB 13812 0.02 B 0 "0" / "92"
Contract BINARY B BB_ 13812 0.17 B 0 "SPX.SPXW.2022-06-22.3880...." / "SPX.SPXW.2025-04-10.5780...."
EntryPrice INT64 B BB_ 13812 0.11 B 0 "1.6000" / "77.0500"
Qty INT64 B BB_ 13812 0.04 B 0 "-15.0000" / "15.0000"
Price INT64 B BB_ 13812 2.09 B 0 "0.0000" / "236.2000"
Iv INT32 B BB_ 13812 1.57 B 0 "0.00" / "853.93"
RiskFreeRate INT32 B BB_ 13812 0.29 B 0 "0.00463" / "0.10041"
common-metrics.csv.gz
This file contains time-series data of strategy NAV and account-level Greeks:
DateTime,StrategyNAV,BenchmarkPrice,acc_delta,acc_gamma,acc_theta,acc_vega,acc_wvega,acc_rho
2022-06-01 09:35:00,100000.0000,4160.4400,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000
2022-06-01 09:40:00,100000.0000,4152.0900,0.0000,0.0000,0.0000,0.0000,0.0000,0.0000
backtest-analytics.json
This file contains summary performance metrics for both the strategy and benchmark:
{
"Benchmark": {
"Start Period": "2022-06-01",
"End Period": "2025-03-21",
"Risk-Free Rate": "0.0",
"Time in Market": "100.0",
"Cumulative Return": "36.0",
"CAGR﹪": "11.58",
"Sharpe": "0.78",
"Prob. Sharpe Ratio": "90.48",
"Smart Sharpe": "0.6",
"Sortino": "1.09",
"Smart Sortino": "0.84",
"Sortino/√2": "0.77",
"Smart Sortino/√2": "0.59",
"Omega": "1.07",
"Max Drawdown": "-19.08",
"Longest DD Days": "300",
"Volatility (ann.)": "15.53",
"R^2": "0.0",
"Information Ratio": "0.01",
"Calmar": "0.61",
"Skew": "-0.65",
"Kurtosis": "95.25",
"Expected Daily": "0.0",
"Expected Monthly": "0.91",
"Expected Yearly": "7.99",
"Kelly Criterion": "2.08",
"Risk of Ruin": "0.0",
"Daily Value-at-Risk": "-0.18",
"Expected Shortfall (cVaR)": "-0.18",
"Max Consecutive Wins": "16",
"Max Consecutive Losses": "22",
"Gain/Pain Ratio": "0.14",
"Gain/Pain (1M)": "0.68",
"Payoff Ratio": "0.98",
"Profit Factor": "1.02",
"Common Sense Ratio": "0.97",
"CPC Index": "0.51",
"Tail Ratio": "0.96",
"Outlier Win Ratio": "6.26",
"Outlier Loss Ratio": "5.68",
"MTD": "-5.37",
"3M": "-4.75",
"6M": "-0.96",
"YTD": "-4.15",
"1Y": "7.89",
"3Y (ann.)": "11.58",
"5Y (ann.)": "11.58",
"10Y (ann.)": "11.58",
"All-time (ann.)": "11.58",
"Best Day": "3.34",
"Worst Day": "-3.74",
"Best Month": "8.5",
"Worst Month": "-9.13",
"Best Year": "24.46",
"Worst Year": "-7.21",
"Avg. Drawdown": "-0.38",
"Avg. Drawdown Days": "2",
"Recovery Factor": "1.89",
"Ulcer Index": "0.06",
"Serenity Index": "0.07",
"Avg. Up Month": "3.77",
"Avg. Down Month": "-4.96",
"Win Days": "51.59",
"Win Month": "67.65",
"Win Quarter": "66.67",
"Win Year": "50.0",
"Beta": "",
"Alpha": "",
"Correlation": "",
"Treynor Ratio": ""
},
"Strategy": {
"Start Period": "2022-06-01",
"End Period": "2025-03-21",
"Risk-Free Rate": "0.0",
"Time in Market": "60.0",
"Cumulative Return": "191.14",
"CAGR﹪": "46.36",
"Sharpe": "1.72",
"Prob. Sharpe Ratio": "99.81",
"Smart Sharpe": "1.32",
"Sortino": "2.5",
"Smart Sortino": "1.92",
"Sortino/√2": "1.77",
"Smart Sortino/√2": "1.36",
"Omega": "1.07",
"Max Drawdown": "-13.33",
"Longest DD Days": "155",
"Volatility (ann.)": "23.75",
"R^2": "0.0",
"Information Ratio": "0.01",
"Calmar": "3.48",
"Skew": "0.57",
"Kurtosis": "150.14",
"Expected Daily": "0.0",
"Expected Monthly": "3.19",
"Expected Yearly": "30.62",
"Kelly Criterion": "3.32",
"Risk of Ruin": "0.0",
"Daily Value-at-Risk": "-0.28",
"Expected Shortfall (cVaR)": "-0.28",
"Max Consecutive Wins": "12",
"Max Consecutive Losses": "10",
"Gain/Pain Ratio": "1.06",
"Gain/Pain (1M)": "9.15",
"Payoff Ratio": "1.05",
"Profit Factor": "1.07",
"Common Sense Ratio": "1.15",
"CPC Index": "0.57",
"Tail Ratio": "1.08",
"Outlier Win Ratio": "8.25",
"Outlier Loss Ratio": "3.46",
"MTD": "-0.13",
"3M": "-3.54",
"6M": "-3.21",
"YTD": "-2.24",
"1Y": "2.92",
"3Y (ann.)": "46.36",
"5Y (ann.)": "46.36",
"10Y (ann.)": "46.36",
"All-time (ann.)": "46.36",
"Best Day": "5.62",
"Worst Day": "-5.35",
"Best Month": "26.12",
"Worst Month": "-2.85",
"Best Year": "67.43",
"Worst Year": "-2.24",
"Avg. Drawdown": "-0.7",
"Avg. Drawdown Days": "2",
"Recovery Factor": "14.34",
"Ulcer Index": "0.03",
"Serenity Index": "1.66",
"Avg. Up Month": "5.46",
"Avg. Down Month": "-1.37",
"Win Days": "50.56",
"Win Month": "67.65",
"Win Quarter": "75.0",
"Win Year": "75.0",
"Beta": "0.0",
"Alpha": "0.41",
"Correlation": "0.08",
"Treynor Ratio": "150799.29"
}
}
note
The performance metrics are calculated using our fork of QuantStats
Example Queries
Basic Query - Event Log Analysis
This query extracts P&L information from position exit events:
select SimTime, Vars.pos_pnl
from '/backtest-results/c03273b0-800f-41fd-99ad-6e82566705e7/c03273b0-800f-41fd-99ad-6e82566705e7.events.json.gz'
where EventType='ExitPosition';
Advanced Query - Consecutive Win/Loss Analysis
This query analyzes multiple backtest runs via event logs to calculate the Consecutive Win and Loss Streaks for each run:
WITH source_data AS (
SELECT
SimTime,
filename,
EventType,
Message,
PositionId,
Vars.pos_pnl as pos_pnl
FROM read_json(
['/backtest-results/ea25e9c0-5da0-456d-bb65-8cb05e534719/ea25e9c0-5da0-456d-bb65-8cb05e534719.events.json.gz',
'/backtest-results/2ca39e5b-abd0-4a11-a28b-c946f0019193/2ca39e5b-abd0-4a11-a28b-c946f0019193.events.json.gz'],
union_by_name = true, filename = true)
),
pnl_events AS (
SELECT
filename,
PositionId,
pos_pnl,
EventType,
Message,
CASE
WHEN EventType = 'ExitPosition' AND pos_pnl > 0 THEN 'win'
WHEN EventType = 'ExitPosition' AND pos_pnl < 0 THEN 'loss'
ELSE 'neutral'
END AS pnl_type,
ROW_NUMBER() OVER (PARTITION BY filename ORDER BY SimTime)
- ROW_NUMBER() OVER (PARTITION BY filename, (CASE WHEN pos_pnl > 0 THEN 'win' WHEN pos_pnl < 0 THEN 'loss' ELSE 'neutral' END) ORDER BY SimTime) AS grp
FROM source_data
),
consecutive_counts AS (
SELECT
filename,
pnl_type,
COUNT(*) AS counts
FROM pnl_events
WHERE EventType = 'ExitPosition' AND pnl_type != 'neutral'
GROUP BY filename, pnl_type, grp
),
max_consecutive AS (
SELECT
filename,
MAX(CASE WHEN pnl_type = 'win' THEN counts ELSE 0 END) AS max_consecutive_wins,
MAX(CASE WHEN pnl_type = 'loss' THEN counts ELSE 0 END) AS max_consecutive_losses
FROM consecutive_counts
GROUP BY filename
),
backtest_names AS (
SELECT
filename,
MAX(CASE WHEN EventType = 'Start' THEN
regexp_extract(Message, 'BacktestName: ([^ ]+)', 1)
ELSE NULL END) AS BacktestName,
MAX(CASE WHEN EventType = 'Start' THEN
regexp_extract(Message, 'StrategyName: ([^ ]+)', 1)
ELSE NULL END) AS StrategyName
FROM source_data
GROUP BY filename
)
SELECT
b.filename,
b.BacktestName,
-- b.StrategyName,
m.max_consecutive_wins,
m.max_consecutive_losses
FROM backtest_names b
JOIN max_consecutive m ON b.filename = m.filename;
This advanced query:
- Reads event logs from multiple backtest runs
- Identifies when trades were exited with profits or losses
- Uses a window function technique to identify consecutive sequences
- Calculates the maximum streak of consecutive wins and losses
- Extracts backtest names from the event logs
- Joins the information to present a comparative analysis
Query Tips
- Study DuckDB's Capabilities and SQL Dialect for advanced queries
- Use
read_jsonwith multiple file paths andunion_by_name=trueto analyze multiple backtests - Access nested JSON fields using dot notation (e.g.,
Vars.pos_pnl) - Use the
filenameparameter to track which file each record came from - Apply
regexp_extractto parse information from message strings - Leverage DuckDB's window functions for sophisticated sequential analysis