EQVOLATILITY
Updated: 04 November 2013
Use EQVOLATILITY to calculate the historical volatility based upon price or valuation data. The historic volatility is calculated as the sample standard deviation of the natural logarithm of the returns multiplied by the square root of the scaling factor supplied to the function. The returns are calculated on the ordered set of data passed as the current price divided by the previous price.
Where
and
Syntax
Arguments
@PDate
the date associated with the price or valuation. @PDate is an expression of type datetime or of a type that can be implicitly converted to datetime.
@PValue
the price or value. @PValue 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.
Return Type
float
Remarks
· If @Scale IS NULL them @Scale is set to 252.
· For daily returns set @Scale = 252.
· For weekly returns set @Scale = 52.
· For monthly returns set @Scale = 12.
· For quarterly returns set @Scale = 4.
· @Scale must the same for all rows in the GROUP BY.
· If there are multiple rows for the same date, the @PValue is accumulated.
· The return values are automatically calculated by putting the @PValue in @PDate order.
Examples
In this example we have price data for IBM and we want to calculate the historic volatility for all the rows.
SELECT wct.EQVOLATILITY(
CAST(tdate as datetime)--@PDate
, pr --@PValue
, 252 --@Scale
) as EQVOLATILITY
FROM (VALUES
('IBM','2012-12-18',195.69),
('IBM','2012-12-17',193.62),
('IBM','2012-12-14',191.76),
('IBM','2012-12-13',191.99),
('IBM','2012-12-12',192.95),
('IBM','2012-12-11',194.2),
('IBM','2012-12-10',192.62),
('IBM','2012-12-07',191.95),
('IBM','2012-12-06',189.7),
('IBM','2012-12-05',188.65),
('IBM','2012-12-04',189.36),
('IBM','2012-12-03',189.48),
('IBM','2012-11-30',190.07),
('IBM','2012-11-29',191.53),
('IBM','2012-11-28',191.98),
('IBM','2012-11-27',191.23),
('IBM','2012-11-26',192.88),
('IBM','2012-11-23',193.49),
('IBM','2012-11-21',190.29),
('IBM','2012-11-20',189.2),
('IBM','2012-11-19',190.35),
('IBM','2012-11-16',186.94),
('IBM','2012-11-15',185.85),
('IBM','2012-11-14',185.51),
('IBM','2012-11-13',188.32),
('IBM','2012-11-12',189.25),
('IBM','2012-11-09',189.64),
('IBM','2012-11-08',190.1),
('IBM','2012-11-07',191.16),
('IBM','2012-11-06',194.22),
('IBM','2012-11-05',193.29),
('IBM','2012-11-02',192.59),
('IBM','2012-11-01',196.29),
('IBM','2012-10-31',193.68)
)n(ticker,tdate,pr)
This produces the following result.
EQVOLATILITY
----------------------
0.141437834070616
In this example, we have multiple tickers and we want to calculate the historic volatility for each ticker.
SELECT *
INTO #tbl1
FROM (VALUES
('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-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-10-30',35.54)
,('MSFT','2013-10-29',35.52)
,('MSFT','2013-10-28',35.57)
,('MSFT','2013-10-25',35.73)
,('MSFT','2013-10-24',33.72)
,('MSFT','2013-10-23',33.76)
,('MSFT','2013-10-22',34.58)
,('MSFT','2013-10-21',34.99)
,('MSFT','2013-10-18',34.96)
,('MSFT','2013-10-17',34.92)
,('MSFT','2013-10-16',34.64)
,('MSFT','2013-10-15',34.49)
,('MSFT','2013-10-14',34.45)
,('MSFT','2013-10-11',34.13)
,('MSFT','2013-10-10',33.76)
,('MSFT','2013-10-09',33.07)
,('MSFT','2013-10-08',33.01)
,('MSFT','2013-10-07',33.3)
,('MSFT','2013-10-04',33.88)
,('MSFT','2013-10-03',33.86)
,('MSFT','2013-10-02',33.92)
,('MSFT','2013-10-01',33.58)
,('MSFT','2013-09-30',33.28)
,('AAPL','2013-10-30',524.9)
,('AAPL','2013-10-29',516.68)
,('AAPL','2013-10-28',529.88)
,('AAPL','2013-10-25',525.96)
,('AAPL','2013-10-24',531.91)
,('AAPL','2013-10-23',524.96)
,('AAPL','2013-10-22',519.87)
,('AAPL','2013-10-21',521.36)
,('AAPL','2013-10-18',508.89)
,('AAPL','2013-10-17',504.5)
,('AAPL','2013-10-16',501.11)
,('AAPL','2013-10-15',498.68)
,('AAPL','2013-10-14',496.04)
,('AAPL','2013-10-11',492.81)
,('AAPL','2013-10-10',489.64)
,('AAPL','2013-10-09',486.59)
,('AAPL','2013-10-08',480.94)
,('AAPL','2013-10-07',487.75)
,('AAPL','2013-10-04',483.03)
,('AAPL','2013-10-03',483.41)
,('AAPL','2013-10-02',489.56)
,('AAPL','2013-10-01',487.96)
,('AAPL','2013-09-30',476.75)
,('IBM','2013-10-30',180.15)
,('IBM','2013-10-29',182.12)
,('IBM','2013-10-28',177.35)
,('IBM','2013-10-25',176.85)
,('IBM','2013-10-24',177.8)
,('IBM','2013-10-23',175.77)
,('IBM','2013-10-22',174.97)
,('IBM','2013-10-21',172.86)
,('IBM','2013-10-18',173.78)
,('IBM','2013-10-17',174.83)
,('IBM','2013-10-16',186.73)
,('IBM','2013-10-15',184.66)
,('IBM','2013-10-14',186.97)
,('IBM','2013-10-11',186.16)
,('IBM','2013-10-10',184.77)
,('IBM','2013-10-09',181.32)
,('IBM','2013-10-08',178.72)
,('IBM','2013-10-07',182.01)
,('IBM','2013-10-04',184.1)
,('IBM','2013-10-03',183.86)
,('IBM','2013-10-02',184.96)
,('IBM','2013-10-01',186.38)
,('IBM','2013-09-30',185.18)
)n(ticker,tdate,price)
SELECT ticker
,wct.EQVOLATILITY(cast(tdate as datetime),price, 252) as VOL
FROM #tbl1
GROUP BY ticker
This produces the following result.
ticker VOL
------ ----------------------
AAPL 0.187965609662433
FB 0.417191302857349
IBM 0.290730860152208
MSFT 0.249263738730003
ORCL 0.161520549212189
Using the same date as the previous example, we can calculate the historical volatility for the last 5 days (which requires 6 days of prices) simply by adding a WHERE clause.
SELECT ticker
,wct.EQVOLATILITY(cast(tdate as datetime),price, 252) as VOL
FROM #tbl1
WHERE tdate between wct.BUSINESSDATE(CAST('2013-10-30' as datetime),'D',-5,'P','') AND CAST('2013-10-30' as datetime)
GROUP BY ticker
This produces the following result.
ticker VOL
------ ----------------------
AAPL 0.279520698602733
FB 0.253546334476966
IBM 0.23412325718261
MSFT 0.423597750600135
ORCL 0.143389046882403