15

In Google Sheets, GOOGLEFINANCE returns an array of values, like so:

array of results

The formula is entered in A1 but the result extends into other rows and columns. I would like to "select" (not sure what the exact term is) a subset of this data. For example, I would like to select only the prices, without header (so B2:B22 in the image above). And I would like to do that in one formula, without ever printing the full table.

I tried OFFSET, but it doesn't work since it doesn't recognize the GOOGLEFINANCE formula as a range. Conversely, INDEX works, but only returns one cell at a time, so

 INDEX(GOOGLEFINANCE("TSLA", "price", TODAY()-30, TODAY(), "DAILY"),2,2)

returns "345.66" for example.

1
  • 2
    The linked question asks for just one cell, which is answered in my question (and in the post) by using index. Mine asks about returning several cells. But yes, they're related.
    – Timst
    Sep 28, 2017 at 18:02

1 Answer 1

23

You could use INDEX without a row parameter to return a column

For example,

=INDEX(GOOGLEFINANCE("TSLA", "price", TODAY()-30, TODAY(), "DAILY"),,2)

will return the second column fully. However, To fully manipulate the array, you can use QUERY:

 =QUERY(GOOGLEFINANCE("TSLA", "price", TODAY()-30, TODAY(), "DAILY"), "Select Col1,Col2 where Col2>355 limit 6 offset 1",0)

Here I've made the following conditions:

  • ๐Ÿ„ฟ๐Ÿ…๐Ÿ„ธ๐Ÿ„ฒ๐Ÿ„ด more than ใ€Ž355ใ€(Remove where Col2>355 if you don't need it )
  • Total ๐Ÿ„ป๐Ÿ„ธ๐Ÿ„ผ๐Ÿ„ธ๐Ÿ…ƒ of rows =5
  • ๐Ÿ„พ๐Ÿ„ต๐Ÿ„ต๐Ÿ…‚๐Ÿ„ด๐Ÿ…ƒ the first row
2
  • Fantastic. I completely forgot about QUERY, but that works perfectly. Thanks!
    – Timst
    Sep 28, 2017 at 9:16
  • 1
    This just solved my problem. I wanted the closing price of a day and not the complete array. Here is how modified the formula into =INDEX(GOOGLEFINANCE(A2, "price", C2, 1, "DAILY"),2,2) Here A2 - is my Ticker and C2 is the date
    – Jess
    May 12 at 20:18

Your Answer

By clicking โ€œPost Your Answerโ€, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.