Google Sheet, Finance: FILTER twice to get only one cell

Question

I have a Google Sheet with some stock information.

I'm using the formula GOOGLEFINANCE($B2, "price",TODAY()-15) to retrieve historical information about a stock (symbol named in $B2).

That returns a 2x2 table:

Date                 Close
8/25/2017 17:36:00   7.46

I only want the 7.46:

Using =FILTER(GOOGLEFINANCE($B2, "price",TODAY()-15),{FALSE; TRUE}) I get:

8/25/2017 17:36:00   7.46

I can't see to be able to nest FILTER twice.

I checked the documentation. Other than say that I should not use FILTER to filter columns and rows in the same call, I didn't get much out of it.

score 3 · Answer 1 · answered Sep 24 '17 at 11:55

3

I gather a lot of stock information and always use index to get the stock price.

=index(GOOGLEFINANCE($B2, "price",TODAY()-15),2,2)

If you want the date use this. Be sure to format the cell as date or date/time.

=index(GOOGLEFINANCE($B2, "price",TODAY()-15),2,1)

answered Sep 24 '17 at 11:55

Ed Nelson

This solution is better for this particular use case, but it's nice to know the general case. – Ivan Perez Sep 24 '17 at 15:53

score 2 · Accepted Answer · answered Sep 24 '17 at 11:32

2

For 2 filters try:

=FILTER(FILTER(GOOGLEFINANCE($B2, "price",TODAY()-15),{false;true}),{false,true})

I prefer query in this case:

=QUERY(GOOGLEFINANCE($B2, "price",TODAY()-15),"select Col2 label Col2 ''")

Also please try this formulas separately:

={false;true}

={false,true}

and see the result.

answered Sep 24 '17 at 11:32

Max Makhrov

That was not intuitive at all. The last part with the comma vs semi-colon explained everything. – Ivan Perez Sep 24 '17 at 15:55

2 Answers2