Answered by:
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.
There is a tradelog table already in the model like this:
And this is the wanted PowerPivot output:
I´m stuck right at the start of the needed calculated fields.
Here are the needed formulas:
Total Trades = TradeID count
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
Can you please help?
Saturday, January 28, 2017 9:31 PM
Answers

Excel 2016 Pro Plus with PowerPivot.
Partial solution, with question.
http://www.mediafire.com/file/lzbmeibn4zb4xbk/01_28_17a.xlsx Proposed as answer by davidbaxterbrowneMicrosoft employee Sunday, January 29, 2017 3:03 PM
 Marked as answer by NicoPer Monday, January 30, 2017 4:19 AM
Sunday, January 29, 2017 1:55 AM
All replies

Excel 2016 Pro Plus with PowerPivot.
Partial solution, with question.
http://www.mediafire.com/file/lzbmeibn4zb4xbk/01_28_17a.xlsx Proposed as answer by davidbaxterbrowneMicrosoft employee Sunday, January 29, 2017 3:03 PM
 Marked as answer by NicoPer Monday, January 30, 2017 4:19 AM
Sunday, January 29, 2017 1:55 AM 
Hi Herbert
Thank you very much for your reply.
(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 NicoPer 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.
Same link.Monday, January 30, 2017 2:08 AM 
Perfect. Thanks Herbert a lot for your help.Monday, January 30, 2017 4:20 AM