## How to Calculate Nifty Important Technical Analysis Level in Excel Sheet

Posted by Ashish Sankhla
on
0

**Calculation of MACD**

**Moving average convergence divergence MACD, A momentum indicator that following the current trend, as the name itself has been a indicator that follow the relationship between two moving averages of price.**

To calculate MACD in excel sheet you should have the historical close price of index or stock, we suggest to take at least 50 days historical data

First of all you have to need 12 day exponential moving average.

Then 26 day EMA.

Now for calculate MACD line value less 12EMA - 26DEMA in MACD column.

For getting Signal line value you need to calculate 9 day average of MACD value.

After finding average of MACD value you should convert 9 day moving average into the exponential moving average you can also see in the excel grid below for example.

Getting divergence value simply less MACD value in to the signal line value.

**Calculation Of RSI**

**Relative Strength Index RSI is not only important word but also an useful trend indicator in technical analysis, to calculate RSI you have to taken minimum 30 days historical data of**

**Closing**value in excel sheet of underlying index or nifty stock which you want to calculate.

So for computation of RSI we need to

**Gain**and

**Loss**value of particular trading day in separate column in the excel sheet, let us assume column

**'E'**is column of

**Closing price**and now insert closing price from the cell

**'E2'**in the sheet.

Column

**'F'**take as Day Gain and column

**'G'**as Day Loss.

Put this formula on to the cell

**'F3'**=IF(E3>E2,E3-E2,0)

Put this formula on to the cell

**'G3'**=IF(E3<E2,E2-E3,0)

Now insert Column

**'H'**for

**Average Gain**and column

**'I'**for

**Average Loss**.

On to the cell 'H16' put this formula =SUM(F3:F16)/14

And on to the cell 'I16' put this one =SUM(G3:G16)/14

Now you have five columns on to the sheet :

E = Closing price

F = Gain

G = Loss

H = Average Gain

I = Average Loss

Now calculate

**RS**value on to the column

**'J'**, for this you have to put this formula =H16/I16 on to the cell 'J16' of sheet.

And now for

**RSI**insert last and result oriented column

**'K'**and

**put this formula =100-100/(1+J16) on to the cell 'K16'.**

Copy paste the formula till last cell of closing price, after this some formulas you have to change in the column of Average Gain/Loss for getting to the exponential average for this simply put :

=(H16*13+F17)/14 instead of =SUM(F3:F16)/14 only to the cell

**'H17'**and

=(I16*13+G17)/14 instead of =SUM(G3:G16)/14 only to the cell

**'I17'**now copy and paste this formula till end of last data.

You have done enjoy !!!

If you facing difficulties to calculate all these formulas so don't worry you can see online spread sheet below with same calculation method which i mentioned above and most important you can edit this sheet online also.

Still not clear or you have any doubt in your mind so download ready to use offline excel sheet from here :

**Download RSI Calculation Excel Sheet**

Tagged as: Technical Analysis, Tutorial

**About the Author**

Write admin description here..

Get Updates

Subscribe to our e-mail newsletter to receive updates.

Share This Post

Related posts

## 0 comments: