Where to Get Trailing Returns for Every US Fund and ETF in Bulk

​

The options at a glance

​

Licensed data: Morningstar and CRSP

​

Free: the SEC’s prospectus data

• Once a year. The table is as of the last calendar year end, updated when the fund files its annual prospectus. In November it can be ten months old.
• Three periods. There’s no year-to-date, 3-year or 3-month figure.
• Sales charges. For share classes with a front-end load, the table’s returns are after the maximum sales charge, so they’ll be lower than a load-waived return.
• Identifiers. Rows are keyed by the SEC’s series and class IDs. You have to map them to tickers to join your other data.

​

Free: the SEC’s N-PORT monthly returns

-- Example against your own table: monthly_returns(class_id, month, monthly_return).
-- N-PORT reports returns as percentages (1.23 for 1.23%), so divide by 100 when loading.
select
    class_id,
    exp(sum(ln(1 + monthly_return))) - 1 as return_1y
from monthly_returns
where month > dateadd('month', -12, (select max(month) from monthly_returns))
group by class_id
having count(*) = 12;

• Months behind, not days. Only the quarter-end filing is public, and it appears 60 days after the quarter ends. The SEC then publishes the data sets quarterly, so the latest month can be three to five months old.
• Short history. The data starts in late 2019, so there are no 10-year returns until late 2029.
• Class IDs, not tickers. Returns are keyed by the SEC’s share class ID, so you map each class to its ticker before joining other data.
• Month-end only. There’s no year-to-date or 1-week figure between month ends.

​

Free but unofficial: yfinance

import pandas as pd
import yfinance as yf

# Adjusted closes (dividends and splits applied) for each fund, one column per ticker.
prices = yf.download(["VTI", "VFIAX", "SCHD"], period="5y", auto_adjust=True)["Close"]

as_of = prices.index[-1]
return_1y = prices.iloc[-1] / prices.asof(as_of - pd.DateOffset(years=1)) - 1

• Throttling. Yahoo doesn’t publish rate limits, and it throttles by IP address. Pull tens of thousands of tickers and requests start failing with “Too Many Requests” (YFRateLimitError). Users report blocks lasting hours, sometimes more than a day. A full backfill means batching, caching and pacing requests over days.
• Yahoo’s quirks. yfinance isn’t an official API. It reads Yahoo’s website endpoints, which change without notice, and each change breaks the library until a new release ships. Data quirks show up too: missing or repeated days, and adjusted history that shifts between pulls.
• No fund list. There’s no call for “every US mutual fund”. You need your own list of ~32,000 tickers and have to keep it current as funds launch, close and merge.
• Personal use only. yfinance says it isn’t affiliated with Yahoo and that Yahoo’s API is intended for personal use only. That rules it out for client work or a shared company warehouse.

​

Calculating trailing returns yourself

cumulative = end_adjusted_close / base_adjusted_close - 1
annualized = (end_adjusted_close / base_adjusted_close) ^ (365.25 / days) - 1

with px as (
    select code, date, adjusted_close
    from security_price_history_vw
    where exchange_code = 'US'
      and code in ('VTI', 'VFIAX', 'SCHD')
),
latest as (
    select code, max(date) as as_of from px group by code
),
windows as (
    select code, as_of, '1y' as period, dateadd('year', -1, as_of) as window_start from latest
    union all
    select code, as_of, '5y', dateadd('year', -5, as_of) from latest
)
select
    w.code,
    w.period,
    b.date as base_date,
    case
        when datediff('day', b.date, w.as_of) >= 730
            then power(e.adjusted_close / b.adjusted_close, 365.25 / datediff('day', b.date, w.as_of)) - 1
        else e.adjusted_close / b.adjusted_close - 1
    end as trailing_return
from windows w
asof join px b
    match_condition (w.window_start >= b.date)
    on w.code = b.code
join px e
    on e.code = w.code and e.date = w.as_of
order by w.code, w.period;

• Adjusted prices change. Every dividend restates a fund’s entire adjusted history. Mutual funds that pay monthly restate it monthly, so stored returns go stale unless you recompute from a full reload. See Adjusted Close vs. Close for why.
• Funds younger than the window. A fund launched 3 years ago has no 5-year return. Without a check, the ASOF JOIN finds no base price and the row quietly drops out.
• Thinly traded funds. When a fund doesn’t trade for a few days, the base price comes from an earlier date than the window implies. Keep base_date next to the return so it’s visible.
• When to annualize. By convention, periods under a year are never annualized. Annualizing a 3-month return turns a good quarter into an implausible annual figure.
• NAV vs. market price. For mutual funds, the daily price is the NAV. For ETFs it’s the market close, so the return is a market-price return. An ETF’s fact sheet usually shows both, and they differ slightly.
• Delisted funds. Funds close and merge every month. If your fund list only has today’s funds, any history you build has survivorship bias.

​

The ready-made version

select f.code, f.type, r.as_of_date,
       r.return_ytd, r.return_1y, r.return_5y, r.return_10y
from ext_fund_trailing_returns_vw r
join ext_fund_vw f
    on f.code = r.code
where r.code in ('VTI', 'VFIAX', 'VBTLX')
qualify row_number() over (partition by r.code order by r.as_of_date desc) = 1
order by r.code;

​

How close are the numbers?

​

Exporting the whole universe

create stage if not exists fund_exports;

copy into @fund_exports/trailing_returns/
from (
    select *
    from ext_fund_trailing_returns_vw
    qualify row_number() over (partition by code order by as_of_date desc) = 1
)
file_format = (type = csv compression = gzip)
header = true
overwrite = true;

​

Which should you use?

• You need the exact figures on a fund’s fact sheet: license Morningstar data.
• You’re doing academic research on fund performance: CRSP, through your institution.
• You need current trailing returns for the whole universe in SQL: a Marketplace listing such as Fund Returns.
• You want free data for the whole universe and can wait a few months: the SEC’s N-PORT monthly returns, compounded yourself.
• You’re running a personal project on a few dozen funds: yfinance, with caching, within Yahoo’s terms.
• You need a custom period or benchmark: calculate from adjusted prices, such as the Historical Stock Prices listing’s, and plan for the issues above.

​

F.A.Q.