STOCKHISTORY FUNCTION

How to use the StockHistory Function in Excel 365 to get data of stocks from different exchanges quickly and easily.

Watch the training video below:

StockHistory Function in Excel 365

The STOCKHISTORY function retrieves historical data about a stock or share. When we enter the formula in a cell and press ENTER, Excel creates the appropriate sized array range dynamically.

The Syntax of the function is given below:

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])

Let’s look at each of the arguments of the stockhistory function:

Stock: This argument is required. We enter a ticker or stock symbol in quotes, eg. “GOOGL” We can also use a reference to a worksheet cell which contains the stock symbol as a Stock data type. If we do not specify the stock exchange, the STOCKHISTORY function will get the data from NASDAQ. We can also refer to a specific exchange by entering a 4-character ISO market identifier code (MIC), followed by a colon, followed by the ticker symbol (e.g., “XNSE:CIPLA”). XNSE refers to the National Stock Exchange of India.

Start_Date: Refers to the earliest date for which the data needs to be extracted. The date arguments are dates enclosed in double quotes (e.g. “02-01-2020”) or a formula (e.g. TODAY()) or a cell reference to a worksheet cell containing a date.

End_Date: This argument is optional. It refers to the last data for which we wish to retrieve the data, eg. “03-05-2020”.

Interval: This argument is also optional. It represents the interval at which we wish to get the data. The values it can take are: 0 = daily, 1 = weekly, 2 = monthly. Default value is 0.

Headers: This argument is optional. It specifies if we wish to display headers for our retrieved data like Date, Close, Open, etc, The values it can accept are: 0 = no headers, 1 = show headers, 2 = show instrument identifier and headers. Default is 1 (i.e., show headers).

Property0 – Property5: This argument is optional. The column headers that are retrieved for each stock as follows: 0 = Date, 1 = Close, 2 = Open, 3 = High, 4 = Low, and 5 = Volume. If we choose 0, 2 and 5 then the column headers will be: Date, Open and Volume in that selected order. The default is 0,1 (i.e., Date and Close). The properties are defined as follows:

PropertyDefinition
DateFirst valid trading day in the defined period
CloseClosing price
OpenOpening price
HighHighest price
LowLowest price
VolumeVolume traded
Properties and their Definitions

The STOCKHISTORY function belongs to the Lookup & Reference family of functions.

Stock History Function in Excel
Stock History Function in Excel