• Algo Trading FAQ

Learn Formulae for Technical Analysis in Excel

[box type=”bio”] Jayantha has been selected as Campus Ambassador at AlgoJi- 2017. He is pursuing B.Tech. + M.Tech. (Dual Degree) from IIT BHU. His hobbies include maths and music.[/box] Technical Analysis in Excel is simple, easy, and fun! Excel is one of the most popular software on entire globe. Even if you hate Microsoft, you might have used Excel at some point of time.

So in this tutorial, we go through the most important TA indicators, and how to calculate them in excel.

Calculating SMA for Technical Analysis in Excel

Let’s start our tutorial of Technical Analysis in Excel with Simple Moving Average (SMA). It is simply the average of the previous data points up till the current data point. The period of the SMA determines how many previous data points should be considered.

For example, if the period of an SMA in 20, then we must calculate the average of the current data point and the previous 19 data points.

The simplest formula to achieve this is to use the AVERAGE function.

Suppose you want the SMA of the data in F column of 21 period.

Just select the 22^nd row in the column where we want to calculate the SMA(Because not enough data to calculate SMA for the previous rows), and enter this formula –

=AVERAGE(F2:F22)

Next, select the cell, select the small square at the right bottom part of the cell, and drag it to the rest of the columns.

But what if you want to change the period of the SMA? It would be tedious to go and change the formula again. Especially if there are many rows for which you have to change the period.

The solution for this is to use the OFFSET function of Excel.

Just enter the period value in a particular cell, here we have entered it in the cell J2. Next enter the following formula in the first column –

=AVERAGE(OFFSET(G21,(-1*$J$2+1),-4,$J$2,1))

and drag it till the end.

Calculating EMA for Technical Analysis in Excel

The exponential moving average is an important TA indicator, where more weightage is given to more recent values.

• To calculate the EMA, we first need to calculate an EMA factor. The formula for this factor is –  2/(1+no. of periods)
• Next we calculate EMA by the formula – (current data point value)*(factor) + (previous value of EMA) * (1 – factor)
• Again the data on which we want to calculate EMA is in column F. The period is in cell J3, calculate the factor in cell J4 by entering the formula: =2/(1+J3)

We are calculating EMA in column H, so enter the value of F2 itself in H2( Because the first value needs to be a sensible seed value)

Next, enter the following formula in H3 –

=F3*$J$4+H2*(1-$J$4)

And then drag it to the rest of the columns.

Calculating MACD for Technical Analysis in Excel

Moving Averag Convergence Divergence (MACD) is one of the most popular momentum indicators used today. This tutorial on Technical Analysis in Excel cannot be completed without describing MACD.

The way MACD is calculated is as follows –

1. Get daily closing prices of the stock which is to be considered
2. Calculate the 12 day Exponential Moving Average
3. Calculate the 26 day EMA
4. Subtract the 12 day EMA and the 26 day EMA, this is called as the MACD line
5. Calculate 9 period EMA of the MACD line, this is called as the signal
6. Calculate the difference between the signal and the MACD line, this is usually represented in the form of a histogram, and is usually referred to as “histogram” itself

Buy the stock when the histogram crosses zero from negative and goes to positive, and sell the stock when the histogram crosses zero from positive to negative.

Here is the data used for the demo in this article : –

Calculate the 12 day and 26 day EMAs.

This is what your workbook should look like after you do this : –

Next subtract the 12 day EMA and the 26 day EMA to get the MACD line.

You can do this by entering the formula – =G4-H4  into the cell I4, and then drag this till the rest of the rows.

Next, calculate the 9 Period EMA of the MACD line and enter it in the column Signal

After doing this, enter the difference between the MACD line and the Signal into the column Histogram.

To generate a histogram, select the “Insert” tab, under Insert select column chart. A chart area will be formed. Next, right click on the chart area and click on “select data”, next select the range under the Histogram column.

Calculating Bollinger Bands for Technical Analysis in Excel

Bollinger bands are three bands drawn on a chart. It consists on a middle Bollinger band, an upper Bollinger band and a lower Bollinger band.

The three bands are calculated as : –

• Middle Bollinger Band – 20 Day Simple Moving Average
• Upper Bollinger Band – Middle band + 2 * (20 Day Standard Deviation)
• Lower Bollinger Band – Middle band – 2 * (20 Day Standard Deviation)

Bollinger bands are used to give a relative idea of high and low, and the volatility of a stock. Although they alone cannot be used to take a decision of buy/sell, they can help us decide along with other indicators.

The same excel workbook which we used in the previous topic will be used here also.

• First make a column to calculate 20 Day SMA and fill it.
• Next, we calculate 20 Day Standard Deviation by using the inbuilt function STDEV(). Enter the below formula into the first cell of the StDev Column =STDEV(E3:E22). Drag till the rest of the columns.

• Next, enter the following formula in the Upper Band column =G22+2*H22. Drag it till the end.
• Enter the following formula in the Lower Band Column =G22-2*H22. Drag it to the end
• Next, select the cells containing the SMA, and then while holding ctrl select the cells containing the upper and lower bands. After selection of data, click on the Insert tab, select Line chart under Charts. You will get the following graph upon doing this –

Calculating RSI for Technical Analysis in Excel

We completed our tutorial on Technical Analysis in Excel with Relative Strength Index. RSI is an indicator which expresses the velocity and strength of a price movement. It is a momentum oscillator indicator. RSI always has value ranging from 0 to 100. This makes it convenient to judge whether a market is overbought or oversold. An RSI of more than 70-80 indicates that it is overbought, whereas an RSI below 20-30 indicates that it is oversold. Typically, RSI is calculated over a 14 Day Period.

This is the formula for calculating RSI –

RSI=100-100/ (1+RS); where, RS = (Average Gains) / (Average Losses)

For example, let us take the data of a stock for 5 days –

1. 30.4
2. 29.9
3. 31.2
4. 33.5
5. 32.9

The gains and losses are – 0.0,(29.9 – 30.4),(31.2 – 29.9),(33.5 – 31.2),(32.9 – 33.5)

Which equates to – 0, – 0.5, + 1.5, + 2.3, – 2.6

The average gains are – (0+1.5+2.3)/5 = 0.76

The average losses are – (0.5+2.6)/5 = 0.62

Which makes RS = 1.225

RSI = 55.056

Which would suggest that the market is balanced, i.e. it is neither oversold nor overbought.

For this demo we will use the same data which was used in the previous section. The closing prices are in column E. The RSI period is entered in cell M3.

• First make separate columns to calculate gains/losses and RSI.
• Next, enter 0 value in the first cell under the column of gains/losses.
• Then enter the following formula in the next cell =E4-E3
• And then, click on the small square in the bottom right and drag to the rest of the rows.

• Next, insert the following formula into the cell H3 =100-100/(1+AVERAGEIF(OFFSET(H3,(-1*$M$3)+1,-         1,$M$3,1),”>=0″)/ABS(AVERAGEIF(OFFSET(H3,(-1*$M$3)+1,-1,$M$3,1),”<0″)))

Here we have used the AVERAGEIF function to take the average only if the value is positive for gains, and only if the value is negative for losses. We have also used the OFFSET function to select the range upon which AVERAGEIF is to be applied.

After these steps you should get the following result –

Calculating Stochastic Oscillator in Excel

The Stochastic Oscillator is a momentum indicator which helps to identify when a security is overbought or oversold and generate Buy/Sell signal . It works by relating the current closing price to the range of the price over a certain period.

The formula for the stochastic oscillator is: %K = 100(C – L14) / (H14 – L14)

Where,

• C = the most recent closing price
• L14 = the low of the 14 previous trading sessions
• H14 = the highest price traded during the same 14-day period
• %K= the current market rate for the currency pair
• %D = 3-period moving average of %K

Buy/Sell signals are generated when the %K crosses the %D

Sometimes, the stochastic may retreat before the price itself, thereby predicting a reversal. For this we will be using the data which was used in the last section itself.

We can calculate the %K by entering the following formula =100*(E16-MIN(D3:D16))/(MAX(C3:C16)-MIN(D3:D16))

Next, just calculate the 3 period SMA of %K by entering =AVERAGE(G16:G18)

This is the result you should get in the end –

You can also generalize this for any period by using the OFFSET function of Excel.