google-sheets

Google sheets ARRAYFORMULA() element-wise operation evaluates even though the QUERY() arrays used limts the sizes such that they should mismatch?

I am using google sheet's ARRAYFORMULA() to do element-wise operations and I am seeing that even when the arrays are not the same size, the operation still works. Why is this? I would think it should throw a standard "Array size mismatch" error.

I have a simple google sheets ARRAYFORMULA() that is measuring whether a series of stock prices is greater or lesser than the closing price the day before over a fixed lookback period. I do this (for 44 day lookback window) via...

=ARRAYFORMULA(
   IF(QUERY(GOOGLEFINANCE("SPY", "close", WORKDAY(TODAY(), -44), WORKDAY(TODAY(), -0)), CONCATENATE("select Col2 order by Col1 desc limit ", 44), 0) >
      QUERY(GOOGLEFINANCE("SPY", "close", WORKDAY(TODAY(), -44-1), WORKDAY(TODAY(), -1)), CONCATENATE("select Col2 order by Col1 desc limit ", 44), 0),
      1,
      0
   )
)

(I'm getting an array of closing prices from today back to 44 days, using query to only get the 2nd column and ordering the results in descending order so that the most recent date data is first (as GOOGLEFINANCE() function returns the data in order of oldest to newest date data values, with a date and values header, which I skip in via the QUERY() function's query string; example below))

enter image description here

Here is the weird thing... The shifted array (the one shifted back by 1 day for making the comparisons) is not actually 44 elements long. I run the first query in the sheet and get 44 elements and run the second "shifted" query and get 43 elements. It turns out that is so happens that 44 work days ago from today was a market holiday (June 19th, which you can see as the image attached uses data from June 20th, so the 1-day shifted array starts it's data on June 19th, though it actually returns data starting at June 20 since there is no June 19 data), so the shifted-range GOOGLEFINANCE() call only actually returns 43 days of data.

Yet, this arrayformula runs without error and I get 44 elements returned (1s and 0s in a column). What is happening here?

Solution

  • It's not QUERY that it's returning 44 values, it's your GOOGLEFINANCE formula.

    One way to solve this is by changing this portion of the formula:

    CONCATENATE("select Col2 order by Col1 desc limit ", 44)

    with this

    CONCATENATE("select Col2 where Col2 is not null order by Col1 desc limit ", 44)