Google Sheet: how to align two timeseries

Question

Using Google Finance in Google Sheet, I get two timeseries:

A) =GOOGLEFINANCE("SPY", "close", DATE(2015,1,1), TODAY(), "DAILY") that has 1483 values

B) =GOOGLEFINANCE("IBTM", "close", DATE(2015,1,1), TODAY(), "DAILY") that has 1489 values

For instance, you can see that A has NO value on 19/01/2015, however B has a value on 19/01/2015. This behaviour creates a misalignment between the two series.

Now, I would like to compute A(close)/B(close) when A(date)==B(date).

Unfortunately, I cannot operate directly on the columns because the two series are not aligned. Is there any smart way to solve this issue?

player0 · Accepted Answer · 2020-11-22T19:32:46.047

sure:

=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))

or directly already divided:

=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''"))

update:

=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"))

Thanks for you answer. Is there also a way to select the value ONLY if both Col1 and Col2 have a value, in order to remove the holes? — SubZeno, Nov 22 '20 at 19:18

Google Sheet: how to align two timeseries

1 Answers1