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:

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:

Property	Definition
Date	First valid trading day in the defined period
Close	Closing price
Open	Opening price
High	Highest price
Low	Lowest price
Volume	Volume traded

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