SQL Server Calculations – How to Calculate Drawdown and Volatility Over 3 Years

sql serversql-server-2016window functions

I have a table with daily prices as below and in this SQLfiddle. Each record is associated to a ShareClassID, a currency, a date, and an amount. In my real scenario I can have at least 10 years of daily prices.

The goal is to calculate the drawdown and volatility over a 3 year period rather than the full period.

The query below is doing the calculation on all records. For a given date, I want to do the calculation only on the past 3 years. I am not sure how to do it.

Adding AND valueDate >= DATEADD(yy, -3, GETDATE()) to the WHERE clause doesn't give me what I need: I need to perform 3 years calculation from the current valueDate record. On each row, I want to perform the calculation from current row to -3 years.

Sample data

CREATE TABLE [dbo].ShareClassData2( 
valueDate  [date] NOT NULL,
 NAVLocal  [numeric](26, 8) NULL,
currency_fk   [bigint] NOT NULL,
vehicleShareClassGroup_fk   [bigint] NOT NULL,
 importTransactionID    [bigint] NOT NULL
)

INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-09-29',113.49,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-09-30',113.75,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-01',113.56,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-02',113.65,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-03',113.61,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-06',113.53,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-07',113.55,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-08',113.58,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-09',113.61,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-10',113.49,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-13',113.74,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-14',113.78,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-15',113.17,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-16',113.26,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-17',113.12,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-20',113.26,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-21',113.37,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-22',113.34,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-23',113.34,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-24',113.4,12,22370,1   );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-27',113.4,12,22370,1   );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-28',113.43,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-29',113.65,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-30',113.95,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-10-31',113.84,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-03',113.91,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-04',113.86,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-05',113.92,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-06',113.99,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-07',114.01,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-10',114.11,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-11',114.1,12,22370,1   );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-12',114.09,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-13',114.16,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-14',114.13,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-17',114.08,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-18',113.9,12,22370,1   );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-19',114.07,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-20',114.37,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-21',114.43,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-24',114.61,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-25',114.65,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-26',114.94,12,22370,1  );
INSERT INTO [dbo].[ShareClassData2]( [valueDate] ,[NAVLocal],[currency_fk],[vehicleShareClassGroup_fk],[importTransactionID]) VALUES (  '2014-11-27',115.02,12,22370,1  );

Current query

     WITH stage1
          AS (
          SELECT valueDate
               , NAVLocal
               , currency_fk
               , vehicleShareClassGroup_fk 
               , LAG(NAVLocal) OVER(PARTITION BY currency_fk
                                               , vehicleShareClassGroup_fk ORDER BY valueDate) AS previousInvestorLevelNAV

               , isnull(LOG(LAG(NAVLocal) OVER(PARTITION BY currency_fk
                                                          , vehicleShareClassGroup_fk ORDER BY valueDate) / NAVLocal), 0) AS            logCalc
               , SQRT(COUNT(NAVLocal) OVER(PARTITION BY currency_fk
                                                      , vehicleShareClassGroup_fk ORDER BY valueDate)) AS                               sqrtTimeSeries
          FROM   ShareClassData2
          WHERE  NAVLocal IS NOT NULL
                 AND NAVLocal != 0),
          stage2
          AS (
          SELECT   *
           , NAVLocal / MAX(previousInvestorLevelNAV) OVER(PARTITION BY currency_fk
                                                                         , vehicleShareClassGroup_fk ORDER BY valueDate) - 1 AS                               drawdown


          FROM      stage1  

),
          stage3
          AS (
          SELECT *
               , STDEV(logCalc) OVER(PARTITION BY currency_fk
                                                , vehicleShareClassGroup_fk ORDER BY valueDate) AS                                                 standardDeviationCalc
               , MIN(drawdown) OVER(PARTITION BY currency_fk
                                               , vehicleShareClassGroup_fk ORDER BY valueDate ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) AS maximumDrawdown
               , MAX(drawdown) OVER(PARTITION BY currency_fk
                                               , vehicleShareClassGroup_fk ORDER BY valueDate ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING) AS minimumDrawdown
          FROM   stage2),
          stage4
          AS (
          SELECT a.*
               , a.standardDeviationCalc * a.sqrtTimeSeries AS volatility
          FROM   stage3 AS a)
          SELECT valueDate
               , currency_fk
               , vehicleShareClassGroup_fk 
               , data.volatility
               , data.drawdown
               , data.drawdown * 100 AS        drawdownPercentage
               , data.maximumDrawdown
               , data.minimumDrawdown
               , data.maximumDrawdown * 100 AS maximumDrawdownPercentage
               , data.minimumDrawdown * 100 AS minimumDrawdownPercentage
          FROM   stage4 AS data 
          where data.vehicleShareClassGroup_fk=22370

Best Answer

While waiting for support for window syntax like RANGE BETWEEN INTERVAL 3 YEARS PRECEDING AND CURRENT ROW, one approach would be to expand the three year window for each source row explicitly:

SELECT
    SCD.valueDate,
    SCD.currency_fk,
    SCD.vehicleShareClassGroup_fk,
    Q3.Volatility,
    Q3.Drawdown,
    DrawdownPercentage = Q3.Drawdown * 100,
    Q3.MaxDrawdown,
    Q3.MinDrawdown,
    MaxDrawdownPercentage = Q3.MaxDrawdown * 100,
    MinDrawdownPercentage = Q3.MinDrawdown * 100
FROM dbo.ShareClassData2 AS SCD
CROSS APPLY
(
    -- Aggregates
    SELECT
        Drawdown = (SCD.NAVLocal / MAX(Q2.PrevNAVLocal)) - 1,
        MaxDrawdown = MIN(Q2.Drawdown),
        MinDrawdown = MAX(Q2.Drawdown),
        Volatility = STDEV(Q2.LogCalc) * SQRT(COUNT_BIG(Q2.NAVLocal))
    FROM 
    (
        -- Calculations
        SELECT
            Q1.NAVLocal,
            Q1.PrevNAVLocal,
            Drawdown = Q1.NAVLocal / MAX(Q1.PrevNAVLocal) OVER (
                ORDER BY Q1.valueDate 
                ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -1,
            LogCalc = ISNULL(LOG(Q1.PrevNAVLocal / Q1.NAVLocal), 0)
        FROM 
        (
            -- Data range for the current row
            SELECT
                SCD3.valueDate,
                SCD3.NAVLocal,
                PrevNAVLocal = LAG(SCD3.NAVLocal, 1) OVER (ORDER BY SCD3.valueDate)
            FROM dbo.ShareClassData2 AS SCD3
            WHERE
                SCD3.currency_fk = SCD.currency_fk
                AND SCD3.vehicleShareClassGroup_fk = SCD.vehicleShareClassGroup_fk
                AND SCD3.valueDate BETWEEN DATEADD(YEAR, -3, SCD.valueDate) AND SCD.valueDate
                AND SCD3.NAVLocal IS NOT NULL
                AND SCD3.NAVLocal <> 0
        ) AS Q1
    ) AS Q2
) AS Q3;

Performance may or may not be acceptable. The following index should help:

CREATE INDEX i 
ON dbo.ShareClassData2 
(
    currency_fk, 
    vehicleShareClassGroup_fk, 
    valueDate
) 
INCLUDE 
(
    NAVLocal
) 
WHERE NAVLocal <> 0;

Execution plan

It might also be possible to adapt some of the techniques (including a SQLCLR function) described in the related Q & A Date range rolling sum using window functions.

Related Question