MovingSHARPE

Updated 2024-03-14 15:25:40.757000

Syntax

SELECT [westclintech].[wct].[MovingSHARPE](
  <@R, float,>
 ,<@Rf, float,>
 ,<@Scale, float,>
 ,<@Prices, bit,>
 ,<@Offset, int,>
 ,<@RowNum, int,>
 ,<@Id, tinyint,>
 ,<@Exact, bit,>)

Description

Use the scalar function MovingSHARPE to calculate the Sharpe ratio from column values in an ordered resultant table without the need to a self-join. The Sharpe ratio is calculated for each value from the first value in the window to the last value in the window. If the column values are presented to the function out of order, an error message will be generated.

The Sharpe ratio is calculated using Sharpe’s 1994 revision of his original formula and is calculated as the mean difference of the returns and risk-free rate divided by the standard deviation of the differences multiplied by the square root of a scaling factor. For daily returns the scale factor might be 252; for weekly returns 52; for monthly returns 12. For the sake of consistency, the risk-free rate should be in the same units as the scaling factor. The standard deviation is the sample standard deviation.

SHARPE=\ \frac{\bar{R}-\ \bar{R_f}}{\sigma_{R-R_f}}\ast\ \sqrt{scale}

Arguments

@Id

a unique identifier for the MovingSHARPE calculation. @Id allows you to specify multiple moving Sharpe ratio calculations within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.

@Offset

specifies the window size. @Offset is an expression of type int or of a type that can be implicitly converted to int.

@Prices

a bit value identifying whether the supplied @R values are prices (or portfolio values) or returns. If @Prices is true, then the return is calculated. @Prices is an expression of type bit or of a type that can be implicitly converted to bit.

@R

the return or price value; if return values are being supplied, it should the percentage return in floating point format (i.e. 10% = 0. 1). @R is an expression of type float or of a type that can be implicitly converted to float.

@Exact

a bit value which tells the function whether or not to return a NULL value if the number of rows in the window is smaller the @Offset value. If @Exact is 'True' and the number of rows in the window is less the @Offset then a NULL is returned. @Exact is an expression of type bit or of a type that can be implicitly converted to bit.

@Rf

the risk-free rate. @Rf is an expression of type float or of a type that can be implicitly converted to float.

@Scale

the scaling factor used in the calculation. @Scale is an expression of type float or of a type that can be implicitly converted to float.

@RowNum

the number of the row within the group for which the Sharpe ratio is being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.

Return Type

float

Remarks

If @Scale IS NULL then @Scale is set to 12.

If @Prices IS NULL then @Prices is set to 'False'.

Examples

In this example we have 60 months’ worth of return data for and we want to calculate the SHARPE ratio with a window size of 36 rows.

SELECT cast(eom as date) as eom,

       CAST(r as float) as r,

       CAST(rf as float) as rf

INTO #s

FROM

(

    VALUES

        ('2013-12-31', 0.006141, 0.000721),

        ('2013-11-30', 0.004506, 0.000794),

        ('2013-10-31', -0.011454, 0.00082),

        ('2013-09-30', -0.001549, 0.000774),

        ('2013-08-31', 0.00568, 0.000869),

        ('2013-07-31', 0.000241, 0.00087),

        ('2013-06-30', 0.00648, 0.000845),

        ('2013-05-31', 0.005113, 0.000796),

        ('2013-04-30', 0.002461, 0.000832),

        ('2013-03-31', -0.012148, 0.00087),

        ('2013-02-28', -0.005735, 0.000826),

        ('2013-01-31', 0.007749, 0.000806),

        ('2012-12-31', -0.001317, 0.000815),

        ('2012-11-30', -0.000728, 0.000845),

        ('2012-10-31', 0.009667, 0.000775),

        ('2012-09-30', 0.017884, 0.000859),

        ('2012-08-31', 0.011488, 0.000833),

        ('2012-07-31', -0.001725, 0.000813),

        ('2012-06-30', 0.001178, 0.000792),

        ('2012-05-31', -0.000403, 0.00076),

        ('2012-04-30', 0.004734, 0.000849),

        ('2012-03-31', 0.018142, 0.000753),

        ('2012-02-29', 0.0099, 0.000788),

        ('2012-01-31', 0.012923, 0.000801),

        ('2011-12-31', 0.01715, 0.000793),

        ('2011-11-30', -0.005677, 0.000783),

        ('2011-10-31', 0.004575, 0.000764),

        ('2011-09-30', 0.005032, 0.000821),

        ('2011-08-31', 0.015355, 0.000762),

        ('2011-07-31', 0.002744, 0.000737),

        ('2011-06-30', 0.004112, 0.000794),

        ('2011-05-31', 0.007087, 0.000833),

        ('2011-04-30', 0.001757, 0.000818),

        ('2011-03-31', 0.002962, 0.000778),

        ('2011-02-28', 0.001222, 0.000782),

        ('2011-01-31', 0.008292, 0.00078),

        ('2010-12-31', 0.007464, 0.000738),

        ('2010-11-30', 0.009659, 0.00084),

        ('2010-10-31', 0.007148, 0.000693),

        ('2010-09-30', 0.015428, 0.000777),

        ('2010-08-31', 0.006701, 0.000799),

        ('2010-07-31', 0.013108, 0.000785),

        ('2010-06-30', 0.003393, 0.000856),

        ('2010-05-31', 0.009797, 0.000731),

        ('2010-04-30', 0.01416, 0.000718),

        ('2010-03-31', 0.004966, 0.000854),

        ('2010-02-28', -0.001553, 0.000752),

        ('2010-01-31', 0.01009, 0.000804),

        ('2009-12-31', 0.006488, 0.000721),

        ('2009-11-30', 0.001107, 0.00092),

        ('2009-10-31', 0.009641, 0.000774),

        ('2009-09-30', 0.001297, 0.000846),

        ('2009-08-31', 0.001314, 0.000767),

        ('2009-07-31', 0.008622, 0.000815),

        ('2009-06-30', 0.015167, 0.000721),

        ('2009-05-31', -0.002137, 0.0008),

        ('2009-04-30', 0.016035, 0.000798),

        ('2009-03-31', 0.018067, 0.00077),

        ('2009-02-28', 0.013449, 0.000823),

        ('2009-01-31', -0.000029, 0.000852)

) n (eom, r, rf);

SELECT eom,

       r,

       rf,

       wct.MovingSharpe(   r,                                    --@R

                           rf,                                   --@Rf

                           12,                                   --@Scale

                           'False',                              --@Prices

                           36,                                   --@Offset

                           ROW_NUMBER() OVER (ORDER BY eom ASC), --@RowNum

                           NULL,                                 --@Id

                           'True'                                --@Exact

                       ) as SHARPE

FROM #s;

DROP TABLE #s;