Function Problems

Fix Excel STOCKHISTORY Returning Incorrect Values for VTV and BLV

Ayan Masood Sep 28, 2026 869 views

Question details

The user needs to resolve an issue where the STOCKHISTORY function retrieves inaccurate historical pricing data for certain ticker symbols like VTV and BLV.

How to Fix STOCKHISTORY Returning Incorrect Values for VTV and BLV in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Retrieving historical stock or fund prices using the STOCKHISTORY function in a spreadsheet.
Observed behavior: The function returns incorrect numerical values for specific ambiguous ticker symbols because they are listed on multiple exchanges.

Before you start

Ensure that your device has an active internet connection, as the STOCKHISTORY function relies on real-time data from online providers to fetch accurate market prices.

Solution 1Recommended

Use Exchange-Qualified Ticker Symbols

Adding the specific stock exchange prefix to the ticker symbol resolves ambiguity and forces Excel to fetch data from the correct security.

When a ticker symbol is listed on multiple exchanges, Excel might pull data from the wrong one by default. By qualifying the ticker symbol with its specific exchange (such as ARCX for NYSE Arca), you ensure that the STOCKHISTORY function retrieves the exact fund or stock you intend to track.

Locate the formula

Select the cell containing your current STOCKHISTORY formula that is returning incorrect values.

Add the exchange prefix

Edit the formula to include the exchange prefix before the ticker symbol, separated by a colon. For example, change "VTV" to "ARCX:VTV".

Complete and apply the formula

Ensure your formula parameters are correct, such as =STOCKHISTORY("ARCX:VTV", 45558, 45558, 0, 0, 1), and press Enter to update the cell.

Tip: This method also applies to BLV and any other ETF or stock returning unexpected results. Always verify the correct exchange code for your security.

Solution 2

Allow Time for Data Source Corrections

Occasionally, the incorrect data is due to a temporary glitch from the financial data provider rather than a formula error.

Free Microsoft Office alternative

Try WPS Office for Your Financial Spreadsheets

If you are experiencing ongoing issues with Microsoft Office functions or are looking for a reliable, cost-effective solution, WPS Office offers a comprehensive suite. It is highly compatible with Microsoft formats, lightweight, and completely free to use.

1. Download the software: Visit the official WPS website and download the free WPS Office suite for your operating system.
2. Install WPS Office: Run the downloaded installer and follow the on-screen prompts to complete the installation.
3. Open your spreadsheets: Launch WPS Spreadsheet and open your existing financial trackers to continue working seamlessly.

Seamlessly open, edit, and save Microsoft Excel (.xlsx) workbooks without formatting loss.Rich library of built-in financial and statistical functions for accurate data analysis.Lightweight installation that runs smoothly on Windows, Mac, Linux, and mobile.Free to use with a familiar tabbed interface for quick and easy onboarding.

microsoft office alternative - wps office

Frequently Asked Questions

Why does STOCKHISTORY return a #VALUE! or #NAME? error?

A #VALUE! error usually means the date parameters are incorrect or unrecognized. A #NAME? error indicates that you may not have a Microsoft 365 subscription, which is currently required to access the STOCKHISTORY function.

How do I find the correct exchange prefix for my ticker symbol in Excel?

You can use the built-in Stocks data type feature. Type the company name or ticker in a blank cell, go to the Data tab, and click 'Stocks'. Click the small building icon that appears next to the cell to view the full exchange-qualified ticker provided by the data provider.

Can I use STOCKHISTORY to get real-time stock prices?

No, STOCKHISTORY is designed specifically for pulling past historical data. To get real-time or current stock prices, you should convert your cell using Excel's Stocks Data Type and reference the 'Price' field.

Does the STOCKHISTORY formula update automatically?

Yes, the data refreshes automatically when you open the workbook or when you manually click 'Refresh All' on the Data tab, provided you have an active internet connection.

Excel & Data Problems