Market Data

Market Data Setup

Guide the user through importing market data so other skills (DC analysis, health checks, replay) have the data they need.

When This Skill Triggers

• enrich_trades returns warnings about missing market data
• analyze_regime_performance returns "No trades matched to market data"
• replay_trade returns 0 minute bars / $0 P&L
• batch_exit_analysis returns mostly noTrigger with $0 P&L
• User says "import market data", "set up market data", "I need VIX data", etc.

Step 1: Diagnose What's Missing

Ask the user what they're trying to do, then check what data exists:

-- Check daily data coverage
SELECT ticker, COUNT(*) as rows,
  MIN(date) as earliest, MAX(date) as latest
FROM market.daily GROUP BY ticker ORDER BY ticker

-- Check intraday data coverage
SELECT
  CASE WHEN ticker LIKE 'SPX%' OR ticker LIKE 'SPXW%' THEN 'SPX options'
       WHEN ticker LIKE 'QQQ%' THEN 'QQQ options'
       WHEN ticker = 'SPX' THEN 'SPX underlying'
       ELSE ticker END as category,
  COUNT(DISTINCT ticker) as tickers,
  COUNT(*) as total_bars
FROM market.intraday GROUP BY category

-- Check context derived (regime/term structure)
SELECT COUNT(*) as rows, MIN(date) as earliest, MAX(date) as latest
FROM market._context_derived

Present what's available and what's missing for their goal.

Step 2: Determine the Right Import

For Regime Analysis (VIX regimes, term structure, enriched trades)

Minimum needed: Daily OHLCV for the underlying + VIX context (VIX/VIX9D/VIX3M)

| What to import | Tool | Example | |---------------|------|---------| | Underlying daily (SPX) | import_from_api | ticker: "SPX", targettable: "daily" | | Underlying daily (QQQ) | import_from_api | ticker: "QQQ", targettable: "daily" | | VIX context (all 3 tickers) | import_from_api | ticker: "VIX", targettable: "context" | | Enrichment (RSI, VolRegime, etc.) | enrich_market_data | Auto-runs after import, or manual |

Order matters: Import daily first, then context, then enrichment runs automatically.

Useful flags:

• dry_run: true — validates parameters and shows what would be imported without writing
• skip_enrichment: true — skips automatic enrichment (useful when batching multiple imports, then run enrich_market_data once at the end)

Date range: Match the trade data range. Check with:

SELECT MIN(date_opened) as earliest, MAX(date_opened) as latest
FROM trades.trade_data WHERE block_id = ''

For Trade Replay (minute-level P&L paths, greeks, exit trigger simulation)

Needed: Intraday 1-minute bars for each option leg in the trades

The replay engine auto-fetches on cache miss if MASSIVE_API_KEY is set. For bulk pre-loading:

1. Get the option tickers from recent trades:

SELECT DISTINCT legs, date_opened FROM trades.trade_data
WHERE block_id = '' ORDER BY date_opened DESC LIMIT 10

1. Parse the OCC tickers from the legs string (the replay tool does this automatically)

1. Import each option ticker:

import_from_api: ticker="SPXW260320P06410000", target_table="intraday", timespan="1m"

Note: Option data availability varies by provider. Massive.com typically has data from ~2022 onward for SPX/SPY options. Older trades won't have replay data.

For TradingView CSV Import

TradingView exports have a Unix timestamp column called time. The mapping handles date+time extraction automatically.

Daily bars:

{
  "file_path": "~/Downloads/SPX_daily.csv",
  "ticker": "SPX",
  "target_table": "daily",
  "column_mapping": {
    "time": "date",
    "open": "open",
    "high": "high",
    "low": "low",
    "close": "close"
  }
}

Intraday bars:

{
  "file_path": "~/Downloads/SPX_15m.csv",
  "ticker": "SPX",
  "target_table": "intraday",
  "column_mapping": {
    "time": "date",
    "open": "open",
    "high": "high",
    "low": "low",
    "close": "close"
  }
}

Use dry_run: true first to validate the mapping before writing.

For External DuckDB Import

If the user has market data in another DuckDB file:

{
  "db_path": "~/data/market.duckdb",
  "query": "SELECT date, open, high, low, close FROM ext_import_source.spx_daily",
  "ticker": "SPX",
  "target_table": "daily",
  "column_mapping": {
    "date": "date",
    "open": "open",
    "high": "high",
    "low": "low",
    "close": "close"
  }
}

Step 3: Execute the Import

Run the imports based on what's needed. Common recipes:

Recipe: Full SPX Setup (regime + enrichment)

1. import_from_api: ticker="SPX", from="2016-01-01", to="today", target_table="daily"
2. import_from_api: ticker="VIX", from="2016-01-01", to="today", target_table="context"
3. Enrichment runs automatically after each import

Recipe: Full QQQ Setup

1. import_from_api: ticker="QQQ", from="2016-01-01", to="today", target_table="daily"
2. VIX context (same as above — shared across all underlyings)
3. Enrichment runs automatically

Recipe: Replay Data for a Block

1. Run batch_exit_analysis or replay_trade — auto-fetches on cache miss if API key is set
2. Or manually import specific option tickers via import_from_api with target_table="intraday"

Step 4: Verify

After importing, verify the data is available:

-- Check daily coverage
SELECT ticker, COUNT(*) as rows, MIN(date) as earliest, MAX(date) as latest
FROM market.daily GROUP BY ticker ORDER BY ticker

-- Check regime data populated
SELECT Vol_Regime, COUNT(*) as days
FROM market._context_derived
GROUP BY Vol_Regime ORDER BY Vol_Regime

-- Check term structure populated
SELECT Term_Structure_State, COUNT(*) as days
FROM market._context_derived
WHERE Term_Structure_State IS NOT NULL
GROUP BY Term_Structure_State

If Term_Structure_State is all NULL, VIX3M data is missing — run the context import.

Common Issues

| Symptom | Cause | Fix | |---------|-------|-----| | enrich_trades shows null VIX3M fields | VIX3M not in market.daily | Import with targettable="context" | | Term_Structure_State all NULL | VIX3M data missing | Import with targettable="context" | | analyze_regime_performance returns 0 matched | No daily data for that ticker | Import daily OHLCV for the underlying | | replay_trade returns 0 bars | No intraday option data cached | Set MASSIVEAPIKEY for auto-fetch, or import manually | | Enrichment fields missing (RSI, ATR) | enrich_market_data not run | Run it manually for the ticker | | API import returns 0 rows | Date range outside provider coverage | Try a more recent date range | | CSV import fails on column mapping | Wrong column names | Use dry_run: true first to preview |

Cleanup

Use purge_market_table to remove market data when needed (e.g., corrupted import, wrong ticker). This deletes rows from the specified table matching a ticker and optional date range.

What NOT to Do

• Don't import daily data without also importing VIX context — regime analysis needs both
• Don't assume all date ranges have data — Massive.com option data starts ~2022
• Don't skip enrichment — without it, RSI, Vol_Regime, and other derived fields won't exist
• Don't import the same data twice without checking — upserts are safe but waste API calls

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: davidromeo
• Source: davidromeo/tradeblocks-skills
• License: MIT
• Homepage: https://tradeblocks.io

Install and usage instructions live in the source repository linked above.