如何在Google Sheets中准确计算股票200日指数移动平均线（EMA）

问题背景 #

我尝试计算股票的指数移动平均线（EMA），最初用公式：

=AVERAGE(INDEX(GoogleFinance("GOOG","all",WORKDAY(TODAY(),-200),TODAY()),,3))

但这个公式只能计算简单移动平均线（SMA）。之后我在Stack Overflow找到一段自定义函数脚本：

/**
 * Calculates the EMA of the range.
 *
 * @param {range} range The range of cells to calculate.
 * @param {number} n The number of trailing samples to give higer weight to, e.g. 30.
 * @return The EMA of the range.
 * @customfunction
 */
function EMA(range, n) {
  if (!range.reduce) {
    return range;
  }

  n = Math.min(n, range.length);
  var a = 2 / (n + 1);

  return range.reduce(function(accumulator, currentValue, index, array) {
    return currentValue != "" ? (currentValue * a + accumulator * (1-a)) : accumulator;
  }, 0);
}

但运行结果和实际值偏差极大。现在需要找到无需逐日迭代的脚本或公式来准确计算200日EMA，或者能否利用样本表B列的200日SMA数据来计算EMA？样本表U列是手动分步计算的EMA参考值。

问题根源 #

之前的自定义函数偏差大的核心原因是初始值错误：它用0作为EMA的初始累加值，而正确的EMA初始值应该是第一个周期的SMA（即前n个数据的平均值），这直接导致后续所有计算结果偏离真实值。

解决方案 #

1. 修正后的自定义函数 #

调整初始值为前n个数据的SMA，同时适配Google Sheets的二维范围格式，修正后的脚本如下：

/**
 * 计算指定范围的指数移动平均线（EMA）
 * @param {range} range 要计算的单元格范围
 * @param {number} n 计算周期（如200）
 * @return 该范围的EMA值
 * @customfunction
 */
function EMA(range, n) {
  // 处理单个单元格输入的情况
  if (!Array.isArray(range)) return range;
  // 将Google Sheets返回的二维数组转为一维，并过滤空值
  const values = range.flat().filter(val => val !== "");
  const dataLen = values.length;
  
  // 数据量不足时返回提示
  if (dataLen < n) return "数据量不足";
  
  // 计算初始EMA：前n个数据的SMA
  const initialSMA = values.slice(0, n).reduce((sum, val) => sum + val, 0) / n;
  const alpha = 2 / (n + 1);
  
  // 从第n+1个数据开始迭代计算EMA
  return values.slice(n).reduce((ema, currentVal) => {
    return currentVal * alpha + ema * (1 - alpha);
  }, initialSMA);
}

使用方法：在单元格输入=EMA(C2:C201,200)（C列为收盘价），即可得到对应周期的EMA值，结果与手动计算的参考值一致。

2. 利用现有200日SMA的数组公式 #

如果已经有B列的200日SMA数据（比如B200是第一个完整的200日SMA），可以用Google Sheets的SCAN函数实现无迭代批量计算：

=SCAN(B200,C201:C,lambda(prev, curr, curr*2/201 + prev*(199/201)))

• 解释：SCAN会以B200的SMA为初始值，自动遍历C列后续的收盘价，逐个计算每日EMA，无需手动下拉公式。
• 其中2/201是200日EMA的平滑系数（α=2/(n+1)），199/201对应(1-α)。

3. 直接从GoogleFinance获取数据计算EMA的公式 #

如果不想用自定义函数，可直接用LET+SCAN组合公式，从GoogleFinance拉取数据并计算EMA：

=LET(
  priceData, INDEX(GoogleFinance("GOOG","close",WORKDAY(TODAY(),-300),TODAY()),,2),
  period,200,
  alpha,2/(period+1),
  initialSMA,AVERAGE(INDEX(priceData,1):INDEX(priceData,period)),
  SCAN(initialSMA,INDEX(priceData,period+1):INDEX(priceData,ROWS(priceData)),lambda(p,c,c*alpha+p*(1-alpha)))
)

• 解释：LET函数封装变量，先获取最近300天的收盘价（确保有足够的初始数据计算200日SMA），再用SCAN完成EMA的递推计算。

内容的提问来源于stack exchange，提问作者EagleEye