Dynamic RSI calculation in Excel Sheet with charts

RSI or Relative Strength Index is one of the most popular momentum indicator in Technical Analysis. It’s value fluctuates between 0 to 100 and indicates the strength and velocity of price move. RSI indicator is mostly calculated on 14 period timeframe and any value above 70 indicates over-brought level and value below 30  indicates over-sold level. However, there are many variations of this indicator and the manner in which the RSI levels are interpreted varies with your trading style. This posts intends to explain RSI calculation in Excel sheet. Also the calculation steps would be described. See below one of our very popular trading systems bases on RSI and ADX:

Intraday Trading Strategy using RSI and ADX

The relative strength index was developed by J. Welles Wilder and published in a 1978 book, New Concepts in Technical Trading Systems, and in Commodities magazine (now Futures magazine) in the June 1978 issue. It has become one of the most popular oscillator indices.

How RSI is calculated?

RSI calculation is based on average gains and losses for the specific time period.  For instance, if you want to calculate the 14-day RSI and the stock went up on nine days and fell on five days. The absolute gains (stock’s closing price on a given day — closing price on the previous day) on each of these nine days are added up and divided by 14 to get the average gains. Similarly, the absolute losses on each of the five days are added up and divided by 14 to get the average losses. The ratio between these values (average gains / average losses) is known as relative strength (RS). To make sure that the RSI always moves between 0 and 100, the indicator is normalised later by using the formula given below:

RSI = 100 – 100 / (1+RS*) * RS = Average gains / Average losses

Excel Sheet Overview

The Excel sheet would dynamically calculate the RSI based on the periods entered. Also you have to manually enter the Open, High,Low,Close data for the selected stock or index. The calculation formula can be found in Excel sheet itself. The price chart and RSI chart is embedded into the excel sheet which will update accordingly.

Parameter Description
Worksheet Name Dynamic RSI
Inputs Column A,B,C,D,E  

Date,Open,High,Low and Close for the selected stock/index

Column L3 The periods for which RSI should be calculated. Default is 5.
Outputs Change (Column F) Change in stock price from previous day. It may be positive or negative.
Advance/Decline (Column G,H) If Change (Column F) is positive, then Advance is equal to change else it is zero.

If Change (Column F) is negative, then Decline is equal to -change else it is zero.

Average Gain/Loss (Column I,J) Average of Advance/Decline for the RSI period specified.
Relative Strength (Column K) Ratio of Average Gain and Average Loss
RSI (Column L) The final value of RSI for the given time period.

Screenshots

Dynamic RSI

RSI Chart

Download Link

Please download the Excel sheet from the below link. Let us know if you have any questions or comments.

Dynmaic RSI Excel Sheet

6 Comments

    • Hi Poomani,

      RSI is an oscillator which moves between 0 to 100. Any value below 30 indicates oversold and a Buy position can be initiated, and values above 70 indicates overbought and a Short position can be initiated. Please do paper trading before entering into the actual trade.

  1. Can I add other indicators like MACD for better buy and sell signal???
    What you say.

    • Hi Ketan,

      Yes, you should always observe multiple indicators and price action for better probability of success.

  2. Isnt RSI available on most charting platform and website ? why to enter data manually to find the RSI chart? Jus curious

    • Hi Harshit,

      Yes it is. But most of the traders like to use the classic way of Excel analysis.

Leave a Reply

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