institutional-trader/TESTING_GUIDE.md

# Testing Guide for Historical Data & Backtesting

## Prerequisites

1. Database connection configured (see `.env` file)
2. Node.js installed and in PATH
3. Python 3.x installed with required packages:
   - `yfinance`
   - `pandas`
   - `psycopg2`
   - `python-dotenv`

## Step 1: Validate Database Schema

Run the schema validation script to ensure all tables and indexes exist:

```bash
cd backend
node scripts/validateSchema.js
```

**Expected Output:**
- ✅ All required tables exist
- ✅ Indexes on critical tables
- ✅ PRIMARY KEY constraints verified
- ⚠️  Any warnings about data types

**If issues are found:**
- Run `backend/database/schema.sql` in your database
- Run `backend/database/missing_tables.sql` if needed

## Step 2: Pull Historical Data

### Test with Single Symbol First

```bash
python yahhooscript.py --only AAPL
```

This will:
- Pull 30 days of 5-minute intraday data for AAPL
- Pull daily data (2 years) for AAPL
- Store in PostgreSQL database

**Verify data was pulled:**
```sql
-- Check intraday data
SELECT COUNT(*), MIN(ts), MAX(ts)
FROM prices_intraday_1m
WHERE symbol = 'AAPL'
  AND ts >= NOW() - INTERVAL '30 days';

-- Check daily data
SELECT COUNT(*), MIN("Date"), MAX("Date")
FROM prices_daily
WHERE symbol = 'AAPL';
```

### Pull Full Universe

```bash
python yahhooscript.py
```

This will pull data for all symbols in your universe (S&P 500, S&P 400, Nasdaq-100, plus custom groups).

**Note:** This may take 30-60 minutes depending on:
- Number of symbols
- Yahoo Finance rate limiting
- Network speed

**Monitor progress:**
- Script shows batch progress
- Check for errors in output
- Verify data counts in database

## Step 3: Test Backtester

### Run Test Script

```bash
cd backend
node scripts/testBacktester.js
```

This will:
- Test database connectivity
- Check price data availability
- Run a sample backtest
- Verify enhanced metrics are calculated

### Test via API

Start the backend server:
```bash
cd backend
npm run dev
```

**Run Single Backtest:**
```bash
curl -X POST http://localhost:3010/api/backtest/run \
  -H "Content-Type: application/json" \
  -d '{
    "pattern": {
      "rocketScoreMin": 5.0,
      "session": "RTH"
    },
    "options": {
      "lookbackDays": 30,
      "targetPct": 1.5,
      "stopPct": 1.5
    }
  }'
```

**Compare Strategies:**
```bash
curl -X POST http://localhost:3010/api/backtest/compare \
  -H "Content-Type: application/json" \
  -d '{
    "strategies": [
      {
        "name": "High Score RTH",
        "pattern": { "rocketScoreMin": 5, "session": "RTH" }
      },
      {
        "name": "Tape Aligned",
        "pattern": { "rocketScoreMin": 3, "tapeAligned": true }
      }
    ],
    "options": {
      "lookbackDays": 30
    }
  }'
```

## Step 4: Validate Results

### Check Metrics

Verify that all new metrics are being calculated:

```javascript
{
  "winRate": 70.1,
  "avgWin": 2.7,
  "avgLoss": -1.3,
  "expectancy": 1.5,
  "sharpeRatio": 1.23,        // ✅ New
  "maxDrawdown": 15.0,         // ✅ New
  "streaks": {                 // ✅ New
    "maxWinStreak": 5,
    "maxLossStreak": 2
  },
  "sessionPerformance": [     // ✅ New
    { "session": "RTH", "winRate": 72.5, "totalTrades": 80 }
  ],
  "symbolPerformance": {      // ✅ New
    "best": [{"symbol": "AAPL", "avgReturn": 3.2}],
    "worst": [{"symbol": "TSLA", "avgReturn": -1.5}]
  }
}
```

### Verify Data Quality

```sql
-- Check data completeness for a symbol
SELECT 
  symbol,
  COUNT(*) as total_bars,
  COUNT(DISTINCT DATE(ts)) as trading_days,
  MIN(ts) as earliest,
  MAX(ts) as latest
FROM prices_intraday_1m
WHERE symbol = 'AAPL'
  AND ts >= NOW() - INTERVAL '30 days'
GROUP BY symbol;

-- Should show ~20-22 trading days (excluding weekends/holidays)
-- Should show ~390 bars per day for 5-minute data (6.5 hours * 12 bars/hour)
```

## Troubleshooting

### No Data Found in Backtest

**Possible causes:**
1. No options flow data in date range
   - Check: `SELECT COUNT(*) FROM "OptionsFlow_monthly" WHERE "CreatedDate" >= CURRENT_DATE - INTERVAL '30 days'`
2. Pattern criteria too strict
   - Try lowering `rocketScoreMin` or removing filters
3. No price data available
   - Run: `python yahhooscript.py --only SYMBOL`

### Yahoo Finance Rate Limiting

**Symptoms:**
- Empty data returned
- Timeout errors
- JSON parsing errors

**Solutions:**
1. Increase `SLEEP_BETWEEN_BATCHES` in `yahhooscript.py`
2. Reduce `BATCH_SIZE` (try 50 instead of 110)
3. Run during off-peak hours
4. Use VPN if IP is rate-limited

### Database Connection Issues

**Check:**
1. `.env` file has correct credentials
2. Database is not paused (Supabase)
3. Network connectivity
4. Firewall rules

**Test connection:**
```bash
cd backend
node -e "import('./src/db.js').then(m => m.testConnection())"
```

## Success Criteria

✅ Schema validation passes  
✅ 30 days of historical data pulled  
✅ Backtester runs without errors  
✅ All enhanced metrics are calculated  
✅ API endpoints return valid results  
✅ Data quality checks pass  

## Next Steps

After successful testing:
1. Run full universe data pull (if not done)
2. Test with various pattern combinations
3. Compare multiple strategies
4. Analyze results and refine patterns
5. Set up scheduled data pulls (optional)