EQBETA
Updated 2023-10-09 15:17:07.070000
Syntax
SELECT [westclintech].[wct].[EQBETA] (
<@PDate, datetime,>
,<@PValue, float,>
,<@BValue, float,>)
Description
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.
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 Type
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.
{"columns":[{"field":"BETA","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"columns":[{"field":"BETA","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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.
{"columns":[{"field":"BETA","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"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 .
{"columns":[{"field":"ticker"},{"field":"BETA","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"ticker":"AAPL","BETA":"1.92080040946786"},{"ticker":"IBM","BETA":"0.902353333082278"},{"ticker":"MSFT","BETA":"0.97329486013293"},{"ticker":"ORCL","BETA":"0.867119644814556"},{"ticker":"SP500","BETA":"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.
{"columns":[{"field":"ticker"},{"field":"BETA","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"ticker":"AAPL","BETA":"1.0661648629348"},{"ticker":"IBM","BETA":"1.17768781252228"},{"ticker":"MSFT","BETA":"1.7718017011058"},{"ticker":"ORCL","BETA":"0.673748418269754"},{"ticker":"SP500","BETA":"1"}]}
See Also
SLOPE - slope of the linear regression through the data points in the known x-values and y-values
SHARPE - Sharpe ratio based upon return data
SHARPE2 - Sharpe ratio based upon price or valuation data
INFORATIO - Information ratio based upon return data
INFORATIO2 - Information ratio based upon price or valuation data
TREYNOR - TREYNOR ratio based upon return data
TREYNOR2 - Calculate the Treynor ratio based upon price or valuation data.