Methodology: ETF Lookthrough (True Exposure)

What the panel says and what it means

On the portfolio Risk tab, beneath the metric-cards row, the True Exposure (ETF Lookthrough) panel shows two horizontal stacked bars: one for sector mix, one for geographic mix. Both are computed after expanding every eligible ETF in your portfolio into the underlying stocks it holds, weighted by your position size in the ETF. A user who holds VTI sees not "100% diversified fund" but a Technology / Healthcare / Industrials / … breakdown reflecting the roughly 3,800 stocks VTI actually holds.

The panel is informational, not advice. It exists to make exposure honest: an investor who thinks they hold "just an S&P 500 fund" can see that nearly thirty percent of that fund is in three companies, and a portfolio of three different broad-market ETFs is usually less diversified than three separate logos suggest.

1. Data source

The panel reads GET /api/portfolio/lookthrough?tickers=<...>&weights=<...>. The backend (services/portfolio_lookthrough.py via routes/routes_portfolio_fastapi/portfolio_state.py) reads from two PostgreSQL tables:

• fund_holdings_snapshot — the SEC 13F-HR positions for each ETF’s issuer CIK, joined through etf_catalog.cik. 13F-HR is the institutional holdings disclosure filed quarterly by managers of more than $100 million in qualifying assets; for ETFs whose issuer files 13F (Vanguard, iShares, State Street, Schwab, Invesco, and so on) the holdings are the most authoritative public record we can ingest without paying a commercial data vendor.
• company_profiles — for the sector and headquarters country of each underlying ticker (both the ETF expansions and your direct stock holdings).

Per-ETF expansion math: for each underlying row u inside an ETF E the panel computes u.value_usd / sum(value_usd) as the underlying’s share of the ETF’s reported value, then multiplies by your portfolio weight in E. The result is summed across every position into per-sector and per-country totals. The math is pinned in tests/test_portfolio_lookthrough_contract.py; you can read the contract test for the exact arithmetic on a worked two-ETF example (test_overlapping_etfs_sum_correctly).

2. Why some funds aren’t expanded

Several broad categories of ticker carry an inline opaque-fund chip on the panel rather than expanding into holdings:

• Commodity trusts (GLD, SLV, USO, and similar) do not file 13F — the trust holds a physical asset (gold bars, oil contracts) rather than equity securities, and the holdings disclosure we ingest does not apply. These are counted at the headline ticker; your portfolio shows the exposure as "GLD" rather than as the underlying metal.
• Bond funds file an NPORT-P rather than a 13F, and the NPORT-P pipeline is on the ingest roadmap (CHAOS audit Track #3, planned post-launch). Until that lands, bond funds are treated like commodity trusts: counted at the ticker, surfaced in the opaque-fund chip.
• ETFs not yet in the catalog — the etf_catalog table is the backfill list for fund-to-CIK joins. A newly-launched ETF, or an obscure niche fund that hasn’t been ingested, will route through the same opaque path until the catalog catches up.

None of these cases are a bug; they are coverage gaps with named causes. The coverage_pct caption on the panel reflects exactly how much of your portfolio we successfully expanded versus the unexpanded remainder.

3. Why leveraged and inverse ETFs aren’t expanded

Leveraged ETFs (TQQQ, UPRO, SOXL, …) and inverse ETFs (SQQQ, SDS, SOXS, UVXY, …) are flagged on the panel and deliberately not expanded into the underlying index. The reason is the daily-reset structure of these products: a 3× levered ETF holds swap contracts whose payoff resets every trading day, so the headline ticker’s economic exposure is not the same shape as three times the underlying index’s holdings. Multiplying your position weight in TQQQ by QQQ’s underlying weights would mis-state both gross and net exposure for the lookthrough purpose.

Conservative choice: the headline ticker is the more honest exposure picture for these products. The position weight is preserved in the panel’s totals; it just doesn’t blow up into 3× of QQQ. This choice is pinned by tests/test_portfolio_lookthrough_contract.py::test_levered_etf_is_flagged_not_expanded.

4. Coverage percentage

The coverage_pct caption below the bars reads, for example, "Lookthrough covers 85% of portfolio". The number is the fraction of your non-cash portfolio weight that was successfully expanded via ETF lookthrough; the remainder is direct stock holdings, opaque funds, and leveraged/inverse ETFs counted at the headline ticker.

The panel elevates the caption (gives it more visual weight and adds a clause about commodity and bond funds) when coverage drops below 75 percent — the threshold OWNER chose to flag commodity-heavy or bond-heavy portfolios where the expanded mix is a smaller share of the whole. Above 75 percent, the caption is the bare percentage; this is the typical retail-equity case and does not need elevation.

Cash positions are excluded from the denominator and surfaced separately as a cash_weight field, consistent with how the Concentration (HHI) card handles cash. Exposure is a "risky position" metric; cash has no sector and no country, so reporting cash as "Unknown sector" would degrade the signal.

5. Top-N rollup and the "Other" drill-down

Each bar shows the top sectors or countries by weight; everything below the cutoff rolls up into an "Other" segment (always pinned to the rightmost position, regardless of its weight, so the eye finds it consistently across portfolios). The cutoff is 8 entries on desktop, 5 on tablet, and 4 on mobile — chosen because more segments at a given screen width drop below the readability floor for a hover-less touch surface.

Click the "Other" segment to drill into the rolled-up entries with their individual weights, sorted largest first. Click "Other" again or click outside the bar to collapse. The full enumeration is one tap away; honesty is preserved while the headline composition stays scannable.

6. Overlapping ETFs

If you hold VTI and VOO together (roughly 85 percent of holdings in common), the panel correctly sums your exposure to a shared underlying: Apple inside VTI contributes VTI_weight × VTI_AAPL_pct, Apple inside VOO contributes VOO_weight × VOO_AAPL_pct, and your true Apple exposure is the sum. This is the desired behavior — not double-counting — and it is pinned by test_overlapping_etfs_sum_correctly in the contract test. The top-underlying-holdings list shows Apple once with the summed weight; if it ever shows up twice, that is a backend regression and the panel surfaces it as a visual anomaly.

7. How accurate is this?

The two main sources of inaccuracy are staleness and resolution. 13F filings are quarterly and due within forty-five days of the period end, so the underlying snapshot for any ETF may be up to four months out of date during the worst stretch of the cycle. CUSIP resolution — mapping a 13F holding to a stock ticker — is correct for the vast majority of holdings but occasionally drops a row (CHAOS audit Track #1, on the post-launch QA list); when it does, that row is silently absent from the expansion rather than wrongly attributed. Bond and commodity coverage is the third source: those are surfaced honestly through the opaque-fund chip and the coverage percentage rather than papered over.

Coverage health across the entire ETF catalog is observable via tools/etf_coverage_scoreboard.py, a read-only CLI diagnostic that emits a distribution histogram of per-ETF resolution rates, the top-ranked CUSIPs that fail ticker resolution across multiple funds, and the worst-N ETFs by coverage. The tool is intended for periodic cron consumption (--json output) and ad-hoc QA. The honest framing for the panel itself is: the math is correct, the inputs are best-available public data, and the coverage percentage is the load-bearing honesty signal you should read alongside the bars.

8. Tier gating

The ETF Lookthrough panel is available on the Plus tier and above. The PostgreSQL joins on fund_holdings_snapshot scale with portfolio size; Free-tier users see a paywall card with a one-click upgrade. The route is gated by require_min_tier("plus") on the backend and the FE renders a paywall placeholder when the route returns 402. The tier choice mirrors the rest of the compute-heavy portfolio analytics surfaces.

9. What we deliberately do not do

• No "diversification score" on top of the bars. The panel shows the composition; it does not collapse the composition into a 1-to-10 conviction number. The Concentration (HHI) card already encodes a numerical concentration measure; layering a second one on the same Risk tab would be redundant.
• No advice or recommendation. The panel does not say "you should diversify more" or "your tech weight is too high." Those are judgments that depend on your goals, time horizon, and risk tolerance — not on the data alone.
• No expansion of leveraged or inverse ETFs. Documented in section 3; preserved at the headline ticker.
• No 100-row enumerations on the bars themselves. The top-N rollup keeps each bar scannable; the "Other" drill-down is the disclosure pattern for the long tail.

10. Changelog

Source code and references

Backend service: services/portfolio_lookthrough.py. Backend route: routes/routes_portfolio_fastapi/portfolio_state.py (api_portfolio_lookthrough). Backend typed helpers: pg_db/queries/portfolio_lookthrough.py. Frontend renderer: static/js/portfolio-lookthrough.ts. Contract tests: tests/test_portfolio_lookthrough_contract.py (backend math) and tests/test_portfolio_lookthrough_fe_contract.py (frontend wire shape + UX invariants).

This page mirrors the source files named above and is reviewed on the date shown at the top. How we keep every figure honest — the contract tests, freshness reviews, and public incident log behind the data — is documented in the Trust Dossier.

Corrections: oxfordledge@gmail.com.