Using Excel to Calculate the RSI Indicator

Relative Strength Index (RSI) is considered one of the momentum indicators. When using indicators in my analysis, I never rely on the results of a single indicator. For example, I might use RSI with the MACD indicator. The reason I do this is to try and mitigate the risk that a single indicator could be wrong (see http://www.investopedia.com/articles/trading/12/using-trading-indicators-effectively.asp).

calculation

UP(n_day) = n_day average of closing values that were gains
DOWN(n_day) = n_day average of closing values that were losses
RS(n_day) = UP(n_day) / DOWN(n_day)

RSI(n_day) = 100 - 100/(1 + RS(n_day))

RSI is one of the easier calculations to show “on paper”. But it isn’t so obvious to calculate in Excel.

calculating in excel

Download RSI Spreadsheet

After downloading the spreadsheet you will notice several columns. The highlighted columns are non-calculated columns. In this example I’ll use an RSI(14)

1. The first seven columns are data columns. I typically get this information from http://finance.yahoo.com (if you are unsure how to get this information, see this article):
   
2. The next data cell 14 is simply there to indicate how many periods n number of days in our RSI(n) function. You can play around with these numbers to see different calculations. The remaining columns are calculated from the data columns.
   
3. VERY IMPORTANT: you should know that the first row of calculated values is not calculated. It is set to zeros. This is because the formula relies on a previous value. Since we have no previous value, I set the default to zero. This means the first 20 or so rows are not very accurate.

conclusion

Hopefully this is helpful in your studies. As I have stated before in a previous post, indicators haven’t proven successful for me in my strategies. There always seems to be promise when reading what other people have done. But when I look deeper into how reliable or consistant the indicators are, I am often disappointed (such as http://www.forbes.com/fdc/welcome_mjx.shtml). Contrary to my experience if you’ve had any success let us know in the comments section.