price-cache

A gap-aware, SQLite / PostgreSQL-backed OHLCV price data cache with a seven-provider fallback chain.

Caches data by (ticker, date, interval) so overlapping or adjacent date ranges are served from the local database without extra API calls. Gap detection ensures only genuinely missing ranges are fetched from the network.

---

Installation

# Editable install (development)
pip install -e .

# Or with uv
uv sync

Dependencies

Package	Version	Purpose
pandas	≥ 3.0	DataFrame I/O
psycopg2-binary	≥ 2.9	PostgreSQL driver
yfinance	≥ 1.3	Primary data provider
tqdm	≥ 4.67	Migration progress bars
requests	≥ 2.32	Secondary provider HTTP calls
json5	≥ 0.9	Config file parser (JSON with comments)

---

Quick Start

Local SQLite mode (no server required)

import price_cache

price_cache.configure(remote=False)
df = price_cache.get_price_data("AAPL", "2023-01-01", "2023-12-31")
if df is not None:
    print(df[["Open", "Close"]].tail())

The database is created automatically at data/yfd_prices.db on first use.

PostgreSQL mode

import price_cache

price_cache.POSTGRES_URL = "postgresql://user:pass@host/db"
price_cache.configure(remote=True)
df = price_cache.get_price_data("AAPL", "2023-01-01", "2023-12-31")

Three-tier cache (local mirror + PostgreSQL + API)

import price_cache

price_cache.POSTGRES_URL = "postgresql://user:pass@host/db"
price_cache.configure(remote=True, local_mirror_path="data/yfd_prices.db")
df = price_cache.get_price_data("AAPL", "2023-01-01", "2023-12-31")

Lookup order:

1. Local SQLite mirror → fastest; no network
2. Remote PostgreSQL → cache hit without API call
3. External API → fetched and written to both stores

---

API Reference

configure(remote=True, local_mirror_path=None)

Switch between remote PostgreSQL (default) and local SQLite.

price_cache.configure(remote=False)                                     # SQLite only
price_cache.configure(remote=True)                                      # PostgreSQL only
price_cache.configure(remote=True, local_mirror_path="data/local.db")  # three-tier

Call this before any database operation, typically at the start of main().

---

configure_logging(verbose=False)

Attach a stream handler to the price_cache logger.

price_cache.configure_logging(verbose=True)   # debug: shows fallback sources, rate limits
price_cache.configure_logging(verbose=False)  # warnings only (default)

Without calling this, all log messages from the library are silently discarded.

---

reconfigure_from_config(cfg: dict)

Apply settings from a loaded config dict. Environment variables always take priority.

from price_cache._config import load_config
import price_cache

cfg = load_config("config.json")
price_cache.reconfigure_from_config(cfg)

Config dict schema:

{
  "secrets": {
    "tiingo_api_key": "...",
    "fmp_api_key": "...",
    "alpha_vantage_api_key": "...",
    "eodhd_api_key": "...",
    "finnhub_api_key": "..."
  },
  "database": {
    "postgres_url": "postgresql://user:pass@host/db",
    "local_db": false
  },
  "paths": {
    "yfd_db": "data/yfd_prices.db"
  }
}

---

get_price_data(ticker, start_date, end_date, interval="1d", db_path=DB_PATH)

Return OHLCV data for a ticker between two dates.

df = price_cache.get_price_data("AAPL", "2023-01-01", "2023-12-31")
df = price_cache.get_price_data("AAPL", "2023-01-01", "2023-01-31", interval="1h")

Parameters:

Name	Type	Description
ticker	str	Stock ticker symbol, e.g. "AAPL"
start_date	str	Start date "YYYY-MM-DD", inclusive
end_date	str	End date "YYYY-MM-DD", inclusive
interval	str	Bar interval; see table below
db_path	str	SQLite path (local mode only)

Supported intervals:

1m, 2m, 5m, 15m, 30m, 60m, 90m, 1h, 1d (default), 5d, 1wk, 1mo, 3mo

Returns: pandas.DataFrame with DatetimeIndex (America/New_York timezone) and columns Open, High, Low, Close, Volume, Dividends, Stock Splits, market_cap, or None when no data is available.

---

fetch_bars_bulk_from_local(tickers_and_ranges)

Fetch daily OHLCV for many tickers from the local SQLite cache in exactly 3 SQL queries.

import datetime
result = price_cache.fetch_bars_bulk_from_local([
    ("AAPL", datetime.date(2024, 1, 1), datetime.date(2024, 3, 31)),
    ("MSFT", datetime.date(2024, 1, 1), datetime.date(2024, 3, 31)),
])
# result["AAPL"] → DataFrame or None
# "MSFT" absent from result → cache gap, fall back to get_price_data()

Only tickers whose entire requested range is fully covered (no gaps) by fetched_ranges/prices are included in the result. A ticker maps to None when its range is fully covered but genuinely has zero price rows (a confirmed-empty period, e.g. pre-IPO/delisted); a ticker is absent from the result dict when there's a coverage gap, and the caller should fall back to get_price_data(). This function does not consult no_data_tickers at all (see the note under that table below) — a marker for the ticker never withholds data that's actually cached, regardless of what range is being requested.

---

lookup_ticker_by_name(company_name, db_path=DB_PATH)

Look up a ticker symbol for a company name, querying Yahoo Finance if needed. Results are cached.

ticker = price_cache.lookup_ticker_by_name("Apple Inc.")
# Returns "AAPL" or None

Strips common corporate suffixes (Inc., Corp., Ltd., LLC, PLC, AB) when the first search returns nothing.

---

close_all_connections()

Close all open SQLite and PostgreSQL connections.

import atexit
atexit.register(price_cache.close_all_connections)

---

migrate(dry_run=False, db_path=DB_PATH)

Import legacy data/yfd_cache/*.parquet files into the SQLite database.

price_cache.configure(remote=False)
price_cache.migrate(dry_run=True)  # preview only
price_cache.migrate()              # run for real

Also imports data/yfd_cache/ticker_nodata.json (delisted tickers). Safe to re-run (idempotent).

---

API Key Configuration

API keys are read from environment variables. Set them before importing the library or call reconfigure_from_config().

Provider	Environment Variable	Free Limit	Supported
yfinance	— (no key)	adaptive rate	daily + intraday
FMP	FMP_API_KEY	250 calls/day	intraday (paid)
Alpha Vantage	ALPHA_VANTAGE_API_KEY	25 calls/day	intraday (paid)
EODHD	EODHD_API_KEY	1 000 calls/day	EOD free, intraday paid
Finnhub	FINNHUB_API_KEY	60 calls/min	intraday (paid)
Tiingo	TIINGO_API_KEY	45 symbols/hr, 900 req/hr	daily only (free)
Stooq	— (no key)	unlimited	daily / weekly / monthly

---

Database Schema

The SQLite / PostgreSQL schema has four tables:

prices

Primary key: (ticker, date, interval_minutes)

Column	Type	Description
ticker	TEXT	Stock ticker
date	TEXT	YYYY-MM-DD (daily) or YYYY-MM-DD HH:MM:SS (intraday)
interval_minutes	INTEGER	1440 = daily
open, high, low, close	REAL / DOUBLE	OHLC prices
volume	INTEGER / BIGINT	Trade volume
dividends, stock_splits	REAL / DOUBLE	Corporate actions
market_cap	INTEGER / BIGINT	Latest market cap (best-effort)

fetched_ranges

Unique: (ticker, fetched_from, fetched_to, interval_minutes)

Tracks every date range that has been attempted, including empty periods (holidays, weekends, delisted). This prevents the same range from ever being re-fetched.

no_data_tickers

Primary key: ticker

Diagnostic audit trail only, as of 2026-08 — not consulted by get_price_data() or fetch_bars_bulk_from_local(). A row records "the most recent fetch attempt for this ticker found nothing at time X" (TTL NO_DATA_TTL_SECONDS = 1 hour, self-purged on next read once expired). It used to gate reads too: any row, however old the noted attempt, blocked the entire ticker for every future request regardless of date range. A real incident (2026-08) hit this — a ticker with 400k+ cached price rows returned None for a date range that had nothing to do with the marker at all. Gap detection for actual reads now comes exclusively from fetched_ranges (which already tracks known-empty sub-ranges at the correct per-range granularity via _mark_fetched_empty), so no ticker-wide gate is needed to avoid repeat network calls for genuinely empty periods.

ticker_lookup

Primary key: company_name (lowercased)

Cache for lookup_ticker_by_name() results. An empty ticker means "searched but not found".

---

Fallback Chain and Rate Limiting

The library tries providers in order, skipping any that are rate-limited:

yfinance → FMP → Alpha Vantage → EODHD → Finnhub → Tiingo → Stooq

yfinance adaptive AIMD:

• Starts at 240 calls/min.
• After every 10 consecutive successes, cap increases by 10 (max 580/min).
• On HTTP 429: cap drops to 75 % of current value (min 15/min), all yfinance calls freeze for 65 seconds.

---

CLI

# Fetch and cache a ticker (local SQLite)
price-cache --local-db --fetch AAPL 2023-01-01 2023-12-31

# Intraday fetch
price-cache --local-db --fetch AAPL 2023-06-01 2023-06-30 --interval 1h

# Cache coverage stats
price-cache --local-db --stats

# Migrate legacy parquet cache
price-cache --local-db --migrate
price-cache --local-db --migrate --dry-run

# Debug logging
price-cache --local-db --fetch AAPL 2023-01-01 2023-01-31 -v

# Use config file
price-cache --configfile /path/to/config.json --stats

---

Config File (config.json)

The CLI and load_config_from_args() look for a config file in this order:

1. --configfile <path> argument
2. PRICE_CACHE_CONFIG environment variable
3. config.json in the current working directory

A missing config file is silently ignored (falls back to defaults).

Example config.json:

{
  // API keys (can also be set via environment variables)
  "secrets": {
    "tiingo_api_key": "your-tiingo-key",
    "fmp_api_key": "your-fmp-key"
  },

  // Database settings
  "database": {
    "postgres_url": "postgresql://user:pass@host:5432/mydb",
    "local_db": false
  },

  // Override default SQLite path
  "paths": {
    "yfd_db": "data/yfd_prices.db"
  }
}

---

Development

uv sync
uv run pytest tests/ -v
uv run flake8 --max-line-length=120 src/
uv run mypy --ignore-missing-imports src/