EQVOLATILITY
Updated 2024-02-23 15:16:43.453000
Syntax
SELECT [westclintech].[wct].[EQVOLATILITY] (
<@PDate, datetime,>
,<@PValue, float,>
,<@Scale, float,>)
Description
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.
v=s_r*\sqrt{scale}
Where
r=\{r_1,r_2,r_3,\dots,r_n\}
and
\mathrm{r_n=\ln\left(\frac{Price_n}{Price_{n-1}}\right)}
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.
{"columns":[{"field":"EQVOLATILITY","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"columns":[{"field":"ticker"},{"field":"VOL","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"ticker":"AAPL","VOL":"0.187965609662433"},{"ticker":"FB","VOL":"0.417191302857349"},{"ticker":"IBM","VOL":"0.290730860152208"},{"ticker":"MSFT","VOL":"0.249263738730003"},{"ticker":"ORCL","VOL":"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.
{"columns":[{"field":"ticker"},{"field":"VOL","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"ticker":"AAPL","VOL":"0.279520698602733"},{"ticker":"FB","VOL":"0.253546334476966"},{"ticker":"IBM","VOL":"0.23412325718261"},{"ticker":"MSFT","VOL":"0.423597750600135"},{"ticker":"ORCL","VOL":"0.143389046882403"}]}