EQALPHA
Updated 2023-10-09 14:59:48.657000
Syntax
SELECT [westclintech].[wct].[EQALPHA] (
<@PDate, datetime,>
,<@PValue, float,>
,<@BValue, float,>)
Description
Use the EQALPHA aggregate function to calculate the intercept of the security characteristic line (SCL), between an asset and a specified benchmark. The EQALPHA function take prices (rather than return data) as input. If you want to calculate the alpha using return information, use the INTERCEPT 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.
EQALPHA is an aggregate function and follows the same conventions as all other aggregate functions in SQL Server.
The EQALPHA function automatically calculates the returns.
To calculate the alpha using return values, consider using the INTERCEPT function.
To calculate beta consider using the EQBETA 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 alpha for the symbol IBM against a benchmark of the S&P 500.
SELECT wct.EQALPHA(tDate, IBM, SP500) as ALPHA
FROM #eq;
This produces the following result.
{"columns":[{"field":"ALPHA","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"ALPHA":"-0.000395022617631785"}]}
In this example, we want to calculate the alpha for the last 15 days. To get 15 days’ worth of returns, we will need 16 days’ worth of prices.
SELECT wct.EQALPHA(tDate, IBM, SP500) as ALPHA
FROM
(SELECT TOP 16 * FROM #eq ORDER BY tDate DESC) n(tDate, IBM, SP500);
This produces the following result.
{"columns":[{"field":"ALPHA","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"ALPHA":"-0.000938239863795479"}]}
If we have return data, rather than price data, we can use the INTERCEPT function to calculate the alpha. In this example, we have converted the price data into return data and then calculated the intercept. This example uses the INTERCEPT function from the XLeratorDB / statistics 2008 library and the 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.INTERCEPT(IBM, SP500) as ALPHA
FROM
(SELECT TOP 15 * FROM #ret ORDER BY rn DESC) n(rn, tDate, IBM, SP500);
This produces the following result.
{"columns":[{"field":"ALPHA","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"ALPHA":"-0.000938239863795479"}]}
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 alpha for each of tickers against the SP500 benchmark
SELECT a.ticker,
wct.EQALPHA(a.tDate, a.cPrice, b.cPrice) as ALPHA
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":"ALPHA","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"ticker":"AAPL","ALPHA":"-0.00317518803816589"},{"ticker":"IBM","ALPHA":"-0.000395022617631785"},{"ticker":"MSFT","ALPHA":"-0.0025136478079348"},{"ticker":"ORCL","ALPHA":"0.00101255546597677"},{"ticker":"SP500","ALPHA":"0"}]}
In this example, we will calculate the alpha for the last 15 days, which requires the prices for the last 16 days.
SELECT a.ticker,
wct.EQALPHA(a.tDate, a.cPrice, b.cPrice) as ALPHA
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":"ALPHA","headerClass":"ag-right-aligned-header","cellClass":"ag-right-aligned-cell"}],"rows":[{"ticker":"AAPL","ALPHA":"-0.00475808955985833"},{"ticker":"IBM","ALPHA":"-0.000938239863795479"},{"ticker":"MSFT","ALPHA":"-0.00192399853446044"},{"ticker":"ORCL","ALPHA":"0.00341896675068646"},{"ticker":"SP500","ALPHA":"0"}]}