How to Use the Excel STOCKHISTORY Function (Stocks, Bonds & Currencies)

Free Excel workbook

Get the STOCKHISTORY Practice Workbook

Download the ready-to-use workbook and practice the exact formulas from the tutorial.

• Practice pulling daily, weekly, and monthly stock prices in Excel
• See close, high, and volume examples already set up and ready to test
• Use the workbook to build your own automated investment tracker faster

Enter your email below for instant access.

Get the Practice Workbook

Free. Instant access.

What is the Excel STOCKHISTORY Function?

STOCKHISTORY is a dynamic array function in Microsoft 365 that retrieves historical price data for stocks, bonds, or currencies over a specific time range.

Unlike the real-time Stocks Data Type, it is used for trend analysis, charting, and backtesting financial models.

It’s perfect for creating charts, analyzing trends, or comparing multiple stocks over time.

For example, you can use STOCKHISTORY to:

• View monthly closing prices for your favorite stocks.
• Identify high and low prices over a chosen time range.
• Build an in-cell chart to track price changes visually.

The STOCKHISTORY function in Excel is a simple yet powerful tool to get recent stock quotes.

You can use it to monitor your investments, study stock trends, or work on finance-related projects.

Even if you’re not an Excel expert, this function makes it easy to stay informed about the stock market.

What Is the STOCKHISTORY Function?

The STOCKHISTORY function pulls historical stock price data into your Excel spreadsheet.

You can use it to get daily, weekly, or monthly prices for any stock listed on the market.

It’s perfect for creating charts, analyzing trends, or comparing multiple stocks over time.

For example, you can use STOCKHISTORY to:

• View monthly closing prices for your favorite stocks.
• Identify high and low prices over a chosen time range.
• Build an in-cell chart to track price changes visually.

STOCKHISTORY Syntax and Arguments Explained

The STOCKHISTORY function retrieves stock price data for a specific time range and displays it in Excel.

It creates an “array,” meaning the data automatically fills multiple rows and columns.

With STOCKHISTORY, you can:

• Get stock prices daily, weekly, or monthly.
• Retrieve important details like open, close, high, low prices, and volume.
• Display this data in an easy-to-analyze format in your spreadsheet.:

Syntax of STOCKHISTORY

Here’s how the STOCKHISTORY function looks:

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])

Let’s break it down:

1. stock:
   • Enter the ticker symbol for the stock (e.g., "AAPL" for Apple).
   • If you want data from a specific stock exchange, use its code (e.g., "XNAS:MSFT" for Microsoft on NASDAQ).
2. start_date:
   • The earliest date you want data for.
   • You can type the date in quotes (e.g., "01/01/2023") or reference a cell containing a date.
3. [end_date] (optional):
   • The last date for the data. If you skip this, Excel uses the start_date as the end date too.
4. [interval] (optional):
   • The time interval for data:
     • 0 = daily
     • 1 = weekly
     • 2 = monthly
5. [headers] (optional):
   • Controls whether headers appear:
     • 0 = No headers
     • 1 = Show headers (default)
     • 2 = Show headers plus the stock identifier
6. [property0–property5] (optional):
   • Choose the columns to include in your data:
     • 0 = Date
     • 1 = Close price (default)
     • 2 = Open price
     • 3 = High price
     • 4 = Low price
     • 5 = Volume

How to Get a Single Stock Price with STOCKHISTORY

Let’s say you want to find Microsoft’s stock price for December 1, 2020. With Excel’s STOCKHISTORY function, this is quick and easy!

Here’s how you can do it:

• Open Excel: Start with a blank spreadsheet.
• Enter the STOCKHISTORY Formula:
  • In a cell, type the following formula:

=STOCKHISTORY(“MSFT”, “12/1/2020”)

• "MSFT": This is Microsoft’s stock ticker symbol.
• "12/1/2020": This is the date you’re interested in.

View the Result:

• After pressing Enter, Excel will return two columns:
  • The Date (12/1/2020).
  • The Closing Price of Microsoft stock on that day.

Featured Course

Fundamentals of Financial Analysis

Read financial statements properly and learn the Excel formulas behind financial reporting. For people moving into finance, and for people who want to be sure nothing is missing.

Learn More

How to Retrieve Multiple Data Columns (Close, High, Volume)

Let’s expand on the basic STOCKHISTORY function example. Here, we’ll learn how to:

• Retrieve daily stock prices for Microsoft (MSFT) during the last quarter of 2020.
• Customize the data columns to include Date, Close, High, and Volume.
• Use dynamic and regional-friendly date strategies for better flexibility.

Suppose you want Microsoft’s daily stock prices for October 1, 2020, to December 31, 2020.

Use this formula:

=STOCKHISTORY(“MSFT”, “10/1/2020”, “12/31/2020”, 0, 1, 0, 1, 3, 5)

Here’s what each argument means:

• 0, 1, 3, 5: Shows Date, Close price, High price, and Volume (in that order).
• "MSFT": Microsoft’s stock ticker symbol.
• "10/1/2020": Start date.
• "12/31/2020": End date.
• 0: Daily prices.
• 1: Includes headers in the output.

You can rearrange the columns by changing the order of the “property” arguments.

For instance, to show Volume, Open, Low, High, and Close for December 31, 2020, use:

=STOCKHISTORY(“MSFT”, “12/31/2020”, 0, 1, 5, 2, 4, 3, 1)

How to Use Dynamic Dates with TODAY and EDATE

Instead of hardcoding dates, consider dynamic strategies to make your formula more flexible and compatible with regional settings:

Using the Excel TODAY Function

• Use the TODAY Function to automatically fetch the latest stock prices up to the current date.
• Example:

=STOCKHISTORY("MSFT", "10/1/2020", TODAY(), 0, 1, 0, 1, 3, 5)

Using the Excel DATE Function

• The DATE function ensures compatibility with different regional date formats.
• Example:

=STOCKHISTORY("MSFT", DATE(2020, 10, 1), TODAY(), 0, 1, 0, 1, 3, 5)

Using Cell References:

If you want to fetch stock information for the past two years from today’s date, you can use the EDATE function to dynamically calculate the start date.

This method keeps your formula flexible and automatically adjusts based on the current date.

1. Define the end_date:

• In cell B3, use the Excel TODAY function to dynamically fetch the current date.

=TODAY()

2. Calculate the start_date:

• In cell B1, calculate the start date dynamically:

=EDATE(TODAY(), -24)

• This formula will return the date 24 months (2 years) before today.

3. Combine with STOCKHISTORY:

• In our example, the start date is in cell B1, the end date in cell B3.
• Use this STOCKHISTORY formula to retrieve the data

=STOCKHISTORY("MSFT", B1, B3, 2, 1, 0, 1)

How to Specify a Stock Exchange in STOCKHISTORY

If you need stock information from a specific exchange, you can specify the exchange symbol by adding it before the stock ticker, separated by a colon.

This ensures that the data comes from the correct exchange.

If we wanted to pull data from the Wiener Boerse Exchange in Austria, the formula would appear as follows.

=STOCKHISTORY(“XWBO:MSFT”, A1, TODAY(), 2, 1, 0, 1)

How to Build an Automated Investment Portfolio in Excel

Creating a stock report is a powerful way to track financial performance over time.

In this example, we’ll walk through how to build a free Excel investment portfolio spreadsheet. We’ll create a monthly stock close report for multiple companies, spanning the last two years.

Step 1: Set Up Your Ticker Symbols and Start Dates

1. Start Date: Place the starting date in cell A1 (e.g., 01/01/2022).
2. Stock Ticker Symbols: List the company ticker symbols in cells A3(e.g., MSFT, AAPL, GOOGL).

Step 2: Use STOCKHISTORY to Retrieve Monthly Closing Prices

We want to retrieve the monthly close prices for each company.

1. Basic Formula:
Write this formula in D3 to retrieve data for the stock listed in A3:

=STOCKHISTORY(A3, $A$1, TODAY(), 2, 0, 1)

Result: The function spills values vertically into the rows below D3.

2. Transform the Output:
To display results horizontally across a row, nest the formula in a TRANSPOSE function:

=TRANSPOSE(STOCKHISTORY(A3, $A$1, TODAY(), 2, 0, 1) )

3. Fill Down:
Drag the formula in D3 down to the rows corresponding to the other tickers (D4 – D7) to retrieve data for all companies.

Step 3: Generate Dynamic Date Headers Using INDEX & TRANSPOSE

The report needs a date header that matches the start_date and end_date range of the STOCKHISTORY function.

1. Initial Formula (Fails):
Attempting this formula to fetch only the dates:

=STOCKHISTORY(A3, $A$1, TODAY(), 2, 0, 0)

This fails because the function requires at least one additional argument, such as Close prices.

2. Correct Formula:
Update the formula to include Date and Close information (default behavior):

=STOCKHISTORY(A3, $A$1, TODAY(), 2, 0)

3. Transpose the Results:
To spill the dates horizontally, use:

=TRANSPOSE(STOCKHISTORY(A3, $A$1, TODAY(), 2, 0) )

4. Restrict to Dates Only:
Nest the formula in an INDEX function to extract only the first row (dates):

=INDEX(TRANSPOSE(STOCKHISTORY(A3, $A$1, TODAY(), 2, 0) ), 1)

• Place this formula in D2 to generate the date header.

Alternate Approach to Headers

Instead of building the date header separately, you can:

1. Use the STOCKHISTORY function to fetch both dates and data for the first company (e.g., MSFT).
2. Use a second STOCKHISTORY formula to retrieve just the stock data for the remaining companies.

For example:

• First company formula (dates and close prices):

• Remaining companies formula (close prices only):

How to Add Sparklines to Your Stock Report

To give your stock report a professional and visually appealing touch, you can add Sparklines to represent the ups and downs of each company’s monthly data.

Sparklines are mini charts that fit inside a single cell, making them perfect for quick trend analysis.

Step 1: Prepare Your Layout for Mini-Charts

1. Adjust Row Height:
   • Increase the height of the rows containing company names (e.g., rows 3 to 7) to make the Sparklines more readable.
2. Resize Column C:
   • Widen Column C to give the Sparklines enough room for clear visualization.

Step 2: Insert and Define the Sparkline Range

• Select a Cell:
  • Choose the cell where you want the Sparkline for the first company. For example, select C3.
• Open the Sparklines Dialog:
  • Go to the Insert tab, then click on Sparklines in the Charts group.
  • Select Line from the dropdown.

• Define the Data Range:
  • In the Create Sparklines dialog box, enter the range of monthly closing prices for the first company.
  • For instance, D3:AB3.

The results are as follows:

Step 3: Auto-Fill Sparklines for Multiple Tickers

• Use the Fill Handle:
  • Drag the fill handle from C3 down to the other rows (C4 – C7) to replicate the Sparkline for each company.
  • Excel automatically adjusts the data range for each Sparkline to match the corresponding company’s data.

Step 4: Highlight Performance with High & Low Markers

Use the Sparkline Tools ribbon to enhance your charts:

1. Line Color:
   • Change the line color to match your report’s theme.
   • Example: Set Microsoft (MSFT) to blue, Apple (AAPL) to green, etc.
2. Line Thickness:
   • Increase the thickness for better visibility.
3. Highlight Highs and Lows:
   • Enable markers for the highest and lowest points in the data.
   • Customize colors for High Points (e.g., green) and Low Points (e.g., red).
4. Add Markers:
   • Add markers to show all data points for more detail.
5. Adjust Axes:
   • Use the Axis Controls to standardize the scale across all Sparklines, ensuring accurate comparisons between companies.

STOCKHISTORY vs Stocks Data Type: What Is the Difference?

In previous examples, we manually typed the ticker symbols for the companies we wanted to analyze.

However, Excel offers a powerful feature—the Stocks Data Type—to make your stock data more dynamic and interactive.

By converting your ticker symbols into the Stocks data type, you can unlock real-time metrics and additional features to enhance your stock tracking.

Ready to build a complete financial dashboard? Once you’ve mastered historical trends, follow our guide to mastering the Excel Stocks Data Type to add instant, live portfolio tracking to your spreadsheet.

How to Get Currency Exchange Rates with STOCKHISTORY

Despite the name, STOCKHISTORY works with currency exchange rates too. Enter the two ISO currency codes separated by a colon as the stock argument:

=STOCKHISTORY("USD:EUR", "1/1/2025", TODAY(), 2, 1, 0, 1)

This returns monthly USD-to-EUR exchange rates from January 2025 to today, with dates and closing rates.

To reverse the direction (EUR to USD), swap the codes:

=STOCKHISTORY("EUR:USD", "1/1/2025", TODAY(), 2, 1, 0, 1)

Single-cell exchange rate trick: To return only the exchange rate in one cell (no date column, no headers), use:

=STOCKHISTORY("USD:EUR", TODAY()-1, , , 0, 1)

This returns yesterday’s closing rate as a single number. Use TODAY()-1 instead of TODAY() because the closing rate for today may not be available yet depending on your time zone. In some regions east of the US (Europe, Asia, Australia), you may need TODAY()-2.

Practical example: To convert a foreign currency payment to your home currency based on the transaction date:

=D2 * STOCKHISTORY(E2 & ":GBP", C2, , , 0, 1)

Where D2 is the payment amount, E2 is the currency code (e.g., “USD”), and C2 is the payment date. This formula fetches the exchange rate for that specific date and multiplies.

Weekend and holiday dates: If the transaction falls on a day markets were closed, STOCKHISTORY returns a #VALUE! error.

To handle this, fetch a short range and take the most recent value:

=MAX(STOCKHISTORY("USD:EUR", C2-2, C2, , 0, 1))

This pulls the last three days of rates and returns the highest (most recent available), avoiding the closed-market error.

For more on dynamic date calculations in Excel, see our guide on Excel basic math operations.

How to Fix STOCKHISTORY Errors (#CONNECT!, #VALUE!, #BUSY!)

STOCKHISTORY pulls live data from an external source, which means things can break for reasons that have nothing to do with your formula.

Here’s what each error means and what to do about it.

#BLOCKED! — The Most Common Source of Confusion

This error is almost always one of two things:

Wrong Excel version. STOCKHISTORY requires a Microsoft 365 subscription. Excel 2019, Excel 2021, and Excel 2024 (one-time purchase) do not include this function, even if they look identical.

Connected Experiences is turned off. This setting controls whether Excel can access online data. To turn it on: go to File → Options → Trust Center → Trust Center Settings → Privacy Options → enable “Turn on optional connected experiences.” Restart Excel after saving.

On a corporate or school network? Firewalls and proxies frequently block Excel’s outbound connections to data services. Try switching to a personal wifi network to test. If that fixes it, your IT team will need to whitelist Microsoft’s stock data endpoints.

#CONNECT! — Service Outages Are Real

STOCKHISTORY data comes from LSEG Data & Analytics (formerly Refinitiv). When LSEG’s feed has issues, or when Microsoft’s servers are overloaded, your formulas will show #CONNECT!, even though your formula is perfectly correct.

This has happened multiple times, including a widely-reported outage around January 1–4, 2026 that affected users across Windows, Mac, and Excel for the web. Microsoft support confirmed it as a server-side issue with no user-level fix available.

What to do during an outage:

• Check the Microsoft 365 Service Health dashboard: admin.microsoft.com → Health → Service Health
• Wait. These outages typically resolve within hours to a few days.
• Don’t reinstall Excel, change your formula, or contact support expecting a fast fix. It won’t help while the upstream service is down.

Prevention tip: If you rely on STOCKHISTORY for time-sensitive work, build a snapshot workflow. Use Power Query to cache the previous day’s prices into a static table, and reference that table instead of live STOCKHISTORY calls when you need reliability over real-time data.

Which Excel Versions Support STOCKHISTORY?

Required: A Microsoft 365 subscription — Personal, Family, Business Standard, or Business Premium.

Works on:

• Excel for Windows (Microsoft 365)
• Excel for Mac (Microsoft 365)
• Excel for the web

Does NOT work on:

• Excel 2019 (one-time purchase)
• Excel 2021 (one-time purchase)
• Excel 2024 (one-time purchase) — despite being a recent version, one-time licenses don’t include connected data features
• Excel for iPad or mobile apps
• Excel Starter or any free/trial version

Important: Even with a valid Microsoft 365 subscription, STOCKHISTORY won’t work if:

• You’re signed into Excel with a different account than the one holding the subscription
• Connected Experiences is disabled in your Trust Center settings
• Your organization’s network blocks outbound data connections

Frequently Asked Questions

Does STOCKHISTORY work in Excel 2021 or Excel 2024?

No. STOCKHISTORY requires a Microsoft 365 subscription (Personal, Family, Business Standard, or Business Premium). Excel 2019, 2021, and 2024 are one-time purchases and do not include connected data features like STOCKHISTORY, even though they may look identical to Microsoft 365.

Can STOCKHISTORY pull live intraday stock prices?

No. STOCKHISTORY returns historical data recorded after market close. Data updates once per trading day, typically three to four hours after the market closes. For current prices, use the Stocks Data Type instead.

Can I use STOCKHISTORY for cryptocurrency prices?

Direct cryptocurrency prices (like BTC or ETH) are generally not available through STOCKHISTORY. However, some crypto-related ETFs and stocks may work if they are listed in the LSEG database. For crypto prices, consider using Power Query with a public API.

Where does STOCKHISTORY get its data?

All data comes from LSEG Data & Analytics (formerly Refinitiv). Microsoft provides it “as-is” and notes it may be delayed. Do not use STOCKHISTORY for real-time trading decisions.

Why does STOCKHISTORY return dates that are different from what I entered?

When using weekly (1) or monthly (2) intervals, STOCKHISTORY returns the last available price within each period. A monthly interval starting December 31 may return December 1 data because that is the first available monthly data point. This is expected behavior.

Download the Investment Portfolio Template

Featured Bundle

Black Belt Excel Bundle

This Excel Black Belt Package includes EIGHT of our Popular Courses. You’ll learn high-value, in-depth Excel skills that solve real problems.

Learn More

LinkedIn Twitter YouTube

Leila Gharani

Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.

More About Leila Join 500,000+ professionals in our courses