Excel has a fantastic function for collecting historical stock quotations: STOCKHISTORY. Let's see how to use it.
Syntax of the STOCKHISTORY function
STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property1], [property2], …)
- Stock: The stock ticker symbol, explanations in the next paragraph.
- Start_date: The starting date for the historical data
- End_date (optional): The ending date for the historical data.
- Interval (optional): The frequency of the data. Possible values are:
- 0: Daily. If omitted, this value is used by default.
- 1: Weekly
- 2: Monthly
- Headers (optional): A number to specify whether to include headers and which headers to include:
- 0: No headers
- 1: Show headers. If omitted, this value is used by default.
- 2: Show instrument identifier and headers
- Property1, property2, … (optional): Additional properties to include in the returned data. Possible properties are:
- 0: Date
- 1: Close (default if no properties are specified)
- 2: Open
- 3: High
- 4: Low
- 5: Volume
For instance, the following single formula will return the last 90 days of quotations. The 5 columns are identified by the sequence 0, 2, 3, 4, 1
Now, if you only want the Date and the closing quote, you will write this formula.
What is a "Ticker"?
A ticker symbol is a unique code assigned to a publicly traded company for stock exchange identification. It represents the company’s name in shorthand and helps investors and traders track and trade stocks easily. In Europe, another system for the codification of equities is the ISIN code.
You can find them on official websites like nasdaq.com or nyse.com. You can also use Excel's Data Type feature to find the ticker symbol by entering the company name. For this tutorial, we will use the ticker "MSFT".
=STOCKHISTORY("MSFT",
- If you use two currency codes instead of a ticker, you can retrieve the historical exchange rate between the two currencies 😀
Start Date
- You can write any dates between double-quote ( " ); e;g: "01/01/2014"
- But, the easiest way is to subtract days from TODAY function: =TODAY()-90
=STOCKHISTORY("MSFT", TODAY()-90
End Date
- Optional information. If omitted, only the start date value will be returned.
Adding properties
- Adding properties is intuitive.
- You can see the meaning of each number in the tooltip.
- You can add one property or many.
Tutorial Video
If you want to learn more about the STOCKHISTORY function of Excel, watch the following video.
