Function Problems

How to Display Historical Stock Opening Price in One Excel Cell

Maira Mehtab Sep 20, 2026 869 views

Question details

The user wants to retrieve the historical opening price for a specific stock on a specific date (e.g., IRM on August 21, 2023) and display it in a single cell, avoiding the default 2x2 array.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Extracting a single historical stock price data point using formulas without returning unnecessary headers and dates.
Observed behavior: The STOCKHISTORY function defaults to returning a 2x2 spilled array containing headers, dates, and the price, rather than just the specific opening price value.

Before you start

Ensure you have an active Microsoft 365 subscription and a stable internet connection, as the STOCKHISTORY function requires online connectivity to fetch financial data.

Solution 1Recommended

Configure STOCKHISTORY Parameters to Suppress Headers

Use the built-in arguments of the STOCKHISTORY function to disable headers and restrict the output to only the opening price.

The STOCKHISTORY function contains optional parameters that allow you to customize the output interval, toggle headers, and specify exact properties like Open, High, Low, or Close.

Select the target cell

Click on the specific empty cell where you want the single stock price to appear.

Enter the modified formula

Type the formula using exact parameter flags to remove the headers. For example: =STOCKHISTORY("IRM", "2023-08-21", , 0, 0, 2)

Understand the parameters

In this formula, the first 0 sets the interval to daily, the second 0 removes the header entirely, and the 2 requests only the 'Open' price property.

Execute the formula

Press Enter. The cell should now display exactly one value (the opening price) without spilling into adjacent rows or columns.

Formula Modification Complete: By setting the header parameter to 0, Excel skips the generation of the 2x2 array and returns a clean scalar value.

Solution 2

Use the INDEX Function to Extract the Value

If your version of Excel ignores the parameter settings and still returns an array, wrap the STOCKHISTORY function in an INDEX function to isolate the price.

Solution 3

Report Known Function Issues to Microsoft Support

Occasionally, the STOCKHISTORY function malfunctions on the server side and returns incorrect arrays despite proper formatting. You may need to report this bug.

Free Microsoft Office alternative

Experience Bug-Free Spreadsheets with WPS Office

If you frequently encounter server-side issues or bugs with advanced Excel functions like STOCKHISTORY, consider using WPS Office. It provides a highly compatible, stable, and lightweight spreadsheet environment.

1. Download the software: Visit the official WPS website to download and install WPS Office for your device.
2. Open WPS Spreadsheet: Launch the application and select 'Spreadsheet' from the main dashboard.
3. Import your files: Open your existing Excel workbooks to continue managing your financial data seamlessly.

Seamless format compatibility with Microsoft Excel (.xlsx, .xls, .csv).Includes hundreds of stable built-in financial, statistical, and logical formulas.Free and lightweight, ensuring rapid calculation speeds even on older devices.Familiar ribbon interface ensures zero learning curve when migrating.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my STOCKHISTORY formula return a #SPILL! error?

A #SPILL! error occurs when the function attempts to output a 2x2 array of dates and prices, but adjacent cells already contain data. You can either clear the surrounding cells or modify the formula to disable headers (using 0 as the header parameter) to return a single value.

Can I get the closing price instead of the opening price?

Yes. In the STOCKHISTORY formula parameters, change the property argument to 1 (Close) instead of 2 (Open). For example: =STOCKHISTORY("IRM", "2023-08-21", , 0, 0, 1).

Why does STOCKHISTORY show a #CONNECT! error?

The #CONNECT! error indicates that Excel cannot reach the online service required to retrieve the financial data. Verify that your device is connected to the internet and that you are actively signed into your Microsoft 365 account.

Excel & Data Problems