Skip to main content

Troubleshooting Returns Discrepancies

Guide for debugging returns mismatches between BTIG and Sable calculations.

Overview​

Sable computes returns using Modified Dietz methodology to match BTIG's time-weighted returns. Discrepancies indicate issues in NAV calculations, cash flow timing, or entity mapping.

Key API Endpoints​

EndpointDescription
GET /api/returns/comparisonCompare BTIG vs Sable returns
GET /api/returns/dietz-dailyDaily Modified Dietz returns
GET /api/returns/cash-transactionsCash flow transactions

Base URL: https://sable-data.jettaintelligence.com

Diagnostic Workflow​

Step 1: Identify Discrepancies​

curl "https://sable-data.jettaintelligence.com/api/returns/comparison?account_code=AV7K&limit=50" \
  -H "x-api-key: $SABLE_API_KEY"

Response includes:

  • status: match (10 bps or less), review (10-50 bps), investigate (over 50 bps), missing
  • diff_dietz_bps: Difference in basis points
  • sable_dietz_pct vs btig_return_pct: Raw return percentages

Step 2: Analyze Daily Returns​

For months with discrepancies, drill into daily returns:

curl "https://sable-data.jettaintelligence.com/api/returns/dietz-daily?entity_name=EntityName&start_date=2024-01-01&end_date=2024-01-31" \
  -H "x-api-key: $SABLE_API_KEY"

Check:

  • Are there days with unusual daily_return_pct?
  • Are begin_nav and end_nav consistent day-over-day?
  • Is return_factor always positive?

Step 3: Check Cash Flows​

curl "https://sable-data.jettaintelligence.com/api/returns/cash-transactions?entity_name=EntityName&limit=100" \
  -H "x-api-key: $SABLE_API_KEY"

Verify:

  • Do cash flows match BTIG records?
  • Are deposits positive and withdrawals negative?
  • Is timing (transaction_date) correct?

Common Issues​

Missing Data​

Symptom: status: "missing" or sable_dietz_pct: null

Causes:

  1. Entity not mapped to BTIG account
  2. Missing NAV data in gold.v_nav_daily
  3. Missing capital data in gold.v_capital_daily

Fix:

  1. Check sable.entity table for btig_account_code column
  2. Verify NAV exists for the entity/date range
  3. Verify capital exists for month start

Large Discrepancy (>50 bps)​

Symptom: status: "investigate"

Common Causes:

  1. Cash flow timing mismatch: BTIG uses different transaction dates
  2. NAV calculation error: Check P&L rollup in gold.v_pnl_daily
  3. Missing days: Calendar gaps causing incorrect linking

Fix:

  1. Compare btig_cashflow vs sable_cashflow in comparison data
  2. If mismatch: Update cash transactions
  3. Re-run dbt models: dbt run --select v_dietz_daily v_returns_comparison

Small Discrepancy (10-50 bps)​

Symptom: status: "review"

Common Causes:

  1. Rounding differences
  2. Slight timing differences in cash flows
  3. Holiday/weekend date handling

Fix:

  • Usually acceptable - document and monitor
  • If persistent: Check cash flow weighting calculation

dbt Model Hierarchy​

gold.v_returns_comparison
├── tmp_btig.v_performance_monthly_by_account  (BTIG source)
├── gold.v_nav_daily                           (Sable NAV)
├── gold.v_capital_daily                       (Sable capital)
└── gold.v_dietz_daily                         (Daily Modified Dietz)
    └── gold.v_cash_transactions               (Cash flows)

Refresh After Fixes​

cd sable-data/dbt
dbt run --select v_dietz_daily v_returns_comparison

Success Criteria​

  • match_rate > 95% in comparison summary
  • No investigate status records
  • diff_dietz_bps within +/- 10 bps for all months