Calculating Moving Average with Excel: Dynamic Lookback (Window) Duration Based on Changing Values

Introduction

A moving average is a widely used statistical tool in finance, economics, and engineering to analyze time-series data. It helps to smooth out short-term fluctuations and highlight long-term trends or cycles. In this article, we will learn how to calculate a moving average with Excel using a dynamic lookback (window) duration based on changing values in a dataset.

Context

A moving average can be calculated by taking the average of a certain number of data points in a dataset. The number of data points used in the calculation is called the lookback or window duration. In a fixed lookback duration, the same number of data points is used for each calculation. However, in a dynamic lookback duration, the number of data points used in the calculation changes based on time or other factors.

Key Concepts

• Time-series data: Data that is collected or recorded over time, usually at regular intervals.
• Moving average: A statistical tool used to analyze time-series data by taking the average of a certain number of data points.
• Lookback or window duration: The number of data points used in the calculation of a moving average.
• Dynamic lookback duration: A lookback duration that changes based on time or other factors.

Subtitles

1. Setting up the dataset
2. Creating a dynamic lookback duration
3. Calculating the moving average
4. Visualizing the results
5. References

Setting up the Dataset

Before we can calculate a moving average, we need to set up the dataset. In this example, we will use a dataset of daily stock prices for a hypothetical company. The dataset includes the date, opening price, closing price, and volume traded.

To set up the dataset, we need to:

1. Open a new Excel workbook.
2. Enter the dataset in columns A to D, starting from row 2.
3. Format the date column (column A) as a date.
4. Format the number columns (columns C and D) as numbers with two decimal places.

Here's an example of how the dataset should look like:

A               B           C           D
1   Date         Open        Close       Volume
2   1/1/2022     100         105         1000000
3   1/2/2022     105         110         1200000
4   1/3/2022     110         115         1300000
5   1/4/2022     115         120         1400000
6   1/5/2022     120         125         1500000
7   1/6/2022     125         130         1600000
8   1/7/2022     130         135         1700000
9   1/8/2022     135         140         1800000
10  1/9/2022     140         145         1900000
11  1/10/2022    145         150         2000000
12  1/11/2022    150         155         2100000
13  1/12/2022    155         160         2200000
14  1/13/2022    160         165         2300000
15  1/14/2022    165         170         2400000
16  1/15/2022    170         175         2500000
17  1/16/2022    175         180         2600000
18  1/17/2022    180         185         2700000
19  1/18/2022    185         190         2800000
20  1/19/2022    190         195         2900000
21  1/20/2022    195         200         3000000
22  1/21/2022    200         205         3100000
23  1/22/2022    205         210         3200000
24  1/23/2022    210         215         3300000
25  1/24/2022    215         220         3400000
26  1/25/2022    220         225         3500000
27  1/26/2022    225         230         3600000
28  1/27/2022    230         235         3700000
29  1/28/2022    235         240         3800000
30  1/29/2022    240         245         3900000
31  1/30/2022    245         250         4000000
32

Creating a Dynamic Lookback Duration

To create a dynamic lookback duration, we need to determine the number of data points to use in the calculation based on time. In this example, we will use a lookback duration of 5 days, 10 days, and 20 days.

To create a dynamic lookback duration, we need to:

1. Insert a new column (column E) to the right of the volume column (column D).
2. In the first row (row 2) of the new column (column E), enter the formula =IF(A2<TODAY()-20,A2,TODAY()).
3. In the second row (row 3) of the new column (column E), enter the formula =IF(A3<E2,A3,E2).
4. Drag the formula in the second row (row 3) down to the last row of the dataset.

Here's an example of how the dynamic lookback duration should look like:

A               B           C           D           E
1   Date         Open        Close       Volume      Lookback
2   1/1/2022     100         105         1000000     1/1/2022
3   1/2/2022     105         110         1200000     1/2/2022
4   1/3/2022     110         115         1300000     1/3/2022
5   1/4/2022     115         120         1400000     1/4/2022
6   1/5/2022     120         125         1500000     1/5/2022
7   1/6/2022     125         130         1600000     1/6/2022
8   1/7/2022     130         135         1700000     1/7/2022
9   1/8/2022     135         140         1800000     1/8/2022
10  1/9/2022     140         145         1900000     1/9/2022
11  1/10/2022    145         150         2000000     1/10/2022
12  1/11/2022    150         155         2100000     1/11/2022
13  1/12/2022    155         160         2200000     1/12/2022
14  1/13/2022    160         165         2300000     1/13/2022
15  1/14/2022    165         170         2400000     1/14/2022
16  1/15/2022    170         175         2500000     1/15/2022
17  1/16/2022    175         180         2600000     1/16/2022
18  1/17/2022    180         185         2700000     1/17/2022
19  1/18/2022    185         190         2800000     1/18/2022
20  1/19/2022    190         195         2900000     1/19/2022
21  1/20/2022    195         200         3000000     1/20/2022
22  1/21/2022    200         205         3100000     1/21/2022
23  1/22/2022    205         210         3200000     1/22/2022
24  1/23/2022    210         215         3300000     1/23/2022
25  1/24/2022    215         220         3400000     1/24/2022
26  1/25/2022    220         225         3500000     1/25/2022
27  1/26/2022    225         230         3600000     1/26/2022
28  1/27/2022    230         235         3700000     1/27/2022
29  1/28/2022    235         240         3800000     1/28/2022
30  1/29/2022    240         245         3900000     1/29/2022
31  1/30/2022    245         250         4000000     1/30/2022
32

Calculating the Moving Average

Now that we have a dynamic lookback duration, we can calculate the moving average. In this example, we will calculate the moving average of the closing price.

To calculate the moving average, we need to:

1. Insert a new column (column F) to the right of the lookback column (column E).
2. In the first row (row 2) of the new column (column F), enter the formula =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E2,A$2:A$32,">="&E2-5).
3. In the second row (row 3) of the new column (column F), enter the formula =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E3,A$2:A$32,">="&E3-5).
4. Drag the formula in the second row (row 3) down to the last row of the dataset.

Here's an example of how the moving average should look like:

A               B           C           D           E           F
1   Date         Open        Close       Volume      Lookback    Moving Average
2   1/1/2022     100         105         1000000     1/1/2022     =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E2,A$2:A$32,">="&E2-5)
3   1/2/2022     105         110         1200000     1/2/2022     =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E3,A$2:A$32,">="&E3-5)
4   1/3/2022     110         115         1300000     1/3/2022     =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E4,A$2:A$32,">="&E4-5)
5   1/4/2022     115         120         1400000     1/4/2022     =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E5,A$2:A$32,">="&E5-5)
6   1/5/2022     120         125         1500000     1/5/2022     =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E6,A$2:A$32,">="&E6-5)
7   1/6/2022     125         130         1600000     1/6/2022     =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E7,A$2:A$32,">="&E7-5)
8   1/7/2022     130         135         1700000     1/7/2022     =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E8,A$2:A$32,">="&E8-5)
9   1/8/2022     135         140         1800000     1/8/2022     =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E9,A$2:A$32,">="&E9-5)
10  1/9/2022     140         145         1900000     1/9/2022     =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E10,A$2:A$32,">="&E10-5)
11  1/10/2022    145         150         2000000     1/10/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E11,A$2:A$32,">="&E11-5)
12  1/11/2022    150         155         2100000     1/11/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E12,A$2:A$32,">="&E12-5)
13  1/12/2022    155         160         2200000     1/12/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E13,A$2:A$32,">="&E13-5)
14  1/13/2022    160         165         2300000     1/13/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E14,A$2:A$32,">="&E14-5)
15  1/14/2022    165         170         2400000     1/14/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E15,A$2:A$32,">="&E15-5)
16  1/15/2022    170         175         2500000     1/15/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E16,A$2:A$32,">="&E16-5)
17  1/16/2022    175         180         2600000     1/16/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E17,A$2:A$32,">="&E17-5)
18  1/17/2022    180         185         2700000     1/17/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E18,A$2:A$32,">="&E18-5)
19  1/18/2022    185         190         2800000     1/18/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E19,A$2:A$32,">="&E19-5)
20  1/19/2022    190         195         2900000     1/19/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E20,A$2:A$32,">="&E20-5)
21  1/20/2022    195         200         3000000     1/20/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E21,A$2:A$32,">="&E21-5)
22  1/21/2022    200         205         3100000     1/21/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E22,A$2:A$32,">="&E22-5)
23  1/22/2022    205         210         3200000     1/22/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E23,A$2:A$32,">="&E23-5)
24  1/23/2022    210         215         3300000     1/23/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E24,A$2:A$32,">="&E24-5)
25  1/24/2022    215         220         3400000     1/24/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E25,A$2:A$32,">="&E25-5)
26  1/25/2022    220         225         3500000     1/25/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E26,A$2:A$32,">="&E26-5)
27  1/26/2022    225         230         3600000     1/26/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E27,A$2:A$32,">="&E27-5)
28  1/27/2022    230         235         3700000     1/27/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E28,A$2:A$32,">="&E28-5)
29  1/28/2022    235         240         3800000     1/28/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E29,A$2:A$32,">="&E29-5)
30  1/29/2022    240         245         3900000     1/29/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E30,A$2:A$32,">="&E30-5)
31  1/30/2022    245         250         4000000     1/30/2022    =AVERAGEIFS(C$2:C$32,A$2:A$32,"<="&E31,A$2:A$32,">="&E31-5)
32

Visualizing the Results

To visualize the results, we can create a line chart that shows the closing price and the moving average.

To create a line chart, we need to:

1. Select the closing price column (column C) and the moving average column (column F).
2. Go to the "Insert" tab and click on the "Line Chart" button.
3. Customize the chart as needed.

Here's an example of how the line chart should look like:

References

• Investopedia. (n.d.). Moving Average. Retrieved from https://www.investopedia.com/terms/m/movingaverage.asp
• Excel Easy. (n.d.). AVERAGEIFS function. Retrieved from https://www.excel-easy.com/functions/averageifs.html
• Microsoft. (n.d.). Create a line chart. Retrieved from https://support.microsoft.com/en-us/office/create-a-line-chart-7314786e-6b00-43f6-a7d2-6c175d3d2882

Summary

In this article, we learned how to calculate a moving average with Excel using a dynamic lookback (window) duration based on changing values in a dataset. We set up the dataset, created a dynamic lookback duration, calculated the moving average, and visualized the results using a line chart.

By using a dynamic lookback duration, we can better analyze time-series data and identify trends or cycles that may not be apparent using a fixed lookback duration. This technique can be applied to various fields, such as finance, economics, and engineering, where time-series data is commonly used.

• Types of references:
  • Books:
    • Investopedia. (2021). Investopedia Academy: Technical Analysis. John Wiley & Sons.
  • Articles:
    • Excel Easy. (n.d.). AVERAGEIFS function.
    • Microsoft. (n.d.). Create a line chart.
  • Online resources:
    • Investopedia. (n.d.). Moving Average.