Function Problems

How to Automate Excel Stock Returns with the STOCKHISTORY Function

WPS Content Manager Oct 9, 2026 868 views

Question details

The user needs to automate daily investment return grids across multiple timeframes (YTD, 1-year, 3-year, 5-year) using Excel's STOCKHISTORY function instead of manually updating data from external sources.

Automate Excel Stock Returns with the STOCKHISTORY Function

Product: Excel
Device & OS: not provided
Scenario: Creating an automated spreadsheet model to track historical investment returns across various intervals without daily manual data entry.
Observed behavior: Currently, return data requires repeated manual updates. The user seeks a structured method to define dates, frequencies, and return calculations correctly using STOCKHISTORY.

Before you start

Ensure you have a stable internet connection, as the STOCKHISTORY function requires online access to retrieve the latest financial market data from its provider.

Solution 1Recommended

Use Dynamic Dates with STOCKHISTORY for Return Calculations

Combine the STOCKHISTORY function with dynamic date functions like TODAY() to automatically retrieve starting and ending adjusted prices for any specific timeframe.

To calculate automated stock returns, you must fetch the starting price and the ending price. By using the TODAY() function, your spreadsheet will automatically roll forward each day. You can adjust the frequency argument to daily for short-term (YTD) tracking, or month-end for longer periods like three or five years.

Determine the Timeframe using TODAY()

In your spreadsheet, identify the start date using basic date math. For a 1-year return, use TODAY()-365. For a 3-year return, use TODAY()-1095. Set the end date simply as TODAY().

Enter the STOCKHISTORY Formula

Select a cell and input the formula: =STOCKHISTORY("Ticker", TODAY()-365, TODAY(), 0, 1, 0, 1). This pulls daily data (interval 0) with headers (1), showing the Date (0) and Close price (1). Change the interval to 2 for monthly frequency if needed.

Calculate the Percentage Return

Once the starting and ending adjusted prices are retrieved into your cells, use the standard percentage return formula: =(Ending Price - Starting Price) / Starting Price. Format the result cell as a Percentage.

Use Dynamic Dates with STOCKHISTORY for Return Calculations

Handling Market Holidays: If your calculated start date falls on a weekend or market holiday, STOCKHISTORY may return a #VALUE! error because no trading occurred. Consider wrapping your date calculation in the WORKDAY function to find the nearest valid trading day.

Free Microsoft Office alternative

Need a Lightweight Tool for Financial Data Analysis? Try WPS Office

If you are building complex investment grids or need a reliable, fast, and free spreadsheet application, WPS Office offers an excellent environment for all your daily data calculation and reporting needs.

1. Download and Install WPS Office: Visit the official WPS website, download the free installer, and complete the quick installation process.
2. Open Your Financial Spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx investment tracking files.
3. Continue Calculating with Ease: Use standard financial and math formulas to calculate returns, manage portfolios, and generate reports.

Fully compatible with Microsoft Excel (.xlsx) formats, ensuring your historical data and financial models open seamlessly.Lightweight and optimized for speed, effortlessly handling large datasets and complex financial formulas.Free to use with a highly familiar user interface, allowing you to migrate your workflow with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the STOCKHISTORY function return a #BUSY! or #CONNECT! error?

These errors generally occur if your internet connection is unstable or if Microsoft's financial data provider is temporarily unavailable. Check your network connection, wait a few moments, and refresh the worksheet.

Can I change the frequency of data retrieved by STOCKHISTORY?

Yes. The fourth argument in the STOCKHISTORY function controls the interval. Use 0 for Daily, 1 for Weekly, and 2 for Monthly historical data.

How do I calculate Year-to-Date (YTD) returns accurately?

To calculate YTD returns, use the DATE function combined with YEAR(TODAY()) to pinpoint January 1st of the current year as your start date (e.g., DATE(YEAR(TODAY()), 1, 1)). Use this date in your STOCKHISTORY formula to pull the correct starting price.

Excel & Data Problems