Search Shortcut cmd + k | ctrl + k

finance

1.0k downloads this week GitHub 10 stars Extension descriptor

SQL-native quant finance functions for DuckDB

Maintainer(s): leonardovida

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

Platform 1.4.4 1.4.5 1.5.5 1.5.6 2.0.0
Linux AMD64
Linux ARM64
macOS AMD64
macOS ARM64
Windows AMD64
Windows ARM64
WebAssembly

Installing and Loading 

INSTALL finance FROM community;
LOAD finance;

Example 

INSTALL finance FROM community;
LOAD finance;
SELECT
  fin_bsm_price('call', 100.0, 100.0, 1.0, 0.05, 0.20) AS price,
  fin_simple_return(105.0, 100.0) AS simple_return;

About finance

DuckDB Finance adds SQL-native quant finance analytics to DuckDB under the fin_ namespace. It includes return and risk analytics, option pricing and Greeks, fixed-income and cash-flow helpers, portfolio math, technical indicators, and table functions. The extension is deterministic and local: it does not call market-data vendors or remote pricing services.

Added Functions

function_name function_type description comment examples
__finance_bar_index aggregate NULL NULL  
__finance_bootstrap_curve aggregate NULL NULL  
fin_accrued_interest scalar Accrued coupon interest between the last coupon date and settlement. NULL [SELECT fin_accrued_interest(DATE '2026-04-01', DATE '2026-01-01', DATE '2026-07-01', 0.04, 100.0, 'ACT/365F') AS accrued;]
fin_active_return macro Annualized mean active return avg(r - benchmark_r) * annualization. NULL [SELECT fin_active_return(r, benchmark_r, 252) AS active_return FROM (VALUES (0.010, 0.008), (-0.020, -0.015), (0.015, 0.010), (0.004, 0.002)) t(r, benchmark_r);]
fin_ad_line aggregate Chaikin accumulation/distribution line sum(((C - L) - (H - C)) / (H - L) * volume); bars with high <= low add 0 (TA-Lib AD). NULL [SELECT fin_ad_line(high, low, close, volume) AS ad_line FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low, 1000 + 10 * (i % 9) AS volume FROM range(60) t(i));]
fin_adf macro Augmented Dickey-Fuller unit-root test with a fixed lag order. NULL [SELECT fin_adf(x, ts, 1, 'c') AS adf FROM (SELECT i AS ts, sin(i * 1.7) + 0.3 * cos(i * 0.9) AS x FROM range(40) t(i));]
fin_adosc aggregate Chaikin A/D oscillator EMA(AD, fast) - EMA(AD, slow) with k = 2 / (period + 1), both EMAs seeded with the first AD value (TA-Lib ADOSC). NULL [SELECT fin_adosc(high, low, close, volume, 3, 10 ORDER BY ts) AS adosc FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low, 1000 + 10 * (i % 9) AS volume FROM range(60) t(i));]
fin_adx aggregate Average directional index: Wilder average of DX, seeded with the mean of the first period DX values (TA-Lib ADX). NULL [SELECT fin_adx(high, low, close, 14 ORDER BY ts) AS adx FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low FROM range(60) t(i));]
fin_adxr aggregate ADX rating (ADX + ADX period - 1 rows earlier) / 2 (TA-Lib ADXR). NULL [SELECT fin_adxr(high, low, close, 14 ORDER BY ts) AS adxr FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low FROM range(60) t(i));]
fin_aggregate_return macro Compound the returns of each group; alias of fin_total_return. NULL [SELECT month, fin_aggregate_return(r, 'simple') AS monthly_return FROM (VALUES (1, 0.01), (1, 0.02), (2, -0.01), (2, 0.03)) t(month, r) GROUP BY month ORDER BY month;]
fin_alpha macro Annualized CAPM (Jensen) alpha (avg(r - rf_p) - beta * avg(b - rf_p)) * annualization over complete pairs. NULL [SELECT fin_alpha(r, benchmark_r, 0.02, 252) AS alpha FROM (VALUES (0.010, 0.008), (-0.020, -0.015), (0.015, 0.010), (0.004, 0.002)) t(r, benchmark_r);]
fin_alpha_beta macro Return CAPM alpha and beta together. NULL [SELECT fin_alpha_beta(r, benchmark_r, 0.02, 252) AS capm FROM (VALUES (0.010, 0.008), (-0.020, -0.015), (0.015, 0.010), (0.004, 0.002)) t(r, benchmark_r);]
fin_amihud_illiquidity aggregate Amihud (2002) illiquidity mean(abs(ret) / dollar_volume) over rows with positive dollar volume. NULL [SELECT fin_amihud_illiquidity(ret, dollar_volume) AS illiquidity FROM (VALUES (0.010, 1.0e6), (-0.020, 2.5e6), (0.005, 0.8e6)) t(ret, dollar_volume);]
fin_annual_return macro Annualized compound return; alias of fin_cagr. NULL [SELECT fin_annual_return(r, 252) AS annual_return FROM (VALUES (0.001), (0.002), (-0.001), (0.0015)) t(r);]
fin_annuity_payment scalar Level payment that amortizes a present value. NULL [SELECT fin_annuity_payment(0.05 / 12, 360.0, 300000.0, 0.0, 'end') AS monthly_payment;]
fin_anova_oneway macro One-way ANOVA F test of equal group means. NULL [SELECT fin_anova_oneway(value, grp) AS anova FROM (VALUES (1.0, 'a'), (2.0, 'a'), (3.0, 'a'), (4.0, 'b'), (5.0, 'b'), (7.0, 'b')) t(value, grp);]
fin_apo aggregate Absolute price oscillator MA(fast) - MA(slow) (TA-Lib APO). NULL [SELECT fin_apo(close, 12, 26, 'ema' ORDER BY ts) AS apo FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_arithmetic_return macro Arithmetic mean return avg(r). NULL [SELECT fin_arithmetic_return(r) AS mean_return FROM (VALUES (0.01), (-0.02), (0.015)) t(r);]
fin_aroon aggregate Aroon over the last period + 1 rows: aroon_up = 100 * (period - rows since the highest high) / period, aroon_down likewise for the lowest low; ties pick the most recent extreme (TA-Lib AROON). NULL [SELECT fin_aroon(high, low, 14 ORDER BY ts) AS aroon FROM (SELECT i AS ts, 101 + 3 * sin(i / 4) + i / 10 AS high, 99 + 3 * sin(i / 4) + i / 10 AS low FROM range(60) t(i));]
fin_aroonosc aggregate Aroon oscillator aroon_up - aroon_down (TA-Lib AROONOSC). NULL [SELECT fin_aroonosc(high, low, 14 ORDER BY ts) AS aroon_oscillator FROM (SELECT i AS ts, 101 + 3 * sin(i / 4) + i / 10 AS high, 99 + 3 * sin(i / 4) + i / 10 AS low FROM range(60) t(i));]
fin_asian_geometric_price scalar Price a continuously monitored geometric-average Asian option in closed form (Kemna-Vorst 1990). NULL [SELECT fin_asian_geometric_price('call', 100.0, 100.0, 1.0, 0.05, 0.2) AS price;]
fin_asset_or_nothing_price scalar Price an asset-or-nothing option. NULL [SELECT fin_asset_or_nothing_price('call', 100.0, 100.0, 1.0, 0.05, 0.2) AS price;]
fin_atr aggregate Average true range: Wilder average of the true range, seeded with the mean of the first period true ranges (TA-Lib ATR). NULL [SELECT fin_atr(high, low, close, 14 ORDER BY ts) AS atr FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low FROM range(60) t(i));]
fin_autocorr macro Lag autocorrelation of a series ordered by ts. NULL [SELECT fin_autocorr(x, ts, 1) AS lag1_autocorr FROM (SELECT i AS ts, sin(i / 3) AS x FROM range(30) t(i));]
fin_avg_drawdown aggregate Mean of the drawdown series NAV_t / peak_t - 1 over all ordered observations. NULL [SELECT fin_avg_drawdown(r ORDER BY ts) AS avg_drawdown FROM (VALUES (1, 0.05), (2, -0.10), (3, 0.02), (4, -0.03), (5, 0.08)) t(ts, r);]
fin_avg_price scalar Average price (open + high + low + close) / 4 (TA-Lib AVGPRICE). NULL [SELECT fin_avg_price(100.0, 102.0, 99.0, 101.0) AS avg_price;]
fin_bachelier_greeks scalar Compute Greeks under the normal forward-price model. NULL [SELECT fin_bachelier_greeks('call', 100.0, 100.0, 1.0, 0.05, 5.0) AS greeks;]
fin_bachelier_implied_vol scalar Solve normal volatility in forward-price units per square root year. NULL [SELECT fin_bachelier_implied_vol('call', 1.9, 100.0, 100.0, 1.0, 0.05) AS normal_vol;]
fin_bachelier_price scalar Price a European option under the Bachelier normal model on a forward: exp(-rT) ((F - K) N(d) + vol sqrt(T) phi(d)) for a call, d = (F - K) / (vol sqrt(T)). NULL [SELECT fin_bachelier_price('call', 100.0, 100.0, 1.0, 0.05, 5.0) AS price;]
fin_bar_spec macro Pack a bar-sampling configuration (tick, volume, dollar or imbalance kind with a positive threshold) into a spec struct. NULL [SELECT fin_bar_spec('volume', 1000.0) AS spec;]
fin_barrier_price scalar Price a continuously monitored European single-barrier option with an optional rebate. NULL [SELECT fin_barrier_price('call', 'up-out', 100.0, 100.0, 120.0, 0.0, 1.0, 0.05, 0.2, 0.0) AS price;]
fin_bbands aggregate Bollinger Bands: middle = SMA(period), upper/lower = middle +/- k * stddev of the last period values (TA-Lib BBANDS with SMA and nbdevup = nbdevdn = k). NULL [SELECT fin_bbands(close, 20, 2.0, 0 ORDER BY ts) AS bands FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_beta aggregate OLS slope cov(r, b) / var(b) over complete pairs. NULL [SELECT fin_beta(r, benchmark_r) AS beta FROM (VALUES (0.010, 0.008), (-0.020, -0.015), (0.015, 0.010), (0.004, 0.002)) t(r, benchmark_r);]
fin_binomial_price scalar Price European or American options with CRR or Jarrow-Rudd trees. NULL [SELECT fin_binomial_price('put', 100.0, 100.0, 1.0, 0.05, 0.2, 0.0, 200, 'american', 'crr') AS price;]
fin_bipower_variation aggregate Estimate annualized bipower variation pi/2 * sum(abs(r_t) * abs(r_{t-1})) * annualization / (n - 1). NULL [SELECT fin_bipower_variation(log_r, 252 ORDER BY ts) AS bipower_variation FROM (VALUES (1, 0.001), (2, -0.002), (3, 0.0015), (4, -0.0005)) t(ts, log_r);]
fin_black76_greeks scalar Compute Greeks under the lognormal forward-price model. NULL [SELECT fin_black76_greeks('call', 100.0, 100.0, 1.0, 0.05, 0.2) AS greeks;]
fin_black76_implied_vol scalar Solve annualized decimal Black-76 volatility. NULL [SELECT fin_black76_implied_vol('call', 7.58, 100.0, 100.0, 1.0, 0.05) AS vol;]
fin_black76_price scalar Price a European option on a forward with Black-76: exp(-rT) (F N(d1) - K N(d2)) for a call. NULL [SELECT fin_black76_price('call', 100.0, 100.0, 1.0, 0.05, 0.2) AS price;]
fin_black_litterman_returns macro Black-Litterman posterior expected returns. NULL [SELECT fin_black_litterman_returns([0.6, 0.4], [[0.04, 0.006], [0.006, 0.09]], [[1.0, -1.0]], [0.02], 0.05, NULL, 2.5) AS posterior_returns;]
fin_bond_convexity scalar Convexity (1 / P) d^2P / dy^2 in years squared of a regular fixed-coupon bond priced at nominal annual yield ytm. NULL [SELECT fin_bond_convexity(0.05, 0.04, 5.0, 2, 100.0) AS convexity;]
fin_bond_duration scalar Macaulay duration in years (default) or modified duration D_mac / (1 + ytm / frequency) of a regular fixed-coupon bond. NULL [SELECT fin_bond_duration(0.05, 0.04, 5.0, 2, 100.0, 'modified') AS modified_duration;]
fin_bond_price scalar Discount a regular coupon bond using nominal annual YTM. NULL [SELECT fin_bond_price(0.05, 0.04, 5.0, 2, 100.0) AS price;]
fin_bond_ytm scalar Solve nominal annual yield for the regular coupon model. NULL [SELECT fin_bond_ytm(104.49, 0.05, 5.0, 2, 100.0) AS ytm;]
fin_bootstrap_curve table Bootstrap a single discount curve from deposit, FRA, par swap, and zero-rate quotes. NULL [WITH quotes AS (SELECT * FROM (VALUES ('deposit', 0.25, 0.040, NULL), ('fra', 0.75, 0.042, 0.25), ('swap', 2.0, 0.045, NULL)) t(kind, maturity, rate, start_time)) SELECT * FROM fin_bootstrap_curve('quotes', 'kind', 'maturity', 'rate', 'continuous', start_col := 'start_time', fixed_frequency := 2);]
fin_bop scalar Balance of power (close - open) / (high - low) per bar (TA-Lib BOP). NULL [SELECT fin_bop(100.0, 102.0, 99.0, 101.0) AS balance_of_power;]
fin_bps scalar Convert a decimal rate to basis points: x * 10000. NULL [SELECT fin_bps(0.0125) AS bps;]
fin_bsm_all scalar Return Black-Scholes-Merton price, Greeks, d1/d2, intrinsic value, and time value from explicit inputs or an option spec. NULL [SELECT fin_bsm_all('call', 100.0, 100.0, 1.0, 0.05, 0.2) AS bsm;]
fin_bsm_charm scalar Return Black-Scholes-Merton charm. NULL [SELECT fin_bsm_charm('call', 100.0, 100.0, 1.0, 0.05, 0.2) AS charm;]
fin_bsm_color scalar Return Black-Scholes-Merton color. NULL [SELECT fin_bsm_color('call', 100.0, 100.0, 1.0, 0.05, 0.2) AS color;]
fin_bsm_d1 scalar Black-Scholes-Merton d1 = (ln(S / K) + (r - q + vol^2 / 2) T) / (vol sqrt(T)). NULL [SELECT fin_bsm_d1(100.0, 100.0, 1.0, 0.05, 0.2, 0.0) AS d1;]
fin_bsm_d2 scalar Black-Scholes-Merton d2 = d1 - vol sqrt(T). NULL [SELECT fin_bsm_d2(100.0, 100.0, 1.0, 0.05, 0.2, 0.0) AS d2;]
fin_bsm_delta scalar Return Black-Scholes-Merton spot delta from explicit inputs or an option spec. NULL [SELECT fin_bsm_delta('put', 100.0, 100.0, 1.0, 0.05, 0.2) AS delta;]
fin_bsm_elasticity scalar Black-Scholes-Merton option elasticity (lambda) delta * S / price: the percentage option-price change per 1% spot move. NULL [SELECT fin_bsm_elasticity('call', 100.0, 100.0, 1.0, 0.05, 0.2) AS elasticity;]
fin_bsm_gamma scalar Return Black-Scholes-Merton gamma from explicit inputs or an option spec. NULL [SELECT fin_bsm_gamma(100.0, 100.0, 1.0, 0.05, 0.2, 0.0) AS gamma;]
fin_bsm_greeks scalar Return Black-Scholes-Merton delta, gamma, vega, theta, and rho from explicit inputs or an option spec. NULL [SELECT fin_bsm_greeks(fin_option_spec('call', 100.0, 100.0, 1.0, 0.05, 0.2)) AS greeks;]
fin_bsm_implied_vol scalar Solve annualized decimal BSM volatility. NULL [SELECT fin_bsm_implied_vol('call', 10.4506, 100.0, 100.0, 1.0, 0.05) AS implied_vol;]
fin_bsm_price scalar Price a Black-Scholes-Merton option from explicit inputs or an option spec; ttm, rate, volatility, and dividend yield are annual decimal values. NULL [SELECT fin_bsm_price('call', 100.0, 100.0, 1.0, 0.05, 0.2, 0.01) AS price;]
fin_bsm_price_dates scalar Black-Scholes-Merton price with time to expiry taken from valuation and expiry dates: ttm = fin_yearfrac(valuation_date, expiry_date, day_count). NULL [SELECT fin_bsm_price_dates('call', 100.0, 100.0, DATE '2026-01-02', DATE '2026-12-18', 0.05, 0.2) AS price;]
fin_bsm_prob_itm scalar Risk-neutral probability of finishing in the money, N(d2). NULL [SELECT fin_bsm_prob_itm('call', 100.0, 110.0, 0.5, 0.05, 0.25) AS prob_itm;]
fin_bsm_prob_touch scalar Risk-neutral probability that spot touches the strike level before expiry. NULL [SELECT fin_bsm_prob_touch('call', 100.0, 110.0, 0.5, 0.05, 0.25) AS prob_touch;]
fin_bsm_rho scalar Return Black-Scholes-Merton rho. NULL [SELECT fin_bsm_rho('call', 100.0, 100.0, 1.0, 0.05, 0.2, 0.0, 'market') AS rho_per_point;]
fin_bsm_speed scalar Black-Scholes-Merton speed dGamma/dS = -Gamma / S * (d1 / (vol sqrt(T)) + 1). NULL [SELECT fin_bsm_speed('call', 100.0, 100.0, 1.0, 0.05, 0.2) AS speed;]
fin_bsm_theta scalar Return Black-Scholes-Merton theta. NULL [SELECT fin_bsm_theta('call', 100.0, 100.0, 1.0, 0.05, 0.2, 0.0, 'day') AS theta_per_day;]
fin_bsm_ultima scalar Black-Scholes-Merton ultima dVomma/dvol = -Vega / vol^2 * (d1 d2 (1 - d1 d2) + d1^2 + d2^2). NULL [SELECT fin_bsm_ultima('call', 100.0, 100.0, 1.0, 0.05, 0.2) AS ultima;]
fin_bsm_vanna scalar Black-Scholes-Merton vanna dDelta/dvol = -exp(-qT) phi(d1) d2 / vol. NULL [SELECT fin_bsm_vanna('call', 100.0, 100.0, 1.0, 0.05, 0.2) AS vanna;]
fin_bsm_vega scalar Return Black-Scholes-Merton vega from explicit inputs or an option spec. NULL [SELECT fin_bsm_vega('call', 100.0, 100.0, 1.0, 0.05, 0.2, 0.0, 'market') AS vega_per_point;]
fin_bsm_vomma scalar Black-Scholes-Merton vomma (volga) dVega/dvol = Vega d1 d2 / vol. NULL [SELECT fin_bsm_vomma('call', 100.0, 100.0, 1.0, 0.05, 0.2) AS vomma;]
fin_bsm_zomma scalar Black-Scholes-Merton zomma dGamma/dvol = Gamma (d1 d2 - 1) / vol. NULL [SELECT fin_bsm_zomma('call', 100.0, 100.0, 1.0, 0.05, 0.2) AS zomma;]
fin_business_days_between scalar Count business days in the half-open date range. NULL [SELECT fin_business_days_between(DATE '2026-12-21', DATE '2026-12-28', 'NYSE') AS business_days;]
fin_cagr macro Compound annual growth rate exp(annualization / n * sum(log1p(r))) - 1, computed in log space. NULL [SELECT fin_cagr(r, 12) AS cagr FROM (VALUES (0.01), (0.02), (-0.01), (0.015)) t(r);]
fin_calendar table Return business-calendar dates for a calendar name and date range. NULL [SELECT * FROM fin_calendar('NYSE', DATE '2026-12-23', DATE '2026-12-28');]
fin_calendar_spec macro Pack a trading calendar (weekday or nyse), time zone and regular session hours into a spec struct. NULL [SELECT fin_calendar_spec('NYSE', 'America/New_York', TIME '09:30:00', TIME '16:00:00') AS spec;]
fin_calmar macro Calmar ratio: fin_cagr(r, annualization) / abs(fin_max_drawdown(r ORDER BY ts)). NULL [SELECT fin_calmar(r, ts, 252) AS calmar FROM (VALUES (1, 0.05), (2, -0.10), (3, 0.02), (4, -0.03), (5, 0.08)) t(ts, r);]
fin_cashflow_spec macro Pack a cash flow into a STRUCT(amount DOUBLE, date DATE, currency VARCHAR) with an upper-cased currency. NULL [SELECT fin_cashflow_spec(1000.0, DATE '2026-06-30', 'usd') AS cashflow;]
fin_cci aggregate Commodity channel index (TP - SMA(TP)) / (constant * mean absolute deviation of TP) over the last period rows, TP = (high + low + close) / 3 (TA-Lib CCI). NULL [SELECT fin_cci(high, low, close, 20, 0.015 ORDER BY ts) AS cci FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low FROM range(60) t(i));]
fin_cents_to_money macro Convert integer minor units to an exact decimal amount: cents / 10^scale. NULL [SELECT fin_cents_to_money(12345, 2) AS amount;]
fin_changes macro Count how many times consecutive non-NULL values differ when ordered by ts (Prometheus changes). NULL [SELECT fin_changes(x, ts) AS changes FROM (VALUES (1, 1.0), (2, 1.0), (3, 2.0), (4, 2.0), (5, 1.0)) t(ts, x);]
fin_changes_to_grid table Flag whether each grid sample differs from the previous grid sample (1.0 changed, 0.0 unchanged). NULL [WITH ticks AS (SELECT TIMESTAMP '2026-01-02 09:30:00' + i * INTERVAL '20 seconds' AS ts, 100 + (i % 5) * 0.1 AS price, 100.0 + 50 * (i % 3) AS volume FROM range(12) t(i)) SELECT * FROM fin_changes_to_grid('ticks', 'ts', 'price', TIMESTAMP '2026-01-02 09:30:00', TIMESTAMP '2026-01-02 09:34:00', INTERVAL '1 minute');]
fin_chi2_cdf scalar Evaluate the chi-square CDF. NULL [SELECT fin_chi2_cdf(11.0705, 5.0) AS p;]
fin_chi2_inv scalar Invert the chi-square CDF. NULL [SELECT fin_chi2_inv(0.95, 5.0) AS x;]
fin_clip scalar Clamp a value to [lower, upper]. NULL [SELECT fin_clip(12.0, 0.0, 10.0) AS clipped;]
fin_cmo aggregate Chande momentum oscillator 100 * (avg_gain - avg_loss) / (avg_gain + avg_loss) with Wilder averages seeded like RSI (TA-Lib CMO). NULL [SELECT fin_cmo(close, 14 ORDER BY ts) AS cmo FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_component_risk macro Compute each weight's contribution to portfolio volatility. NULL [SELECT fin_component_risk([0.7, 0.3], [[0.04, 0.006], [0.006, 0.09]]) AS component_vol;]
fin_conditional_drawdown_at_risk aggregate Conditional drawdown at risk (Chekhlov, Uryasev and Zabarankin 2005): mean of drawdown magnitudes 1 - NAV_t / peak_t at or beyond fin_drawdown_at_risk. NULL [SELECT fin_conditional_drawdown_at_risk(r, 0.8 ORDER BY ts) AS cdar FROM (VALUES (1, 0.05), (2, -0.10), (3, 0.02), (4, -0.03), (5, 0.08)) t(ts, r);]
fin_corr_matrix aggregate Labelled correlation matrix from long-format returns. NULL [SELECT fin_corr_matrix(ts, asset, r) AS corr FROM (VALUES (1, 'AAA', 0.010), (1, 'BBB', 0.004), (2, 'AAA', -0.020), (2, 'BBB', -0.006), (3, 'AAA', 0.015), (3, 'BBB', 0.009), (4, 'AAA', 0.003), (4, 'BBB', -0.002)) t(ts, asset, r);]
fin_cov_matrix aggregate Labelled sample covariance matrix from long-format returns. NULL [SELECT fin_cov_matrix(ts, asset, r) AS cov FROM (VALUES (1, 'AAA', 0.010), (1, 'BBB', 0.004), (2, 'AAA', -0.020), (2, 'BBB', -0.006), (3, 'AAA', 0.015), (3, 'BBB', 0.009), (4, 'AAA', 0.003), (4, 'BBB', -0.002)) t(ts, asset, r);]
fin_cramers_v macro Cramer's V association between two categorical columns. NULL [SELECT fin_cramers_v(x, y, false) AS cramers_v FROM (VALUES ('a', 'u'), ('a', 'u'), ('b', 'v'), ('b', 'v'), ('a', 'v'), ('b', 'u'), ('a', 'u')) t(x, y);]
fin_crosscorr macro Lagged cross-correlation of two series ordered by ts. NULL [SELECT fin_crosscorr(x, y, ts, 1) AS lagged_corr FROM (SELECT i AS ts, sin(i / 3) AS x, sin((i - 1) / 3) AS y FROM range(30) t(i));]
fin_cum_return macro Cumulative compounded return; alias of fin_total_return. NULL [SELECT fin_cum_return(r, 'simple') AS cumulative_return FROM (VALUES (0.01), (0.02), (-0.01)) t(r);]
fin_curve_bootstrap table Alias of fin_bootstrap_curve. NULL [WITH quotes AS (SELECT * FROM (VALUES ('deposit', 0.5, 0.040), ('swap', 1.0, 0.042), ('swap', 2.0, 0.045)) t(kind, maturity, rate)) SELECT * FROM fin_curve_bootstrap('quotes', 'kind', 'maturity', 'rate', fixed_frequency := 2);]
fin_curve_discount_factor scalar Discount using linearly interpolated zero rates and continuous compounding. NULL [SELECT fin_curve_discount_factor([0.5, 1.0, 2.0], [0.04, 0.045, 0.05], 1.5) AS discount_factor;]
fin_curve_spec macro Pack a zero-rate or discount-factor curve with validated value type, interpolation, compounding and day count into a spec struct. NULL [SELECT fin_curve_spec([0.5, 1.0, 2.0], [0.04, 0.045, 0.05], 'zero_rate', 'linear', 'continuous', 'ACT/365F') AS curve;]
fin_curve_zero_rate scalar Linearly interpolate zero rates. NULL [SELECT fin_curve_zero_rate([0.5, 1.0, 2.0], [0.04, 0.045, 0.05], 1.5) AS zero_rate;]
fin_cvar aggregate Conditional value at risk (expected shortfall) at the given confidence. NULL [SELECT fin_cvar(r, 0.8, 'historical', true) AS cvar FROM (VALUES (0.01), (-0.04), (0.02), (-0.01), (0.03), (-0.02), (0.005), (0.015), (-0.03), (0.01)) t(r);]
fin_data_quality_report macro Data-quality summary of a numeric column: row count, NULL count, finite count, min, max and z-score outlier count. NULL [SELECT fin_data_quality_report(x) AS report FROM (VALUES (1.0), (NULL), ('nan'::DOUBLE), (2.0)) t(x);]
fin_delta macro Last minus first non-NULL value when ordered by ts. NULL [SELECT fin_delta(x, ts) AS delta FROM (VALUES (1, 10.0), (2, 12.0), (3, 11.5)) t(ts, x);]
fin_delta_to_grid table Difference between each grid sample and the previous grid sample. NULL [WITH ticks AS (SELECT TIMESTAMP '2026-01-02 09:30:00' + i * INTERVAL '20 seconds' AS ts, 100 + (i % 5) * 0.1 AS price, 100.0 + 50 * (i % 3) AS volume FROM range(12) t(i)) SELECT * FROM fin_delta_to_grid('ticks', 'ts', 'price', TIMESTAMP '2026-01-02 09:30:00', TIMESTAMP '2026-01-02 09:34:00', INTERVAL '1 minute');]
fin_dema aggregate Double exponential moving average 2 * EMA - EMA(EMA); each EMA stage is seeded with the SMA of its first period inputs (TA-Lib DEMA). NULL [SELECT fin_dema(close, 20 ORDER BY ts) AS dema FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_digital_price scalar Price a cash-or-nothing digital option. NULL [SELECT fin_digital_price('call', 100.0, 100.0, 1.0, 0.05, 0.2, 0.0, 10.0) AS price;]
fin_discount_factor scalar Discount factor for an annual decimal rate over t years under a compounding convention. NULL [SELECT fin_discount_factor(0.05, 2.0, 'semiannual', 2) AS discount_factor;]
fin_dollar_bars table Aggregate trades into dollar bars that close once accumulated abs(price) * volume reaches the threshold. NULL [WITH ticks AS (SELECT TIMESTAMP '2026-01-02 09:30:00' + i * INTERVAL '20 seconds' AS ts, 100 + (i % 5) * 0.1 AS price, 100.0 + 50 * (i % 3) AS volume FROM range(12) t(i)) SELECT * FROM fin_dollar_bars('ticks', 'ts', 'price', 'volume', 40000.0);]
fin_donchian aggregate Donchian channel over the last period rows: upper = highest high, lower = lowest low, middle = (upper + lower) / 2. NULL [SELECT fin_donchian(high, low, 20 ORDER BY ts) AS channel FROM (SELECT i AS ts, 101 + 3 * sin(i / 4) + i / 10 AS high, 99 + 3 * sin(i / 4) + i / 10 AS low FROM range(60) t(i));]
fin_dot scalar Dot product of two lists. NULL [SELECT fin_dot([1.0, 2.0, 3.0], [4.0, 5.0, 6.0]) AS dot;]
fin_down_capture macro Down-market capture avg(r) FILTER (b < 0) / avg(b) FILTER (b < 0) over complete pairs. NULL [SELECT fin_down_capture(r, benchmark_r) AS down_capture FROM (VALUES (0.010, 0.008), (-0.020, -0.015), (0.015, 0.010), (-0.004, -0.006)) t(r, benchmark_r);]
fin_downside_deviation aggregate Annualized downside deviation sqrt(mean(min(r - mar_p, 0)^2)) * sqrt(annualization) over all observations. NULL [SELECT fin_downside_deviation(r, 0.0, 252) AS downside_deviation FROM (VALUES (0.01), (-0.02), (0.015), (-0.005), (0.012)) t(r);]
fin_drawdown aggregate Current drawdown NAV_n / peak_n - 1 at the last ordered observation. NULL [SELECT fin_drawdown(r ORDER BY ts) AS current_drawdown FROM (VALUES (1, 0.05), (2, -0.10), (3, 0.02)) t(ts, r);]
fin_drawdown_at_risk aggregate Drawdown at risk (Chekhlov et al. 2005): linear-interpolation confidence quantile of drawdown magnitudes 1 - NAV_t / peak_t. NULL [SELECT fin_drawdown_at_risk(r, 0.8 ORDER BY ts) AS dar FROM (VALUES (1, 0.05), (2, -0.10), (3, 0.02), (4, -0.03), (5, 0.08)) t(ts, r);]
fin_drawdown_duration aggregate Longest run of consecutive observations below the running peak. NULL [SELECT fin_drawdown_duration(r ORDER BY ts) AS longest_drawdown_rows FROM (VALUES (1, 0.05), (2, -0.10), (3, 0.02), (4, -0.03), (5, 0.20)) t(ts, r);]
fin_dv01 scalar Dollar value of one basis point: price change P(ytm - 0.0001) - P(ytm) of a regular coupon bond for a 1 bp fall in yield. NULL [SELECT fin_dv01(0.05, 0.04, 5.0, 2, 100.0) AS dv01;]
fin_dx aggregate Directional movement index 100 * abs(+DI - -DI) / (+DI + -DI) (TA-Lib DX). NULL [SELECT fin_dx(high, low, close, 14 ORDER BY ts) AS dx FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low FROM range(60) t(i));]
fin_efficient_frontier table Markowitz mean-variance efficient frontier: minimum-variance fully invested portfolios for evenly spaced target returns from the global minimum-variance return to max(mu). NULL [SELECT * FROM fin_efficient_frontier([0.08, 0.12], [[0.04, 0.006], [0.006, 0.09]], 5, false);]
fin_ema aggregate Exponential moving average ema = k * x + (1 - k) * ema, k = 2 / (period + 1), seeded with the SMA of the first period values (TA-Lib EMA). NULL [SELECT fin_ema(close, 10 ORDER BY ts) AS ema FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_ema_halflife aggregate Time-aware exponential moving average with a half-life. NULL [SELECT fin_ema_halflife(x, ts, INTERVAL '1 day') AS ema FROM (VALUES (TIMESTAMP '2026-01-01', 10.0), (TIMESTAMP '2026-01-02', 12.0), (TIMESTAMP '2026-01-03', 11.0)) t(ts, x);]
fin_entropy macro Shannon entropy of the empirical distribution of x. NULL [SELECT fin_entropy(x) AS entropy_bits FROM (VALUES ('a'), ('a'), ('b'), ('c')) t(x);]
fin_equal_weights scalar Equal portfolio weights: a list of n entries of 1 / n. NULL [SELECT fin_equal_weights(4) AS weights;]
fin_ewma_variance aggregate Annualized RiskMetrics EWMA variance of ordered returns: s2_t = lambda s2_(t-1) + (1 - lambda) r_t^2, times annualization. NULL [SELECT fin_ewma_variance(r, 0.94, 252.0 ORDER BY ts) AS ewma_variance FROM (VALUES (1, 0.01), (2, -0.02), (3, 0.015), (4, -0.005)) t(ts, r);]
fin_ewma_vol aggregate Annualized RiskMetrics EWMA volatility of ordered returns: square root of fin_ewma_variance. NULL [SELECT fin_ewma_vol(r, 0.94, 252.0 ORDER BY ts) AS ewma_vol FROM (VALUES (1, 0.01), (2, -0.02), (3, 0.015), (4, -0.005)) t(ts, r);]
fin_excess_return macro Per-period excess return r - rf_p. NULL [SELECT fin_excess_return(0.001, 0.05, 252, 'annual') AS excess_return;]
fin_exp_decay_avg aggregate Exponentially time-decayed mean. NULL [SELECT fin_exp_decay_avg(x, ts, 2.0) AS decayed_mean FROM (VALUES (1.0, 10.0), (2.0, 12.0), (3.0, 11.0)) t(ts, x);]
fin_exp_decay_count aggregate Exponentially time-decayed row count. NULL [SELECT fin_exp_decay_count(ts, INTERVAL '1 hour') AS decayed_count FROM (VALUES (TIMESTAMP '2026-01-02 09:00:00'), (TIMESTAMP '2026-01-02 10:00:00'), (TIMESTAMP '2026-01-02 11:00:00')) t(ts);]
fin_exp_decay_max aggregate Maximum of exponentially time-decayed values x_i 2^(-(t_max - t_i) / halflife). NULL [SELECT fin_exp_decay_max(x, ts, 1.0) AS decayed_max FROM (VALUES (1.0, 20.0), (2.0, 12.0), (3.0, 11.0)) t(ts, x);]
fin_exp_decay_sum aggregate Exponentially time-decayed sum. NULL [SELECT fin_exp_decay_sum(x, ts, 2.0) AS decayed_sum FROM (VALUES (1.0, 10.0), (2.0, 12.0), (3.0, 11.0)) t(ts, x);]
fin_expectancy macro Expected return per period avg(r). NULL [SELECT fin_expectancy(r) AS expectancy FROM (VALUES (0.02), (-0.01), (0.03), (-0.015)) t(r);]
fin_expected_shortfall macro Positive expected shortfall; alias of fin_cvar(r, confidence, method, true). NULL [SELECT fin_expected_shortfall(r, 0.8, 'historical') AS expected_shortfall FROM (VALUES (0.01), (-0.04), (0.02), (-0.01), (0.03), (-0.02), (0.005), (0.015), (-0.03), (0.01)) t(r);]
fin_factor_alpha macro Annualized single-factor alpha; alias of fin_alpha. NULL [SELECT fin_factor_alpha(r, factor_r, 0.02, 252) AS alpha FROM (VALUES (0.010, 0.008), (-0.020, -0.015), (0.015, 0.010), (0.004, 0.002)) t(r, factor_r);]
fin_factor_ic macro Information coefficient between factor values and forward returns. NULL [SELECT fin_factor_ic(factor, forward_return, 'spearman') AS ic FROM (VALUES (1.0, 0.01), (2.0, 0.00), (3.0, 0.02), (4.0, 0.03), (5.0, 0.025)) t(factor, forward_return);]
fin_factor_report table Cross-sectional factor diagnostics: per-date Pearson IC, Spearman rank IC, and top-minus-bottom quantile spread, averaged across dates. NULL [WITH panel AS (SELECT d, asset, (asset * 7 + d * 3) % 10 AS factor, ((asset * 7 + d * 3) % 10) * 0.002 + ((asset + d) % 3) * 0.001 AS fwd FROM range(1, 5) a(asset), range(1, 4) b(d)) SELECT * FROM fin_factor_report('panel', 'd', 'asset', 'factor', 'fwd', 2);]
fin_factor_turnover macro Factor rank turnover: one minus the mean rank autocorrelation. NULL [SELECT fin_factor_turnover(ts, asset, factor, 1) AS turnover FROM (VALUES (1, 'A', 1.0), (1, 'B', 2.0), (1, 'C', 3.0), (2, 'A', 1.0), (2, 'B', 3.0), (2, 'C', 2.0), (3, 'A', 2.0), (3, 'B', 3.0), (3, 'C', 1.0)) t(ts, asset, factor);]
fin_fama_macbeth table Fama-MacBeth (1973) two-pass regression: per-date OLS of y on all x_cols with an intercept, then the time-series mean of each coefficient. NULL [WITH panel AS (SELECT d, asset, (asset * 7 + d * 3) % 10 AS factor, ((asset * 7 + d * 3) % 10) * 0.002 + ((asset + d) % 3) * 0.001 AS fwd FROM range(1, 6) a(asset), range(1, 5) b(d)) SELECT * FROM fin_fama_macbeth('panel', 'd', 'asset', 'fwd', ['factor']);]
fin_first_non_null macro First non-NULL value when ordered by ts. NULL [SELECT fin_first_non_null(x, ts) AS first_value FROM (VALUES (1, NULL), (2, 12.0), (3, 11.0)) t(ts, x);]
fin_forward_price scalar Cost-of-carry forward price spot * exp((rate - dividend_yield) * ttm). NULL [SELECT fin_forward_price(100.0, 1.0, 0.05, 0.02) AS forward;]
fin_forward_rate scalar Forward rate between t1 and t2 implied by discount factors df1 and df2: the rate whose discount factor over t2 - t1 years is df2 / df1. NULL [SELECT fin_forward_rate(0.9608, 0.8869, 1.0, 2.0, 'continuous') AS forward_rate;]
fin_fra_rate scalar Forward rate between two zero-rate maturities. NULL [SELECT fin_fra_rate(0.04, 0.05, 1.0, 2.0) AS forward_rate;]
fin_from_bps scalar Convert basis points to a decimal rate: bps / 10000. NULL [SELECT fin_from_bps(125.0) AS decimal_rate;]
fin_from_log_return macro Convert a log return to a simple return expm1(lr). NULL [SELECT fin_from_log_return(0.05) AS simple_return;]
fin_future_value scalar Future value present_value / DF(rate, t) of an amount after t years. NULL [SELECT fin_future_value(100.0, 0.05, 2.0, 'annual') AS future_value;]
fin_gain_to_pain macro Gain-to-pain ratio (Schwager): sum(r) / abs(sum(r) FILTER (r < 0)). NULL [SELECT fin_gain_to_pain(r) AS gain_to_pain FROM (VALUES (0.02), (-0.01), (0.03), (-0.015)) t(r);]
fin_garch11_forecast aggregate One-step-ahead GARCH(1,1) variance: filters sigma2_{t+1} = omega + alpha r_t^2 + beta sigma2_t through the ordered returns and returns annualization * sigma2_{n+1}. NULL [SELECT fin_garch11_forecast(r, 0.000002, 0.08, 0.9 ORDER BY ts) AS annualized_variance FROM (VALUES (1, 0.01), (2, -0.02), (3, 0.015), (4, -0.005), (5, 0.007)) t(ts, r);]
fin_garch_fit table Maximum-likelihood GARCH(1,1) fit of a zero-mean return series ordered by order_col. NULL [WITH returns AS (SELECT i AS t, 0.01 * sin(i * 1.7) * (1 + (i % 7) / 3) AS r FROM range(200) t(i)) SELECT * FROM fin_garch_fit('returns', 'r', 't', 1, 1, 'normal');]
fin_garman_klass_vol macro Garman-Klass (1980) volatility sqrt(annualization * mean(0.5 ln(H/L)^2 - (2 ln 2 - 1) ln(C/O)^2)). NULL [SELECT fin_garman_klass_vol(open, high, low, close, 252) AS vol FROM (VALUES (100.0, 102.0, 99.0, 101.0), (101.0, 103.0, 100.5, 102.5), (102.5, 103.0, 100.0, 100.5)) t(open, high, low, close);]
fin_geometric_return macro Geometric mean per-period return exp(mean(log1p(r))) - 1. NULL [SELECT fin_geometric_return(r) AS geometric_mean FROM (VALUES (0.10), (-0.05), (0.08)) t(r);]
fin_gross_return macro Gross return 1 + r of a decimal simple return. NULL [SELECT fin_gross_return(r) AS gross_return FROM (VALUES (0.10), (-0.05), (0.08)) t(r);]
fin_half_life_mean_reversion macro Half-life of mean reversion from an AR(1) fit. NULL [SELECT fin_half_life_mean_reversion(x, ts) AS half_life FROM (VALUES (1, 1.0), (2, 0.6), (3, 0.5), (4, 0.2), (5, 0.25), (6, 0.05), (7, 0.1), (8, -0.05)) t(ts, x);]
fin_hit_ratio macro Fraction of observations above the per-period threshold. NULL [SELECT fin_hit_ratio(r, 0.0, 252) AS hit_ratio FROM (VALUES (0.02), (-0.01), (0.03), (-0.015)) t(r);]
fin_hma aggregate Hull moving average WMA(2 * WMA(x, floor(period / 2)) - WMA(x, period), floor(sqrt(period))) (Alan Hull; not part of TA-Lib). NULL [SELECT fin_hma(close, 16 ORDER BY ts) AS hma FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_hrp_weights table Hierarchical risk parity weights (Lopez de Prado 2016) from a covariance matrix. NULL [SELECT * FROM fin_hrp_weights([[0.04, 0.006, 0.01], [0.006, 0.09, 0.02], [0.01, 0.02, 0.0625]], ['AAA', 'BBB', 'CCC'], 'single');]
fin_hurst macro Hurst exponent by rescaled-range (R/S) analysis. NULL [SELECT fin_hurst(x, ts) AS hurst FROM (SELECT i AS ts, sin(i * 1.3) + 0.5 * sin(i * 0.37) AS x FROM range(64) t(i));]
fin_imbalance_bars table Fixed-threshold volume imbalance bars (AFML ch. 2.3.2): the bar closes once abs(sum of signed volume) reaches the threshold. NULL [WITH ticks AS (SELECT TIMESTAMP '2026-01-02 09:30:00' + i * INTERVAL '20 seconds' AS ts, 100 + (i % 5) * 0.1 AS price, 100.0 + 50 * (i % 3) AS volume FROM range(12) t(i)) SELECT * FROM fin_imbalance_bars('ticks', 'ts', 'price', 'volume', 300.0, 'tick_rule');]
fin_information_ratio macro Annualized information ratio avg(r - b) / stddev_samp(r - b) * sqrt(annualization). NULL [SELECT fin_information_ratio(r, benchmark_r, 252) AS information_ratio FROM (VALUES (0.010, 0.008), (-0.020, -0.015), (0.015, 0.010), (0.004, 0.002)) t(r, benchmark_r);]
fin_interpolate_curve scalar Linearly interpolate finite values over strictly increasing maturities. NULL [SELECT fin_interpolate_curve([0.5, 1.0, 2.0], [0.04, 0.045, 0.05], 1.5) AS value;]
fin_inverse_vol_weights scalar Inverse-volatility weights (1 / vol_i) / sum_j (1 / vol_j). NULL [SELECT fin_inverse_vol_weights([0.10, 0.20, 0.40]) AS weights;]
fin_irr scalar Internal rate of return of periodic cash flows: the per-period rate where sum(cf_t / (1 + irr)^t) = 0. NULL [SELECT fin_irr([-100.0, 60.0, 60.0]) AS irr;]
fin_is_business_day scalar Check whether a date is a business day. NULL [SELECT fin_is_business_day(DATE '2026-12-25', 'NYSE') AS is_business_day;]
fin_is_decimal_return macro Check that a value looks like a decimal return rather than a percentage; alias of fin_validate_return(x). NULL [SELECT fin_is_decimal_return(0.05) AS decimal_ok, fin_is_decimal_return(5.0) AS percent_like;]
fin_is_finite scalar Check that a value is neither NaN nor infinite. NULL [SELECT fin_is_finite('inf'::DOUBLE) AS finite;]
fin_is_outlier_zscore scalar Flag x when its absolute z-score abs(x - mean) / stddev exceeds threshold (default 3). NULL [SELECT fin_is_outlier_zscore(3.1, 0.0, 1.0, 3.0) AS is_outlier;]
fin_is_price scalar Check that a value is a usable price. NULL [SELECT fin_is_price(0.0) AS zero_ok, fin_is_price(0.0, false) AS strictly_positive;]
fin_is_rate scalar Check that a value is a usable decimal rate. NULL [SELECT fin_is_rate(-0.005) AS negative_ok, fin_is_rate(-0.005, false) AS non_negative;]
fin_is_regular_session scalar Check whether a timestamp falls in the regular trading session. NULL [SELECT fin_is_regular_session(TIMESTAMP '2026-11-27 14:00:00', 'NYSE') AS in_session;]
fin_is_vol scalar Check that a value is a usable decimal volatility. NULL [SELECT fin_is_vol(0.2) AS valid_vol;]
fin_iv_percentile aggregate Count the fraction of prior IV observations strictly below the latest IV. NULL [SELECT fin_iv_percentile(iv ORDER BY quote_date) AS iv_percentile FROM (VALUES (DATE '2026-01-02', 0.20), (DATE '2026-01-05', 0.25), (DATE '2026-01-06', 0.18), (DATE '2026-01-07', 0.22)) t(quote_date, iv);]
fin_iv_rank aggregate Compute where the latest implied volatility sits within the observed min/max range. NULL [SELECT fin_iv_rank(iv ORDER BY quote_date) AS iv_rank FROM (VALUES (DATE '2026-01-02', 0.20), (DATE '2026-01-05', 0.25), (DATE '2026-01-06', 0.18), (DATE '2026-01-07', 0.22)) t(quote_date, iv);]
fin_jensen_alpha macro Jensen's alpha; alias of fin_alpha. NULL [SELECT fin_jensen_alpha(r, benchmark_r, 0.02, 252) AS jensen_alpha FROM (VALUES (0.010, 0.008), (-0.020, -0.015), (0.015, 0.010), (0.004, 0.002)) t(r, benchmark_r);]
fin_kahan_sum macro Compensated sum via DuckDB fsum. NULL [SELECT fin_kahan_sum(0.1::DOUBLE) AS total FROM range(10);]
fin_kama aggregate Kaufman adaptive moving average: efficiency ratio abs(x_t - x_(t-period)) / sum(abs(x_i - x_(i-1))) maps to the smoothing constant (ER * (2/(fast+1) - 2/(slow+1)) + 2/(slow+1))^2, seeded with the price at row period - 1 (TA-Lib KAMA, which fixes fast 2 and slow 30). NULL [SELECT fin_kama(close, 10, 2, 30 ORDER BY ts) AS kama FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_keltner aggregate Keltner channel: middle = EMA(close, period) (TA-Lib seeding) and bands middle +/- multiplier * ATR(atr_period) with TA-Lib Wilder ATR (StockCharts definition; not part of TA-Lib). NULL [SELECT fin_keltner(high, low, close, 20, 10, 2.0 ORDER BY ts) AS channel FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low FROM range(60) t(i));]
fin_ks_test aggregate Two-sample Kolmogorov-Smirnov test. NULL [SELECT fin_ks_test(x, y) AS ks FROM (VALUES (0.1, 0.4), (0.2, 0.5), (0.3, 0.6), (0.35, 0.7), (NULL, 0.8)) t(x, y);]
fin_kyle_lambda aggregate Kyle (1985) price impact: OLS slope (with intercept) of price_change on signed_volume. NULL [SELECT fin_kyle_lambda(price_change, signed_volume) AS kyle_lambda FROM (VALUES (0.02, 100.0), (-0.01, -60.0), (0.03, 140.0), (-0.02, -90.0)) t(price_change, signed_volume);]
fin_last_non_null macro Last non-NULL value when ordered by ts. NULL [SELECT fin_last_non_null(x, ts) AS last_value FROM (VALUES (1, 10.0), (2, 12.0), (3, NULL)) t(ts, x);]
fin_last_to_grid table Alias of fin_resample_grid (last observation at or before each grid point). NULL [WITH ticks AS (SELECT TIMESTAMP '2026-01-02 09:30:00' + i * INTERVAL '20 seconds' AS ts, 100 + (i % 5) * 0.1 AS price, 100.0 + 50 * (i % 3) AS volume FROM range(12) t(i)) SELECT * FROM fin_last_to_grid('ticks', 'ts', 'price', TIMESTAMP '2026-01-02 09:30:00', TIMESTAMP '2026-01-02 09:34:00', INTERVAL '1 minute');]
fin_linear_trend macro Ordinary least-squares trend of y on an explicit numeric axis x. NULL [SELECT fin_linear_trend(y, x) AS trend FROM (VALUES (1.0, 1.0), (2.1, 2.0), (2.9, 3.0), (4.2, 4.0)) t(y, x);]
fin_linearreg aggregate Least-squares line over the last period values (x = 0 for the oldest row) evaluated at the newest row (TA-Lib LINEARREG). NULL [SELECT fin_linearreg(close, 14 ORDER BY ts) AS linearreg FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_linearreg_intercept aggregate Intercept of the least-squares line over the last period values, i.e. its value at the oldest row of the lookback (TA-Lib LINEARREG_INTERCEPT). NULL [SELECT fin_linearreg_intercept(close, 14 ORDER BY ts) AS intercept FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_linearreg_slope aggregate Slope per row of the least-squares line over the last period values (TA-Lib LINEARREG_SLOPE). NULL [SELECT fin_linearreg_slope(close, 14 ORDER BY ts) AS slope FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_ljung_box macro Ljung-Box portmanteau test for autocorrelation. NULL [SELECT fin_ljung_box(x, ts, 5) AS ljung_box FROM (SELECT i AS ts, sin(i / 3) AS x FROM range(40) t(i));]
fin_log_nav macro Log NAV ln(initial_nav) + sum(log1p(r)). NULL [SELECT fin_log_nav(r, 100.0) AS log_nav FROM (VALUES (0.01), (0.02), (-0.01)) t(r);]
fin_log_return macro Log return ln(price / prev_price) between two prices. NULL [SELECT fin_log_return(105.0, 100.0) AS log_return;]
fin_loss_rate macro Fraction of non-NULL observations below zero. NULL [SELECT fin_loss_rate(r) AS loss_rate FROM (VALUES (0.02), (-0.01), (0.03), (-0.015)) t(r);]
fin_macd aggregate MACD line EMA(fast) - EMA(slow), signal = EMA(signal) of the line and hist = macd - signal (TA-Lib MACD: both EMAs start at row slow - 1, the fast one seeded with the SMA of the fast rows ending there). NULL [SELECT fin_macd(close, 12, 26, 9 ORDER BY ts) AS macd FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_mad macro Median absolute deviation via DuckDB mad. NULL [SELECT fin_mad(x) AS mad FROM (VALUES (1.0), (2.0), (2.0), (4.0), (10.0)) t(x);]
fin_mann_whitney_u aggregate Mann-Whitney U rank-sum test. NULL [SELECT fin_mann_whitney_u(x, y) AS mwu FROM (VALUES (1.0, 4.0), (2.0, 5.0), (3.0, 6.0), (3.5, 7.0)) t(x, y);]
fin_marginal_risk macro Multiply the covariance matrix by portfolio weights. NULL [SELECT fin_marginal_risk([0.7, 0.3], [[0.04, 0.006], [0.006, 0.09]]) AS cov_times_weights;]
fin_matrix_cholesky scalar Compute a Cholesky factor for a symmetric positive semidefinite matrix. NULL [SELECT fin_matrix_cholesky([[4.0, 2.0], [2.0, 3.0]]) AS lower;]
fin_matrix_is_psd scalar Check whether a symmetric matrix is positive semidefinite. NULL [SELECT fin_matrix_is_psd([[1.0, 0.9], [0.9, 1.0]]) AS psd, fin_matrix_is_psd([[1.0, 2.0], [2.0, 1.0]]) AS not_psd;]
fin_matrix_mul scalar Multiply two matrices. NULL [SELECT fin_matrix_mul([[1.0, 2.0], [3.0, 4.0]], [[5.0], [6.0]]) AS product;]
fin_matrix_shape scalar Row and column count of a matrix given as a list of rows. NULL [SELECT fin_matrix_shape([[1.0, 2.0, 3.0], [4.0, 5.0, 6.0]]) AS shape;]
fin_matrix_transpose scalar Transpose of a matrix given as a list of rows. NULL [SELECT fin_matrix_transpose([[1.0, 2.0, 3.0], [4.0, 5.0, 6.0]]) AS transposed;]
fin_matrix_vecmul scalar Matrix-vector product A x for a matrix given as a list of rows. NULL [SELECT fin_matrix_vecmul([[1.0, 2.0], [3.0, 4.0]], [1.0, 0.5]) AS product;]
fin_max_drawdown aggregate Maximum drawdown min_t(NAV_t / peak_t - 1) over the ordered returns. NULL [SELECT fin_max_drawdown(r ORDER BY ts) AS max_drawdown FROM (VALUES (1, 0.05), (2, -0.10), (3, 0.02), (4, -0.03), (5, 0.08)) t(ts, r);]
fin_max_sharpe_weights macro Maximum-Sharpe (tangency) portfolio weights summing to one. NULL [SELECT fin_max_sharpe_weights([0.08, 0.12], [[0.04, 0.006], [0.006, 0.09]], 0.02, true) AS weights;]
fin_median_price scalar Median price (high + low) / 2 (TA-Lib MEDPRICE). NULL [SELECT fin_median_price(102.0, 99.0) AS median_price;]
fin_mfi aggregate Money flow index over the last period typical-price changes: money flow TP * volume counts as positive when TP = (H + L + C) / 3 rises and negative when it falls; MFI = 100 * positive / (positive + negative) (TA-Lib MFI). NULL [SELECT fin_mfi(high, low, close, volume, 14 ORDER BY ts) AS mfi FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low, 1000 + 10 * (i % 9) AS volume FROM range(60) t(i));]
fin_microprice scalar Size-weighted midpoint (bid * ask_size + ask * bid_size) / (bid_size + ask_size). NULL [SELECT fin_microprice(99.98, 500.0, 100.02, 300.0) AS microprice;]
fin_mid scalar Quote midpoint (bid + ask) / 2. NULL [SELECT fin_mid(99.98, 100.02) AS mid;]
fin_min_variance_weights macro Global minimum-variance portfolio weights summing to one. NULL [SELECT fin_min_variance_weights([[0.04, 0.006], [0.006, 0.09]], true) AS weights;]
fin_minus_di aggregate Minus directional indicator 100 * S(-DM) / S(TR) (TA-Lib MINUS_DI). NULL [SELECT fin_minus_di(high, low, close, 14 ORDER BY ts) AS minus_di FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low FROM range(60) t(i));]
fin_minus_dm aggregate Wilder-smoothed minus directional movement: -DM = previous low - low when positive and larger than high - previous high, else 0, smoothed as fin_plus_dm (TA-Lib MINUS_DM). NULL [SELECT fin_minus_dm(high, low, 14 ORDER BY ts) AS minus_dm FROM (SELECT i AS ts, 101 + 3 * sin(i / 4) + i / 10 AS high, 99 + 3 * sin(i / 4) + i / 10 AS low FROM range(60) t(i));]
fin_mirr scalar Modified internal rate of return of periodic cash flows: negative flows discounted at finance_rate, positive flows compounded to the end at reinvest_rate. NULL [SELECT fin_mirr([-100.0, 60.0, 60.0], 0.1, 0.05) AS mirr;]
fin_missing_count macro Count the NULL rows of a column. NULL [SELECT fin_missing_count(x) AS missing FROM (VALUES (1.0), (NULL), ('nan'::DOUBLE), (2.0)) t(x);]
fin_mom aggregate Momentum x_t - x_(t-period) (TA-Lib MOM). NULL [SELECT fin_mom(close, 10 ORDER BY ts) AS momentum FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_money_round macro Round a money amount to scale decimal places with an explicit rounding mode. NULL [SELECT fin_money_round(2.345, 2, 'half_even') AS rounded;]
fin_money_sum macro Sum money amounts. NULL [SELECT fin_money_sum(amount) AS total FROM (VALUES (10.10::DECIMAL(18, 2)), (20.20), (0.05)) t(amount);]
fin_money_to_cents macro Convert an amount to integer minor units (cents) with exact decimal rounding. NULL [SELECT fin_money_to_cents(12.345, 'nearest') AS cents;]
fin_money_weighted_sum macro Weighted sum of amounts, sum(amount * weight). NULL [SELECT fin_money_weighted_sum(amount, weight) AS weighted FROM (VALUES (100.0, 0.5), (200.0, 0.25)) t(amount, weight);]
fin_mutual_information macro Binned mutual information of two numeric columns. NULL [SELECT fin_mutual_information(x, y, 2) AS mutual_information_nats FROM (VALUES (1.0, 1.1), (2.0, 2.2), (3.0, 2.9), (4.0, 4.2), (5.0, 5.1), (6.0, 5.8)) t(x, y);]
fin_natr aggregate Normalized ATR 100 * ATR / close (TA-Lib NATR). NULL [SELECT fin_natr(high, low, close, 14 ORDER BY ts) AS natr FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low FROM range(60) t(i));]
fin_nav macro Ending NAV initial_nav * exp(sum(log1p(r))). NULL [SELECT fin_nav(r, 100.0) AS nav FROM (VALUES (0.01), (0.02), (-0.01)) t(r);]
fin_nearest_psd scalar Project a symmetric matrix onto the positive semidefinite cone. NULL [SELECT fin_nearest_psd([[1.0, 0.9, 0.2], [0.9, 1.0, 0.9], [0.2, 0.9, 1.0]], 'higham') AS nearest_correlation;]
fin_newey_west_tstat macro HAC (Newey-West) t-statistic of the slope in y = a + b x. NULL [SELECT fin_newey_west_tstat(y, x, ts, 1) AS hac_tstat FROM (VALUES (1, 0.9, 1.0), (2, 2.3, 2.0), (3, 2.8, 3.0), (4, 4.4, 4.0), (5, 4.9, 5.0), (6, 6.2, 6.0)) t(ts, y, x);]
fin_next_business_day scalar Advance by business days, excluding the start for positive offsets. NULL [SELECT fin_next_business_day(DATE '2026-12-24', 'NYSE', 1) AS next_day;]
fin_norm_cdf scalar Standard normal cumulative distribution function N(x). NULL [SELECT fin_norm_cdf(1.96) AS p;]
fin_norm_inv scalar Invert the standard normal CDF. NULL [SELECT fin_norm_inv(0.975) AS z;]
fin_norm_pdf scalar Standard normal density exp(-x^2 / 2) / sqrt(2 pi). NULL [SELECT fin_norm_pdf(0.0) AS density;]
fin_normalize_currency scalar Normalize a three-letter currency code. NULL [SELECT fin_normalize_currency(' usd ') AS currency;]
fin_normalize_ohlcv table Project source OHLCV columns into canonical ts, asset_id, open, high, low, close, and volume fields. NULL [WITH bars AS (SELECT * FROM (VALUES (TIMESTAMP '2026-01-02 09:30:00', 'AAA', 100, 101, 99, 100.5, 1200)) t(bar_time, symbol, o, h, l, c, v)) SELECT * FROM fin_normalize_ohlcv('bars', 'bar_time', 'o', 'h', 'l', 'c', 'v', 'symbol');]
fin_normalize_option_chain table Project source option columns into canonical option fields and an option_spec struct for BSM functions. NULL [WITH quotes AS (SELECT * FROM (VALUES ('C', 100.0, 105.0, DATE '2026-12-18', DATE '2026-06-18', 0.05, 0.22, 0.01)) t(cp, underlying_px, strike_px, expiry_dt, valuation_dt, zero_rate, iv, q)) SELECT * FROM fin_normalize_option_chain('quotes', 'cp', 'underlying_px', 'strike_px', 'expiry_dt', 'valuation_dt', 'zero_rate', 'iv', 'q');]
fin_normalize_returns table Project source return columns into canonical date, asset_id, and return_decimal fields. NULL [WITH returns AS (SELECT * FROM (VALUES (DATE '2026-01-02', 'AAA', 0.010), (DATE '2026-01-05', 'AAA', -0.004)) t(d, symbol, ret)) SELECT * FROM fin_normalize_returns('returns', 'd', 'symbol', 'ret');]
fin_npv scalar Net present value of cash flows. NULL [SELECT fin_npv(0.1, [-100.0, 60.0, 60.0]) AS npv;]
fin_obv aggregate On-balance volume: starts at the first row's volume, then adds the volume when the close rises and subtracts it when the close falls (TA-Lib OBV). NULL [SELECT fin_obv(close, volume ORDER BY ts) AS obv FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low, 1000 + 10 * (i % 9) AS volume FROM range(60) t(i));]
fin_ohlc macro Open, high, low and close of the group: open and close are the prices at the first and last ts. NULL [SELECT fin_ohlc(price, ts) AS bar FROM (VALUES (TIMESTAMP '2026-01-02 09:30:00', 100.0), (TIMESTAMP '2026-01-02 09:30:20', 101.5), (TIMESTAMP '2026-01-02 09:30:40', 99.5), (TIMESTAMP '2026-01-02 09:30:59', 100.5)) t(ts, price);]
fin_ohlcv macro OHLC plus total volume and VWAP of the group, open/close at the first and last ts. NULL [SELECT fin_ohlcv(price, volume, ts) AS bar FROM (VALUES (TIMESTAMP '2026-01-02 09:30:00', 100.0, 200.0), (TIMESTAMP '2026-01-02 09:30:20', 101.5, 100.0), (TIMESTAMP '2026-01-02 09:30:59', 100.5, 300.0)) t(ts, price, volume);]
fin_ols aggregate Multi-factor ordinary least squares with an intercept. NULL [SELECT fin_ols(y, [x1, x2]) AS fit FROM (VALUES (3.1, 1.0, 0.0), (4.9, 2.0, 0.5), (7.2, 3.0, 0.8), (8.8, 4.0, 1.5), (11.1, 5.0, 1.9)) t(y, x1, x2);]
fin_ols_no_intercept aggregate Multi-factor least squares through the origin. NULL [SELECT fin_ols_no_intercept(y, [x]) AS fit FROM (VALUES (2.1, 1.0), (3.9, 2.0), (6.2, 3.0)) t(y, x);]
fin_omega_ratio macro Omega ratio sum(max(r - L_p, 0)) / abs(sum(min(r - L_p, 0))). NULL [SELECT fin_omega_ratio(r, 0.0, 252) AS omega FROM (VALUES (0.02), (-0.01), (0.03), (-0.015)) t(r);]
fin_optimizer_spec macro Pack a portfolio-optimizer objective, risk-free rate and weight bounds into a validated spec struct. NULL [SELECT fin_optimizer_spec('min_variance', weight_max := 0.6) AS spec;]
fin_option_chain table Project option input rows and append model-prefixed BSM columns such as model_price, model_delta, and model_implied_volatility. NULL [WITH chain AS (SELECT * FROM (VALUES ('call', 100.0, 95.0, 0.5, 0.05, 0.2), ('put', 100.0, 105.0, 0.5, 0.05, 0.25)) t(kind, spot, strike, ttm, rate, vol)) SELECT * FROM fin_option_chain('chain', 'kind', 'spot', 'strike', 'ttm', 'rate', 'vol');]
fin_option_market_spec macro Build a European BSM option spec from expiry and valuation dates. NULL [SELECT fin_option_market_spec('call', 100.0, 100.0, DATE '2026-12-18', DATE '2026-01-02', 0.05, 0.2) AS spec;]
fin_option_payoff scalar Intrinsic payoff at expiry: max(spot - strike, 0) for a call, max(strike - spot, 0) for a put. NULL [SELECT fin_option_payoff('put', 95.0, 100.0) AS payoff;]
fin_option_spec macro Pack reusable option inputs into a struct accepted by BSM pricing and Greek functions. NULL [SELECT fin_bsm_price(fin_option_spec('call', 100.0, 100.0, 1.0, 0.05, 0.2, dividend_yield := 0.01)) AS price;]
fin_option_spec_dates macro Build an option spec from valuation and expiry dates using a day-count convention for time to expiry. NULL [SELECT fin_option_spec_dates('put', 100.0, 95.0, DATE '2026-01-02', DATE '2026-07-02', 0.05, 0.25) AS spec;]
fin_order_imbalance scalar Top-of-book order imbalance (bid_size - ask_size) / (bid_size + ask_size). NULL [SELECT fin_order_imbalance(500.0, 300.0) AS imbalance;]
fin_outlier_count aggregate Count observations whose absolute sample z-score exceeds the threshold. NULL [SELECT fin_outlier_count(x, 'zscore', 2.0) AS outliers FROM (VALUES (1.0), (1.1), (0.9), (1.0), (1.2), (0.8), (1.0), (9.0)) t(x);]
fin_parametric_cvar macro Parametric expected shortfall (positive loss) scaled by sqrt(horizon). NULL [SELECT fin_parametric_cvar(0.0005, 0.02, 0.99, 10.0, 'normal', NULL) AS cvar_10d;]
fin_parametric_var macro Parametric value at risk (positive loss) scaled by sqrt(horizon). NULL [SELECT fin_parametric_var(0.0005, 0.02, 0.99, 10.0, 't', 5.0) AS var_10d;]
fin_parkinson_vol macro Parkinson (1980) range volatility sqrt(annualization * mean(ln(H/L)^2) / (4 ln 2)). NULL [SELECT fin_parkinson_vol(high, low, 252) AS vol FROM (VALUES (102.0, 99.0), (103.0, 100.5), (103.0, 100.0)) t(high, low);]
fin_parse_compounding scalar Normalize and validate a compounding convention. NULL [SELECT fin_parse_compounding('Semi-Annually') AS compounding;]
fin_parse_day_count scalar Normalize and validate a day-count convention. NULL [SELECT fin_parse_day_count('actual/365 fixed') AS day_count;]
fin_parse_exercise_style scalar Normalize and validate an exercise style. NULL [SELECT fin_parse_exercise_style('American') AS exercise;]
fin_parse_option_kind scalar Canonicalize an option kind (call, put, c, p, any case) to 'call' or 'put'. NULL [SELECT fin_parse_option_kind('C') AS kind;]
fin_parse_return_method scalar Canonicalize a return method name to 'simple' or 'log'. NULL [SELECT fin_parse_return_method('LOG') AS method;]
fin_payoff_ratio macro Average win over average loss avg(r) FILTER (r > 0) / abs(avg(r) FILTER (r < 0)). NULL [SELECT fin_payoff_ratio(r) AS payoff_ratio FROM (VALUES (0.02), (-0.01), (0.03), (-0.015)) t(r);]
fin_pct_change macro Last / first - 1 over non-NULL values ordered by ts. NULL [SELECT fin_pct_change(x, ts) AS pct_change FROM (VALUES (1, 100.0), (2, 104.0), (3, 110.0)) t(ts, x);]
fin_plus_di aggregate Plus directional indicator 100 * S(+DM) / S(TR) with Wilder sums of +DM and the true range (TA-Lib PLUS_DI). NULL [SELECT fin_plus_di(high, low, close, 14 ORDER BY ts) AS plus_di FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low FROM range(60) t(i));]
fin_plus_dm aggregate Wilder-smoothed plus directional movement: +DM = high - previous high when positive and larger than previous low - low, else 0; the first period - 1 moves are summed, then S = S - S / period + DM (TA-Lib PLUS_DM). NULL [SELECT fin_plus_dm(high, low, 14 ORDER BY ts) AS plus_dm FROM (SELECT i AS ts, 101 + 3 * sin(i / 4) + i / 10 AS high, 99 + 3 * sin(i / 4) + i / 10 AS low FROM range(60) t(i));]
fin_portfolio_expected_return scalar Expected portfolio return w' mu: the dot product of weights and expected returns. NULL [SELECT fin_portfolio_expected_return([0.6, 0.4], [0.08, 0.12]) AS expected_return;]
fin_portfolio_optimize table Bounded mean-variance optimizer over fully invested weights. NULL [SELECT * FROM fin_portfolio_optimize([0.08, 0.12, 0.10], [[0.04, 0.006, 0.01], [0.006, 0.09, 0.02], [0.01, 0.02, 0.0625]], 'max_sharpe', 0.02, true, 0.0, 0.6);]
fin_portfolio_optimize_table table Optimize weights from a long return history: sample mean returns and the pairwise-complete sample covariance of each asset, then fin_portfolio_optimize. NULL [WITH returns AS (SELECT d, asset, 0.001 * asset + 0.01 * sin(d * asset) AS r FROM range(1, 4) a(asset), range(20) b(d)) SELECT * FROM fin_portfolio_optimize_table('returns', 'asset', 'd', 'r', 'min_variance');]
fin_portfolio_return scalar Realized portfolio return sum(w_i r_i) for one period from weight and return lists. NULL [SELECT fin_portfolio_return([0.6, 0.4], [0.010, -0.005]) AS portfolio_return;]
fin_portfolio_return_table table Compute portfolio return sum(weight * return) directly from table-shaped asset, weight, and return columns, optionally per date. NULL [WITH holdings AS (SELECT * FROM (VALUES (DATE '2026-01-02', 'AAA', 0.6, 0.010), (DATE '2026-01-02', 'BBB', 0.4, -0.005), (DATE '2026-01-05', 'AAA', 0.6, 0.002), (DATE '2026-01-05', 'BBB', 0.4, 0.004)) t(d, asset, weight, ret)) SELECT * FROM fin_portfolio_return_table('holdings', 'asset', 'weight', 'ret', 'd');]
fin_portfolio_sharpe scalar Portfolio Sharpe ratio (w'mu - risk_free) / sqrt(w'Sw). NULL [SELECT fin_portfolio_sharpe([0.6, 0.4], [0.08, 0.12], [[0.04, 0.006], [0.006, 0.09]], 0.02) AS sharpe;]
fin_portfolio_spec macro Pack asset labels, weights and a base currency into a portfolio spec struct; the lists must have equal length. NULL [SELECT fin_portfolio_spec(['AAA', 'BBB'], [0.6, 0.4], 'usd') AS spec;]
fin_portfolio_variance scalar Portfolio variance w' Sigma w. NULL [SELECT fin_portfolio_variance([0.6, 0.4], [[0.04, 0.006], [0.006, 0.09]]) AS variance;]
fin_portfolio_variance_table table Compute portfolio variance w'Cw and volatility from table-shaped weights and pairwise covariance rows. NULL [WITH weights AS (SELECT * FROM (VALUES ('AAA', 0.6), ('BBB', 0.4)) t(asset, weight)), cov AS (SELECT * FROM (VALUES ('AAA', 'AAA', 0.04), ('AAA', 'BBB', 0.006), ('BBB', 'BBB', 0.09)) t(asset_i, asset_j, covariance)) SELECT * FROM fin_portfolio_variance_table('weights', 'asset', 'weight', 'cov', 'asset_i', 'asset_j', 'covariance');]
fin_portfolio_vector macro Pair portfolio weights with asset labels. NULL [SELECT fin_portfolio_vector([0.6, 0.4], ['AAA', 'BBB']) AS portfolio;]
fin_portfolio_vol scalar Portfolio volatility sqrt(w' Sigma w). NULL [SELECT fin_portfolio_vol([0.6, 0.4], [[0.04, 0.006], [0.006, 0.09]]) AS volatility;]
fin_ppo aggregate Percentage price oscillator 100 * (MA(fast) - MA(slow)) / MA(slow) (TA-Lib PPO). NULL [SELECT fin_ppo(close, 12, 26, 'sma' ORDER BY ts) AS ppo FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_predict_linear_to_grid table Prometheus-style predict_linear: at each grid point g, least-squares fit value = a + b * t over observations with ts in (g - lookback, g] and predict at g + horizon. NULL [WITH ticks AS (SELECT TIMESTAMP '2026-01-02 09:30:00' + i * INTERVAL '20 seconds' AS ts, 100 + (i % 5) * 0.1 AS price, 100.0 + 50 * (i % 3) AS volume FROM range(12) t(i)) SELECT * FROM fin_predict_linear_to_grid('ticks', 'ts', 'price', TIMESTAMP '2026-01-02 09:30:00', TIMESTAMP '2026-01-02 09:34:00', INTERVAL '1 minute', INTERVAL '2 minutes', INTERVAL '1 minute');]
fin_present_value scalar Present value future_value * DF(rate, t) of an amount due in t years. NULL [SELECT fin_present_value(110.25, 0.05, 2.0, 'annual') AS present_value;]
fin_prev_business_day scalar Move backward by business days. NULL [SELECT fin_prev_business_day(DATE '2026-01-05', 'NYSE', 2) AS previous_day;]
fin_price_from_return macro Apply a simple or log return to a price. NULL [SELECT fin_price_from_return(100.0, 0.05, 'simple') AS price;]
fin_profit_factor macro Gross profit over gross loss sum(r) FILTER (r > 0) / abs(sum(r) FILTER (r < 0)). NULL [SELECT fin_profit_factor(r) AS profit_factor FROM (VALUES (0.02), (-0.01), (0.03), (-0.015)) t(r);]
fin_put_call_parity scalar Put-call parity residual C - P - S exp(-qT) + K exp(-rT); zero when European call and put prices are consistent. NULL [SELECT fin_put_call_parity(10.4506, 5.5735, 100.0, 100.0, 1.0, 0.05, 0.0) AS parity_gap;]
fin_quantile_spread aggregate Compute the mean forward-return spread between the top and bottom factor buckets. NULL [SELECT fin_quantile_spread(factor, forward_return, 2) AS top_minus_bottom FROM (VALUES (1.0, 0.00), (2.0, 0.01), (3.0, 0.02), (4.0, 0.03)) t(factor, forward_return);]
fin_queue_imbalance scalar Queue imbalance (bid_size - ask_size) / (bid_size + ask_size) (Gould and Bonart 2016); same formula as fin_order_imbalance. NULL [SELECT fin_queue_imbalance(500.0, 300.0) AS imbalance;]
fin_rank_corr macro Rank correlation of paired observations. NULL [SELECT fin_rank_corr(x, y, 'kendall') AS kendall_tau FROM (VALUES (1.0, 2.0), (2.0, 1.0), (3.0, 4.0), (4.0, 3.0), (5.0, 5.0)) t(x, y);]
fin_rank_ic macro Rank information coefficient (Spearman). NULL [SELECT fin_rank_ic(factor, forward_return) AS rank_ic FROM (VALUES (1.0, 0.01), (2.0, 0.00), (3.0, 0.02), (4.0, 0.03), (5.0, 0.025)) t(factor, forward_return);]
fin_rate macro Average rate of change: (last - first) / elapsed time between the first and last non-NULL observations ordered by ts, per unit. NULL [SELECT fin_rate(x, ts, 'minute') AS per_minute FROM (VALUES (TIMESTAMP '2026-01-02 09:30:00', 100.0), (TIMESTAMP '2026-01-02 09:35:00', 160.0)) t(ts, x);]
fin_rate_from_discount scalar Zero rate implied by a discount factor over t years; inverse of fin_discount_factor. NULL [SELECT fin_rate_from_discount(0.9512, 1.0, 'continuous') AS zero_rate;]
fin_rate_spec macro Pack a rate with canonical compounding, integer frequency and day-count names into a spec struct. NULL [SELECT fin_rate_spec(0.05, 'semiannual', 2, 'ACT/360') AS spec;]
fin_rate_to_grid table Per-second rate of change between consecutive grid samples: (value - previous) / seconds between grid points. NULL [WITH ticks AS (SELECT TIMESTAMP '2026-01-02 09:30:00' + i * INTERVAL '20 seconds' AS ts, 100 + (i % 5) * 0.1 AS price, 100.0 + 50 * (i % 3) AS volume FROM range(12) t(i)) SELECT * FROM fin_rate_to_grid('ticks', 'ts', 'price', TIMESTAMP '2026-01-02 09:30:00', TIMESTAMP '2026-01-02 09:34:00', INTERVAL '1 minute');]
fin_realized_beta macro Realized beta; alias of fin_beta. NULL [SELECT fin_realized_beta(r, benchmark_r) AS beta FROM (VALUES (0.010, 0.008), (-0.020, -0.015), (0.015, 0.010), (0.004, 0.002)) t(r, benchmark_r);]
fin_realized_corr macro Sample correlation via DuckDB corr. NULL [SELECT fin_realized_corr(r1, r2) AS corr FROM (VALUES (0.010, 0.008), (-0.020, -0.015), (0.015, 0.010), (0.004, 0.002)) t(r1, r2);]
fin_realized_cov macro Sample covariance via DuckDB covar_samp. NULL [SELECT fin_realized_cov(r1, r2) AS cov FROM (VALUES (0.010, 0.008), (-0.020, -0.015), (0.015, 0.010), (0.004, 0.002)) t(r1, r2);]
fin_realized_quarticity macro Realized quarticity n / 3 * sum(r^4) * annualization. NULL [SELECT fin_realized_quarticity(log_r, 252) AS quarticity FROM (VALUES (0.001), (-0.002), (0.0015), (-0.0005)) t(log_r);]
fin_realized_variance macro Annualized realized variance mean(r^2) * annualization (no mean adjustment). NULL [SELECT fin_realized_variance(log_r, 252) AS realized_variance FROM (VALUES (0.001), (-0.002), (0.0015), (-0.0005)) t(log_r);]
fin_realized_vol macro Square root of fin_realized_variance. NULL [SELECT fin_realized_vol(log_r, 252) AS realized_vol FROM (VALUES (0.001), (-0.002), (0.0015), (-0.0005)) t(log_r);]
fin_rebalance_trades table Trades that move current weights to target weights at a portfolio value (default 1.0). NULL [WITH current_w AS (SELECT * FROM (VALUES ('AAA', 0.7), ('BBB', 0.3)) t(asset, weight)), target_w AS (SELECT * FROM (VALUES ('AAA', 0.5), ('BBB', 0.5)) t(asset, weight)), prices AS (SELECT * FROM (VALUES ('AAA', 50.0), ('BBB', 20.0)) t(asset, price)) SELECT * FROM fin_rebalance_trades('current_w', 'target_w', 'prices', 100000.0);]
fin_recovery_factor macro Recovery factor fin_total_return(r) / abs(fin_max_drawdown(r ORDER BY ts)). NULL [SELECT fin_recovery_factor(r, ts) AS recovery_factor FROM (VALUES (1, 0.05), (2, -0.10), (3, 0.02), (4, -0.03), (5, 0.08)) t(ts, r);]
fin_resample_grid table Resample values onto a regular timestamp grid using the last observation at or before each grid timestamp. NULL [WITH ticks AS (SELECT TIMESTAMP '2026-01-02 09:30:00' + i * INTERVAL '20 seconds' AS ts, 100 + (i % 5) * 0.1 AS price, 100.0 + 50 * (i % 3) AS volume FROM range(12) t(i)) SELECT * FROM fin_resample_grid('ticks', 'ts', 'price', TIMESTAMP '2026-01-02 09:30:00', TIMESTAMP '2026-01-02 09:34:00', INTERVAL '1 minute', 'last', INTERVAL '30 seconds');]
fin_resets macro Count how many times a non-NULL value is lower than its predecessor when ordered by ts (Prometheus resets). NULL [SELECT fin_resets(x, ts) AS resets FROM (VALUES (1, 5.0), (2, 9.0), (3, 2.0), (4, 4.0), (5, 1.0)) t(ts, x);]
fin_resets_to_grid table Flag whether each grid sample is lower than the previous grid sample (1.0 reset, 0.0 otherwise). NULL [WITH ticks AS (SELECT TIMESTAMP '2026-01-02 09:30:00' + i * INTERVAL '20 seconds' AS ts, 100 + (i % 5) * 0.1 AS price, 100.0 + 50 * (i % 3) AS volume FROM range(12) t(i)) SELECT * FROM fin_resets_to_grid('ticks', 'ts', 'price', TIMESTAMP '2026-01-02 09:30:00', TIMESTAMP '2026-01-02 09:34:00', INTERVAL '1 minute');]
fin_return macro Simple or log return between two prices. NULL [SELECT fin_return(105.0, 100.0, 'log') AS log_return;]
fin_risk_contribution macro Each asset's share of portfolio volatility: fin_component_risk normalized to sum to one. NULL [SELECT fin_risk_contribution([0.7, 0.3], [[0.04, 0.006], [0.006, 0.09]]) AS risk_shares;]
fin_risk_parity_weights macro Long-only risk-budgeting (equal risk contribution) weights. NULL [SELECT fin_risk_parity_weights([[0.04, 0.006], [0.006, 0.09]]) AS weights;]
fin_risk_spec macro Pack annualization, risk-free rate, VaR confidence and tail conventions into a validated spec struct. NULL [SELECT fin_risk_spec(252, 0.02, 0.99) AS spec;]
fin_roc aggregate Rate of change in percent 100 * (x_t / x_(t-period) - 1) (TA-Lib ROC). NULL [SELECT fin_roc(close, 10 ORDER BY ts) AS roc_percent FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_rocp aggregate Rate of change as a fraction (x_t - x_(t-period)) / x_(t-period) (TA-Lib ROCP). NULL [SELECT fin_rocp(close, 10 ORDER BY ts) AS roc_fraction FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_rocr aggregate Rate of change ratio x_t / x_(t-period) (TA-Lib ROCR). NULL [SELECT fin_rocr(close, 10 ORDER BY ts) AS roc_ratio FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_rocr100 aggregate Rate of change ratio in percent 100 * x_t / x_(t-period) (TA-Lib ROCR100). NULL [SELECT fin_rocr100(close, 10 ORDER BY ts) AS roc_ratio_percent FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_rogers_satchell_vol macro Rogers-Satchell (1991) volatility sqrt(annualization * mean(ln(H/C) ln(H/O) + ln(L/C) ln(L/O))). NULL [SELECT fin_rogers_satchell_vol(open, high, low, close, 252) AS vol FROM (VALUES (100.0, 102.0, 99.0, 101.0), (101.0, 103.0, 100.5, 102.5), (102.5, 103.0, 100.0, 100.5)) t(open, high, low, close);]
fin_roll_spread aggregate Roll (1984) effective spread 2 * sqrt(-cov(dP_t, dP_{t-1})) from the sample (n - 1) covariance of consecutive price changes. NULL [SELECT fin_roll_spread(price ORDER BY ts) AS roll_spread FROM (VALUES (1, 100.00), (2, 100.02), (3, 100.00), (4, 100.03), (5, 100.01), (6, 100.03), (7, 100.00)) t(ts, price);]
fin_rolling_beta aggregate OLS beta cov(r, f) / var(f) usable as a rolling window function. NULL [SELECT ts, fin_rolling_beta(r, factor_r) OVER (ORDER BY ts ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS beta_3 FROM (VALUES (1, 0.010, 0.008), (2, -0.020, -0.015), (3, 0.015, 0.010), (4, 0.004, 0.002)) t(ts, r, factor_r) ORDER BY ts;]
fin_rolling_zscore aggregate Z-score of the latest observation (by ts) within the group or window frame. NULL [SELECT ts, fin_rolling_zscore(x, ts) OVER (ORDER BY ts ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS zscore_4 FROM (VALUES (1, 10.0), (2, 11.0), (3, 9.5), (4, 12.0), (5, 10.5)) t(ts, x) ORDER BY ts;]
fin_round_to_tick scalar Round a price to a tick grid. NULL [SELECT fin_round_to_tick(100.037, 0.05, 'nearest') AS price;]
fin_rsi aggregate Wilder relative strength index 100 * avg_gain / (avg_gain + avg_loss); the averages are seeded with the mean of the first period changes, then updated as (avg * (period - 1) + change) / period (TA-Lib RSI). NULL [SELECT fin_rsi(close, 14 ORDER BY ts) AS rsi FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_sabr_vol scalar Compute Hagan's lognormal SABR implied-volatility approximation. NULL [SELECT fin_sabr_vol(100.0, 110.0, 1.0, 2.0, 0.5, -0.2, 0.4) AS implied_vol;]
fin_safe_div scalar Divide numerator / denominator, returning fallback (default NULL) when the denominator is zero. NULL [SELECT fin_safe_div(1.0, 0.0, 0.0) AS ratio;]
fin_sar aggregate Wilder parabolic SAR with TA-Lib's algorithm: the first two bars' directional movement sets the initial trend; the acceleration factor starts at acceleration, grows by acceleration at each new extreme and is capped at maximum (TA-Lib SAR). NULL [SELECT fin_sar(high, low, 0.02, 0.2 ORDER BY ts) AS sar FROM (SELECT i AS ts, 101 + 3 * sin(i / 4) + i / 10 AS high, 99 + 3 * sin(i / 4) + i / 10 AS low FROM range(60) t(i));]
fin_sarext aggregate Extended parabolic SAR (TA-Lib SAREXT) with positional start_value (0 = automatic direction, > 0 start long at that SAR, < 0 start short at its absolute value), offset_on_reverse, af_init_long, af_long, af_max_long, af_init_short, af_short, af_max_short. NULL [SELECT fin_sarext(high, low, 0, 0, 0.02, 0.02, 0.2, 0.02, 0.02, 0.2 ORDER BY ts) AS sar FROM (SELECT i AS ts, 101 + 3 * sin(i / 4) + i / 10 AS high, 99 + 3 * sin(i / 4) + i / 10 AS low FROM range(60) t(i));]
fin_schema_template table Return the expected columns for a named finance schema template (ohlcv, returns, option_chain, rates_curve, portfolio). NULL [SELECT * FROM fin_schema_template('ohlcv');]
fin_semivariance macro Per-period downside semivariance mean(min(r - T_p, 0)^2). NULL [SELECT fin_semivariance(r, 0.0, 252) AS semivariance FROM (VALUES (0.01), (-0.02), (0.015), (-0.005)) t(r);]
fin_session_date scalar Return the exchange-local calendar date of a timestamp. NULL [SELECT fin_session_date(TIMESTAMP '2026-05-06 23:30:00', 'NYSE', 'UTC') AS session_date;]
fin_sharpe aggregate Annualized Sharpe ratio mean(e) / stddev_samp(e) * sqrt(annualization) with e = r - rf_p. NULL [SELECT fin_sharpe(r, 0.02, 252) AS sharpe FROM (VALUES (0.010), (-0.004), (0.006), (0.002), (0.008)) t(r);]
fin_simple_return macro Simple return price / prev_price - 1 between two prices. NULL [SELECT fin_simple_return(105.0, 100.0) AS simple_return;]
fin_sma aggregate Simple moving average: mean of the last period values (TA-Lib SMA). NULL [SELECT fin_sma(close, 20 ORDER BY ts) AS sma FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_sortino aggregate Annualized Sortino ratio: mean excess return over the per-period MAR divided by downside deviation, times sqrt(annualization). NULL [SELECT fin_sortino(r, 0.0, 252) AS sortino FROM (VALUES (0.010), (-0.004), (0.006), (-0.002), (0.008)) t(r);]
fin_spread scalar Quoted spread ask - bid. NULL [SELECT fin_spread(99.98, 100.02) AS spread;]
fin_spread_bps scalar Quoted spread in basis points of the midpoint (ask - bid) / ((bid + ask) / 2) * 10000. NULL [SELECT fin_spread_bps(99.98, 100.02) AS spread_bps;]
fin_stability aggregate Stability of returns (empyrical stability_of_timeseries): R^2 of an OLS fit of cumulative log returns on the observation index. NULL [SELECT fin_stability(r ORDER BY ts) AS stability FROM (VALUES (1, 0.010), (2, 0.004), (3, 0.006), (4, -0.002), (5, 0.008)) t(ts, r);]
fin_stable_corr macro Sample correlation via DuckDB corr. NULL [SELECT fin_stable_corr(y, x) AS corr FROM (VALUES (1.0, 1.0), (2.1, 2.0), (2.9, 3.0), (4.2, 4.0)) t(y, x);]
fin_stable_cov macro Sample covariance via DuckDB covar_samp. NULL [SELECT fin_stable_cov(y, x) AS cov FROM (VALUES (1.0, 1.0), (2.1, 2.0), (2.9, 3.0), (4.2, 4.0)) t(y, x);]
fin_stable_mean macro Mean with a compensated (fsum) numerator. NULL [SELECT fin_stable_mean(x) AS mean FROM (VALUES (1e9 + 1.0), (1e9 + 2.0), (1e9 + 3.0)) t(x);]
fin_stable_stddev macro Square root of fin_stable_var. NULL [SELECT fin_stable_stddev(x, 1) AS stddev FROM (VALUES (1.0), (2.0), (4.0)) t(x);]
fin_stable_var macro Variance with denominator count(x) - ddof. NULL [SELECT fin_stable_var(x, 0) AS population_variance FROM (VALUES (1.0), (2.0), (4.0)) t(x);]
fin_stddev aggregate Rolling standard deviation of the last period values, population by default (TA-Lib STDDEV with nbdev 1). NULL [SELECT fin_stddev(close, 20, 0 ORDER BY ts) AS stddev FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_stoch aggregate Slow stochastic oscillator: fastK = 100 * (close - LL) / (HH - LL) over the last k rows, k = SMA(fastK, smooth) and d = SMA(k, d) (TA-Lib STOCH with SMA smoothing; fastk_period = k, slowk_period = smooth, slowd_period = d). NULL [SELECT fin_stoch(high, low, close, 14, 3, 3 ORDER BY ts) AS stoch FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low FROM range(60) t(i));]
fin_stochrsi aggregate Stochastic RSI: k = 100 * (RSI - min RSI) / (max RSI - min RSI) over the last k RSI(period) values and d = SMA(k, d) (TA-Lib STOCHRSI with fastk_period = k, fastd_period = d). NULL [SELECT fin_stochrsi(close, 14, 3, 3 ORDER BY ts) AS stochrsi FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_student_t_cdf scalar Evaluate the Student-t CDF. NULL [SELECT fin_student_t_cdf(2.228139, 10.0) AS p;]
fin_student_t_inv scalar Invert the Student-t CDF. NULL [SELECT fin_student_t_inv(0.975, 10.0) AS t;]
fin_svi_total_variance scalar Raw SVI total implied variance w(k) = a + b (rho (k - m) + sqrt((k - m)^2 + sigma^2)) at log-moneyness k (Gatheral 2004). NULL [SELECT fin_svi_total_variance(0.1, 0.02, 0.1, -0.3, 0.0, 0.2) AS total_variance;]
fin_svi_vol scalar Implied volatility sqrt(w(k) / ttm) from the raw SVI total-variance slice. NULL [SELECT fin_svi_vol(0.1, 0.5, 0.02, 0.1, -0.3, 0.0, 0.2) AS implied_vol;]
fin_swap_rate scalar Par swap rate from annual payment times and discount factors. NULL [SELECT fin_swap_rate([1.0, 2.0, 3.0], [0.96, 0.92, 0.88]) AS par_rate;]
fin_t3 aggregate Tillson T3: six chained SMA-seeded EMAs combined as c1 e6 + c2 e5 + c3 e4 + c4 e3 with volume-factor coefficients (TA-Lib T3). NULL [SELECT fin_t3(close, 5, 0.7 ORDER BY ts) AS t3 FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_tail_ratio macro Tail ratio quantile(r, upper_q) / abs(quantile(r, lower_q)) (linear interpolation). NULL [SELECT fin_tail_ratio(r, 0.9, 0.1) AS tail_ratio FROM (VALUES (0.01), (-0.04), (0.02), (-0.01), (0.03), (-0.02), (0.005), (0.015)) t(r);]
fin_tema aggregate Triple exponential moving average 3 * EMA1 - 3 * EMA2 + EMA3 of chained, SMA-seeded EMA stages (TA-Lib TEMA). NULL [SELECT fin_tema(close, 10 ORDER BY ts) AS tema FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_theils_u macro Theil's uncertainty coefficient U(x given y). NULL [SELECT fin_theils_u(x, y) AS theils_u FROM (VALUES ('a', 'u'), ('a', 'u'), ('b', 'v'), ('b', 'v'), ('a', 'v'), ('b', 'u')) t(x, y);]
fin_tick_bars table Aggregate trades into tick bars of ceil(threshold) rows (default threshold 100). NULL [WITH ticks AS (SELECT TIMESTAMP '2026-01-02 09:30:00' + i * INTERVAL '20 seconds' AS ts, 100 + (i % 5) * 0.1 AS price, 100.0 + 50 * (i % 3) AS volume FROM range(12) t(i)) SELECT * FROM fin_tick_bars('ticks', 'ts', 'price', 4, volume_col := 'volume');]
fin_to_log_return macro Convert a simple return to a log return log1p(r). NULL [SELECT fin_to_log_return(0.05) AS log_return;]
fin_total_return aggregate Compound decimal simple or log returns in log space: expm1(sum(log1p(r))) or expm1(sum(r)). NULL [SELECT fin_total_return(r, 'simple') AS total_return FROM (VALUES (0.01), (0.02), (-0.01)) t(r);]
fin_tracking_error macro Annualized tracking error stddev_samp(r - b) * sqrt(annualization). NULL [SELECT fin_tracking_error(r, benchmark_r, 252) AS tracking_error FROM (VALUES (0.010, 0.008), (-0.020, -0.015), (0.015, 0.010), (0.004, 0.002)) t(r, benchmark_r);]
fin_trade_sign scalar Trade initiator sign: +1 buyer-initiated, -1 seller-initiated, 0 unclassified. Method 'lee_ready' (default, Lee and Ready 1991): above the quote midpoint +1, below -1, at the midpoint the tick test against prev_price. Method 'emo' (Ellis, Michaely and O'Hara 2000): at or above the ask +1, at or below the bid -1, otherwise the tick test. NULL [SELECT fin_trade_sign(100.03, 99.98, 100.02, 100.01, 'lee_ready') AS sign;]
fin_treynor_ratio macro Treynor ratio avg(r - rf_p) * annualization / beta over complete pairs. NULL [SELECT fin_treynor_ratio(r, benchmark_r, 0.02, 252) AS treynor FROM (VALUES (0.010, 0.008), (-0.020, -0.015), (0.015, 0.010), (0.004, 0.002)) t(r, benchmark_r);]
fin_trima aggregate Triangular moving average: triangular weights 1, 2, ..., 2, 1 over the last period values, computed as an SMA of an SMA (TA-Lib TRIMA). NULL [SELECT fin_trima(close, 20 ORDER BY ts) AS trima FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_trimmed_mean aggregate Average observations between the inclusive lower and upper continuous quantiles. NULL [SELECT fin_trimmed_mean(x, 0.1, 0.9) AS trimmed_mean FROM (VALUES (1.0), (2.0), (3.0), (4.0), (100.0)) t(x);]
fin_trix aggregate 1-row rate of change in percent of a triple-smoothed, SMA-seeded EMA (TA-Lib TRIX). NULL [SELECT fin_trix(close, 10 ORDER BY ts) AS trix FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_true_range aggregate True range of the last row, max(high - low, abs(high - previous close), abs(low - previous close)) (TA-Lib TRANGE). NULL [SELECT fin_true_range(high, low, close ORDER BY ts) AS true_range FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low FROM range(60) t(i));]
fin_ts_grid_spec macro Pack a time-grid start, end, positive step, staleness and sampling method into a validated spec struct. NULL [SELECT fin_ts_grid_spec(TIMESTAMP '2026-01-02 09:30:00', TIMESTAMP '2026-01-02 16:00:00', INTERVAL '5 minutes') AS spec;]
fin_tsf aggregate Time series forecast: the least-squares line over the last period values extrapolated one row ahead, intercept + slope * period (TA-Lib TSF). NULL [SELECT fin_tsf(close, 14 ORDER BY ts) AS forecast FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_ttest_1samp macro One-sample Student t test of the mean against mu. NULL [SELECT fin_ttest_1samp(x, 0.0) AS ttest FROM (VALUES (0.5), (1.2), (0.8), (1.5), (0.9)) t(x);]
fin_ttest_2samp macro Two-sample t test of equal means. NULL [SELECT fin_ttest_2samp(x, y, true) AS ttest FROM (VALUES (1.0, 2.0), (2.0, 3.0), (3.0, 4.5), (2.5, 3.5)) t(x, y);]
fin_turnover scalar One-way portfolio turnover sum(abs(new_i - old_i)) / 2 between two weight vectors. NULL [SELECT fin_turnover([0.5, 0.3, 0.2], [0.4, 0.4, 0.2]) AS turnover;]
fin_twap aggregate Time-weighted average price: each price holds from its timestamp until the next distinct timestamp, sum(price_k * (t_{k+1} - t_k)) / (t_last - t_first); the last timestamp carries no weight. NULL [SELECT fin_twap(price, ts ORDER BY ts) AS twap FROM (VALUES (TIMESTAMP '2026-01-02 09:30:00', 100.0), (TIMESTAMP '2026-01-02 09:30:40', 101.0), (TIMESTAMP '2026-01-02 09:31:00', 100.5)) t(ts, price);]
fin_typ_price scalar Typical price (high + low + close) / 3 (TA-Lib TYPPRICE). NULL [SELECT fin_typ_price(102.0, 99.0, 101.0) AS typical_price;]
fin_typeof macro Return the DuckDB logical type name of the argument (DuckDB typeof). NULL [SELECT fin_typeof(1.5) AS type_name;]
fin_ulcer_index aggregate Ulcer index sqrt(mean(D_t^2)) of the fractional drawdown series. NULL [SELECT fin_ulcer_index(r ORDER BY ts) AS ulcer_index FROM (VALUES (1, 0.05), (2, -0.10), (3, 0.02), (4, -0.03), (5, 0.08)) t(ts, r);]
fin_ultosc aggregate Ultimate oscillator 100 * (4 A1 + 2 A2 + A3) / 7 with Ak = sum(BP) / sum(TR) over the three periods sorted ascending, buying pressure BP = close - min(low, previous close) (TA-Lib ULTOSC). NULL [SELECT fin_ultosc(high, low, close, 7, 14, 28 ORDER BY ts) AS ultosc FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low FROM range(60) t(i));]
fin_up_capture macro Up-market capture avg(r) FILTER (b > 0) / avg(b) FILTER (b > 0) over complete pairs. NULL [SELECT fin_up_capture(r, benchmark_r) AS up_capture FROM (VALUES (0.010, 0.008), (-0.020, -0.015), (0.015, 0.010), (0.004, 0.002)) t(r, benchmark_r);]
fin_upside_deviation macro Annualized upside deviation sqrt(mean(max(r - T_p, 0)^2)) * sqrt(annualization). NULL [SELECT fin_upside_deviation(r, 0.0, 252) AS upside_deviation FROM (VALUES (0.01), (-0.02), (0.015), (-0.005)) t(r);]
fin_validate_curve_spec macro Validate the numerical inputs used by curve interpolation. NULL [SELECT fin_validate_curve_spec(fin_curve_spec([1.0, 0.5], [0.04, 0.05])) AS check;]
fin_validate_ohlc scalar Validate an OHLC bar. NULL [SELECT fin_validate_ohlc(100.0, 101.0, 99.0, 100.5) AS valid_bar, fin_validate_ohlc(100.0, 99.0, 101.0, 100.5) AS invalid_bar;]
fin_validate_option_spec macro Validate an option spec before pricing. NULL [SELECT fin_validate_option_spec(fin_option_spec('call', 100.0, 100.0, 0.0, 0.05, 0.2)) AS check;]
fin_validate_rate_spec macro Validate a rate spec and report the first failed rule. NULL [SELECT fin_validate_rate_spec(fin_rate_spec(0.05)) AS check;]
fin_validate_return scalar Check that a decimal return is plausible. NULL [SELECT fin_validate_return(0.05) AS ok, fin_validate_return(-1.5) AS below_total_loss;]
fin_validate_schema table Validate a table's columns against a schema template. NULL [WITH returns AS (SELECT DATE '2026-01-02' AS date, 'AAA' AS asset_id, 0.01 AS return_decimal) SELECT * FROM fin_validate_schema('returns', 'returns');]
fin_var aggregate Value at risk at the given confidence. NULL [SELECT fin_var(r, 0.9, 'historical', true) AS var FROM (VALUES (0.01), (-0.04), (0.02), (-0.01), (0.03), (-0.02), (0.005), (0.015), (-0.03), (0.01)) t(r);]
fin_var_indicator aggregate Rolling variance of the last period values, population by default (TA-Lib VAR). NULL [SELECT fin_var_indicator(close, 20, 0 ORDER BY ts) AS variance FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_var_spec macro Pack a VaR confidence in (0, 1), method (historical, parametric, cornish_fisher) and tail convention into a spec struct. NULL [SELECT fin_var_spec(0.99, 'parametric') AS spec;]
fin_vector_add scalar Element-wise sum of two equal-length lists. NULL [SELECT fin_vector_add([1.0, 2.0], [3.0, 4.0]) AS total;]
fin_vector_mean scalar Arithmetic mean of a list's elements. NULL [SELECT fin_vector_mean([1.0, 2.0, 6.0]) AS mean;]
fin_vector_normalize_sum scalar Scale a list so that its elements sum to one: x_i / sum(x). NULL [SELECT fin_vector_normalize_sum([2.0, 1.0, 1.0]) AS weights;]
fin_vector_scale scalar Multiply every element of a list by a scalar. NULL [SELECT fin_vector_scale([1.0, 2.0], 0.5) AS scaled;]
fin_vector_sub scalar Element-wise difference a - b of two equal-length lists. NULL [SELECT fin_vector_sub([3.0, 4.0], [1.0, 2.0]) AS difference;]
fin_vector_sum scalar Sum of a list's elements. NULL [SELECT fin_vector_sum([1.0, 2.0, 3.0]) AS total;]
fin_version scalar Return the loaded finance extension version string. NULL [SELECT fin_version() AS version;]
fin_vol_of_vol macro Annualized dispersion of a volatility series stddev_samp(vol) * sqrt(annualization). NULL [SELECT fin_vol_of_vol(vol, 252) AS vol_of_vol FROM (VALUES (0.20), (0.22), (0.19), (0.25)) t(vol);]
fin_volatility aggregate Annualized volatility sqrt(sum((r - mean)^2) / (n - ddof)) * sqrt(annualization). NULL [SELECT fin_volatility(r, 252, 1) AS volatility FROM (VALUES (0.010), (-0.004), (0.006), (0.002), (0.008)) t(r);]
fin_volume_bars table Aggregate trades into volume bars that close once accumulated volume reaches the threshold. NULL [WITH ticks AS (SELECT TIMESTAMP '2026-01-02 09:30:00' + i * INTERVAL '20 seconds' AS ts, 100 + (i % 5) * 0.1 AS price, 100.0 + 50 * (i % 3) AS volume FROM range(12) t(i)) SELECT * FROM fin_volume_bars('ticks', 'ts', 'price', 'volume', 500.0);]
fin_volume_profile aggregate Volume per equal-width price bin over [min(price), max(price)]; bin i covers [min + i w, min + (i + 1) w) with w = (max - min) / bins, the last bin closed. NULL [SELECT fin_volume_profile(price, volume, 3) AS profile FROM (VALUES (100.0, 200.0), (100.5, 100.0), (101.0, 300.0), (101.5, 50.0), (102.0, 150.0)) t(price, volume);]
fin_vpin aggregate Volume-synchronized probability of informed trading (Easley, Lopez de Prado and O'Hara 2012): rows fill equal-volume buckets of bucket_volume (a row crossing a boundary is split pro rata), and VPIN = sum(abs(buy - sell)) / (buckets * bucket_volume) over the last buckets completed buckets, with buy - sell = the bucket's signed volume. NULL [SELECT fin_vpin(signed_volume, volume, 5, 100.0 ORDER BY ts) AS vpin FROM (SELECT i AS ts, 50.0 AS volume, CASE WHEN i % 3 = 0 THEN -30.0 ELSE 20.0 END AS signed_volume FROM range(20) t(i));]
fin_vwap aggregate Volume-weighted average price sum(price * volume) / sum(volume). NULL [SELECT fin_vwap(price, volume) AS vwap FROM (VALUES (100.0, 200.0), (101.0, 100.0), (100.5, 300.0)) t(price, volume);]
fin_weighted_close scalar Weighted close (high + low + 2 * close) / 4 (TA-Lib WCLPRICE). NULL [SELECT fin_weighted_close(102.0, 99.0, 101.0) AS weighted_close;]
fin_weighted_mean macro Compute the mean over value/weight pairs. NULL [SELECT fin_weighted_mean(x, w) AS weighted_mean FROM (VALUES (1.0, 1.0), (2.0, 2.0), (3.0, 1.0)) t(x, w);]
fin_weighted_quantile aggregate Compute a quantile from the weighted empirical distribution. NULL [SELECT fin_weighted_quantile(x, w, 0.5, 'linear') AS weighted_median FROM (VALUES (1.0, 1.0), (2.0, 2.0), (3.0, 1.0)) t(x, w);]
fin_weighted_stddev macro Square root of fin_weighted_var. NULL [SELECT fin_weighted_stddev(x, w, 0) AS weighted_stddev FROM (VALUES (1.0, 1.0), (2.0, 2.0), (3.0, 1.0)) t(x, w);]
fin_weighted_var macro Compute weighted variance with denominator sum(w) - ddof. NULL [SELECT fin_weighted_var(x, w, 0) AS weighted_variance FROM (VALUES (1.0, 1.0), (2.0, 2.0), (3.0, 1.0)) t(x, w);]
fin_welch_ttest macro Welch's unequal-variance two-sample t test. NULL [SELECT fin_welch_ttest(x, y) AS welch FROM (VALUES (1.0, 2.0), (2.0, 3.0), (3.0, 6.5), (2.5, 3.5)) t(x, y);]
fin_willr aggregate Williams %R -100 * (HH - close) / (HH - LL) over the last period rows (TA-Lib WILLR). NULL [SELECT fin_willr(high, low, close, 14 ORDER BY ts) AS williams_r FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close, close + 1.5 AS high, close - 0.5 AS low FROM range(60) t(i));]
fin_win_rate macro Fraction of non-NULL observations above zero. NULL [SELECT fin_win_rate(r) AS win_rate FROM (VALUES (0.02), (-0.01), (0.03), (-0.015)) t(r);]
fin_winsorized_mean aggregate Clamp observations to the lower and upper continuous quantiles, then average them. NULL [SELECT fin_winsorized_mean(x, 0.1, 0.9) AS winsorized_mean FROM (VALUES (1.0), (2.0), (3.0), (4.0), (100.0)) t(x);]
fin_wma aggregate Linearly weighted moving average of the last period values, weights 1..period with the newest row heaviest (TA-Lib WMA). NULL [SELECT fin_wma(close, 20 ORDER BY ts) AS wma FROM (SELECT i AS ts, 100 + 3 * sin(i / 4) + i / 10 AS close FROM range(60) t(i));]
fin_xirr scalar Internal rate of return of dated cash flows: the annual rate where sum(cf_i / (1 + r)^((d_i - d_0) / 365)) = 0. NULL [SELECT fin_xirr([-100.0, 50.0, 60.0], [DATE '2026-01-01', DATE '2026-07-01', DATE '2027-01-01']) AS xirr;]
fin_yang_zhang_vol aggregate Yang-Zhang (2000) volatility: sqrt(annualization * (var(o) + k var(c) + (1 - k) mean(rs))) over periods 2..N, with overnight o = ln(O_t / C_{t-1}), open-close c = ln(C_t / O_t), Rogers-Satchell rs, sample variances and k = 0.34 / (1.34 + (n + 1) / (n - 1)). NULL [SELECT fin_yang_zhang_vol(open, high, low, close, 252 ORDER BY ts) AS vol FROM (VALUES (1, 100.0, 102.0, 99.0, 101.0), (2, 101.0, 103.0, 100.5, 102.5), (3, 102.5, 103.0, 100.0, 100.5), (4, 100.0, 101.5, 99.0, 101.0)) t(ts, open, high, low, close);]
fin_yearfrac scalar Compute a day-count year fraction between two dates. NULL [SELECT fin_yearfrac(DATE '2026-01-15', DATE '2026-07-15', '30/360') AS year_fraction;]
fin_zscore_last macro Z-score of the latest observation by ts: (x_last - avg(x)) / stddev_samp(x). NULL [SELECT fin_zscore_last(x, ts) AS zscore FROM (VALUES (1, 10.0), (2, 11.0), (3, 9.5), (4, 12.0)) t(ts, x);]
fin_ztest_mean macro One-sample z test of the mean. NULL [SELECT fin_ztest_mean(x, 0.0, 1.0) AS ztest FROM (VALUES (0.5), (1.2), (0.8), (1.5), (0.9)) t(x);]
finance_adf_internal aggregate NULL NULL  
finance_annualization_internal macro NULL NULL  
finance_anova_oneway_internal aggregate NULL NULL  
finance_autocorr_internal aggregate NULL NULL  
finance_black_litterman_internal scalar NULL NULL  
finance_checked_periodic_rate_internal macro NULL NULL  
finance_cramers_v_internal aggregate NULL NULL  
finance_crosscorr_internal aggregate NULL NULL  
finance_expm1_internal macro NULL NULL  
finance_factor_ic_internal aggregate NULL NULL  
finance_factor_turnover_internal aggregate NULL NULL  
finance_finite_internal scalar NULL NULL  
finance_first_by_internal aggregate NULL NULL  
finance_garch11_fit_internal aggregate NULL NULL  
finance_half_life_internal aggregate NULL NULL  
finance_hurst_internal aggregate NULL NULL  
finance_last_by_internal aggregate NULL NULL  
finance_ljung_box_internal aggregate NULL NULL  
finance_log1p_internal macro NULL NULL  
finance_max_sharpe_weights_internal scalar NULL NULL  
finance_min_variance_weights_internal scalar NULL NULL  
finance_money_round_internal macro NULL NULL  
finance_mutual_information_internal aggregate NULL NULL  
finance_newey_west_internal aggregate NULL NULL  
finance_periodic_rate_internal macro NULL NULL  
finance_portfolio_optimize_internal scalar NULL NULL  
finance_rank_corr_internal aggregate NULL NULL  
finance_risk_parity_weights_internal scalar NULL NULL  
finance_t_struct_internal macro NULL NULL  
finance_theils_u_internal aggregate NULL NULL  
finance_ttest2_internal macro NULL NULL  
finance_vector_multiply_internal scalar NULL NULL  
finance_weighted_mean_internal aggregate NULL NULL  
finance_weighted_var_internal aggregate NULL NULL  
finance_z_struct_internal macro NULL NULL  

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings

name description input_type scope aliases typed_value
temp_directory Set the directory to which temporary files are written. VARCHAR GLOBAL [] post.db.tmp
© 2026 DuckDB Foundation, Amsterdam NL
DuckDB Home Code of Conduct Trademark Use Blog