RunningEWMA
Updated: 31 Oct 2012
Use RunningEWMA to calculate the exponentially weighted moving average of column values in an ordered resultant table, without the need for a self-join. The exponentially weighted moving average is calculated over all the values from the first value to the last value in the ordered group or partition. If the column values are presented to the functions out of order, an error message will be generated.
Syntax
SELECT [Example].[wct].[RunningEWMA](
<@Val, float,>
,<@Alpha, float,>
,<@Lag, int,>
,<@RowNum, int,>
,<@Id, tinyint,>)
GO
Arguments
@Val
the value passed into the function. @Val is an expression of type float or of a type that can be implicitly converted to float.
@Alpha
the degree of weighting decrease. A higher @Alpha discounts older observations faster. @Alpha is an expression of type float or of a type that can be implicitly converted to float.
@Lag
permits the exponentially weighted moving average to by returned with the current row (@Lag = 0) or the subsequent row (@Lag = 1). @Lag is an expression of type int or of a type that can be implicitly converted to int.
@RowNum
the number of the row within the group for which the sum is being calculated. If @RowNum for the current row in a set is less than or equal to the previous @RowNum and @RowNum is not equal to 1, an error message will be generated. @RowNum is an expression of type int or of a type that can be implicitly converted to int.
@Id
a unique identifier for the RunningEWMA calculation. @Id allows you to specify multiple RunningEWMA calculations within a resultant table. @Id is an expression of type tinyint or of a type that can be implicitly converted to tinyint.
Remarks
· If @Id is NULL then @Id = 0.
· To calculate daily moving averages, use the DWMA function.
· To calculate a daily exponentially weighted moving average, use the DEMA function,
· 0 < @Alpha < 1.
· @Lag must be 0 or 1.
· @RowNum must be in ascending order.
· There may be cases where the order in which the data are returned to the function and the order in which the results are returned are different, generally due to parallelism. You can use OPTION(MAXDOP 1) or OPTION(MAXDOP 1,FORCE ORDER) to help eliminate this problem
Example
In this example, we put a couple of months of price history for stock indices into a table and then calculate the exponentially weighted moving average for one of the indices with a = .05.
--Create the temporary table
CREATE TABLE #p(
ticker varchar(4),
date_trade datetime,
price_open money,
price_high money,
price_low money,
price_close money,
volume bigint,
price_adj_close money,
PRIMARY KEY (ticker, date_trade)
)
--Populate the table with some data
INSERT INTO #p VALUES ('GSPC', '2012-10-26', 1412.97, 1417.09, 1403.28, 1411.94, 3284910000, 1411.94)
INSERT INTO #p VALUES ('GSPC', '2012-10-25', 1409.74, 1421.12, 1405.14, 1412.97, 3512640000, 1412.97)
INSERT INTO #p VALUES ('GSPC', '2012-10-24', 1413.2, 1420.04, 1407.1, 1408.75, 3385970000, 1408.75)
INSERT INTO #p VALUES ('GSPC', '2012-10-23', 1433.74, 1433.74, 1407.56, 1413.11, 3587670000, 1413.11)
INSERT INTO #p VALUES ('GSPC', '2012-10-22', 1433.21, 1435.46, 1422.06, 1433.81, 3216220000, 1433.81)
INSERT INTO #p VALUES ('GSPC', '2012-10-19', 1457.34, 1457.34, 1429.85, 1433.19, 3875170000, 1433.19)
INSERT INTO #p VALUES ('GSPC', '2012-10-18', 1460.94, 1464.02, 1452.63, 1457.34, 3880030000, 1457.34)
INSERT INTO #p VALUES ('GSPC', '2012-10-17', 1454.22, 1462.2, 1453.35, 1460.91, 3655320000, 1460.91)
INSERT INTO #p VALUES ('GSPC', '2012-10-16', 1440.31, 1455.51, 1440.31, 1454.92, 3568770000, 1454.92)
INSERT INTO #p VALUES ('GSPC', '2012-10-15', 1428.75, 1441.31, 1427.24, 1440.13, 3483810000, 1440.13)
INSERT INTO #p VALUES ('GSPC', '2012-10-12', 1432.84, 1438.43, 1425.53, 1428.59, 3134750000, 1428.59)
INSERT INTO #p VALUES ('GSPC', '2012-10-11', 1432.82, 1443.9, 1432.82, 1432.84, 3672540000, 1432.84)
INSERT INTO #p VALUES ('GSPC', '2012-10-10', 1441.48, 1442.52, 1430.64, 1432.56, 3225060000, 1432.56)
INSERT INTO #p VALUES ('GSPC', '2012-10-09', 1455.9, 1455.9, 1441.18, 1441.48, 3216320000, 1441.48)
INSERT INTO #p VALUES ('GSPC', '2012-10-08', 1460.93, 1460.93, 1453.1, 1455.88, 2328720000, 1455.88)
INSERT INTO #p VALUES ('GSPC', '2012-10-05', 1461.4, 1470.96, 1456.89, 1460.93, 3172940000, 1460.93)
INSERT INTO #p VALUES ('GSPC', '2012-10-04', 1451.08, 1463.14, 1451.08, 1461.4, 3615860000, 1461.4)
INSERT INTO #p VALUES ('GSPC', '2012-10-03', 1446.05, 1454.3, 1441.99, 1450.99, 3531640000, 1450.99)
INSERT INTO #p VALUES ('GSPC', '2012-10-02', 1444.99, 1451.52, 1439.01, 1445.75, 3321790000, 1445.75)
INSERT INTO #p VALUES ('GSPC', '2012-10-01', 1440.9, 1457.14, 1440.9, 1444.49, 3505080000, 1444.49)
INSERT INTO #p VALUES ('GSPC', '2012-09-28', 1447.13, 1447.13, 1435.6, 1440.67, 3509230000, 1440.67)
INSERT INTO #p VALUES ('GSPC', '2012-09-27', 1433.36, 1450.2, 1433.36, 1447.15, 3150330000, 1447.15)
INSERT INTO #p VALUES ('GSPC', '2012-09-26', 1441.6, 1441.6, 1430.53, 1433.32, 3565380000, 1433.32)
INSERT INTO #p VALUES ('GSPC', '2012-09-25', 1456.94, 1463.24, 1441.59, 1441.59, 3739900000, 1441.59)
INSERT INTO #p VALUES ('GSPC', '2012-09-24', 1459.76, 1460.72, 1452.06, 1456.89, 3008920000, 1456.89)
INSERT INTO #p VALUES ('GSPC', '2012-09-21', 1460.34, 1467.07, 1459.51, 1460.15, 4833870000, 1460.15)
INSERT INTO #p VALUES ('GSPC', '2012-09-20', 1461.05, 1461.23, 1449.98, 1460.26, 3382520000, 1460.26)
INSERT INTO #p VALUES ('GSPC', '2012-09-19', 1459.5, 1465.15, 1457.88, 1461.05, 3451360000, 1461.05)
INSERT INTO #p VALUES ('GSPC', '2012-09-18', 1461.19, 1461.47, 1456.13, 1459.32, 3377390000, 1459.32)
INSERT INTO #p VALUES ('GSPC', '2012-09-17', 1465.42, 1465.63, 1457.55, 1461.19, 3482430000, 1461.19)
INSERT INTO #p VALUES ('GSPC', '2012-09-14', 1460.07, 1474.51, 1460.07, 1465.77, 5041990000, 1465.77)
INSERT INTO #p VALUES ('GSPC', '2012-09-13', 1436.56, 1463.76, 1435.34, 1459.99, 4606550000, 1459.99)
INSERT INTO #p VALUES ('GSPC', '2012-09-12', 1433.56, 1439.15, 1432.99, 1436.56, 3641200000, 1436.56)
INSERT INTO #p VALUES ('GSPC', '2012-09-11', 1429.13, 1437.76, 1429.13, 1433.56, 3509630000, 1433.56)
INSERT INTO #p VALUES ('GSPC', '2012-09-10', 1437.92, 1438.74, 1428.98, 1429.08, 3223670000, 1429.08)
INSERT INTO #p VALUES ('GSPC', '2012-09-07', 1432.12, 1437.92, 1431.45, 1437.92, 3717620000, 1437.92)
INSERT INTO #p VALUES ('GSPC', '2012-09-06', 1403.74, 1432.12, 1403.74, 1432.12, 3952870000, 1432.12)
INSERT INTO #p VALUES ('GSPC', '2012-09-05', 1404.94, 1408.81, 1401.25, 1403.44, 3389110000, 1403.44)
INSERT INTO #p VALUES ('GSPC', '2012-09-04', 1406.54, 1409.31, 1396.56, 1404.94, 3200310000, 1404.94)
INSERT INTO #p VALUES ('GSPC', '2012-08-31', 1400.07, 1413.09, 1398.96, 1406.58, 2938250000, 1406.58)
INSERT INTO #p VALUES ('GSPC', '2012-08-30', 1410.08, 1410.08, 1397.01, 1399.48, 2530280000, 1399.48)
INSERT INTO #p VALUES ('GSPC', '2012-08-29', 1409.32, 1413.95, 1406.57, 1410.49, 2571220000, 1410.49)
INSERT INTO #p VALUES ('GSPC', '2012-08-28', 1410.44, 1413.63, 1405.59, 1409.3, 2629090000, 1409.3)
INSERT INTO #p VALUES ('GSPC', '2012-08-27', 1411.13, 1416.17, 1409.11, 1410.44, 2472500000, 1410.44)
INSERT INTO #p VALUES ('GSPC', '2012-08-24', 1401.99, 1413.46, 1398.04, 1411.13, 2598790000, 1411.13)
INSERT INTO #p VALUES ('DJI', '2012-10-26', 13104.22, 13151.72, 13040.17, 13107.21, 1346400, 13107.21)
INSERT INTO #p VALUES ('DJI', '2012-10-25', 13079.64, 13214.11, 13017.37, 13103.68, 1145900, 13103.68)
INSERT INTO #p VALUES ('DJI', '2012-10-24', 13103.53, 13155.21, 13063.63, 13077.34, 1106700, 13077.34)
INSERT INTO #p VALUES ('DJI', '2012-10-23', 13344.9, 13344.9, 13083.28, 13102.53, 1222200, 13102.53)
INSERT INTO #p VALUES ('DJI', '2012-10-22', 13344.28, 13368.55, 13235.15, 13345.89, 1218800, 13345.89)
INSERT INTO #p VALUES ('DJI', '2012-10-19', 13545.33, 13545.49, 13312.22, 13343.51, 2390800, 13343.51)
INSERT INTO #p VALUES ('DJI', '2012-10-18', 13553.24, 13588.73, 13510.93, 13548.94, 1284100, 13548.94)
INSERT INTO #p VALUES ('DJI', '2012-10-17', 13539.63, 13561.65, 13468.9, 13557, 1355700, 13557)
INSERT INTO #p VALUES ('DJI', '2012-10-16', 13423.84, 13556.37, 13423.76, 13551.78, 1134500, 13551.78)
INSERT INTO #p VALUES ('DJI', '2012-10-15', 13329.54, 13437.66, 13325.93, 13424.23, 1148800, 13424.23)
INSERT INTO #p VALUES ('DJI', '2012-10-12', 13325.62, 13401.32, 13296.43, 13328.85, 1137400, 13328.85)
INSERT INTO #p VALUES ('DJI', '2012-10-11', 13346.28, 13428.49, 13326.12, 13326.39, 866300, 13326.39)
INSERT INTO #p VALUES ('DJI', '2012-10-10', 13473.53, 13478.83, 13327.62, 13344.97, 1011200, 13344.97)
INSERT INTO #p VALUES ('DJI', '2012-10-09', 13582.88, 13592.33, 13473.31, 13473.53, 1036300, 13473.53)
INSERT INTO #p VALUES ('DJI', '2012-10-08', 13589.26, 13610.38, 13552.09, 13583.65, 713000, 13583.65)
INSERT INTO #p VALUES ('DJI', '2012-10-05', 13569.18, 13661.87, 13568.75, 13610.15, 1155000, 13610.15)
INSERT INTO #p VALUES ('DJI', '2012-10-04', 13495.18, 13594.33, 13495.18, 13575.36, 1063900, 13575.36)
INSERT INTO #p VALUES ('DJI', '2012-10-03', 13479.21, 13536.27, 13439.12, 13494.61, 1038900, 13494.61)
INSERT INTO #p VALUES ('DJI', '2012-10-02', 13515.3, 13567.06, 13424.92, 13482.36, 907300, 13482.36)
INSERT INTO #p VALUES ('DJI', '2012-10-01', 13437.66, 13598.25, 13437.66, 13515.11, 1061200, 13515.11)
INSERT INTO #p VALUES ('DJI', '2012-09-28', 13485.89, 13487.66, 13367.27, 13437.13, 1469500, 13437.13)
INSERT INTO #p VALUES ('DJI', '2012-09-27', 13413.47, 13522.83, 13413.47, 13485.97, 1139900, 13485.97)
INSERT INTO #p VALUES ('DJI', '2012-09-26', 13458.63, 13480.37, 13406.91, 13413.51, 1243500, 13413.51)
INSERT INTO #p VALUES ('DJI', '2012-09-25', 13559.92, 13620.21, 13457.25, 13457.55, 1386300, 13457.55)
INSERT INTO #p VALUES ('DJI', '2012-09-24', 13577.85, 13601.9, 13521.68, 13558.92, 1203700, 13558.92)
INSERT INTO #p VALUES ('DJI', '2012-09-21', 13597.24, 13647.1, 13571.53, 13579.47, 4296100, 13579.47)
INSERT INTO #p VALUES ('DJI', '2012-09-20', 13575.17, 13599.02, 13503, 13596.93, 1179100, 13596.93)
INSERT INTO #p VALUES ('DJI', '2012-09-19', 13565.41, 13626.48, 13556.74, 13577.96, 1162100, 13577.96)
INSERT INTO #p VALUES ('DJI', '2012-09-18', 13552.33, 13582.12, 13517.81, 13564.64, 1207200, 13564.64)
INSERT INTO #p VALUES ('DJI', '2012-09-17', 13588.57, 13593.15, 13526.67, 13553.1, 1280200, 13553.1)
INSERT INTO #p VALUES ('DJI', '2012-09-14', 13540.4, 13653.24, 13533.94, 13593.37, 1851600, 13593.37)
INSERT INTO #p VALUES ('DJI', '2012-09-13', 13329.71, 13573.33, 13325.11, 13539.86, 1517700, 13539.86)
INSERT INTO #p VALUES ('DJI', '2012-09-12', 13321.62, 13373.62, 13317.52, 13333.35, 1115200, 13333.35)
INSERT INTO #p VALUES ('DJI', '2012-09-11', 13254.6, 13354.34, 13253.21, 13323.36, 1049200, 13323.36)
INSERT INTO #p VALUES ('DJI', '2012-09-10', 13308.56, 13324.1, 13251.39, 13254.29, 1238100, 13254.29)
INSERT INTO #p VALUES ('DJI', '2012-09-07', 13289.53, 13320.27, 13266.22, 13306.64, 1422100, 13306.64)
INSERT INTO #p VALUES ('DJI', '2012-09-06', 13045.23, 13294.13, 13045.08, 13292, 1286500, 13292)
INSERT INTO #p VALUES ('DJI', '2012-09-05', 13036.09, 13095.91, 13018.74, 13047.48, 925500, 13047.48)
INSERT INTO #p VALUES ('DJI', '2012-09-04', 13092.15, 13092.39, 12977.09, 13035.94, 1039200, 13035.94)
INSERT INTO #p VALUES ('DJI', '2012-08-31', 13002.72, 13151.87, 13002.64, 13090.84, 1197800, 13090.84)
INSERT INTO #p VALUES ('DJI', '2012-08-30', 13101.29, 13101.37, 12978.91, 13000.71, 899800, 13000.71)
INSERT INTO #p VALUES ('DJI', '2012-08-29', 13103.46, 13144.81, 13081.27, 13107.48, 915300, 13107.48)
INSERT INTO #p VALUES ('DJI', '2012-08-28', 13122.74, 13147.32, 13081.12, 13102.99, 816300, 13102.99)
INSERT INTO #p VALUES ('DJI', '2012-08-27', 13157.74, 13176.17, 13115.46, 13124.67, 960700, 13124.67)
INSERT INTO #p VALUES ('DJI', '2012-08-24', 13052.82, 13175.51, 13027.2, 13157.97, 880300, 13157.97)
INSERT INTO #p VALUES ('IXIC', '2012-10-26', 2986.05, 2999.14, 2961.16, 2987.95, 1839700000, 2987.95)
INSERT INTO #p VALUES ('IXIC', '2012-10-25', 3005.04, 3007.71, 2975.98, 2986.12, 1922660000, 2986.12)
INSERT INTO #p VALUES ('IXIC', '2012-10-24', 3011.82, 3012.95, 2978.73, 2981.7, 1967000000, 2981.7)
INSERT INTO #p VALUES ('IXIC', '2012-10-23', 2989.44, 3006.59, 2974.07, 2990.46, 1830840000, 2990.46)
INSERT INTO #p VALUES ('IXIC', '2012-10-22', 3005.92, 3020.61, 2995.78, 3016.96, 1654130000, 3016.96)
INSERT INTO #p VALUES ('IXIC', '2012-10-19', 3066.56, 3066.56, 3000.27, 3005.62, 2225580000, 3005.62)
INSERT INTO #p VALUES ('IXIC', '2012-10-18', 3097.77, 3102.56, 3065.24, 3072.87, 2043290000, 3072.87)
INSERT INTO #p VALUES ('IXIC', '2012-10-17', 3091.38, 3112.45, 3088.05, 3104.12, 1770920000, 3104.12)
INSERT INTO #p VALUES ('IXIC', '2012-10-16', 3073.21, 3102.97, 3070.25, 3101.17, 1736930000, 3101.17)
INSERT INTO #p VALUES ('IXIC', '2012-10-15', 3053.21, 3066.31, 3037.27, 3064.18, 1563440000, 3064.18)
INSERT INTO #p VALUES ('IXIC', '2012-10-12', 3049.08, 3061.77, 3039.58, 3044.11, 1524840000, 3044.11)
INSERT INTO #p VALUES ('IXIC', '2012-10-11', 3075.89, 3078.08, 3047.14, 3049.41, 1595020000, 3049.41)
INSERT INTO #p VALUES ('IXIC', '2012-10-10', 3066.25, 3071.57, 3046.78, 3051.78, 1788970000, 3051.78)
INSERT INTO #p VALUES ('IXIC', '2012-10-09', 3108.01, 3108.01, 3062.52, 3065.02, 1645740000, 3065.02)
INSERT INTO #p VALUES ('IXIC', '2012-10-08', 3121.33, 3125.49, 3107.57, 3112.35, 1186260000, 3112.35)
INSERT INTO #p VALUES ('IXIC', '2012-10-05', 3161.21, 3171.46, 3130.76, 3136.19, 1607940000, 3136.19)
INSERT INTO #p VALUES ('IXIC', '2012-10-04', 3142.38, 3153.48, 3132.56, 3149.46, 1585190000, 3149.46)
INSERT INTO #p VALUES ('IXIC', '2012-10-03', 3130.85, 3142.36, 3115.04, 3135.23, 1704050000, 3135.23)
INSERT INTO #p VALUES ('IXIC', '2012-10-02', 3127.73, 3131.64, 3101.64, 3120.04, 1609570000, 3120.04)
INSERT INTO #p VALUES ('IXIC', '2012-10-01', 3130.31, 3146.99, 3103.89, 3113.53, 1758170000, 3113.53)
INSERT INTO #p VALUES ('IXIC', '2012-09-28', 3125.31, 3132.51, 3109.91, 3116.23, 1864640000, 3116.23)
INSERT INTO #p VALUES ('IXIC', '2012-09-27', 3105.87, 3142.02, 3098.46, 3136.6, 1691800000, 3136.6)
INSERT INTO #p VALUES ('IXIC', '2012-09-26', 3113.4, 3114.54, 3080.28, 3093.7, 1738010000, 3093.7)
INSERT INTO #p VALUES ('IXIC', '2012-09-25', 3170.37, 3176.3, 3117.73, 3117.73, 1975470000, 3117.73)
INSERT INTO #p VALUES ('IXIC', '2012-09-24', 3155.35, 3167.74, 3150.71, 3160.78, 1704860000, 3160.78)
INSERT INTO #p VALUES ('IXIC', '2012-09-21', 3194.86, 3196.93, 3178.09, 3179.96, 2526250000, 3179.96)
INSERT INTO #p VALUES ('IXIC', '2012-09-20', 3166.84, 3178.45, 3156.46, 3175.96, 1809130000, 3175.96)
INSERT INTO #p VALUES ('IXIC', '2012-09-19', 3179.04, 3189.35, 3170.29, 3182.62, 1850920000, 3182.62)
INSERT INTO #p VALUES ('IXIC', '2012-09-18', 3173.62, 3179.37, 3169.41, 3177.8, 1707200000, 3177.8)
INSERT INTO #p VALUES ('IXIC', '2012-09-17', 3183.4, 3183.4, 3168.63, 3178.67, 1485390000, 3178.67)
INSERT INTO #p VALUES ('IXIC', '2012-09-14', 3166.24, 3195.67, 3164.26, 3183.95, 1984720000, 3183.95)
INSERT INTO #p VALUES ('IXIC', '2012-09-13', 3117.66, 3167.63, 3112.62, 3155.83, 1870050000, 3155.83)
INSERT INTO #p VALUES ('IXIC', '2012-09-12', 3115.33, 3120.12, 3098.82, 3114.31, 1689140000, 3114.31)
INSERT INTO #p VALUES ('IXIC', '2012-09-11', 3105.02, 3117.86, 3099.1, 3104.53, 1586250000, 3104.53)
INSERT INTO #p VALUES ('IXIC', '2012-09-10', 3131.34, 3133.89, 3102.76, 3104.02, 1575370000, 3104.02)
INSERT INTO #p VALUES ('IXIC', '2012-09-07', 3133.22, 3139.61, 3128.17, 3136.42, 1740640000, 3136.42)
INSERT INTO #p VALUES ('IXIC', '2012-09-06', 3087.94, 3135.81, 3087.67, 3135.81, 1918900000, 3135.81)
INSERT INTO #p VALUES ('IXIC', '2012-09-05', 3072.58, 3082.75, 3062.54, 3069.27, 1495030000, 3069.27)
INSERT INTO #p VALUES ('IXIC', '2012-09-04', 3063.25, 3082.26, 3040.24, 3075.06, 1505960000, 3075.06)
INSERT INTO #p VALUES ('IXIC', '2012-08-31', 3069.64, 3078.52, 3040.59, 3066.96, 1394760000, 3066.96)
INSERT INTO #p VALUES ('IXIC', '2012-08-30', 3066.73, 3067.54, 3045.92, 3048.71, 1216640000, 3048.71)
INSERT INTO #p VALUES ('IXIC', '2012-08-29', 3078.05, 3087.24, 3067.62, 3081.19, 1282900000, 3081.19)
INSERT INTO #p VALUES ('IXIC', '2012-08-28', 3069.4, 3083.19, 3063.65, 3077.14, 1364740000, 3077.14)
INSERT INTO #p VALUES ('IXIC', '2012-08-27', 3083.62, 3085.81, 3068.13, 3073.19, 1383530000, 3073.19)
INSERT INTO #p VALUES ('IXIC', '2012-08-24', 3045.22, 3076.8, 3042.22, 3069.79, 1349740000, 3069.79)
--Calculate the EWMA for DJI with an Alpha of .05
SELECT ticker
,cast(date_trade as date) as [trade date]
,price_close as [Closing Price]
,wct.RunningEWMA(price_close,.05,1,ROW_NUMBER() OVER (ORDER BY date_trade ASC),NULL) as [EWMA]
FROM #p
WHERE ticker = 'DJI'
ORDER BY date_trade ASC
This produces the following result.
ticker trade date Closing Price EWMA
------ ---------- --------------------- ----------------------
DJI 2012-08-24 13157.97 NULL
DJI 2012-08-27 13124.67 13157.97
DJI 2012-08-28 13102.99 13156.305
DJI 2012-08-29 13107.48 13153.63925
DJI 2012-08-30 13000.71 13151.3312875
DJI 2012-08-31 13090.84 13143.800223125
DJI 2012-09-04 13035.94 13141.1522119687
DJI 2012-09-05 13047.48 13135.8916013703
DJI 2012-09-06 13292.00 13131.4710213018
DJI 2012-09-07 13306.64 13139.4974702367
DJI 2012-09-10 13254.29 13147.8545967249
DJI 2012-09-11 13323.36 13153.1763668886
DJI 2012-09-12 13333.35 13161.6855485442
DJI 2012-09-13 13539.86 13170.268771117
DJI 2012-09-14 13593.37 13188.7483325611
DJI 2012-09-17 13553.10 13208.9794159331
DJI 2012-09-18 13564.64 13226.1854451364
DJI 2012-09-19 13577.96 13243.1081728796
DJI 2012-09-20 13596.93 13259.8507642356
DJI 2012-09-21 13579.47 13276.7047260238
DJI 2012-09-24 13558.92 13291.8429897226
DJI 2012-09-25 13457.55 13305.1968402365
DJI 2012-09-26 13413.51 13312.8144982247
DJI 2012-09-27 13485.97 13317.8492733134
DJI 2012-09-28 13437.13 13326.2553096478
DJI 2012-10-01 13515.11 13331.7990441654
DJI 2012-10-02 13482.36 13340.9645919571
DJI 2012-10-03 13494.61 13348.0343623593
DJI 2012-10-04 13575.36 13355.3631442413
DJI 2012-10-05 13610.15 13366.3629870292
DJI 2012-10-08 13583.65 13378.5523376778
DJI 2012-10-09 13473.53 13388.8072207939
DJI 2012-10-10 13344.97 13393.0433597542
DJI 2012-10-11 13326.39 13390.6396917665
DJI 2012-10-12 13328.85 13387.4272071782
DJI 2012-10-15 13424.23 13384.4983468192
DJI 2012-10-16 13551.78 13386.4849294783
DJI 2012-10-17 13557.00 13394.7496830044
DJI 2012-10-18 13548.94 13402.8621988541
DJI 2012-10-19 13343.51 13410.1660889114
DJI 2012-10-22 13345.89 13406.8332844659
DJI 2012-10-23 13102.53 13403.7861202426
DJI 2012-10-24 13077.34 13388.7233142304
DJI 2012-10-25 13103.68 13373.1541485189
DJI 2012-10-26 13107.21 13359.680441093
In this example we will show the closing price and calculate the exponentially weighted moving average for each of the three indices with a = .05.
SELECT cast(date_trade as date) as [trade date]
,p1.price_close as [S&P]
,ROUND(wct.RunningEWMA(p1.price_close,.05,0,ROW_NUMBER() OVER (ORDER BY p1.date_trade ASC),1), 2) as [EWMA]
,p2.price_close as [DJIA]
,ROUND(wct.RunningEWMA(p2.price_close,.05,0,ROW_NUMBER() OVER (ORDER BY p2.date_trade ASC),2), 2) as [EWMA]
,p3.price_close as [NASDAQ]
,ROUND(wct.RunningEWMA(p3.price_close,.05,0,ROW_NUMBER() OVER (ORDER BY p3.date_trade ASC),3), 2) as [EWMA]
FROM #p p1
JOIN #p p2
ON p2.date_trade = p1.date_trade
AND p2.ticker = 'DJI'
JOIN #p p3
ON p3.date_trade = p1.date_trade
AND p3.ticker = 'IXIC'
WHERE p1.ticker = 'GSPC'
This produces the following result.
trade date S&P EWMA DJIA EWMA NASDAQ EWMA
---------- --------- --------- --------- --------- --------- ---------
2012-08-24 1411.13 1411.13 13157.97 13157.97 3069.79 3069.79
2012-08-27 1410.44 1411.10 13124.67 13156.30 3073.19 3069.96
2012-08-28 1409.30 1411.01 13102.99 13153.64 3077.14 3070.32
2012-08-29 1410.49 1410.98 13107.48 13151.33 3081.19 3070.86
2012-08-30 1399.48 1410.40 13000.71 13143.80 3048.71 3069.75
2012-08-31 1406.58 1410.21 13090.84 13141.15 3066.96 3069.62
2012-09-04 1404.94 1409.95 13035.94 13135.89 3075.06 3069.89
2012-09-05 1403.44 1409.62 13047.48 13131.47 3069.27 3069.86
2012-09-06 1432.12 1410.75 13292.00 13139.50 3135.81 3073.15
2012-09-07 1437.92 1412.11 13306.64 13147.85 3136.42 3076.32
2012-09-10 1429.08 1412.96 13254.29 13153.18 3104.02 3077.70
2012-09-11 1433.56 1413.99 13323.36 13161.69 3104.53 3079.04
2012-09-12 1436.56 1415.12 13333.35 13170.27 3114.31 3080.81
2012-09-13 1459.99 1417.36 13539.86 13188.75 3155.83 3084.56
2012-09-14 1465.77 1419.78 13593.37 13208.98 3183.95 3089.53
2012-09-17 1461.19 1421.85 13553.10 13226.19 3178.67 3093.99
2012-09-18 1459.32 1423.72 13564.64 13243.11 3177.80 3098.18
2012-09-19 1461.05 1425.59 13577.96 13259.85 3182.62 3102.40
2012-09-20 1460.26 1427.32 13596.93 13276.70 3175.96 3106.08
2012-09-21 1460.15 1428.96 13579.47 13291.84 3179.96 3109.77
2012-09-24 1456.89 1430.36 13558.92 13305.20 3160.78 3112.32
2012-09-25 1441.59 1430.92 13457.55 13312.81 3117.73 3112.59
2012-09-26 1433.32 1431.04 13413.51 13317.85 3093.70 3111.65
2012-09-27 1447.15 1431.85 13485.97 13326.26 3136.60 3112.89
2012-09-28 1440.67 1432.29 13437.13 13331.80 3116.23 3113.06
2012-10-01 1444.49 1432.90 13515.11 13340.96 3113.53 3113.08
2012-10-02 1445.75 1433.54 13482.36 13348.03 3120.04 3113.43
2012-10-03 1450.99 1434.41 13494.61 13355.36 3135.23 3114.52
2012-10-04 1461.40 1435.76 13575.36 13366.36 3149.46 3116.27
2012-10-05 1460.93 1437.02 13610.15 13378.55 3136.19 3117.27
2012-10-08 1455.88 1437.96 13583.65 13388.81 3112.35 3117.02
2012-10-09 1441.48 1438.14 13473.53 13393.04 3065.02 3114.42
2012-10-10 1432.56 1437.86 13344.97 13390.64 3051.78 3111.29
2012-10-11 1432.84 1437.61 13326.39 13387.43 3049.41 3108.19
2012-10-12 1428.59 1437.16 13328.85 13384.50 3044.11 3104.99
2012-10-15 1440.13 1437.31 13424.23 13386.48 3064.18 3102.95
2012-10-16 1454.92 1438.19 13551.78 13394.75 3101.17 3102.86
2012-10-17 1460.91 1439.32 13557.00 13402.86 3104.12 3102.92
2012-10-18 1457.34 1440.23 13548.94 13410.17 3072.87 3101.42
2012-10-19 1433.19 1439.87 13343.51 13406.83 3005.62 3096.63
2012-10-22 1433.81 1439.57 13345.89 13403.79 3016.96 3092.65
2012-10-23 1413.11 1438.25 13102.53 13388.72 2990.46 3087.54
2012-10-24 1408.75 1436.77 13077.34 13373.15 2981.70 3082.25
2012-10-25 1412.97 1435.58 13103.68 13359.68 2986.12 3077.44
2012-10-26 1411.94 1434.40 13107.21 13347.06 2987.95 3072.96