Fund data for quant research: pandas, NAV series and exposure matrices

The problem

Researchers rarely lack ideas; they lack tidy inputs. A study of tracking behaviour across MSCI World ETFs needs an indexed NAV series for each share class, the benchmark returns per calendar year, the TER and the replication structure. Gathering those from a dozen factsheets and holdings files takes longer than the analysis and produces a one-off spreadsheet nobody can rerun.

Reproducibility is the second issue. A notebook that reads from files on someone's laptop cannot be re-executed six months later with fresh data.

What you need from the data

For a fund universe, three tables cover most studies:

• Attributes, one row per ISIN: asset class, region, structure, TER, AUM, inception, risk rating, volatility, Sharpe ratio, maximum drawdown
• Returns, long format: ISIN, year, fund return, benchmark return; plus cumulative returns per standard period
• Exposure, long format: ISIN, dimension (sector, geography, credit quality), label, weight

The indexed NAV series in indexedPerformance.points supports drawdown and rolling-window work directly, with the caveat that it is rebased to 100 and its frequency depends on what the fund house publishes.

How it works with FundFacts API

Pull each ISIN once into a local cache (a directory of JSON files is enough), then build DataFrames from the cache. The notebook stays reproducible and the API is only called when the cache has expired.

python
import json, os, pathlib, time
import httpx
import pandas as pd
​
CACHE = pathlib.Path(".fundfacts")
CACHE.mkdir(exist_ok=True)
CLIENT = httpx.Client(
    base_url="https://fundfactsapi.com/api/v1",
    headers={"Authorization": f"Bearer {os.environ['FUNDFACTS_API_KEY']}"},
    timeout=240,
)
​
def load(isin: str) -> dict:
    path = CACHE / f"{isin}.json"
    if path.exists():
        doc = json.loads(path.read_text())
        if pd.Timestamp(doc["expiresAt"]) > pd.Timestamp.now(tz="UTC"):
            return doc
    r = CLIENT.get(f"/funds/{isin}")
    if r.status_code == 429:
        time.sleep(int(r.headers.get("X-RateLimit-Reset", "60")))
        return load(isin)
    r.raise_for_status()
    path.write_text(r.text)
    return r.json()
​
universe = ["IE00B4L5Y983", "LU1681043599", "IE00BJ0KDQ92", "IE00B4X9L533"]
docs = {isin: load(isin) for isin in universe}
​
attrs = pd.DataFrame([
    {
        "isin": i,
        "name": d["name"],
        "structure": d["data"].get("structure"),
        "ter": (d["data"].get("headlineMetrics") or {}).get("ter"),
        "vol3y": (d["data"].get("headlineMetrics") or {}).get("volatility3y"),
        "maxDrawdown": (d["data"].get("metrics") or {}).get("maxDrawdown"),
        "dataAsOf": d["data"].get("dataAsOf"),
    }
    for i, d in docs.items()
]).set_index("isin")
​
returns = pd.concat([
    pd.DataFrame({
        "isin": i,
        "year": d["data"]["calendarReturns"]["years"],
        "fund": d["data"]["calendarReturns"]["fund"],
        "benchmark": d["data"]["calendarReturns"].get("benchmark"),
    })
    for i, d in docs.items() if d["data"].get("calendarReturns")
])
​
exposure = pd.concat([
    pd.DataFrame(d["data"].get("sector") or []).assign(isin=i)
    for i, d in docs.items()
]).pivot_table(index="isin", columns="label", values="weight", fill_value=0)

exposure is now an ISIN-by-sector matrix ready for distance measures or clustering, and returns is tidy for groupby work. Subtracting benchmark from fund per year gives a calendar-year tracking difference for index funds.

Design notes

Frequency and length. Indexed series come from the documents the fund house publishes, so frequency (daily, weekly, monthly) and history length vary by fund. Check the number of points and the date spacing before computing statistics that assume a fixed frequency, and record both in your attributes table.

Caching. Data refreshes every 24 hours; the expiresAt check above means re-running a notebook the same day costs zero requests. Keep the cache directory in a research bucket rather than in git, and note the dataAsOf range in the write-up.

Quota. A Free key gives 10 requests per day, enough to prototype on a handful of ISINs. For a universe in the hundreds, use Pro or Scale and build the cache over a few sessions. Handle 429 by sleeping until X-RateLimit-Reset, as above, and treat 502 upstream_error as retry-later rather than as a missing fund.

Missing data. Active funds often lack a benchmark series or a full sector table. Use NaN, not zero, and report coverage per column so readers can judge the sample.

Disclaimers. Research output is not investment advice, and figures derived from published documents inherit those documents' as-of dates. The performance data guide explains what each series measures; see also volatility and maximum drawdown.