Function Problems

How to Get the Previous Trading Day's Volume Using STOCKHISTORY in Excel

Natalie Taylor Oct 7, 2026 870 views

Question details

The user needs a reliable formula to retrieve yesterday's stock trading volume, specifically compensating for market closures like weekends or holidays.

How to Get the Previous Trading Day's Volume Using STOCKHISTORY

Product: Excel
Device & OS: not provided
Scenario: Pulling historical stock market data for the most recent valid trading day to track financial volume.
Observed behavior: Formulas targeting specifically yesterday (such as TODAY()-1) fail or return an error because no data exists for days when the market is closed.

Before you start

Ensure you have an active internet connection so the application can fetch the latest financial data, and verify that the correct stock ticker symbol is entered in your reference cell.

Solution 1Recommended

Expand the Date Range in the Formula

Request a wider date window (e.g., the past 7 days) so the software can search backward and return the most recent available trading day's data automatically.

When using TODAY()-1, the formula strictly looks for yesterday's data. If yesterday was a Saturday, Sunday, or a recognized market holiday, the stock exchange was closed, resulting in missing data. By expanding the date range, the function will dynamically pull the most recent valid volume metric available within that window.

Select the target cell

Click on the cell where you want the previous trading day's volume to appear.

Enter the expanded STOCKHISTORY formula

Type the formula =STOCKHISTORY(B2,TODAY()-7,TODAY(),,0,0,5) and press Enter. Replace 'B2' with the actual cell reference that contains your target stock symbol.

Verify the returned data

Check the output to ensure it accurately displays the volume. The formula checks the last 7 days and outputs the latest available trading volume.

Formula Parameter Breakdown: In the provided formula, 'TODAY()-7' designates the start date as one week ago, while the '5' at the end specifically tells the function to return the 'Volume' property rather than the closing price.

Free Microsoft Office alternative

Manage Your Financial Spreadsheets Efficiently with WPS Office

While specific external data types like STOCKHISTORY are exclusive to Microsoft 365, WPS Office provides a powerful, free, and lightweight alternative for all your spreadsheet analysis and financial tracking needs. Enjoy seamless compatibility without the burden of expensive subscriptions.

1. Download WPS Office: Visit the official WPS Office website and download the free installation package for your operating system.
2. Install the application: Run the downloaded installer and follow the on-screen instructions to complete the setup.
3. Open your financial spreadsheets: Launch WPS Spreadsheets and open your existing .xlsx files to continue analyzing your financial data seamlessly.

Free and lightweight spreadsheet applicationFully compatible with standard Microsoft Excel (.xlsx) formatsComprehensive built-in financial and statistical formulasFamiliar user interface that requires zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why does my STOCKHISTORY formula return a #NAME? error?

A #NAME? error typically means that your version of the software does not support the STOCKHISTORY function (such as older, non-subscription versions of Excel), or the function name was misspelled.

How can I get the closing price instead of the stock volume?

To get the closing price, simply modify the last parameter in your formula. Change the '5' (which specifies volume) to '1' to retrieve the close price. For example: =STOCKHISTORY(B2,TODAY()-7,TODAY(),,0,0,1).

Can I use this formula to retrieve data for multiple stock symbols at once?

Yes, if your software supports dynamic arrays, you can replace the single cell reference (like B2) with a range (like B2:B10) containing multiple stock tickers. The function will spill the results for all symbols simultaneously.

Excel & Data Problems