Excel can help you get historical stock quotes easily with the STOCKHISTORY function. Let’s see how to use it.
Historical Stock Quotes and STOCKHISTORY
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 example, use this formula to get the last 90 days of stock prices. The columns are 0, 2, 3, 4, 1 in order.
To get only the Date and Close price, use 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
- Type dates in quotes, like “01/01/2014”.
- Or, use the TODAY function to calculate dates: =TODAY()-90
=STOCKHISTORY(“MSFT”, TODAY()-90
End Date
- This is optional. Without it, only the start date will be used.
Adding Properties
- Adding properties is simple.
- When you add a comma in the function, it shows a tooltip with all the options for this specific property
- You can add as much argument as you want
Tutorial Video
Watch this video to learn more about using the STOCKHISTORY function in Excel.
