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