Skip to main content

Epic 4.8: Entity-Level Comparison

Status: ✅ Complete (January 15, 2026)

Overview​

Five critical fixes deployed to achieve near-perfect return matching:

  1. Capital denominator fix: Changed from prior month-end NAV to prior day NAV + same-day cash flow
  2. Multi-account aggregation fix: Changed from incorrect geometric linking to market-value weighted average
  3. Account mapping fix: Now includes both entity.btig_account_code and all account.account_code values
  4. Returns start date filter: Added daily-level filtering to exclude data before entity's valid return period
  5. BTIG/Cash aggregation fix: Fixed DISTINCT ON bug and cash transaction duplication

Final Results​

Anthracite Holdings LP:

MonthBeforeAfter
Jan 2023+1426 bps-17 bps (review) ✓
Feb 2023-192 bps2 bps (match) ✓
Dec 2025-82 bps-3 bps (match) ✓

ARP (Anthracite Realty Partners):

MonthBeforeAfter
Jan 2023+2812 bps0 bps (match) ✓
Feb 2023N/A0 bps (match) ✓
Mar 2023N/A0 bps (match) ✓

ARP overall: 35/37 months matching (94.6%)

Daily returns now match exactly where both systems have data (0 bps diff on trading days).

Root Cause: Granularity Mismatch​

The Problem: We were comparing at the wrong level.

AspectAccount-LevelEntity-Level
Match rate34.7%~95%
Investigate casesMany0
Internal transfersShow as cash flowsCancel out
BTIG reportingNot used✅ Matches

Example: Anthracite Holdings LP (Entity 2)​

Entity 2 has two accounts: AV7K and XPB006152.

When $50M transfers from AV7K to XPB006152:

  • Account-level: AV7K shows -$50M outflow, XPB006152 shows +$50M inflow
  • Entity-level: Net zero cash flow (transfers cancel)

BTIG reports entity-level returns, so internal transfers don't affect their calculations.

Key Discovery: BTIG daily_ror Format​

BTIG stores daily_ror as percentage, not decimal.

-- WRONG: Treats 1.4 as 140%
1 + daily_ror  -- Gives impossible returns like +2371%

-- CORRECT: Treats 1.4 as 1.4%
1 + daily_ror/100  -- Gives reasonable returns

Results: Entity-Level Comparison (Final)​

Summary Statistics​

StatusResult
Investigate cases0 (was 8)
Match rate~95% (was 69.4%)

All Investigate Cases Resolved​

EntityMonthBeforeAfterFix
ARPJan 2023+2812 bps0 bpsreturns_start_date filter
Anthracite Holdings LPJan 2023+1426 bps-17 bpsDuplicate removal + returns_start_date
Anthracite Holdings LPFeb 2023-192 bps2 bpsCapital denominator fix
Anthracite Holdings LPDec 2025-82 bps-3 bpsBTIG aggregation + cash dedup

Critical Fixes (January 15, 2026)​

Fix 1: Capital Denominator​

Problem: Using prior month-end NAV as denominator caused massive discrepancies when cash flows occurred mid-month.

BTIG Formula (verified):

daily_ror = (End MV - Prior MV - CF) / (Prior MV + CF)

Fix 2: Multi-Account Aggregation​

Problem: Was geometric-linking daily returns from ALL accounts, which is mathematically incorrect.

Solution:

-- First: market-value weighted daily return per entity
SUM(account_mv * daily_ror) / SUM(account_mv) AS entity_daily_ror

-- Then: compound entity daily returns over month
EXP(SUM(LN(1 + entity_daily_ror/100))) - 1

Fix 3: Account Mapping​

Problem: Only used entity.btig_account_code, missing additional accounts.

Solution:

entity_accounts AS (
    SELECT entity_id, entity_name, LOWER(btig_account_code) AS account_code
    FROM entity WHERE btig_account_code IS NOT NULL
    UNION
    SELECT e.entity_id, e.entity_name, LOWER(a.account_code)
    FROM entity e JOIN account a ON e.entity_id = a.entity_id
)

Fix 4: Returns Start Date Filter​

Problem: Erroneous year-end positions (e.g., ARP Dec 31 phantom $39M) polluted TWR calculations.

Solution: Filter daily data at returns_start_date:

WHERE pnl.pnl_date >= ei.returns_start_date

Fix 5: BTIG Aggregation & Cash Deduplication​

Problem 1: DISTINCT ON only took one account's market value, missing xpb006152's $9M.

Problem 2: Multiple cash transactions per day caused row duplication via LEFT JOIN.

Solution: Aggregate BTIG data and cash transactions by entity/date with GROUP BY.

Success Criteria​

CriterionTargetCurrent
Entity-level match rateGreater than 80%✅ ~95%
Investigate casesLess than 5✅ 0
View deployedYes✅ Complete
CLI updatedYes⏳ Pending
All fixes deployedYes✅ Complete

Tickets​

TicketDescriptionPriorityStatus
-Create v_returns_comparison_entity viewHigh✅ Complete
-Fix Jan 2023 duplicate deposits (AV7K)Critical✅ Fixed
-Fix Jan 2023 duplicate REC (ARP)Critical✅ Fixed
77b39a3aARP Jan 2023 year-end P&L spikeHigh✅ Fixed
9f7a0824Update sable-cli to use entity-levelNormalBacklog

Data Quality Fixes Applied​

AV7K - Duplicate $15M Deposit​

  • Deleted: Jan 16, 2023 - $15M DEP (duplicate)
  • Kept: Jan 17, 2023 - $15M DEP (matches BTIG perf)

ARP - Duplicate REC Transactions​

  • Deleted: Jan 19, 2023 - 55 REC transactions ($7.98M total)
  • Kept: Jan 20, 2023 - 55 REC transactions (matches BTIG perf)

ARP - Returns Start Date Filter​

  • Set: returns_start_date = '2023-01-20'
  • Reason: Erroneous phantom positions Dec 31, 2022 ($39.5M) vanish Jan 1

v_dietz_daily - Aggregation Fixes​

  • BTIG: Changed from DISTINCT ON to GROUP BY with market-value weighted average
  • Cash: Added GROUP BY to aggregate multiple transactions per day

Artifacts​