|
A simple moving average calculates the average price for a set number of time periods. To
calculate a 10 day moving average you would calculate the sum of the closing price for a period
of 10 days and divide by 10. On the eleventh day you would add the price for the eleventh day
to the average and subtract the price for the first day.
Excel has a built in trend line feature. If you click on the chart you have created, and
choose Add Trend Line from Excel’s Chart menu you can add a simple moving average along with
other types of trend lines. The problem with this feature is that if you want to change the
length of the time periods for your trend line you need to go through several steps to
complete the process. Using a dynamic moving average the trend line is adjusted with a single
cell entry.
In order to reduce the lag in a moving average many technical market analysts use exponentially weighted
moving averages (EMA). Exponential moving averages reduce the lag by applying more weight to recent prices
relative to older prices. This type of calculation makes the moving average more sensitive to a change in
trend.
The formula used to calculate an EMA uses a smoothing constant. The smoothing constant
applies the appropriate weighting for the most recent price relative to the previous day’s
EMA calculation. The formula for the smoothing constant is:
N is equal to the number of time periods for the EMA. If you are calculating a 10 day EMA,
N would be 10.
The complete formula for the calculation of the EMA is:
| =(Today’s Last – Yesterday’s EMA) x (Smoothing constant) + Yesterday’s EMA |
In our mini app we replaced the variables in the formulas with references to the cells containing
the values we want to calculate. We named the cell which contains the number of periods in the moving
average MAV, and reference it in the formulas which calculate the moving average. In our mini app the
cells containing the formula for calculating the EMA are in column I. Since the number of periods in
the smoothing constant refers to a single cell (MAV) when the value in the cell changes so do values
displayed by the formulas which reference that cell. Setting up the formula in this way allows you
to recalculate a new moving average quickly.
Calculating Buy and Sell Signals
We used Excel’s IF and AND functions to calculate Buy and Sell signals based on whether the
instrument’s closing price crossed above or below its exponential moving average. Although it
may appear complicated, the formula to calculate a buy signal is pretty straightforward. If
yesterday’s closing price is less than its EMA and the current closing price is greater than its
EMA then it would indicate the EMA crossed to the upside and the price action would generate a
buy signal. If these conditions were met, then the word Buy appeared in the cell. If they were
not met, then the cell remained blank. The buy signal formula is:
| =IF(AND($F8<$I8,$F7>$I7),"Buy","") |
The closing prices are in column F and the EMA is in column I. We entered a similar formula
in the adjoining column to calculate a sell signal.
Some Design Tips
A chart is most visually appealing and easy to analyze when it is large and the data it
displays can easily be viewed. If it is sufficiently large it also hides the numerical data
displayed in the table. To overcome this problem we added Hide Chart / Show Chart button which
runs a macro which allows you to hide or display the stock chart. We added this feature for the
sake of convenience and to enhance the appearance our mini-app. Along with being able to
visually see when the last price crossed above or below the moving average, with a single
mouse click you can view the actual buy and sell signals displayed in the cells on the analysis
worksheet.
We created the button using Excel’s Control Toolbox toolbar. To view the code, simultaneously
press Alt F11 to open the Visual Basic Editor and double click on the Analysis worksheet. The
macro code is attached to the button click event. The code does nothing more than change the
chart’s visible property to true or false and also changes the text property from hide chart
to show chart.
Another example of dynamic updating is the column heading for the moving average column.
Each time you change the period for the moving average, the heading on the column changes to
display the changed time period for the moving average. This is dynamic feature is created by
using Excel’s concatenate operator (&) in a formula to create the label for the moving average
rather than entering text. It references the cell containing the time period for the moving
average and adds the text label.
|
