102 lines
3.5 KiB
Python
102 lines
3.5 KiB
Python
"""Benchmark price store + alpha helpers.
|
|
|
|
Fetches the S&P 500 proxy (SPY) daily closes via Alpaca and persists them, so
|
|
paper-trade alpha — a trade's return minus the benchmark's return over the same
|
|
holding period — can be computed. The benchmark is a standalone series, NOT a
|
|
tracked ``Ticker``; its closes feed residual momentum and alpha, but it never
|
|
becomes a trade candidate or rankings-table row.
|
|
"""
|
|
|
|
from __future__ import annotations
|
|
|
|
import bisect
|
|
import logging
|
|
from datetime import date, timedelta
|
|
|
|
from sqlalchemy import select
|
|
from sqlalchemy.ext.asyncio import AsyncSession
|
|
|
|
from app.config import settings
|
|
from app.models.benchmark_price import BenchmarkPrice
|
|
from app.providers.alpaca import AlpacaOHLCVProvider
|
|
|
|
logger = logging.getLogger(__name__)
|
|
|
|
BENCHMARK_SYMBOL = "SPY"
|
|
# ~800 calendar days ≈ 550 trading days — comfortably covers any realistic paper
|
|
# holding period plus a margin for the nearest-prior-trading-day lookup.
|
|
_HISTORY_DAYS = 800
|
|
|
|
|
|
async def refresh_benchmark_prices(
|
|
db: AsyncSession, symbol: str = BENCHMARK_SYMBOL, days: int = _HISTORY_DAYS
|
|
) -> int:
|
|
"""Fetch the benchmark's daily closes and upsert them. Returns rows written.
|
|
|
|
Idempotent: inserts new dates, updates a close only if it changed (e.g. after
|
|
a split adjustment). Best-effort — returns 0 when Alpaca keys are unset.
|
|
"""
|
|
if not settings.alpaca_api_key or not settings.alpaca_api_secret:
|
|
logger.warning("Benchmark refresh skipped: Alpaca keys not configured")
|
|
return 0
|
|
|
|
provider = AlpacaOHLCVProvider(settings.alpaca_api_key, settings.alpaca_api_secret)
|
|
end = date.today()
|
|
start = end - timedelta(days=days)
|
|
bars = await provider.fetch_ohlcv(symbol, start, end)
|
|
|
|
existing = {
|
|
row.date: row
|
|
for row in (
|
|
await db.execute(select(BenchmarkPrice).where(BenchmarkPrice.symbol == symbol))
|
|
).scalars()
|
|
}
|
|
|
|
written = 0
|
|
for bar in bars:
|
|
current = existing.get(bar.date)
|
|
if current is None:
|
|
db.add(BenchmarkPrice(symbol=symbol, date=bar.date, close=float(bar.close)))
|
|
written += 1
|
|
elif abs(current.close - float(bar.close)) > 1e-9:
|
|
current.close = float(bar.close)
|
|
written += 1
|
|
|
|
if written:
|
|
await db.commit()
|
|
logger.info("Benchmark %s refreshed: %d rows written", symbol, written)
|
|
return written
|
|
|
|
|
|
async def load_benchmark_closes(
|
|
db: AsyncSession, symbol: str = BENCHMARK_SYMBOL
|
|
) -> dict[date, float]:
|
|
"""Return ``{date: close}`` for the benchmark (empty if none stored yet)."""
|
|
rows = await db.execute(
|
|
select(BenchmarkPrice.date, BenchmarkPrice.close).where(BenchmarkPrice.symbol == symbol)
|
|
)
|
|
return {d: float(c) for d, c in rows.all()}
|
|
|
|
|
|
def benchmark_return_pct(
|
|
closes: dict[date, float], open_date: date, as_of_date: date
|
|
) -> float | None:
|
|
"""Benchmark % return between two dates, using the nearest close on/before each.
|
|
|
|
Returns ``None`` when there's no benchmark data at or before either endpoint
|
|
(e.g. a trade opened before the stored history, or the table is empty).
|
|
"""
|
|
if not closes:
|
|
return None
|
|
dates = sorted(closes)
|
|
|
|
def _close_on_or_before(target: date) -> float | None:
|
|
idx = bisect.bisect_right(dates, target) - 1
|
|
return closes[dates[idx]] if idx >= 0 else None
|
|
|
|
start = _close_on_or_before(open_date)
|
|
end = _close_on_or_before(as_of_date)
|
|
if start is None or end is None or start == 0:
|
|
return None
|
|
return (end - start) / start * 100.0
|