如何在Google Sheets中从Google Finance获取准确月度收盘价数据？

原公式的问题在于，它通过按年月去重保留了当月第一个交易日的价格，而非月度收盘价通常对应的当月最后一个交易日的价格，因此数据不准确。以下是两种可行的解决方案：

方案一：使用LET+BYROW组合公式（推荐，结构清晰） #

=ARRAYFORMULA(
  LET(
    data, GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), DATE(2021,2,1), "daily"),
    dates, INDEX(data,,1),
    prices, INDEX(data,,2),
    year_month, TEXT(dates, "yyyy-mm"),
    unique_ym, UNIQUE(year_month),
    last_trading_dates, BYROW(unique_ym, LAMBDA(ym, MAX(FILTER(dates, year_month=ym)))),
    month_end_prices, VLOOKUP(last_trading_dates, data, 2, FALSE),
    HSTACK(unique_ym, last_trading_dates, month_end_prices)
  )
)

公式说明： #

• LET：定义中间变量，简化公式逻辑
• GOOGLEFINANCE：拉取指定时间段内的每日价格数据
• TEXT(dates, "yyyy-mm")：将日期转换为年月格式，用于按月份分组
• UNIQUE(year_month)：提取所有唯一的年月值
• BYROW+LAMBDA：遍历每个年月，筛选出该月所有交易日，取最大日期（即最后一个交易日）
• VLOOKUP：根据最后交易日匹配对应的收盘价
• HSTACK：将年月、最后交易日、收盘价合并为结构化表格

方案二：使用QUERY分组查询 #

=QUERY(
  GOOGLEFINANCE("NASDAQ:GOOG", "price", DATE(2020,1,1), DATE(2021,2,1), "daily"),
  "select year(Col1), month(Col1)+1, max(Col1), Col2 
   where Col2 is not null 
   group by year(Col1), month(Col1)+1, Col2 
   order by year(Col1), month(Col1)+1",
  1
)

公式说明： #

• 按年份和月份分组，通过max(Col1)获取每个月的最后交易日
• 筛选掉空价格数据（避免非交易日的无效记录）
• 最终返回年份、月份、最后交易日、收盘价的对应关系

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