Calculate Technical Indicators for Trading in Excel

Introduction

In this article we will cover how you can calculate common technical indicators for trading with Excel. Many websites show technical indicators these days but it is important to understand the algorithm behind the indicators to have confidence while trading.

Technical indicators are used to figure out the following tasks :

Check whether the stock is overbought or oversold
Check whether the uptrend in the stock is beginning
What should be the optimal stop loss or target price?
What's the maximum or minimum price stock can reach in near to mid term?
How to get rid of minor stock fluctuations in calculation of buying price?

Table of Contents

Simple Moving Average

It is one of the most simplest technical indicator. It's an average of historical N time periods. Let's say you want to calculate 5 Days moving average. You can use =AVERAGE( ) function in excel and take previous 5 days period closing market price values and average them up. It helps to remove fluctuations. For example stock moved upward by 20% yesterday (hit 'upper circuit' - trading terminology for the max price stock can reach in a day, after that you can't trade in the stock on that particular day), If you take yesterday's closing price for trading and set market price based on that, you may lose some money (depending on luck) as stock gained so much last day. Hence it is recommended to take moving average of last 5Days, 20 Days or 50 Days to calculate the buying price for trading.

In the image below we are calculating 5 Days Moving Average

Exponential Moving Average

The exponential moving average gives greater weightage to recent periods. For example if you calculate 5 Days exponential moving average it gives more importance to today's and previous closing price than the closing price which was 5 days before. Similarly if you take bigger window and calculate 20 Days moving average it gives more weightage to this week closing price than the 3 weeks prior price.

Step 1 : Calculate Average of T Time Period

Suppose you want to calculate 5 Days Exponential moving average so time period T would be 5 so it will be an average of (T - 4) through T in the first iteration.

Step 2 : Apply Exponential Moving Average Formula

In the second iteration you need to perform the following calculation. Prev. Day EMA is what we calculated in step 1.


=(Today's Closing Price)*(2/(T+ 1)) + (Prev. Day EMA) * (1-(2/(T+1)))

Step 3 : Paste the formula down

Now the last step is to paste down the formula shown in step 2. You can use shortcut key Ctrl + D to perform this action.

Relative Strength Index (RSI)

Relative Strength Index measures whether stock is overbought or oversold. If a stock is strongly overbought it may have pull back very soon. Similarly if a stock is oversold but company is fundamentally strong the likelihood of increase in share price of the stock in near or mid term is very high. Many traders use RSI as a method to determine whether they should invest in the stock now or should they wait for the reversal.

Thumb Rule
If RSI is less than 30, stock is considered 'Oversold'
If RSI is greater than 70, stock is considered 'Overbought'

Step 1 : Calculate Gain or Loss

First step is to calculate a day change based on closing price of the stock. Suppose stock price of the stock increases from 100 to 120. It is a gain of 20. Similary if it declines from 100 to 80, it's a loss of 20.

It is important to note here that we take absolute value in calculating loss because RSI lies between 0 and 100. It can't be negative.

Step 2 : Calculate Average Gain or Loss

Suppose you are calculating 14 Days RSI so you need to calculate 14 days simple moving average of gain or loss. It's a simple average of previous 14 values of gain or loss. See the snapshot below. Let's say if you calculate 7 days RSI so it will be an average of previous 7 values.

Final Step : Calculate RSI

By using Avg. Gain and Avg. Loss you can calculate RSI using the formula below. When Avg. Gain is greater than Avg. Loss, RSI is above 50 which shows bullish pattern. Similarly when Avg Loss > Avg Gain, RSI is below 50 and shows bearish behavior.

100-(100/(1+(Avg Gain/Avg Loss)))

Commodity Channel Index (CCI)

CCI compares current price to average price over a specific time period. When CCI is greater than 100 it indicates buy signal (price going to be up). When CCI is less than -100 it indicates sell signal (likely downfall of prices).

Step 1 : Calculate Typical Price

Typical price is defined by a simple average of closing, low and high price of any given trading day.

Typical Price = (High + Low + Close) / 3

Step 2 : Calculate 20 Days Moving Average of Typical Price

CCI of 20 Days period is very popular in trading world. Hence we are taking 20 days simple moving average of typical price in this step.

Step 3 : Calculate Avg. of Absolute Deviation

In this step we are taking deviation of each of "typical price" points from 20 days moving average of typical price. We can calculate mean deviation using =AVEDEV( ) formula.

=AVEDEV(range of prev. 20 days typical price,range of prev. 20 days typical price)

Step 4 : Calculate CCI

Final step is to perform CCI calculation by using the following formula -

CCI of 20 Days = (Typical Price - 20 Days Simple Moving Average of Typical Price) /(0.015 * Avg. Deviation)

Williams %R

Williams %R is also called as Williams Percent Range. It lies between 0 and -100 and measures momemntum status of stock - if a stock is overbought or oversold. If a stock is above -20, it is considered overbought. If a stock is below -80, it is considered oversold Many traders also use it to find out if a reversal in stock is nearby (i.e. changing trend of stock). If the indicator is moving below -80 and then the price starts going up, it means price would remain up for a next few days or sessions.

This formula was discovered by Larry Williams who made a million dollar in an year in 1980s. His strategy was based on this indicator.

=(Highest value of high of last 14 days - Closing Price) / (Highest value of high of last 14 days - Lowest value of low of last 14 days) * -100

End Note

The intend of this article is to show and explain mathematics behind the technical indicators. I would suggest you not to follow any trading strategy blindly based on these technical indicators. It's because markets behave very differently in some days and movement of stock depends on various factors such as fundamentals of the company, emerging news related to the stock, sector or macro economy in general, statements of Senior Management etc. Sometimes you may observe stock goes down even after announcing quarterly profit. It's because of profit booking by a big players like FII, Mutual Funds etc. In simple words stock values went up in the past as big players assessed that the company would be in profit in the coming quarter and now traders or investors start selling off after announcement of quarterly result. There can be several other reasons like quarterly result is below analysts' expectation and they don't see further growth in this stock in future.

About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn