Function Problems

How to Calculate Year-to-Date Stock Price Change in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user wants to find the correct Excel formula to calculate the year-to-date (YTD) percentage change of a specific stock price.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking stock portfolio performance from the beginning of the year (January 2, 2025) to the current date.
Observed behavior: The user needs to retrieve both the starting historical price and the latest closing price using the STOCKHISTORY function, and then apply a formula to calculate the percentage difference.

Before you start

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

Solution 1Recommended

Calculate YTD Stock Price Change Using STOCKHISTORY

Retrieve the start-of-year price and the current price using the STOCKHISTORY function, then calculate the difference using a standard percentage change formula.

The STOCKHISTORY function retrieves historical data for a financial instrument. By combining two STOCKHISTORY formulas (one for the start date and one for today), you can seamlessly calculate year-to-date performance.

Enter the stock ticker symbol

Click on cell A1 and type the ticker symbol of the stock you want to track (for example, type 'AAPL' or 'MSFT').

Retrieve the start-of-year price

Select cell B1 and enter the formula =STOCKHISTORY(A1,"2025-01-02","2025-01-02",0,1,0,5) to fetch the closing price on January 2, 2025. Press Enter.

Retrieve the latest available price

Select cell B2 and enter the formula =STOCKHISTORY(A1,TODAY(),TODAY(),0,1,0,5) to fetch the most recent closing price. Press Enter.

Calculate the percentage change

Select cell B3 and enter the calculation formula =(B2-B1)/B1 to find the decimal difference between the two prices. Press Enter.

Format the result as a percentage

Keep cell B3 selected, navigate to the 'Home' tab on the Excel ribbon, and click the '%' (Percentage Style) icon in the Number group to format the decimal as a readable YTD percentage.

Handling Unrecognized Tickers: If Excel does not recognize the ticker symbol and returns a #VALUE! error, verify the official security symbol or include the exchange prefix (e.g., 'XNAS:MSFT').

Free Microsoft Office alternative

Manage Your Financial Data Efficiently with WPS Office

While dynamic array functions like STOCKHISTORY are exclusive to Microsoft 365, WPS Office offers a completely free, highly compatible, and powerful spreadsheet alternative for all your financial data tracking and daily calculations.

Fully compatible with Microsoft Excel (.xlsx, .xls) files and standard formulas.Provides robust built-in financial and statistical functions for portfolio management.Lightweight software design that ensures fast loading and smooth performance.Free to use with a familiar interface, requiring zero learning curve to migrate.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the STOCKHISTORY function returning a #NAME? error?

The #NAME? error usually occurs if you are using an older version of Excel (like Excel 2016 or 2019) that does not support dynamic array functions. STOCKHISTORY is only available to Microsoft 365 subscribers.

How do I resolve the #BUSY! error when fetching stock prices?

The #BUSY! error means Excel is currently downloading the data from the internet. Simply wait a few moments for the connection to finish. If the error persists, check your internet connection or restart Excel.

Can I use cell references instead of typing dates directly into the STOCKHISTORY formula?

Yes. Instead of hardcoding the date like "2025-01-02", you can type the date into a separate cell (e.g., C1) and reference that cell in your formula: =STOCKHISTORY(A1, C1, C1, 0, 1, 0, 5). This makes your financial spreadsheet much easier to update.

Excel & Data Problems