How to Calculate the RSI Indicator in Excel

[@CoentraDZ]

6 years and still worth it to watch this video.

[@mstafadev6729]

Thanks, old videos are always straightforward no bs

[@andreiyevanov]

Thank you Herr. Ursell. Your video helped my son in understanding power of MS Excel.

[@AbdulSamad-zt7rk]

I was even able to program a python script after watching this RSI calculation based on the logic. Thanks.

[@nicog.9243]

me, too. great template!

[@alfredo1972dirk]

Nice work Mark!!! I am learning a lot from your courses

[@MarkUrsell]

Thanks Alfred, I'm glad you're finding them useful.

[@mohamedal-dabbagh8710]

Thank you very much Mark... You made it ridiculously easy! You are a great teacher!

[@OttawaRocks]

A very clear explanation! Many many thanks! I tried to learn the theory and the formulas from two sources - investopedia and an instructional video from someone here on YT. Quite frankly, I had many unanswered questions and was feeling frustrated and confused. As it turned out, given I am reasonably competent with Excel, your video turned out to be a million times more simpler, clearer and better. Best regards!

[@MarkUrsell]

Thank you

[@CliseruGabriel]

I know it's many years old video. I have seen many who are asking if it's correct. Based on my tests, for 1 day EasyJet (relevant to the pandemic) it's correct. It takes 16 positions (in my situation with 14) to calibrate itself because it always relies on historical data, but afterwards is quite spot on with the website i am playing on.

[@nth.education]

Hi, Thanks for your video on this. I had a question which is troubling me a bit. Should the average gain be calculated not for 14 days but for the days when gain happened ? E.g. If 5 out of 14 days had positive movement, the denominator should be 5 and not 14 ? I am thinking correctly on this ?

[@MarkUrsell]

Thanks for the comment. I'm not certain what you mean about the -1 and +1. The formula for the Average Upward and Downward Movements use the number of periods-1 to calculate a smoothed average. The formula for the RSI uses +1 to convert the Relative Strength into a index that oscillates between 0 and 100.

[@borg3575]

Do zero's in the data list give accurate results for a moving average? I'm not able to get an accurate RS or RSI with zero's.

[@haimschlesinger4920]

Very good explanation! thank you :)

[@lifelongcoder]

Great video, Mark! What is the second column in the excel which contains values 0,4,8,12,16 and 20?

[@hakanasilturk284]

Great job. Can you share the excell link please?

[@amirmoezi8316]

Thanks a million for your invaluable content.. it's just wonderful

[@TechnomTigerGeek]

hey buddy great video. I'm a student just learning stocks and stuff. I got a question, whats the point of the -1 and +1? Thanks You sir!

[@jamesyates-personal542]

Do you know or have you done RSI using Wilders Average

[@8ksmiff502]

Hi ! How can I apply this formula automatically to all tickers which are placed under a single sheet having 30days historical data?

[@MarkUrsell]

Yep, totally agree.

[@Sky-lm1gu]

Great job. Thanks so much.

[@MarkUrsell]

Thank you for watching

[@danishhussain3857]

RSI readings on mt5 platform are different from the calculations done on excel sheet...any reason?

[@MarkUrsell]

It could be that MT5 uses a different average in the calculation. Also, you need to tell a large enough sample of data to make sure the RSI has time to match your charts.

[@andresvasquez3260]

I have this problem, sometimes the prices used to calculate the first rs are all gains, so there arent negative prices, i cant divide the average gains / 0, what to do in that case?, in other Words, lets say i want to calculate rsi (14), and the first 14 prices are all gains (green candles), what sup there?.

[@byronlovelace8379]

What's the difference between using the Welles Wilder moving average and just using a normal moving average?

[@MarkUrsell]

Hi Byron, I am using this average because this is how it was originally done. Wilder invented the RSI before the mass home computing age and his average is easier to calculate on a rolling basis compared to the SMA. If you compare the averages on a chart they all give similar results.

[@FrancisRodgers]

Thanks for the great video. My question is: Where you place 14, and the fixed average on the 14th line, then used a different formula for the remaining averages. This does not seem accurate to me. Change 14 to 15 or 20 and everything changes, but it would also require you to move the fixed average to the 15th or 20th place. Otherwise how can it work? Is this correct?

[@MarkUrsell]

Hi Francis. I used the mean Average formula only for the first calculation. Each subsequent calculation uses the Welles Wilder average. The Welles Wilder average needs at least 1 previous value to calculate correctly so you have to use a a value to start with.

[@g1kluang]

Hi Mark, what is the value for RSI to show that it has a strong or weak movement? And I've seen many of your videos such as the MACD, Bollinger band, I really hope you can draw a line graph to clear show us the movement. Thanks for the video Mark

[@Dan-uf2vh]

is there any point to using moving averages or is it just a leftover from the 1970s ? beyond legacy RSI, why not use the actual averages for the average up and down movements?

[@MarkUrsell]

I like your thinking Dan, it is a good idea to question everything. Do you mean the Welles Wilder style of moving average? I agree that this is not necessary and the RSI would work perfectly well with either a simple moving average or exponential moving average.

[@chriswong3120]

Hi Mark, if I can't get the historical data from the first day of the stock, could I still calculate the RSI?

[@MarkUrsell]

Hi Chris, are you referring to the opening price? If so, the RSI uses the closing price, so you only need this to calculate the indicator.

[@chriswong3120]

@@MarkUrsell sorry, I didn't express my question clearly. From your video, calculating the RSI of S&P required the closing price from 1957 (the date of establishment) to now. But I only could get the historical data from 2009 to now. Could I still calculate the RSI?

[@8ksmiff502]

Hi Mark! I am calculating with 1 month data but getting a different value (80% Accuracy). Can you please help?

[@MarkUrsell]

Hi, the RSI average formula is based on the previous period. So it won't match the values in your charts until it has been calculated over more data. You could try again with a larger dataset or match the first RSI value with the chart value.

[@bipinsedai6493]

Just alert while calculating average gain or loss. It is not only averge but also smooth average so the value depends also on the data set included historical.

[@Mastifffriend]

Hello, are any type of stocks better than others to use the RSI strategy? (Commodities, non dividends, etc.) Thank you.

[@MarkUrsell]

Hi, I have not tested different stocks so I cannot help you on this.

[@Mastifffriend]

Thank you anyway. I've entered a somewhat volatile airline and won't add until the RSI hits the low 30s. This practice will stop me from making compulsive buying as well, I'll just keep adding a little cash into account each paycheck.

[@hemanthkumar9908]

thanks a lot.. it saved me a lot of time

[@tanweerkabeer9249]

spread sheet contains daily index volume ?

[@karthikematta]

Excellent video.

[@MarkUrsell]

karthik matta Thanks very much for the feedback.

[@MarkUrsell]

The latest versions of my 21 Technical Indicators & Candlesticks Spreadsheets (new formulas & descriptions) are available for instant download in my Spring Store: teespring.com/stores/tradinformed

[@steveford8966]

Hi Mark, great tutorials, thank you for uploading. I've followed your instructions and downloaded some historical data from my broker and created the spreadsheet for the RSI. Is there any way of getting the daily OHLC prices to download (and populate the spreadsheet) automatically or is it a manual job every night/morning...Any help would be much appreciated...

[@MarkUrsell]

Hi Steve, thanks for the comment. I am developing the new versions of the Tradinformed Backtest Models and they will have an automated data input system. But if you are manually updating price data for trading purposes, have you considered Google Sheets? I use these for my trading and find them very useful. I have done a tutorial on importing live data: kzbin.info/www/bejne/m5bIfo2LnKmZhMk I am also planning some new Google Sheets trading videos so let me know if you have any thoughts.

[@jeanmuch4213]

Hi, when sel and bhy signals take place???/?/ Thanks

[@MarkUrsell]

Jean, this video is not showing a trading strategy but only how to calculate the indicator. The RSI is normally used to identify changes in the market direction and below 30 is considered oversold and above 70 is considered overbought.

[@jamesls99]

I tested this one against TradingView RSI, but has different outcome.. can you try it?

[@MarkUrsell]

Hi James, I've looked at a Trading View chart I got the same results. To get the same results you need to make sure the price data is exactly the same. Try comparing the S&P 500 or another major index with a fixed closing time. The other thing to make sure is that you are calculating enough periods so that Welles Wilder average has calculated correctly. Hope this helps!

[@TC10394]

Looks like where you calculate the average, we can use. 1) Simple Moving Average 2) Exponential Moving Average 3) Wilder's Smoothing Average. Do you have an example of #2 or #3? Reason I am asking is I am trying to plot RSI similar to Binance and I think they may be using 2 or 3 but pretty sure not 1 which is similar to your example. Thanks in advance.

[@TC10394]

Actually #1 works really well. I had made a mistake in my calculation.

[@MarkUrsell]

Hi Mike, you can use any of the averages and still get a good result. I demonstrate the original Welles Wilder average in this video.

[@TC10394]

@@MarkUrsell I do find that to be true. Thanks so much Mark. (there was a mistake in my calculation and it kind of threw off the results when compared to Binance).

[@georgepapaioannou3399]

i saw many differences too. Also in other indicators. Do you know what they use ? BTW Many Thanks Mike !

[@vcool]

Does this mean that I need 28 rows of data to calculate the RSI over the most recent 14 rows?

[@DrTalevich]

What stock is this? Without knowing the ticker symbol we can't verify the accuracy of this tutorial.

[@marcthomassibayan3520]

thank you very much sir.. 😇😇

[@jeanmuch4213]

Hi, when buy and sell signals generated please.??????????????? Thanks

[@evgenymakar4777]

THANK YOU VERY MUCH, ILOVE YOU!

[@VladyslavHavshynskyy]

How to count RSI divergence in the excel?

[@MarkUrsell]

Hi Vladyslav, you can program in the RSI divergence using an IF Statement. Thanks for the suggestion, I'll get a video recorded with the formula.

[@hectormanuelvanegascruz4263]

Los valores del RSI en Metatrader 4 no coinciden con los de la hoja de cálculo

[@shajahanismail258]

Dear sir ,please make a video for How to Calculate the Connors RSI Indicator in Excel?

[@MarkUrsell]

Done! Thanks for the request: kzbin.info/www/bejne/j6qTdn-DgN6JoJI

[@sanjayparmar3526]

I am getting negative RSI value, is it possible?

[@MarkUrsell]

You should not be getting a negative value. The indicator is bounded between 0 and 100.

[@byronlovelace8379]

Check brackets around the final part

[@alexalmeidajr.9203]

Thanks a LOT!

[@davidbarrar5968]

The best!

[@geary2]

tHANKS

[@pedrojandrade]

Hi Mark. I hope You're ok. I'd like you to upload how to calculate the Williams %R Indicator-oscillator, it would be very helpful to see how it's done...please, If you had the formula to replicate it in excel i'd be great for my final thesis and for your youtube followers . From Chile. Have a great day. Thanks Mark

[@MarkUrsell]

+Pedro Rojas Hi Pedro, good idea. I will try to get a video published this week. Thanks, Mark

[@nicko9611]

Im losing my mind over my excel. I cant get your formula to work. Ive tried it on my pc, on wps spreasheets and on a mac. The formula just enters the cell and I get a red box a round the cell. I do have it on general and calc is on automattic. I use US language.

[@MarkUrsell]

Hi Nicholas, which formula is causing the problem?

[@nicko9611]

@@MarkUrsell Hey, I got it working. Fore some reason I had to write ";" instead of ":"

[@mahdyfo]

It is connors rsi. The RSI used today is smoothed rsi.

[@joedraht8741]

Hi Mark, I think the "slightly different formula", which you use to calculate the second and all following averages of the up-/downward movements is wrong (at least you're not calculating a simple moving average with it). You can easily check that by using excel's average-function. Best regards Joe

[@joedraht8741]

I've just read that Wilder himself proposed this way to calculate the RSI. Didn't knew that. Bye!

[@Masterofcreations]

I got a better question.. .What do you do when the rsi indicator is mostly influenced by bad news? I can click the top 10 losers and they all have oversold indicators.

[@ilhankirbas8119]

Why am I calculating rsi like this in excel but different from investing rsi ?????

[@billmietelski]

Bill was here.

[@25jiten]

RSI = Avg Gain/ Avg Loss (in last 14 days)

[@harmindersingh5585]

RSI FIGURE DOES NOT MATCH WITH INDIGATOR

[@VidderTheKing]

Mark Hello! Who is this

[@Masterofcreations]

I must be a dummy or the 13 different articles explaining rsi assume you're intelligent or good at math. Your video just confuses me even more!

[@MarkUrsell]

It is actually not very complicated. Try working it out on paper with a calculator. The only confusing bit is the beginning: When a bar closes higher than the previous close you enter the difference between the closes to the Upward Movement column and leave the Downward Movement column blank. When the close is lower you enter the difference between the closes in the Downward Movement column. After that take an average of the Upward and Downward Movement columns. Now in the next column divide the Average Upward by the Average Downward Movement and you get the Relative Strength (RS). Finally you turn the RS into the RSI by the formula 100-(100/(RS+1)). Hopefully this helps!

[@Masterofcreations]

***** Lol, forget this. I think I got an idea of how to use it though. Basically I add up all the ups then divide by 14 and the same for downs. Then the ratio is rsi. Who needs to do this though when they're sites that calculate it for you!

[@siz9000]

@@MarkUrsell Hi Mark, could you please explain to me logically how the formula you use for the second column after the average works, I'm struggling to rap my head around how the formula works, thanks in advance.