Get an intraday historical price at a specific time

To get an intraday historical price at a particular time, pull the intra-day time series with the Date (with time) and price columns, then select the row for that timestamp with a native function such as XLOOKUP, FILTER, or QUERY — or wrap everything in LET for a single-cell formula. SF_TIMESERIES() takes start and end dates only, so the time-of-day selection happens in the sheet.

Pull the intra-day series with date and price

Request the timestamp and close columns for the day you care about, at the finest interval that covers it. Each row is one interval bar, with the timestamp (the Date (with time) metric) in the first column and the close in the second.

=SF_TIMESERIES("AAPL", "2023-02-01", "2023-02-01", "5min", "date&close")

Replace "5min" with any supported intra-day interval, and "date&close" with the metrics you need (for example "date&open&high&low&close&volume" or "all").

Intra-day requests are limited to these ranges. If the requested range is larger than the maximum, the output is constrained to the maximum range using the endDate as the reference and startDate is ignored, so keep the date you are looking up inside the returned range. The finer the interval, the closer the returned bar is to the time you want, so use 1min or 5min whenever the date falls inside their limits.

Look up the exact time with XLOOKUP

XLOOKUP returns the price from the row whose timestamp equals the time you specify, and works in both Excel and Google Sheets. Keep the series formula in cell A1 so the output spills into columns A and B, then build the lookup time in another cell (for example D1):

=DATE(2023, 2, 1) + TIME(10, 30, 0)

Point XLOOKUP at the data rows below the header:

=XLOOKUP(D1, A2:A, B2:B)

XLOOKUP returns #N/A when no bar matches exactly. A 5min series has a bar only at each 5-minute mark, so 10:33 has no exact row — use the FILTER pattern below to get the most recent bar at or before the time.

Filter to a time with FILTER

FILTER returns every row whose timestamp matches your condition, so you can inspect the matching bar or select the nearest one. Exact timestamp:

=FILTER(B2:B, A2:A = D1)

Most recent bar at or before the requested time (when the time falls between bars):

=INDEX(SORT(FILTER(A2:B, A2:A <= D1), 1, FALSE), 1, 2)

The SORT/FILTER form keeps the rows at or before the time, sorts them newest first, and takes the first close, so it works whatever order the series returns in.

Query the series with QUERY (Google Sheets)

QUERY filters the returned table with a where clause and is available in Google Sheets only. It expects the header row, so point it at the full output starting in A1:

=QUERY(A1:B, "select Col2 where Col1 = datetime '2023-02-01 10:30:00'")

For the most recent bar at or before a time between bars, compare with <= and take the latest row:

=QUERY(A1:B, "select Col2 where Col1 <= datetime '2023-02-01 10:30:00' order by Col1 desc limit 1")

Do it in one cell with LET

LET names the series once and reuses it, so a single cell returns the price and the series is fetched only once. The "NH" option removes the header row, and INDEX(ts, 0, 1) and INDEX(ts, 0, 2) select the timestamp and close columns:

=LET(
  ts, SF_TIMESERIES("AAPL", "2023-02-01", "2023-02-01", "5min", "date&close", "NH"),
  XLOOKUP(DATE(2023, 2, 1) + TIME(10, 30, 0), INDEX(ts, 0, 1), INDEX(ts, 0, 2))
)

LET works in both Excel and Google Sheets. Repeating SF_TIMESERIES in several cells multiplies the function calls; Performance Tips notes that fewer function calls means better performance and lower quota usage, so the single-call form is the cheaper option. For more ways to combine functions in one cell, see Use LET and ARRAYFORMULA with SheetsFinance.

Which method to choose

• XLOOKUP — one cell, exact match, works in Excel and Google Sheets. Best default.

• FILTER — returns the matching rows so you can inspect the bar; use the SORT/FILTER form for the most recent bar at or before a time.

• QUERY — Google Sheets only; useful when you want to combine several conditions in one where clause.

• LET — wraps any of the above into one cell and fetches the series once, which reduces function calls and quota usage.

What to watch for

• Exact matches exist only on interval boundaries. A 5min series has no bar at 10:33, and a time outside the returned range has no row.

• Match date-time values, not text. Build the lookup with DATE() and TIME(), or point at a cell that holds a date-time value; a typed string such as "2023-02-01 10:30" does not equal the numeric timestamp in the sheet.

• Intra-day ranges are limited per interval. A request larger than the maximum is constrained from the endDate, so confirm the time you want falls inside the returned range.

• The LET version keeps everything inside one cell; nested formulas that spill into occupied cells raise a #REF! array expansion error.

What is next

• Intra-day time series — intervals, metrics, and range limits.

• Get a single value from a multi-row function — pull one cell from any multi-row result.

• Calculate high, low, and average prices over a custom period — aggregate a whole window instead of one timestamp.