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.
