Aveek Das
Moving Average in SQL and Excel

Calculate Moving Averages in SQL, Power BI and MS Excel

April 21, 2020 by

In this article, I’m going to talk in detail about the moving average in SQL and how to find out the same in Power BI and Excel. This might be a new topic for many newbies who have started out in this field, but I’m sure this will help anyone who is trying to work on smoothing averages in SQL or in Power BI.

Moving Average in SQL or Power BI, as it goes by the name, is a type of average function that is moving; in other words, it is calculated over a period. This is an important scenario while dealing in finance where often analysts tend to work on smoothing the stock prices, for example, over a period and find out the trend of the prices. This trend then aids in defining whether the average is an upward or downward moving trend. This article will focus on how to calculate the moving average in SQL and then we will look at an example to implement the same in Power BI and Excel as well. It is also known by Rolling Average or Running Average.

For the sake of this article, we will try to analyze the share price of Facebook, the data of which is easily available to download from yahoo finance website. I have downloaded the data for the last 5 years and saved it in a CSV file.

Facebook Stock Data (Yahoo Finance) to calculate Moving Average in SQL

Figure 1 – Facebook Stock Data (Yahoo Finance)

Moving Average in SQL

To begin with, let us first import the CSV file into SQL Server so that we can execute our queries accordingly.

Right-click on the Database on which you wish to import the data and select Tasks and then select Import Flat File. Proceed through the dialog boxes and get it imported into the database.

Import Flat File Into SQL Server to calculate Moving Average in SQL

Figure 2 – Import Flat File into SQL Server

Once the data is imported into a table in SQL, we can go ahead and execute our queries. In this article, I’m going to use the WINDOW function in SQL – the OVER clause to calculate the average of every row with consideration of the values from the records before and after that specific row.

Now that our data is ready, let us try to calculate a moving average in SQL of 5 days on the closing prices. This means each row will consider 2 rows both before and after the current record, calculate the sum of the price and then divide it by 5. For the first row, since there are no rows before that, the logic remains the same, but only the 2 later records are calculated. Similar is the case for the last record where only the 2 previous records will be calculated since there are no records after that.

Five Day Moving Average in SQL

Figure 3 – Five Day MA

This is not all. This technique can be applied to calculate the moving average in SQL for different periods based on the requirement. If we want to calculate the moving average of 15 days, such that it can include 7 previous and 7 next days, we can just rewrite the query as follows.

Fifteen Day Moving Average in SQL

Figure 4 – Fifteen Day MA

As you can see in the figure above, the average is now based upon 7 previous and 7 next records, including the current one.

Moving Average in Power BI

Power BI as we all know is one of the most fantastic tools in the visualization world. One of the main advantages of using Power BI is that it helps us with some pre-defined calculations, which we can create just with a few clicks without writing a single line of DAX. Of course, you can write your own DAX in case you want to customize; however, the New Quick Measure really does the tough job.

Let us first fetch the data into the Power BI data model. Select Get Data and then from SQL Server. Provide the necessary details and get the data imported into the data model. Click Load once completed and the data will be imported.

Importing Data into Power BI

Figure 5 – Importing Data into Power BI

Once the data is imported, click on the “Line and Clustered Column Chart” and drag and drop the Date to the Axis and the Close to the Column Values pane. Also, an important thing to note here is that since the dataset has five years of data, we will only visualize it by months and remove the Day from the hierarchy.

Building the chart

Figure 6 – Building the chart

Once the visualization is ready, the next step is to create the new calculated measure that will store the moving average. Right-click the Date hierarchy and select New Quick Measure from the context menu.

New Quick Measure

Figure 7 – New Quick Measure

In the new dialog box that appears, select the Calculation as Rolling Average and the Base Value as Average of Close (since we want to calculate the MA based on the closing price). Select the period for which the rolling average is to be calculated. In this example, I’m going to calculate it based on 7 months before and after the actual month, basically a fifteen-month moving average. Click OK once done.

Configuring Rolling Average

Figure 8 – Configuring Rolling Average

Once you finish defining the configuration, a new measure will be created by the name “Average of Close Rolling Average”. You can also see the underlying DAX formula that the Power BI engine automatically generates for us.

Rolling Average Formula in DAX

Figure 9 – Rolling Average Formula in DAX

Once the new measure is created, just drag and drop it into the Line Values pane. You can see a new line will be displayed on the chart which will give us the running average of the fifteen months.

Fifteen Months Rolling Average

Figure 10 – Fifteen Months Rolling Average

Moving Average in Excel

Excel has always been a go-to tool for many people for a long time now, and hence, it is also extremely important to be able to perform such kind of calculations with ease. Excel provides an add-in for Data Analysis purposes, which needs to be imported into Excel before performing the analysis.

Open the CSV file that you have downloaded earlier from the yahoo portal and save it as an excel file. Click on Home on the ribbon bar and then select Options.

Select Options in Excel

Figure 11 – Select Options in Excel

In the Excel Options dialog box that opens, select Excel Add-ins against Manage and click on Go.

Excel Plugins

Figure 12 – Excel Plugins

In the Add-ins dialog box, select the Analysis ToolPak and click OK.

Analysis ToolPak

Figure 13 – Analysis ToolPak

From the Data tab, click on the Data Analysis button under the Analysis pane in the ribbon.

Data Analysis Tab

Figure 14 – Data Analysis Tab

Select Moving Average and click OK.

Data Analysis Options

Figure 15 – Data Analysis Options

In the next dialog box that appears, select the Input Range from the column values in the Excel sheet. Define how many intervals should the rolling average has to be calculated. For this example, I want to smooth the average for 60 intervals. Click OK once done. This will add a new column, which is defined by the Output Range cells. Let the name for this column be MovingAverage.

Defining Options

Figure 16 – Defining Options

Once the new field is added, select the entire dataset and click on the Insert tab. Select the Line Chart, as shown in the figure below and plot it on the sheet.

Selecting the Line Chart

Figure 17 – Selecting the Line Chart

Two lines will be displayed in the chart, as shown in the figure below. The blue line represents the original value from the dataset, whereas the orange line represents the rolling average of the selected periods. In this way, we can easily smooth out the original curve and plot a rolling average calculation in Excel.

Moving Average in SQL and Excel

Figure 18 – Rolling Average in Excel

Conclusion

In this article, we have seen what a moving average in SQL is and how to calculate one. I have also explained how to implement the same with examples in Power BI and Excel.

Aveek Das
Latest posts by Aveek Das (see all)
Excel, PowerBI

About Aveek Das

Aveek is an experienced Data and Analytics Engineer, currently working in Dublin, Ireland. His main areas of technical interest include SQL Server, SSIS/ETL, SSAS, Python, Big Data tools like Apache Spark, Kafka, and cloud technologies such as AWS/Amazon and Azure. He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums. In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn View all posts by Aveek Das

24,743 Views