# System Quality Number (SQN) and Expectancy in Power Pivot

• ### Question

• Hello all

We want to incorporate Van Tharp´s "System Quality Number" (SQN) and "Expectancy" into PowerPivot.

And this is the wanted PowerPivot output:

I´m stuck right at the start of the needed calculated fields.

Here are the needed formulas:
Wins % = % of wins using Total Trades (Win: PNL % >0)
Avg Win = Avg of PNL% of the winning trades
Losses % = % of losses using Total Trades (Loss: PNL % <=0)
Avg Loss = Avg of PNL% of the Losing trades
Expectancy = (Wins % * Avg Win) – (Losses % * Avg Loss)

PNL % in R = PNL % / 25
SQN = Squareroot (Total Trades) * Avg (PNL % in R) / Std Dev (PNL % in R)

This is the sample file:
https://www.mediafire.com/?drqw1zculf1qj5t

Saturday, January 28, 2017 9:31 PM

### All replies

• Excel 2016 Pro Plus with PowerPivot.
Partial solution, with question.
http://www.mediafire.com/file/lzbmeibn4zb4xbk/01_28_17a.xlsx

Sunday, January 29, 2017 1:55 AM
• Hi Herbert

(I´m using Excel 2013 64b. Excel 2016 doesn´t work yet with TOS RTD for some reason.)

All columns seem to be working great.
The Expectancy column was showing only positive numbers, but it was just the sign in the formula. It seems that the results are exactly the same as the Average of PNL %.

About the SQN, the original formula is this:
System Quality Number (SQN):   SQN = Squareroot (# Trades) * Avg R / Std Dev R

R is the risk unit. Everything is measured in risks.
Example:

75 / 25 = 3
(PNL %)/ (1R) = (R_Multiple = Amount of risk units)

That is the R in the SQN formula.

I got the R_Multiple working:
=ROUND([Average of PNL %]/25,2)

But still stuck with the SQN one.

• Edited by Sunday, January 29, 2017 7:51 PM
Sunday, January 29, 2017 4:08 PM
• You can't take the SQRT() of a negative number.
Sunday, January 29, 2017 10:01 PM
• But the SQRT must be taken from from the # Trades (Cnt_ID), and that is never negative.

SQN = Squareroot (# Trades) * Avg R / Std Dev R

Sunday, January 29, 2017 10:31 PM
• Updated file.