ETF Analysis with AI: Compare Funds and Find the Best Investments
Learn how to analyze and compare ETFs using AI. Master expense ratio analysis, tracking error detection, sector exposure mapping, and factor decomposition.
Andrew Grosser
May 12, 2026 • 11 min read
ETF Analysis with AI: Compare Funds and Find the Best Investments
Learn how to analyze and compare ETFs using AI. Master expense ratio analysis, tracking error detection, sector exposure mapping, and factor decomposition.
You're comparing two S&P 500 ETFs: SPY (0.09% expense ratio) and VOO (0.03% expense ratio). Both track the same index, but over 20 years, that 0.06% difference costs you $12,000 on a $100,000 investment. Finding the right ETF isn't just about the index it tracks — it's about expense ratios, tracking error, sector tilts, and hidden factor exposures that can make or break your returns.
Sourcetable's AI data analyst is free to try. Sign up here.
ETF analysis requires comparing dozens of metrics across hundreds of funds. Manual analysis in Excel takes hours: downloading holdings data, calculating weighted averages, mapping sector exposures, and computing tracking differences. This guide shows you how to perform professional-grade ETF analysis manually, then demonstrates how AI completes the same work in seconds.
What Is ETF Analysis and Why It Matters
ETF analysis is the process of evaluating exchange-traded funds to identify which ones best match your investment goals. Unlike picking individual stocks, ETF selection focuses on cost efficiency, tracking accuracy, liquidity, and portfolio composition. A good ETF gives you broad market exposure at minimal cost with tight tracking to its benchmark index.
The core metrics that separate excellent ETFs from mediocre ones include expense ratio (annual fee as percentage of assets), tracking error (how closely returns match the index), bid-ask spread (trading cost), assets under management (liquidity indicator), and holdings overlap (diversification measure). For example, the difference between a 0.03% and 0.50% expense ratio on $200,000 invested for 30 years is $94,000 in lost returns at 7% annual growth.
How to Calculate and Compare Expense Ratios
Expense ratio is the annual fee charged by the fund, expressed as a percentage of your investment. If you invest $50,000 in an ETF with a 0.20% expense ratio, you pay $100 per year. This fee is automatically deducted from the fund's returns, so you never see it as a separate charge — but it compounds over decades.
To compare expense ratios manually, create a spreadsheet with columns for ETF ticker, expense ratio, your investment amount, and annual cost. The formula is: Annual Cost = Investment Amount × (Expense Ratio / 100). For a $100,000 portfolio split across three ETFs, calculate each fund's individual cost, then sum them. If you hold $40,000 in VTI (0.03%), $30,000 in VXUS (0.07%), and $30,000 in BND (0.03%), your total annual cost is $12 + $21 + $9 = $42.
| ETF | Expense Ratio | Investment | Annual Cost | 30-Year Cost at 7% |
|---|---|---|---|---|
| VTI | 0.03% | $40,000 | $12 | $912 |
| VXUS | 0.07% | $30,000 | $21 | $1,596 |
| BND | 0.03% | $30,000 | $9 | $684 |
| Total | — | $100,000 | $42 | $3,192 |
With Sourcetable, you upload your portfolio holdings and ask: 'Calculate total expense ratio cost for my portfolio over 30 years.' The AI pulls current expense ratios, applies compound growth formulas, and returns a breakdown by fund in seconds. No manual lookups, no spreadsheet formulas, no calculation errors.
Tracking Error: Measuring How Well an ETF Follows Its Index
Tracking error measures the standard deviation of the difference between an ETF's returns and its benchmark index returns. An ETF that perfectly tracks the S&P 500 would have zero tracking error. In reality, tracking error of 0.05% to 0.15% is excellent, 0.15% to 0.30% is acceptable, and above 0.50% indicates poor index replication.
To calculate tracking error manually, you need daily return data for both the ETF and its benchmark over at least one year (252 trading days). First, calculate daily returns: (Today's Price / Yesterday's Price) - 1. Then calculate the difference between ETF return and index return for each day. Finally, compute the standard deviation of these differences and annualize it by multiplying by the square root of 252.
Here's a worked example for five days of SPY tracking the S&P 500. Day 1: SPY returns 0.8%, S&P returns 0.82%, difference = -0.02%. Day 2: SPY returns -0.3%, S&P returns -0.28%, difference = -0.02%. Day 3: SPY returns 1.1%, S&P returns 1.15%, difference = -0.05%. Day 4: SPY returns 0.5%, S&P returns 0.48%, difference = 0.02%. Day 5: SPY returns -0.2%, S&P returns -0.22%, difference = 0.02%. The standard deviation of these differences is 0.027%. Annualized: 0.027% × √252 = 0.43%.
In Excel, this requires downloading 252+ days of price data from Yahoo Finance, creating return columns with formulas like =(B3/B2)-1, calculating difference columns, then using =STDEV.S() on the differences and multiplying by SQRT(252). This takes 30-45 minutes per ETF. With Sourcetable, you ask: 'Calculate tracking error for SPY, VOO, and IVV over the last year.' The AI fetches price data, runs the calculations, and displays a comparison table in under 10 seconds.
Sector Exposure Analysis: Finding Hidden Concentration Risk
Sector exposure shows what percentage of an ETF's assets are invested in each market sector: technology, healthcare, financials, consumer discretionary, industrials, energy, utilities, real estate, materials, consumer staples, and communication services. Even 'diversified' index funds can have dangerous concentrations. As of early 2026, the S&P 500 allocates roughly 30% to technology — if you hold multiple S&P 500 ETFs plus a tech-focused fund, you might have 50%+ tech exposure without realizing it.
To analyze sector exposure manually, download each ETF's holdings file (usually a CSV from the fund provider's website). Each holding has a ticker, number of shares, market value, and sector classification. Calculate each sector's weight: (Sum of Market Value for All Holdings in Sector) / (Total Portfolio Market Value) × 100. If an ETF holds $2 billion in tech stocks out of $10 billion total assets, tech exposure is 20%.
| Sector | SPY Weight | QQQ Weight | Combined (50/50) |
|---|---|---|---|
| Technology | 30.2% | 49.8% | 40.0% |
| Consumer Discretionary | 10.5% | 12.3% | 11.4% |
| Healthcare | 12.8% | 6.1% | 9.5% |
| Financials | 13.2% | 4.2% | 8.7% |
| Communication Services | 8.9% | 15.7% | 12.3% |
| Other | 24.4% | 11.9% | 18.2% |
This table shows that a 50/50 split between SPY and QQQ gives you 40% technology exposure — far higher than most investors realize. Manual sector analysis requires downloading holdings files for each ETF (often 500+ rows), mapping tickers to sectors using GICS classifications, creating pivot tables, and calculating weighted averages. For a five-ETF portfolio, expect 90+ minutes of work.
Sourcetable's AI connects directly to ETF data providers, pulls current holdings, maps sectors automatically, and generates exposure breakdowns on demand. Ask: 'Show me sector exposure for my portfolio and flag any sector over 25%.' The AI returns an interactive breakdown with visual alerts for concentration risk. What took 90 minutes now takes 15 seconds.
Factor Decomposition: Understanding What Drives Returns
Factor analysis breaks down an ETF's returns into systematic risk factors: market beta (overall market exposure), size (small-cap vs large-cap tilt), value (cheap vs expensive stocks), momentum (recent winners vs losers), quality (profitable, stable companies), and volatility (low-risk vs high-risk stocks). Understanding factor exposures helps you identify why an ETF outperforms or underperforms and whether those drivers align with your investment thesis.
The Fama-French three-factor model is the foundation. It regresses an ETF's excess returns (returns minus risk-free rate) against three factors: market excess return (Rm - Rf), size premium (SMB: small minus big), and value premium (HML: high book-to-market minus low). The regression equation is: ETF Return - Rf = α + β₁(Rm - Rf) + β₂(SMB) + β₃(HML) + ε. The beta coefficients reveal factor loadings.
To perform factor analysis manually, download 60 months of returns for your ETF, the market index (S&P 500), the risk-free rate (3-month Treasury), and the Fama-French factor data from Kenneth French's data library. Calculate excess returns for each month. Run a multiple regression in Excel using Data Analysis Toolpak or the LINEST function. For example, if your regression yields β₁ = 1.05, β₂ = 0.30, β₃ = -0.15, this means the ETF has slightly higher market sensitivity than the index, a moderate small-cap tilt, and a growth (anti-value) bias.
| ETF | Market Beta | Size Factor | Value Factor | Interpretation |
|---|---|---|---|---|
| SPY | 1.00 | -0.05 | -0.02 | Pure large-cap market exposure |
| VBR | 0.92 | 0.68 | 0.54 | Small-cap value fund |
| QQQ | 1.12 | -0.22 | -0.38 | Large-cap growth, tech-heavy |
| MTUM | 0.98 | -0.10 | -0.25 | Momentum tilt, growth bias |
This analysis reveals that QQQ has a strong anti-value (growth) tilt with beta of -0.38 on the value factor, while VBR loads heavily on both size (0.68) and value (0.54) factors. Running this analysis manually requires downloading data from multiple sources, cleaning inconsistent date formats, merging datasets, and running regressions — typically 2-3 hours per ETF.
With Sourcetable, you ask: 'Run Fama-French factor analysis on my ETF portfolio.' The AI pulls factor data, calculates regressions, and displays factor loadings with plain-English interpretations. It highlights factor overlaps (e.g., 'Your portfolio has 3 funds with strong growth tilts, creating concentration risk') and suggests rebalancing options. The entire analysis completes in under 30 seconds.
Liquidity and Trading Cost Analysis
Liquidity determines how easily you can buy or sell an ETF without moving the price. The two key metrics are average daily volume (number of shares traded per day) and bid-ask spread (difference between the highest buy price and lowest sell price). An ETF with 10 million shares traded daily and a $0.01 spread is highly liquid. One with 50,000 shares daily and a $0.15 spread costs you significantly more to trade.
Bid-ask spread represents your immediate trading cost. If SPY has a bid of $450.00 and ask of $450.02, the spread is $0.02 or 0.0044%. On a $100,000 purchase, you lose $4.40 to the spread. Compare this to a niche sector ETF with a $45.00 bid and $45.20 ask — a $0.20 spread or 0.44%. The same $100,000 trade costs you $440 in spread, 100 times more.
To analyze liquidity manually, check your broker's quote screen for real-time bid, ask, and volume. Calculate spread percentage: (Ask - Bid) / Ask × 100. Multiply by your trade size to estimate cost. For multiple ETFs, create a comparison table. This takes 5-10 minutes per fund and requires checking during market hours when spreads are representative.
Sourcetable pulls live quote data and calculates trading costs automatically. Ask: 'Compare bid-ask spreads for SPY, VOO, and IVV and show estimated cost to trade $50,000 of each.' The AI displays current spreads, calculates dollar costs, and ranks funds by trading efficiency. It updates in real-time during market hours, so you can time trades when spreads narrow.
Holdings Overlap: Avoiding Unintentional Duplication
Holdings overlap measures how many stocks appear in multiple ETFs in your portfolio. If you own both SPY (S&P 500) and VTI (total U.S. market), they share roughly 80% of holdings by weight — you're essentially doubling down on the same 500 large-cap stocks. High overlap reduces diversification and amplifies concentration risk.
To calculate overlap manually, download holdings files for each ETF. Create a master list of all unique tickers across your portfolio. For each ticker, sum its weight across all funds. If Apple (AAPL) is 6% of SPY and 5% of QQQ, and you hold them 50/50, your effective AAPL exposure is 5.5%. Repeat for every holding, then calculate what percentage of your portfolio consists of overlapping positions.
For example, if you hold $50,000 in SPY and $50,000 in VOO (both S&P 500 trackers), overlap is nearly 100% — you've effectively invested $100,000 in the same 500 stocks. This isn't diversification; it's redundancy. Manual overlap analysis requires cross-referencing thousands of holdings across multiple CSV files, using VLOOKUP or INDEX-MATCH formulas, and aggregating weights — typically 60-90 minutes for a three-fund portfolio.
Sourcetable's AI analyzes overlap instantly. Upload your ETF list and ask: 'Calculate holdings overlap and show me redundant positions.' The AI maps all holdings, identifies duplicates, calculates effective weights, and flags funds with >70% overlap. It suggests alternatives with lower correlation to improve diversification. The analysis that took 90 minutes runs in 20 seconds.
Tax Efficiency: Comparing Capital Gains Distributions
Tax efficiency measures how much of an ETF's returns you keep after taxes. ETFs are generally more tax-efficient than mutual funds due to their in-kind creation/redemption mechanism, but some ETFs still distribute capital gains annually. A fund that distributes 2% in capital gains forces you to pay taxes on money you never withdrew, reducing your after-tax return.
To compare tax efficiency, check each fund's distribution history (available on the provider's website). Look for annual capital gains distributions as a percentage of NAV. Funds with zero or near-zero distributions are most tax-efficient. Calculate after-tax return: Pre-Tax Return - (Capital Gains Distribution × Your Tax Rate). If an ETF returns 10% with a 1.5% capital gain distribution and you're in the 20% capital gains bracket, your after-tax return is 10% - (1.5% × 0.20) = 9.7%.
| ETF | 2025 Return | Capital Gains Dist. | After-Tax Return (20% bracket) |
|---|---|---|---|
| VTI | 12.5% | 0.0% | 12.5% |
| SPY | 12.3% | 0.1% | 12.28% |
| SCHD | 11.8% | 0.8% | 11.64% |
| QQQM | 15.2% | 0.0% | 15.2% |
This table shows VTI and QQQM are perfectly tax-efficient with zero capital gains distributions, while SCHD's 0.8% distribution reduces after-tax returns by 0.16%. Over 20 years, this difference compounds significantly. Manual tax analysis requires downloading distribution histories, calculating after-tax returns for multiple tax scenarios, and projecting long-term impacts — 45-60 minutes of work.
Sourcetable automates tax efficiency analysis. Ask: 'Compare after-tax returns for my ETF portfolio in the 24% tax bracket.' The AI pulls distribution histories, applies your tax rate, calculates after-tax returns, and shows the cumulative tax drag over 10, 20, and 30 years. It identifies which funds are bleeding returns to taxes and suggests more efficient alternatives.
Building an ETF Comparison Dashboard
A comprehensive ETF comparison dashboard consolidates all key metrics into one view: expense ratio, tracking error, sector exposure, factor loadings, liquidity, overlap, and tax efficiency. Professional analysts spend 4-6 hours building these dashboards in Excel, connecting to multiple data sources, writing complex formulas, and creating charts.
The manual process involves: (1) downloading data from 5+ sources (fund providers, Yahoo Finance, Fama-French library, broker platforms), (2) cleaning and standardizing formats, (3) building calculation sheets for each metric, (4) creating summary tables with conditional formatting, (5) generating charts for sector exposure and factor analysis, and (6) setting up refresh macros to update data. For a portfolio of 6-8 ETFs, this is a half-day project.
With Sourcetable, you describe what you want: 'Create a dashboard comparing VTI, VOO, SCHD, QQQ, and VBR across expense ratio, tracking error, sector exposure, and factor loadings.' The AI builds the entire dashboard in under 60 seconds. It pulls live data, runs all calculations, generates interactive charts, and formats everything for easy comparison. When new data arrives, the dashboard updates automatically — no manual refresh needed.
The AI also answers follow-up questions instantly. Ask: 'Which fund has the lowest total cost of ownership over 20 years?' or 'Show me which funds have the highest tech exposure' or 'Flag any funds with tracking error above 0.30%.' Each query returns answers in seconds, letting you explore your data conversationally instead of rebuilding formulas.
Real-World Example: Choosing Between S&P 500 ETFs
Let's compare the three largest S&P 500 ETFs: SPY (State Street), VOO (Vanguard), and IVV (iShares). All three track the same index, but they differ in cost, liquidity, and tracking precision. SPY charges 0.09%, VOO charges 0.03%, and IVV charges 0.03%. On a $200,000 investment over 25 years at 8% annual returns, SPY costs $5,400 in fees while VOO and IVV cost $1,800 each — a $3,600 difference.
Tracking error tells another story. SPY has a 1-year tracking error of 0.08%, VOO is 0.06%, and IVV is 0.07% (example figures). All three are excellent, but VOO edges slightly ahead. Liquidity favors SPY with 70 million shares traded daily versus 5 million for VOO and 4 million for IVV. SPY's bid-ask spread is $0.01 (0.0022%) while VOO and IVV are $0.02-0.03 (0.0044%-0.0066%).
The trade-off: SPY offers superior liquidity and tighter spreads, making it better for active traders and large institutional orders. VOO and IVV offer lower expense ratios, making them better for long-term buy-and-hold investors. For a $200,000 position held 20+ years with minimal trading, VOO saves $3,000+ in fees. For a $5 million position traded quarterly, SPY's tighter spreads save thousands per trade.
Running this comparison manually requires pulling data from multiple sources, building comparison tables, calculating long-term fee impacts, and evaluating trade-offs. With Sourcetable, ask: 'Compare SPY, VOO, and IVV for a $200,000 buy-and-hold portfolio over 20 years.' The AI returns a complete analysis with cost projections, tracking error comparison, liquidity metrics, and a recommendation based on your use case — all in 15 seconds.
Common ETF Analysis Mistakes to Avoid
The biggest mistake is choosing ETFs based solely on past performance. A fund that returned 25% last year might have taken excessive risk or benefited from temporary sector rotation. Performance chasing leads to buying high and selling low. Instead, focus on structural factors: low costs, tight tracking, diversified holdings, and alignment with your risk tolerance.
Another common error is ignoring holdings overlap. Investors often buy multiple 'diversified' funds that hold the same stocks. Owning SPY, QQQ, and VGT (tech sector) gives you 40%+ technology exposure — far from diversified. Always check overlap before adding new positions. A portfolio with 70%+ overlap across funds is essentially a concentrated bet disguised as diversification.
Underestimating expense ratio impact is the third major mistake. A 0.50% difference seems trivial, but over 30 years it costs 15% of your portfolio value due to compounding. On $500,000, that's $75,000 in lost returns. Always compare expense ratios within the same asset class and favor the lowest-cost option when tracking error and liquidity are comparable.
Finally, many investors neglect tax efficiency in taxable accounts. Holding tax-inefficient funds (those with frequent capital gains distributions) in taxable brokerage accounts instead of IRAs can cost 1-2% annually in after-tax returns. Place tax-inefficient funds in tax-advantaged accounts and keep tax-efficient index ETFs in taxable accounts.
What is the most important metric when comparing ETFs?
How much tracking error is acceptable for an index ETF?
Should I choose SPY or VOO for long-term investing?
How do I check if my ETFs have too much overlap?
What's the difference between sector exposure and factor exposure?
Are ETFs with higher expense ratios ever worth it?
How often should I rebalance my ETF portfolio?
Can Sourcetable analyze international and bond ETFs?
What's the best way to screen for tax-efficient ETFs?
How does Sourcetable get real-time ETF data?
Analyze Your ETFs with AI
Compare funds and find the best investments in seconds.
Sources
Research and data sources used in this article
- Fama, Eugene F., and Kenneth R. French. 'Common risk factors in the returns on stocks and bonds.' Journal of Financial Economics 33.1 (1993): 3-56.
- Morningstar Direct - ETF Holdings and Performance Data (2026)
- CBOE - ETF Trading Volume and Liquidity Metrics (2026)
- Vanguard - The Case for Index-Fund Investing (2025)
- BlackRock iShares - Understanding ETF Tracking Difference (2025)
- Kenneth French Data Library - Fama-French Factor Returns (2026)