GitHub - pdet/Scrooge-McDuck

A financial analysis extension for DuckDB. 59+ SQL functions for technical analysis, risk management, portfolio analytics, options pricing, and market data access.

Note: This codebase is currently maintained and updated by an AI agent running Claude Code. While all changes go through CI and automated testing, use with appropriate caution and review before relying on it for financial decisions.

Disclaimer: This extension is not affiliated with the DuckDB Foundation or DuckDB Labs. Binaries are unsigned.

INSTALL scrooge FROM community;
LOAD scrooge;

SELECT date, close,
    ema(close, date::TIMESTAMPTZ, 20) as ema_20,
    rsi(close, date::TIMESTAMPTZ, 14) as rsi_14,
    bollinger_signal(close,
        bollinger_upper(close, date::TIMESTAMPTZ, 20, 2.0),
        bollinger_lower(close, date::TIMESTAMPTZ, 20, 2.0)) as signal
FROM yahoo_finance('AAPL', '2024-01-01', '2025-01-01', '1d')
GROUP BY ALL;

Quick Start

INSTALL scrooge FROM community;
LOAD scrooge;

-- Stock data
SELECT * FROM yahoo_finance('MSFT', '2024-01-01', '2025-01-01', '1d');

-- Crypto data (free, no API key)
SELECT * FROM coingecko('bitcoin', 'usd', 365);

-- Economic data (requires free FRED API key)
SELECT * FROM fred_series('GDP', 'your_api_key');

Functions

Technical Indicators

Function	Description
`ema(close, timestamp, period)`	Exponential Moving Average
`dema(close, timestamp, period)`	Double EMA (reduced lag)
`tema(close, timestamp, period)`	Triple EMA (minimal lag)
`wma(close, timestamp, period)`	Weighted Moving Average
`rsi(close, timestamp, period)`	Relative Strength Index
`macd(close, timestamp)`	MACD Line
`bollinger_upper/lower/middle/width(close, ts, period, stddev)`	Bollinger Bands
`vwap(price, volume)`	Volume Weighted Average Price
`obv(close, volume, timestamp)`	On-Balance Volume
`atr(high, low, close, timestamp, period)`	Average True Range
`stochastic_k(high, low, close, timestamp, period)`	Stochastic Oscillator %K
`mfi(high, low, close, volume, timestamp, period)`	Money Flow Index
`cmf(high, low, close, volume, timestamp, period)`	Chaikin Money Flow
`ad_line(high, low, close, volume, timestamp)`	Accumulation/Distribution Line
`pivot_point/pivot_r1/pivot_r2/pivot_r3/pivot_s1/pivot_s2/pivot_s3(h, l, c)`	Pivot Points

Returns and Risk

Function	Description
`simple_return(close, timestamp)`	Simple return
`log_return(close, timestamp)`	Logarithmic return
`cumulative_return(close, timestamp)`	Cumulative return
`sharpe_ratio(returns, timestamp)`	Sharpe ratio
`sortino_ratio(returns, timestamp)`	Sortino ratio
`max_drawdown(close, timestamp)`	Maximum drawdown
`value_at_risk(returns, timestamp, confidence)`	Value at Risk
`annualized_volatility(returns)`	Annualized volatility
`calmar_ratio(returns, close, timestamp)`	Calmar ratio
`information_ratio(asset_returns, benchmark_returns)`	Information ratio
`drawdown_duration(close, timestamp)`	Longest drawdown in days
`profit_factor(returns)`	Sum of wins / sum of losses

Candlestick Patterns

Function	Returns
`is_doji(open, high, low, close)`	`BOOLEAN`
`is_hammer(open, high, low, close)`	`BOOLEAN`
`is_shooting_star(open, high, low, close)`	`BOOLEAN`
`is_engulfing(open, high, low, close, prev_open, prev_close)`	`'BULLISH'/'BEARISH'/NULL`
`is_morning_star(o1, c1, o2, h2, l2, c2, o3, c3)`	`BOOLEAN`
`is_evening_star(o1, c1, o2, h2, l2, c2, o3, c3)`	`BOOLEAN`

Portfolio Analytics

Function	Description
`portfolio_correlation(returns_a, returns_b)`	Pearson correlation
`portfolio_beta(asset_returns, benchmark_returns)`	Systematic risk (beta)
`momentum_score(close, timestamp, period_days)`	Rate-of-change momentum
`relative_strength(asset_close, benchmark_close, ts)`	Performance vs benchmark

Signal Generation

Function	Description
`ma_crossover_signal(fast_ma, slow_ma)`	`'BUY'/'SELL'/'HOLD'`
`rsi_signal(rsi [, overbought, oversold])`	`'BUY'/'SELL'/'HOLD'`
`bollinger_signal(close, upper, lower)`	`'BUY'/'SELL'/'HOLD'`
`kelly_fraction(win_rate, avg_win, avg_loss)`	Optimal position size [0,1]
`composite_score(rsi, macd, bb_pct, obv_pct)`	Multi-indicator conviction [-100,100]

Options Pricing (Black-Scholes)

Function	Description
`bs_call(S, K, T, r, sigma)`	Call option price
`bs_put(S, K, T, r, sigma)`	Put option price
`bs_delta_call / bs_delta_put`	Delta
`bs_gamma`	Gamma
`bs_theta_call / bs_theta_put`	Theta (per day)
`bs_vega`	Vega (per 1% vol)
`bs_implied_vol(price, S, K, T, r, is_call)`	Implied volatility

Data Scanners

Function	Description
`yahoo_finance(symbol, from, to, interval)`	Yahoo Finance OHLCV data
`coingecko(coin_id, vs_currency, days)`	CoinGecko crypto OHLC data
`fred_series(series_id, api_key [, start, end])`	Federal Reserve economic data
`read_eth(url, method, from_block, to_block)`	Ethereum blockchain logs

Utility

Function	Description
`first_s(value, timestamp)`	First value by timestamp
`last_s(value, timestamp)`	Last value by timestamp
`timebucket(timestamp, interval)`	Time bucketing

Examples

Analysis Pipeline

WITH prices AS (
    SELECT * FROM yahoo_finance('AAPL', '2024-01-01', '2025-01-01', '1d')
)
SELECT date, close,
    rsi(close, date::TIMESTAMPTZ, 14) as rsi_14,
    rsi_signal(rsi(close, date::TIMESTAMPTZ, 14)) as rsi_signal,
    sharpe_ratio(simple_return(close, date::TIMESTAMPTZ), date::TIMESTAMPTZ) as sharpe
FROM prices
GROUP BY ALL;

Options Analysis

-- Price a call option and compute Greeks
SELECT
    bs_call(150, 145, 0.25, 0.05, 0.30) as call_price,
    bs_delta_call(150, 145, 0.25, 0.05, 0.30) as delta,
    bs_gamma(150, 145, 0.25, 0.05, 0.30) as gamma,
    bs_implied_vol(12.5, 150, 145, 0.25, 0.05, true) as implied_vol;

Portfolio Diversification

SELECT
    portfolio_correlation(aapl_ret, msft_ret) as correlation,
    portfolio_beta(aapl_ret, spy_ret) as beta
FROM returns_table;

Build

git clone --recurse-submodules https://github.com/pdet/Scrooge-McDuck.git
cd Scrooge-McDuck
GEN=ninja make

Why Scrooge

Pure SQL -- no Python glue code needed for financial analysis
Privacy -- DuckDB runs locally, portfolio data stays on your machine
Free -- MIT licensed, no cloud costs
Fast -- columnar storage and vectorized execution
Composable -- combine indicators, signals, and data sources in a single query

Roadmap

See EXPANSION_PLAN.md and Discussions.

License

MIT