SQL Server EQVOLATILITY Function

Updated 2024-02-23 15:16:43.453000

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)}

Syntax

SELECT [westclintech].[wct].[EQVOLATILITY] (
  <@PDate, datetime,> 
 ,<@PValue, float,> 
 ,<@Scale, float,>)

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.

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.

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.