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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.