Moving Average is the classical method of time series decomposition to estimate the trend-cycle. You can calculate it for any period of time.
Variations of moving average are:
- Simple Moving Average (SMA)
- Cumulative / Weighted Moving Average (EMA)
These types of moving averages can be used to identify the direction of the trend or define potential support and resistance levels.
The reason to use a running average is to smooth out short-term fluctuations and highlight longer-term trends or cycles.
Using Moving Average are mostly the following:
- Technical analysis of financial data like stock prices, returns or trading volumes and will often use a 50 or 200 day moving average to help them see trends in the stock market and forecast where the stocks are headed.
- It is used in economics to examine the gross domestic product, employment or other macroeconomic time series.
- It is used as a Salespeople who often need to estimate the sales of upcoming months by smoothening sales data to see a trend.
The first example shows how is calculated moving average for Internet Sales in Adventure Works DW.
/****** Calculating Quartaly and Average sale from FactInternetSales - Adventure Works DW2017 ******/ SELECT CAST (datepart (YEAR, OrderDate) AS varchar) + '-'+ cast(datepart ([QUARTER], OrderDate)AS varchar) Year_Quarter, sum([SalesAmount]) AS SalesAmount, avg(sum([SalesAmount])) OVER ( ORDER BY CAST (datepart (YEAR, OrderDate) AS varchar) + '-'+ cast(datepart ([QUARTER], OrderDate)AS varchar) ROWS BETWEEN 3 preceding AND CURRENT ROW) QTLY_Moving_Average FROM [AdventureWorksDW2017].[dbo].[FactInternetSales] GROUP BY CAST (datepart (YEAR, OrderDate) AS varchar) + '-'+ cast(datepart ([QUARTER], OrderDate)AS varchar) ORDER BY 1
The second example shows calculating SMA200 (Simple Moving Average for the last 200 days) for Pandora (PNDORA).
First of all, we will have to create a table to hold stock prices for the last 200 days.
/* Script for crating table Stock_Prices table */ CREATE TABLE [dbo].[StockPrices]( [Ticker] [nchar](10) NULL, [Date] [date] NULL, [Close] [float] NULL, [Open] [float] NULL, [High] [float] NULL, [Low] [float] NULL, [Volume] [bigint] NULL ) ON [PRIMARY] GO
Copy/Paste records from Excel Sheet “Pandora AS.xls”. Data is available for download below in Excel format.
We are now going to comparing Close price with SMA200, trading volumes and showing a possible correlation of those metrics.
--********* Comparing Close price with SMA200, trading volumes and showing possible corelation of those metrics. ***************** SELECT [date] as Date, sum([Close]) AS [Close], avg(sum([Close])) OVER ( ORDER BY [date] ROWS BETWEEN 200 preceding AND CURRENT ROW) as SMA200, -- Using "preceding function to calculate AVG 200 days back avg(sum(Volume)) OVER ( ORDER BY [date] ROWS BETWEEN 200 preceding AND CURRENT ROW) as SMA200_Volume -- Using "preceding function to calculate AVG 200 days back FROM [PLAYGROUND].[dbo].[StockPrices] GROUP BY [date] ORDER BY 1
This graf is included in Excel workbook together with data if you would like to reuse layout.
In this article, we:
• walked through basic definition of Moving Average/Running Average elements.
• Vent through two different usage of Moving Average calculation with SQL examples.