Search code examples
google-sheetsgoogle-finance

Google Sheet: how to align two timeseries

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?

Solution

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

    enter image description here

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