Login     Register

        Contact Us     Search

XLeratorDB/windowing Documentation

SQL Server running Exponentially Weighted Moving Average


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
 


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service