'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Does Excel STOCKHISTORY Support Adjusted Closing Prices?
Question details
The user wants to know if the Excel STOCKHISTORY function can output adjusted closing prices instead of standard closing prices for historical market data.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Retrieving historical market data and stock prices in a spreadsheet for accurate financial analysis and tracking.
- Observed behavior
- Users notice unexpected historical price changes (such as anomalies around November 27) and cannot find a documented option within the formula to specify adjusted closing prices.
Ensure your Excel application is connected to the internet and signed into a Microsoft 365 account, as the STOCKHISTORY function requires an active connection to retrieve financial data.
Understand Limitations and Submit Feedback
Currently, Excel does not offer a built-in argument within STOCKHISTORY to toggle between standard and adjusted closing prices.
The STOCKHISTORY function retrieves data directly from Microsoft's financial data provider. By default, it pulls the standard closing price. There is no documented parameter to switch this to an adjusted closing price that accounts for dividends and stock splits.
Check your existing formula (e.g., =STOCKHISTORY("MSFT", "1/1/2023", "1/31/2023")) to confirm it is pulling the standard closing prices based on the default properties.
If you notice sudden, unexplained shifts in historical prices in your spreadsheet, use the Microsoft 365 Admin Center to raise a support ticket to investigate potential data provider corrections.
Go to the 'Help' tab in the Excel ribbon and select 'Feedback'. Choose 'I have a suggestion' to request that Microsoft add an adjusted closing price parameter to the STOCKHISTORY function.
Manually Calculate Adjusted Closing Prices
Since STOCKHISTORY does not natively provide adjusted prices, you can calculate them manually using dividend and stock split data.
Try WPS Office for Your Data Analysis Needs
If you are encountering limitations with connected data types in Microsoft Excel, consider switching to WPS Office. It provides a lightweight, free, and highly compatible alternative for comprehensive spreadsheet management and robust financial data analysis without expensive subscription fees.
- 1. Download WPS Office: Visit the official WPS website and click the free download button for your operating system.
- 2. Install the software: Run the downloaded installer and follow the simple on-screen instructions to complete the setup.
- 3. Open your Excel files: Launch WPS Spreadsheets and seamlessly open your existing .xlsx financial documents without losing any cell formatting or standard formulas.
Frequently Asked Questions
Why did my STOCKHISTORY prices change suddenly?
Sudden changes in STOCKHISTORY data can occur due to stock splits, dividend distributions, or upstream data provider corrections. Because standard closing prices are pulled, structural adjustments by the provider might retroactively alter how historical data is displayed.
What data source does Excel use for STOCKHISTORY?
Excel retrieves its financial and stock market data from Refinitiv, which supplies the historical pricing and company information used by the STOCKHISTORY function.
Can I get real-time stock prices in Excel?
Yes, by using the 'Stocks' data type found in the Data tab. However, note that STOCKHISTORY is designed specifically for historical data arrays, while the Stocks data type pulls current market prices that may be slightly delayed.
Is the STOCKHISTORY function available in Excel 2019 or Excel 2016?
No, the STOCKHISTORY function is exclusive to Microsoft 365 subscribers and Excel for the Web. It is a connected data function that is not available in standalone, perpetual-license versions like Excel 2019 or Excel 2016.
