trading_bot_v4/docs/ENHANCEMENT_6_ANALYSIS_GUIDE.md

# Enhancement #6 - SL Distance Analysis Guide

**Status:** DATA COLLECTION ENABLED (Nov 27, 2025)  
**Purpose:** Gather revenge trade data to determine optimal SL distance multiplier

---

## What We're Tracking Now

### Database Fields Added
```sql
-- In StopHunt table:
slDistanceAtEntry    Float?   -- Distance from entry to stop zone (absolute value)
revengeOutcome       String?  -- "TP1", "TP2", "SL", "TRAILING_SL"
revengePnL           Float?   -- Actual P&L from revenge trade
revengeFailedReason  String?  -- "stopped_again" if re-stopped
originalATR          Float?   -- ATR at original stop-out
```

### What Gets Calculated
```typescript
// When revenge trade executes:
const slDistance = direction === 'long'
  ? currentPrice - stopHuntPrice  // LONG: Room below entry
  : stopHuntPrice - currentPrice  // SHORT: Room above entry

// Stored: Math.abs(slDistance)
// Logged: Distance in $ and in ATR multiples
// Example: "$1.48 distance (2.47× ATR)"
```

---

## Analysis Queries (After 20+ Revenge Trades)

### Query 1: SL Distance vs Outcome
```sql
-- Do tighter entries get re-stopped more often?
SELECT 
  CASE 
    WHEN "slDistanceAtEntry" / "originalATR" < 1.0 THEN '<1× ATR (Very Tight)'
    WHEN "slDistanceAtEntry" / "originalATR" < 1.5 THEN '1-1.5× ATR (Tight)'
    WHEN "slDistanceAtEntry" / "originalATR" < 2.0 THEN '1.5-2× ATR (Moderate)'
    WHEN "slDistanceAtEntry" / "originalATR" < 2.5 THEN '2-2.5× ATR (Safe)'
    ELSE '2.5×+ ATR (Very Safe)'
  END as distance_tier,
  
  COUNT(*) as total_trades,
  
  -- Win rate
  ROUND(100.0 * SUM(CASE 
    WHEN "revengeOutcome" IN ('TP1', 'TP2', 'TRAILING_SL') THEN 1 
    ELSE 0 
  END) / COUNT(*), 1) as win_rate,
  
  -- Re-stopped rate
  ROUND(100.0 * SUM(CASE 
    WHEN "revengeFailedReason" = 'stopped_again' THEN 1 
    ELSE 0 
  END) / COUNT(*), 1) as restopped_rate,
  
  -- Average P&L
  ROUND(AVG("revengePnL"), 2) as avg_pnl,
  
  -- Total P&L
  ROUND(SUM("revengePnL"), 2) as total_pnl
  
FROM "StopHunt"
WHERE "revengeExecuted" = true
  AND "slDistanceAtEntry" IS NOT NULL
  AND "originalATR" IS NOT NULL
GROUP BY distance_tier
ORDER BY MIN("slDistanceAtEntry" / "originalATR");
```

**Expected Output:**
```
distance_tier          | total | win_rate | restopped_rate | avg_pnl | total_pnl
-----------------------+-------+----------+----------------+---------+-----------
<1× ATR (Very Tight)   |     5 |     40.0 |           60.0 |  -25.50 |   -127.50
1-1.5× ATR (Tight)     |     8 |     62.5 |           25.0 |   15.25 |    122.00
1.5-2× ATR (Moderate)  |    12 |     75.0 |           16.7 |   42.30 |    507.60
2-2.5× ATR (Safe)      |     7 |     85.7 |           14.3 |   68.45 |    479.15
2.5×+ ATR (Very Safe)  |     3 |    100.0 |            0.0 |   92.30 |    276.90
```

**Decision Logic:**
- If <1× ATR has high re-stop rate (>50%): Filter needed
- If 1.5-2× ATR has best risk/reward: Use 1.5× multiplier
- If 2-2.5× ATR has highest win rate: Use 2.0× multiplier
- If Very Safe (2.5×+) rarely happens: Lower multiplier to catch more

---

### Query 2: Missed Opportunities
```sql
-- How many revenge opportunities didn't execute because reversal wasn't deep enough?
SELECT 
  COUNT(*) as total_stop_hunts,
  
  -- How many reversed but didn't enter
  SUM(CASE 
    WHEN "revengeExecuted" = false 
    AND "revengeWindowExpired" = true 
    AND ("lowestPriceAfterStop" < "originalEntryPrice" -- LONG reversed
         OR "highestPriceAfterStop" > "originalEntryPrice") -- SHORT reversed
    THEN 1 ELSE 0 
  END) as missed_reversals,
  
  -- Average distance of missed reversals
  ROUND(AVG(CASE 
    WHEN "revengeExecuted" = false 
    AND "revengeWindowExpired" = true 
    THEN ABS("lowestPriceAfterStop" - "stopHuntPrice") 
  END), 2) as avg_missed_distance
  
FROM "StopHunt"
WHERE "originalATR" IS NOT NULL;
```

**Tells us:** If we filter too strictly (e.g., 3× ATR), how many opportunities do we lose?

---

### Query 3: Time to Re-Stop
```sql
-- How quickly do re-stopped revenge trades fail?
SELECT 
  CASE 
    WHEN EXTRACT(EPOCH FROM ("Trade"."exitTime" - "StopHunt"."revengeTime")) < 300 THEN '<5min (Instant)'
    WHEN EXTRACT(EPOCH FROM ("Trade"."exitTime" - "StopHunt"."revengeTime")) < 900 THEN '5-15min (Fast)'
    WHEN EXTRACT(EPOCH FROM ("Trade"."exitTime" - "StopHunt"."revengeTime")) < 1800 THEN '15-30min (Moderate)'
    ELSE '30min+ (Slow)'
  END as time_to_restop,
  
  COUNT(*) as count,
  
  ROUND(AVG("StopHunt"."slDistanceAtEntry" / "StopHunt"."originalATR"), 2) as avg_atr_multiple
  
FROM "StopHunt"
INNER JOIN "Trade" ON "StopHunt"."revengeTradeId" = "Trade"."id"
WHERE "StopHunt"."revengeFailedReason" = 'stopped_again'
GROUP BY time_to_restop
ORDER BY MIN(EXTRACT(EPOCH FROM ("Trade"."exitTime" - "StopHunt"."revengeTime")));
```

**Insight:** If most re-stops happen <5min, they're wicks. Wider SL distance helps.

---

### Query 4: Direction-Specific Analysis
```sql
-- Do LONGs vs SHORTs need different SL distances?
SELECT 
  "direction",
  
  COUNT(*) as total,
  
  ROUND(AVG("slDistanceAtEntry" / "originalATR"), 2) as avg_atr_multiple,
  
  ROUND(100.0 * SUM(CASE 
    WHEN "revengeFailedReason" = 'stopped_again' THEN 1 ELSE 0 
  END) / COUNT(*), 1) as restopped_rate,
  
  ROUND(AVG("revengePnL"), 2) as avg_pnl
  
FROM "StopHunt"
WHERE "revengeExecuted" = true
  AND "slDistanceAtEntry" IS NOT NULL
GROUP BY "direction";
```

**Possible outcome:** SHORTs need wider distance (more volatile reversals)

---

## Decision Matrix (After 20+ Trades)

### Scenario A: Tight Entries Work
**Data shows:** 1-1.5× ATR has 70%+ win rate, low re-stop rate
**Decision:** Use 1.5× ATR multiplier (or no filter at all)
**Trade-off:** More revenge opportunities, slightly higher re-stop risk

### Scenario B: Moderate Distance Optimal
**Data shows:** 1.5-2× ATR has best risk/reward
**Decision:** Use 1.75× or 2.0× ATR multiplier
**Trade-off:** Balanced approach (recommended starting point)

### Scenario C: Wide Distance Required
**Data shows:** <2× ATR has >40% re-stop rate
**Decision:** Use 2.5× or 3.0× ATR multiplier
**Trade-off:** Fewer opportunities, but much higher win rate

### Scenario D: Direction-Specific
**Data shows:** LONGs work at 1.5×, SHORTs need 2.5×
**Decision:** Implement separate multipliers per direction
**Trade-off:** More complex but optimized

---

## Implementation Plan (After Data Collection)

### Step 1: Review Data (After 20 Revenge Trades)
```bash
# Run Query 1 in database
docker exec trading-bot-postgres psql -U postgres -d trading_bot_v4 -c "
[Query 1 from above]
"

# Save results to CSV
# Analyze in spreadsheet or share with me
```

### Step 2: Calculate Optimal Multiplier
```python
# Simple calculation:
optimal_multiplier = (
  sum(distance * pnl for each tier) / 
  sum(pnl for each tier)
)

# Weight by win rate:
optimal_multiplier_weighted = (
  sum(distance * win_rate * count for each tier) /
  sum(win_rate * count for each tier)
)
```

### Step 3: Implement Filter
```typescript
// In stop-hunt-tracker.ts shouldExecuteRevenge()
const slDistance = stopHunt.direction === 'long'
  ? currentPrice - stopHunt.stopHuntPrice
  : stopHunt.stopHuntPrice - currentPrice

const minSafeDistance = stopHunt.originalATR * 2.0  // Use data-driven value

if (Math.abs(slDistance) < minSafeDistance) {
  console.log(`⚠️ SL distance too tight: ${Math.abs(slDistance).toFixed(2)} < ${minSafeDistance.toFixed(2)}`)
  return false
}
```

### Step 4: A/B Test (Optional)
```typescript
// Randomly assign filter on/off
const useFilter = Math.random() > 0.5

if (useFilter && Math.abs(slDistance) < minSafeDistance) {
  await prisma.stopHunt.update({
    where: { id: stopHunt.id },
    data: { 
      notes: 'Would have been filtered by SL distance check',
      filterTestGroup: 'A' 
    }
  })
  return false
}

// Compare groups after 40 trades (20 each)
```

---

## Monitoring Dashboard

### Key Metrics to Track
1. **Re-Stop Rate:** % of revenge trades that hit SL immediately
2. **Average SL Distance:** Median ATR multiple at entry
3. **Win Rate by Distance:** Scatterplot (x=distance, y=outcome)
4. **Missed Opportunities:** Reversals that didn't execute

### Alert Thresholds
- **High re-stop rate:** >30% revenge trades fail = tighten filter
- **Low execution rate:** <50% stop hunts execute = loosen filter
- **Negative total P&L:** Sum of revenge P&L < 0 = pause system

---

## Timeline

**Week 1-2:** Data collection (first 5-10 revenge trades)
**Week 3:** Preliminary analysis (spot obvious patterns)
**Week 4:** Full analysis (20+ trades, statistical significance)
**Week 5:** Implement optimal filter based on data
**Week 6+:** Monitor improvement, adjust if needed

**Current Status:** ✅ Data collection enabled, awaiting first revenge trades

---

## Example Real-World Analysis

### Hypothetical Results After 25 Revenge Trades
```
Distance Tier        | Trades | Win Rate | Avg P&L | Total P&L
---------------------|--------|----------|---------|----------
<1× ATR              |      3 |     33%  |  -$45   |  -$135
1-1.5× ATR           |      7 |     57%  |   $12   |   $84
1.5-2× ATR           |      9 |     78%  |   $58   |  $522
2-2.5× ATR           |      4 |     75%  |   $62   |  $248
2.5×+ ATR            |      2 |    100%  |   $89   |  $178
```

**Conclusion from data:**
- <1× ATR: High failure rate, negative P&L → FILTER OUT
- 1-1.5× ATR: Break-even, mediocre win rate → QUESTIONABLE
- 1.5-2× ATR: **SWEET SPOT** - 9 trades, 78% WR, +$522
- 2-2.5× ATR: Good but fewer opportunities
- 2.5×+ ATR: Perfect WR but rarely happens

**Optimal setting:** 1.5× ATR minimum distance
**Expected impact:** Block 10 bad trades, keep 15 good trades = +$606 net improvement

---

## Questions for Future AI Agents

1. **What's the optimal multiplier?** → Check Query 1 results
2. **Are we missing too many opportunities?** → Check Query 2 results
3. **Do re-stops happen instantly or slowly?** → Check Query 3 results
4. **Do LONGs vs SHORTs need different filters?** → Check Query 4 results
5. **Should we implement the filter?** → If re-stop rate >30%, YES

**Remember:** Data > theory. Don't implement filter until data proves it helps.