locked
System Quality Number (SQN) and Expectancy in Power Pivot RRS feed

  • 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

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

    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