Building a Portfolio Asset Exposure Analyzer: From ETF Holdings to True Risk Assessment
Introduction
Traditional portfolio tracking apps show you what you own, but they miss a crucial question: what are you actually exposed to? When you hold QQQ, TQQQ, and individual tech stocks, your real exposure to companies like Apple or Microsoft might be far higher than you realize.
This article explores building a portfolio exposure analyzer that goes beyond surface-level holdings to reveal your true economic risk exposure. We'll dive into the technical challenges, data requirements, and implementation strategies for creating a system that decomposes complex financial instruments into their underlying components.
Background: The Hidden Complexity of Modern Portfolios
The Problem with Traditional Portfolio Views
Most investment apps present your holdings as discrete line items:
- QQQ: $10,000
- TQQQ: $5,000
- AAPL: $2,000
But this view obscures the reality that you might have 4x exposure to Apple through these three positions combined. The economic substance differs dramatically from the nominal holdings.
Why This Matters
Consider a typical tech-heavy portfolio:
- QQQ (Nasdaq 100 ETF): Contains ~12% Apple, ~10% Microsoft
- TQQQ (3x Leveraged QQQ): Same components with 3x leverage
- Individual stocks: Direct exposure
Your actual Apple exposure could be:
Total AAPL Exposure =
Direct holding +
(QQQ position × AAPL weight in QQQ) +
(TQQQ position × AAPL weight × 3)
This aggregation reveals concentration risks invisible in traditional portfolio views.
Core Concepts: Decomposing Financial Instruments
The Asset Exposure Model
The foundation of our system is mapping all positions to a unified exposure format:
// Input: Various position types
Position {
asset_type: 'stock' | 'etf' | 'leveraged_etf'
ticker: string
market_value: number
leverage_factor: number // default = 1
}
// Output: Unified exposure representation
Exposure {
underlying_stock: string
exposure_value: number // position_value × component_weight × leverage
source_positions: Position[] // traceability
}ETF Decomposition Logic
Every position type flows through the same decomposition pipeline:
Individual Stocks → Direct 1:1 exposure
ETFs → Decompose using holdings data, leverage = 1x
Leveraged ETFs → Same decomposition, multiply by leverage factor
This creates a clean abstraction where all analysis operates on the unified Exposure layer.
Leverage Calculation Framework
We can provide multiple leverage metrics:
1. Nominal Leverage
Total Exposure Value / Account Net Worth
Shows overall leverage including derivatives and leveraged products.
2. Component-Weighted Leverage
Σ(Individual Stock Exposures) / Portfolio Value
Reveals hidden leverage from overlapping ETF holdings.
3. Position-Level Leverage Breakdown
Shows which positions contribute most to overall leverage, helping identify concentration risks.
Technical Analysis: Implementation Challenges and Solutions
Challenge 1: ETF Holdings Data Acquisition
The biggest technical hurdle is obtaining reliable, up-to-date ETF composition data.
Public Data Sources (No API Key Required)
SPY Holdings: State Street provides direct access to SPY holdings:
https://www.ssga.com/library-content/products/fund-data/etfs/us/holdings-daily-us-en-spy.xlsx
This Excel file contains complete daily holdings and can be fetched directly or downloaded manually.
QQQ and Other ETFs: More challenging due to CORS restrictions and varying publisher policies. Most require either:
- API keys (Finnhub, Financial Modeling Prep)
- Screen scraping (fragile)
- Manual file downloads
Recommended Hybrid Approach
For maximum reliability with minimal complexity:
- Auto-fetch when possible (SPY-style direct links)
- Manual import for others (drag-and-drop CSV/Excel files)
- Cache locally to minimize API calls
// Dual-mode data loading
async function loadETFHoldings(ticker) {
// Try direct fetch first
const directUrl = DIRECT_URLS[ticker];
if (directUrl) {
return await fetchAndParseHoldings(directUrl);
}
// Fallback to manual import
return await promptUserForFile(ticker);
}Challenge 2: Data Structure Design
The key to a maintainable system is clean data modeling:
// Core data structures
const Portfolio = {
positions: [
{
ticker: 'QQQ',
marketValue: 10000,
assetType: 'etf',
leverageFactor: 1
},
{
ticker: 'TQQQ',
marketValue: 5000,
assetType: 'leveraged_etf',
leverageFactor: 3
}
]
};
const ETFHoldings = {
'QQQ': [
{ ticker: 'AAPL', weight: 0.1234 },
{ ticker: 'MSFT', weight: 0.0987 }
// ... more holdings
]
};
// Generated exposure map
const Exposures = {
'AAPL': {
totalValue: 1234 + 617.5, // from QQQ + TQQQ
sources: ['QQQ', 'TQQQ'],
leverageBreakdown: { 'QQQ': 1, 'TQQQ': 3 }
}
};Challenge 3: Browser-Based Implementation
For a single-page HTML solution, we need to handle:
File Processing
// Handle drag-and-drop Excel/CSV files
function setupFileDropZone() {
const dropZone = document.getElementById('file-drop');
dropZone.addEventListener('drop', async (e) => {
e.preventDefault();
const files = Array.from(e.dataTransfer.files);
for (const file of files) {
if (file.name.includes('SPY')) {
const holdings = await parseExcelFile(file);
updateETFHoldings('SPY', holdings);
}
}
});
}Real-Time Calculation
function calculateExposures(portfolio, etfHoldings) {
const exposures = {};
portfolio.positions.forEach(position => {
if (position.assetType === 'stock') {
// Direct stock exposure
addExposure(exposures, position.ticker, position.marketValue, position);
} else {
// ETF decomposition
const holdings = etfHoldings[position.ticker];
holdings.forEach(holding => {
const exposureValue = position.marketValue * holding.weight * position.leverageFactor;
addExposure(exposures, holding.ticker, exposureValue, position);
});
}
});
return exposures;
}Analysis: Building Meaningful Insights
Key Metrics to Surface
1. Top Holdings by True Exposure
Show the largest positions after decomposition, not just the largest nominal holdings.
2. Concentration Risk Analysis
function calculateConcentration(exposures) {
const sorted = Object.entries(exposures)
.sort(([,a], [,b]) => b.totalValue - a.totalValue);
return {
top5Percent: sorted.slice(0, 5).reduce((sum, [,exp]) => sum + exp.totalValue, 0) / totalPortfolioValue,
top10Percent: sorted.slice(0, 10).reduce((sum, [,exp]) => sum + exp.totalValue, 0) / totalPortfolioValue,
herfindahlIndex: sorted.reduce((hhi, [,exp]) => {
const weight = exp.totalValue / totalPortfolioValue;
return hhi + (weight * weight);
}, 0)
};
}3. Leverage Attribution
Break down where leverage comes from:
- Direct leveraged products (TQQQ, SQQQ)
- Overlapping exposures (holding both QQQ and individual Nasdaq stocks)
- Options positions (future enhancement)
4. Sector/Theme Exposure
Group underlying stocks by sector to reveal thematic concentrations.
Visualization Strategies
Before/After Comparison: Show nominal holdings vs. true exposures side-by-side
Leverage Heatmap: Color-code positions by their contribution to overall leverage
Sankey Diagram: Visualize how ETF positions flow into underlying stock exposures
Implementation: MVP Architecture
Single-Page HTML Approach
For rapid prototyping and personal use, a browser-only solution offers several advantages:
<!DOCTYPE html>
<html>
<head>
<title>Portfolio Exposure Analyzer</title>
<script src="https://unpkg.com/xlsx/dist/xlsx.full.min.js"></script>
</head>
<body>
<div id="input-section">
<!-- Manual position entry -->
<div id="position-input">
<input type="text" placeholder="Ticker" id="ticker">
<input type="number" placeholder="Market Value" id="value">
<select id="type">
<option value="stock">Individual Stock</option>
<option value="etf">ETF</option>
<option value="leveraged_etf">Leveraged ETF</option>
</select>
<button onclick="addPosition()">Add Position</button>
</div>
<!-- File upload for ETF holdings -->
<div id="file-upload" ondrop="handleFileDrop(event)">
Drop ETF holdings files here
</div>
</div>
<div id="analysis-section">
<!-- Real-time exposure calculations -->
<div id="exposure-summary"></div>
<div id="concentration-metrics"></div>
<div id="leverage-analysis"></div>
</div>
</body>
</html>Core Calculation Engine
class PortfolioAnalyzer {
constructor() {
this.positions = [];
this.etfHoldings = {};
this.exposures = {};
}
addPosition(ticker, value, type, leverageFactor = 1) {
this.positions.push({ ticker, value, type, leverageFactor });
this.recalculateExposures();
}
updateETFHoldings(etfTicker, holdings) {
this.etfHoldings[etfTicker] = holdings;
this.recalculateExposures();
}
recalculateExposures() {
this.exposures = {};
this.positions.forEach(position => {
if (position.type === 'stock') {
this.addExposure(position.ticker, position.value, [position]);
} else {
const holdings = this.etfHoldings[position.ticker] || [];
holdings.forEach(holding => {
const exposureValue = position.value * holding.weight * position.leverageFactor;
this.addExposure(holding.ticker, exposureValue, [position]);
});
}
});
this.updateUI();
}
addExposure(ticker, value, sources) {
if (!this.exposures[ticker]) {
this.exposures[ticker] = { totalValue: 0, sources: [] };
}
this.exposures