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
ResultOne 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_capMarket cap, US$ millions · definitionmarket_cap_ageMarket cap age, days · definitionrevenueRevenue, US$ millions · definitionttm_revenueRevenue TTM, US$ millions · definitionnet_incomeNet income, US$ millions · definitionttm_net_incomeNet income TTM, US$ millions · definitionoperating_incomeOperating income, US$ millions · definitiontotal_assetsTotal assets, US$ millions · definitiontotal_liabilitiesTotal liabilities, US$ millions · definitiontotal_equityEquity, US$ millions · definitionemployeesEmployees, countrevenue_per_employeeRevenue per employee, US dollars · definitionprofit_per_employeeProfit per employee, US dollars · definitionrankWorld rank, count · definition
Valuation
pe_ratioP/E, times · definitionps_ratioPrice to sales, times · definitionpb_ratioPrice to book, times · definitionfcf_yieldFree cash flow yield, percent · definition
Profitability
gross_marginGross margin, percent · definitionoperating_marginOperating margin, percent · definitionnet_marginNet margin, percent · definitionroeReturn on equity, percent · definitionroaReturn on assets, percent · definitionroicReturn on invested capital, percent · definitionasset_turnoverAsset turnover, times · definition
Growth
revenue_growthRevenue growth, percent · definitionrevenue_cagr_3yRevenue growth 3 years, percent · definitionrevenue_cagr_5yRevenue growth 5 years, percent · definitionnet_income_growthNet income growth, percent · definitioneps_growthEPS growth, percent · definition
Financial health
debt_to_equityDebt to equity, times · definitionnet_debtNet debt, US$ millions · definitionnet_debt_to_ebitdaNet debt to EBITDA, times · definitioninterest_coverInterest cover, times · definitioncurrent_ratioCurrent ratio, times · definitionequity_ratioEquity ratio, percent · definition
Cash flow
fcfFree cash flow, US$ millions · definitionfcf_marginFree cash flow margin, percent · definitioncash_conversionCash conversion, percent · definitioncapex_to_revenueCapital spending to revenue, percent · definition
Shareholder returns
dividend_yieldDividend yield, percent · definitionbuyback_yieldBuyback yield, percent · definitionshareholder_yieldShareholder yield, percent · definitionpayout_ratioDividend payout, percent · definitionshare_changeShare count change, percent · definition
Scores
fiscal_yearFiscal year, year · definitionf_scorePiotroski F-score, points · definitionz_scoreAltman Z-score, times · definitionscorecardScorecard, points · definitionscorecard_knownScorecard checks known, count · definitionscore_valuationValuation score, points · definitionscore_growthGrowth score, points · definitionscore_profitabilityProfitability score, points · definitionscore_healthFinancial health score, points · definitionscore_returnsShareholder returns score, points · definition
Ownership and trading
institutional_ownershipInstitutional ownership, percent · definitioninstitutional_holdersInstitutional holders, count · definitiondividend_streakDividend growth years, count · definitioninsider_buyingInsider buying, yes or no · definition
Company
founded_yearFounded, yearipo_yearIPO 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.