Metadata-Version: 2.4
Name: valuein-sdk
Version: 4.0.0
Summary: Official Python SDK for the Valuein US Core Fundamentals dataset — SEC EDGAR financials via API.
Project-URL: Homepage, https://valuein.biz
Project-URL: Documentation, https://valuein.biz/docs
Project-URL: Repository, https://github.com/valuein/sdk
Project-URL: Bug Tracker, https://github.com/valuein/sdk/issues
Project-URL: Changelog, https://github.com/valuein/sdk/blob/main/CHANGELOG.md
Author-email: Valuein <support@valuein.biz>
License: Apache-2.0
License-File: LICENSE
License-File: NOTICE
Keywords: EDGAR,SEC,backtesting,duckdb,finance,fundamentals,parquet,quant
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: Financial and Insurance Industry
Classifier: Intended Audience :: Science/Research
Classifier: License :: OSI Approved :: Apache Software License
Classifier: Natural Language :: English
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Office/Business :: Financial :: Investment
Classifier: Topic :: Scientific/Engineering :: Information Analysis
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Classifier: Typing :: Typed
Requires-Python: >=3.10
Requires-Dist: duckdb<1.5,>=1.4.4
Requires-Dist: httpx[http2]>=0.27.0
Requires-Dist: jinja2>=3.1.6
Requires-Dist: pandas>=2.2.3
Requires-Dist: pyarrow>=18.0.0
Requires-Dist: pydantic>=2.9.0
Requires-Dist: python-dotenv>=1.0.0
Provides-Extra: all
Requires-Dist: matplotlib>=3.8.0; extra == 'all'
Requires-Dist: numpy>=1.26.0; extra == 'all'
Requires-Dist: opentelemetry-api>=1.20.0; extra == 'all'
Requires-Dist: polars>=0.20.0; extra == 'all'
Provides-Extra: otel
Requires-Dist: opentelemetry-api>=1.20.0; extra == 'otel'
Provides-Extra: polars
Requires-Dist: polars>=0.20.0; extra == 'polars'
Provides-Extra: research
Requires-Dist: ipykernel>=6.29.0; extra == 'research'
Requires-Dist: jupytext>=1.16.0; extra == 'research'
Requires-Dist: matplotlib>=3.8.0; extra == 'research'
Requires-Dist: numpy>=1.26.0; extra == 'research'
Provides-Extra: test
Requires-Dist: pytest-cov>=4.1.0; extra == 'test'
Requires-Dist: pytest>=9.0.0; extra == 'test'
Description-Content-Type: text/markdown

[![Valuein Logo](https://www.valuein.biz/valuein/twitter-rounded.png)](https://valuein.biz)

[![PyPI version](https://img.shields.io/pypi/v/valuein-sdk?cacheSeconds=300)](https://pypi.org/project/valuein-sdk/)
[![Python 3.10+](https://img.shields.io/badge/python-3.10%2B-blue)](https://pypi.org/project/valuein-sdk/)
[![License](https://img.shields.io/badge/license-Apache%202.0-green)](LICENSE)

# 💎 Valuein Python SDK: Frictionless Financial Data
A high-performance toolkit for querying point-in-time US fundamentals from SEC EDGAR, built for quants, analysts, and data engineers.

The Valuein SDK, is a complete infrastructure solution for consuming point-in-time accurate US Core financial fundamentals (facts) on your daily workflow. Whether you are building complex asynchronous Python pipelines or executing templated SQL, this library provides frictionless, zero-setup access to institutional-grade data.

## The Data Engine
Powered by survivorship-bias-free data containing 12M+ filings and 111M+ facts from 10-Ks, 10-Qs, 8-Ks, 20-Fs, 40-Fs, and amendments across 19,000+ active and delisted US companies since 1993 (the SEC EDGAR electronic-filing floor).

## Why use this toolkit?

⚡ Lightning-Fast Python SDK: Execute blazing-fast queries against remote Parquet files hosted on R2, powered entirely by DuckDB under the hood. No database setup, no massive local downloads.

🛠️ Plug-and-Play SQL Templates: Skip the boilerplate. Use our pre-built SQL templates to immediately extract insights, calculate intrinsic values, or model standardized financial statements.

📚 Comprehensive Context: Deep-dive documentation mapping out table schemas, primary keys, and field definitions to support your specific financial research use cases.

## 🚀 Why Valuein Data and SDK Library
> Easy of use and intelligence.

| Feature                               | Benefit                                                    |
|---------------------------------------|-----------------------------------------------------------|
| 🕒 **<span title="Provides historical snapshots for accurate backtesting">Point‑in‑Time Data</span>** | Eliminate look‑ahead bias in backtests |
| ⚖️ **<span title="Includes companies that went bankrupt, delisted, or were acquired">Survivorship‑Bias Free</span>** | Includes bankrupt, delisted, and acquired firms |
| 📊 **<span title="Maps ~11,966 raw XBRL tags to ~292 standard financial concepts">Standardized Concepts</span>** | ~11,966 raw XBRL tags mapped to ~292 canonical financial concepts |
| 🚀 **<span title="High-speed in-memory SQL engine using DuckDB">DuckDB SQL Engine</span>** | Millisecond analytics directly in Python |
| ☁️ **<span title="Stream Parquet data from cloud storage without downloading locally">Cloud Parquet Streaming</span>** | No local data downloads required |
| 🧩 **<span title="Ready-to-use financial templates for backtesting and signals">Financial Templates</span>** | Production‑ready investment signals |
| 🔬 **<span title="Universe, signal panel, forward returns and quantile backtest with bias closed by construction">Backtest Engine</span>** | Evaluate a factor in one call — look-ahead and survivorship closed by construction |
| 🎲 **<span title="GBM, correlated GBM, i.i.d. and stationary block bootstrap, VaR/CVaR">Monte Carlo</span>** | Seeded, reproducible simulation on your own strategy's returns |
| 🔒 **<span title="Materialize a point-in-time slice with a SHA-256 per file and replay it offline">Reproducible Bundles</span>** | Freeze a study's inputs and hash them, so results stay traceable |


## 🧠 What You Can Do With This Repository

| Use Case | Who | Where to Start |
|---|---|---|
| **Query financial data via Python** | Quants, data engineers | [Quickstart](#-quickstart) |
| **Call every SDK method** | AI agents, integrators | [API Reference](#-api-reference) |
| **Backtest a factor without bias** | Quants, PMs | [Quant Cookbook](docs/quant-cookbook.md) |
| **Run 58 pre-built financial signals** | Analysts, quants | [SQL Templates](#-template-categories) |
| **Learn with interactive notebooks** | Students, new users | [Python Examples](#-python-examples) |
| **Prove data quality to stakeholders** | Institutional buyers, compliance | [Research & Quality Proofs](#-research--quality-proofs) |
| **Read methodology and compliance docs** | Due diligence, enterprise | [Documentation](#-documentation) |
| **Contribute templates, examples, research** | Open-source contributors | [Contributing](#-contributing) |

---

## ⚡ Quickstart

**1. Install Package**

Pick the workflow you already use — both work, no extra setup:

``` bash
# Option A — pip (universal, ships with Python)
python -m venv .venv && source .venv/bin/activate
pip install valuein-sdk
```

``` bash
# Option B — uv (10–100× faster; install from https://docs.astral.sh/uv/)
uv venv && source .venv/bin/activate
uv pip install valuein-sdk
```

**2. Backtest a factor — no token, no signup, one command**

```bash
valuein backtest mom_12m --start 2021-01-01 --end 2025-12-31 --freq ME
```

```
Backtest — 'mom_12m' | long/short | top/bottom 5 quantiles | equal-weighted
  execution lag 1 trading day(s), costs 10.0 bps per unit traded, 11.78 rebalances/yr

NET OF COSTS
  Periods             40 @ 11.78/yr
  Annualized return   -2.04%
  Annualized vol      15.41%
  Sharpe              -0.06
  Max drawdown        -23.64%
  Hit rate            62.5%

  gross annualized   -0.87%   (cost drag 1.17%)
  mean IC            +0.0076   IC-IR 0.04
  mean turnover      50.4% one-way per rebalance
  universe return    +1.22% per period (equal-weight, same universe)

EXCLUSIONS
  dates used 40 of 52 — 12 skipped for thin coverage
  names scored 450/date (71% of universe had a usable signal)
  positions truncated by delisting: 0
```

**That is a real run on the free tier, cold, in under 7 seconds** — and it is
a real answer: on the 500-name sample slice, 12-month momentum did not pay
after costs. The point is that you found that out in one command instead of
in two weeks, and that the assumptions are printed next to the number. *A
Sharpe ratio quoted without its execution lag and cost assumption is not a
result.*

The `EXCLUSIONS` block is there for the same reason. It tells you what the
backtest **could not** use — thin dates, unscored names, positions truncated
by a delisting — because a study that silently drops what it cannot handle
reports the survivors' returns and calls them the strategy's.

Same thing from Python, plus the diagnostics:

```python
from valuein_sdk import ValueinClient

with ValueinClient() as client:  # no API key needed
    result = client.factor_backtest(
        "mom_12m",
        start="2021-01-01",
        end="2025-12-31",
        freq="ME",
    )
    print(result.summary())
    print(result.mean_ic, result.ic_ir)  # signal quality
    print(result.quantile_summary())  # is it monotonic?
    result.equity_curve().plot()
```

Look-ahead and survivorship bias are closed **by construction**, not by
convention: the universe comes from index-membership spells so delisted
companies are present on the dates they were members, and every fundamental
is filtered to `accepted_at <= as_of`, the moment the SEC actually published
it. You cannot accidentally turn them off.

→ **[Full quickstart](docs/quickstart.md)** · **[Quant cookbook](docs/quant-cookbook.md)**

Add a token at any time to widen the universe from 500 names to 19,000+ —
the code above does not change.

## 🔑 2. Get Your API Token

| Data Plan                  | Coverage                                                                                                                                                          | Price             | Get Access                                       |
|----------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------|-------------------|--------------------------------------------------|
| **Sample**                 | S&P 500 universe, last 5 years<br>Active & delisted companies                                                                                                     | **Free**          | No registration                                  |
| **S&P 500 (Free)**         | Full S&P 500 universe, full history (1993 → present)<br>Active & delisted companies                                                                               | **Free**          | [Register](https://valuein.biz/signup/free)      |
| **Pro**                    | Full US universe (19,000+ entities, active + delisted)<br>15-year rolling point-in-time window (2011 → present)<br>10-K, 10-Q, 8-K, 20-F + amendments<br>Individual / single-seat license, no redistribution | **$49 / mo** · **$490 / yr** | [Subscribe](https://valuein.biz/checkout?tier=pro) |
| **Institutional**          | Everything in Pro **plus** the smart-money dataset (Forms 3/4/5/144 + 13F/13D/13G), foreign issuers, full history back to 1993, intraday `accepted_at`, filing-event webhooks, commercial redistribution license, business-hours SLA | **$499 / mo** · **$4,790 / yr** | [Subscribe](https://valuein.biz/checkout?tier=full) |
| **Enterprise** (custom)    | Dedicated infrastructure, zero-retention option, sub-minute filing push, multi-seat team access, white-label resale, bespoke SLA                                  | **Talk to sales** | [Contact](https://valuein.biz/enterprise)        |


## 🔐 3. Set Your API Token

```bash
# optional — sample tier works without a key
echo 'VALUEIN_API_KEY="your_token"' >> .env
```

## ▶️ 4. Production-ready code

The ValueinClient handles authentication, table discovery, and local caching in a high-performance DuckDB instance.

The Recommended Way For Production is the Context Manager block/pattern because it ensures that temporary files and database connections are closed automatically, even if your script crashes.

```python
from valuein_sdk import ValueinClient, ValueinError

# Two-level try/except is intentional:
#   outer = init errors (auth, manifest fetch, gateway 503 at __enter__)
#   inner = per-query errors (rate-limit, plan denial, bad SQL, validation)

try:
    with ValueinClient() as client:
        try:
            # 1) Build & run a raw SQL query → pandas DataFrame
            sql = "SELECT COUNT(cik) FROM entity"
            result_df = client.run_query(sql)
            print(result_df)

            # 2) Run a named SQL template with kwargs (the SDK quotes safely)
            df = client.run_template(
                "fundamentals_by_ticker",
                ticker="AAPL",
                start_date="2020-01-01",
                end_date="2024-01-01",
                form_types=["10-K", "10-Q"],
                metrics=["TotalRevenue", "NetIncome", "OperatingCashFlow"],
            )
            print(df)
        except ValueinError as ve:
            print(f"Query failed: {ve}")
except Exception as e:
    print(f"Initialization failed: {e}")
```

---

## 🧰 API Reference

Everything below is importable from the top-level package (`from valuein_sdk import ...`)
and fully type-hinted with `help()`-ready docstrings — so an LLM reading the
signatures can call it correctly on the first try.

### `ValueinClient(as_of=None, api_key=None, gateway_url=None, tables=None, config=None)`

| Method | Returns | What it does |
|---|---|---|
| **Querying** | | |
| `run_query(sql)` | `DataFrame` | Run read-only DuckDB SQL (SELECT / WITH / EXPLAIN only). PIT-filtered via the views. |
| `run_template(name, **params)` | `DataFrame` | Run a named SQL template from `queries/` with validated, injection-safe params. |
| `read_table(table, *, columns=None, limit=None)` | `DataFrame` | **Recommended** whole-table read — PIT-safe, loads on demand, optional column/row subset. |
| `get(table)` | `DataFrame` | Lower-level: download a table's raw Parquet (does **not** apply the `as_of` filter). |
| `to_arrow(sql)` | `pyarrow.Table` | Zero-copy Arrow output (Spark/Polars hand-off). |
| `to_polars(sql)` | `polars.DataFrame` | Zero-copy Polars output (needs `pip install valuein-sdk[polars]`). |
| `stream(sql, batch_size=10_000)` | `Iterator[DataFrame]` | Chunked iteration for results too large to materialize. |
| **Typed helpers** | | |
| `factor_scores(ticker=None, sector=None, min_composite_rank=None, limit=100)` | `DataFrame` | Cross-sectional factor scores + percentile ranks. |
| `earnings_signals(ticker=None, min_surprise_pct=None, direction=None, limit=100)` | `DataFrame` | EPS trend / surprise + YoY revenue change. |
| `pit_universe(as_of_date, index="SP500", as_of_basis="effective")` | `DataFrame` | Survivorship-free index members on a historical date. |
| **Quant research** *(see the [cookbook](docs/quant-cookbook.md))* | | |
| `factor_backtest(signal, *, start, end, freq="ME", ...)` | `BacktestResult` | Universe → signal → forward returns → quantile backtest, in one call. Look-ahead-free and survivorship-free by construction. |
| `universe(index_name="SP500", *, start, end, freq)` | `DataFrame` | Index membership resolved **at each** rebalance date — delisted and acquired companies included. |
| `signal_panel(universe, *, ratios=, concepts=, factors=, momentum_months=)` | `DataFrame` | `(date × member)` panel where every value is the latest vintage with `accepted_at <= date`. |
| `forward_returns(panel, *, execution_lag=1)` | `DataFrame` | Realized return to the next rebalance, delisting-aware (`truncated` / `entry_stale` flags). |
| `trading_days(start, end)` / `rebalance_dates(start, end, freq=)` | `DatetimeIndex` / `list[str]` | Calendars derived from the actual bars — no hardcoded holiday table. |
| `write_bundle(path, *, tables, ...)` | `BundleManifest` | Freeze a PIT slice to Parquet with a SHA-256 per file. |
| `ValueinClient.from_bundle(path)` | `ValueinClient` | Replay a bundle offline; every hash verified before mounting. |
| **Market data** | | |
| `prices(tickers)` | `PriceQuery` | Lazy daily-bar builder — `.between()`, `.fields()`, `.last()`, `.to_pandas()/.to_arrow()/.to_polars()`. |
| `total_return(ticker, start, end, *, basis="total_return")` | `float` | Splits **and** dividends, counted once. |
| `price_panel(tickers, start, end, *, field="total_return")` | `DataFrame` | Wide matrix, one column per ticker. |
| `pit_panel(tickers, dates, *, concepts=)` | `DataFrame` | Last knowable price + fundamentals per `(ticker, date)` cell. |
| `price_health()` | `dict` | Coverage and integrity of your tier's price data. |
| **Introspection** | | |
| `me()` | `dict` | Plan, status, email for the current token. |
| `manifest()` / `refresh_manifest()` | `dict` | Snapshot metadata (5-min cache / force-refresh). |
| `health()` | `dict` | Gateway health. |
| `tables()` | `list[str]` | Loaded table names. |
| `list_templates()` | `list[str]` | Every runnable SQL template name (pass to `run_template`). |
| `filing_links(ticker, ...)` | `DataFrame` | SEC EDGAR provenance links per filing — inline-XBRL viewer, rendered document, and filing-index URLs for auditor click-through. |
| `get_schema(table)` | `dict[str, str]` | Column → DuckDB type for any known table. |
| **Document generation** *(Pro+ — proxies to the MCP server)* | | |
| `generate_dcf_xlsx(ticker, *, as_of_date=None, depth="standard")` | `dict` | DCF workbook → 15-min presigned download URL. |
| `generate_research_brief_docx(ticker, *, as_of_date=None, depth="full", peers=None)` | `dict` | Institutional research brief `.docx`. |
| `generate_comps_xlsx(ticker, *, peers=None, as_of_date=None)` | `dict` | Peer-comparables workbook. |
| **Lifecycle** | | |
| `close()` / `with ValueinClient() as c:` | — | Release DuckDB + HTTP resources (use the context manager). |

### Alpha framework

```python
from valuein_sdk import AlphaEngine, ROE, GROSS_MARGIN, CURRENT_RATIO

with ValueinClient() as client:
    result = (
        AlphaEngine(client)
        .add_factors(ROE, GROSS_MARGIN, CURRENT_RATIO)
        .compute(as_of="2024-01-01")
    )
    top = result.rank().combine()  # composite cross-sectional score
    print(top.head())
```

Built-ins: `ROE`, `GROSS_MARGIN`, `OPERATING_MARGIN`, `NET_PROFIT_MARGIN`,
`REVENUE_GROWTH_YOY`, `FCF_TO_ASSETS`, `DEBT_TO_EQUITY`, `ASSET_TURNOVER`,
`CURRENT_RATIO`, `PIOTROSKI_F_SCORE` (all in `BUILTIN_FACTORS`). Define your own
with `AlphaFactor`.

### One token, four channels

Your Stripe-issued Bearer token unlocks every Valuein surface at your tier — no
per-channel billing:

| Channel | How | Best for |
|---|---|---|
| **Python SDK** (this package) | `pip install valuein-sdk` | Heavy out-of-core DuckDB compute |
| **MCP server** | `mcp.valuein.biz/mcp` | AI agents (Claude, Cursor, Codex) — reasoning + 50+ tools |
| **Bulk Data API** | `GET data.valuein.biz/v1/{plan}/{table}` | Direct HTTP / partner integrations (raw Parquet streams) |
| **Web dashboard / Workspace** | [valuein.biz](https://valuein.biz) | Browser analysis, theses, watchlists, reports |

The SDK is the **Muscle** (compute); the MCP server is the **Nervous System**
(reasoning). The `generate_*` methods above are the bridge — they let SDK power
users reach the MCP's document-generation tools without writing a JSON-RPC client.

---

# 🗂️ Data Schema
> The authoritative schema is embedded in each tier's R2 `manifest.json` (written by `data-pipeline/run_exports.py`). The SDK fetches it at init and exposes the active version via `client.get_schema(table)` — there is no bundled schema file in this package. Use `print(client.get_schema("fact"))` to inspect column types live.

Current snapshot: **schema v2.28.0**, **23 tables** (15 core on every tier + 6 smart-money and 2 Form ADV tables on the Institutional tier). The exact column list for any table is live in the manifest — run `client.get_schema("fact")`.

| Table              | Description                   | Records |
|-------------------|-------------------------------|---------|
| **references**     | **Start here.** Flat join of entity + security (one row per security) with `cik`, `symbol`, `name`, `sector`, `industry`, `sic_code`, `is_active`, FIGI. For index membership (current OR historical) JOIN `index_membership` on `cik = cik`. There is no `is_sp500` flag — it was dropped 2026-05-02 because it was snapshot-only and single-index. | 19K+ |
| **entity**         | Company metadata. `business_address` + `mailing_address`, `sic_description`, `state_of_incorporation_description`, `country_code`, `is_foreign`, `flags`, `has_insider_transactions`, `is_insider_owner`, `former_names` (JSON). | 19K+    |
| **security**       | Ticker history (SCD Type 2). `is_primary_ticker` for multi-share-class issuers (BRK-A/BRK-B, GOOG/GOOGL). | 19K+    |
| **filing**         | Filing metadata since 1993. `is_xbrl_numeric` flags filings whose XBRL exhibit carries numeric facts; `superseded_by` chains the amendment lineage; `core_type` strips `/A` for form-family filtering. | 12M+    |
| **fact**           | Standardized financial facts. Bloomberg Option-C restatement columns (`value_current`, `value_as_filed`, `first_filed_at`, `restated`), quality columns (`confidence_score` — filter `>= 0.95` for Bloomberg-grade; `reliability_code` 1–4), `accounting_standard` (US-GAAP/IFRS), `reporting_currency`, and PIT semantics via `accepted_at`. | 111M+   |
| **valuation**      | Pre-computed intrinsic values per (entity_id, valuation_date, model_type). `model_type` rows coexist: `dcf` / `dcf_fcf` / `ddm`. Recomputed each run — **not** PIT-filtered (use `created_at`). | 500K+   |
| **ratio**          | Pipeline-computed financial ratios per entity per fiscal period (`ratio_name`, `category`, `value`, `fiscal_period`, `is_ttm`, `confidence_score`, `computed_at`). Holds **both** annual (`fiscal_period='FY'`, `is_ttm=false`) **and** trailing-twelve-month (`fiscal_period='TTM'`, `is_ttm=true`) rows per company — TTM is the most-current trailing read (latest quarter close). 164 unique `ratio_name`s: profitability 32, leverage 14, efficiency 15, per_share 11, liquidity 5 (all five have TTM), forensic 5, growth/CAGR 60, sector-percentile 22 — `category='rank'` (these three are annual-only). ⚠️ A raw `read_table('ratio')` query that does **not** filter `is_ttm` / `fiscal_period` double-counts each metric — always `WHERE is_ttm = FALSE` (or `fiscal_period = 'FY'`); the SDK's `financial_ratios_*` templates already filter and are safe. Full ratio-name catalog in [`SQL_CHEATSHEET.md`](valuein_sdk/queries/SQL_CHEATSHEET.md#reference--ratio-names--categories). Recomputed each run; freshness via `computed_at`. | 10M+    |
| **standard_concept** | Curated gold-standard concept catalog — the dictionary for `fact.standard_concept`. Carries `level`, `statement_type`, `definition`, `unit_default`, `bloomberg_equivalent`, `factset_equivalent`. The `cpa_verified_concepts` template surfaces CPA-reviewed concepts via `review_confidence` as that field rolls out (v3.4.0). | ~292 |
| **taxonomy_guide** | Raw SEC us-gaap tag reference for the `fact.concept` column (`human_name`, `definition`, `balance_type`, `level`). | ~11,966 |
| **index_membership** | Survivorship-free index constituents — **SP500, RUSSELL1000, RUSSELL2000, RUSSELL3000**. Keyed on **`cik`** (since migration 0015 — same column name as `references.cik`). `effective_date` / `removal_date` use `[)` interval semantics; carries `announcement_date`, `removal_reason`, `successor_cik`, `source`, `confidence`. | 4 indices |
| **factor_scores** | Cross-sectional factor scores + percentile ranks (10 factors + `composite_rank`) from recent annual filings. | 1M+    |
| **earnings_signals** | Trailing earnings-trend estimate + EPS surprise %, plus YoY revenue change. | 500K+   |
| **stock_price** | Coarse EOD price pre-aggregation of `stock_price_daily`: one row per entity at each `period_end` plus monthly closes (`observation` = `'period_end'` \| `'monthly'`). No OHLC/volume/`adjusted_close` — exists so valuation overlays and price-derived ratios never need a daily-bar scan. Resolved to the issuer's **primary listing** before alignment, with `security_id`/`symbol` recording which, so a P/E is auditable back to the share class it was computed from. PIT via `accepted_at` (the market-close stamp). | 19K+ entities |
| **stock_price_daily** | Daily OHLCV bars since 1993 (tier-windowed): `open/high/low/close`, `adjusted_close`, `volume`, `div_cash`, `split_factor`, plus `security_id`/`symbol`/`is_primary_listing`/`total_return_index` (schema 2.29.0). ⚠️ **Grain is one row per (SECURITY, day), not per company** — an issuer with two listings contributes two rows, so partition on `security_id` or filter `is_primary_listing`. ⚠️ **For return math use `total_return_index`**, not `adjusted_close` (the vendor populates it on ~2% of bars) and never raw `close` (a 4-for-1 split reads −75%). The SDK's `price_field="total_return"` does this for you. | 30+ yrs daily |
| **restatement_events** | **Restatement Radar** — every fact a later SEC filing materially changed, back to 1993, as a before/after diff with both accessions deep-linked. `disclosure_class` = `non_reliance` (a real 8-K Item 4.02) \| `amended` (10-K/A, 10-Q/A) \| `undisclosed` (changed inside a routine filing). Available on **every tier including anonymous guest**. | 150K events |
| **insider_party · insider_filing · insider_transaction · institutional_filing · institutional_holding · insider_ownership** | Smart-money dataset — Forms 3/4/5/144 + 13F-HR + SC 13D/13G. **Institutional / `full` plan only.** | FULL tier |
| **investment_adviser · investment_adviser_private_fund** | SEC Form ADV Part 1A / Schedule D 7.B.(1) — RIA firm profiles + private funds. **CRD-keyed, not CIK-keyed** (advisers file into FINRA-operated IARD); `cik` is a nullable soft link on ~26% of filers. ⚠️ Items 5.A–5.F are absent for Exempt Reporting Advisers (a different filing obligation, not missing data); `raum_total` is structurally double-counted across sub-advisory relationships — per-firm only, never aggregate. **Institutional / `full` plan only.** | FULL tier |


### 🔗 Key Joins

```
references.cik                  →  entity.cik  (references is the fast entry point)
security.entity_id              →  entity.cik
filing.entity_id                →  entity.cik
fact.entity_id                  →  entity.cik
fact.accession_id               →  filing.accession_id
index_membership.cik            →  entity.cik   (same column name on both sides
                                                 since migration 0015 — JOIN as
                                                 references.cik = im.cik)
```

### 🎯 Survivorship-free PIT universe (the canonical backtest pattern)

```sql
-- All SP500 members on a historical as_of_date, resolved to active ticker.
-- index_membership keys on `cik` (since pipeline migration 0015) — same column
-- name as references.cik / entity.cik, so the join read straight across.
SELECT m.cik, s.id AS security_id, s.symbol AS ticker_at_date,
       e.name AS company_name, m.effective_date, m.removal_date, m.confidence
FROM index_membership m
JOIN entity e ON e.cik = m.cik
LEFT JOIN security s ON s.entity_id = m.cik
    AND s.is_primary_ticker = TRUE
    AND $as_of_date >= s.valid_from
    AND $as_of_date <  COALESCE(s.valid_to, '9999-12-31'::DATE)
WHERE m.index_name = 'SP500'
  AND $as_of_date >= m.effective_date
  AND $as_of_date <  COALESCE(m.removal_date, '9999-12-31'::DATE);
```

`LEFT JOIN security` so delisted companies still surface
(`ticker_at_date = NULL`).  `is_primary_ticker = TRUE` pins
multi-share-class issuers to one row per CIK — universe count stays at
~500 even when BRK-A and BRK-B both exist in `security`.  `[)`
semantics — a company removed on 2017-06-19 is NOT a member ON
2017-06-19.

### ⚡ DuckDB Query Patterns

Three patterns that eliminate redundant joins and scans on every cross-company query:

**1. `references` replaces the entity + security join; index_membership stays separate**
```sql
-- Filter current S&P 500 tech companies (membership lives in index_membership).
-- The previous references.is_sp500 flag was dropped 2026-05-02 — both
-- snapshot-only and single-index, two footguns avoided by JOIN-on-membership.
SELECT r.symbol, r.name, r.sector
FROM   "references" r
JOIN   index_membership im ON im.cik = r.cik
WHERE  im.index_name   = 'SP500'
  AND  im.removal_date IS NULL
  AND  r.sector ILIKE '%technology%'
  AND  r.is_active     = TRUE
```

**2. `LATERAL` for the latest filing per company**
```sql
JOIN LATERAL (
  SELECT accession_id, filing_date
  FROM   filing
  WHERE  entity_id = r.cik AND form_type = '10-K'
  ORDER  BY filing_date DESC
  LIMIT  1
) f ON true
```

**3. Pivot multiple concepts in one `fact` scan**
```sql
-- Debt + equity in one pass — no self-join
SELECT
  MAX(CASE WHEN standard_concept = 'LongTermDebt'       THEN numeric_value END) AS debt,
  MAX(CASE WHEN standard_concept = 'StockholdersEquity' THEN numeric_value END) AS equity
FROM fact WHERE standard_concept IN ('LongTermDebt', 'StockholdersEquity')
GROUP BY accession_id
```

> For quarterly cash flow metrics, use `COALESCE(derived_quarterly_value, numeric_value)` — Q2/Q3 10-Qs report YTD; this column isolates the single quarter.

See [`valuein_sdk/queries/SQL_CHEATSHEET.md`](valuein_sdk/queries/SQL_CHEATSHEET.md) for 8 complete patterns including FCF screens, PIT backtesting, and restatement auditing.

---

### 🏷️ Standard Concept Names

> [!Note]
> Each `fact` row carries **both** the raw XBRL tag (`concept`, ~11,966 distinct us-gaap tags — see `taxonomy_guide`) **and** the normalized `standard_concept` (~292 canonical concepts — see `standard_concept`). Tags that don't map to a canonical concept fall through to the `Other` bucket, so no fact is dropped.
>
> Because both columns live on `fact`, you never need to join a separate mapping table to filter by either the raw tag or the canonical concept.


### 📅 Date Columns Reference

| Column | Table | Use for |
|---|---|---|
| `report_date` / `period_end` | `filing` / `fact` | Aligning to fiscal calendar |
| `filing_date` | `filing` | **PIT backtest filter** — when the SEC received the filing |
| `accepted_at` | `fact` | Millisecond-precision PIT for intraday signal research |


### 🧩 Template Categories

**58 templates** ship in `valuein_sdk/queries/`, auto-discovered by filename. Run any of them with `client.run_template("<name>", ...)`:

| Category | Templates (examples) |
|---|---|
| Data Access | `fundamentals_by_ticker`, `figi_to_fundamentals_mapping`, `peer_group_comparison`, `survivorship_bias_free_screen` |
| Income Statement | `revenue_yoy_growth`, `trailing_twelve_months_ttm`, `margin_analysis`, `free_cash_flow`, `rnd_intensity` |
| Balance Sheet | `liquidity_ratios`, `solvency_debt_to_equity`, `interest_coverage`, `efficiency_cash_conversion`, `capex_to_revenue` |
| Investment Scores | `dupont_analysis_inputs`, `piotroski_f_score_inputs`, `altman_z_score_inputs`, `earnings_quality_accruals_anomaly` |
| Valuation & Screening | `sector_relative_valuation_outperformers`, `financial_ratios_screener`, `shareholder_dilution`, `factor_screen_top_quintile` |
| Smart-money & Ownership *(FULL tier)* | `insider_buys`, `blockholders`, `top_institutional_holders`, `manager_portfolio` |
| Event & Short Signals | `late_reporter_short_signal`, `restatement_history`, `8k_material_event_signal`, `ghost_company_screener` |
| Advanced Analytics | `true_point_in_time_backtest_engine`, `time_series_outlier_detection_zscore`, `seasonal_frame_based_extraction`, `cpa_verified_concepts` |

See [`valuein_sdk/queries/SQL_CHEATSHEET.md`](valuein_sdk/queries/SQL_CHEATSHEET.md) for the full reference with copy-paste DuckDB patterns.


## 📚 Documentation

| Document | Description | Where |
|----------|------------|--------|
| Methodology | Data sourcing, PIT architecture, restatement handling, XBRL normalization | [valuein.biz/docs](https://valuein.biz/docs) · [public hub](https://github.com/valuein/valuein/blob/main/docs/methodology.md) |
| Compliance & DDQ | Data provenance, MNPI policy, PIT integrity, security, SLA summary | [public hub](https://github.com/valuein/valuein) |
| SLA | Uptime targets, data freshness SLAs, support response times | [valuein.biz/sla](https://valuein.biz/sla) |
| Data Catalog | Every column, type, definition, sample value | [public hub `DATA_CATALOG.xlsx`](https://github.com/valuein/valuein/blob/main/docs/DATA_CATALOG.xlsx) |
| Live schema | Machine-readable schema for the active snapshot | `client.get_schema("<table>")` — read from the R2 manifest at runtime |

---

## 🐍 Python Examples
Standalone Python scripts and ten Jupyter notebooks, designed to go from install to insight in under 3 minutes.

### Ticker lookup example
Run any SQL against the data lake. No downloads. No local database. DuckDB executes your queries in-process.

```python
from valuein_sdk import ValueinClient

client = ValueinClient(tables=["entity", "security"])
# This client only fetch these 2 tables, making it faster!

df = client.run_query("""
    SELECT e.cik, e.name, e.sector, e.status,
           s.symbol, s.exchange
    FROM   security s
    JOIN   entity   e ON s.entity_id = e.cik
    WHERE  s.symbol = 'AAPL' AND s.is_active = TRUE
""")
print(df)
```

You are now querying **SEC financial statements directly from the
cloud**.


### Python scripts (`examples/python/`)

| Script | Level | What it demonstrates |
|---|---|---|
| [`getting_started.py`](examples/python/getting_started.py) | Beginner | Auth check, first query, entity counts by sector |
| [`usage.py`](examples/python/usage.py) | Reference | Every public SDK method demonstrated end to end |
| [`production-ready.py`](examples/python/production-ready.py) | Reference | Context-manager pattern, config, full error hierarchy |
| [`entity_screening.py`](examples/python/entity_screening.py) | Beginner | Screen by sector, SIC code, active vs inactive status |
| [`financial_analysis.py`](examples/python/financial_analysis.py) | Intermediate | Revenue trends, margins, concept normalization, peer comparison |
| [`pit_backtest.py`](examples/python/pit_backtest.py) | Intermediate | Correct PIT discipline, restatement impact, `filing_date` vs `report_date` |
| [`survivorship_bias.py`](examples/python/survivorship_bias.py) | Intermediate | Delisted/bankrupt companies, index_membership, bias quantification |

### Jupyter notebooks (`notebooks/`)

| Notebook | Open in Colab |
|---|---|
| [`01_quickstart`](notebooks/01_quickstart.ipynb) | [![Open in Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/valuein/sdk/blob/main/notebooks/01_quickstart.ipynb) |
| [`02_dcf_valuation`](notebooks/02_dcf_valuation.ipynb) | [![Open in Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/valuein/sdk/blob/main/notebooks/02_dcf_valuation.ipynb) |
| [`03_piotroski_screen`](notebooks/03_piotroski_screen.ipynb) | [![Open in Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/valuein/sdk/blob/main/notebooks/03_piotroski_screen.ipynb) |
| [`04_earnings_quality`](notebooks/04_earnings_quality.ipynb) | [![Open in Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/valuein/sdk/blob/main/notebooks/04_earnings_quality.ipynb) |
| [`05_factor_backtest`](notebooks/05_factor_backtest.ipynb) | [![Open in Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/valuein/sdk/blob/main/notebooks/05_factor_backtest.ipynb) |
| [`06_sector_comparison`](notebooks/06_sector_comparison.ipynb) | [![Open in Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/valuein/sdk/blob/main/notebooks/06_sector_comparison.ipynb) |
| [`07_restatement_alpha`](notebooks/07_restatement_alpha.ipynb) | [![Open in Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/valuein/sdk/blob/main/notebooks/07_restatement_alpha.ipynb) |
| [`08_capital_allocation`](notebooks/08_capital_allocation.ipynb) | [![Open in Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/valuein/sdk/blob/main/notebooks/08_capital_allocation.ipynb) |
| [`09_filing_delay`](notebooks/09_filing_delay.ipynb) | [![Open in Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/valuein/sdk/blob/main/notebooks/09_filing_delay.ipynb) |
| [`10_survivorship_demo`](notebooks/10_survivorship_demo.ipynb) | [![Open in Colab](https://colab.research.google.com/assets/colab-badge.svg)](https://colab.research.google.com/github/valuein/sdk/blob/main/notebooks/10_survivorship_demo.ipynb) |

---

### 🛡️ Error Handling

```python
from valuein_sdk import (
    ValueinAuthError,  # HTTP 401/403 — invalid or expired token
    ValueinPlanError,  # HTTP 403 — endpoint requires a higher plan
    ValueinNotFoundError,  # HTTP 404 — no table found
    ValueinRateLimitError,  # HTTP 429 — includes .retry_after (seconds)
    ValueinAPIError,  # HTTP 5xx — includes .status_code
    ValueinClient,
)

client = None
try:
    client = ValueinClient()
    df = client.run_query("SELECT * FROM fact LIMIT 1000000")
except ValueinAuthError:
    print("Check your VALUEIN_API_KEY. It might be expired or invalid.")
except ValueinPlanError:
    print("This requires a higher-tier plan. Upgrade at valuein.biz.")
except ValueinRateLimitError as e:
    print(f"Slow down! Retry allowed in {e.retry_after}s.")
except ValueinNotFoundError as e:
    print(f"That table or endpoint doesn't exist: {e}")
except ConnectionError as e:
    print(f"Physical network issue: {e}")
except ValueinAPIError as e:
    print(f"The Gateway is having a bad day (Status {e.status_code}).")
except Exception as e:
    print(f"Non-SDK error (Python/Logic): {e}")
finally:
    # Always close manually if not using a context manager and if a client was created.
    if client is not None:
        client.close()
```

---

## 🔬 Research & Quality Proofs

The ten [notebooks](#jupyter-notebooks-notebooks) double as runnable due-diligence
proofs — PIT correctness, survivorship-bias quantification, restatement alpha, and
balance-sheet identity checks — all against live data. Install the research extras
to run them locally:

```bash
# pip
pip install "valuein-sdk[research]"
# uv (dev workflow)
uv sync --group research
```

For institutional due-diligence material (methodology, compliance/DDQ, SLA, and the
full reproducible research suite) see the open-source hub at
[github.com/valuein/valuein](https://github.com/valuein/valuein).

---

## 🤝 Contributing

We welcome contributions including SQL templates, notebooks, scripts, research modules, and documentation improvements.

See [CONTRIBUTING.md](CONTRIBUTING.md) for code standards, naming conventions, and the PR process.

## 📄 License

Apache-2.0 License — see [LICENSE](LICENSE).

**Disclosure:** This repository is for research and educational purposes
only and does not constitute financial advice.
