基于Revolut交易日志计算FBAR准确余额：原生函数实现咨询

前提假设 #

你的Revolut交易日志需包含以下列（可根据实际列号调整）：

• A列：完整交易时间（带时分秒，例如2023-10-05 14:30:00）
• B列：交易币种（例如EUR、USD、GBP）
• C列：交易金额（存入/转入记正，转出/兑换记负）

步骤1：生成各币种的累计期末余额 #

如果日志没有直接提供每笔交易后的余额，先添加一列（比如D列）计算累计余额：

=QUERY(SORT(A2:C, B:B, TRUE, A:A, TRUE), "select Col1, Col2, Col3, sum(Col3) over (partition by Col2 order by Col1) label sum(Col3) over (partition by Col2 order by Col1) '期末余额'", 1)

这个公式会按币种分组、按时间排序，自动算出每笔交易后的累计余额，结果放在新的第四列（对应D列）。

步骤2：计算每日总USD余额 #

用数组公式一次性算出所有日期的总余额（已转换为USD）：

=BYROW(UNIQUE(INT(A2:A)), LAMBDA(date, 
  SUM(BYCOL(UNIQUE(B2:B), LAMBDA(currency, 
    LET(
      latest_time, IFERROR(MAXIFS(A2:A, INT(A2:A)=date, B2:B=currency), MAXIFS(A2:A, A2:A<date, B2:B=currency)),
      end_balance, XLOOKUP(latest_time, A2:A, D2:D, 0, 0),
      exchange_rate, INDEX(GOOGLEFINANCE("CURRENCY:"&currency&"USD", "price", date, date), 2, 2),
      end_balance * exchange_rate
    )
  ))
))

公式拆解： #

• UNIQUE(INT(A2:A))：提取所有不重复的交易日期（去掉时分秒）
• BYROW：逐个处理每个日期，计算当日总USD余额
• BYCOL(UNIQUE(B2:B))：逐个处理每个币种，算出该币种当日的USD价值
• MAXIFS：找到该币种当日最晚的交易时间；如果当日无交易，就取最近一次交易的时间
• XLOOKUP：根据找到的时间，匹配对应的期末余额
• GOOGLEFINANCE：获取该币种当日对USD的汇率，INDEX(...,2,2)提取具体汇率数值

步骤3：提取年度最大日余额 #

直接对步骤2的结果取最大值即可：

=MAX(上述步骤2的公式)

或者把步骤2的公式嵌套进去，一步到位：

=MAX(BYROW(UNIQUE(INT(A2:A)), LAMBDA(date, 
  SUM(BYCOL(UNIQUE(B2:B), LAMBDA(currency, 
    LET(
      latest_time, IFERROR(MAXIFS(A2:A, INT(A2:A)=date, B2:B=currency), MAXIFS(A2:A, A2:A<date, B2:B=currency)),
      end_balance, XLOOKUP(latest_time, A2:A, D2:D, 0, 0),
      exchange_rate, INDEX(GOOGLEFINANCE("CURRENCY:"&currency&"USD", "price", date, date), 2, 2),
      end_balance * exchange_rate
    )
  ))
)))

补充说明 #

• 若某币种在当日无交易，公式会自动沿用该币种最近一次交易的余额，符合FBAR对每日余额的统计要求
• GOOGLEFINANCE返回的汇率会自动匹配日期，节假日/周末会取最近工作日的汇率，符合FBAR的合规要求

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