To get ETF data into Excel, type a MarketXLS function in a cell: =QM_Last("SPY") returns the last price, =FundNetAssets("SPY") returns AUM, =FundExpenseRatio("SPY") returns the expense ratio, and =FundHoldings("SPY") spills the top holdings. Put tickers in column A and fill the formulas across, and you have a live ETF comparison table. On the Standard plan, US ETF prices are 15-minute delayed; real-time streaming needs the Advanced or Business plan. This guide lists the fields that matter for an allocation decision and walks through 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.
Try it in your own spreadsheet
Get the free MarketXLS sample workbook (.xlsx).
No account needed. MarketXLS live data requires a paid plan. Preview workbook
Explore more videos
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.
| Field | Why it matters | MarketXLS formula |
|---|---|---|
| Last price | Anchor for every position-sizing calculation | =QM_Last("SPY") |
| Net assets (AUM) | Liquidity proxy. Sub-billion AUM can mean wider spreads | =FundNetAssets("SPY") |
| Total assets | Larger reporting view of fund size | =FundTotalAssets("SPY") |
| Expense ratio | Drag on long-term returns | =FundExpenseRatio("SPY") |
| Dividend yield | Trailing 12-month income for the holdings | =DividendYield("SPY") |
| YTD price change | Year-to-date momentum check | =ChangePercentYTD("SPY") |
| YTD total return | YTD return including distributions | =StockReturnYTD("SPY") |
| 30-day volatility | Annualized realized volatility | =StockVolatilityThirtyDays("SPY") |
| One-year volatility | Smoother realized volatility view | =StockVolatilityOneYear("SPY") |
| 52-week high | Range upper bound | =FiftyTwoWeekHigh("SPY") |
| 52-week low | Range lower bound | =FiftyTwoWeekLow("SPY") |
| Average daily volume | Trading liquidity | =AverageDailyVolume("SPY") |
| Beta | Sensitivity vs broad market | =Beta("SPY") |
| Fund family | Issuer name | =FundFamily("SPY") |
| Inception date | Track record window | =FundInceptionDate("SPY") |
| Top holdings | Constituents for overlap checks | =FundHoldings("SPY") |
| Report period | Most recent reporting date | =FundReportPeriodEndDate("SPY") |
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
Excel lets you compare many ETFs on one sheet, which fund web pages do not. Issuer pages 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 exposes the data as Excel functions that recalculate when Excel recalculates (for example when you press F9) or when you use the refresh controls on the MarketXLS ribbon.
Once ETF data lives in Excel, the next layer of analysis takes ordinary Excel formulas: 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.
| Bucket | Tickers |
|---|---|
| US broad equity | SPY, IVV, VOO, VTI, QQQ, DIA, IWM |
| US factor | VTV (value), VUG (growth), SCHD (dividend) |
| US sector SPDRs | XLE, XLK, XLF, XLV, XLY, XLI, XLU, XLP |
| International equity | VXUS, EFA, VWO |
| Fixed income | AGG, TLT, HYG |
| Commodity | GLD |
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
Each dashboard column answers a specific question about a fund. The walkthrough below follows the order in which allocators usually read them.
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 returns the last traded price during market hours (15-minute delayed on the Standard plan) and the closing price 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:
- Expense efficiency. A fund at 0% expense ratio earns the full 25. A fund at 0.50% or higher earns zero.
- One-year total return. A fund up 20% or more earns the full 25. A fund down 10% or worse earns zero.
- Asset size. A fund with 50 billion or more in net assets earns the full 25. A fund with under one billion earns zero.
- 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)
Formula documentation: FundFamily, FundInceptionDate, FundNetAssets, FundExpenseRatio, FundHoldings
=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 Holdings Drilldown exists because overlap is a common 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 eight SPDR sector funds in the universe. 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
The MarketXLS functions used in this workbook are listed below. On the Microsoft 365 add-in (Excel for Mac and the web), add the mxls. prefix, for example =mxls.FundExpenseRatio("SPY"); some functions differ by platform, so check /formulas.
=QM_Last("SPY") -- live last price
=Last("SPY") -- last price
=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
=QM_Stream_Last("SPY") -- streaming last price
Formula documentation: QM_Last, Last, FundNetAssets, FundTotalAssets, FundExpenseRatio, FundFamily, FundInceptionDate, FundHoldings, FundReportPeriodEndDate, DividendYield, ChangePercentYTD, StockReturnYTD, StockVolatilityThirtyDays, StockVolatilityOneYear, FiftyTwoWeekHigh, FiftyTwoWeekLow, AverageDailyVolume, Beta, SimpleMovingAverage, RSI, MarketCapitalization, Sector, Industry, QM_GetHistory, QM_Stream_Last
All of these functions are part of a MarketXLS subscription. Plan level controls data speed: US stock and ETF quotes are 15-minute delayed on Standard and real-time streaming on Advanced and Business (see pricing). The same functions work for individual equities, so the same workbook layout works as a stock 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:
- Tickers in column A. Type or paste the ticker symbols. Keep them in upper case.
- Static descriptors in columns B and C. Category and Asset Class are typed values, not formulas. They power the rollup sheet later.
- 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. - 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.
- 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. - Conditional formatting. Apply a red-yellow-green color scale to the score column. The visual saves time later.
- Asset class rollup. Use
AVERAGEIFSagainst the Asset Class column on the dashboard to build per-bucket averages. - 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. Twenty-five to fifty funds keeps the dashboard readable.
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?
Yes, with plan limits. =QM_Last("SPY") returns the last traded price and updates when Excel recalculates; on the Standard plan it is 15-minute delayed. =QM_Stream_Last("SPY") updates automatically while streaming is on, and real-time streaming for US ETFs requires the Advanced or Business plan. After the close, both show the closing price.
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?
Yes, while the workbook is open in Excel with the MarketXLS add-in active. On-demand functions refresh when Excel recalculates or when you use the refresh controls on the MarketXLS ribbon, and QM_Stream_* functions update continuously while streaming is on.
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
With MarketXLS, an entire ETF universe fits on one Excel sheet and every metric is a formula. The functions cover the fields that matter most 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 the full function list, see the MarketXLS formulas page. For browser-based ETF research, the ETF screener and ETF overlap calculator cover screening and overlap checks. If you would like a walkthrough of the ETF workflow with one of our team members, you can book a demo.