Interactive Moving Average Chart

This tutorial shows an interactive moving average trend chart. It is based on the example found in Chart_Mov_Avg.XLS.

1. Moving Average Charts

Detailed process data can include diurnal (daily), weekly, monthly and seasonal variations as well as spike events that can make evaluation of trends challenging. Excel's moving average tool can help to evaluate underlying data trends by averaging out the dynamic fluctuations. This example chart shows daily flow data for 18 months. This data has a weekly as well as a seasonal pattern.

We can add a moving average with Excels Chart Add Trendline window to help us separate the longer term trends from the day-to-day fluctuations.

Let's see what a 30 day moving average looks like.

The chart on the left shows both the detailed data and the 30 day moving average. The chart on the right shows just the 30 day moving average.

To me, the chart on the right gives a clearer of the long term trend.

The example file lets you vary the time period, moving average period and to toggle the detailed data on and off. Why not try it to get some ideas about how to set up interactive charts.

2. Adding Interactive Capabilities

When analyzing a time series data set, it is often helpful to adjust one or more parameters and see what happens. In our moving average example, we may want to change date range, date format or interval on x axis, moving average time period or display of moving average line or data line. It would be nice to be able to make a simple change and have Excel redraw our chart so that we can interact with the chart, cycle through a series of changes to help us see if there are any underlying patterns that may be important to us.

We can build interactive charts with a combination of Excel's event handling capabilities and VBA. In this example, I created a control table where all of the adjustable moving average chart variable are stored Cells B3:C3 store the X axis minimum and maximum chart dates. Cells C4:C5 store the X axis major unit and number format.

Cell C6 stores the moving average period and cell C7 has a On/Off list to allow User to turn data display on or off.

Control X Axis Min - Max Values provides information on how to set the min and max x axis dates for a trend chart.

The Chart_Mov_Avg.XLS. workbook includes VBA code to generate the interactive moving average chart. Reviewing this code may give you some ideas on how you could automate your charts.

A worksheet change event procedure is used to monitor the colored cells noted above. Any change in one of the colored cells triggers a worksheet change event and call the appropriate procedure to update the chart. This way, the user can make a change in date or interval and the chart is immediately updated with the new set of chart conditions.