AmazonH

Nifty RSI Calculation in excel


Here's an example of RSI calculation done in excel spreadsheet with real life data of Nifty index. You can download that from here -



In fact you can understand from the excel sheet how exactly to compute RSI and then you can apply the formulas to data of any stock or indices. Remember each value of RSI takes in an unlimited number of previous values in consideration, therefore it makes sense to have atleast 50 data points before your calculation date so that the values you get are more or less accurate.


RSI Calculation


I guess many of you have come here searching for how to calculate RSI. Given below is the process of how to calculate RSI for any stock or index.


1. First calculate average gain & average loss of past 14 days

Average Gain = [(previous Average Gain) x 13 + current Gain] / 14
Average Loss = [(previous Average Loss) x 13 + current Loss] / 14

First Average Gain = Total of Gains during past 14 periods / 14
First Average Loss = Total of Losses during past 14 periods / 14


2. Calculate RS

RS = Average Gain / Average Loss


3. Calculate RSI

RSI = 100 - (100/(1+RS))


A lot of you might be confused by the first step here. Let me explain. The formula above for calculating average loss in general is a sort of weighted moving average, more specifically called Wilders modified or smoothened moving average technique. The formula is such that technically each value incorporates all previous values before it. But then again you have to start somewhere. So in your data, take the first 14 days, calculate its average and take that to be the first average gain/loss & then you can move on from there as per the normal average gain/loss formula.


I have another post here detailing how to Calculate RSI in Excel

How much data should I have to accurately calculate RSI ?

How much data should I have to accurately calculate RSI ?

That's a valid question. Because, in theory, the RSI value for any day takes into account all the previous values before it, therefore incorporating in it even the effect of the earliest available value. This happens in a EMA based system. Ofcourse the effects of previous data points keeps on getting diminished as one moves forward in time. So thus, even though even very old data points might have an effect on the RSI value, it would be very less.

So then, how many data points should one use, so that one can calculate a more or less correct value but its more convenient to do so.

The answer is, as many as possible. I mean mostly one would be calculating the values in excel, where calculating for 100 values or 10000 values is all the same. So, if you have the data, use all of it.

Problem arises when there is a scarcity of data. In that case i would aim to have 5 times data. 5 times of what ? While calculating RSI, one chooses to calculate it as a moving average of 'X' number of days, typically the number 14 is used. Thus I would prefer that i have atleast 14*5=70 days of data so that the calculation is accurate with an error rate of less than 0.5%

Nifty historical chart from 1990 to 2009

For those of you looking for Nifty chart from as far back as possible - here it is.

First is the chart on normal scale -





Next is the same chart, but this time on log scale -



You can download the files from these links -

http://www.ziddu.com/download/4014920/niftydailychartsince1990normalscale.jpg.html

http://www.ziddu.com/download/4014984/niftydailychartsince1990logscale.jpg.html

Where can I find historical price data of Nifty and Indian stocks?

Right here is the answer !!!!

I know I spent a lot of time trying to find data so that I could do various technical analysis calculation and see how things were and also to form charts & have a look. I mean I was looking for data for a long period, not just past 2-3 years. Then i could see how markets have moved over various time and market cycles.

The excel sheet below has data on Nifty since 1990 taken directly from NSE. It has data till mid march 2009. Thats a reasonable 19 years of data to perform any calculations, see and understand trends and charts.

For data even earlier to that I presently do not have but I am searching for the same. As soon as i find it, I will post it here. I will also post data for individual stocks. Leave a comment if you want anything specific.



Cheers !!!

Nifty/Sensex data before 1990?


So you've got data since 1990. But what about data from before that period. How does one get hold of that data ? From where ? You might be interested to know more of Indian markets historical price movements, or do calculations or technical studies on longer periods. Well, I have both good news & bad news. Bad news first - Nifty data is not available before 1990, simply because it didnt exist. Infact it didnt exist even in 1990, thats why one doesnt see Open,High,Low values for Nifty, just the Close values. Those close values were infact computed backwards from the closing values of the components of the index as on the day of the formation of the index (3rd November 1995).

But the good news is that data for Sensex is available, infact for quite a long period, from 1979 onwards. Leave me a comment with your email/ph if you need it.

AmazonH

Adsense