MovingVOLATILITY
Updated: 31 Dec 2013
Use MovingVOLATILITY to calculate the historical volatility based upon price or valuation data from column values in an ordered resultant table, without the need for a self-join. The volatility 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 functions out of order, an error message will be generated.
The historic volatility is calculated as the sample standard deviation of the natural logarithm of the returns multiplied by the square of the scaling factor supplied to the function.
Syntax
SELECT [wctWindowing].[wct].[MovingVOLATILITY](
<@Price, float,>
,<@Scale, float,>
,<@Offset, int,>
,<@RowNum, int,>
,<@Id, tinyint,>
,<@Exact, bit,>)
Arguments
@Price
the price passed into the function. Generally, price is the end-of-day price for the security, commodity, currency, or index for which the volatility is being calculated. @Price 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.
@Offset
specifies the window size. The window size (or the number of rows included in the result) is the current row plus the @Offset. Since the volatility calculation is based in returns, the window-size needs to account for the initial price; in other words, 30 days of returns requires 31 days of prices. @Offset is an expression of type int or of a type that can be implicitly converted to int.
@RowNum
the number of the row within the group for which the sum 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.
@Id
a unique identifier for the MovingVOLATILITY calculation. @Id allows you to specify multiple moving sums within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.
@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.
Returns
float
Remarks
· If @Id is NULL then @Id = 0.
· To calculate the running volatility from the beginning of a dataset or partition, use the RunningVOLATILITY function.
· If @RowNum is equal to 1, MovingVOLATILITY is equal to NULL.
· @RowNum must be in ascending order.
· There may be cases where the order in which the data are returned to the function and the order in which the results are returned are different, generally due to parallelism. You can use OPTION(MAXDOP 1) or OPTION(MAXDOP 1,FORCE ORDER) to help eliminate this problem.
Example
In this example we calculate the 30-day moving volatility.
SELECT
ticker
,tdate
,CAST(price as money) as price
,wct.MovingVOLATILITY(
price, --@Price
252, --@Scale
30, --@Offset
ROW_NUMBER() OVER (ORDER BY tdate), --@RowNum
NULL, --@Id
'True' --@Exact
) as VOL
FROM
(VALUES
('IBM','2013-12-27',185.08)
,('IBM','2013-12-26',185.35)
,('IBM','2013-12-24',183.22)
,('IBM','2013-12-23',182.23)
,('IBM','2013-12-20',180.02)
,('IBM','2013-12-19',180.22)
,('IBM','2013-12-18',178.7)
,('IBM','2013-12-17',175.76)
,('IBM','2013-12-16',177.85)
,('IBM','2013-12-13',172.8)
,('IBM','2013-12-12',173.37)
,('IBM','2013-12-11',175.2)
,('IBM','2013-12-10',177.12)
,('IBM','2013-12-09',177.46)
,('IBM','2013-12-06',177.67)
,('IBM','2013-12-05',176.08)
,('IBM','2013-12-04',175.74)
,('IBM','2013-12-03',176.08)
,('IBM','2013-12-02',177.48)
,('IBM','2013-11-29',179.68)
,('IBM','2013-11-27',178.97)
,('IBM','2013-11-26',177.31)
,('IBM','2013-11-25',178.94)
,('IBM','2013-11-22',181.3)
,('IBM','2013-11-21',184.13)
,('IBM','2013-11-20',185.19)
,('IBM','2013-11-19',185.25)
,('IBM','2013-11-18',184.47)
,('IBM','2013-11-15',183.19)
,('IBM','2013-11-14',182.21)
,('IBM','2013-11-13',183.55)
,('IBM','2013-11-12',183.07)
,('IBM','2013-11-11',182.88)
,('IBM','2013-11-08',179.99)
,('IBM','2013-11-07',180)
,('IBM','2013-11-06',179.19)
,('IBM','2013-11-05',176.9)
,('IBM','2013-11-04',179.31)
,('IBM','2013-11-01',178.27)
,('IBM','2013-10-31',178.25)
,('IBM','2013-10-30',179.19)
,('IBM','2013-10-29',181.15)
,('IBM','2013-10-28',176.4)
,('IBM','2013-10-25',175.91)
,('IBM','2013-10-24',176.85)
,('IBM','2013-10-23',174.83)
,('IBM','2013-10-22',174.04)
,('IBM','2013-10-21',171.94)
,('IBM','2013-10-18',172.85)
,('IBM','2013-10-17',173.9)
,('IBM','2013-10-16',185.73)
,('IBM','2013-10-15',183.67)
,('IBM','2013-10-14',185.97)
,('IBM','2013-10-11',185.17)
,('IBM','2013-10-10',183.78)
,('IBM','2013-10-09',180.35)
,('IBM','2013-10-08',177.77)
,('IBM','2013-10-07',181.04)
,('IBM','2013-10-04',183.12)
,('IBM','2013-10-03',182.88)
,('IBM','2013-10-02',183.97)
,('IBM','2013-10-01',185.38)
,('IBM','2013-09-30',184.19)
)n(ticker,tdate,price)
This produces the following result.
ticker tdate price VOL
------ ---------- --------------------- ----------------------
IBM 2013-09-30 184.19 NULL
IBM 2013-10-01 185.38 NULL
IBM 2013-10-02 183.97 NULL
IBM 2013-10-03 182.88 NULL
IBM 2013-10-04 183.12 NULL
IBM 2013-10-07 181.04 NULL
IBM 2013-10-08 177.77 NULL
IBM 2013-10-09 180.35 NULL
IBM 2013-10-10 183.78 NULL
IBM 2013-10-11 185.17 NULL
IBM 2013-10-14 185.97 NULL
IBM 2013-10-15 183.67 NULL
IBM 2013-10-16 185.73 NULL
IBM 2013-10-17 173.90 NULL
IBM 2013-10-18 172.85 NULL
IBM 2013-10-21 171.94 NULL
IBM 2013-10-22 174.04 NULL
IBM 2013-10-23 174.83 NULL
IBM 2013-10-24 176.85 NULL
IBM 2013-10-25 175.91 NULL
IBM 2013-10-28 176.40 NULL
IBM 2013-10-29 181.15 NULL
IBM 2013-10-30 179.19 NULL
IBM 2013-10-31 178.25 NULL
IBM 2013-11-01 178.27 NULL
IBM 2013-11-04 179.31 NULL
IBM 2013-11-05 176.90 NULL
IBM 2013-11-06 179.19 NULL
IBM 2013-11-07 180.00 NULL
IBM 2013-11-08 179.99 NULL
IBM 2013-11-11 182.88 0.259600756641362
IBM 2013-11-12 183.07 0.25886435068979
IBM 2013-11-13 183.55 0.258084881217436
IBM 2013-11-14 182.21 0.25838941695409
IBM 2013-11-15 183.19 0.258851361662631
IBM 2013-11-18 184.47 0.257272874238383
IBM 2013-11-19 185.25 0.25113141270427
IBM 2013-11-20 185.19 0.248098166203494
IBM 2013-11-21 184.13 0.242810179393185
IBM 2013-11-22 181.30 0.245804723406176
IBM 2013-11-25 178.94 0.247889849528801
IBM 2013-11-26 177.31 0.246782618938391
IBM 2013-11-27 178.97 0.24604234701296
IBM 2013-11-29 179.68 0.152062274334431
IBM 2013-12-02 177.48 0.155660954549234
IBM 2013-12-03 176.08 0.156753405261162
IBM 2013-12-04 175.74 0.153166929494441
IBM 2013-12-05 176.08 0.152731802077581
IBM 2013-12-06 177.67 0.151297530686739
IBM 2013-12-09 177.46 0.15046658789529
IBM 2013-12-10 177.12 0.15040745529138
IBM 2013-12-11 175.20 0.131153490377389
IBM 2013-12-12 173.37 0.13091290987267
IBM 2013-12-13 172.80 0.130493430608268
IBM 2013-12-16 177.85 0.15657740328222
IBM 2013-12-17 175.76 0.159130504552285
IBM 2013-12-18 178.70 0.161891633259364
IBM 2013-12-19 180.22 0.159420520640619
IBM 2013-12-20 180.02 0.158928812684109
IBM 2013-12-23 182.23 0.162812027777703
IBM 2013-12-24 183.22 0.156848086287117
IBM 2013-12-26 185.35 0.160341722727633
IBM 2013-12-27 185.08 0.160289637318093
In this example, we calculate the 30-day moving volatility for multiple securities.
SELECT
ticker
,tdate
,CAST(price as money) as price
,wct.MovingVOLATILITY(
price, --@Price
252, --@Scale
30, --@Offset
ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY ticker, tdate), --@RowNum
NULL, --@Id
'True' --@Exact
) as VOL
FROM
(VALUES
('IBM','2013-12-27',185.08)
,('IBM','2013-12-26',185.35)
,('IBM','2013-12-24',183.22)
,('IBM','2013-12-23',182.23)
,('IBM','2013-12-20',180.02)
,('IBM','2013-12-19',180.22)
,('IBM','2013-12-18',178.7)
,('IBM','2013-12-17',175.76)
,('IBM','2013-12-16',177.85)
,('IBM','2013-12-13',172.8)
,('IBM','2013-12-12',173.37)
,('IBM','2013-12-11',175.2)
,('IBM','2013-12-10',177.12)
,('IBM','2013-12-09',177.46)
,('IBM','2013-12-06',177.67)
,('IBM','2013-12-05',176.08)
,('IBM','2013-12-04',175.74)
,('IBM','2013-12-03',176.08)
,('IBM','2013-12-02',177.48)
,('IBM','2013-11-29',179.68)
,('IBM','2013-11-27',178.97)
,('IBM','2013-11-26',177.31)
,('IBM','2013-11-25',178.94)
,('IBM','2013-11-22',181.3)
,('IBM','2013-11-21',184.13)
,('IBM','2013-11-20',185.19)
,('IBM','2013-11-19',185.25)
,('IBM','2013-11-18',184.47)
,('IBM','2013-11-15',183.19)
,('IBM','2013-11-14',182.21)
,('IBM','2013-11-13',183.55)
,('IBM','2013-11-12',183.07)
,('IBM','2013-11-11',182.88)
,('IBM','2013-11-08',179.99)
,('IBM','2013-11-07',180)
,('IBM','2013-11-06',179.19)
,('IBM','2013-11-05',176.9)
,('IBM','2013-11-04',179.31)
,('IBM','2013-11-01',178.27)
,('IBM','2013-10-31',178.25)
,('IBM','2013-10-30',179.19)
,('IBM','2013-10-29',181.15)
,('IBM','2013-10-28',176.4)
,('IBM','2013-10-25',175.91)
,('IBM','2013-10-24',176.85)
,('IBM','2013-10-23',174.83)
,('IBM','2013-10-22',174.04)
,('IBM','2013-10-21',171.94)
,('IBM','2013-10-18',172.85)
,('IBM','2013-10-17',173.9)
,('IBM','2013-10-16',185.73)
,('IBM','2013-10-15',183.67)
,('IBM','2013-10-14',185.97)
,('IBM','2013-10-11',185.17)
,('IBM','2013-10-10',183.78)
,('IBM','2013-10-09',180.35)
,('IBM','2013-10-08',177.77)
,('IBM','2013-10-07',181.04)
,('IBM','2013-10-04',183.12)
,('IBM','2013-10-03',182.88)
,('IBM','2013-10-02',183.97)
,('IBM','2013-10-01',185.38)
,('IBM','2013-09-30',184.19)
,('FB','2013-12-27',55.44)
,('FB','2013-12-26',57.73)
,('FB','2013-12-24',57.96)
,('FB','2013-12-23',57.77)
,('FB','2013-12-20',55.12)
,('FB','2013-12-19',55.05)
,('FB','2013-12-18',55.57)
,('FB','2013-12-17',54.86)
,('FB','2013-12-16',53.81)
,('FB','2013-12-13',53.32)
,('FB','2013-12-12',51.83)
,('FB','2013-12-11',49.38)
,('FB','2013-12-10',50.25)
,('FB','2013-12-09',48.84)
,('FB','2013-12-06',47.94)
,('FB','2013-12-05',48.34)
,('FB','2013-12-04',48.62)
,('FB','2013-12-03',46.73)
,('FB','2013-12-02',47.06)
,('FB','2013-11-29',47.01)
,('FB','2013-11-27',46.49)
,('FB','2013-11-26',45.89)
,('FB','2013-11-25',44.82)
,('FB','2013-11-22',46.23)
,('FB','2013-11-21',46.7)
,('FB','2013-11-20',46.43)
,('FB','2013-11-19',46.36)
,('FB','2013-11-18',45.83)
,('FB','2013-11-15',49.01)
,('FB','2013-11-14',48.99)
,('FB','2013-11-13',48.71)
,('FB','2013-11-12',46.61)
,('FB','2013-11-11',46.2)
,('FB','2013-11-08',47.53)
,('FB','2013-11-07',47.56)
,('FB','2013-11-06',49.12)
,('FB','2013-11-05',50.11)
,('FB','2013-11-04',48.22)
,('FB','2013-11-01',49.75)
,('FB','2013-10-31',50.21)
,('FB','2013-10-30',49.01)
,('FB','2013-10-29',49.4)
,('FB','2013-10-28',50.23)
,('FB','2013-10-25',51.95)
,('FB','2013-10-24',52.45)
,('FB','2013-10-23',51.9)
,('FB','2013-10-22',52.68)
,('FB','2013-10-21',53.85)
,('FB','2013-10-18',54.22)
,('FB','2013-10-17',52.21)
,('FB','2013-10-16',51.14)
,('FB','2013-10-15',49.5)
,('FB','2013-10-14',49.51)
,('FB','2013-10-11',49.11)
,('FB','2013-10-10',49.05)
,('FB','2013-10-09',46.77)
,('FB','2013-10-08',47.14)
,('FB','2013-10-07',50.52)
,('FB','2013-10-04',51.04)
,('FB','2013-10-03',49.18)
,('FB','2013-10-02',50.28)
,('FB','2013-10-01',50.42)
,('FB','2013-09-30',50.23)
,('ORCL','2013-12-27',37.98)
,('ORCL','2013-12-26',37.69)
,('ORCL','2013-12-24',37.32)
,('ORCL','2013-12-23',36.93)
,('ORCL','2013-12-20',36.37)
,('ORCL','2013-12-19',36.6)
,('ORCL','2013-12-18',34.6)
,('ORCL','2013-12-17',33.63)
,('ORCL','2013-12-16',33.54)
,('ORCL','2013-12-13',33.23)
,('ORCL','2013-12-12',33.6)
,('ORCL','2013-12-11',34.56)
,('ORCL','2013-12-10',34.8)
,('ORCL','2013-12-09',35.6)
,('ORCL','2013-12-06',35.48)
,('ORCL','2013-12-05',34.85)
,('ORCL','2013-12-04',35.07)
,('ORCL','2013-12-03',35.07)
,('ORCL','2013-12-02',35.08)
,('ORCL','2013-11-29',35.29)
,('ORCL','2013-11-27',35.29)
,('ORCL','2013-11-26',34.93)
,('ORCL','2013-11-25',34.78)
,('ORCL','2013-11-22',34.83)
,('ORCL','2013-11-21',34.94)
,('ORCL','2013-11-20',34.75)
,('ORCL','2013-11-19',34.76)
,('ORCL','2013-11-18',34.93)
,('ORCL','2013-11-15',34.92)
,('ORCL','2013-11-14',34.38)
,('ORCL','2013-11-13',35)
,('ORCL','2013-11-12',34.7)
,('ORCL','2013-11-11',34.37)
,('ORCL','2013-11-08',34.35)
,('ORCL','2013-11-07',34)
,('ORCL','2013-11-06',34.07)
,('ORCL','2013-11-05',33.5)
,('ORCL','2013-11-04',33.71)
,('ORCL','2013-11-01',33.53)
,('ORCL','2013-10-31',33.5)
,('ORCL','2013-10-30',33.53)
,('ORCL','2013-10-29',33.71)
,('ORCL','2013-10-28',33.14)
,('ORCL','2013-10-25',33.15)
,('ORCL','2013-10-24',33.07)
,('ORCL','2013-10-23',32.7)
,('ORCL','2013-10-22',32.9)
,('ORCL','2013-10-21',32.95)
,('ORCL','2013-10-18',32.9)
,('ORCL','2013-10-17',32.87)
,('ORCL','2013-10-16',33.02)
,('ORCL','2013-10-15',32.75)
,('ORCL','2013-10-14',33.28)
,('ORCL','2013-10-11',33.26)
,('ORCL','2013-10-10',32.99)
,('ORCL','2013-10-09',32.19)
,('ORCL','2013-10-08',32.37)
,('ORCL','2013-10-07',32.84)
,('ORCL','2013-10-04',33.21)
,('ORCL','2013-10-03',33.12)
,('ORCL','2013-10-02',33.56)
,('ORCL','2013-10-01',33.38)
,('ORCL','2013-09-30',33.05)
,('MSFT','2013-12-27',37.29)
,('MSFT','2013-12-26',37.44)
,('MSFT','2013-12-24',37.08)
,('MSFT','2013-12-23',36.62)
,('MSFT','2013-12-20',36.8)
,('MSFT','2013-12-19',36.25)
,('MSFT','2013-12-18',36.58)
,('MSFT','2013-12-17',36.52)
,('MSFT','2013-12-16',36.89)
,('MSFT','2013-12-13',36.69)
,('MSFT','2013-12-12',37.22)
,('MSFT','2013-12-11',37.61)
,('MSFT','2013-12-10',38.11)
,('MSFT','2013-12-09',38.71)
,('MSFT','2013-12-06',38.36)
,('MSFT','2013-12-05',38)
,('MSFT','2013-12-04',38.94)
,('MSFT','2013-12-03',38.31)
,('MSFT','2013-12-02',38.45)
,('MSFT','2013-11-29',38.13)
,('MSFT','2013-11-27',37.6)
,('MSFT','2013-11-26',37.35)
,('MSFT','2013-11-25',37.64)
,('MSFT','2013-11-22',37.57)
,('MSFT','2013-11-21',37.4)
,('MSFT','2013-11-20',37.08)
,('MSFT','2013-11-19',36.74)
,('MSFT','2013-11-18',36.92)
,('MSFT','2013-11-15',37.56)
,('MSFT','2013-11-14',37.73)
,('MSFT','2013-11-13',37.87)
,('MSFT','2013-11-12',37.08)
,('MSFT','2013-11-11',37.31)
,('MSFT','2013-11-08',37.5)
,('MSFT','2013-11-07',37.22)
,('MSFT','2013-11-06',37.89)
,('MSFT','2013-11-05',36.36)
,('MSFT','2013-11-04',35.67)
,('MSFT','2013-11-01',35.26)
,('MSFT','2013-10-31',35.14)
,('MSFT','2013-10-30',35.27)
,('MSFT','2013-10-29',35.25)
,('MSFT','2013-10-28',35.3)
,('MSFT','2013-10-25',35.46)
,('MSFT','2013-10-24',33.47)
,('MSFT','2013-10-23',33.51)
,('MSFT','2013-10-22',34.32)
,('MSFT','2013-10-21',34.73)
,('MSFT','2013-10-18',34.7)
,('MSFT','2013-10-17',34.66)
,('MSFT','2013-10-16',34.38)
,('MSFT','2013-10-15',34.23)
,('MSFT','2013-10-14',34.19)
,('MSFT','2013-10-11',33.87)
,('MSFT','2013-10-10',33.51)
,('MSFT','2013-10-09',32.82)
,('MSFT','2013-10-08',32.76)
,('MSFT','2013-10-07',33.05)
,('MSFT','2013-10-04',33.62)
,('MSFT','2013-10-03',33.61)
,('MSFT','2013-10-02',33.66)
,('MSFT','2013-10-01',33.33)
,('MSFT','2013-09-30',33.03)
,('AAPL','2013-12-27',560.09)
,('AAPL','2013-12-26',563.9)
,('AAPL','2013-12-24',567.67)
,('AAPL','2013-12-23',570.09)
,('AAPL','2013-12-20',549.02)
,('AAPL','2013-12-19',544.46)
,('AAPL','2013-12-18',550.77)
,('AAPL','2013-12-17',554.99)
,('AAPL','2013-12-16',557.5)
,('AAPL','2013-12-13',554.43)
,('AAPL','2013-12-12',560.54)
,('AAPL','2013-12-11',561.36)
,('AAPL','2013-12-10',565.55)
,('AAPL','2013-12-09',566.43)
,('AAPL','2013-12-06',560.02)
,('AAPL','2013-12-05',567.9)
,('AAPL','2013-12-04',565)
,('AAPL','2013-12-03',566.32)
,('AAPL','2013-12-02',551.23)
,('AAPL','2013-11-29',556.07)
,('AAPL','2013-11-27',545.96)
,('AAPL','2013-11-26',533.4)
,('AAPL','2013-11-25',523.74)
,('AAPL','2013-11-22',519.8)
,('AAPL','2013-11-21',521.14)
,('AAPL','2013-11-20',515)
,('AAPL','2013-11-19',519.55)
,('AAPL','2013-11-18',518.63)
,('AAPL','2013-11-15',524.99)
,('AAPL','2013-11-14',528.16)
,('AAPL','2013-11-13',520.63)
,('AAPL','2013-11-12',520.01)
,('AAPL','2013-11-11',519.05)
,('AAPL','2013-11-08',520.56)
,('AAPL','2013-11-07',512.49)
,('AAPL','2013-11-06',520.92)
,('AAPL','2013-11-05',522.4)
,('AAPL','2013-11-04',523.69)
,('AAPL','2013-11-01',517.01)
,('AAPL','2013-10-31',519.67)
,('AAPL','2013-10-30',521.85)
,('AAPL','2013-10-29',513.68)
,('AAPL','2013-10-28',526.8)
,('AAPL','2013-10-25',522.91)
,('AAPL','2013-10-24',528.82)
,('AAPL','2013-10-23',521.91)
,('AAPL','2013-10-22',516.85)
,('AAPL','2013-10-21',518.33)
,('AAPL','2013-10-18',505.94)
,('AAPL','2013-10-17',501.57)
,('AAPL','2013-10-16',498.2)
,('AAPL','2013-10-15',495.79)
,('AAPL','2013-10-14',493.16)
,('AAPL','2013-10-11',489.95)
,('AAPL','2013-10-10',486.8)
,('AAPL','2013-10-09',483.77)
,('AAPL','2013-10-08',478.15)
,('AAPL','2013-10-07',484.92)
,('AAPL','2013-10-04',480.23)
,('AAPL','2013-10-03',480.6)
,('AAPL','2013-10-02',486.72)
,('AAPL','2013-10-01',485.13)
,('AAPL','2013-09-30',473.98)
)n(ticker,tdate,price)
This produces the following result.
ticker tdate price VOL
------ ---------- --------------------- ----------------------
AAPL 2013-09-30 473.98 NULL
AAPL 2013-10-01 485.13 NULL
AAPL 2013-10-02 486.72 NULL
AAPL 2013-10-03 480.60 NULL
AAPL 2013-10-04 480.23 NULL
AAPL 2013-10-07 484.92 NULL
AAPL 2013-10-08 478.15 NULL
AAPL 2013-10-09 483.77 NULL
AAPL 2013-10-10 486.80 NULL
AAPL 2013-10-11 489.95 NULL
AAPL 2013-10-14 493.16 NULL
AAPL 2013-10-15 495.79 NULL
AAPL 2013-10-16 498.20 NULL
AAPL 2013-10-17 501.57 NULL
AAPL 2013-10-18 505.94 NULL
AAPL 2013-10-21 518.33 NULL
AAPL 2013-10-22 516.85 NULL
AAPL 2013-10-23 521.91 NULL
AAPL 2013-10-24 528.82 NULL
AAPL 2013-10-25 522.91 NULL
AAPL 2013-10-28 526.80 NULL
AAPL 2013-10-29 513.68 NULL
AAPL 2013-10-30 521.85 NULL
AAPL 2013-10-31 519.67 NULL
AAPL 2013-11-01 517.01 NULL
AAPL 2013-11-04 523.69 NULL
AAPL 2013-11-05 522.40 NULL
AAPL 2013-11-06 520.92 NULL
AAPL 2013-11-07 512.49 NULL
AAPL 2013-11-08 520.56 NULL
AAPL 2013-11-11 519.05 0.182448898566812
AAPL 2013-11-12 520.01 0.172083895254781
AAPL 2013-11-13 520.63 0.172088922541504
AAPL 2013-11-14 528.16 0.169557381867762
AAPL 2013-11-15 524.99 0.171284793834041
AAPL 2013-11-18 518.63 0.175500927555936
AAPL 2013-11-19 519.55 0.168585936287561
AAPL 2013-11-20 515.00 0.169619590467246
AAPL 2013-11-21 521.14 0.17158174055262
AAPL 2013-11-22 519.80 0.171665850721973
AAPL 2013-11-25 523.74 0.171926757898195
AAPL 2013-11-26 533.40 0.178087844824715
AAPL 2013-11-27 545.96 0.187987250653775
AAPL 2013-11-29 556.07 0.19291247343686
AAPL 2013-12-02 551.23 0.195392658373822
AAPL 2013-12-03 566.32 0.198211747949849
AAPL 2013-12-04 565.00 0.19808360298352
AAPL 2013-12-05 567.90 0.19716095745469
AAPL 2013-12-06 560.02 0.200448611372548
AAPL 2013-12-09 566.43 0.198263046444197
AAPL 2013-12-10 565.55 0.198100660818933
AAPL 2013-12-11 561.36 0.182686310947391
AAPL 2013-12-12 560.54 0.178968180571978
AAPL 2013-12-13 554.43 0.182177169130701
AAPL 2013-12-16 557.50 0.181086060938817
AAPL 2013-12-17 554.99 0.179464900321255
AAPL 2013-12-18 550.77 0.181182396659916
AAPL 2013-12-19 544.46 0.184811075666558
AAPL 2013-12-20 549.02 0.177873957920341
AAPL 2013-12-23 570.09 0.202045192617558
AAPL 2013-12-24 567.67 0.202426988470211
AAPL 2013-12-26 563.90 0.204336339708499
AAPL 2013-12-27 560.09 0.206145940389823
FB 2013-09-30 50.23 NULL
FB 2013-10-01 50.42 NULL
FB 2013-10-02 50.28 NULL
FB 2013-10-03 49.18 NULL
FB 2013-10-04 51.04 NULL
FB 2013-10-07 50.52 NULL
FB 2013-10-08 47.14 NULL
FB 2013-10-09 46.77 NULL
FB 2013-10-10 49.05 NULL
FB 2013-10-11 49.11 NULL
FB 2013-10-14 49.51 NULL
FB 2013-10-15 49.50 NULL
FB 2013-10-16 51.14 NULL
FB 2013-10-17 52.21 NULL
FB 2013-10-18 54.22 NULL
FB 2013-10-21 53.85 NULL
FB 2013-10-22 52.68 NULL
FB 2013-10-23 51.90 NULL
FB 2013-10-24 52.45 NULL
FB 2013-10-25 51.95 NULL
FB 2013-10-28 50.23 NULL
FB 2013-10-29 49.40 NULL
FB 2013-10-30 49.01 NULL
FB 2013-10-31 50.21 NULL
FB 2013-11-01 49.75 NULL
FB 2013-11-04 48.22 NULL
FB 2013-11-05 50.11 NULL
FB 2013-11-06 49.12 NULL
FB 2013-11-07 47.56 NULL
FB 2013-11-08 47.53 NULL
FB 2013-11-11 46.20 0.413408370202952
FB 2013-11-12 46.61 0.414365401035481
FB 2013-11-13 48.71 0.435894949700045
FB 2013-11-14 48.99 0.431653934954346
FB 2013-11-15 49.01 0.416987639366383
FB 2013-11-18 45.83 0.458041915373724
FB 2013-11-19 46.36 0.414670400399728
FB 2013-11-20 46.43 0.414121926202439
FB 2013-11-21 46.70 0.389126146601151
FB 2013-11-22 46.23 0.389789146687931
FB 2013-11-25 44.82 0.397353542073473
FB 2013-11-26 45.89 0.404887818224236
FB 2013-11-27 46.49 0.393950906964857
FB 2013-11-29 47.01 0.389859680209728
FB 2013-12-02 47.06 0.370105984913441
FB 2013-12-03 46.73 0.370115858530058
FB 2013-12-04 48.62 0.387834189850461
FB 2013-12-05 48.34 0.386228074606856
FB 2013-12-06 47.94 0.384613467723489
FB 2013-12-09 48.84 0.389068143912512
FB 2013-12-10 50.25 0.386866728808467
FB 2013-12-11 49.38 0.387174385541323
FB 2013-12-12 51.83 0.410886254243779
FB 2013-12-13 53.32 0.413017561316782
FB 2013-12-16 53.81 0.412114218265737
FB 2013-12-17 54.86 0.40195314573859
FB 2013-12-18 55.57 0.389719506456651
FB 2013-12-19 55.05 0.385389246215378
FB 2013-12-20 55.12 0.370063133368606
FB 2013-12-23 57.77 0.389076800308892
FB 2013-12-24 57.96 0.374973997553309
FB 2013-12-26 57.73 0.376430695610467
FB 2013-12-27 55.44 0.384016812079829
IBM 2013-09-30 184.19 NULL
IBM 2013-10-01 185.38 NULL
IBM 2013-10-02 183.97 NULL
IBM 2013-10-03 182.88 NULL
IBM 2013-10-04 183.12 NULL
IBM 2013-10-07 181.04 NULL
IBM 2013-10-08 177.77 NULL
IBM 2013-10-09 180.35 NULL
IBM 2013-10-10 183.78 NULL
IBM 2013-10-11 185.17 NULL
IBM 2013-10-14 185.97 NULL
IBM 2013-10-15 183.67 NULL
IBM 2013-10-16 185.73 NULL
IBM 2013-10-17 173.90 NULL
IBM 2013-10-18 172.85 NULL
IBM 2013-10-21 171.94 NULL
IBM 2013-10-22 174.04 NULL
IBM 2013-10-23 174.83 NULL
IBM 2013-10-24 176.85 NULL
IBM 2013-10-25 175.91 NULL
IBM 2013-10-28 176.40 NULL
IBM 2013-10-29 181.15 NULL
IBM 2013-10-30 179.19 NULL
IBM 2013-10-31 178.25 NULL
IBM 2013-11-01 178.27 NULL
IBM 2013-11-04 179.31 NULL
IBM 2013-11-05 176.90 NULL
IBM 2013-11-06 179.19 NULL
IBM 2013-11-07 180.00 NULL
IBM 2013-11-08 179.99 NULL
IBM 2013-11-11 182.88 0.259600756641362
IBM 2013-11-12 183.07 0.25886435068979
IBM 2013-11-13 183.55 0.258084881217436
IBM 2013-11-14 182.21 0.25838941695409
IBM 2013-11-15 183.19 0.258851361662631
IBM 2013-11-18 184.47 0.257272874238383
IBM 2013-11-19 185.25 0.25113141270427
IBM 2013-11-20 185.19 0.248098166203494
IBM 2013-11-21 184.13 0.242810179393185
IBM 2013-11-22 181.30 0.245804723406176
IBM 2013-11-25 178.94 0.247889849528801
IBM 2013-11-26 177.31 0.246782618938391
IBM 2013-11-27 178.97 0.24604234701296
IBM 2013-11-29 179.68 0.152062274334431
IBM 2013-12-02 177.48 0.155660954549234
IBM 2013-12-03 176.08 0.156753405261162
IBM 2013-12-04 175.74 0.153166929494441
IBM 2013-12-05 176.08 0.152731802077581
IBM 2013-12-06 177.67 0.151297530686739
IBM 2013-12-09 177.46 0.15046658789529
IBM 2013-12-10 177.12 0.15040745529138
IBM 2013-12-11 175.20 0.131153490377389
IBM 2013-12-12 173.37 0.13091290987267
IBM 2013-12-13 172.80 0.130493430608268
IBM 2013-12-16 177.85 0.15657740328222
IBM 2013-12-17 175.76 0.159130504552285
IBM 2013-12-18 178.70 0.161891633259364
IBM 2013-12-19 180.22 0.159420520640619
IBM 2013-12-20 180.02 0.158928812684109
IBM 2013-12-23 182.23 0.162812027777703
IBM 2013-12-24 183.22 0.156848086287117
IBM 2013-12-26 185.35 0.160341722727633
IBM 2013-12-27 185.08 0.160289637318093
MSFT 2013-09-30 33.03 NULL
MSFT 2013-10-01 33.33 NULL
MSFT 2013-10-02 33.66 NULL
MSFT 2013-10-03 33.61 NULL
MSFT 2013-10-04 33.62 NULL
MSFT 2013-10-07 33.05 NULL
MSFT 2013-10-08 32.76 NULL
MSFT 2013-10-09 32.82 NULL
MSFT 2013-10-10 33.51 NULL
MSFT 2013-10-11 33.87 NULL
MSFT 2013-10-14 34.19 NULL
MSFT 2013-10-15 34.23 NULL
MSFT 2013-10-16 34.38 NULL
MSFT 2013-10-17 34.66 NULL
MSFT 2013-10-18 34.70 NULL
MSFT 2013-10-21 34.73 NULL
MSFT 2013-10-22 34.32 NULL
MSFT 2013-10-23 33.51 NULL
MSFT 2013-10-24 33.47 NULL
MSFT 2013-10-25 35.46 NULL
MSFT 2013-10-28 35.30 NULL
MSFT 2013-10-29 35.25 NULL
MSFT 2013-10-30 35.27 NULL
MSFT 2013-10-31 35.14 NULL
MSFT 2013-11-01 35.26 NULL
MSFT 2013-11-04 35.67 NULL
MSFT 2013-11-05 36.36 NULL
MSFT 2013-11-06 37.89 NULL
MSFT 2013-11-07 37.22 NULL
MSFT 2013-11-08 37.50 NULL
MSFT 2013-11-11 37.31 0.254950380754112
MSFT 2013-11-12 37.08 0.256181821008676
MSFT 2013-11-13 37.87 0.260609811856037
MSFT 2013-11-14 37.73 0.261088903767016
MSFT 2013-11-15 37.56 0.262029725657673
MSFT 2013-11-18 36.92 0.262089613468521
MSFT 2013-11-19 36.74 0.26070530174271
MSFT 2013-11-20 37.08 0.261092730265093
MSFT 2013-11-21 37.40 0.256650902151733
MSFT 2013-11-22 37.57 0.255805679120139
MSFT 2013-11-25 37.64 0.255215135927795
MSFT 2013-11-26 37.35 0.257129548246975
MSFT 2013-11-27 37.60 0.257329564017454
MSFT 2013-11-29 38.13 0.258909236195725
MSFT 2013-12-02 38.45 0.259260740509458
MSFT 2013-12-03 38.31 0.259976144055881
MSFT 2013-12-04 38.94 0.25853796720115
MSFT 2013-12-05 38.00 0.259063106130924
MSFT 2013-12-06 38.36 0.258973608663078
MSFT 2013-12-09 38.71 0.204833360753929
MSFT 2013-12-10 38.11 0.210778618295736
MSFT 2013-12-11 37.61 0.215426672867916
MSFT 2013-12-12 37.22 0.218466590217453
MSFT 2013-12-13 36.69 0.222925338094543
MSFT 2013-12-16 36.89 0.22315833165144
MSFT 2013-12-17 36.52 0.223499790309627
MSFT 2013-12-18 36.58 0.216646945703847
MSFT 2013-12-19 36.25 0.179805278869911
MSFT 2013-12-20 36.80 0.179064974608653
MSFT 2013-12-23 36.62 0.177929915549888
MSFT 2013-12-24 37.08 0.181497152287328
MSFT 2013-12-26 37.44 0.182768111571843
MSFT 2013-12-27 37.29 0.172163656133405
ORCL 2013-09-30 33.05 NULL
ORCL 2013-10-01 33.38 NULL
ORCL 2013-10-02 33.56 NULL
ORCL 2013-10-03 33.12 NULL
ORCL 2013-10-04 33.21 NULL
ORCL 2013-10-07 32.84 NULL
ORCL 2013-10-08 32.37 NULL
ORCL 2013-10-09 32.19 NULL
ORCL 2013-10-10 32.99 NULL
ORCL 2013-10-11 33.26 NULL
ORCL 2013-10-14 33.28 NULL
ORCL 2013-10-15 32.75 NULL
ORCL 2013-10-16 33.02 NULL
ORCL 2013-10-17 32.87 NULL
ORCL 2013-10-18 32.90 NULL
ORCL 2013-10-21 32.95 NULL
ORCL 2013-10-22 32.90 NULL
ORCL 2013-10-23 32.70 NULL
ORCL 2013-10-24 33.07 NULL
ORCL 2013-10-25 33.15 NULL
ORCL 2013-10-28 33.14 NULL
ORCL 2013-10-29 33.71 NULL
ORCL 2013-10-30 33.53 NULL
ORCL 2013-10-31 33.50 NULL
ORCL 2013-11-01 33.53 NULL
ORCL 2013-11-04 33.71 NULL
ORCL 2013-11-05 33.50 NULL
ORCL 2013-11-06 34.07 NULL
ORCL 2013-11-07 34.00 NULL
ORCL 2013-11-08 34.35 NULL
ORCL 2013-11-11 34.37 0.150189552419081
ORCL 2013-11-12 34.70 0.150003854614319
ORCL 2013-11-13 35.00 0.151056836715572
ORCL 2013-11-14 34.38 0.155524872821249
ORCL 2013-11-15 34.92 0.160960193098425
ORCL 2013-11-18 34.93 0.156351173876592
ORCL 2013-11-19 34.76 0.149932066659868
ORCL 2013-11-20 34.75 0.148269358199017
ORCL 2013-11-21 34.94 0.13321556186422
ORCL 2013-11-22 34.83 0.132644327249953
ORCL 2013-11-25 34.78 0.132900547285873
ORCL 2013-11-26 34.93 0.122245849244905
ORCL 2013-11-27 35.29 0.123265455440989
ORCL 2013-11-29 35.29 0.121790241831743
ORCL 2013-12-02 35.08 0.124115407570261
ORCL 2013-12-03 35.07 0.124303653522474
ORCL 2013-12-04 35.07 0.123999332335981
ORCL 2013-12-05 34.85 0.124113192575353
ORCL 2013-12-06 35.48 0.129747978388065
ORCL 2013-12-09 35.60 0.129782418776215
ORCL 2013-12-10 34.80 0.148701750006502
ORCL 2013-12-11 34.56 0.143225579124709
ORCL 2013-12-12 33.60 0.165344904639033
ORCL 2013-12-13 33.23 0.168462795758679
ORCL 2013-12-16 33.54 0.170707200458498
ORCL 2013-12-17 33.63 0.170154787941637
ORCL 2013-12-18 34.60 0.187985873953149
ORCL 2013-12-19 36.60 0.243149504428376
ORCL 2013-12-20 36.37 0.244133722672318
ORCL 2013-12-23 36.93 0.245997226333116
ORCL 2013-12-24 37.32 0.247034049568057
ORCL 2013-12-26 37.69 0.247109898714265
ORCL 2013-12-27 37.98 0.246930768592208
In this example, we use the same data as in the previous example, except that the data are no longer in 3rd normal form. The closing prices for each of the 5 tickers are now stored in colums with each each date containing a 5 columns, one for each ticker. We can use the @Id variable to calculate the 5 moving volatilities in a single select. Additionally, we will only return the non-null values.
SELECT
*
FROM (
SELECT
tdate,
wct.MovingVOLATILITY(AAPL,252,30,ROW_NUMBER() OVER (ORDER BY tdate),0,'True') as AAPL,
wct.MovingVOLATILITY(FB,252,30,ROW_NUMBER() OVER (ORDER BY tdate),1,'True') as FB,
wct.MovingVOLATILITY(IBM,252,30,ROW_NUMBER() OVER (ORDER BY tdate),2,'True') as IBM,
wct.MovingVOLATILITY(MSFT,252,30,ROW_NUMBER() OVER (ORDER BY tdate),3,'True') as MSFT,
wct.MovingVOLATILITY(ORCL,252,30,ROW_NUMBER() OVER (ORDER BY tdate),4,'True') as ORCL
FROM
(VALUES
('2013-09-30',473.98,50.23,184.19,33.03,33.05)
,('2013-10-01',485.13,50.42,185.38,33.33,33.38)
,('2013-10-02',486.72,50.28,183.97,33.66,33.56)
,('2013-10-03',480.6,49.18,182.88,33.61,33.12)
,('2013-10-04',480.23,51.04,183.12,33.62,33.21)
,('2013-10-07',484.92,50.52,181.04,33.05,32.84)
,('2013-10-08',478.15,47.14,177.77,32.76,32.37)
,('2013-10-09',483.77,46.77,180.35,32.82,32.19)
,('2013-10-10',486.8,49.05,183.78,33.51,32.99)
,('2013-10-11',489.95,49.11,185.17,33.87,33.26)
,('2013-10-14',493.16,49.51,185.97,34.19,33.28)
,('2013-10-15',495.79,49.5,183.67,34.23,32.75)
,('2013-10-16',498.2,51.14,185.73,34.38,33.02)
,('2013-10-17',501.57,52.21,173.9,34.66,32.87)
,('2013-10-18',505.94,54.22,172.85,34.7,32.9)
,('2013-10-21',518.33,53.85,171.94,34.73,32.95)
,('2013-10-22',516.85,52.68,174.04,34.32,32.9)
,('2013-10-23',521.91,51.9,174.83,33.51,32.7)
,('2013-10-24',528.82,52.45,176.85,33.47,33.07)
,('2013-10-25',522.91,51.95,175.91,35.46,33.15)
,('2013-10-28',526.8,50.23,176.4,35.3,33.14)
,('2013-10-29',513.68,49.4,181.15,35.25,33.71)
,('2013-10-30',521.85,49.01,179.19,35.27,33.53)
,('2013-10-31',519.67,50.21,178.25,35.14,33.5)
,('2013-11-01',517.01,49.75,178.27,35.26,33.53)
,('2013-11-04',523.69,48.22,179.31,35.67,33.71)
,('2013-11-05',522.4,50.11,176.9,36.36,33.5)
,('2013-11-06',520.92,49.12,179.19,37.89,34.07)
,('2013-11-07',512.49,47.56,180,37.22,34)
,('2013-11-08',520.56,47.53,179.99,37.5,34.35)
,('2013-11-11',519.05,46.2,182.88,37.31,34.37)
,('2013-11-12',520.01,46.61,183.07,37.08,34.7)
,('2013-11-13',520.63,48.71,183.55,37.87,35)
,('2013-11-14',528.16,48.99,182.21,37.73,34.38)
,('2013-11-15',524.99,49.01,183.19,37.56,34.92)
,('2013-11-18',518.63,45.83,184.47,36.92,34.93)
,('2013-11-19',519.55,46.36,185.25,36.74,34.76)
,('2013-11-20',515,46.43,185.19,37.08,34.75)
,('2013-11-21',521.14,46.7,184.13,37.4,34.94)
,('2013-11-22',519.8,46.23,181.3,37.57,34.83)
,('2013-11-25',523.74,44.82,178.94,37.64,34.78)
,('2013-11-26',533.4,45.89,177.31,37.35,34.93)
,('2013-11-27',545.96,46.49,178.97,37.6,35.29)
,('2013-11-29',556.07,47.01,179.68,38.13,35.29)
,('2013-12-02',551.23,47.06,177.48,38.45,35.08)
,('2013-12-03',566.32,46.73,176.08,38.31,35.07)
,('2013-12-04',565,48.62,175.74,38.94,35.07)
,('2013-12-05',567.9,48.34,176.08,38,34.85)
,('2013-12-06',560.02,47.94,177.67,38.36,35.48)
,('2013-12-09',566.43,48.84,177.46,38.71,35.6)
,('2013-12-10',565.55,50.25,177.12,38.11,34.8)
,('2013-12-11',561.36,49.38,175.2,37.61,34.56)
,('2013-12-12',560.54,51.83,173.37,37.22,33.6)
,('2013-12-13',554.43,53.32,172.8,36.69,33.23)
,('2013-12-16',557.5,53.81,177.85,36.89,33.54)
,('2013-12-17',554.99,54.86,175.76,36.52,33.63)
,('2013-12-18',550.77,55.57,178.7,36.58,34.6)
,('2013-12-19',544.46,55.05,180.22,36.25,36.6)
,('2013-12-20',549.02,55.12,180.02,36.8,36.37)
,('2013-12-23',570.09,57.77,182.23,36.62,36.93)
,('2013-12-24',567.67,57.96,183.22,37.08,37.32)
,('2013-12-26',563.9,57.73,185.35,37.44,37.69)
,('2013-12-27',560.09,55.44,185.08,37.29,37.98)
)n(tdate,AAPL,FB,IBM,MSFT,ORCL)
)p
WHERE AAPL IS NOT NULL
AND FB IS NOT NULL
AND IBM IS NOT NULL
AND MSFT IS NOT NULL
AND ORCL IS NOT NULL
This produces the following result which has been reformatted for ease of viewing.
|
tdate
|
AAPL
|
FB
|
IBM
|
MSFT
|
ORCL
|
|
11/11/2013
|
0.182449
|
0.413408
|
0.259601
|
0.25495
|
0.15019
|
|
11/12/2013
|
0.172084
|
0.414365
|
0.258864
|
0.256182
|
0.150004
|
|
11/13/2013
|
0.172089
|
0.435895
|
0.258085
|
0.26061
|
0.151057
|
|
11/14/2013
|
0.169557
|
0.431654
|
0.258389
|
0.261089
|
0.155525
|
|
11/15/2013
|
0.171285
|
0.416988
|
0.258851
|
0.26203
|
0.16096
|
|
11/18/2013
|
0.175501
|
0.458042
|
0.257273
|
0.26209
|
0.156351
|
|
11/19/2013
|
0.168586
|
0.41467
|
0.251131
|
0.260705
|
0.149932
|
|
11/20/2013
|
0.16962
|
0.414122
|
0.248098
|
0.261093
|
0.148269
|
|
11/21/2013
|
0.171582
|
0.389126
|
0.24281
|
0.256651
|
0.133216
|
|
11/22/2013
|
0.171666
|
0.389789
|
0.245805
|
0.255806
|
0.132644
|
|
11/25/2013
|
0.171927
|
0.397354
|
0.24789
|
0.255215
|
0.132901
|
|
11/26/2013
|
0.178088
|
0.404888
|
0.246783
|
0.25713
|
0.122246
|
|
11/27/2013
|
0.187987
|
0.393951
|
0.246042
|
0.25733
|
0.123265
|
|
11/29/2013
|
0.192912
|
0.38986
|
0.152062
|
0.258909
|
0.12179
|
|
12/2/2013
|
0.195393
|
0.370106
|
0.155661
|
0.259261
|
0.124115
|
|
12/3/2013
|
0.198212
|
0.370116
|
0.156753
|
0.259976
|
0.124304
|
|
12/4/2013
|
0.198084
|
0.387834
|
0.153167
|
0.258538
|
0.123999
|
|
12/5/2013
|
0.197161
|
0.386228
|
0.152732
|
0.259063
|
0.124113
|
|
12/6/2013
|
0.200449
|
0.384613
|
0.151298
|
0.258974
|
0.129748
|
|
12/9/2013
|
0.198263
|
0.389068
|
0.150467
|
0.204833
|
0.129782
|
|
12/10/2013
|
0.198101
|
0.386867
|
0.150407
|
0.210779
|
0.148702
|
|
12/11/2013
|
0.182686
|
0.387174
|
0.131153
|
0.215427
|
0.143226
|
|
12/12/2013
|
0.178968
|
0.410886
|
0.130913
|
0.218467
|
0.165345
|
|
12/13/2013
|
0.182177
|
0.413018
|
0.130493
|
0.222925
|
0.168463
|
|
12/16/2013
|
0.181086
|
0.412114
|
0.156577
|
0.223158
|
0.170707
|
|
12/17/2013
|
0.179465
|
0.401953
|
0.159131
|
0.2235
|
0.170155
|
|
12/18/2013
|
0.181182
|
0.38972
|
0.161892
|
0.216647
|
0.187986
|
|
12/19/2013
|
0.184811
|
0.385389
|
0.159421
|
0.179805
|
0.24315
|
|
12/20/2013
|
0.177874
|
0.370063
|
0.158929
|
0.179065
|
0.244134
|
|
12/23/2013
|
0.202045
|
0.389077
|
0.162812
|
0.17793
|
0.245997
|
|
12/24/2013
|
0.202427
|
0.374974
|
0.156848
|
0.181497
|
0.247034
|
|
12/26/2013
|
0.204336
|
0.376431
|
0.160342
|
0.182768
|
0.24711
|
|
12/27/2013
|
0.206146
|
0.384017
|
0.16029
|
0.172164
|
0.246931
|