google-sheets - 谷歌表 : how to align two timeseries

Question

在 Google Sheet 中使用 Google Finance，我得到两个时间序列:
A) =GOOGLEFINANCE("SPY", "close", DATE(2015,1,1), TODAY(), "DAILY")有 1483 个值
B) =GOOGLEFINANCE("IBTM", "close", DATE(2015,1,1), TODAY(), "DAILY")有 1489 个值
例如，您可以看到 A在 19/01/2015 上没有值(value)，但是 B在 19/01/2015 上有一个值。这种行为会造成两个系列之间的错位。
现在，我想计算 A(close)/B(close)当A(date)==B(date) .
不幸的是，我不能直接对列进行操作，因为两个系列没有对齐。
有什么聪明的方法可以解决这个问题吗？

Answer 1

当然:

=ARRAYFORMULA(QUERY(QUERY({QUOTIENT(INDEX({
        GOOGLEFINANCE("SPY",  "close", DATE(2015,1,1), TODAY(), "DAILY"); 
        GOOGLEFINANCE("IBTM", "close", DATE(2015,1,1), TODAY(), "DAILY")},, 1), 1), 
 {QUERY(GOOGLEFINANCE("SPY",  "close", DATE(2015,1,1), TODAY(), "DAILY"), "select Col2,'SPY'"); 
  QUERY(GOOGLEFINANCE("IBTM", "close", DATE(2015,1,1), TODAY(), "DAILY"), "select Col2,'IBTM' label 'IBTM'''")}}, 
 "select Col1,max(Col2) where Col1 is not null group by Col1 pivot Col3 format Col1'dd/mm/yyyy'"), "offset 1", 0))

或直接已经划分:

=ARRAYFORMULA(QUERY(QUERY(QUERY({QUOTIENT(INDEX({
        GOOGLEFINANCE("SPY",  "close", DATE(2015,1,1), TODAY(), "DAILY"); 
        GOOGLEFINANCE("IBTM", "close", DATE(2015,1,1), TODAY(), "DAILY")},, 1), 1), 
 {QUERY(GOOGLEFINANCE("SPY",  "close", DATE(2015,1,1), TODAY(), "DAILY"), "select Col2,'SPY'"); 
  QUERY(GOOGLEFINANCE("IBTM", "close", DATE(2015,1,1), TODAY(), "DAILY"), "select Col2,'IBTM' label 'IBTM'''")}}, 
 "select Col1,max(Col2) where Col1 is not null group by Col1 pivot Col3 format Col1'dd/mm/yyyy'"), "offset 1", 0), 
 "select Col1,Col2/Col3 label Col2/Col3''"))

更新:

=ARRAYFORMULA(QUERY(QUERY(QUERY(QUERY({QUOTIENT(INDEX({
        GOOGLEFINANCE("SPY",  "close", DATE(2015,1,1), TODAY(), "DAILY"); 
        GOOGLEFINANCE("IBTM", "close", DATE(2015,1,1), TODAY(), "DAILY")},, 1), 1), 
 {QUERY(GOOGLEFINANCE("SPY",  "close", DATE(2015,1,1), TODAY(), "DAILY"), "select Col2,'SPY'"); 
  QUERY(GOOGLEFINANCE("IBTM", "close", DATE(2015,1,1), TODAY(), "DAILY"), "select Col2,'IBTM' label 'IBTM'''")}}, 
 "select Col1,max(Col2) where Col1 is not null group by Col1 pivot Col3 format Col1'dd/mm/yyyy'"), "offset 1", 0), 
 "select Col1,Col2/Col3 label Col2/Col3''"), "where Col2 is not null"))