EQBETA
Updated: 21 December 2012
Use the EQBETA aggregate function to calculate the correlated volatility (beta) between an asset and a specified benchmark. The EQBETA function take prices (rather than return data) as input. If you want to calculate the beta using return information, use the SLOPE aggregate function.
Syntax
Arguments
@PDate
the date of the price or value. @PDate must be of type datetime or of a type that implicitly converts to datetime.
@PValue
the asset value. This could be the price of a security, the value of a portfolio, or other valuations. It should not be a return value. @PValue must be of a type float or of a type that implicitly converts to float.
@BValue
the benchmark value. This could be the price of a security, the value of a portfolio, or other valuations. It should not be a return value. @BValue must be of a type float or of a type that implicitly converts to float.
Return Types
float
Remarks
· If there are fewer than 3 rows in a GROUP a NULL will be returned.
· EQBETA is an aggregate function and follows the same conventions as all other aggregate functions in SQL Server.
· The EQBETA function automatically calculates the returns.
· To calculate the beta using return values, consider using the SLOPE function.
· To calculate alpha consider using the EQALPHA function.
Examples
First, let’s put some data in a very simple table.
CREATE TABLE #eq(
tDate date,
IBM money,
SP500 money
)
INSERT INTO #eq
SELECT *
FROM (VALUES
('2012-12-11',194.2,1427.84),
('2012-12-10',192.62,1418.55),
('2012-12-07',191.95,1418.07),
('2012-12-06',189.7,1413.94),
('2012-12-05',188.65,1409.28),
('2012-12-04',189.36,1407.05),
('2012-12-03',189.48,1409.46),
('2012-11-30',190.07,1416.18),
('2012-11-29',191.53,1415.95),
('2012-11-28',191.98,1409.93),
('2012-11-27',191.23,1398.94),
('2012-11-26',192.88,1406.29),
('2012-11-23',193.49,1409.15),
('2012-11-21',190.29,1391.03),
('2012-11-20',189.2,1387.81),
('2012-11-19',190.35,1386.89),
('2012-11-16',186.94,1359.88),
('2012-11-15',185.85,1353.33),
('2012-11-14',185.51,1355.49),
('2012-11-13',188.32,1374.53),
('2012-11-12',189.25,1380),
('2012-11-09',189.64,1379.85),
('2012-11-08',190.1,1377.51),
('2012-11-07',191.16,1394.53),
('2012-11-06',194.22,1428.39),
('2012-11-05',193.29,1417.26),
('2012-11-02',192.59,1414.2),
('2012-11-01',196.29,1427.59)
)n(tDate,IBM,SP500)
In this example we will calculate the beta for the symbol IBM against a benchmark of the S&P 500.
SELECT wct.EQBETA(tDate,IBM,SP500) as BETA
FROM #eq
This produces the following result.
BETA
----------------------
0.902353333082278
In this example, we want to calculate the BETA for the last 15 days. To get 15 days’ worth of returns, we will need 16 days’ worth of prices.
SELECT wct.EQBETA(tDate,IBM,SP500) as BETA
FROM (SELECT TOP 16 * FROM #eq)n(tDate,IBM,SP500)
This produces the following result.
BETA
----------------------
1.17768781252228
If we have return data, rather than price data, we can use the SLOPE function to calculate the beta. In this example, we have converted the price data into return data and then calculated the slope. This example uses the SLOPE function from the XLeratorDB / statistics 2008 library and LAG function from the XLeratorDB / windowing library.
SELECT ROW_NUMBER() OVER (ORDER BY tDate) as rn
,tdate
,IBM / wct.LAG(IBM,1,NULL,ROW_NUMBER() OVER (ORDER BY tDate),0) - 1 as IBM
,SP500 / wct.LAG(SP500,1,NULL,ROW_NUMBER() OVER (ORDER BY tDate),1) - 1 as SP500
INTO #ret
FROM #eq
SELECT wct.SLOPE(IBM,SP500) as BETA
FROM (SELECT TOP 15 * FROM #ret ORDER BY rn DESC)n(rn,tDate,IBM,SP500)
This produces the following result.
BETA
----------------------
1.17768781252228
In this example, we will use a different table structure, where the ticker is now a piece of data rather than a column name. We will store the benchmark data in the same table.
CREATE TABLE #eq1(
tDate date,
ticker varchar(10),
cPrice money,
PRIMARY KEY(tDate,ticker)
)
INSERT INTO #eq1
SELECT *
FROM (VALUES
('2012-12-11','IBM',194.2),('2012-12-10','IBM',192.62),
('2012-12-07','IBM',191.95),('2012-12-06','IBM',189.7),
('2012-12-05','IBM',188.65),('2012-12-04','IBM',189.36),
('2012-12-03','IBM',189.48),('2012-11-30','IBM',190.07),
('2012-11-29','IBM',191.53),('2012-11-28','IBM',191.98),
('2012-11-27','IBM',191.23),('2012-11-26','IBM',192.88),
('2012-11-23','IBM',193.49),('2012-11-21','IBM',190.29),
('2012-11-20','IBM',189.2),('2012-11-19','IBM',190.35),
('2012-11-16','IBM',186.94),('2012-11-15','IBM',185.85),
('2012-11-14','IBM',185.51),('2012-11-13','IBM',188.32),
('2012-11-12','IBM',189.25),('2012-11-09','IBM',189.64),
('2012-11-08','IBM',190.1),('2012-11-07','IBM',191.16),
('2012-11-06','IBM',194.22),('2012-11-05','IBM',193.29),
('2012-11-02','IBM',192.59),('2012-11-01','IBM',196.29),
('2012-12-11','SP500',1427.84),('2012-12-10','SP500',1418.55),
('2012-12-07','SP500',1418.07),('2012-12-06','SP500',1413.94),
('2012-12-05','SP500',1409.28),('2012-12-04','SP500',1407.05),
('2012-12-03','SP500',1409.46),('2012-11-30','SP500',1416.18),
('2012-11-29','SP500',1415.95),('2012-11-28','SP500',1409.93),
('2012-11-27','SP500',1398.94),('2012-11-26','SP500',1406.29),
('2012-11-23','SP500',1409.15),('2012-11-21','SP500',1391.03),
('2012-11-20','SP500',1387.81),('2012-11-19','SP500',1386.89),
('2012-11-16','SP500',1359.88),('2012-11-15','SP500',1353.33),
('2012-11-14','SP500',1355.49),('2012-11-13','SP500',1374.53),
('2012-11-12','SP500',1380),('2012-11-09','SP500',1379.85),
('2012-11-08','SP500',1377.51),('2012-11-07','SP500',1394.53),
('2012-11-06','SP500',1428.39),('2012-11-05','SP500',1417.26),
('2012-11-02','SP500',1414.2),('2012-11-01','SP500',1427.59),
('2012-12-11','MSFT',27.32),('2012-12-10','MSFT',26.94),
('2012-12-07','MSFT',26.46),('2012-12-06','MSFT',26.73),
('2012-12-05','MSFT',26.67),('2012-12-04','MSFT',26.37),
('2012-12-03','MSFT',26.43),('2012-11-30','MSFT',26.62),
('2012-11-29','MSFT',26.95),('2012-11-28','MSFT',27.36),
('2012-11-27','MSFT',27.08),('2012-11-26','MSFT',27.39),
('2012-11-23','MSFT',27.7),('2012-11-21','MSFT',26.95),
('2012-11-20','MSFT',26.71),('2012-11-19','MSFT',26.73),
('2012-11-16','MSFT',26.52),('2012-11-15','MSFT',26.66),
('2012-11-14','MSFT',26.84),('2012-11-13','MSFT',27.09),
('2012-11-12','MSFT',27.99),('2012-11-09','MSFT',28.6),
('2012-11-08','MSFT',28.58),('2012-11-07','MSFT',28.84),
('2012-11-06','MSFT',29.62),('2012-11-05','MSFT',29.39),
('2012-11-02','MSFT',29.26),('2012-11-01','MSFT',29.28),
('2012-12-11','AAPL',541.39),('2012-12-10','AAPL',529.82),
('2012-12-07','AAPL',533.25),('2012-12-06','AAPL',547.24),
('2012-12-05','AAPL',538.79),('2012-12-04','AAPL',575.85),
('2012-12-03','AAPL',586.19),('2012-11-30','AAPL',585.28),
('2012-11-29','AAPL',589.36),('2012-11-28','AAPL',582.94),
('2012-11-27','AAPL',584.78),('2012-11-26','AAPL',589.53),
('2012-11-23','AAPL',571.5),('2012-11-21','AAPL',561.7),
('2012-11-20','AAPL',560.91),('2012-11-19','AAPL',565.73),
('2012-11-16','AAPL',527.68),('2012-11-15','AAPL',525.62),
('2012-11-14','AAPL',536.88),('2012-11-13','AAPL',542.9),
('2012-11-12','AAPL',542.83),('2012-11-09','AAPL',547.06),
('2012-11-08','AAPL',537.75),('2012-11-07','AAPL',558),
('2012-11-06','AAPL',580.2),('2012-11-05','AAPL',581.96),
('2012-11-02','AAPL',574.18),('2012-11-01','AAPL',593.83),
('2012-12-11','ORCL',32.34),('2012-12-10','ORCL',32.07),
('2012-12-07','ORCL',31.92),('2012-12-06','ORCL',32.03),
('2012-12-05','ORCL',32),('2012-12-04','ORCL',32.38),
('2012-12-03','ORCL',32.31),('2012-11-30','ORCL',32.18),
('2012-11-29','ORCL',31.84),('2012-11-28','ORCL',31.8),
('2012-11-27','ORCL',31.22),('2012-11-26','ORCL',30.96),
('2012-11-23','ORCL',30.92),('2012-11-21','ORCL',30.4),
('2012-11-20','ORCL',30.2),('2012-11-19','ORCL',30.14),
('2012-11-16','ORCL',30),('2012-11-15','ORCL',29.95),
('2012-11-14','ORCL',29.58),('2012-11-13','ORCL',30.02),
('2012-11-12','ORCL',30.3),('2012-11-09','ORCL',30.35),
('2012-11-08','ORCL',30.42),('2012-11-07','ORCL',30.79),
('2012-11-06','ORCL',31.63),('2012-11-05','ORCL',31.25),
('2012-11-02','ORCL',31.21),('2012-11-01','ORCL',31.48)
) n(tDate,ticker,cPrice)
Let’s calculate the BETA for each of tickers against the SP500 benchmark.
SELECT a.ticker
,wct.EQBETA(a.tDate,a.cPrice,b.cPrice) as BETA
FROM #eq1 a
JOIN #eq1 b
ON a.tDate = b.tDate
WHERE b.ticker = 'SP500'
GROUP BY a.ticker
This produces the following result.
ticker BETA
---------- ----------------------
AAPL 1.92080040946786
IBM 0.902353333082278
MSFT 0.97329486013293
ORCL 0.867119644814556
SP500 1
In this example, we will calculate the BETA for the last 15 days, which requires the prices for the last 16 days.
SELECT a.ticker
,wct.EQBETA(a.tDate,a.cPrice,b.cPrice) as BETA
FROM (
SELECT TOP 16 tDate, cPrice
FROM #eq1
WHERE ticker = 'SP500'
ORDER BY tDate DESC
) b
JOIN #eq1 a
ON a.tDate = b.tDate
GROUP BY a.ticker
This produces the following result.
ticker BETA
---------- ----------------------
AAPL 1.0661648629348
IBM 1.17768781252228
MSFT 1.7718017011058
ORCL 0.673748418269754
SP500 1
See Also