Function Problems

Why Does STOCKHISTORY Return Duplicate Stock Prices in Excel?

John WilsonJohn Wilson Nov 10, 2026 868 views

Question details

The STOCKHISTORY function is returning duplicate Open, High, Low, Close (OHLC) records for identical dates.

How to Fix STOCKHISTORY Returning Duplicate Stock Prices
Product
Excel
Device & OS
not provided
Scenario
Pulling historical stock market data dynamically using the STOCKHISTORY formula.
Observed behavior
The formula generates multiple identical entries for the same date instead of returning a single historical record per day.
Before you start

Verify your internet connection and check online community forums to see if the external market-data provider is currently experiencing widespread data-quality issues or outages.

Solution 1Recommended

Filter Duplicates Using the UNIQUE Function

Wrap your existing STOCKHISTORY formula in a UNIQUE function to instantly filter out identical duplicate records from your results.

Since STOCKHISTORY is a dynamic array function, you cannot manually delete individual duplicate rows in the spilled results. Using the UNIQUE function solves this seamlessly by evaluating the entire returned array and keeping only distinct rows.

1
Select your formula cell

Click on the top-left cell where your original STOCKHISTORY formula is entered.

2
Modify the formula

Click into the formula bar and wrap the existing function with UNIQUE. For example, modify =STOCKHISTORY("MSFT", "1/1/2023", "1/31/2023") to =UNIQUE(STOCKHISTORY("MSFT", "1/1/2023", "1/31/2023")).

3
Apply the changes

Press Enter to execute the updated formula. The dynamic array will recalculate and spill the cleaned data without duplicate records.

Check Data Accuracy: Verify that removing duplicates does not accidentally discard legitimate records that might have slight variations in trading volume or other fields.
Free Microsoft Office alternative

Try WPS Office for Reliable Spreadsheet Data Management

If you frequently experience issues with external connected services or complex dynamic arrays in your spreadsheets, consider trying WPS Office. It provides a lightweight, highly compatible, and free alternative to Microsoft Office for your daily data analysis and financial calculations.

Highly compatible with Microsoft Excel file formats (.xlsx, .csv, .xls)Rich library of built-in financial, statistical, and array functionsLightweight application with fast installation and smooth performanceFree to use with a familiar interface for quick and seamless migration
microsoft office alternative - wps office

Frequently Asked Questions

Can I manually delete duplicate rows returned by STOCKHISTORY?

No. Because STOCKHISTORY is a dynamic array formula, the results 'spill' into adjacent cells automatically. You cannot edit, delete, or modify individual cells within a spilled array. You must wrap the formula in a UNIQUE function to filter the results.

Why does the financial data provider send duplicate records?

Duplicates usually occur due to temporary server glitches, database synchronization issues, or data-quality corrections happening on the external provider's backend (such as Refinitiv).

Will the UNIQUE function slow down my spreadsheet?

Wrapping STOCKHISTORY in a UNIQUE function is highly optimized and should not noticeably impact calculation speed for standard historical stock queries. However, applying it to massive datasets covering decades of daily data for hundreds of tickers simultaneously may require more processing power.