Screener Weekly Summary What's new Pricing

Live stock data in Google Sheets and Excel

Every figure on stockrow can be a live cell in your own spreadsheet: ten years of statements, any metric’s history, a grid of your companies, your watchlist and your saved screens. The recipes below are copy-and-paste; they refresh on their own, and they keep working as long as your Powerpack does.

Get your API key Part of Powerpack. Your keys are on your account page.

First, your key

Make a key on your account’s API page and paste it into one cell of your sheet — a sheet called Settings, cell B1. Every formula below reads it from there, so changing the key is one edit.

Before you share a spreadsheet, make a copy (File → Make a copy) and delete the key from the copy. Anyone with the key can read your data feed — never your account — until you roll it on the API page.

Google Sheets

IMPORTDATA reads a CSV address into the cells below and to the right of the formula.

A company’s ten-year income statement

With the ticker in A1:

=IMPORTDATA("https://stockrow.com/api/v1/companies/"&A1&"/statements/income-statement.csv?period=annual&key="&Settings!B1)

Use balance-sheet, cash-flow or metrics-ratios for the others, and period=quarterly or period=ttm for quarters and trailing twelve months.

One metric’s history

=IMPORTDATA("https://stockrow.com/api/v1/companies/AAPL/metrics/pe-ratio.csv?key="&Settings!B1)

A grid of your companies

With tickers in A2:A50, one request for the whole grid — which is kinder to Google’s limits than a formula per cell: 

=IMPORTDATA("https://stockrow.com/api/v1/metrics/latest.csv?tickers="&TEXTJOIN(",",TRUE,A2:A50)&"&metrics=price,market-cap,pe-ratio,dividend-yield&key="&Settings!B1)

The metric names are slugs; /api/v1/indicators lists them all, and any indicator’s lower-cased code works too. A ticker stockrow does not cover gets an empty row, so one delisted company does not break the sheet.

Your stockrow watchlist

=IMPORTDATA("https://stockrow.com/api/v1/watchlist.csv?key="&Settings!B1)

A saved screen

The screen’s address is on https://stockrow.com/api/v1/screens.csv — one row per saved screen, with its results_url: 

=IMPORTDATA("https://stockrow.com/api/v1/screens/<id>/results.csv?key="&Settings!B1)

One cell: the latest revenue

Look a line up by its code, never by its row number — rows can be added between others:

=INDEX(IMPORTDATA(Settings!B2), MATCH("REVENUE", INDEX(IMPORTDATA(Settings!B2),,1), 0), 4)

where Settings!B2 holds the statement’s address from the first recipe. Column 4 is always the newest period.

What Google does

Google refreshes IMPORTDATA about once an hour while the spreadsheet is open, fetches from its own servers (which is why the key is in the address rather than a header), caps how many imports one spreadsheet may hold and how large each may be, and asks you to allow access the first time. The exact limits are Google’s and change; we are checking them against Google’s current help and will state them here.

Excel

Power Query is the way: Data → Get Data → From Web, paste a CSV address from above with your key, and Load. Then in Query Properties, tick “Refresh every 60 minutes” and “Refresh data when opening the file”.

To keep the key and the ticker in cells, name two cells ApiKey and Ticker and use this query (Home → Advanced Editor): 

let key = Excel.CurrentWorkbook(){[Name="ApiKey"]}[Content]{0}[Column1],
    t   = Excel.CurrentWorkbook(){[Name="Ticker"]}[Content]{0}[Column1],
    src = Csv.Document(Web.Contents("https://stockrow.com/api/v1/companies/" & t &
          "/statements/income-statement.csv", [Query=[period="annual", key=key]]),
          [Delimiter=",", Encoding=65001])
in  Table.PromoteHeaders(src)

Power Query’s From Web is in Excel for Windows and Microsoft 365; we are checking Excel for Mac and Excel on the web and will say here what works.

WEBSERVICE and FILTERXML are not supported: they work only in Excel for Windows, cut an answer at 32,767 characters and cannot read CSV. Use Power Query.

The layout contract

The statement CSV and the Excel export (“layout 2”) are laid out the same way, and will stay so, so a model built on them does not break:

  • Column A is the line’s code, B its label, C its unit, and D onwards a period each — D is always the latest period.
  • Row 1 is the header: code, label, unit and the period end dates.
  • Numbers are numbers: cash in raw dollars, percentages as fractions; a missing value is an empty cell.
  • A row is identified by its code. Codes are never renamed or reordered against each other, but new lines can appear between them — so look lines up by code: =INDEX(IS_A!$D:$D, MATCH("REVENUE", IS_A!$A:$A, 0)).
  • The workbook’s sheets are always Info, IS_A, IS_Q, IS_T, BS_A, BS_Q, CF_A, CF_Q, CF_T, MR_A, MR_Q and MR_T, in that order, with the panes frozen at D2. Info says the layout version, 2.

Any change to this is a new layout version, asked for by a new parameter; layout 2 does not change.

Templates

Two ready-made workbooks: a ten-year model of one company — the statements, margins, growth, free cash flow and its valuation against its own history — and a dashboard of up to fifty companies. Put your key in Settings!B1 and they fill themselves in.