4

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.

  • 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 '17 at 18:02
5

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
  • Fantastic. I completely forgot about QUERY, but that works perfectly. Thanks! – Timst Sep 28 '17 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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