ETF Data in Excel: Live Prices, AUM, and Expense Ratios for May 2026

ETF data in Excel is the workflow most allocators keep coming back to whenever they need to compare more than two or three funds at once. ETF web pages are designed for browsing one product at a time, not for sorting twenty-five funds by expense ratio against one-year return, or for blending three model portfolios from a single yellow input cell. Excel handles that comfortably, but only if the data flowing into the workbook is live. This guide explains how to pull current ETF data into Excel with MarketXLS, which fields actually move an allocation decision, and how to assemble a 25-ETF screener that ranks every fund on a composite Cost-Quality Score. A live-formula template and a static sample workbook with data as of May 13, 2026 are linked further down so you can open them side by side while you read.

ETF Data in Excel: The Fields That Matter (At a Glance)

Most ETF research narrows to a small set of fields. The table below shows the columns the rest of this guide is built around, why each one matters, and the MarketXLS function that pulls it into Excel.

Every cell that follows is a live MarketXLS formula in the template version of the workbook. The static sample workbook is pre-filled with snapshot values from May 13, 2026 so the model is fully readable even without a MarketXLS session running.

Why Pull ETF Data Into Excel At All

The honest answer is that fund web pages were built for end customers, not allocators. They display one fund at a time, with attribution and disclosure layouts that vary by issuer. Comparing twenty funds means twenty browser tabs and a lot of manual copying. The values you copy go stale within hours, and the moment you blend three of those funds into a model portfolio you no longer have a single source of truth.

Excel solves a different shape of problem. One sheet can hold every ETF in a watchlist, every metric per fund, every weight assigned to a model, and every derived calculation that ties weights and metrics together. The friction is not the spreadsheet. The friction is getting current data into it. MarketXLS removes that friction by exposing the underlying data feed as Excel functions that recalculate when the workbook opens, when the user presses F9, and on a configurable refresh schedule.

The shift in workflow is real. Once ETF data lives in Excel, the next layer of analysis is trivial: a screener that excludes funds above a 0.25% expense ratio, a model portfolio that sizes thirteen ETFs by a target weight column, a Cost-Quality Score that combines expense and trailing performance, a holdings drilldown that pulls the top constituents of any fund in a single cell. None of those require copying anything from a website.

The 25-ETF Universe in the Workbook

The dashboard is wired to 25 liquid, well-known ETFs that span the major asset class buckets. The mix is deliberately diversified, not curated for a single thesis.

Column A on the Main Dashboard sheet is the ticker. Every other column in the template version is a live MarketXLS call. If you want a different universe, replace the tickers in column A and the rest of the row recalculates. The only sheet that needs a manual update when you change tickers is the Allocation Builder, because the model portfolios reference specific symbols.

What Each Field Tells You

A short walkthrough of the columns matters here, because allocators read these in a specific order.

Price anchors share counts. A 250,000 dollar allocation to a fund trading at 738 dollars sizes very differently than the same allocation to a fund trading at 84 dollars. The =QM_Last("SPY") formula pulls the last traded price during market hours and the prior close after the bell.

AUM is the first liquidity proxy. Below roughly one billion in net assets, an ETF tends to have wider bid-ask spreads and shallower order books. The dashboard input cell sets a minimum AUM threshold so you can flag funds that fail it.

Expense ratio is the drag on long-term compounding. The difference between a 0.03% and a 0.50% expense ratio compounded over twenty years is meaningful. The Cost vs Performance sheet rewards low expense ratios explicitly.

Dividend yield matters when the workbook is being used for income work. Bond ETFs and dividend-focused funds carry the highest values in the table. Sector and growth ETFs sit near the bottom.

YTD price change and YTD total return are the two return columns. Price change excludes distributions. Total return includes them. The gap between the two is the year-to-date yield drag.

Thirty-day annualized volatility is the realized risk reading. Long Treasuries and gold can swing harder than the headline equity index funds during macro events. The dashboard input includes a maximum volatility threshold.

Fifty-two-week high and low mark the price range. Knowing where a fund sits inside that range matters when sizing a new position.

Average daily volume is the second liquidity proxy. Net assets and volume together determine the cost of getting in and out.

Beta measures sensitivity to the broader market. A defensive sector ETF often runs below 1. A growth or small-cap ETF often runs above 1. Bond ETFs are usually near zero.

The Cost-Quality Score

The dashboard adds a composite score in column O that ranks every fund on four equally weighted dimensions. The formula sits in the cell so the math is fully auditable.

The four components each contribute 25 points:

1. Expense efficiency. A fund at 0% expense ratio earns the full 25. A fund at 0.50% or higher earns zero.
2. One-year total return. A fund up 20% or more earns the full 25. A fund down 10% or worse earns zero.
3. Asset size. A fund with 50 billion or more in net assets earns the full 25. A fund with under one billion earns zero.
4. Risk control. A fund with 30-day volatility at 10% or lower earns the full 25. A fund at 40% or higher earns zero.

The result is a 0-100 composite. A fund scoring 70 or above gets a "Strong" verdict, 50-69 is "Solid", 30-49 is "Watch", and below 30 is "Weak". These verdicts are not buy or sell signals. They are a way to filter twenty-five funds down to a shortlist before manual review.

The exact formula used in the template, for the row at SPY, is:

=ROUND(
  MAX(0,MIN(25,25*(1-F13/0.5)))
  + MAX(0,MIN(25,25*((I13+10)/30)))
  + MAX(0,MIN(25,25*(E13/50000000000)))
  + MAX(0,MIN(25,25*((40-J13)/30))),
0)

Every component is bounded between 0 and 25 by MAX(0,MIN(25,...)). The four bounded values sum to the final 0-100 score. Adjust the divisors if your house view weights the dimensions differently.

The Cost vs Performance Sheet

The second sheet pulls the expense ratio and one-year return columns from the Main Dashboard and rebuilds them as two 50-point sub-scores. The Cost Score punishes any expense ratio above 0.50% on a linear scale. The Return Score rewards trailing one-year return, capped at 30%. The composite is the sum, ranged 0-100.

This sheet exists because allocators routinely run into the trap of buying the cheapest fund regardless of what it has delivered, or chasing the highest one-year return regardless of fee load. Splitting cost and return into separate sub-scores forces both to clear a bar before the composite climbs.

A composite of 70 or higher on this sheet means the fund is inexpensive and has been delivering. A composite below 30 means either fees are eating the return or the fund has lagged. Either way, it is a signal to dig deeper, not a recommendation.

The Allocation Builder

The Allocation Builder sheet holds three model portfolios sized off the portfolio size input cell on the Main Dashboard.

Conservative. Roughly 45% in fixed income and gold, 30% defensive equity (SCHD, VTV, XLP, XLU), and 25% across broad equity, international, and a small growth tilt. Designed for capital preservation. The blended yield is typically the highest of the three models.

Balanced. A 60% equity, 30% bond, 10% alternative split. Includes broad US (SPY, VTI), growth (QQQ), international (VXUS, EFA), three sectors (XLK, XLV, XLF), and gold. Designed for a long-horizon investor who wants reasonable participation in upside without full equity volatility.

Aggressive. Roughly 95% equity weighted toward growth and small caps (QQQ, VUG, IWM), 15% emerging and developed international (VWO, VXUS), and five sector tilts (XLK, XLY, XLE, XLF, XLI). Designed for the highest expected volatility tier in the table.

Each row in the sheet shows the model name, the ticker, its weight, the dollar allocation derived from the portfolio size input, the live yield pulled from the Main Dashboard, the projected annual income from that position, the live expense ratio, and the weighted fund fee in dollars. The total row at the bottom of each block aggregates everything and surfaces the blended yield and blended expense ratio of the model portfolio.

The same dollar input can run all three models simultaneously, which makes the cost of risk explicit. The Aggressive model carries the highest expense load and the highest expected vol, while the Conservative model often shows the highest projected income because of the bond and dividend weights.

These are illustrative model portfolios. They are educational and are not a recommendation to allocate capital in any specific way. Personal rebalancing schedules, tax considerations, and risk tolerance belong to the investor, not the workbook.

The Holdings Drilldown Sheet

The fourth sheet contains a single yellow input cell that accepts any ETF ticker. When a ticker is typed in, five live formulas update:

=FundFamily(B4)
=FundInceptionDate(B4)
=FundNetAssets(B4)
=FundExpenseRatio(B4)
=FundHoldings(B4)

=FundHoldings(B4) is the one that does the heavy lifting. It returns the top constituents of the fund as a spilled range, so a single cell becomes a table.

The reason this sheet exists is that overlap is the single biggest hidden risk in an ETF portfolio. Holding SPY and VOO is essentially holding the S&P 500 twice. Holding SPY and QQQ adds growth tilt because both top-out at Apple, Microsoft, Nvidia, Amazon, and Meta. Holding SCHD and VTV introduces dividend and value overlap. The Holdings Drilldown lets you check what is actually inside any fund before adding it to a model.

In the sample workbook, the drilldown is shown for SPY with its real top ten positions and weights. In the template workbook, the cell is wired so you can swap to any ticker and see live holdings.

The Asset Class Rollup

The final sheet pivots the Main Dashboard into average values by asset class. The Equity bucket aggregates the broad US, factor, and dividend funds. The Sector bucket aggregates the eleven SPDR sectors. International, Bond, and Commodity each cover their named funds. The pivot is built on AVERAGEIFS against the asset class column, so adding a fund to the dashboard automatically updates the rollup.

Three observations tend to fall out of this sheet:

First, broad equity has the lowest average expense ratio in the table. A US total market or large-cap index fund priced at three to ten basis points is a hard floor.

Second, sector ETFs cluster between roughly 0.10 and 0.40% expense, with a wider volatility band than broad equity. Energy sector funds in particular swing harder than the headline index because they concentrate exposure.

Third, bonds carry the highest aggregate yield in the table, but long-duration bond ETFs (TLT) move sharply on rate expectations. Aggregate bond funds (AGG) carry less duration and more credit diversification.

The Cost-Quality Score by asset class is also useful as a top-down check. If the Sector bucket consistently scores lower than the broad equity bucket, that signals the active selection trade-off of buying a thematic sector versus a market-cap-weighted index.

MarketXLS Implementation in Detail

Every formula referenced above is a real MarketXLS function. The list is short enough to memorize. The most useful calls for ETF research are:

=QM_Last("SPY")                    -- live last price
=Last("SPY")                       -- alias
=FundNetAssets("SPY")              -- AUM
=FundTotalAssets("SPY")            -- total assets
=FundExpenseRatio("SPY")           -- annual expense ratio
=FundFamily("SPY")                 -- issuer name
=FundInceptionDate("SPY")          -- launch date
=FundHoldings("SPY")               -- top holdings (spill range)
=FundReportPeriodEndDate("SPY")    -- most recent report period
=DividendYield("SPY")              -- trailing yield %
=ChangePercentYTD("SPY")           -- YTD price change %
=StockReturnYTD("SPY")             -- YTD total return %
=StockVolatilityThirtyDays("SPY")  -- annualized 30-day vol
=StockVolatilityOneYear("SPY")     -- annualized 1-year vol
=FiftyTwoWeekHigh("SPY")           -- 52-week high
=FiftyTwoWeekLow("SPY")            -- 52-week low
=AverageDailyVolume("SPY")         -- average daily share volume
=Beta("SPY")                       -- beta vs market
=SimpleMovingAverage("SPY", 50)    -- 50-day SMA
=SimpleMovingAverage("SPY", 200)   -- 200-day SMA
=RSI("SPY")                        -- 14-day RSI
=MarketCapitalization("SPY")       -- market cap
=Sector("SPY")                     -- sector classification
=Industry("SPY")                   -- industry classification
=QM_GetHistory("SPY")              -- historical price series
=Stream_Last("SPY")                -- streaming last price

These are the workhorses for ETF research. None of them require a separate data subscription. The same functions work for individual equities, so the same workbook layout is reusable for a stock-focused screener with minor relabeling.

Building Your Own ETF Dashboard From Scratch

If you want to recreate the dashboard from a blank workbook rather than downloading the template, the build order is:

1. Tickers in column A. Type or paste the ticker symbols. Keep them in upper case.
2. Static descriptors in columns B and C. Category and Asset Class are typed values, not formulas. They power the rollup sheet later.
3. Live data columns. Wire =QM_Last(A2), =FundNetAssets(A2), =FundExpenseRatio(A2), =DividendYield(A2), =ChangePercentYTD(A2), =StockReturnYTD(A2), =StockVolatilityThirtyDays(A2), =FiftyTwoWeekHigh(A2), =FiftyTwoWeekLow(A2), =AverageDailyVolume(A2), =Beta(A2) across the row.
4. Input cells. Above the table, place yellow-filled input cells for portfolio size, target yield, maximum expense ratio, minimum AUM, and maximum 30-day volatility. These flow into the downstream sheets.
5. Composite score. Build the four-component Cost-Quality Score as a single ROUND formula. Each component is a MAX(0,MIN(25,...)) bounded transform of the underlying column.
6. Conditional formatting. Apply a red-yellow-green color scale to the score column. The visual saves time later.
7. Asset class rollup. Use AVERAGEIFS against the Asset Class column on the dashboard to build per-bucket averages.
8. Allocation builder. Create one block per model with ticker, weight, and allocation formulas that reference the portfolio size input.

Repeat for as many funds as you want to compare. The workbook scales to several hundred tickers without performance issues, although the dashboard typically sits at twenty-five to fifty for readability.

Common Failure Modes to Avoid

A few patterns trip up new spreadsheets often enough to call them out.

Hardcoding expense ratios. Issuers occasionally update expense ratios, and a number typed in 2024 will not reflect a 2026 change. =FundExpenseRatio("SPY") updates automatically.

Mixing total return with price return. YTD return columns must be consistent across the table. The template uses =StockReturnYTD everywhere so dividends are included.

Ignoring AUM. Sub-billion-AUM funds can have wider spreads than they advertise. Apply the minimum AUM filter on the dashboard.

Comparing apples to oranges in the rollup. A bond ETF and an equity ETF have very different volatility ranges. The Asset Class column exists to keep the rollup honest.

Treating the Cost-Quality Score as a recommendation. It is a sorting aid. Funds with a Strong verdict still need a manual look at the holdings, the issuer, and the strategy.

Download the Templates

The full workbook is available in both pre-filled and live-formula versions.

Download the templates:

• - Pre-filled with current data as of May 13, 2026
• - Live-updating formulas

Both workbooks include six sheets: How To Use, Main Dashboard, Cost vs Performance, Allocation Builder, Holdings Drilldown, and Asset Class Rollup. Every sheet carries a MarketXLS Functions Used box at the bottom listing the exact formulas in use.

FAQ

Which MarketXLS function returns an ETF's expense ratio?

=FundExpenseRatio("SPY") returns the annual expense ratio for the fund. The cell can be formatted with 0.000"%" so the value displays with a percentage suffix.

Can MarketXLS pull the top holdings of an ETF into Excel?

Yes. =FundHoldings("SPY") returns the top constituents of the fund as a spilled range. Drop it into a single cell and the surrounding cells fill with the holdings table.

Does MarketXLS support live ETF prices during market hours?

=QM_Last("SPY") returns the last traded price. During market hours it updates on Excel recalculation. =Stream_Last("SPY") provides streaming last price updates. After the close, both fall back to the closing print.

How do I screen ETFs by expense ratio in Excel?

On the Main Dashboard, the input cell for Max expense ratio holds the threshold. Build a helper column like =IF(F13<=$B$7, "Passes", "Fails") to flag rows. For a sorted view, copy the table to a new sheet and use SORT or apply Excel's native data filter on the expense ratio column.

Can the same workbook work for stocks instead of ETFs?

Yes. The fund-specific functions (FundNetAssets, FundExpenseRatio, FundFamily, FundHoldings) only return values for ETFs and mutual funds. The other functions (QM_Last, DividendYield, Beta, etc.) work for both stocks and ETFs. Swap the tickers in column A and the dashboard recalculates.

Does the dashboard update automatically?

When the workbook is open in Excel with the MarketXLS add-in active, formulas refresh on recalculation. The MarketXLS ribbon has refresh controls that can pull the entire workbook on demand or on a schedule.

Is this an ETF recommendation list?

No. The 25 funds in the workbook were chosen for diversification across asset classes and broad recognition. The Cost-Quality Score is a sorting aid, not a buy or sell signal. Educational use only.

The Bottom Line

ETF data in Excel changes the shape of allocation research. Instead of comparing three funds across three browser tabs, the entire universe fits on one sheet, and every metric is a live formula away. The MarketXLS function set covers the fields that actually matter for ETF work: price, AUM, expense ratio, yield, returns, volatility, range, volume, beta, and top holdings. A six-sheet workbook is enough to handle a 25-fund screener, a cost-versus-performance ranking, three model portfolios, a holdings drilldown, and an asset class rollup, all from one input section.

The static sample workbook and the live-formula template are both linked above. Open the sample first to read the layout, then open the template inside Excel with MarketXLS active to see the same model fed by live data. From there, replacing the universe, the input thresholds, or the scoring weights is a matter of editing a single column.

For a wider view of the MarketXLS function set, the features page lists the major function categories. If you would like a walkthrough of the ETF workflow with one of our team members, you can book a demo.