Microsoft excel have not only for general mathematical or critical statistical calculation, but we can say that now MS excel is like a very useful and handy software Worldwide , so we decided to use this world's best user friendly Excel applications features in technical analysis to finding the technical level for Stocks and Nifty in easiest way for our valuable users and newbie in the stock market to finding the proper trend of underlying.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
