Function Problems

How to Calculate One-Year Stock Price Percentage Change in Excel

Bushra Parveen Oct 1, 2026 868 views

Question details

The user needs to calculate the one-year percentage price change of a specific stock and wants to know how to retrieve this historical data when the local stock exchange might not be supported.

How to Calculate a One-Year Stock Price Percentage Change in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating annual stock performance and retrieving automated historical financial data for specific companies.
Observed behavior: Needs the correct formula combination to fetch one-year historical prices and calculate the percentage change, while accommodating limitations with regional exchanges like VFEX.

Before you start

Ensure you have an active internet connection to retrieve real-time stock data, and verify whether your specific stock ticker and exchange are supported by the spreadsheet's financial data provider.

Solution 1Recommended

Use the STOCKHISTORY Function for Supported Stocks

For widely supported stock exchanges, utilize Excel's built-in STOCKHISTORY function combined with a basic percentage change formula to automate the calculation.

The STOCKHISTORY function pulls historical stock data directly into your spreadsheet. By combining it with the TODAY() function, you can dynamically fetch prices from exactly one year ago.

Note that regional exchanges, such as VFEX in Zimbabwe (e.g., Padenga Holdings Limited), may not be included in the default Refinitiv or Microsoft data providers.

Enter the STOCKHISTORY formula

Select an empty cell (e.g., A1) and input the formula =STOCKHISTORY("MSFT",TODAY()-365,TODAY(),0). Replace "MSFT" with your target stock ticker.

Retrieve the historical data

Press Enter. The formula will automatically generate an array containing the stock's closing price from 365 days ago and the current price.

Calculate the price variance

In a new cell adjacent to your data, type the formula =(B3-B2)/B2, assuming B3 holds today's price and B2 holds the price from one year ago.

Format as a percentage

Select the cell containing your calculated variance, navigate to the Home tab on the ribbon, and click the '%' (Percent Style) icon to display the result as a percentage.

Use the STOCKHISTORY Function for Supported Stocks

Dynamic Updates: Because the formula relies on the TODAY() function, the date range and percentage change will automatically refresh whenever you open or update the workbook.

Solution 2

Calculate Percentage Change Using Manually Imported Data

If your target stock is listed on an unsupported exchange (like VFEX), you must manually import the historical prices before applying the percentage change formula.

Manage Financial Data with WPS Office

Calculate Financial Metrics Easily in WPS Spreadsheet

WPS Spreadsheet provides powerful data analysis tools and robust formula support, allowing you to easily import historical stock prices, calculate percentage changes, and format your financial reports.

1. Import your stock data: Open WPS Spreadsheet, go to the Data tab, and choose 'Import Data' to bring in your historical stock CSV file.
2. Set up your calculation: Locate the cells containing the current stock price and the price from one year ago (for example, B3 and B2).
3. Enter the calculation formula: Type =(B3-B2)/B2 into an empty cell to calculate the variance between the two periods.
4. Apply percentage formatting: Select the result cell, navigate to the Home tab, and click the Percent Style button to instantly format your decimal result as a percentage.

Fully compatible with Microsoft Excel formulas and CSV financial data imports.Easily format financial figures and percentage data with a single click.Lightweight, fast, and completely free to use for your daily financial tracking.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the STOCKHISTORY function return a #BLOCKED! or #VALUE! error?

These errors typically occur if you are offline, if your version of the software does not support dynamic data types (commonly restricted to Microsoft 365), or if the specific stock ticker and exchange are not supported by the data provider.

How can I find out if my local stock exchange is supported?

You can check for supported exchanges by typing your company name or ticker into a cell, selecting it, and clicking 'Stocks' in the Data tab. If the data provider cannot locate the entity, the exchange (such as VFEX) is likely unsupported and requires manual data import.

Can I automatically calculate percentage changes for multiple stocks at once?

Yes. By organizing your stock symbols in a single column, you can use relative cell references in your STOCKHISTORY and percentage change formulas. Once the first row is set up, drag the fill handle down to apply the calculations to all the stocks in your list simultaneously.