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.
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.
No comments:
Post a Comment