RSI (Relative Strength Index) is a momentum oscillator used in technical analysis to measure the speed and change of price movements. It helps traders identify overbought or oversold conditions in a market.
Key Points:
Scale: RSI values range from 0 to 100.
Overbought: RSI > 70 (price may be too high and could fall).
Oversold: RSI < 30 (price may be too low and could rise).
Default period: 14 (typically 14 days for daily charts).
How to Calculate RSI
Formula:
RSI = 100 - [ 100/(1 + RS)]
How to use it:
Buy signal: RSI crosses above 30 from below.
Sell signal: RSI crosses below 70 from above.
Divergence: When RSI moves opposite to price, it may indicate a potential reversal.
Step-by-Step RSI Calculation in Excel (14-period RSI)
1. Get Your Price Data
Start with a list of closing prices. Let's assume you have 15+ days of data in Column B (B2:B16).
2. Calculate Price Change
In Column C, calculate the change:
C3 = B3 - B2
Drag down to fill.
3. Separate Gains and Losses
In Column D (Gains) and E (Losses):
Gain (D3): =IF(C3>0,C3,0)
Loss (E3): =IF(C3<0,ABS(C3),0)
4. Calculate Average Gain and Average Loss
In row 16 (or after 14 values):
Average Gain (F16): =AVERAGE(D3:D16)
Average Loss (G16): =AVERAGE(E3:E16)
5. Calculate RS (Relative Strength)
In H16:
= F16 / G16
6. Calculate RSI
In I16:
= 100 - (100 / (1 + H16))
7. Smooth RSI for subsequent rows
From I17 onward, use:
AvgGain = ((PreviousAvgGain * 13) + CurrentGain) / 14
AvgLoss = ((PreviousAvgLoss * 13) + CurrentLoss) / 14
Note: You can use this to keep the RSI "smoothed" for each day after the initial 14.
Create an RSI Chart:
Select your RSI values (Column I).
Insert a Line Chart in Excel.
Add horizontal lines at 70 and 30 to show overbought/oversold levels.
