Technical Analysis in Excel: Part II – MACD and RSI

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).

Files

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.

 

Data File

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.

MACD Indicator

Overview

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).

Calculation Instructions

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:

  1. MACD Fast – time period for the “fast” EMA used in MACD line calculation.
  2. MACD Slow – the time period for the “slow” EMA used in MACD line calculation.
  3. 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.

   ta_excel_macd_1

   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:

   =D2

5. In cell L3 enter this formula, which will calculate EMA based on the value of variable “MACD Fast”:

   =D3*2/(1+$T$4)+L2*(1-2/(1+$T$4))

   ta_excel_macd_2

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:

      ta_excel_macd_3

   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.

      =D3*2/(1+$T$5)+M2*(1-2/(1+$T$5))

      ta_excel_macd_4

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.

   ta_excel_macd_5

8. In column N (MACD) will calculate the difference between the “fast” EMA and “slow” EMA:

   ta_excel_macd_6

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:

   ta_excel_macd_7

 
   ta_excel_macd_8

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.

   technical analysis in Excel - MACD indicator

MACD Chart

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:

Excel spreadsheet with MACD Chart (final result):
   ta_excel_macd_10_chart

 
 
 

RSI Indicator

Overview

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 Formula

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)

where,

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:

+15
+10
-9
+22
-12
+16
-3
-11
+21
+5

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

Calculation Instructions

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.

   ta_excel_rsi_1

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:

   ta_excel_rsi_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.

   ta_excel_rsi_3

6. Now in cell Q2 enter the final RSI formula and hit Return.

   =100-100/(1+(SUMIF(OFFSET(Q2,(-1*$T$7)+1,-1,$T$7,1),">=0")/$T$7)/ABS((SUMIF(OFFSET(Q2,(-1*$T$7)+1,-1,$T$7,1),"<0")/$T$7)))

   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.

   ta_excel_rsi_4

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.

   technical analysis in Excel - RSI indicator

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:

IB Excel Trader
Have an Interactive Brokers account? Take a look at our new IB Excel Trader spreadsheet application. IB Excel Trader lets you automate your trading using spreadsheet formulas and provides a convenient base template for implementing your own trading algorithms in VBA. In addition, our programmers can customize and extend base IB Excel Trader functionality and implement custom trading rules per your requirements. Find out more details on the IB Excel Trader product page.



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.


Posted in Data Analysis
3 comments on “Technical Analysis in Excel: Part II – MACD and RSI
  1. Samkit says:

    Excellent Work !!
    Looking forward to the third in the series

  2. ajithraj says:

    awesome

1 Pings/Trackbacks for "Technical Analysis in Excel: Part II – MACD and RSI"
  1. […] Part 2 of their tutorial covers MACD and RSI […]

Leave a Reply

Your email address will not be published. Required fields are marked *

*

IB Data Downloader


ib_downloader_3_downconf ib_downloader_3_options

IB Excel Trader

  • IB Excel Trader version 1.6 is now available!
  • Trade Stocks, ETFs, Futures, and Forex directly from Excel.
  • Implement custom trading rules using spreadsheet formulas or VBA.
  • Program entry rules for single or bracket exit orders. Market, Stop, Limit, Stop-Limit, as well as complex algo orders are supported.
  • Order Log sheet (new!). Contains a detailed list of each order status change in a filterable Excel table.
  • Use our Customization Service to extend IB Excel Trader and contract our programmers to develop your custom trading strategies.
IB-Excel-Trader-1-5_wTWS-2

Interactive Brokers (IB) is a low cost provider of trade execution and clearing services for individuals, advisors, prop trading groups, brokers and hedge funds. IB's premier technology provides direct access to stocks, options, futures, forex, bonds and funds on over 100 markets worldwide from a single IB Universal account.

Member NYSE, FINRA, SIPC. Visit www.interactivebrokers.com for more information.

Contact Us!

Please enter your contact details and a short message below and we will respond to your message shortly.