Power queries: questions a conventional database can't answer in one statement
Most research questions are easy to say and awkward to compute. "Do insiders buy after a rare breakdown?" needs an event history, a filings dataset and prices. "Is this multiple high for this company?" needs statements, filing dates and the price on each of those dates. In a conventional stack each of those lives in a different system, so the question becomes an export, a notebook and a join somebody has to get right.
The third tab of the Demo, Power queries, is a collection of questions like that, each answered by one statement. This article explains what earns a query a place there and walks through seven of the ones we lead with. Every statement below is copied from the page, where it has a Run button.
What makes a query a power query
A power query combines several capabilities that normally sit in separate products:
- A universe that comes from a graph. The list of stocks is itself a query: suppliers of companies exposed to a theme, or everything within two hops of one name.
- Events scored for rarity. Every detected event carries the percentile of its size against the same stock's own history.
- Outcomes without hindsight. Forward returns measured against a same-period baseline, with thresholds computed only from data known at the time.
- Datasets next to prices. Statements, estimates, earnings, ratings and insider filings are rows in the same engine.
- Cross-asset conditions. A currency, an index or a theme can be the condition for an equity study.
- Models. Trained by a statement, then used as a function.
Each card on the page states the business question first, then the statement, what the result contains, what to notice, and the server time to expect.
1. A rare sell-off in a supplier, while its theme is falling
When a supplier to the AI-infrastructure build-out has a sell-off that is rare for that stock, on rare volume, while the theme itself is more than 10% off its high, is that a buying opportunity or the start of something worse?
OUTCOMES AFTER ret < pctl(5, 2y) AND volume > pctl(95, 2y)
AND $TH.AI.INFRA@drawdown < -10
IN (GRAPH MATCH company -supplies-> company -has_theme-> TH.AI.INFRA WHERE mcap > 10b)
SINCE 2018-01-01 FWD 5d, 20d, 60d GROUP BY sector
The universe is the graph pattern in brackets: large companies that supply a company exposed to the theme. The trigger is each stock's own worst 5% of days on its own top 5% of volume, and the regime is the theme's index.
Read the result one row at a time: a sector and a horizon, with the number of occurrences, the mean and median forward return, the share that rose, a t-statistic, the baseline and edge_pct. Look at n before anything else. A condition this specific fires rarely, and a large edge on a handful of occurrences is an anecdote.
Hindsight is where a study like this usually goes wrong. pctl(5, 2y) is the trailing two-year percentile on each day, so a 2019 sell-off is judged against 2017 to 2019, not against a distribution that already includes 2020. The baseline is the same stocks over the same years, so a rising market does not get counted as skill.
2. A shock, and what the options market expects
Propagate an 8% NVIDIA shock through the correlation graph. For which stocks is the expected sympathy move larger than the one-day move their options imply?
SELECT p.symbol, p.name, p.sector, p.expected_move_pct AS shock_move_pct, t.exp_move_pct AS implied_daily_move_pct,
abs(p.expected_move_pct) / t.exp_move_pct AS shock_vs_implied, t.iv30, p.corr_60d, p.path
FROM (GRAPH PROPAGATE NVDA -8% VIA beta DEPTH 2 LIMIT 40) p
JOIN top500 t ON p.symbol = t.symbol
WHERE t.exp_move_pct > 0
ORDER BY shock_vs_implied DESC
LIMIT 15
The subquery is a what-if on the correlation graph. The join adds each stock's option-implied daily move and 30-day implied volatility, which are ordinary fields on the list. shock_vs_implied above 1 means the propagated move is larger than what the options imply for a day. path shows the route the shock took. It is a linear what-if on recent betas, so use it to decide where to look, not as a forecast.
3. The multiple an investor saw on each filing date
For five large technology companies, rebuild the trailing P/E as an investor saw it on each filing date over six years, and say where today's multiple sits in that history.
WITH q AS (SELECT symbol, period_end, filed,
sum(eps_diluted) OVER (PARTITION BY symbol ORDER BY period_end ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS ttm_eps,
count(*) OVER (PARTITION BY symbol ORDER BY period_end ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS quarters
FROM financials
WHERE symbol IN ('AAPL', 'MSFT', 'NVDA', 'GOOGL', 'META') AND period <> 'FY' AND period_end >= '2019-06-01'),
pe AS (SELECT symbol, filed, ttm_eps, price_asof(symbol, filed) / ttm_eps AS pe_at_filing
FROM q WHERE quarters = 4 AND ttm_eps > 0 AND filed IS NOT NULL),
h AS (SELECT symbol, count(*) AS filings, min(pe_at_filing) AS pe_low, median(pe_at_filing) AS pe_median,
max(pe_at_filing) AS pe_high, avg(pe_at_filing) AS pe_avg, stddev(pe_at_filing) AS pe_sd, last(ttm_eps) AS latest_ttm_eps
FROM pe GROUP BY symbol)
SELECT h.symbol, h.filings, h.pe_low, h.pe_median, h.pe_high,
s.close / h.latest_ttm_eps AS pe_now, (s.close / h.latest_ttm_eps - h.pe_avg) / h.pe_sd AS z_vs_own_history
FROM h JOIN (AAPL, MSFT, NVDA, GOOGL, META) s ON h.symbol = s.symbol
ORDER BY z_vs_own_history
Trailing earnings are a four-quarter window over the statements. The line that matters is price_asof(symbol, filed): the price on the day the numbers became public, not on the last day of the quarter, when nobody outside the company knew them. Using the period end is one of the most common ways for hindsight to get into a valuation study, and it flatters every result.
The output is one row per company: the lowest, median and highest multiple over the period, today's, and a z-score of today's against the company's own history.
4. Whose upgrades are worth following
For every research firm with enough calls since 2024, what did stocks do in the 5, 20 and 60 days after its upgrades and after its downgrades?
SELECT r.firm, r.action, count(*) AS calls,
avg(e.fwd_5d) AS avg_5d, avg(e.fwd_20d) AS avg_20d, avg(e.fwd_60d) AS avg_60d,
sum(CASE WHEN (r.action = 'upgrade' AND e.fwd_20d > 0) OR (r.action = 'downgrade' AND e.fwd_20d < 0) THEN 1 ELSE 0 END) * 100.0
/ count(e.fwd_20d) AS pct_right_20d
FROM (RATINGS IN top500 SINCE 2024-01-01 WHERE action = 'upgrade' OR action = 'downgrade') r
JOIN (SELECT symbol, timestamp, fwd_5d, fwd_20d, fwd_60d FROM events
WHERE event IN ('analyst_upgrade', 'analyst_downgrade') AND timestamp >= '2024-01-01') e
ON e.symbol = r.symbol AND date(e.timestamp) = r.date
GROUP BY r.firm, r.action
HAVING count(*) >= 10
ORDER BY r.action, avg_20d DESC
The rating carries the firm. The event index carries the forward returns, which were written as each horizon closed. Joined on symbol and day they give a scorecard per firm and action. pct_right_20d is the share of calls where the stock moved the way the call said within 20 days. The HAVING clause is the honest part: a firm with three calls has no record.
5. One rule, with and without its market filter
How much of a breakout strategy's result comes from the market filter? Run it twice, once only when breadth is healthy, and put the two reports side by side.
SELECT a.metric, a.value AS no_filter, b.value AS breadth_above_50, b.value - a.value AS filter_effect
FROM (BACKTEST IN top500 SINCE 2012-01-01
ENTER WHEN close CROSS ABOVE highest(high, 55)[-1] AND rvol(20) > 1.5
EXIT WHEN close < lowest(low, 20)[-1] STOP 10% COST 5 bps MAX 10) a
JOIN (BACKTEST IN top500 SINCE 2012-01-01
ENTER WHEN close CROSS ABOVE highest(high, 55)[-1] AND rvol(20) > 1.5 AND $BRD.A200@close > 50
EXIT WHEN close < lowest(low, 20)[-1] STOP 10% COST 5 bps MAX 10) b ON a.metric = b.metric
Two complete backtests are subqueries, joined on the metric name. Each row is a metric (signals, trades, win rate, return, drawdown and so on) with the unfiltered value, the filtered value and the difference. $BRD.A200@close > 50 reads "more than half of stocks are above their 200-day average". The [-1] on the highest high and lowest low means the prior bar's value, so a bar is never compared with a level that includes itself. The list is today's 500 largest stocks, so a long test favours survivors; read the difference between the columns, not the level.
6. A currency as the trigger for an equity study
When the dollar falls more than 4% against the yen within twenty sessions, the classic sign of carry trades being unwound, what have US stocks done over the next week and month, sector by sector?
OUTCOMES AFTER USDJPY@roc(20) CROSS BELOW -4 IN top500 SINCE 2010-01-01 FWD 5d, 20d GROUP BY sector
USDJPY@roc(20) is the 20-session change of another instrument, used inside a condition on stocks. CROSS BELOW counts the day the threshold is first broken, not every day the pair stays below it. The result has one row per sector and horizon, against each sector's own baseline. A market-wide trigger fires on the same dates for every stock, so n overstates how many independent episodes there were.
7. A model as a function
Apply the volatility model to every large cap. Where does the forecast for next month sit furthest below what options imply, so volatility looks most overpriced by the model's reckoning?
SELECT symbol, sector, rv_cc(20) AS vol_now,
predict('me.pq_vol', rv_cc(20), rv_cc(60), abs(ret), $VIX@close) AS vol_forecast,
iv30 AS implied_vol,
iv30 - predict('me.pq_vol', rv_cc(20), rv_cc(60), abs(ret), $VIX@close) AS implied_minus_forecast,
rank() OVER (PARTITION BY sector ORDER BY iv30 - predict('me.pq_vol', rv_cc(20), rv_cc(60), abs(ret), $VIX@close) DESC) AS rk_in_sector
FROM top500
WHERE iv30 > 0
ORDER BY implied_minus_forecast DESC
LIMIT 20
This is step 2 of 2. The card before it on the page trains me.pq_vol with one CREATE MODEL statement and reports its error on the last 30% of the history, which is held back from training. Run that first, or this one has no model to call. Here the model is scored per row, next to live option fields, with no export. See Machine learning.
Why "no hindsight" keeps coming up
Every statement above could be written to look better than it is. Rank against the full-sample distribution, price at the period end, let a daily value inside an intraday bar see that day's close, or skip the baseline, and the edge grows. The engine's defaults go the other way: percentiles are trailing, as-of lookups use the date you give them, higher timeframes come from the last closed bar, and OUTCOMES always reports a baseline next to the mean.
That does not make a result a strategy. Samples can be small, lists are today's membership, and trying many conditions produces winners by chance; SEARCH exists for that last problem. What it does mean is that the statement you read is the whole method. Nothing happened in a notebook that the reader cannot see.
The page has more than two hundred statements in 22 categories at two levels, Advanced and Expert, with filters by level, capability and expected time. Open Power queries, look for the cards marked "Lead with this" and run the first one. The references are OUTCOMES, EVENTS, GRAPH, BACKTEST and SQL.