The `STOCKHISTORY` function in Excel is used to retrieve historical stock price data directly into your spreadsheet. This function is available in Excel 365 and Excel 2019 but might not be available in older versions. Here’s how you can use it:
Basic Syntax
STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [properties])
Parameters
- stock (required): This can be a stock ticker symbol in quotes (e.g., “MSFT” for Microsoft) or a reference to a cell containing the stock symbol.
- start_date (required): The date from which you want to start retrieving historical data. You can input this as a date serial number, a date returned by the `DATE()` function, or a text string in quotes.
- end_date (optional): The date up to which you want data. If omitted, it defaults to the current date.
- interval (optional): This determines the frequency of data:
- `0` for daily
- `1` for weekly
- `2` for monthly
If omitted, the default is daily.
- headers (optional): This controls whether headers are included.
- `0` for no headers
- `1` for show headers only
- `2` for headers and instrument identifiers (default)
- properties (optional): Use this to specify which data columns you want to include. The default set of properties includes Date, Close, and Volume. Some of the properties you can use include:
- `0` for Date
- `1` for Close
- `2` for Volume
- `3` for Open
- `4` for High
- `5` for Low
Example Usage
To retrieve daily closing prices for Microsoft from January 1, 2021, to January 10, 2021, you could use:
=STOCKHISTORY("MSFT", DATE(2021, 1, 1), DATE(2021, 1, 10), 0, 2, 1)
This function will return a table with Date and Close columns (the default behavior), including headers, for the specified range.
Tips
- Ensure your Excel is updated to a version that supports the `STOCKHISTORY` function.
- Dates should be in a recognizable format for Excel to process them correctly.
- The returned data can depend on market and data provider timing; thus, ensure you verify the retrieved data if it’s critical for decision-making.
By knowing these steps, you can efficiently use the `STOCKHISTORY` function to analyze historical stock data without external data sources directly within Excel.
Unlock Your Potential
Excel
Access
Power BI
