Skip to content
value.today

Google Sheets and Excel

Company figures in your own spreadsheets, by formula, each with the date it belongs to. Bloomberg, FactSet and Capital IQ sell this as an Excel add-in; here it works in Google Sheets too.

1. Get a key

Make one on API keys and usage (signed in), or ask for one on the developers page. A team key shares the team's requests a day and can read the team's lists: list=<number> instead of companies=.

Each formula is one request; Google Sheets keeps answers for an hour, so a sheet refreshed all day stays well inside a free key's 1,000 requests.

2. Google Sheets: three functions

Extensions > Apps Script, paste, set the key, save. Nothing to install.

// value.today for Google Sheets: Extensions > Apps Script, paste this, put your key below, save.
// Then in any cell: =VT("AAPL", "revenue")   =VT_TABLE("AAPL,MSFT,NESN.SW", "market_cap,revenue,roe")   =VT_HISTORY("AAPL", "revenue", 10)
const VT_API_KEY = "vt_your_key";  // everyone who can open this script sees the key: use a team key and withdraw it if it leaks
const VT_BASE = "https://www.value.today/api/v1/sheets/";

function vtGet_(path, params) {
  const query = Object.keys(params).map(function (k) { return k + "=" + encodeURIComponent(params[k]); }).join("&");
  const url = VT_BASE + path + "?" + query;
  const cache = CacheService.getScriptCache();
  const hit = cache.get(url);
  if (hit) return JSON.parse(hit);
  const res = UrlFetchApp.fetch(url, { headers: { "X-API-Key": VT_API_KEY }, muteHttpExceptions: true });
  const body = res.getContentText();
  if (res.getResponseCode() !== 200) {
    let msg = "HTTP " + res.getResponseCode();
    try { msg = JSON.parse(body).error || msg; } catch (e) {}
    throw new Error(msg);
  }
  if (body.length < 90000) cache.put(url, body, 3600);  // an hour: figures change daily at most
  return JSON.parse(body);
}

function vtList_(v) {
  return Array.isArray(v) ? v.reduce(function (a, r) { return a.concat(r); }, []).filter(String).join(",") : String(v);
}

function vtCells_(rows) {
  return rows.map(function (r) { return r.map(function (v) { return v === null ? "" : v; }); });
}

/**
 * The latest value of one figure for one company.
 * @param {string} company A ticker, ISIN, CIK, LEI, name or value.today slug.
 * @param {string} field A figure: market_cap, revenue, roe, pe_ratio... (the list is on value.today/developers/spreadsheets).
 * @customfunction
 */
function VT(company, field) {
  const d = vtGet_("values", { companies: company, fields: field });
  return d.rows.length ? d.rows[0].values[d.fields[0].key] : "not found";
}

/**
 * Companies by figures, with a header row, each row with its market-cap date and fiscal year.
 * @param {string|Array} companies Comma-separated, or a range of cells.
 * @param {string|Array} fields Comma-separated, or a range of cells.
 * @customfunction
 */
function VT_TABLE(companies, fields) {
  return vtCells_(vtGet_("values", { companies: vtList_(companies), fields: vtList_(fields), as: "table" }).table);
}

/**
 * One yearly figure of one company, year by year, with its period end and source.
 * @param {string} company A ticker, ISIN, CIK, LEI, name or value.today slug.
 * @param {string} field revenue, net_income, fcf, roe, eps_diluted...
 * @param {number} years How many years (up to 30).
 * @customfunction
 */
function VT_HISTORY(company, field, years) {
  return vtCells_(vtGet_("history", { company: company, field: field, years: years || 10, as: "table" }).table);
}
  • =VT("AAPL", "revenue"): one figure, the latest.
  • =VT_TABLE(A2:A50, "market_cap,revenue,roe"): a table with a header row, the market-cap date and fiscal year of each row.
  • =VT_HISTORY("MSFT", "fcf", 10): ten years of free cash flow, each with its period end and source.

3. Excel: a query that refreshes

Power Query, in Excel for Windows, Mac and Microsoft 365. Data > Refresh All updates it.

let
    // Excel: Data > Get Data > From Other Sources > Blank Query > Advanced Editor, paste, then Close & Load.
    // When Excel asks how to connect, choose Anonymous: the key travels in the header below.
    Key = "vt_your_key",
    Source = Json.Document(Web.Contents("https://www.value.today/api/v1/sheets/values", [
        Query = [companies = "AAPL,MSFT,NESN.SW,7203.T", fields = "market_cap,revenue,operating_margin,roe,pe_ratio", as = "table"],
        Headers = [#"X-API-Key" = Key]
    ])),
    Rows = Source[table],
    Result = Table.FromRows(List.Skip(Rows, 1), List.First(Rows))
in
    Result

One value in a cell, in Excel for Windows (the key is visible in the cell, so keep the file to your team):

=WEBSERVICE("https://www.value.today/api/v1/sheets/values?companies=AAPL&fields=revenue&as=text&api_key=vt_your_key")

From anywhere else

curl -H "X-API-Key: vt_your_key" "https://www.value.today/api/v1/sheets/values?companies=AAPL,MSFT&fields=market_cap,revenue,roe&as=table"

Fields

Use the key (or the label) in fields=. Amounts are US$ millions, percentages plain numbers (15 means 15%).

Size

  • market_cap Market cap, US$ millions · definition
  • market_cap_age Market cap age, days · definition
  • revenue Revenue, US$ millions · definition
  • ttm_revenue Revenue TTM, US$ millions · definition
  • net_income Net income, US$ millions · definition
  • ttm_net_income Net income TTM, US$ millions · definition
  • operating_income Operating income, US$ millions · definition
  • total_assets Total assets, US$ millions · definition
  • total_liabilities Total liabilities, US$ millions · definition
  • total_equity Equity, US$ millions · definition
  • employees Employees, count
  • revenue_per_employee Revenue per employee, US dollars · definition
  • profit_per_employee Profit per employee, US dollars · definition
  • rank World rank, count · definition

Valuation

Profitability

Growth

  • revenue_growth Revenue growth, percent · definition
  • revenue_cagr_3y Revenue growth 3 years, percent · definition
  • revenue_cagr_5y Revenue growth 5 years, percent · definition
  • net_income_growth Net income growth, percent · definition
  • eps_growth EPS growth, percent · definition

Financial health

Cash flow

  • fcf Free cash flow, US$ millions · definition
  • fcf_margin Free cash flow margin, percent · definition
  • cash_conversion Cash conversion, percent · definition
  • capex_to_revenue Capital spending to revenue, percent · definition

Shareholder returns

  • dividend_yield Dividend yield, percent · definition
  • buyback_yield Buyback yield, percent · definition
  • shareholder_yield Shareholder yield, percent · definition
  • payout_ratio Dividend payout, percent · definition
  • share_change Share count change, percent · definition

Scores

  • fiscal_year Fiscal year, year · definition
  • f_score Piotroski F-score, points · definition
  • z_score Altman Z-score, times · definition
  • scorecard Scorecard, points · definition
  • scorecard_known Scorecard checks known, count · definition
  • score_valuation Valuation score, points · definition
  • score_growth Growth score, points · definition
  • score_profitability Profitability score, points · definition
  • score_health Financial health score, points · definition
  • score_returns Shareholder returns score, points · definition

Ownership and trading

  • institutional_ownership Institutional ownership, percent · definition
  • institutional_holders Institutional holders, count · definition
  • dividend_streak Dividend growth years, count · definition
  • insider_buying Insider buying, yes or no · definition

Company

  • founded_year Founded, year
  • ipo_year IPO year, year

Year by year (VT_HISTORY, field=)

revenue, gross_profit, operating_income, net_income, rnd_expense, employees, operating_cash_flow, capex, fcf, dividends_paid, buybacks, cash, total_debt, net_debt, total_assets, total_equity, gross_margin, operating_margin, net_margin, fcf_margin, roe, roa, revenue_growth, debt_to_equity, eps_diluted, dividends_per_share, revenue_per_employee, market_cap, pe_ratio

What each answer carries

  • The company the input matched (the largest, when a ticker is used on several exchanges), and inputs that matched nothing.
  • The market cap's date: ratios built on the market value are as current as it.
  • The fiscal year of the statements behind each figure.
  • Year-by-year figures name their source: SEC, ESEF, the editors or Wikidata citing the annual report.

Not included

Live prices (they need an exchange licence), short interest (FINRA's terms do not allow passing it on) and IMF series. Figures are for information, not investment advice; show "Data: value.today" when you publish them.