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
Download Link
Please download the Excel sheet from the below link. Let us know if you have any questions or comments.
How can i use this RSI excel sheet, Can you help me with this?
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.
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.
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.
why its 5 period RSI? normal RSI being calculated is 14 periods..
Hi, would like to contact you about doing a multi time frame rsi heat map in xl on forex
Analogous to this can you provide Reverse RSI (the concept wherein one can calculate the underlying index/stock value for predetermined RSI values especially 20/30/40 for oversold and bounce from that and 60/70/80 for overbought and retracement after that) excel ?
Hi, I compares the 5 period RSI value to the 5 period RSI indicator values on TradingView.com as well as AmiBroker. However in TradingView as well as Amibroker, I am getting a value different than what is shown in excel. Moreover, on both the charts, I have identical values. The closing prices of NIFTY (for the same period) matches that of the chart.
Example – On 29-Feb-16, the 5 period RSI of NIFTY as per excel comes to 33.559. However in TradingView as well as Amibroker it shows the 5 period RSI as 34.03
Can you check why there is a difference between excel vs charting platforms?
hi. is it possible to get the spreadsheet for data in reverse chronological order?
thanks.
Hi Ashish,
Its possible, but you’ll need to play around with Excel formulas
Is the a way to use excell for auto and selling, via. A app with a broker like TD Ameritrade.
And do you have a CD or tutorials on all the indicaters