Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server volatility of asset returns


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
XLeratorDB syntax for historic volatility function for SQL Server
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.
·         Available in XLeratorDB / financial 2008 only
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
 


Copyright 2008-2013 WestClinTech LLC         Privacy Policy        Terms of Service