US company financials (SEC EDGAR)

lhbox duckdb --catalog commons/pubdata

SELECT ticker, company_name,
       round(max(value_usd) FILTER (WHERE metric = 'revenue') / 1e9, 2) AS revenue_bn_usd,
       round(max(value_usd) FILTER (WHERE metric = 'net_income') / 1e9, 2) AS net_income_bn_usd,
       round(max(value_usd) FILTER (WHERE metric = 'rd_expense') / 1e9, 2) AS rd_bn_usd
FROM commons_pubdata.population.sec_financials
WHERE fiscal_year = 2024 AND ticker IS NOT NULL
GROUP BY 1, 2
ORDER BY net_income_bn_usd DESC NULLS LAST LIMIT 15;

INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs;
CREATE SECRET pubdata (TYPE S3, ENDPOINT 'lakehousebox-public-data.b-cdn.net', URL_STYLE 'path', REGION 'us-east-1');
ATTACH ':memory:' AS pubdata;
CREATE SCHEMA pubdata.population;
CREATE VIEW pubdata.population.sec_financials AS SELECT * FROM iceberg_scan('s3://commons--pubdata/population/sec_financials');

SELECT ticker, company_name,
       round(max(value_usd) FILTER (WHERE metric = 'revenue') / 1e9, 2) AS revenue_bn_usd,
       round(max(value_usd) FILTER (WHERE metric = 'net_income') / 1e9, 2) AS net_income_bn_usd,
       round(max(value_usd) FILTER (WHERE metric = 'rd_expense') / 1e9, 2) AS rd_bn_usd
FROM pubdata.population.sec_financials
WHERE fiscal_year = 2024 AND ticker IS NOT NULL
GROUP BY 1, 2
ORDER BY net_income_bn_usd DESC NULLS LAST LIMIT 15;

# US company financials (SEC EDGAR)

*Annual revenue, profit, R&D, assets, liabilities, equity and cash for ~7,000 US-listed companies.*

Annual US GAAP financial statement fundamentals for ~7,000 US-listed public companies from 2019 to the latest filings, assembled directly from the U.S. Securities and Exchange Commission (SEC) EDGAR XBRL Frames API and company ticker registry. Covers eight core income statement and balance sheet metrics in US Dollars (`revenue`, `operating_income`, `net_income`, `rd_expense`, `assets`, `liabilities`, `equity`, `cash`) with the company's Central Index Key (`cik`), stock `ticker`, entity name, headquarters `country_code` and state/region, underlying US-GAAP XBRL tag, `fiscal_year`, `period_end` date and SEC filing accession number.

## At a glance

| | |
|---|---|
| Table | `pubdata.population.sec_financials` |
| Category | Population · Economy, Time series, Country |
| Rows | n/a |
| Size | n/a data file(s) |
| Iceberg format | v? |
| Last refreshed | not loaded yet |
| Source version | n/a |
| Refresh cadence | weekly (replace) |
| Coverage | ~7,000 SEC-reporting public companies (US and foreign issuers filing in USD under US GAAP); fiscal years 2019 to present, refreshed weekly |

## Origin

- **Source:** [U.S. Securities and Exchange Commission (SEC), EDGAR XBRL Frames API](https://www.sec.gov/edgar/sec-api-documentation)
- **Licence:** Public domain (US government work)
- **Attribution:** Source: U.S. Securities and Exchange Commission (SEC) EDGAR XBRL disclosures, public domain.
- **Recipe:** `pubdata/recipes/sec_financials.py` in the LakehouseBox repository; registry entry `pubdata/registry/sec_financials.yaml`.

## Schema

| column | type | description |
|---|---|---|
| `cik` | int | SEC Central Index Key (unique numeric registrant identifier) |
| `ticker` | string | primary stock exchange ticker symbol (e.g. AAPL, MSFT, NVDA) |
| `company_name` | string | registrant name as reported to the SEC |
| `country_code` | string | ISO 3166-1 alpha-2 headquarters country code (US for US- domiciled filers; joins reference.countries) |
| `state_or_region` | string | SEC location code (e.g. US-CA, US-NY, CA-ON) |
| `metric` | string | normalized metric name: revenue, operating_income, net_income, rd_expense, assets, liabilities, equity or cash |
| `gaap_tag` | string | underlying US-GAAP XBRL concept tag reported in the filing |
| `fiscal_year` | int | calendar-aligned fiscal year (2019..present) |
| `period_end` | date | fiscal period end date of the reported figure |
| `value_usd` | double | reported figure in US Dollars (exact USD, not thousands or millions) |
| `accession_number` | string | SEC EDGAR filing accession number |
| `form` | string | SEC form type (e.g. 10-K, 10-K/A, 20-F, 40-F) |

**Join keys:** `country_code`, `ticker`, `cik`.

## For agents

One row per `(cik, metric, fiscal_year)`. When a company reports both `RevenueFromContractWithCustomerExcludingAssessedTax`
and `Revenues`, the recipe keeps one row per `(cik, 'revenue', fiscal_year)` (preferring ASC 606 contract revenue,
then general `Revenues`, then `SalesRevenueNet`). `value_usd` is in raw US Dollars (divide by `1e9` for billions).

## Example SQL (DuckDB)

```sql
-- top 15 companies by net income in 2024 (pivoting revenue, net income and R&D into columns)
SELECT ticker, company_name,
       round(max(value_usd) FILTER (WHERE metric = 'revenue') / 1e9, 2) AS revenue_bn_usd,
       round(max(value_usd) FILTER (WHERE metric = 'net_income') / 1e9, 2) AS net_income_bn_usd,
       round(max(value_usd) FILTER (WHERE metric = 'rd_expense') / 1e9, 2) AS rd_bn_usd
FROM pubdata.population.sec_financials
WHERE fiscal_year = 2024 AND ticker IS NOT NULL
GROUP BY 1, 2
ORDER BY net_income_bn_usd DESC NULLS LAST LIMIT 15;
-- multi-year revenue and net margin trajectory for Apple, Microsoft, Alphabet and NVIDIA
SELECT ticker, fiscal_year,
       round(max(value_usd) FILTER (WHERE metric = 'revenue') / 1e9, 1) AS revenue_bn,
       round(max(value_usd) FILTER (WHERE metric = 'net_income') / 1e9, 1) AS net_income_bn
FROM pubdata.population.sec_financials
WHERE ticker IN ('AAPL', 'MSFT', 'GOOGL', 'NVDA')
GROUP BY 1, 2 ORDER BY ticker, fiscal_year;
```

## How to read it

With a LakehouseBox account:

```bash
# with a LakehouseBox account (any organisation): the public catalog by name, read-only
lhbox duckdb --catalog commons/pubdata     # opens DuckDB attached as `commons_pubdata`
SELECT * FROM commons_pubdata.population.sec_financials LIMIT 10;
```

Public URL of the table's storage: `https://s3.lakehousebox.com/commons--pubdata/population/sec_financials/`. This card: `https://lakehousebox.com/pubdata/cards/sec_financials.md`; the page: https://lakehousebox.com/public-data/sec_financials. Every dataset as a table: `commons_pubdata.meta.datasets`; as Markdown: https://lakehousebox.com/public-data.md.