Table Of Content
In the second part of the Technical Analysis in Excel series we will describe how to calculate RSI and MACD indicators using Excel formulas (see Part I where we implemented SMA, Bollinger Bands, and EMA indicators).
Sample Excel spreadsheet file
Excel file (TA in Excel – All Indicators) with MACD and RSI indicator formulas – complete results of implementing instructions in this article.
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.
NOTICE: to see images below in full resolution – simply click on each image. Click “Back” button to get back to article.
Moving Average Convergence Divergence indicator is a trend following indicator, which tracks the difference between two exponential moving averages, this difference is usually referred to as the “MACD line”, and another shorter time-frame exponential moving average of the MACD line, called the “signal line”. This means we will have four data-series when we calculate MACD: 1) fast EMA, short EMA, the MACD line, and the Signal line. The basis for the calculation will be the Close price (column D in our sample spreadsheet).
Since we are using EMA for “fast” and “slow” moving averages in our MACD calcuation – bear in mind that the first 10-20 values in the resulting series will not be accurate. The EMA values become correct after about 2 x EMA PERIODs (price bars). So, if you are calculating EMA over 14 price bars, you can expect pretty accurate EMA values starting around 28’s record in your time series.
1. At this point we are going to have to move our “parameters” table with variable names and values a little to the right then we had it in Part I, since we need to add more columns to calculate MACD. Notice that when you copy-paste those cells – values in all formulas referencing those values will be automatically updated.
2. Let’s add three new variables to our “parameters” table on the right:
- MACD Fast – time period for the “fast” EMA used in MACD line calculation.
- MACD Slow – the time period for the “slow” EMA used in MACD line calculation.
- MACD Signal – time period for the Signal line.
Values 12, 26, and 9 are standard MACD parameter values, and this is usually written as: MACD(12,26,9).
3. Also, let’s add the following four columns for our data series: MACD-Fast, MACD-Slow, MACD, MACD Signal.
The MACD-Fast and MACD-Slow are just two EMA time series, just like the one we calculated in column K earlier, only using different “length” variables.
4. Column L will contain the “fast” EMA for our MACD indicator. In cell L2 enter the following:
5. In cell L3 enter this formula, which will calculate EMA based on the value of variable “MACD Fast”:
6. In column M we’ll calculate the “slow” EMA, this time using MACD Slow value as the length parameter:
6.1. Column M2 – we set the first value in EMA to be equal to the close price:
6.2. Column M3 – formula for EMA, referencing MACD Slow variable value using $T$5. We have to add $ in front of column and row so that the reference does not change as we copy this formula down to the rest of the cells in column M.
7. Now we copy the formula to the rest of the column by selecting cell M3 and double-clicking on the little square in the lower-right corner of the cell.
8. In column N (MACD) will calculate the difference between the “fast” EMA and “slow” EMA:
9. Now copy the formula to the rest of the columns in column N.
10. And finally, we need to calculate the “signal” line, which is an EMA of the MACD values. We will repeat the EMA setup in column O similarly how we did it in for MACD-Fast and MACD-Slow, calculating EMA based on values in column N and using “MACD Signal” variable value from cell T6 as the time period:
11. After copying the formula to the rest of the cells in column O the spreadsheet now contains MACD and MACD signal values for our Close prices. You can now hide columns L and M, since they are not of any practical use except serving as intermediate values for MACD indicator calculations. Columns MACD and MACD Signal in columns N and O respectively constitute the actual MACD indicator values.
Now that we have MACD values calculated in the spreadsheet – we can create a chart in Excel to display MACD line and the signal line graphically. We will also see how we can easily update MACD parameter values and see changes instantly reflected on our chart.
Watch this 1.5-min video to get step-by-step instructions for creating MACD chart in Excel:
The Relative Strength Index (RSI) is a momentum oscillator that calculates velocity and strength of a financial instrument price movement. It was developed by J. Welles Wilder 1970s and to this day remains one of the most popular indicators in technical analysis. RSI ranges between 0 and 100 and this fact makes it a convenient indicator to evaluate whether market is currently overbought or oversold. While each market is different and overbought/oversold levels are somewhat subjective – an RSI value about 70-80 value is generically considered to indicate that market is overbought and value below 20-30 level is generally considered that market is oversold.
RSI is typically calculated for a range of 14 price bars, which is the default value in most trading platforms. However, any positive integer value can be used, with higher values generally considered to be providing stronger but slower signals.
RSI consists of a single time series. However, to calculate RSI we will use one other column to contain an intermediate calculation, which we’ll add to the spreadsheet and then can hide when we are done calculating actual RSI values.
The formula for RSI is pretty straightforward:
RSI = 100 - 100 / (1 + RS)
RS = Average Gain / Average Loss
The only parameter to RSI is the number of periods (days, hours, price bars etc…) to calculate average gain & loss values. Average gain is calculated as the sum value of all gains over the number of periods (using 0 for periods that generated losses), divided by the number of periods. Likewise, average loss is the sum of all losses (using 0 for periods that had a gain), divided by the total number of periods. The formula uses the absolute value for average loss.
For example, if we had the following gain/loss history over the past 14 periods:
We would have:
Average Gain = (15+10+0+22+0+16+0+0+21+5) / 14 = 6.36
Average Loss = (0+0+9+0+12+0+3+11+0+0) / 14 = 2.5
1. Now let’s setup the columns for RSI indicator. First, column – “Close Gain/Loss” will contain an intermediate calculation required for final RSI formula. This column simply contains the gain/loss value based on the close price from column D.
2. Next, column Q will contain the RSI values.
3. Also, let’s add a new variable for RSI period in cell T7.
4. In cell P2 we enter 0.0 as the seed value. In cell P3 we enter formula “
=D3-D2” to calculate difference between close price in row 3 and close price in row 2:
5. Copy formula from P3 to the rest of column P by selecting the cell and double-clicking the square in lower right corner.
6. Now in cell Q2 enter the final RSI formula and hit Return.
This formula is using OFFSET function to refer to Gain/Loss values in column P, and the SUMIF function to calculate gains and losses separately for the RS value.
7. Do not worry about the fact that value in cell Q2 contains #REF!. Expand the formula to the rest of column Q by double-clicking a small square at the lower-right corner of cell Q2. You will see that values in rows 2 to 13 contain #REF!, and the rest of the column has normal RSI values. This is because rows up to row 13 do not have sufficient number of Gain/Loss values to calculate RSI based on the RSI period 14. If you change RSI period to smaller or larger number – the RSI values in column Q will be updated to reflect re-calculated values.
8. To remove the annoying #REF! values – we can use Excel’s conditional formatting feature (notice – this is already done in the sample Excel file you can download at the top of this article). For instructions on how to do that – please refer to section Removing Formula Errors in Part I in this article series.
Part II Conclusion
In this second part of our 3-part series we covered computation of MACD and RSI indicators in Excel. In the next part we’ll focus on implementing a simple trading strategy backtesting spreadsheet. It will include generation of buy & sell signals from indicator values and calculating P&L for our strategies.
In the mean time:
Trading Geeks provides consulting in trading strategy and software development for independent traders, financial advisors, and hedge funds. Please inquire for more information or a free quote for your project via Contact Us form on the right.