Last Updated on 08/04/2024 by Ndanileka

What is an Exponential Moving Average

An Exponential Moving Average (EMA) is a moving average that places a greater weight and significance on more recent data points when compared to Simple Moving Averages (SMAs) which treat all data points equally. EMAs are commonly used in technical analysis to smooth out price data and identify trends.

Unlike SMAs, which calculate the average of a fixed number of data points, EMAs give more weight to recent data, making them more responsive to changes in price. This responsiveness is achieved by using a multiplier that decreases exponentially as data points move further back in time.

In essence, the EMA formula calculates a weighted average of the current data point and the previous EMA value, with the weight of the current data point determined by the multiplier.

Exponential Moving Average Formula

Calculation Steps

1. Initialize the First EMA Value:
   • The first EMA value is typically calculated as the Simple Moving Average (SMA) of the initial period closing prices.
2. Calculate the Multiplier:
   • The multiplier is calculated based on the period length used in the EMA calculation. It is typically derived using the formula:
     Multiplier = 2/ ( Period +1)
     
3. Calculate Subsequent EMA Values:
   • For each subsequent period, the EMA is calculated using the EMA formula, where the previous EMA value serves as a reference point.
     
4. Repeat Steps 2 and 3:
   • Steps 2 and 3 are repeated for each period in the dataset to generate a series of EMA values.

Below is a spreadsheet example illustrating the formulas and columns for a manual calculation, note how the First EMA value cell E17 is the same as the SMA value Cell17, this value serves as the starting point. thereafter Steps 2& 3 are applied to calculate subsequence values.

When calculating an EMA, it is important to ensure there is enough data in the look-back period.
This helps stabilize the initial EMA value, which is crucial because all subsequent EMA values depend on it. Although there is no strict rule for defining the initial value, using the SMA value as the initial EMA value is common practice. As long as your overall price history is sufficiently larger than the window size (period), the effect of the initial value will diminish. In practical terms, if your total price history is at least four times larger than the window size, you can expect the EMA value to stabilize.

EMA Custom Function

Apps Script Workaround

After Google discontinued the use of the Googlefinance() function outside spreadsheets, incorporating it into App Scripts to create complex formulas has become nearly impossible. To overcome this limitation, we can create a custom function that interacts with data within the Google Sheets environment, similar to other built-in Google functions.
This is achieved by assigning variables to the data in the script and then utilizing the custom formula within the spreadsheet to run the script. By doing so, we can integrate our custom functionality into the Google Sheets interface without directly interacting with the SpreadsheetApp Class.

Data Preparation

=GOOGLEFINANCE(B1,"close",TODAY()-((14*6)/5)*7,TODAY())

This function retrieves historical closing prices for the specified stock symbol (cell B1) from Google Finance. The TODAY() function is utilized in the start and end date parameters to define the length of the price history.

To establish the start date we first calculate the minimum price history required for a stable EMA value. As previously noted, a minimum of four times the period length of data is necessary for this stability.

For instance, with a period of 14, the minimum data points required are determined as follows:
Minimum data points required = period (n) * 4
Dividing by 5 (since there are 5 trading days per week) provides the total weeks of data needed: (n * 4) / 5
Multiplying by 7 gives the total days required: ((n * 4) / 5) * 7

We use this calculation to automate our start and end dates, represented as “today() – ((n * 4) / 5) * 7, today()”.

For a period (n) of 14, the end date calculation would be:

((14 * 4) / 5) * 7 = 112 days

To provide an additional buffer, we multiply by a factor of 6 instead of 4. Thus, for a period of 14, the formula becomes:

((14 * 6) / 5) * 7 = 168 days

Step 2: Remove Header Row

To remove the header we will utilize the Query() function.

=QUERY(QUERY(GOOGLEFINANCE(B1,"close",TODAY()-((14*6)/5)*7,TODAY())),"SELECT* OFFSET 1",0)

• QUERY(…, “SELECT * OFFSET 1”, 0): This is the outer QUERY() function. It takes the result of the inner QUERY() function as input. It selects all columns (SELECT *) and offsets the result by one row (OFFSET 1). The last argument 0 specifies that there are no headers in the data.
• QUERY(…, “SELECT *”, 1): This is the inner QUERY() function. It takes the data from GOOGLEFINANCE() as input. It selects all columns (SELECT *) and specifies one row as the header (OFFSET 1). This is done to include the headers in the data explicitly.

EMA Function Parameters

The function is structured to calculate the EMA, utilizing two parameters: range and n.

• Range Parameter: The “range” parameter represents the date and close price data required for the calculation.
• N Parameter (Period): The n parameter specifies the period of the EMA, such as 14 for a 14-day EMA.

Function and Chart Comparison

Both the function and chart return a value of 139.87 for the 14-day EMA of “GOOG (Alphabet Inc ) on March 15, 2024.

Apps Script Code

/**
 * Calculates the Exponential Moving Average (EMA) of the range.
 *
 * @param {Array} range A range containting date and close price data from Googlefinance(), with no header row.
 * Range example,QUERY(QUERY(GOOGLEFINANCE(B1,"close",TODAY()-((14*6)/5)*7,TODAY()), "SELECT *", 1),"SELECT* OFFSET 1",0)
 * @param {number} n The number of periods to calculate the EMA, e.g., 14 for a 14-day EMA.
 * @return The Exponential Moving Average (EMA) of the range.
 * @customfunction
 */


function EMA_GOOGLEFINANCE(range, n) {
  if (n > range.length) {
    throw "Error: The specified period exceeds the length of the range.";
  }

  const dates = range.map(row => new Date(row[0])); // Convert date strings to Date objects
  const closePrices = range.map(row => parseFloat(row[1])); // Parse close prices as floats

  // Initialize an array to store EMA values with corresponding dates
  let results = [];

  // Calculate the multiplier for EMA calculation
  const multiplier = 2 / (n + 1);

  // Calculate SMA for the first n data points
  let sum = 0;
  for (let i = 0; i < n; i++) {
    sum += closePrices[i];
  }
  let sma = sum / n;

  // The first EMA will have the date corresponding to the nth entry (0-indexed, hence n-1)
  results.push([dates[n-1], sma]);

  // Calculate EMA for the remaining data points
  for (let i = n; i < closePrices.length; i++) {
    const currentClose = closePrices[i];
    const prevEMA = results[results.length - 1][1];
    const currentEMA = (currentClose - prevEMA) * multiplier + prevEMA;

    // Append the current date and EMA to the results array
    results.push([dates[i], currentEMA]);
  }

  return results;
}
 

How to use:

1. Open the “Extensions” menu and choose “Apps Scripts”.
2. Put the EMA_GOOGLEFINANCE code into the script editor.
3. Save the script.
4. Go back to your Google Sheets document.
5. Enter “=EMA_GOOGLEFINANCE()” in a cell to use your new function.

Known issues:

1. Loading Error: Upon initial opening of the spreadsheet, values may not update until a symbol is edited. However, once edited, the function should work fine until the spreadsheet is closed.

Spreadsheet Template

EMA Price Chart

The EMA chart features a timeline chart that showcases both the EMA line and the price line, providing a visual comparison between the two.

In addition, there is a column chart representing the EMA price difference, displaying the variance between the EMA value and the price value at each data point. This chart helps gauge how far the price has deviated from the EMA, offering insights into potential entry points. By considering the distance between the price and the EMA, traders can optimize their strategy.

Furthermore, there is a distribution chart, or histogram, illustrating the variance distribution. This chart provides information on the frequency and range of different variance levels, helping traders understand common variances and likely outcomes. Analyzing this distribution can enhance decision-making by providing insights into the typical behavior of price movements relative to the EMA.

Watchlist Scan

For the watchlist scan, the S&P100 serves as a template with columns for price and 20-day EMA. The “Price to EMA” column returns “below” if the price is below the EMA value and “above” if the price is greater than the EMA value.The “Price Diff” column calculates the difference between the price and EMA as a percentage.

The “Price to EMA filter” filters the “below” and “above” results for easy copy and paste into a CSV file.

Additionally, a simple watchlist trend is showcased with a column chart displaying the count of “above” and “below” instances. This aids in analyzing the overall direction of the watchlist.

Common EMA Periods and Set Formulas

10-Day EMA: This shorter-term EMA is commonly used by short-term traders to capture more recent price movements and identify short-term trends.

=EMA_GOOGLEFINANCE(QUERY(GOOGLEFINANCE(B1,"CLOSE", TODAY()-60,TODAY()),"SELECT Col2 LABEL Col2 ''"),10)

20-Day EMA: Slightly longer than the 10-day EMA, the 20-day EMA provides a broader perspective on price trends and is favored by swing traders and medium-term investors

=EMA_GOOGLEFINANCE(QUERY(GOOGLEFINANCE(B1,"CLOSE", TODAY()-112,TODAY()),"SELECT Col2 LABEL Col2 ''"),20)

50-Day EMA: The 50-day EMA is often used as a measure of medium-term trend direction. It helps smooth out price fluctuations and is popular among trend followers and position traders.

=EMA_GOOGLEFINANCE(QUERY(GOOGLEFINANCE(B1,"CLOSE", TODAY()-280,TODAY()),"SELECT Col2 LABEL Col2 ''"),50)

100-Day EMA: This longer-term EMA is utilized by longer-term investors and trend followers to gauge the overall trend direction.

=EMA_GOOGLEFINANCE(QUERY(GOOGLEFINANCE(B1,"CLOSE", TODAY()-560,TODAY()),"SELECT Col2 LABEL Col2 ''"),100)

200-Day EMA: Considered a key indicator by many traders and investors, the 200-day EMA is used to assess the long-term trend of a security or market. It is widely watched for signals of major trend changes.

=EMA_GOOGLEFINANCE(QUERY(GOOGLEFINANCE(B1,"CLOSE", TODAY()-1120,TODAY()),"SELECT Col2 LABEL Col2 ''"),200)

If the cells initially display “loading…” upon opening the sheet, edit one of the stock symbols, and the data should load correctly.

Resources and Credits

Reddit: My hard way to learn EMA’s formula and deep dive of EMA
StackOverflow: GoogleFinance function to calculate Exponential moving average in google sheets
StockCharts: Moving Averages – Simple and Exponential

Happy Trading!