Calculation of Supertrend (ST)

The Supertrend is one of the most widely used Indicator among the Indian traders. And unfortunately there is very little documentation on this subject at Investopedia or the chart school of Stockcharts or even with Zerodha Varsity. So it required quite some investigation to arrive at the right method to calculate the Supertrend.

In its simplest form, when the Supertrend is overlayed over a candlestick chart, if the Supertrend crosses to below the Price we take a Long Position and when the Supertrend crosses to above the Price, we take a Short position. Here is a snapshot of the Supertrend.

This article from Economic Times is probably a good place to start with to get a qucik overview of Supertrend.

So let us dive straight into and get the calculation of Supertrend. Check this Google Sheet for the Calculation of Supertrend. 

Given below is the pseudo code for the calculation of Supertrend:

Basic UpperrBand = (High + Low) / 2 + Multiplier * ATR
Basic LowerBand =  (High + Low) / 2 - Multiplier * ATR

Final UpperBand = IF((Current BasicUpperband < Previous Final UpperBand) OR 
                    (Previous Close > Previous Final UpperBand))  THEN 
                    (Current Basic UpperBand) ELSE
                    (Previous FinalUpperBand)

Final LowerBand = IF((Current Basic LowerBand > Previous Final LowerBand) OR
                    (Previous Close < Previous Final LowerBand)) THEN
                    (Current Basic LowerBand) ELSE
                    (Previous Final LowerBand)

SuperTrend = IF((Previous SuperTrend = Previous Final UpperBand) AND 
               (Current Close <= Current Final UpperBand)) THEN 
               Current Final UpperBand
             ELSE
                IF((Previous SuperTrend = Previous Final UpperBand) AND
                  (Current Close > Current Final UpperBand)) THEN
                  Current Final LowerBand
                ELSE
                   IF((Previous SuperTrend = Previous Final LowerBand) AND
                     (Current Close >= Current Final LowerBand)) THEN
                     Current Final LowerBand
                   ELSE
                      IF((Previous SuperTrend = Previous Final LowerBand) AND
                        (Current Close < Current Final LowerBand)) THEN
                        Current Final UpperBand

Now lets see how this is calculated in Python. Lets first download the OHLCV data from the above google sheet into a CSV file. Name it as "test data". We can use this as our input file for the Python program.

Lets first take a look at the Imports and Global Declarations that we will use in this program:

#imports used in the program
import numpy as np
import pandas as pd

#Global Declarations
input_file = "test data.csv"
multiplier = 2 # An integer to indicate the value to multiply the ATR.
period = 14
atr = 'ATR' + '_' + str(period)
st = 'SuperTrend' + '_' + str(period) + '_' + str(multiplier)

Calculation of Average True Range (ATR)

Average True Range (ATR) is a Technical Indicator that measures Market Volatiity. This indicator was developed by Welles Wilder

From Statistics , we know that Range in Trading refers to the (High - Low) for the timeperiod/candlestick in concern. Wilder has started a Concept caled True Range which is defined as the Greater of the following:

• Current High minus the current Low
• Absolute Value of Current High minus the previous Close
• Absolute Value of Current Low minus the previous Close

For more details on ATR, check out Investopedia

Welles Wilder used the Absolute Values as he was interested in measuring the distance between two points, not the direction.

Take a look at the Google Sheet for Calculation of ATR. I have used GOOGLEFINANCE function to retreive the OHLCV of NIFTY for the first quarter of 2019. The GOOGLEFINANCE function is actually what makes Google Sheets such a compelling use. In due course I intend to publish a post to retreive OHLCV data for 1 minute candlestick from Google Sheets , on that can be used with Pandas DataFrame. In my opinion, once we create such an API, it is a better alternative to Quandl or Alpha Vantage or Quantra Blueshift or Twelve Data API for Backtesting. It also gives us the opportunity to do Live Testing of a Trading System with Live Data , but without risking actual Money. But more on that later on .... Here is the Google Sheet for now :

https://docs.google.com/spreadsheets/d/13pKpTZuDgPlb7jqH2Pyp8ypo7G97NKgV1YIgJ4j4toI/edit#gid=1308165568

The calculation in the above google sheet is based on the standards as per StockCharts

Now lets copy the Values in the Excel We can copy the OHLCV data from the above google sheet and calculate the rest of the data. If you do it n a Excel spreadsheet, you will see that the ATR obtained from the excel is the same as the ATR obtained from Google Sheet. Now let us convert the excel to csv and use this csv file as the Input for our Python program.

FAQ on Google Sheets & Google Finance for Traders

Many of us have used Google Sheets sometime or the other for EOD/RTD data. There are quite a few Screener's out there in the marketplace that are used by retail traders in NSE. One such is the FataFat Stock Screener

There are quite a few things that one can do with Google Sheets and this post seeks to demystify Google Finance for NSE Traders

How do we get EOD/RTD data for NSE by using Google Sheets
Google sheets use a function called GOOGLEFINANCE which is built-in into the google sheets. GOOGLEFINANCE fetches current or historical securities information from Google Finance

How does GOOGLEFINANCE fetch securities information?
GOOGLEFINANCE retrieves data that is stored on Google Servers

Who is the data provider for the data that Google uses?
End of day prices is provided by SIX Financial Information and Intra-day data may be provided by Interactive Data Real-Time Services, Inc.

Calculating Exponential Moving Average (EMA) using Google Sheets

The concept of Exponential Moving Average is explained here in this link: 

The calculation of the Exponential Moving Average(EMA) can get tricky, unlike the calculation of the Simple Moving Average(SMA). If you take the same set of data points and determine the EMA on Google Sheets, Excel and Python, you will find that there is a difference in the EMA. That's becos to calculate the EMA there are different methods in adjusting the weights, which is handled differently (internally) in Excel , Google Sheets and Python.

Or from a traders perspective, you would have noticed that there is a difference between the values of EMA in Sharekhan, Zerodha PI, Amibroker etc. This is becos: 

1. There is a variance in the data feeds that these trading terminals use. This also leads to a difference in calculation of SMA or any other technical indicator.
2. There is a variance in the method that they use internally to determine the calculation of EMA.
3. Or probably one of them is using Excel, another is using Google Sheets and the third is using Python !!!

Thanks to the efforts of  Marcello, we now have an easier google sheets method to calculate the EMA for use in trading. Here is the link:  https://docs.google.com/spreadsheets/d/1iBxaU-yeiG_ta4waj0YD6O8mn6DgbKUfE5Ap6W4aIis/edit#gid=0

In case you want a Python based method, check out this oneliner code I mentioned in Stackoverflow . Or scroll below for more details.

Here is a good explanation for the calculation of EMA using Pandas:

Now lets get to the Python Code for the Calculation of SMA and EMA. Lets have an input file in the standard OHLCV format with Date/Datetime includedlabelled as Open, High, Low, Close, Volume. Now lets say that we want to calculate the SMA and the EMA for the Close price

#imports and Declarations used in the program

import pandas as pd
input_file = "test data.csv"
period = 10
sma = 'SMA' + '_' + str(period)
ema = 'EMA' + '_' + str(period)

These would be the functions that we use in the program

def SMA(df_stock, base_column, target_column, period=14):
    """
    Function to compute Simple Moving Average (SMA)
    
    Arguments :
        df_stock : This is the Pandas DataFrame which contains ['Date', 'Open', 'High', 'Low', 'Close', 'Volume'] columns
        base_column : The String that indicates the column name from which the SMA is computed.
        target_column : The string that indicates the column name in which the computed data will be stored.
        period : This integer that indicates the period of computation in terms of number of candles
        
    Returns :
        df_stock : Pandas DataFrame with new column added with SMA for the period mentioned.
    """
    
    df_stock[target_column] = df_stock[base_column].rolling(window=period).mean()
    df_stock[target_column].fillna(0, inplace=True)

    return df_stock
 
def EMA(df_stock, base_column, target_column, period=14, alpha=False):
    """
    Function to compute Exponential Moving Average (EMA)
    
    Args :
        df_stock : Pandas DataFrame which contains the columns ['Date', 'Open', 'High', 'Low', 'Close', 'Volume']
        base_column : The String that indicates the column name from which the EMA needs to be computed
        target_column : The String that indicates the column name in which the computed data will be stored
        period : An Integer that indicates the period of computation in terms of number of candles
        alpha : Boolean Value- If True indicates to use the formula for computing EMA using alpha (default is False)
        
    Returns :
        df : Pandas DataFrame with new column added with name 'target'
    """

    con = pd.concat([df_stock[:period][base_column].rolling(window=period).mean(), df_stock[period:][base_column]])
    
    if (alpha == True): #  set alpha=True for calculation of ATR
        df_stock[target_column] = con.ewm(alpha=1 / period, adjust=False).mean()
    else: # set alpha=False for Calculation of EMA
        df_stock[target_column] = con.ewm(span=period, adjust=False).mean()
    
    df_stock[target_column].fillna(0, inplace=True)
    return df_stock

Next is to call the functions from the manin program:

df_stock = pd.read_csv(input_file,header='infer',infer_datetime_format=True)
df_stock['Date'] = pd.to_datetime(df_stock['Date']) # convert date to datetime

SMA(df_stock, 'Close', sma, period)

EMA(df_stock, 'Close', ema, period, False)

The EMA calculated using this method is in sync with the EMA retreived from brokers like Sharekhan and Zerodha. In case there is a variance, try and increase the number of datapoints. The trick is to to have more datapoints for accurate smoothing, preferrably 250 datapoints or more.

The EMA calcuation using Python was taken from https://github.com/arkochhar/Technical-Indicators. Thank you arkochhar

Intraday Equity Logbook for Zerodha Traders

Here is a simple logbook for equity trading for  Zerodha traders.

https://docs.google.com/spreadsheets/d/1nvdPRPa_nLb6givFuD057myKn_YcT7lOOrs2VvP-CiE/edit?usp=sharing

Screener to Shortlist Stocks based on Fundamental Analysis

So lets us look at some basic rules/criteria on the basis of which we can filter out stocks. In this method, we will not look at stocks from the point of view of a Chartered Accountant, we will instead look at it from a traders perspective.

The purpose is not to select the best stocks as it is to eliminate/reject stocks that are not suited for trading. From a given list of say 100 stocks, we can eliminate stocks that are not suited for trading.

Criteria 1 : P/B Ratio (Price to Book Value Ratio ) 

P/B Ratio	State of Stock
>5	Expensive	Reject Stock
2-5	Normal
<2	Undervalued

The P/B ratio is in many cases could vary based upon the industry. So some companies that own machinery and land are bound to have higher book value compared to a company in the services sector.

But for the purpose of our screening, it is better to avoid all stocks where the P/B Ratio is greater than 5.

 Criteria 2 : Debt/Equity Ratio

Companies with higher debt are more risky compared to companies with lower debt. For our consideration, we will  reject all stocks with Debt to Equity ration greater than 2

Debt /Equity Ratio	State of Stock
< 1	Good
1-2	Ok
>2	Problematic	Reject Stock

Criteria 3 : Interest Coverage Ratio

The interest coverage ratio measures how many times a company can cover its current interest payment with its available earnings. In other words, it measures the margin of safety a company has for paying interest on its debt during a given period. The lower a company’s interest coverage ratio is, the more its debt expenses burden the company. When a company's interest coverage ratio is 2.0 or lower, its ability to meet interest expenses may be questionable.

Any company with an interest coverage ratio of less than 2.0 will be automatically rejected.

Interest Coverage Ratio	State of Stock
< 2.0	Problematic	Reject Stock

Criteria 4 : Operating Profit Margin

If the (Sales - Expenses) are increasing over the last 5 years, the company is in good shape. The OPM % over last 5 years has to be positive (at the least). We will reject all stocks that have a OPM % in the negative.

Criteria 5 : Calculate Fair Value of Stock

Finally we need to calculate the price at which to purchase a given stock. For that we need to identify the fair value of the stock.

We calculate the EPS yearly growth taken over the last three years. 

Fair value Multiplier = EPS yearly growth / PE

One way of thinking about the PE ratio is that it is the "Expected Growth by the Market". As an example, if the P/E of Infy is 22.20 , the market expects a growth of 22.20 % per annum.

Example: If yearly EPS growth of Infy is 30 and PE ratio is 22.20, then Fair Value Multiplier =  EPS / PE = 30/22.20 =  1.35

Fair Value = (Market Price * Fair Value Multiplier)

If Fair Value of the stock is above the Current Market Price, then the stock is undervalued. 

Criteria 6 : PEG Ratio

The price/earnings to growth ratio (PEG ratio) is a stock's price-to-earnings (P/E) ratio divided by the growth rate of its earnings for a specified time period.

PEG Ratio = Price/EPS  / EPS Growth

Debt /Equity Ratio	State of Stock
< 1	Undervalued
1-2	Normal
>2	Expensive	Reject Stock

The lesser the PEG ratio, the better. For the purpose of our screner, we will reject all stocks that have a PEG ratio > 2.

All the data for calculations have been taken from Screener Here is the link to the actual Google Sheet:

https://docs.google.com/spreadsheets/d/1iYzfQ6oQjmW_tqz_sW2ERURJZVDUmqKcsKvYguI4rkI/edit?usp=sharing

A simple Stock Screener using Google Sheets for Intra Day Trading

So here goes the link:

https://docs.google.com/spreadsheets/d/17lBm-OU1Yv9UZ7VT26eB2TqgDy2NMcrHVohK4CqmWA8/edit#gid=0

In case you want to make any modifications to this google sheet, maybe add some more scrip's or delete some scrips, you have the following options: 

Method 1:
Go to Row # 2. Click on the various columns and copy the formulae. Paste it on your google sheet. Apply this formulae across all the cells in that given column

Method 2:
Login with your Gmail account. Go to the Main Menu of the Google Sheet. Click on File->Make a copy. This will create a copy of the google sheet "Intraday FnO Underlying" in your Google Drive. Now you can edit this new google sheet directly from your own Gmail Account. You can also add/delete rows and make any modifications as you deem necessary.

The screenshot below will provide he necessary guidance: