Skip to content

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.

Download the Excel workbook

Front to back: data → signal → weights → P&L

The walkthrough goes all the way from prices to profit and loss, in four stages:

  1. Data: daily closing prices for six stocks.
  2. Signal (steps 1 to 6): a score for every stock on every day, saying which to buy and which to sell.
  3. Weights (steps 7 and 8): the score turned into a portfolio, how much of the book in each stock.
  4. 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:

  1. 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.
  2. 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.
  3. 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()
AAABBBCCCDDDEEEFFF
Apr 0555.04116.4833.6283.6959.4027.02
Apr 0851.95113.8433.2984.5559.3027.28
Apr 0952.79115.1533.1682.3060.8926.91
Apr 1053.62118.7633.6983.3762.8226.02
Apr 1155.09118.4334.5782.9164.2026.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.

=Prices!B23/Prices!B22-1

In qex: returns(1).

returns(1)
AAABBBCCCDDDEEEFFF
Apr 05−0.037−0.006−0.0190.0420.0240.031
Apr 08−0.056−0.023−0.0100.010−0.0020.010
Apr 090.0160.012−0.004−0.0270.027−0.014
Apr 100.0160.0310.0160.0130.032−0.033
Apr 110.027−0.0030.026−0.0060.0220.008

2. Last week's return

Sheet Return5: today's close over the close five rows up, minus 1.

=Prices!B23/Prices!B18-1

In qex: returns(5).

returns(5)
AAABBBCCCDDDEEEFFF
Apr 05−0.027−0.006−0.0090.0360.007−0.004
Apr 08−0.077−0.0270.0040.0800.0160.014
Apr 09−0.063−0.041−0.0070.0090.005−0.010
Apr 10−0.071−0.014−0.0460.0430.062−0.025
Apr 11−0.0360.0100.0090.0320.1070.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.

=STDEV.S(Returns!B4:B23)

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)
AAABBBCCCDDDEEEFFF
Apr 050.0230.0150.0270.0220.0180.015
Apr 080.0260.0150.0270.0220.0180.015
Apr 090.0250.0150.0250.0220.0190.014
Apr 100.0250.0160.0240.0220.0200.016
Apr 110.0250.0160.0250.0220.0200.016

4. The score

Sheet Score: last week's return, flipped (a loss is a buy), in units of daily volatility.

=-Return5!B23/Vol!B23

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)
AAABBBCCCDDDEEEFFF
Apr 051.210.380.34−1.66−0.390.27
Apr 082.911.73−0.15−3.69−0.89−0.91
Apr 092.472.810.28−0.43−0.240.70
Apr 102.860.881.89−1.97−3.111.56
Apr 111.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.

=Score!B23-AVERAGEIFS(Score!$B23:$G23,Prices!$B$2:$G$2,Prices!B$2)

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')
AAABBBCCCDDDEEEFFF
Apr 050.56−0.26−0.30−1.060.210.86
Apr 081.410.24−1.65−1.860.940.92
Apr 090.610.95−1.57−0.44−0.250.69
Apr 100.99−1.000.01−0.80−1.942.73
Apr 111.29−0.79−0.490.78−2.992.21

6. Rank

Sheet Rank: each stock's percentile across the row, from 1/6 for the lowest to 1 for the highest.

=RANK.AVG(Neutral!B23,Neutral!$B23:$G23,1)/COUNT(Neutral!$B23:$G23)

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'))
AAABBBCCCDDDEEEFFF
Apr 050.830.500.330.170.671.00
Apr 081.000.500.330.170.830.67
Apr 090.671.000.170.330.500.83
Apr 100.830.330.670.500.171.00
Apr 110.830.330.500.670.171.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.

=Rank!B23-AVERAGE(Rank!$B23:$G23)

In qex: demean(...).

demean(rank(group_demean(-returns(5) / ts_std(returns(1), 20), 'sector')))
AAABBBCCCDDDEEEFFF
Apr 050.250−0.083−0.250−0.4170.0830.417
Apr 080.417−0.083−0.250−0.4170.2500.083
Apr 090.0830.417−0.417−0.250−0.0830.250
Apr 100.250−0.2500.083−0.083−0.4170.417
Apr 110.250−0.250−0.0830.083−0.4170.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.

=Demean!B23/SUMPRODUCT(ABS(Demean!$B23:$G23))

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'))))
AAABBBCCCDDDEEEFFF
Apr 050.167−0.056−0.167−0.2780.0560.278
Apr 080.278−0.056−0.167−0.2780.1670.056
Apr 090.0560.278−0.278−0.167−0.0560.167
Apr 100.167−0.1670.056−0.056−0.2780.278
Apr 110.167−0.167−0.0560.056−0.2780.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.

=Weights!B23*Returns!B24

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)
AAABBBCCCDDDEEEFFF
Apr 05−0.002−0.0010.001−0.007−0.0070.009
Apr 08−0.0090.0010.002−0.0030.0000.003
Apr 090.004−0.0010.0010.0070.004−0.001
Apr 100.0010.009−0.004−0.002−0.002−0.006
Apr 110.0050.0000.0010.000−0.0060.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:

=SUM(Contribution!B24:G24)/SUMPRODUCT(ABS(Weights!B23:G23))

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:

scale(demean(rank(group_demean(-returns(5) / ts_std(returns(1), 20), 'sector'))))

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:

scale(demean(rank(group_demean(-returns(5) / ts_std(returns(1), 60), 'sector'))))

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:

scale(demean(rank(group_demean(returns(20) / ts_std(returns(1), 20), 'sector'))))

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(...)):

scale(demean(group_rank(-returns(5) / ts_std(returns(1), 20), 'sector')))

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.