ETF holdings in Excel and Google Sheets
The holdings of every ETF the API covers, the ETFs that hold a stock, the overlap of two funds and what a portfolio holds underneath load into a spreadsheet, each row dated with the holdings it comes from. Google Sheets takes six functions pasted once into the sheet, or one IMPORTDATA formula per file; Excel and Power BI take a Power Query query. There is nothing to install from a marketplace, and no key or account to ask for.
Six functions for Google Sheets
One Apps Script file adds six functions to a sheet, each named TI_ so that it meets no other. Each reads the API, keeps its answer for an hour, since the files change once a night, and writes a short message in the cell when it cannot answer, such as a ticker that is not covered.
- Open your sheet and choose Extensions, then Apps Script.
- Copy the script with the Copy button of tickerinside.gs below.
- Paste it in place of everything in the editor, then save with the disk icon or Ctrl+S.
- Go back to the sheet and type
=TI_HOLDINGS("VOO", 10)in a cell. As you type=TI_, Sheets lists the six functions with their help.
ShowHide the script, 670 lines
/**
* TickerInside for Google Sheets, version 1.0.0 (7 October 2026).
*
* Six functions that put US ETF holdings into cells, read from the free
* TickerInside API: TI_HOLDINGS, TI_HOLDERS, TI_OVERLAP, TI_OVERLAP_MATRIX,
* TI_LOOKTHROUGH and TI_FUND. Every row carries the date of the holdings it
* comes from. No key, no account, no add-on.
*
* Install: in your sheet, Extensions > Apps Script, replace the code there
* with this whole file, save, then type =TI_HOLDINGS("VOO") in a cell.
* Examples and recipes: https://tickerinside.com/spreadsheets/
*
* Data: TickerInside (https://tickerinside.com), CC BY 4.0. Where you share
* or publish a sheet built with it, credit it with this line:
* Data: TickerInside (https://tickerinside.com), CC BY 4.0
* Fund holdings remain the issuers' publications. The figures are
* measurements of what funds hold, not investment advice.
*
* Questions and corrections: contact@tickerinside.com
*/
var TI_BASE = "https://tickerinside.com";
// Every answer is kept for an hour: the files change once a night, so a
// sheet that recalculates all day still reads each answer at most once an
// hour, well within the daily UrlFetchApp quota of a Google account.
var TI_CACHE_SECONDS = 3600;
// CacheService keeps at most 100 KB per value. 30,000 characters stay under
// it even at 3 bytes a character, so a longer answer is kept in pieces.
var TI_PIECE = 30000;
// The prefix of every key this version keeps, so that a later version never
// reads an answer shaped by this one.
var TI_KEY = "ti1:";
// A ticker as the API takes one: BRK.B, 7203.JP, ^GSPC.
var TI_TICKER = /^[A-Z0-9.\-^=*&]{1,16}$/;
// Apps Script sends its own User-Agent (Google-Apps-Script) and does not let
// a script set another, so the requests carry no header of their own.
/**
* The holdings of a US ETF, heaviest first, with the date of the fund's holdings on every row.
*
* @param {string} ticker The fund's ticker, such as "VOO".
* @param {number} top How many holdings to list, heaviest first. All of them when left out or 0.
* @return The holdings under a header row: holding, name, weight_pct, holdings_as_of.
* @customfunction
*/
function TI_HOLDINGS(ticker, top) {
return ti_run_(function () {
var t = ti_ticker_(ticker, "ticker");
var n = ti_whole_(top, "top", 0, 100000, 0);
return ti_answer_("holdings:" + t + ":" + n, function () {
var doc = ti_get_("/api/v1/fund/{fund}.json", { fund: t });
var h = doc.holdings || {};
var rows = Object.keys(h).filter(function (k) { return typeof h[k] === "number"; })
.map(function (k) { return [k, h[k]]; })
.sort(function (a, b) { return b[1] - a[1] || (a[0] < b[0] ? -1 : 1); });
if (!rows.length) {
throw new TiError_(t + " lists no holdings in its file" + (doc.holdings_kind ? " (" + doc.holdings_kind + ")" : "")
+ ". =TI_FUND(\"" + t + "\", \"name\") and the other fields still answer.", true);
}
if (n) rows = rows.slice(0, n);
var names = ti_names_("securities");
var date = doc.holdings_as_of || "";
return [["holding", "name", "weight_pct", "holdings_as_of"]].concat(rows.map(function (r) {
return [r[0], names[r[0]] || "", r[1], date];
}));
});
});
}
/**
* The covered US ETFs that hold a stock, heaviest weight first, with the date and source of each fund's holdings.
*
* @param {string} stock The stock's ticker, such as "NVDA".
* @param {number} top How many funds to list. All of them when left out or 0.
* @return The funds under a header row: fund, name, weight_pct, holdings_as_of, source.
* @customfunction
*/
function TI_HOLDERS(stock, top) {
return ti_run_(function () {
var t = ti_ticker_(stock, "stock");
var n = ti_whole_(top, "top", 0, 6000, 0);
return ti_answer_("holders:" + t + ":" + n, function () {
var rows = ti_holders_screen_(t, n);
if (rows === null) rows = ti_holders_file_(t, n);
if (!rows.length) throw new TiError_("No covered fund holds " + t + ".", true);
return [["fund", "name", "weight_pct", "holdings_as_of", "source"]].concat(rows);
});
});
}
/**
* The overlap of two US ETFs: the weight they hold in common, the share of each held by the other, the companies in both, the correlation of their weekly returns over three years, and both holdings dates.
*
* @param {string} fund1 The first fund, such as "VOO".
* @param {string} fund2 The second fund, such as "QQQ".
* @param {string} field One figure alone, such as "by_weight". The whole table when left out.
* @return Two columns, each figure beside its name, or the one figure asked for.
* @customfunction
*/
function TI_OVERLAP(fund1, fund2, field) {
return ti_run_(function () {
var a = ti_ticker_(fund1, "fund1");
var b = ti_ticker_(fund2, "fund2");
if (a === b) throw new TiError_("fund1 and fund2 are the same fund; give two different tickers.");
var table = ti_answer_("overlap:" + a + "," + b, function () {
var doc = ti_get_("/api/v1/overlap", null, { funds: a + "," + b });
if (doc.comparable === false) {
throw new TiError_(a + " and " + b + " are not compared by holdings. " + ti_whys_(doc.not_comparable), true);
}
var shares = !!doc.overlap;
var o = doc.overlap || doc.issuer_overlap || {};
var dates = doc.holdings_as_of || {};
var src = doc.holdings_source || {};
return [
["fund_a", a], ["fund_b", b], ["overlap_kind", shares ? "shares" : "issuer"],
["by_weight", o.by_weight], ["share_of_a", o.share_of_a], ["share_of_b", o.share_of_b],
["in_both", shares ? o.companies_in_both : o.issuers_in_both], ["correlation_3y", doc.correlation_3y],
["holdings_as_of_a", dates[a]], ["holdings_as_of_b", dates[b]], ["source_a", src[a]], ["source_b", src[b]]
].map(function (r) { return [r[0], ti_cell_(r[1])]; });
});
if (ti_blank_(field)) return table;
var f = String(field).trim().toLowerCase();
for (var i = 0; i < table.length; i++) if (table[i][0] === f) return table[i][1];
throw new TiError_(String(field).slice(0, 40) + " is not a figure of TI_OVERLAP, which gives "
+ table.map(function (r) { return r[0]; }).join(", ") + ".");
});
}
/**
* The overlap by weight of every pair of up to 12 US ETFs, in percent, as a matrix, with each fund's holdings date under it.
*
* @param {Array} funds The cells holding the tickers, such as A2:A6.
* @return A matrix under a header row of the tickers, then a row of holdings dates.
* @customfunction
*/
function TI_OVERLAP_MATRIX(funds) {
return ti_run_(function () {
var list = ti_tickers_(funds, "funds");
if (list.length < 2) throw new TiError_("funds must name at least 2 different funds.");
if (list.length > TI_PAIRWISE_MAX) {
throw new TiError_("funds names " + list.length + " funds; one matrix takes at most " + TI_PAIRWISE_MAX + ".");
}
return ti_answer_("matrix:" + list.join(","), function () {
// More funds than one call compares are compared two by two.
if (list.length > TI_MATRIX_MAX) return ti_matrix_pairwise_(list);
var r = ti_fetch_("/api/v1/overlap", null, { funds: list.join(",") });
// Funds whose files are too large for one call, such as VT, VXUS and
// VTI together, are compared two by two, every pair in one batch of
// requests, when there are few enough of them.
if (r.code === 400 && r.body && r.body.reason === "too_large" && list.length <= TI_PAIRWISE_MAX) {
return ti_matrix_pairwise_(list);
}
var doc = ti_ok_(r);
var cell = {};
var put = function (x, y, v) { cell[x + "|" + y] = v; cell[y + "|" + x] = v; };
var left = {};
(doc.not_comparable || []).forEach(function (x) { left[x.ticker] = "not comparable"; });
((doc.skipped && doc.skipped.not_covered) || []).forEach(function (t) { left[t] = "not covered"; });
if (doc.kind === "pair") {
var o = doc.overlap || doc.issuer_overlap;
if (o) put(list[0], list[1], o.by_weight);
} else {
(doc.pairs || []).concat(doc.issuer_overlap_pairs || []).forEach(function (p) {
put(p.pair[0], p.pair[1], p.by_weight);
});
}
return ti_matrix_(list, cell, left, doc.holdings_as_of || {});
});
});
}
// The most funds whose matrix is asked for two by two when one call cannot
// take their files: 12 funds are 66 pairs, fetched together.
var TI_PAIRWISE_MAX = 12;
// The most funds one call to /api/v1/overlap compares, since 8 October 2026
// (LIMITS in src/compute.js): a longer list is compared two by two.
var TI_MATRIX_MAX = 10;
// The most funds one call to /api/v1/lookthrough looks through.
var TI_LOOKTHROUGH_MAX = 6;
// The matrix: a header row of the tickers, a row per fund, then the dates. A
// fund left out keeps its row and column, empty, with the reason under it.
function ti_matrix_(list, cell, left, dates) {
var out = [["by_weight"].concat(list)];
list.forEach(function (x) {
out.push([x].concat(list.map(function (y) {
if (left[x] || left[y]) return "";
if (x === y) return 100;
return ti_cell_(cell[x + "|" + y]);
})));
});
out.push(["holdings_as_of"].concat(list.map(function (t) { return left[t] || dates[t] || ""; })));
return out;
}
// The matrix from one call per pair. A pair that cannot be compared, such as
// a bond fund beside a stock fund, leaves its cell empty; a fund not covered
// is left out as in one call.
function ti_matrix_pairwise_(list) {
var pairs = [];
for (var i = 0; i < list.length; i++) for (var j = i + 1; j < list.length; j++) pairs.push([list[i], list[j]]);
var answers = ti_fetch_all_(pairs.map(function (p) { return ["/api/v1/overlap", null, { funds: p.join(",") }]; }));
var cell = {}, left = {}, dates = {};
answers.forEach(function (r, k) {
var a = pairs[k][0], b = pairs[k][1];
if (r.code === 404 && r.body && r.body.not_covered) {
r.body.not_covered.forEach(function (t) { left[t] = "not covered"; });
return;
}
if (r.code === 400 && r.body && r.body.reason === "too_large") {
cell[a + "|" + b] = cell[b + "|" + a] = "too large";
return;
}
var doc = ti_ok_(r);
var h = doc.holdings_as_of || {};
Object.keys(h).forEach(function (t) { dates[t] = dates[t] || h[t]; });
var o = doc.overlap || doc.issuer_overlap;
if (o) cell[a + "|" + b] = cell[b + "|" + a] = o.by_weight;
});
return ti_matrix_(list, cell, left, dates);
}
/**
* The companies under a portfolio of US ETFs, largest first: each one's share of the portfolio, the funds it comes through and their holdings dates.
*
* @param {Array} funds The cells holding the tickers, such as A2:A6.
* @param {Array} weights The cells beside them with each fund's weight, percent or amount of money. Every fund counts the same when left out.
* @param {number} top How many companies to list, from 1 to 200. 25 when left out.
* @return The companies under a header row: holding, name, weight_pct, value, through, holdings_as_of.
* @customfunction
*/
function TI_LOOKTHROUGH(funds, weights, top) {
return ti_run_(function () {
var t = ti_flat_(funds);
var w = ti_blank_(weights) ? null : ti_flat_(weights);
if (w && w.length !== t.length) {
throw new TiError_("weights has " + w.length + " cells and funds " + t.length + "; give one weight beside each fund.");
}
var parts = [], seen = {};
for (var i = 0; i < t.length; i++) {
if (ti_blank_(t[i])) {
if (w && !ti_blank_(w[i])) throw new TiError_("Cell " + (i + 1) + " of weights has a weight and no fund beside it.");
continue;
}
var tk = ti_ticker_(t[i], "funds");
if (seen[tk]) throw new TiError_(tk + " is in funds twice; give each fund once, with its whole weight.");
seen[tk] = true;
parts.push(w ? tk + ":" + ti_amount_(w[i], tk) : tk);
}
if (!parts.length) throw new TiError_("funds is empty: give one fund at least, such as VOO.");
if (parts.length > TI_LOOKTHROUGH_MAX) {
throw new TiError_("funds names " + parts.length + " funds; one look-through takes at most " + TI_LOOKTHROUGH_MAX + ".");
}
var n = ti_whole_(top, "top", 1, 200, 25);
var portfolio = parts.join(",");
return ti_answer_("lookthrough:" + portfolio + ":" + n, function () {
var doc = ti_get_("/api/v1/lookthrough", null, { portfolio: portfolio, top: n });
var dates = doc.holdings_as_of || {};
var names = ti_names_("securities");
var out = [["holding", "name", "weight_pct", "value", "through", "holdings_as_of"]];
(doc.companies || []).forEach(function (c) {
var via = c.through || [];
out.push([c.ticker, names[c.ticker] || "", c.weight_pct, ti_cell_(c.value),
via.map(function (x) { return x.fund + " " + x.weight_pct; }).join("; "),
via.map(function (x) { return x.fund + " " + (dates[x.fund] || ""); }).join("; ")]);
});
// The funds the answer leaves out, each with its reason, so that a
// shorter list is never read as the whole portfolio.
(doc.not_looked_through || []).forEach(function (x) {
out.push([x.ticker, "Left out: " + x.why, "", "", "", dates[x.ticker] || ""]);
});
((doc.skipped && doc.skipped.not_covered) || []).forEach(function (x) {
out.push([x, "Left out: not covered", "", "", "", ""]);
});
return out;
});
});
}
/**
* One figure of a US ETF's file: expense_ratio, aum, positions, name, yield, return_1y, holdings_as_of and the other fields of the API's fund file.
*
* @param {string} ticker The fund's ticker, such as "VOO".
* @param {string} field The figure, such as "expense_ratio" (in percent) or "aum" (in billions of US dollars, the net assets of its filing for a fund read from SEC filings).
* @param {boolean} with_date TRUE to give the figure's date in the cell beside it.
* @return The figure, or the figure and its date side by side.
* @customfunction
*/
function TI_FUND(ticker, field, with_date) {
return ti_run_(function () {
var t = ti_ticker_(ticker, "ticker");
if (ti_blank_(field)) throw new TiError_("field is empty: give one such as \"expense_ratio\", \"aum\" or \"positions\".");
var f = String(field).trim().toLowerCase().replace(/[\s\-]+/g, "_");
var key = TI_FUND_ALIAS[f] || f;
var doc = ti_answer_("fund:" + t, function () {
var d = ti_get_("/api/v1/fund/{fund}.json", { fund: t });
var kept = {};
Object.keys(d).forEach(function (k) {
if (d[k] === null || typeof d[k] !== "object") kept[k] = d[k];
});
return kept;
});
if (!(key in doc)) {
throw new TiError_(String(field).slice(0, 40) + " is not a field of " + t + "'s file. Try expense_ratio, aum, "
+ "positions, name, yield, return_1y, return_3y, return_5y, volatility, max_drawdown, price, holdings_as_of "
+ "or source, or a field of " + TI_BASE + "/api/#fund.");
}
var value = doc[key], date = doc[TI_FUND_DATE[key]] || doc.as_of;
// A fund read from an SEC filing has no assets from market data but the
// net assets of its filing, given in the same unit, dated to its report.
if (key === "aum_b" && (value === null || value === undefined) && typeof doc.net_assets === "number") {
value = Math.round(doc.net_assets / 1e6) / 1e3;
date = doc.report_date || doc.holdings_as_of;
}
if (!with_date) return ti_cell_(value);
return [[ti_cell_(value), ti_cell_(date)]];
});
}
// The words TI_FUND takes for the fields of a fund file
// (https://tickerinside.com/api/#fund), besides the fields' own names.
var TI_FUND_ALIAS = {
expense_ratio: "expense_ratio", aum: "aum_b", assets: "aum_b", price: "last_close", return_1y: "r1",
return_3y: "r3", return_5y: "r5", volatility: "vol", max_drawdown: "mdd3", holdings_date: "holdings_as_of",
source: "source_layer"
};
// The date of each field that has one of its own; every other figure is
// dated as_of, the date of the prices.
var TI_FUND_DATE = {
expense_ratio: "expense_ratio_as_of", expense_ratio_gross: "expense_ratio_as_of", aum_b: "aum_date",
positions: "holdings_as_of", name: "holdings_as_of", source_layer: "holdings_as_of",
last_close: "last_close_date", day_change_pct: "last_close_date",
etf_class_net_assets_b: "etf_class_net_assets_as_of"
};
// ---------------------------------------------------------------- holders
// The most funds a page of /api/v1/screen holds, since 8 October 2026.
var TI_SCREEN_PAGE = 100;
// The holders from /api/v1/screen, which names each fund, 100 a call, at
// most 60 calls: more funds than the API covers, so no list is cut short.
// Null for a security with no stock file, which screen refuses by its
// parameter holds; its holders are in /api/v1/sec/ alone.
function ti_holders_screen_(t, n) {
var rows = [];
for (var page = 0; page < 60; page++) {
var limit = n ? Math.min(TI_SCREEN_PAGE, n - rows.length) : TI_SCREEN_PAGE;
var r = ti_fetch_("/api/v1/screen", null, { holds: t, limit: limit, offset: rows.length });
if (r.code === 400 && r.body && r.body.parameter === "holds") return null;
var doc = ti_ok_(r);
(doc.funds || []).forEach(function (f) {
rows.push([f.ticker, f.name || "", f.weight_pct, f.holdings_as_of || "", f.holdings_source || ""]);
});
if (!doc.next || !(doc.funds || []).length || (n && rows.length >= n)) break;
}
return rows;
}
// The holders of a security with no stock file, from its answer under
// /api/v1/sec/: both layers, heaviest first, named from the list of funds.
function ti_holders_file_(t, n) {
var doc = ti_get_("/api/v1/sec/{stock}.json", { stock: t });
var names = ti_names_("funds");
var label = { issuer: "issuer file", sec_nport: "SEC Form N-PORT" };
var all = (doc.holders || []).map(function (r) { return [r[0], r[1], r[2], "issuer"]; })
.concat((doc.holders_sec_nport || []).map(function (r) { return [r[0], r[1], r[2], "sec_nport"]; }));
if (doc.holders_all) all = doc.holders_all;
var seen = {}, rows = [];
all.filter(function (r) { return r[1] > 0; })
.sort(function (a, b) { return b[1] - a[1]; })
.forEach(function (r) {
if (seen[r[0]]) return;
seen[r[0]] = true;
rows.push([r[0], names[r[0]] || "", r[1], r[2] || "", label[r[3]] || r[3] || ""]);
});
return n ? rows.slice(0, n) : rows;
}
// ---------------------------------------------------------------- names
var ti_names_memo_ = {};
// {ticker: name}, kept an hour like any answer and read once per run: of
// every security a covered fund holds, from /api/v1/securities.json (about
// 1 MB), or of every fund, from /api/v1/funds.json (about 1.4 MB), which
// TI_HOLDERS reads only for a security with no stock file.
function ti_names_(kind) {
if (ti_names_memo_[kind]) return ti_names_memo_[kind];
var text = ti_cache_get_("names:" + kind);
if (text === null) {
var rows = [];
if (kind === "funds") {
var doc = ti_get_("/api/v1/funds.json");
Object.keys(doc).forEach(function (k) {
if (Array.isArray(doc[k])) doc[k].forEach(function (x) { if (x && x.t && x.n) rows.push([x.t, x.n]); });
});
} else {
(ti_get_("/api/v1/securities.json").securities || []).forEach(function (r) { if (r[3]) rows.push([r[0], r[3]]); });
}
text = rows.map(function (r) { return r[0] + "\t" + String(r[1]).replace(/[\t\n\r]/g, " "); }).join("\n");
ti_cache_put_("names:" + kind, text);
}
var map = {};
text.split("\n").forEach(function (line) {
var i = line.indexOf("\t");
if (i > 0) map[line.slice(0, i)] = line.slice(i + 1);
});
ti_names_memo_[kind] = map;
return map;
}
// ---------------------------------------------------------------- the API
// A message for the cell. keep is true for an answer that holds until the
// next night's files, such as a ticker that is not covered, which is kept
// like any answer; a failure to reach the API is never kept.
function TiError_(message, keep) {
this.message = message;
this.keep = !!keep;
}
// Runs a function's body and turns any failure into a message in the cell,
// which is easier to read than #ERROR!.
function ti_run_(body) {
try {
return body();
} catch (e) {
return "TickerInside: " + (e && e.message ? e.message : String(e));
}
}
// The answer kept under key, or compute()'s, then kept for an hour.
function ti_answer_(key, compute) {
var kept = ti_cache_get_(key);
if (kept !== null) {
var v = JSON.parse(kept);
if (v.e) throw new TiError_(v.e, true);
return v.ok;
}
var out;
try {
out = compute();
} catch (e) {
if (e instanceof TiError_ && e.keep) ti_cache_put_(key, JSON.stringify({ e: e.message }));
throw e;
}
ti_cache_put_(key, JSON.stringify({ ok: out }));
return out;
}
// One call to the API: path is an address with {fund} or {stock} in it,
// filled from params, and query the parameters after the question mark.
// { code, body }, body the parsed JSON or null.
function ti_fetch_(path, params, query) {
var res;
try {
res = UrlFetchApp.fetch(ti_url_(path, params, query), { muteHttpExceptions: true, followRedirects: true });
} catch (e) {
throw new TiError_("tickerinside.com could not be reached just now (" + (e && e.message ? e.message : e)
+ "). Try again in a minute.");
}
return ti_read_(res);
}
function ti_url_(path, params, query) {
var url = TI_BASE + path.replace(/\{(\w+)\}/g, function (m, k) {
return encodeURIComponent(String(params[k]).toLowerCase());
});
if (query) {
url += "?" + Object.keys(query).map(function (k) {
return k + "=" + encodeURIComponent(String(query[k]));
}).join("&");
}
return url;
}
function ti_read_(res) {
var body = null;
try {
body = JSON.parse(res.getContentText());
} catch (e) {
body = null;
}
return { code: res.getResponseCode(), body: body };
}
// Several calls at once, each [path, params, query] as ti_fetch_ takes them:
// UrlFetchApp.fetchAll sends them together, which a sheet waits for once.
function ti_fetch_all_(calls) {
var res;
try {
res = UrlFetchApp.fetchAll(calls.map(function (c) {
return { url: ti_url_(c[0], c[1], c[2]), muteHttpExceptions: true, followRedirects: true };
}));
} catch (e) {
throw new TiError_("tickerinside.com could not be reached just now (" + (e && e.message ? e.message : e)
+ "). Try again in a minute.");
}
return res.map(ti_read_);
}
function ti_get_(path, params, query) {
return ti_ok_(ti_fetch_(path, params, query));
}
// The body of a 200, or the API's reason in a few words. A 404 or a 400
// holds until the files change, so it is kept; a 503 is not.
function ti_ok_(r) {
var b = r.body || {};
if (r.code === 200 && r.body) return r.body;
// A stock asked for as a fund, or a fund held by others that has no file
// of its own yet: its holders answer.
if (r.code === 404 && b.reason === "has_security_file") {
throw new TiError_(b.ticker + " is not a covered fund. The covered ETFs that hold it: =TI_HOLDERS(\""
+ b.ticker + "\").", true);
}
if (r.code === 404 && b.error === "not_covered") {
var list = b.not_covered && b.not_covered.length ? b.not_covered : [b.ticker];
throw new TiError_(list.join(", ") + (list.length === 1 ? " is" : " are") + " not covered.", true);
}
if (r.code === 400 && b.message) throw new TiError_(b.message, true);
if (r.code === 429 || r.code === 503) throw new TiError_("tickerinside.com is busy; try again in a minute.");
throw new TiError_("tickerinside.com answered HTTP " + r.code + (b.message ? ": " + b.message : "."));
}
function ti_whys_(list) {
return (list || []).map(function (x) { return x.ticker + ": " + x.why; }).join(" ");
}
// ---------------------------------------------------------------- the cells
// An empty cell, a cell of spaces, an argument left out, or a range of them.
function ti_blank_(v) {
if (v === undefined || v === null) return true;
if (typeof v === "string") return v.trim() === "";
if (Array.isArray(v)) return ti_flat_(v).every(function (x) { return !Array.isArray(x) && ti_blank_(x); });
return false;
}
// A range's cells in reading order, or one value as a list of one.
function ti_flat_(v) {
if (!Array.isArray(v)) return [v];
var out = [];
v.forEach(function (row) { (Array.isArray(row) ? row : [row]).forEach(function (x) { out.push(x); }); });
return out;
}
function ti_ticker_(v, name) {
if (Array.isArray(v)) {
var cells = ti_flat_(v);
if (cells.length !== 1) throw new TiError_(name + " takes one cell, not a range.");
v = cells[0];
}
if (ti_blank_(v)) throw new TiError_(name + " is empty: give a ticker such as VOO.");
var t = String(v).trim().toUpperCase().replace(/[\/ ]/g, ".");
if (!TI_TICKER.test(t)) throw new TiError_(String(v).slice(0, 20) + " in " + name + " is not a ticker such as VOO.");
return t;
}
// The tickers of a range, each once, in reading order, blank cells skipped.
function ti_tickers_(v, name) {
var out = [];
ti_flat_(v).forEach(function (x) {
if (ti_blank_(x)) return;
var t = ti_ticker_(x, name);
if (out.indexOf(t) < 0) out.push(t);
});
return out;
}
function ti_whole_(v, name, lo, hi, dflt) {
if (ti_blank_(v)) return dflt;
var x = Number(v);
if (!(x % 1 === 0 && x >= lo && x <= hi)) throw new TiError_(name + " must be a whole number from " + lo + " to " + hi + ".");
return x;
}
// A weight as the API reads it: a positive number written without an
// exponent. A cell formatted as a percent arrives as a fraction (60% is
// 0.6), which weighs the same against the others, and so does the text 60%:
// read as 60, it outweighed a percent cell beside it a hundred times.
function ti_amount_(v, t) {
var s = typeof v === "number" ? null : String(v).trim();
var x = s === null ? v : /%$/.test(s) ? Number(s.slice(0, -1)) / 100 : Number(s);
if (!(x >= 1e-6 && x < 1e15)) {
throw new TiError_("The weight of " + t + " must be a number above 0, such as 60, 12.5% or 25000.");
}
return String(Number(x.toFixed(6)));
}
// A value for a cell: an empty cell for null or a missing figure.
function ti_cell_(v) {
return v === undefined || v === null ? "" : v;
}
// ---------------------------------------------------------------- the cache
function ti_store_() {
try {
return CacheService.getScriptCache();
} catch (e) {
return null;
}
}
// A key CacheService takes: at most 250 characters, so a long one, such as a
// portfolio of many funds, is kept under its hash.
function ti_key_(key) {
key = TI_KEY + key;
if (key.length <= 200) return key;
var a = 0x811c9dc5, b = 0x5bd1e995;
for (var i = 0; i < key.length; i++) {
var c = key.charCodeAt(i);
a = Math.imul(a ^ c, 0x01000193) >>> 0;
b = Math.imul(b ^ c, 0x5bd1e995) >>> 0;
}
return TI_KEY + "h:" + a.toString(16) + b.toString(16) + ":" + key.length;
}
// The text kept under key, or null. A long text is kept as a head, "#" and
// the number of pieces, and the pieces; it is read whole or not at all.
function ti_cache_get_(key) {
var store = ti_store_();
if (!store) return null;
key = ti_key_(key);
try {
var head = store.get(key);
if (head === null || head === undefined) return null;
if (head.charAt(0) === "=") return head.slice(1);
var n = parseInt(head.slice(1), 10), keys = [];
for (var i = 0; i < n; i++) keys.push(key + "#" + i);
var got = store.getAll(keys), parts = [];
for (i = 0; i < n; i++) {
if (typeof got[keys[i]] !== "string") return null;
parts.push(got[keys[i]]);
}
return parts.join("");
} catch (e) {
return null;
}
}
// Keeps text under key for an hour. A failure to keep it costs a call to
// the API next time, never the answer.
function ti_cache_put_(key, text) {
var store = ti_store_();
if (!store) return;
key = ti_key_(key);
try {
if (text.length < TI_PIECE) {
store.put(key, "=" + text, TI_CACHE_SECONDS);
return;
}
var pieces = {}, n = 0;
for (var at = 0; at < text.length; n++) {
var end = Math.min(at + TI_PIECE, text.length);
// Never between the two halves of a character outside the basic plane.
var c = text.charCodeAt(end - 1);
if (end < text.length && c >= 0xd800 && c <= 0xdbff) end--;
pieces[key + "#" + n] = text.slice(at, end);
at = end;
}
if (n > 200) return;
store.putAll(pieces, TI_CACHE_SECONDS);
store.put(key, "#" + n, TI_CACHE_SECONDS);
} catch (e) {
return;
}
}
TI_HOLDINGS(ticker, [top])The holdings of a fund, heaviest first: all of them, or the top ones.=TI_HOLDINGS("VOO", 10)The ten largest holdings of VOO, each with its name, its weight in percent and the date of the fund's holdings.TI_HOLDERS(stock, [top])The covered ETFs that hold a stock, heaviest weight first.=TI_HOLDERS("NVDA", 20)The 20 ETFs that weigh NVDA most, each with the fund's name, the stock's weight in it and the date and source of its holdings.TI_OVERLAP(fund1, fund2, [field])The overlap of two funds, or one figure of it with a field such as "by_weight".=TI_OVERLAP("VOO", "QQQ")The weight VOO and QQQ hold in common, the share of each held by the other, the companies in both, the correlation of their weekly returns and both holdings dates.TI_OVERLAP_MATRIX(funds)The overlap by weight of every pair among 2 to 12 funds.=TI_OVERLAP_MATRIX(A2:A5)A matrix in percent for the funds in A2 to A5, with each fund's holdings date under it.TI_LOOKTHROUGH(funds, [weights], [top])The companies under a portfolio of funds, largest first. Without weights, every fund counts the same.=TI_LOOKTHROUGH(A2:A4, B2:B4, 25)The 25 largest companies under the funds in A2 to A4 with the weights in B2 to B4, each with its share, the funds it comes through and their dates.TI_FUND(ticker, field, [with_date])One figure of a fund's file, such as expense_ratio (percent), aum (billions of US dollars), positions, name, yield, return_1y or volatility.=TI_FUND("VOO", "expense_ratio", TRUE)The expense ratio of VOO in percent, with the date of the figure beside it.
Weights and overlaps are in percent, as in the files. In a sheet whose locale writes decimals with a comma, separate the arguments with semicolons: =TI_HOLDINGS("VOO"; 10).
Three recipes
Each starts from a few cells you type, then gives the formulas to paste, with the script above installed.
How much do my ETFs overlap
1. Type your funds in column A, one per row, here VOO, QQQ, SCHD and VTI in A2 to A5.
2. Paste this formula in C1. It shows the overlap by weight of every pair, in percent: two funds at 90 hold nearly the same companies in nearly the same weights.
=TI_OVERLAP_MATRIX(A2:A5)3. Paste this formula in C9 for one pair in detail, with the share of each fund the other holds and both holdings dates.
=TI_OVERLAP(A2, A5)4. Paste this formula in B2 for one figure alone, to use in your own formulas.
=TI_OVERLAP(A2, A3, "by_weight")In Excel or Power BI: the overlap of two funds, a query to paste.
What my portfolio really holds
1. Type your funds in column A and what each weighs in column B: a weight, a percent or the amount of money you hold, here VOO 60, QQQ 30 and SCHD 10.
2. Paste this formula in D1. It lists the 25 largest companies underneath, each with its share of the portfolio and the funds it comes through.
=TI_LOOKTHROUGH(A2:A4, B2:B4, 25)3. Paste this formula in B6. It shows how much of the portfolio is one company, here Nvidia, in percent.
=VLOOKUP("NVDA", TI_LOOKTHROUGH(A2:A4, B2:B4, 200), 3, FALSE)In Excel or Power BI: what a portfolio holds underneath, a query to paste.
Which ETFs hold my stocks
1. Type your stocks in column A, here NVDA, MSFT and AAPL in A2 to A4.
2. Paste this formula in D1. It lists the ten ETFs that weigh the stock in A2 most, with the date and source of each fund's holdings.
=TI_HOLDERS(A2, 10)3. Paste this formula in B2, then fill it down to B4. It shows each stock's weight in one fund you own, here VOO, in percent.
=VLOOKUP(A2, TI_HOLDINGS("VOO"), 3, FALSE)In Excel or Power BI: the ETFs that hold a stock, a query to paste.
Google Sheets with IMPORTDATA
Without the script, one formula loads a file. Paste this in any cell for the holdings of VOO:
=IMPORTDATA("https://tickerinside.com/api/v1/fund/voo.csv")Type a ticker in A1 and paste this in B1, so that the holdings follow the cell:
=IMPORTDATA("https://tickerinside.com/api/v1/fund/"&LOWER(A1)&".csv")For a stock, paste this for the ETFs that hold NVDA, with its weight in each and the date of each fund's holdings:
=IMPORTDATA("https://tickerinside.com/api/v1/sec/nvda.csv")For every fund, one row each, with its issuer, its expense ratio, its assets and the address of its file:
=IMPORTDATA("https://tickerinside.com/api/v1/funds.csv")The files write decimals with a point. In a sheet whose locale writes them with a comma, as French or German do, give the file's locale as the third argument so that the weights are read as numbers, with semicolons between the arguments where the sheet uses them:
=IMPORTDATA("https://tickerinside.com/api/v1/fund/voo.csv"; ","; "en_US")Excel and Power BI
Four Power Query queries load the same answers into Excel and Power BI, with the type of every column set and the decimal point read whatever the locale. Paste one once; Data, Refresh All fetches it again, or the schedule set in the properties of its query. A ticker that is not covered stops the query with a message that says why.
- In Excel, choose Data, then Get Data, From Other Sources, Blank Query. In Power BI, choose Get data, then Blank query.
- In the Power Query editor, choose Advanced Editor.
- Copy a query below with its Copy button and paste it in place of the text there. Its description says which line to change for another ticker.
- Choose Done, then Close & Load.
The holdings of a fund
One row per holding, heaviest first, with its name, its weight and the date of the fund's holdings. For another fund, change VOO on the line Fund = "VOO".
ShowHide the query, 32 lines
// The holdings of one US ETF: https://tickerinside.com/spreadsheets/
// Data: TickerInside, CC BY 4.0. Questions: contact@tickerinside.com
let
Fund = "VOO",
Response = Web.Contents("https://tickerinside.com", [
RelativePath = Text.Format("api/v1/fund/#[fund].csv",
[fund = Text.Lower(Fund)]),
ManualStatusHandling = {400, 404}]),
Checked = if Value.Metadata(Response)[Response.Status] = 200
then Response
else error Error.Record("TickerInside",
Json.Document(Response)[message]),
Rows = Csv.Document(Checked, [Delimiter = ",",
Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
Promoted = Table.PromoteHeaders(Rows, [PromoteAllScalars = true]),
Typed = Table.TransformColumnTypes(Promoted,
{{"weight_pct", type number}, {"holdings_as_of", type date}},
"en-US"),
Securities = Table.PromoteHeaders(Csv.Document(
Web.Contents("https://tickerinside.com", [
RelativePath = "api/v1/securities.csv"]),
[Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
[PromoteAllScalars = true]),
Indexed = Table.AddIndexColumn(Typed, "row", 1, 1),
Joined = Table.NestedJoin(Indexed, {"ticker"},
Table.SelectColumns(Securities, {"ticker", "name"}), {"ticker"},
"security", JoinKind.LeftOuter),
Named = Table.ExpandTableColumn(Joined, "security", {"name"}),
Sorted = Table.RemoveColumns(
Table.Sort(Named, {{"row", Order.Ascending}}), {"row"})
in
Sorted
The overlap of two funds
One row per pair, with the overlap by weight, the share of each fund held by the other and both holdings dates. Type up to 10 funds on the line Funds = {"VOO", "QQQ"} for every pair among them.
ShowHide the query, 23 lines
// The overlap of US ETFs: https://tickerinside.com/spreadsheets/
// Data: TickerInside, CC BY 4.0. Questions: contact@tickerinside.com
let
Funds = {"VOO", "QQQ"},
Response = Web.Contents("https://tickerinside.com", [
RelativePath = "api/v1/overlap.csv",
Query = [funds = Text.Combine(Funds, ",")],
ManualStatusHandling = {400, 404}]),
Checked = if Value.Metadata(Response)[Response.Status] = 200
then Response
else error Error.Record("TickerInside",
Json.Document(Response)[message]),
Rows = Csv.Document(Checked, [Delimiter = ",",
Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
Promoted = Table.PromoteHeaders(Rows, [PromoteAllScalars = true]),
Typed = Table.TransformColumnTypes(Promoted, {
{"by_weight", type number}, {"share_of_a", type number},
{"share_of_b", type number}, {"in_both", Int64.Type},
{"correlation_3y", type number},
{"holdings_as_of_a", type date}, {"holdings_as_of_b", type date}},
"en-US")
in
Typed
The ETFs that hold a stock
One row per fund, heaviest weight first, with the fund's name, the stock's rank in it, the value held and the date of the fund's holdings. For another stock, change NVDA on the line Stock = "NVDA".
ShowHide the query, 34 lines
// The US ETFs that hold a stock: https://tickerinside.com/spreadsheets/
// Data: TickerInside, CC BY 4.0. Questions: contact@tickerinside.com
let
Stock = "NVDA",
Response = Web.Contents("https://tickerinside.com", [
RelativePath = Text.Format("api/v1/sec/#[stock].csv",
[stock = Text.Lower(Stock)]),
ManualStatusHandling = {400, 404}]),
Checked = if Value.Metadata(Response)[Response.Status] = 200
then Response
else error Error.Record("TickerInside",
Json.Document(Response)[message]),
Rows = Csv.Document(Checked, [Delimiter = ",",
Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
Promoted = Table.PromoteHeaders(Rows, [PromoteAllScalars = true]),
Typed = Table.TransformColumnTypes(Promoted, {
{"weight_pct", type number}, {"holdings_as_of", type date},
{"rank_in_fund", Int64.Type}, {"fund_positions", Int64.Type},
{"value_musd", type number}}, "en-US"),
Funds = Table.PromoteHeaders(Csv.Document(
Web.Contents("https://tickerinside.com", [
RelativePath = "api/v1/funds.csv"]),
[Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
[PromoteAllScalars = true]),
Indexed = Table.AddIndexColumn(Typed, "row", 1, 1),
Joined = Table.NestedJoin(Indexed, {"fund"},
Table.SelectColumns(Funds, {"ticker", "name"}), {"ticker"},
"listed", JoinKind.LeftOuter),
Named = Table.ExpandTableColumn(Joined, "listed", {"name"},
{"fund_name"}),
Sorted = Table.RemoveColumns(
Table.Sort(Named, {{"row", Order.Ascending}}), {"row"})
in
Sorted
What a portfolio holds underneath
The companies under a portfolio of funds, largest first, with the funds each one comes through and their holdings dates.
- In Excel, type your funds in a table named Portfolio, with the columns Ticker and Weight. A weight can be a percent or an amount of money.
- In Power BI, which has no workbook table, put // before the line Portfolio = Excel.CurrentWorkbook, remove the // from the line under it and type your funds there.
- To list more or fewer companies, change Top = 50 to any number from 1 to 200.
ShowHide the query, 38 lines
// https://tickerinside.com/spreadsheets/ CC BY 4.0, contact@tickerinside.com
let
Portfolio = Excel.CurrentWorkbook(){[Name = "Portfolio"]}[Content],
// Portfolio = #table({"Ticker", "Weight"}, {{"VOO", 60}, {"QQQ", 30}, {"SCHD", 10}}),
Top = 50,
Filled = Table.SelectRows(Portfolio, each [Ticker] <> null
and Text.Trim(Text.From([Ticker])) <> ""),
Pairs = List.Transform(Table.ToRecords(Filled),
each Text.Trim(Text.From([Ticker]))
& (if [Weight] = null then "" else ":" & Number.ToText(
Number.From([Weight]), "0.############", "en-US"))),
Response = Web.Contents("https://tickerinside.com", [
RelativePath = "api/v1/lookthrough.csv",
Query = [portfolio = Text.Combine(Pairs, ","), top = Text.From(Top)],
ManualStatusHandling = {400, 404}]),
Checked = if Value.Metadata(Response)[Response.Status] = 200
then Response
else error Error.Record("TickerInside",
Json.Document(Response)[message]),
Rows = Csv.Document(Checked, [Delimiter = ",",
Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
Promoted = Table.PromoteHeaders(Rows, [PromoteAllScalars = true]),
Typed = Table.TransformColumnTypes(Promoted, {{"rank", Int64.Type},
{"weight_pct", type number}, {"value", type number}}, "en-US"),
Securities = Table.PromoteHeaders(Csv.Document(
Web.Contents("https://tickerinside.com", [
RelativePath = "api/v1/securities.csv"]),
[Delimiter = ",", Encoding = 65001, QuoteStyle = QuoteStyle.Csv]),
[PromoteAllScalars = true]),
Indexed = Table.AddIndexColumn(Typed, "row", 1, 1),
Joined = Table.NestedJoin(Indexed, {"ticker"},
Table.SelectColumns(Securities, {"ticker", "name"}), {"ticker"},
"security", JoinKind.LeftOuter),
Named = Table.ExpandTableColumn(Joined, "security", {"name"}),
Sorted = Table.RemoveColumns(
Table.Sort(Named, {{"row", Order.Ascending}}), {"row"})
in
Sorted
The look-through query sends the tickers and weights of your table to tickerinside.com. Its records keep the tickers, never a weight or an amount; the answer, which repeats them, stays in Cloudflare's edge cache for up to a day, served only to the same query. If Excel stops on a message about privacy levels or Formula.Firewall, which it gives when one query reads the workbook and the web, choose Data, Get Data, Query Options, then Privacy under Current Workbook, and Ignore the Privacy Levels; in Power BI, the same choice is under File, Options and settings, Options, Current File.
One CSV file from the web
For a single file, choose Data, then From Web, paste the address of a CSV file and load it as a table. Power BI reads the same addresses under Get data, then Web.
https://tickerinside.com/api/v1/fund/voo.csvWhere the system writes decimals with a comma, set the type of the number columns with Change Type, Using Locale, and English (United States), or set the regional setting of the query to English (United States), so that a weight such as 8.0927 is read as a number.
The JSON files load the same way, From Web reading each as a record, for what the holdings CSV does not carry: a fund's returns and risk figures and its weekly series. funds.csv has the expense ratio and the assets. The API reference documents every field.
When the data changes
The files are rebuilt every weekday night, so a refresh more often than once a day returns the same rows. The holdings_as_of column gives the date of each row: an issuer's own file is read every weekday night, and an SEC filing describes a fund at a quarter end, months before it is read. How fresh the holdings are, by source measures both.
Bond, leveraged, option income and gold funds
A fund whose holdings are not stocks loads from the same addresses, /api/v1/fund/{ticker}.csv and .json. Its rows are the holdings its file lists one by one: stocks and funds by ticker, other lines by name, a bond line by its issuer, coupon and maturity. Its swaps, futures and options are never rows. A leveraged fund's Treasury bills and money market funds, which its file gives by kind only, are in the composition of its JSON file. Two more columns say what the rows hold and how much of the fund's net assets they cover. Its own JSON file, /api/v1/etf/{ticker}.json, adds a summary in words, its composition and, for a leveraged or option income fund, its swaps, futures and options as exposure; the file column of funds.csv gives its address.
The columns
/api/v1/fund/{ticker}.csv
fund,holdings_as_ofandsource_layer: the fund, the date of its holdings, and where they were read,issuerfor the issuer's own file,sec_nportfor an SEC filing, or the layer of a fund whose holdings are not stocks.ticker: the holding, a US ticker, or a local code and its market for a line listed abroad, such as 7203.JP for Toyota in Tokyo. For a fund whose holdings are not stocks, a line it holds is named in lower case.weight_pct: its weight in percent of the fund, negative for a short position.holdings_kindandlisted_pct, only for a fund whose holdings are not stocks: what its rows hold (bond, derivative, commodity, multi-asset, stock or prospectus) and the share of its net assets they cover, in percent. A fund with no rows to give, such as one known from its prospectus only, has one row with these two and no holding.
/api/v1/sec/{ticker}.csv
securityandfund: the stock, and a covered fund that holds it.weight_pct: the stock's weight in that fund, negative for a short position.holdings_as_ofandsource_layer, as above.rank_in_fund: 1 for the fund's largest position.fund_positions: how many positions the fund has.value_musd: the value held, in millions of US dollars. It is empty when the fund's net assets are unknown or known to one figure only (under $1 billion), and when its filing covers mutual fund classes too.- For a security with no stock file, such as a line listed abroad,
rank_in_fund,fund_positionsandvalue_musdare empty.
/api/v1/funds.csv
ticker,name,source_layer,issuerandcategory, the fund's category, set by TickerInside from its own file.expense_ratio_pctandaum_b, the assets in billions of US dollars from market data, empty where they are not in the data.positions: the positions in its file, the lines of its filing for a fixed income fund, and 0 for a trust or pool and for a fund known from its prospectus only.file: the address of its JSON file.category_id: the id of its category, defined at /etf/category/.
Credit
TickerInside's work is published under CC BY 4.0: credit TickerInside where the figures appear, with a link to the page they come from wherever the medium allows a link, and by name where it does not. On a site or in an app, one line in the footer can do it:
Data: <a href="https://tickerinside.com">TickerInside</a> (CC BY 4.0)How you mark the link is up to you. Fund holdings remain the issuers' publications, as the legal notice says. The figures are measurements of what funds hold, not investment advice.
