Exponential Moving Average (EMA) Google Sheets Custom Function

Last Updated on 28/08/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.

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

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 exclude 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. Paste the EMA_GOOGLEFINANCE code into the script editor.
3. Save the script.
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.

Charts

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

The column chart represents 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.

The histogram visualizes the spread of variances. It reveals how often different variance levels occur and their range. By analyzing this distribution, we can gain insights into the typical price movements relative to the EMA

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.

A column chart displaying the count of “above” and “below” instances of the current price relative to the EMA. 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)``

You’ll need to save the App Script code for the formulas to function. Initially, the cells might show “loading…” when you open the sheet. Edit any stock symbol and the data should populate correctly.

2 thoughts on “Exponential Moving Average (EMA) Google Sheets Custom Function”

1. Ashish K says:

Can You Please share your Email ID ,there is query regarding EMA Google Finance Script I am not getting the Result .