# backtest_from_signals

Backtests a long-only spot trading strategy in SQL from entry and exit signals, with exact token amounts. An entry buys with all cash at the next row's price; an exit sells the whole position at the next row's price. Gas, price impact and failed trades are not modeled.

Canonical HTML: <https://determica.com/docs/functions/backtest_from_signals>

- Kind: table function
- Category: [Backtesting](/docs/category/backtesting.md)
- Tags: Backtest, Strategy, Signals, Exact Math

Backtests a long-only spot trading strategy in SQL from entry and exit signals, with exact token amounts. An entry buys with all cash at the next row's price; an exit sells the whole position at the next row's price. Gas, price impact and failed trades are not modeled.

## Overload 1: backtest_from_signals(TABLE)

```sql
backtest_from_signals(TABLE)
```

Takes a query of prices, signals and settings and returns one ledger row per input row.

- Kind: table
- Execution context: Offline
- Behavior: Replay Signals
- Execution modes: Offline
- Input shape: Relation
- Planning contract: Runtime
- Cardinality: One Per Input
- Determinism: Deterministic
- Return shape: Table
- Schema stability: Fixed
- Stability: Stable
- Side effects: None documented

### Inputs

- `signals` (TABLE; required; positional) — One row per strategy and time, with the price, the entry and exit signals, and the strategy's settings as columns.

### Relation input

Columns match by name, in any order; unknown or repeated columns are errors. Every column except ts, price, entry and exit must stay the same within a strategy. price and init_cash take UINT256, INT256 or integer types; decimals and fee_bps take integer types.

### Relation columns

- `ts` (TIMESTAMP) — TIMESTAMP or TIMESTAMPTZ; one row per time within a strategy. Required
- `price` (UINT256) — Quote per whole base token, as a positive integer scaled by 10^price_decimals. Required
- `entry` (BOOLEAN) — Buy with all cash at the next row when flat. NULL means false. Required
- `exit` (BOOLEAN) — Sell the whole position at the next row when holding. NULL means false. Required
- `init_cash` (UINT256) — Starting cash in raw quote units; must be positive. Required
- `strategy` (VARCHAR; default: 'default') — Name of a separate run with its own cash and position. Optional. Default: 'default'
- `fee_bps` (INTEGER; default: 0) — Fee per fill, 0 to 9999 basis points, charged in quote units. Optional. Default: 0
- `base_decimals` (INTEGER; default: 18) — Decimals of the base token, 0 to 77. Optional. Default: 18
- `quote_decimals` (INTEGER; default: 18) — Decimals of the quote token, 0 to 77. Optional. Default: 18
- `price_decimals` (INTEGER; default: 18) — Decimals of the price, 0 to 77. Optional. Default: 18
- `base_token` (VARCHAR) — Base token label. Labels convert nothing. Optional
- `quote_token` (VARCHAR) — Quote token label. Labels convert nothing. Optional

```sql
SELECT ts, price, entry, exit, init_cash FROM signals
```

### Result columns

One ledger row per input row

Returns each row's fill, the cash, base size and equity after it, and the strategy's tokens and decimals. Amounts are raw UINT256 integers.

- `strategy` (VARCHAR) — Strategy name; 'default' when omitted.
- `ts` (TIMESTAMP) — Row time in UTC.
- `price` (UINT256) — Input price for this row.
- `side` (VARCHAR) — 'buy', 'sell', or NULL when nothing filled.
- `fill_size` (UINT256) — Base units traded; 0 when nothing filled.
- `fee` (UINT256) — Fee in quote units; 0 when nothing filled.
- `cash` (UINT256) — Quote units held after this row.
- `size` (UINT256) — Base units held after this row.
- `equity` (UINT256) — Cash plus the position valued at this row's price, in quote units, rounded down.
- `base_token` (VARCHAR) — Base token label, if given.
- `quote_token` (VARCHAR) — Quote token label, if given.
- `base_decimals` (INTEGER) — Decimals of base amounts.
- `quote_decimals` (INTEGER) — Decimals of cash, fees and equity.
- `price_decimals` (INTEGER) — Decimals of the price.

One ledger row per input row. Returns each row's fill, the cash, base size and equity after it, and the strategy's tokens and decimals. Amounts are raw UINT256 integers.

## Guidance

### Trading rules

Each strategy starts with init_cash and no position. On each row, the previous row's order fills at this row's price, then this row's signals set the next order. So a signal on the last row never fills.

- An entry while holding, or an exit while flat, does nothing.
- If entry and exit are both true on one row, nothing happens, even when holding. To let the exit win, set entry to false wherever exit is true.
- A buy that cannot afford one raw base unit, or a sale worth less than one raw quote unit, does not fill and charges no fee. After a skipped sale the position stays open.
- A position still open at the end stays open and is valued at the last price.

### Signals and look-ahead bias

Fills wait for the next row, but that does not make the signals honest. A signal must use only data up to its own row.

- Safe: lag(), and window frames that end at the current row, such as ROWS 2 PRECEDING.
- Leaks future prices: lead(), frames with FOLLOWING, and averages over the whole series such as avg(price) OVER ().
- Strategies share no cash or position. Run many in one call for a parameter sweep or a buy-and-hold benchmark.

### Prices and exact amounts

Balances stay exact UINT256 integers. A buy spends at most floor(cash * 10000 / (10000 + fee_bps)), rounds its cost up, and rounds the fee up on what it spent. A sale rounds proceeds down and the fee up. A result too large for UINT256 raises an error.

- Turn prices and cash into scaled integers first, for example with parse_units. DOUBLE and DECIMAL are rejected.
- Pass the real token decimals; all three default to 18.
- Price in the token you trade: an ETH/USD feed is not an ETH/USDC price.

### Reading results

The output has one row per input row. Group by strategy to sum up each run.

- Fills are the rows WHERE side IS NOT NULL; count(side) counts them.
- Final equity is arg_max(equity, ts).
- exact_change(init_cash, quote_decimals, final_equity, quote_decimals) gives the exact return.
- UINT256 subtraction wraps around below zero. Cast to INT256 first: equity::INT256 - init_cash::INT256.
- Output order is not guaranteed; add ORDER BY strategy, ts.

```sql
-- A momentum strategy against buy-and-hold, 1,000 USDC each
WITH prices(ts, price) AS (VALUES
  (TIMESTAMP '2026-01-01', 2000), (TIMESTAMP '2026-01-02', 2100),
  (TIMESTAMP '2026-01-03', 2200), (TIMESTAMP '2026-01-04', 2000)
),
signals AS (
  SELECT 'momentum' AS strategy, ts, price,
         price > lag(price) OVER (ORDER BY ts) AS entry,
         price < lag(price) OVER (ORDER BY ts) AS exit
  FROM prices
  UNION ALL
  SELECT 'hold', ts, price, ts = TIMESTAMP '2026-01-01', false
  FROM prices
),
ledger AS (
  FROM backtest_from_signals((
    SELECT *, parse_units('1000', 6) AS init_cash, 30 AS fee_bps,
           18 AS base_decimals, 6 AS quote_decimals, 0 AS price_decimals
    FROM signals
  ))
)
SELECT strategy, count(side) AS fills,
       format_units(sum(fee), 6) AS fees_usdc,
       format_units(arg_max(equity, ts), 6) AS final_usdc,
       (exact_change(parse_units('1000', 6), 6, arg_max(equity, ts), 6)).change_percent AS return_pct
FROM ledger
GROUP BY strategy
ORDER BY strategy;
```

## Additional examples

```sql
-- Daily ETH closes in whole USDC. Long while the close is above its 3-day average.
WITH prices(day, close) AS (VALUES
  ('2026-01-01', 2000), ('2026-01-02', 2050), ('2026-01-03', 2120), ('2026-01-04', 2200),
  ('2026-01-05', 2150), ('2026-01-06', 2050), ('2026-01-07', 1980), ('2026-01-08', 2060),
  ('2026-01-09', 2180), ('2026-01-10', 2100)
),
signals AS (
  SELECT day::TIMESTAMP AS ts, close,
         close > avg(close) OVER (ORDER BY day ROWS 2 PRECEDING) AS above_ma3
  FROM prices
),
ledger AS (
  FROM backtest_from_signals((
    SELECT ts, close AS price, above_ma3 AS entry, NOT above_ma3 AS exit,
           parse_units('10000', 6) AS init_cash, 30 AS fee_bps,   -- 10,000 USDC, 0.30% per fill
           18 AS base_decimals, 6 AS quote_decimals, 0 AS price_decimals
    FROM signals
  ))
)
-- Signals fill at the next close; day 10's exit has no next day, so the position stays open
SELECT ts::DATE AS day, close, above_ma3, side,
       format_units(size, 18) AS eth, format_units(equity, 6) AS equity_usdc
FROM ledger JOIN signals USING (ts)
ORDER BY ts;
```