In the example below we show you how to calculate simple moving average using Excel and a shell script. As more data is added to the file, both the shell script and the Excel formula will automatically use the specified number of the latest data points to calculate the moving average. The sample input file (data.txt) contains two tab-separated columns with dates and corresponding values, as shown below.

2011-08-01      12.3
2011-08-02      16.2
2011-08-03      13.5
2011-08-04      17.5
2011-08-05      12.0
2011-08-06      15.1
2011-08-07      17.2
2011-08-08      19.2
2011-08-09      13.3
2011-08-10      13.2
2011-08-11      16.7
2011-08-12      17.4
2011-08-13      11.5
2011-08-14      14.6
2011-08-15      15.6
2011-08-16      16.8

The following shell script will calculate simple moving average for the past seven data points.

i=7 ; sum=0 ; mavg=0 ; 
tail -$i data.txt | awk '{print $2}' | while read line
do
   sum=$(echo "scale=1;${sum}+${line}"|bc -l)
   echo $sum > /tmp/sum
done
mavg=$(echo "scale=1;`cat /tmp/sum`/${i}"|bc -l)
echo $mavg
rm /tmp/sum

In this script variable “i” represents the number of data points and the “scale” varible represents the number of decimal places.

To make the same calculation in Excel, we need to import the data file into a spreadsheet as a tab-separated file. In our example column “A” will contain the dates and column “B” will contain the corresponding data values, as shown below.

The formula we use to calculate simple moving average for the past seven days goes something like this:

=AVERAGE(OFFSET($B$1,COUNT($B$1:$B$100)-7,0,7,1))

Where “B” is the table column containing data values; “$B$1” is the beginning of the column; $B$100 is an arbitrary end of the column (you can put $B$1000000 and it will still work fine); “7” is the number of data points used to calculate the moving average; and “1” is the step between the data points.