# Backtest a trading strategy Read one ETH price per day from Chainlink, write buy and sell rules, and compare them with buying and holding. Canonical HTML: ## Goal Test a simple trading rule on real ETH prices: - Buy ETH when the price is above its 20-day average. - Sell ETH when the price falls below it. Then check whether the rule did better than buying ETH once and holding it. You will read the prices from the Chainlink ETH/USD price feed on Ethereum, then let [`backtest_from_signals`](/docs/functions/backtest_from_signals.md) play the trades for you. Each strategy starts with 1,000 USDC and pays a 0.30% fee on every trade. ## Before you begin The notebook reads old blocks, so it needs an Ethereum client that keeps history (an archive node). Each step shows a saved result. Select **Run all** to fetch fresh data. The steps build on each other: each one reads the table the step before it made. ## Parameters - **Ethereum client** (`client`) - **Chainlink feed ABI** (`feed_abi`, type `abi`) — The notebook calls latestRoundData for the price and decimals for its scale. Default: `[{"type":"function","name":"latestRoundData","stateMutability":"view","inputs":[],"outputs":[{"name":"roundId","type":"uint80"},{"name":"answer","type":"int256"},{"name":"startedAt","type":"uint256"},{"name":"updatedAt","type":"uint256"},{"name":"answeredInRound","type":"uint80"}]},{"type":"function","name":"decimals","stateMutability":"view","inputs":[],"outputs":[{"name":"","type":"uint8"}]}]`. ### 1. Pick one block per day You cannot ask the price feed for "the price on 3 July". You can only ask what it said at a given block. So first, turn each day into a block. [`block_at`](/docs/functions/block_at.md) returns the last block at or before a time. This step asks for the block at 00:00 UTC on each day from 1 July to 28 September 2026: 90 days. #### One block per day Find the last block at or before 00:00 UTC on each of 90 days. ```sql SELECT day::DATE AS day, block_at($client, day) AS block_number FROM generate_series( TIMESTAMP '2026-07-01', TIMESTAMP '2026-09-28', INTERVAL 1 DAY ) AS days(day) ORDER BY day; ``` ### 2. Read the price on each day The ETH/USD feed lives at `0x5f4eC3Df9cbd43714FE2740f5E3616155c5b8419`. [`read_contract`](/docs/functions/read_contract.md) calls two of its functions at each day's block: - `latestRoundData` returns the price in the `answer` field. - `decimals` says how many digits of `answer` come after the decimal point. For ETH/USD it is 8, so an answer of `250000000000` means 2,500 USD. The block number goes right after the function name. Keep `answer` as a whole number: the backtest needs exact values. Use [`format_units`](/docs/functions/format_units.md) only to show the price. #### ETH/USD at each block Read the Chainlink ETH/USD answer and its decimals at each day's block. ```sql WITH reads AS ( SELECT day, block_number, read_contract( $client, '0x5f4eC3Df9cbd43714FE2740f5E3616155c5b8419'::ADDRESS, $feed_abi, 'latestRoundData', block_number ) AS round, read_contract( $client, '0x5f4eC3Df9cbd43714FE2740f5E3616155c5b8419'::ADDRESS, $feed_abi, 'decimals', block_number ) AS price_decimals FROM daily_blocks ) SELECT day, block_number, (round).answer AS price, price_decimals, format_units((round).answer, price_decimals) AS eth_usd FROM reads ORDER BY day; ``` ### 3. Write the buy and sell rules A rule is a true or false value on each day: - `entry` is true on days you want to buy. - `exit` is true on days you want to sell. This step makes two strategies: - `ma20` buys when the price is above the average of the last 20 days, and sells when it is below. `ROWS 19 PRECEDING` means "this day and the 19 days before it". The rule waits until it has 20 days of prices. - `hold` says buy on the first day and never says sell. It is the baseline to beat. A rule may only look at today and earlier days. `lag` and `PRECEDING` are safe. `lead`, `FOLLOWING` and an average over all days (`OVER ()`) peek at future prices. A rule that peeks looks better in a backtest than it could ever do for real. #### Entry and exit signals Build a 20-day moving-average strategy and a buy-and-hold baseline. ```sql WITH averages AS ( SELECT day, price, price_decimals, avg(price) OVER last_20_days AS average_20d, count(*) OVER last_20_days AS days_seen FROM daily_prices WINDOW last_20_days AS (ORDER BY day ROWS 19 PRECEDING) ) SELECT 'ma20' AS strategy, day, price, price_decimals, format_units(price, price_decimals) AS eth_usd, format_units(average_20d, price_decimals) AS average_20d_usd, days_seen = 20 AND price > average_20d AS entry, days_seen = 20 AND price < average_20d AS exit FROM averages UNION ALL SELECT 'hold', day, price, price_decimals, format_units(price, price_decimals), NULL, day = DATE '2026-07-01', false FROM daily_prices ORDER BY strategy, day; ``` ### 4. Run the backtest [`backtest_from_signals`](/docs/functions/backtest_from_signals.md) takes a query with one row per strategy and day. It needs these columns: | Column | What it holds | | --- | --- | | `strategy` | The strategy's name. Each one gets its own cash. | | `ts` | The time of the row. | | `price` | The price, as a whole number. | | `entry`, `exit` | The rules from step 3. | | `init_cash` | Starting cash: 1,000 USDC is `parse_units('1000', 6)`. | | `fee_bps` | Fee per trade, in hundredths of a percent. 30 means 0.30%. | | `base_decimals` | Decimals of the asset you buy. ETH has 18. | | `quote_decimals` | Decimals of your cash. USDC has 6. | | `price_decimals` | Decimals of the price. The feed said 8. | A signal trades on the next day, at the next day's price. You decide after you see today's price, so you can only trade after that. This is why `hold` buys on 2 July, and why a signal on the last day never trades. The feed prices ETH in US dollars. This guide counts 1 USD as 1 USDC. #### Replay the trades Replay both strategies with 1,000 USDC each and a 0.30% fee. ```sql SELECT * FROM backtest_from_signals(( SELECT strategy, day::TIMESTAMP AS ts, price, entry, exit, parse_units('1000', 6) AS init_cash, 30 AS fee_bps, 18 AS base_decimals, 6 AS quote_decimals, price_decimals FROM signals )) ORDER BY strategy, ts; ``` ### 5. List the trades The ledger has one row per strategy and day. Most days have no trade. A row with a trade has `side` set to `buy` or `sell`. The amounts are whole numbers in each token's smallest unit, so `format_units` makes them readable. #### Buys and sells Show the days each strategy bought or sold, with the amounts formatted. ```sql SELECT strategy, ts::DATE AS day, side, format_units(price, price_decimals) AS eth_usd, format_units(fill_size, 18) AS eth_traded, format_units(fee, 6) AS fee_usdc, format_units(equity, 6) AS value_usdc FROM ledger WHERE side IS NOT NULL ORDER BY strategy, ts; ``` ### 6. Compare the results `equity` is what a strategy is worth on that day: its cash plus its ETH at that day's price. The value on the last day is the final result, and `arg_max(equity, ts)` picks it. [`exact_change`](/docs/functions/exact_change.md) turns the start and end values into an exact percent return. Do not subtract the raw amounts yourself: they are `UINT256`, a type with no negative numbers. A loss would wrap around to a huge number. If you need the difference, cast both to `INT256` first. #### Final value and return Count trades and fees, then compare each strategy's final value and return. ```sql SELECT strategy, count(side) AS trades, 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_percent FROM ledger GROUP BY strategy ORDER BY strategy; ``` In the saved result, holding wins. ETH rose from about 1,567 USD to about 2,685 USD, so `hold` ends near 1,662 USDC, up 66%. `ma20` ends near 1,158 USDC, up 16%. It waited 20 days before its first buy, sold during each dip, bought back higher, and paid fees on 9 trades. Its last buy is still open, so its ETH is valued at the last day's price. One window proves little. A rule that loses here can win in a falling market. Try other dates before you trust a result. ## What to try next - Change `ROWS 19 PRECEDING` and `days_seen = 20` to try a shorter or longer average. Add it as a third strategy to compare all of them in one run. - Change `fee_bps` to see how much fees cost a strategy that trades often. - Use another feed, such as BTC/USD at `0xF4030086522a5bEEa4988F8cA5B36dbC97BeE88c`. Read its `decimals`: not every feed uses 8. ## Limits - A trade buys with all the cash or sells the whole position. There is no short selling. - Gas, price impact and failed trades are not counted. - A position still open on the last day stays open. It is valued at the last price. ## Functions used in this guide - [block_at](/docs/functions/block_at.md) - [read_contract](/docs/functions/read_contract.md) - [backtest_from_signals](/docs/functions/backtest_from_signals.md) - [exact_change](/docs/functions/exact_change.md) - [parse_units](/docs/functions/parse_units.md) - [format_units](/docs/functions/format_units.md) ## Continue with - [get_logs](/docs/functions/get_logs.md) - [muldiv](/docs/functions/muldiv.md)