Ravindra BagaleCourses & study guides Track your progress

Excel STOCKHISTORY Function

By · Last updated:

Chala mitrano — today we learn STOCKHISTORY. Read Why → What → How, then try the practice list.

Version note. Microsoft 365 with Stocks / Finance data connected. Needs internet. Availability varies by region and licence.
Fictional sample data for learning. Tickers and dates are for formula practice. Not investment advice. Availability varies.

Purpose

Pull historical stock price data into a spill grid (Finance / Stocks data features).

Return value

An array of dates and price fields for the requested instrument and interval.

Syntax

STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], …)

Arguments

ArgumentRequired?Description
stockRequiredTicker / instrument text (market-dependent).
start_dateRequiredFirst date.
end_dateOptionalLast date.
intervalOptional0 daily, 1 weekly, 2 monthly (confirm on your build).
headersOptionalInclude header row or not.
property…OptionalWhich fields (close, open, etc.).

Version

Microsoft 365 with Stocks / Finance data connected. Needs internet. Availability varies by region and licence.

How to use — Why → What → How

Why: Finance learners want OHLC history without copy-paste from websites.

What: STOCKHISTORY spills a dated price table you can chart.

How — numbered steps:

  1. Confirm Stocks data type works on a sample ticker in your Excel.
  2. Try a short date window first.
  3. Spill to a clear area; chart Close.
  4. Document that data is for learning — not trading advice.
  5. If #FIELD! / #CONN! appears, check licence and connection.

Ravindra Bagale's Tip

This is educational market data practice — not investment advice.

Ravindra Bagale's Tip

Start with a famous ticker your region supports; inactive tickers fail.

Worked examples

1) Short daily window

Learning file.

Formula: =STOCKHISTORY("MSFT", DATE(2026,1,2), DATE(2026,1,15), 0, 1)

Result: Dated price table

What it means: Daily with headers — ticker must resolve.

2) Weekly interval

Fewer rows.

Formula: =STOCKHISTORY("MSFT", DATE(2025,1,1), DATE(2025,6,30), 1, 1)

Result: Weekly rows

What it means: Interval 1.

3) Chart close

Select Close column → line chart.

Formula: (after STOCKHISTORY spills)

Result: Line chart

What it means: Visual check.

4) LET for dates

Cleaner.

Formula: =LET(s, DATE(2026,1,2), e, DATE(2026,1,31), STOCKHISTORY("MSFT",s,e,0,1))

Result: January window

What it means: LET + STOCKHISTORY.

5) Failure demo

Bad ticker.

Formula: =STOCKHISTORY("NOTAREALTICKERXYZ", TODAY()-10, TODAY())

Result: Error

What it means: Shows connection/field errors safely.

Common errors

#CONN! / #BLOCKED!: Data service not available.
#FIELD!: Property not available.
#N/A: Ticker or dates unresolved.

Practice tasks (10+)

  1. Task 1. Pull 10 days of history for a supported ticker.
    Hint: try on a tiny range first
  2. Task 2. Switch daily → weekly.
    Hint: try on a tiny range first
  3. Task 3. Chart Close.
    Hint: try on a tiny range first
  4. Task 4. Wrap with LET for start/end.
    Hint: try on a tiny range first
  5. Task 5. Write a disclaimer cell: learning only.
    Hint: try on a tiny range first
  6. Task 6. Explain licence needs.
    Hint: try on a tiny range first
  7. Task 7. Compare to manual CSV download.
    Hint: try on a tiny range first
  8. Task 8. Document your region availability.
    Hint: try on a tiny range first
  9. Task 9. Handle errors with IFERROR message.
    Hint: try on a tiny range first
  10. Task 10. Bonus: calculate simple daily return with MAP/LAG pattern carefully.
    Hint: try on a tiny range first

Quick quiz

Q1. STOCKHISTORY needs connectivity and supported data.
Q2. Output is useful as trading advice automatically.
Q3. You can chart the spilled Close column.
Q4. Works offline always.

Answers: Q1=True, Q2=False, Q3=True, Q4=False

Related: LAMBDA · LET · GROUPBY · Function hub · Learning path