Table Of Content
In this three-part series or articles “Technical Analysis in Excel” we will explore how traders can use Excel to apply technical analysis (TA) to historical market data. This will include computation of some of the most popular technical analysis indicators and implementation of a trading strategy backtesting spreadsheet (in Part III). Backtesting will involve generation of buy and sell signals based on TA indicators and computation of strategy P&L. We’d like to point out upfront that all computations in these articles will be performed using standard Excel functions available in Excel 2011 and later. We will not be using any VBA/custom Excel macros. This is done on purpose to keep spreadsheets simple and functionality understandable by non-programmers.
In the first part of this article series we will create an Excel spreadsheet where we will use formulas some common technical analysis indicators such as: Simple Moving Average, Bollinger Bands, and Exponential Moving Average. We’ll explain the formulas and include step-by-step instructions below. In addition, we are providing a spreadsheet we’ve created by following steps listed in this article so that you can use it for your own market data analysis or as basis for building your own spreadsheets.
Sample Excel File
Excel file (download) containing formulas for calculation of simple moving average, Bollinger Bands, and exponential moving average as described in this post.
For this example we’ve got a CSV file with 6 months of hourly SPY data, covering Sep 3, 2013 – Feb 28, 2014. SPY is an ETF tracking S&P500 index. We have nearly 2000 data points in this file. The file contains OHCL price columns, volume, and timestamp column. Disclaimer: this file has been generated using IB Data Downloader.
Data file: historical_data_SPY_1hour_20140301 (text file – to download – right-click and select “Save Linked File As…”)
Simple Moving Average
Simple Moving Average (SMA) is simply the average price over last N number of bars. Let’s calculate SMA for the close prices from our sample data file.
We’ll be calculating a 20-day moving average based on the SPY close price (column D). Let’s add column header “SMA-20” in column G and we type in the following formula value in cell G21 (since row 21 is the first one that has enough data to calculate 20-day SMA):
After hitting return to save the formula you should see value ‘164.57’ or close to that in cell G21. In order to calculate SMA-20 for all of the remaining cells below – just select cell G21, move cursor over cell and double-click the small square in the lower-right corner of that cell. You should now see values in column G calculated for the remainder of SPY prices.
Generalizing SMA Calculation
Now we have calculated 20-day simple moving average values in column G . It’s great, but what if we want to calculate 50-day, or 200-day SMA now? Updating formula values every time you want to change SMA range is pretty tedious and error-prone. Let’s make our calculation more generic by adding a “length” parameter. We can start off by storing SMA range parameter in a separate cell so that we can reference it in or formula.
Here are the steps we followed to implement a generic SMA calculation in our spreadsheet:
- Let’s start off by creating a little table on the side where we can store some input parameter values for our indicators.
- In cell O1 let’s type “Variable Name”, in cell P1 let’s type “Value”.
- In cell O2 let’s type name of our variable: “PERIOD”.
- In cell P2 we specify value of the “PERIOD” variable which we’ll be using to specify period length for our generalized SMA calculation. Changing this variable will trigger recalculation of SMA with the current period value. Let’s use value 14 for now.
- Let’s type column header value “SMA” in cell H1; column H will contain values for our generic SMA indicator.
- In cell H2 enter this formula:
Let’s dissect this formula. We are now using value of our PERIOD variable from cell P2. We had to add $ in front of column and row numbers to freeze reference to cell P2 as we copy SMA formula to other cells in column H. We’ve also replaced absolute reference to the Close column price range with the OFFSET Excel function. OFFSET returns a range of cells based on the offset in terms of number rows and columns from a given “reference” cell. First parameter is the reference cell (in our case H2 itself), second is an expression calculating the first row of the range based on the value of length parameter ($P$2), 3rd parameter is the column offset to the Close column (-4), negative value represents offset to the left while positive is offset to the right of the reference cell, and the last function parameter with value 1 represents the width of the range returned by OFFSET function, which in our case is just one column: D (CLOSE).
- Save the formula in cell in H2 and expand it to the rest of cells in column H by double-clicking the little square in lower-right corner of the cell, or dragging the formula down.
Removing Formula Errors
Now, you will notice that first several rows in the column have error value #REF!. This happens because there are not enough rows in our data set to calculate the SMA value, and the range returned by OFFSET function goes over the edge of the worksheet for some rows. There exists a number of various techniques to hide error values in excel. Some of them involve formulas which return blank or zero values if a cell value contains an error. While this is perfectly valid technique- it complicates cell formulas and makes them hard to read. Instead, we’ll use conditional formatting to simply hide error values be changing foreground color to white. To change cell’s font color to white and use no error highlighting follow these instructions:
- Select columns H-N
- In Excel: Home -> Conditional Formatting -> Highlight Cell Rules -> More Rules.
- In the “New Formatting Rule” dialog select “Errors” and in “Format with…” select “Custom format”, then set Fill color to white and font color to white as well.
Bollinger Bands is a simple but useful indicator providing valuable information on historical price volatility of a financial instrument, as well as current price deviation from a moving average. When price moves become more volatile – the bands widen, in the periods of relative calm – they come closer together. The relative position of the current price to the bands can also be used to estimate whether market is overbought or oversold. If the current price is close to or crossed upper band – the price is considered in overbought territory, while price close to/crossed lower band – underlying market is considered oversold.
Bollinger Bands indicator could be calculated using either simple moving average or exponential moving average as the basis. Bollinger Bands consists of three data series: moving average (simple or exponential) and two standard deviation (boundary) lines, one above, and one below the moving average, usually at 2 standard deviations from the moving average. Exponential moving average (covered below) gives more weight to the more recent price action, while Simple moving average provides a more stable and less jittery indicator. There are a total of 2 input parameters: 1) moving average period (number of bars), 2) number of standard deviations for the upper band lower bands. In this example we’ll use simple moving average we already calculated in column H (see instructions in the section above). All that’s remaining is to add columns for upper and lower bands.
- We are still using 14-day moving average period value. The first row that has enough data for 14-day SMA is row 15 (since row 1 is used for column header). The upper band will be in column I, so in cell I15 we type the following formula:
In this formula we are simply adding two standard deviations of the Close prices from cells D2:D15 to the SMA value.
- And for lower band in column I we enter the following:
Here the only difference from the previous formula is that we are subtracting two standard deviations from SMA. Excel formula STDEV() calculates standard deviation for a series of values. In this case we are multiplying value by 2 to get 2 standard deviations, and adding/subtracting the result from the moving average to generate the upper/lower band values.
- To expand the formulas – just roll over and double-click on a small square in the lower-right corner of the cell to replicate formula for the rest of the data range.
Generalized Bollinger Band Computation
Now , how about generalizing the Bollinger Band formula so that we don’t have to update our formulas every time we want to calculate Bollinger bands for different number of standard deviations from MA or when we change moving average length.
- Let’s add another parameter to our generic variables table on the right of the spreadsheet. Let’s type “Std devs:” in cell O3, and 2.0 in P3.
- Next, let’s add the following formula in I15:
In this formula we’ve replaced 2 with $P$3 – which points to our variable in cell P3 containing number of standard deviations for the bands, and calculate offset based on the PERIOD variable in cell P2.
- Similarly in cell J15 let’s now enter this formula:
The only difference from the formula in the previous step is that we’ve replaced + after H15 with – (minus), to subtract number of standard deviations from SMA, and we had to change offset to the price columnd , notice -6, instead of -5 in the “cols” parameter to the OFFSET function to refer to column D (CLOSE).
- Don’t forget to copy new formulas in cells I15 and J15 to the rest of the respective column cells.
You can now change values of “PERIOD” and “Std devs” variables in cells P2 & P3, and have SMA and Bollinger Band values automatically recalculated.
Bollinger Bands Chart in Excel
Watch this video with instructions for adding a Bollinger Band chart to the spreadsheet we created above.
Exponential Moving Average
Exponential Moving Average (EMA) is type of moving average that is similar to a simple moving average, except that more weight is given to the latest data. The exponential moving average is also known as “exponentially weighted moving average”.
- We’ll use column K to calculate EMA.
- Let’s set our PERIOD value to 1 (cell P2), so that we could enter formula at the top of our sheet and have some values we can see entering the formulas. We can set PERIOD to any value after we are done and have EMA (and SMA) automatically recalculated.
- In cell K2 we set the first value of the EMA series to be simply equal to the Close value (D2) in the same row, just because we need to “seed” EMA computation with some sensible value.
- Next, in cell K3 we enter a standard EMA formula which uses the industry-standard exponent function 2/(1+number of periods in MA). To better understand the math behind this refer to this page.
In this formula we multiply row’s Close price (D3) by the exponent function, using $P$2 to reference our “number of periods” variable, and add to the result the previous EMA value (K2), multiplied “1- the exponent”. This is the standard EMA formula.
- Now expand the formula to the rest of the column by clicking a square in the lower right of cell K3.
- We can now change PERIOD value to any other number, make sure your conditional formatting rule is updated to hide error values displayed in cells that don’t have enough data going back to calculate their values.
Part I Conclusion
In this first part of our 3-part series we calculated Simple Moving Average, Bollinger Bands, and Exponential Moving Average technical analysis indicators for our sample historical data set. In the next part we’ll cover two of the most famous technical analysis indicators: MACD and RSI.
Before you continue reading this article series we’d like to bring your attention to a couple of books we hand-picked from a large number of volumes available on the subjects of technical analysis and trading with Microsoft Excel. We found that the selections we listed below provide invaluable fundamental information on using technical analysis and Excel-based trading idea generation, testing, and execution. Combining material described in these books will enable you to develop and test your own trading systems and take them to markets sooner and with more confidence.