Excel to qex¶
A walkthrough for spreadsheet people.
If you know how to use Excel, you are well on your way to expressing quant ideas in the qex language! A sheet of prices, a few columns of formulas, a ranking at the end: that's how qex thinks too. It just writes it down differently.
To be clear about what qex is: it isn't an Excel add-in, and it doesn't run inside a spreadsheet. It's a short language for describing a signal. You write the steps as one line, and qex works them out for every stock and every day at once.
This page builds one strategy twice: first the way you would in Excel, then the way you'd write it in qex. Same idea, same numbers. If you'd like to follow along, the workbook has every step as ordinary Excel formulas.
Front to back: data → signal → weights → P&L
The walkthrough goes all the way from prices to profit and loss, in four stages:
- Data: daily closing prices for six stocks.
- Signal (steps 1 to 6): a score for every stock on every day, saying which to buy and which to sell.
- Weights (steps 7 and 8): the score turned into a portfolio, how much of the book in each stock.
- P&L (steps 9 and 10): what that portfolio earned, day by day.
The qex language covers the first three, from data to weights, in one line. Adding up the P&L is the job of a backtest, which runs the weights through history; it's in the workbook too, so you can see the whole trip.
The idea¶
Short-term reversal. Stocks that fell hard last week tend to bounce back, and stocks that rose tend to give some of it back. A portfolio manager would ask for three refinements before trading it:
- Scale by volatility. A 5% drop is news for a utility and noise for a biotech. Measure last week's move in units of the stock's own daily volatility.
- Compare within the sector. If every energy stock fell because oil fell, that is not an overreaction to buy. Compare each stock with its own sector.
- Rank. Turn the scores into percentiles, so one extreme stock doesn't dominate the book.
The data: six stocks over 30 trading days, AAA, BBB and CCC in Tech, DDD, EEE and FFF in Energy. The tables below show the last five days.
close()| AAA | BBB | CCC | DDD | EEE | FFF | |
|---|---|---|---|---|---|---|
| Apr 05 | 55.04 | 116.48 | 33.62 | 83.69 | 59.40 | 27.02 |
| Apr 08 | 51.95 | 113.84 | 33.29 | 84.55 | 59.30 | 27.28 |
| Apr 09 | 52.79 | 115.15 | 33.16 | 82.30 | 60.89 | 26.91 |
| Apr 10 | 53.62 | 118.76 | 33.69 | 83.37 | 62.82 | 26.02 |
| Apr 11 | 55.09 | 118.43 | 34.57 | 82.91 | 64.20 | 26.22 |
Step by step in Excel¶
The workbook has one sheet per step. Every sheet has the same layout: dates down column A from row 3, one stock per column from B to G, and each stock's sector in row 2. Each step is one formula, written in one cell and filled across the grid. The formulas below are the ones in cell B23 (AAA on Mar 29, the first day every step has a value).
1. Daily returns¶
Sheet Returns: today's close over yesterday's, minus 1.
In qex: returns(1).
returns(1)| AAA | BBB | CCC | DDD | EEE | FFF | |
|---|---|---|---|---|---|---|
| Apr 05 | −0.037 | −0.006 | −0.019 | 0.042 | 0.024 | 0.031 |
| Apr 08 | −0.056 | −0.023 | −0.010 | 0.010 | −0.002 | 0.010 |
| Apr 09 | 0.016 | 0.012 | −0.004 | −0.027 | 0.027 | −0.014 |
| Apr 10 | 0.016 | 0.031 | 0.016 | 0.013 | 0.032 | −0.033 |
| Apr 11 | 0.027 | −0.003 | 0.026 | −0.006 | 0.022 | 0.008 |
2. Last week's return¶
Sheet Return5: today's close over the close five rows up, minus 1.
In qex: returns(5).
returns(5)| AAA | BBB | CCC | DDD | EEE | FFF | |
|---|---|---|---|---|---|---|
| Apr 05 | −0.027 | −0.006 | −0.009 | 0.036 | 0.007 | −0.004 |
| Apr 08 | −0.077 | −0.027 | 0.004 | 0.080 | 0.016 | 0.014 |
| Apr 09 | −0.063 | −0.041 | −0.007 | 0.009 | 0.005 | −0.010 |
| Apr 10 | −0.071 | −0.014 | −0.046 | 0.043 | 0.062 | −0.025 |
| Apr 11 | −0.036 | 0.010 | 0.009 | 0.032 | 0.107 | 0.000 |
3. Volatility¶
Sheet Vol: the standard deviation of the last 20 daily returns. This is why the signal starts on row 23: the first 20 returns run from row 4 to row 23.
In qex: ts_std(returns(1), 20). Any qex operator whose name starts with ts_ looks back over a window of rows like
this one, for each stock.
ts_std(returns(1), 20)| AAA | BBB | CCC | DDD | EEE | FFF | |
|---|---|---|---|---|---|---|
| Apr 05 | 0.023 | 0.015 | 0.027 | 0.022 | 0.018 | 0.015 |
| Apr 08 | 0.026 | 0.015 | 0.027 | 0.022 | 0.018 | 0.015 |
| Apr 09 | 0.025 | 0.015 | 0.025 | 0.022 | 0.019 | 0.014 |
| Apr 10 | 0.025 | 0.016 | 0.024 | 0.022 | 0.020 | 0.016 |
| Apr 11 | 0.025 | 0.016 | 0.025 | 0.022 | 0.020 | 0.016 |
4. The score¶
Sheet Score: last week's return, flipped (a loss is a buy), in units of daily volatility.
In qex: -returns(5) / ts_std(returns(1), 20). Arithmetic works on the whole grid at once, cell by cell, as if
you had filled the formula everywhere.
-returns(5) / ts_std(returns(1), 20)| AAA | BBB | CCC | DDD | EEE | FFF | |
|---|---|---|---|---|---|---|
| Apr 05 | 1.21 | 0.38 | 0.34 | −1.66 | −0.39 | 0.27 |
| Apr 08 | 2.91 | 1.73 | −0.15 | −3.69 | −0.89 | −0.91 |
| Apr 09 | 2.47 | 2.81 | 0.28 | −0.43 | −0.24 | 0.70 |
| Apr 10 | 2.86 | 0.88 | 1.89 | −1.97 | −3.11 | 1.56 |
| Apr 11 | 1.42 | −0.66 | −0.36 | −1.44 | −5.22 | −0.02 |
5. Sector-neutral¶
Sheet Neutral: the score minus the average score of the stock's sector that day. AVERAGEIFS averages the
day's row over the columns whose sector (row 2) matches this stock's.
In qex: group_demean(score, 'sector'). Any qex operator whose name starts with group_ works across the row, one
sector at a time. In each sector the three numbers now add up to zero: a long-short bet inside Tech and another
inside Energy, with no bet on Tech against Energy.
group_demean(-returns(5) / ts_std(returns(1), 20), 'sector')| AAA | BBB | CCC | DDD | EEE | FFF | |
|---|---|---|---|---|---|---|
| Apr 05 | 0.56 | −0.26 | −0.30 | −1.06 | 0.21 | 0.86 |
| Apr 08 | 1.41 | 0.24 | −1.65 | −1.86 | 0.94 | 0.92 |
| Apr 09 | 0.61 | 0.95 | −1.57 | −0.44 | −0.25 | 0.69 |
| Apr 10 | 0.99 | −1.00 | 0.01 | −0.80 | −1.94 | 2.73 |
| Apr 11 | 1.29 | −0.79 | −0.49 | 0.78 | −2.99 | 2.21 |
6. Rank¶
Sheet Rank: each stock's percentile across the row, from 1/6 for the lowest to 1 for the highest.
In qex: rank(...). The highest rank is the stock that fell furthest last week, for its volatility, against its
sector: the one to buy.
rank(group_demean(-returns(5) / ts_std(returns(1), 20), 'sector'))| AAA | BBB | CCC | DDD | EEE | FFF | |
|---|---|---|---|---|---|---|
| Apr 05 | 0.83 | 0.50 | 0.33 | 0.17 | 0.67 | 1.00 |
| Apr 08 | 1.00 | 0.50 | 0.33 | 0.17 | 0.83 | 0.67 |
| Apr 09 | 0.67 | 1.00 | 0.17 | 0.33 | 0.50 | 0.83 |
| Apr 10 | 0.83 | 0.33 | 0.67 | 0.50 | 0.17 | 1.00 |
| Apr 11 | 0.83 | 0.33 | 0.50 | 0.67 | 0.17 | 1.00 |
7. Long and short¶
Sheet Demean: the rank minus the day's average rank. Above the average is a stock to buy, below it a stock to sell short, and the row adds up to zero.
In qex: demean(...).
demean(rank(group_demean(-returns(5) / ts_std(returns(1), 20), 'sector')))| AAA | BBB | CCC | DDD | EEE | FFF | |
|---|---|---|---|---|---|---|
| Apr 05 | 0.250 | −0.083 | −0.250 | −0.417 | 0.083 | 0.417 |
| Apr 08 | 0.417 | −0.083 | −0.250 | −0.417 | 0.250 | 0.083 |
| Apr 09 | 0.083 | 0.417 | −0.417 | −0.250 | −0.083 | 0.250 |
| Apr 10 | 0.250 | −0.250 | 0.083 | −0.083 | −0.417 | 0.417 |
| Apr 11 | 0.250 | −0.250 | −0.083 | 0.083 | −0.417 | 0.417 |
8. Weights¶
Sheet Weights: each number divided by the day's total, ignoring signs, so the sizes add up to 1. These are the portfolio weights: a weight of 0.278 on a $10 million book is $2.78 million long, and −0.278 is $2.78 million short. Every day the longs add up to 0.5 and the shorts to −0.5, so the book is dollar-neutral: as much bought as sold.
In qex: scale(...). Notice that every day has the same six weights, ±0.278, ±0.167 and ±0.056, because ranks are
always 1/6 to 1. The signal only decides who gets which.
scale(demean(rank(group_demean(-returns(5) / ts_std(returns(1), 20), 'sector'))))| AAA | BBB | CCC | DDD | EEE | FFF | |
|---|---|---|---|---|---|---|
| Apr 05 | 0.167 | −0.056 | −0.167 | −0.278 | 0.056 | 0.278 |
| Apr 08 | 0.278 | −0.056 | −0.167 | −0.278 | 0.167 | 0.056 |
| Apr 09 | 0.056 | 0.278 | −0.278 | −0.167 | −0.056 | 0.167 |
| Apr 10 | 0.167 | −0.167 | 0.056 | −0.056 | −0.278 | 0.278 |
| Apr 11 | 0.167 | −0.167 | −0.056 | 0.056 | −0.278 | 0.278 |
9. What each position earned¶
Sheet Contribution: each stock's weight times its return. The weights are set with today's closing prices, so the earliest they can earn is tomorrow's return: the cell for Apr 01 multiplies Mar 29's weight by Apr 01's return. Using the same day's return would be cheating, trading on prices you couldn't have known when you set the weights.
In qex this is delay(weights, 1) * returns(1): yesterday's weights times today's returns. You write this pairing
only because this page builds the P&L by hand; Backtest does it for you, pairing each day's weights with the next
day's return, so a signal never needs a delay to be honest. A positive number made
money, whichever side the position was on: a short (negative weight) in a falling stock (negative return) is a
gain.
delay(scale(demean(rank(group_demean(-returns(5) / ts_std(returns(1), 20), 'sector')))), 1) * returns(1)| AAA | BBB | CCC | DDD | EEE | FFF | |
|---|---|---|---|---|---|---|
| Apr 05 | −0.002 | −0.001 | 0.001 | −0.007 | −0.007 | 0.009 |
| Apr 08 | −0.009 | 0.001 | 0.002 | −0.003 | 0.000 | 0.003 |
| Apr 09 | 0.004 | −0.001 | 0.001 | 0.007 | 0.004 | −0.001 |
| Apr 10 | 0.001 | 0.009 | −0.004 | −0.002 | −0.002 | −0.006 |
| Apr 11 | 0.005 | 0.000 | 0.001 | 0.000 | −0.006 | 0.002 |
10. The book's return¶
Sheet PnL: one number a day, the whole book's return on its gross market value (GMV, the long dollars plus the short dollars). It's the day's total P&L divided by the gross held:
Here the gross is always 1, so it's simply the sum of the day's contributions. On a $10 million book, a day of +0.25% is $25,000 made. "Growth of $1" compounds the days: each day's value is the day before times (1 + the day's return).
| Day | Return on GMV | Growth of $1 |
|---|---|---|
| Apr 01 | −0.80% | 0.9920 |
| Apr 02 | +0.25% | 0.9945 |
| Apr 03 | +1.69% | 1.0113 |
| Apr 04 | −0.08% | 1.0105 |
| Apr 05 | −0.70% | 1.0034 |
| Apr 08 | −0.67% | 0.9966 |
| Apr 09 | +1.56% | 1.0122 |
| Apr 10 | −0.43% | 1.0078 |
| Apr 11 | +0.28% | 1.0107 |
The sheet sums it up on the right: over the nine days the book returned 1.10% on its gross (1.07% compounded), with
an annualized Sharpe ratio, AVERAGE / STDEV.S × SQRT(252), of 2.06. Don't read anything into that number: nine
days of made-up prices prove nothing. The arithmetic is the point.
This step has no qex line, and that's by design. The qex language describes the portfolio you'd hold each day;
adding up what it earns over time is a backtest. In Python, qex's Backtest does exactly these sums for the whole
history, and adds trading costs, turnover and drawdowns. Run on this data, it gives the PnL sheet's numbers: the
same nine daily returns, 1.10% on gross and a Sharpe ratio of 2.06.
The same signal in qex¶
Now the qex side. Everything from prices to portfolio weights, steps 1 to 8, is one line:
If you prefer to see the steps, give each one a name, the way each sheet has one. This is the same signal:
let daily = returns(1), # sheet Returns
week = returns(5), # sheet Return5
vol = ts_std(daily, 20), # sheet Vol
score = -week / vol, # sheet Score
neutral = group_demean(score, 'sector'), # sheet Neutral
ranked = rank(neutral), # sheet Rank
centered = demean(ranked) # sheet Demean
in scale(centered) # sheet Weights
The numbers are the same too: the workbook's values match qex's to the 15th decimal place.
You don't type this into Excel. The line is the whole idea, written down; running it on real prices takes a few lines of Python, which the Quick Start shows, or a colleague who already runs qex. This page is about the writing part, and you've just read your first qex expression.
The translation¶
| In Excel | In qex |
|---|---|
| A sheet for each step | A name in let, or just nesting |
| Filling a formula down the dates and across the stocks | Nothing: every operator works on the whole grid |
A formula over the last 20 rows, STDEV.S(B4:B23) |
A time-series operator, ts_std(x, 20) |
AVERAGEIFS over the stocks in the same sector |
A group operator, group_demean(x, 'sector') |
RANK.AVG over the day's row |
A cross-sectional operator, rank(x) |
Subtracting the row's AVERAGE |
demean(x) |
Dividing by the row's SUMPRODUCT(ABS(...)) |
scale(x) |
| Yesterday's weight times today's return | delay(w, 1) * returns(1) |
| Adding up the book's P&L and compounding it | A backtest, not the language: in Python, Backtest |
| Starting the formulas on row 23, once there is enough history | Automatic: no value until the window has its data |
Why write it in qex¶
The workbook works because its data is tidy. Real data isn't, and that's where writing the idea in qex pays off:
- A stock that didn't trade. A blank price breaks
=B23/B22-1. In qex, that day's return is simply missing, and the next day's return spans the gap. See Missing Values. - Looking ahead by accident. In a spreadsheet, one formula that points a row down quietly uses tomorrow's price. The qex language has no way to say "tomorrow".
- Size. 3,000 stocks over 20 years is 15 million cells a sheet. The qex line stays one line.
- Changing your mind. A 60-day volatility, or industries instead of sectors, is one edit, not a rebuilt workbook.
Your turn¶
Three small changes to try. Each is one edit to the line above.
1. Use three months of volatility instead of one.
Answer
Change the window from 20 days to 60:
2. Bet on momentum instead: last month's winners, not last week's losers.
Answer
Use last month's return, and drop the minus sign, so the biggest gain ranks highest:
3. Rank each stock within its own sector, instead of across all six.
Answer
group_rank ranks each stock against its own sector only, in place of rank(group_demean(...)):
Every sector now gets the same ranks each day, 1/3, 2/3 and 1, so the book bets the same amount inside every sector. In the original, a sector whose stocks moved far apart from each other gets the most extreme ranks, and the biggest bets. Both are sensible; they answer "how much should a big move inside a sector count?" differently.
Where to go next¶
- Syntax: the handful of rules the language has.
- Operator Reference: every operator, most with a small worked example like the tables above.
- Examples: momentum, mean reversion, pairs, earnings and more, each explained in plain words.
- Philosophy: why signals are written this way.