STOCKHISTORY is a Lookup & Reference function that can retrieve historical data about given financial instrument and spill the results into the workbook. You can populate a stock quote’s daily open, close, volume, high, and low values for the specified periods. In this guide, we’re going to show you how to use the STOCKHISTORY function and also go over some tips and error handling methods.
Supported versions
- Requires Microsoft 365 Personal, Microsoft 365 Family, Microsoft 365 Business Standard, or Microsoft 365 Business Premium subscription.
STOCKHISTORY Function Syntax
Arguments
| stock |
The financial instrument to populate historical data. You can use the following formats:
|
| start_date | The earliest date for which data is retrieved. If [interval] is not 0 (daily), the first date may be earlier than the specified date. |
| [end_date] | Optional. The latest date for which data will be retrieved. If omitted, the data at start_date will be returned. |
| [interval] |
Optional. Specifies the interval between retrieved data points.
|
| [headers] |
Optional. Specifies header options:
|
| [property0] - [property5] |
Identifies the type of data you want to retrieve.
|
STOCKHISTORY Function Examples
Close date at a specific date
The basic use case of the STOCKHISTORY function is to get the close date for a specified date by supplying the mandatory arguments: stock and start_date. You can enter date values enclosed in double quotes (e.g. "4/29/2021") or a formula (e.g. TODAY()) or a cell reference to a cell with a date.
Close values between specific period
Specify a date for the [end_date] argument to retrieve data points between the start_date and the [end_date] arguments. The [end_date] should be a valid date value just like the start_date.
The following example returns data points on a daily basis, up to current date, starting from 5 days ago.
The default interval for financial instruments is one day for the STOCKHISTORY function. You can set an [interval] value to increase the interval to weekly or monthly. Use 1 for weekly and 2 for monthly.
The following example shows the formula to list data for the past 4 weeks (28 days) in weekly periods. Note that the last argument is set to 1 for weekly results.
=STOCKHISTORY(Stock,TODAY()-28,TODAY(),1)
Use 2 to get monthly data. The formula below returns financial instrument data for the last 12 months:
Retrieve Close (1), Open (2), High (3), Low (4) and /or Volume (5) values
By default, the STOCKHISTORY function returns close values of the financial instruments for the corresponding dates. As you can see in the previous examples, the function displays the headers of the relevant columns as well.
You can choose either to hide the headers or display them with the symbol of the instrument. Type in either 0 or 2 to hide or show headers with the selected instruments, respectively. Omit the argument or enter 1 to continue to show headers.
The last optional arguments of the STOCKHISTORY function are named from [property0] to [property5]. You can assign numbers from 0 to 5 to these arguments to determine which columns will be listed and their order.
The following sample adds Volume data next to the default columns: Date and Close. Please note that [property0], [property1] and [property2] are populated with 0 (Date), 1 (Close) and 5 (Volume).
Finally, you can list columns in a custom order as well. See that Date (0) column is missing and Close (1) is the 4th column.
STOCKHISTORY Function Tips
- You must be connected to the internet to retrieve the historical data.
- Prefer using data types for up-to-date information instead of historical data.
- Cryptocurrency support is currently limited to 5 currencies: BTC, ETH, BCH, LTC and XRP
- The STOCKHISTORY function does not override any formatting.
Issues
- The data may be incorrect or missing. For example, ETH:EUR parity on 11/16/2018 will not yield a result.
- You can see #BUSY! Error while the data is being downloaded from internet.
