How to Get Adjusted Close Prices in Excel (STOCKHISTORY Limitations)
Question details
The user needs to retrieve historical adjusted close prices for financial analysis, but finds that the built-in function lacks this specific data field.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Performing financial analysis requiring accurate historical stock data that accounts for corporate actions like dividends and splits.
- Observed behavior
- The STOCKHISTORY function successfully returns open, high, low, close, and volume, but natively lacks a property to output adjusted close prices.
Identify a reliable financial data source, such as Yahoo Finance or a market-data API, that allows you to download historical stock data including adjusted close prices in CSV format.
Import Adjusted Close Data from a CSV File
Since the STOCKHISTORY function does not natively support adjusted close prices, you can manually download and import this data from external financial platforms.
Excel's STOCKHISTORY function is designed to pull raw market data, which means it currently does not account for corporate actions like stock splits and dividends that make up the 'adjusted close' price.
To get this data into your workbook, the most reliable method is to export a CSV file from a financial website and import it using Excel's text import tools.
Visit a financial market website that offers historical stock data downloads. Search for your targeted stock ticker, navigate to the historical data section, select your desired date range, and download the data as a CSV file.
Open your Excel workbook where you want to perform your financial analysis. Click on the 'Data' tab located in the top ribbon, then select 'From Text/CSV'.
Locate the downloaded CSV file on your computer and click 'Import'. A preview window will appear. Verify that the 'Adjusted Close' column is present and formatted correctly, then click 'Load' to insert the data into your spreadsheet.
Analyze Historical Stock Data with WPS Spreadsheet
WPS Office provides a lightweight, highly compatible spreadsheet application to easily import, format, and analyze CSV financial data containing adjusted close prices without missing a beat.
- 1. Open WPS Spreadsheet: Launch WPS Office on your device and open a new or existing Spreadsheet document for your financial analysis.
- 2. Import the CSV File: Navigate to the 'Data' tab on the top ribbon, click on 'Import Data', and browse your computer to select the downloaded CSV stock data file.
- 3. Configure Import Settings: Follow the intuitive text import wizard, ensuring your delimiters (usually commas) are set correctly so the adjusted close prices separate into their own distinct column.
- 4. Analyze the Market Data: Click 'Finish' to load the data into your grid. You can now use WPS Spreadsheet's built-in charting and financial functions to complete your market evaluation.
Frequently Asked Questions
What is the difference between close and adjusted close prices?
The standard close price is simply the cash value of the last transacted price before the market closes. The adjusted close price amends this value to reflect corporate actions like dividends, stock splits, and new stock offerings, providing a much more accurate reflection of the stock's true value over time.
Is there any Excel add-in that can pull adjusted close prices directly?
Yes, several third-party financial add-ins and API connectors (like those provided by financial data platforms) can fetch adjusted close prices directly into Excel, bypassing the limitations of the native STOCKHISTORY function.
Can I calculate the adjusted close price manually using formulas?
While it is mathematically possible, calculating it manually requires you to have the standard closing prices along with a comprehensive historical record of all dividends and stock splits for that specific asset, which can be highly complex and tedious to maintain in a standard workbook.
