EQALPHA
Updated: 21 December 2012
Use the EQALPHA aggregate function to calculate 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.
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.
· 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.
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.
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 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 wctStatistics.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.
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.
ticker ALPHA
---------- ----------------------
AAPL -0.00317518803816589
IBM -0.000395022617631785
MSFT -0.0025136478079348
ORCL 0.00101255546597677
SP500 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.
ticker ALPHA
---------- ----------------------
AAPL -0.00475808955985833
IBM -0.000938239863795479
MSFT -0.00192399853446044
ORCL 0.00341896675068646
SP500 0