TickerInside

ETF holdings in Excel and Google Sheets

Edited by VoidLab Data through Last revised

View as Markdown What changed

The holdings of every ETF the API covers, and for each stock the covered ETFs that hold it, load into a spreadsheet in one step, as CSV. Each row carries the date of the holdings it comes from. There is nothing to install, and no key or account to ask for.

Google Sheets

A single formula in a cell loads a fund's holdings, here those of VOO:

=IMPORTDATA("https://tickerinside.com/api/v1/fund/voo.csv")

With the ticker typed in cell A1, the same formula follows the cell:

=IMPORTDATA("https://tickerinside.com/api/v1/fund/"&LOWER(A1)&".csv")

For a stock, the file lists the ETFs that hold it, with its weight in each and the date of each fund's holdings, here for NVDA:

=IMPORTDATA("https://tickerinside.com/api/v1/sec/nvda.csv")

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

In Excel, 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. The table is fetched again with Data, Refresh All, or on the schedule set in the properties of its query.

https://tickerinside.com/api/v1/fund/voo.csv

Where 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_of and source_layer: the fund, the date of its holdings, and where they were read, issuer for the issuer's own file, sec_nport for 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_kind and listed_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

  • security and fund: 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_of and source_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_positions and value_musd are empty.

/api/v1/funds.csv

  • ticker, name, source_layer, issuer and category.
  • expense_ratio_pct and aum_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.

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.