tickerbot_scan
Screen every ticker with a SQL WHERE clause and return matches; run now or backtest past dates, with optional grouping into rollups.
Instructions
Screens every ticker with a SQL WHERE clause and returns the matches. Run it now, or add asof to backtest it on any past date. The q grammar is a flat SQL WHERE over signal names: AND/OR/NOT, comparisons, numeric and string literals, custom signals by name. No JOIN or subqueries. With group_by the result is rollup rows, not tickers. Example: gap_up AND market_cap < 2000000000 AND NOT earnings_this_week.
Tool call template
Replace each placeholder with a value matching the input schema. Only required parameters are included; add optional parameters as needed.
{
"id": 1,
"jsonrpc": "2.0",
"method": "tools/call",
"params": {
"arguments": {
"q": "<q>"
},
"name": "tickerbot_scan"
}
}Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| q | Yes | SQL WHERE expression. Max 4000 chars; semicolons, comments and write keywords are rejected. Your custom signals are valid here: each expands to its SQL at run time. To scan named tickers: `ticker IN ('AAPL', 'MSFT')`; to scan one asset class: `asset_class = 'crypto'`. | |
| dir | No | Sort direction. Values: asc: Ascending: smallest or earliest first. desc: Descending: largest or most recent first. | desc |
| asof | No | Target moment as `YYYY-MM-DD` (that day's close) or an ISO timestamp (that intraday moment; daily-only signals then carry the previous session's close, never that day's): the same read as it stood then, unlimited depth. In `q`, `now()` and `current_date` mean that moment, so `last_earnings_date >= now() - interval '7 days'` backtests as written. Full contract under As of a past date. | |
| grain | No | Only with `asof`. Grain the past state is read at: `1m`, `1h`, `1d` or `auto` (default). `auto` blends: each signal comes from its freshest grain at or before the instant (minute, then hourly, then the last closed daily session), so nothing is refused for grain and no ticker is dropped; `_meta.blended` and `_meta.grains_present` say what contributed. Pin a grain for the fastest response: one grain is read instead of three, and every ticker is captured on the same clock. A signal the pinned grain does not store is a `400 interval_unavailable` naming the grains that carry it (`rsi_14`, fundamentals and valuation ratios are `1d`-only). `1m` and `1h` need a timestamp `asof`; with a bare date they are a `400 interval_unavailable`. Values: 1m: Minute. The one-minute state at or before the instant. Covers the most liquid tickers and the signals stored at one minute. Needs a timestamp `asof`; with a bare date it is a `400 interval_unavailable`. 1h: Hour. The hourly state at or before the instant. Covers every ticker and the signals stored hourly. Needs a timestamp `asof`; with a bare date it is a `400 interval_unavailable`. 1d: Day. The last daily session closed before the instant; a bare date reads that day’s close. Covers every ticker and the most signals, including the daily-only ones such as `rsi_14`, fundamentals and valuation ratios. auto: Auto. Each signal from its freshest grain at or before the instant: minute, then hourly, then the last closed daily session. `_meta.blended` and `_meta.grains_present` say what contributed. | auto |
| limit | No | Page size. Max 100. Grouped scans do not paginate: `truncated: true` when groups were cut, so sort with `order` to keep the ones you want. | |
| order | No | Signal to sort by; it joins the default row. On a grouped scan the default is the count alias `tickers` (or, with a custom `select`, the last item's alias, so alias it with `AS`), sorted NULLS LAST with the group keys as tiebreak. | change_1d_pct |
| cursor | No | The previous response's `next_cursor`. Send it with the same params as the first page (`limit` may change); a page whose params differ is a 400 naming the param. Row scans only. | |
| having | No | Only with `group_by`. Filter the rollups. Custom signals are valid here too. | |
| select | No | What each result carries, as in SQL. Omitted: the default row (ticker, name, asset_class, asset_type, price, change_1d_pct, gap_pct, relative_volume, market_cap) plus every signal used in `q` and `order`; a custom signal appears as its own value, and a cross-ticker reference (`SPY.sma_50`) is not added. A list: exactly those items, `ticker` always included; expressions with `AS` work (`price / sma_50 AS dist`) and are validated like `q`. `*`: every signal in the catalog. All aggregates (`count(*), avg(rsi_14)`) without `group_by`: one summary row, in `results`. With `group_by`, each item is an aggregate or a group key. Aggregates: count/avg/sum/min/max/stddev/string_agg/bool_and/bool_or, plus `FILTER (WHERE …)`. | |
| group_by | No | Groups the scan: 1–6 group keys (signals, expressions or one of your custom signals as a boolean key). Results become rollups in `results`, each carrying its keys. Name a key with `AS` to choose its JSON key (`market_cap > 1e11 AS mega`); an un-named expression is named for you. Default `select`: the keys + `COUNT(*) AS tickers`. Works with `asof`; does not paginate. | |
| universe | No | Scope to a built-in universe (`top_10`, `top_100`) or one of your own, by id. An unknown one answers 200 with no results and its id in `not_found`. Omitted, the screen runs across every ticker. |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
| _meta | No | Present when there is something to disclose. Row scans add `null_coverage`: per signal in the predicate (under `signals`), how many in-scope tickers are `null` and therefore never evaluated; absence from `results` means "no value", not "did not match". With `asof`: `grain_scope` (the snapshot spans every ticker in scope at one grain), `signals_not_stored_at_grain` (default-row signals this grain does not store, returned as `null`), `minute_only_signals_fell_back_to_daily`, `empty_diagnostic` (on an empty result, each signal's coverage at that instant) and a `note` naming the signals that have no history and show today's value. Shared keys: see the `_meta` reference. | |
| as_of | Yes | Server time this response was assembled (ISO 8601); on an as-of read, the instant you asked for (As-of). | |
| count | Yes | Rows in this page (matches on a row scan, groups on a grouped scan). It is not a total: for one, scan with `select=count(*)`. | |
| query | Yes | Your query, echoed in param order (`q`, `universe`, `asof`, `grain`, `select`, `group_by`, `having`, `order`, `dir`, `limit`, `cursor`), each with its effective value: `select` lists the signals each result carries. | |
| results | Yes | Row scans: one result per match, carrying what `select` asked for (see `select`). Grouped scans (`group_by`, or a `select` of aggregates alone): one row per group, carrying the group keys, then the `select` items under their aliases (default: `tickers`, the count). A `select` of aggregates alone without `group_by` returns one row. | |
| not_found | No | With `universe` only: the `universe` id you named when it does not exist (the scan then has no results). Absent otherwise. | |
| truncated | No | On grouped scans (`group_by`, or a `select` of aggregates alone), on every response: `true` when the rollup stopped at its result cap, `false` when the list is whole. Grouped responses are unpaged, so `next_cursor` is absent there. | |
| _advisories | No | Present only when the response carried a warning: `deprecated` (since, sunset_on, successor, warning; on a deprecated tool, successor names the tool to call), `rate_limit` (warning, retry_after_seconds, limit, remaining, reset_epoch_s) or `monthly_quota` (warning, limit, remaining, reset_epoch_s). | |
| next_cursor | No | Opaque token for the next page; `null` on the last page. Pass it back as `cursor`. Row scans only. |