How to extract specific cell value from GOOGLEFINANCE data using QUERY function

Other Questions Google Sheets
1

I’m building a trading tracker in Google Sheets and need help with getting historical price data. I want to calculate percentage changes over different time periods like 7, 30, and 90 days.

When I use the GOOGLEFINANCE function, it gives me a table with dates and closing prices. I need to grab just one specific price value from this data - the oldest price in the range.

Here’s my current formula that gets AAPL prices for the last 7 days:

=googlefinance("AAPL", "close", today()-7, today())

This creates a table where the price I need is always in the second column, second row (let’s say it shows 145.67).

I tried using QUERY to get just the price column:

=query(googlefinance("AAPL", "close", today()-7, today()), "select Col2 label Col2 ''")

This removes the header and shows only prices, but I still get all the daily values stacked vertically. I only want that first price value (145.67) to appear in my cell.

How can I modify my QUERY to return just that single oldest price value instead of the entire column?

2

heres another option if your data gets messy - use the SMALL function:

=SMALL(googlefinance("AAPL", "close", today()-7, today()), 1)

this pulls the lowest price from the range, which might not be what ur after tho. if u just want the oldest date’s price, stick with charlottew’s query method. its way more reliable than INDEX when googlefinance gets wonky.

3

You’re on the right track with QUERY, but add a LIMIT clause to grab just the first row:

=query(googlefinance("AAPL", "close", today()-7, today()), "select Col2 limit 1 offset 1 label Col2 ''")

offset 1 skips the header, limit 1 returns only the first data row. Gets you exactly that single oldest price.

I’ve used this in my portfolio tracker for months - works great. Just heads up: GOOGLEFINANCE gets flaky on weekends/holidays, so consider adding error handling if you’re automating this.

4

You can also use INDEX to grab that cell directly without QUERY:

=index(googlefinance("AAPL", "close", today()-7, today()), 2, 2)

This pulls row 2, column 2 from your GOOGLEFINANCE result - that’s where the oldest price lives. INDEX is cleaner than QUERY for single cells since there’s way less syntax.

I’ve been doing this in my trading sheets for over a year. Quick heads up: when markets are closed or you hit weekends, GOOGLEFINANCE sometimes returns fewer rows than you expect. Wrap it in IFERROR if you want bulletproof automation, otherwise you’ll get reference errors when the data structure shifts.