STOCKHISTORY

Excel Functions

Retrieve stock price information

Syntax

EXCEL

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [properties], ...)

Arguments

Parameter	Description
`stock`	A ticker symbol in double quotes ("MSFT", "AAPL", "GOOG", etc.).
`start_date`	The start date for data to be retrieved.
`end_date`	[optional] The end date for data to be retrieved. Default is start_date.
`interval`	[optional] Time interval. Daily = 0, weekly = 1, monthly = 2. Default is 0.
`headers`	[optional] No header = 0, basic header = 1, instrument + header = 2. Default is 1.
`properties`	[optional] Additional data to retrieve. Default is Date and Close. See below.

Return Value

Array of stock price information

Details

The STOCKHISTORY function retrieves historical stock price information based on a given symbol and date range. The main purpose of STOCKHISTORY is to get the history of a financial instrument over time. Although the name suggests that STOCKHISTORY is meant to work only with stocks, STOCKHISTORY can also work with bonds, index funds, mutual funds, and currency exchange rates. Note that STOCKHISTORY retrieves multiple results. The result is an array of values that spill onto the worksheet into multiple cells. The STOCKHISTORY function accepts five primary arguments, and six additional property arguments to retrieve additional information. Stock and start_date are the only required arguments. Each argument is described in detail below. Additional properties are described in the table here. No

Examples

Example - Get daily close prices for a stock

To retrieve the daily close price for Apple ("AAPL") for the month of January 2021, the formula in cell B4 is:

EXCEL

=STOCKHISTORY("AAPL",DATE(2021,1,1),DATE(2021,1,31))

Example - Monthly close prices

The STOCKHISTORY function can retrieve daily (0), weekly (1), and monthly (2) price data by setting a value for the interval argument. For example, to

EXCEL

=STOCKHISTORY("AAPL",DATE(2023,1,1),DATE(2023,12,31),2)

Example - Make inputs variable

In the example below, the inputs for stock, start_date, and end_date are made variable by exposing them on the worksheet in cells F6, F7, and F8. The

EXCEL

=STOCKHISTORY(F6,F7,F8,2)

Example - Additional properties

The properties returned by STOCKHISTORY can be controlled by customizing and/or reordering numbers starting with the sixth argument (property1). In th

EXCEL

=STOCKHISTORY(I6,I7,I8,2,1,0,5,3,4,1)

Example - Additional properties

Note that the reason STOCKHISTORY uses numeric codes for properties is so they can be easily reordered. In the worksheet below, we are returning the s

EXCEL

=STOCKHISTORY(I6,I7,I8,2,1,0,1,3,4,5)

Example - A horizontal layout for multiple stocks

By default, STOCKHISTORY returns information in a vertical layout. To display results in a horizontal layout, you can use the TRANSPOSE function. In t

EXCEL

=TRANSPOSE(STOCKHISTORY(B5,DATE(2024,1,1),DATE(2024,6,1),2,0,1))