Quotations Stock Market History in Excel

Quotations Stock Market History in Excel
Last Updated on 11/06/2024
Reading time: 2 minutes

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], …)

  1. Stock: The stock ticker symbol, explanations in the next paragraph.
  2. Start_date: The starting date for the historical data
  3. End_date (optional): The ending date for the historical data.
  4. Interval (optional): The frequency of the data. Possible values are:
    • 0: Daily. If omitted, this value is used by default.
    • 1: Weekly
    • 2: Monthly
  5. 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
  6. 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

Example of the stockhistory function with the ticker MSFT

Now, if you only want the Date and the closing quote, you will write this formula.

Stockhistory with the date and the closing

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.
sotckhistory returns only one value

Adding properties

  • Adding properties is intuitive.
  • You can see the meaning of each number in the tooltip.
  • You can add one property or many.
select properties for stockhistory

Tutorial Video

If you want to learn more about the STOCKHISTORY function of Excel, watch the following video.

YouTube video

Leave a Reply

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

Quotations Stock Market History in Excel

Reading time: 2 minutes
Last Updated on 11/06/2024

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], …)

  1. Stock: The stock ticker symbol, explanations in the next paragraph.
  2. Start_date: The starting date for the historical data
  3. End_date (optional): The ending date for the historical data.
  4. Interval (optional): The frequency of the data. Possible values are:
    • 0: Daily. If omitted, this value is used by default.
    • 1: Weekly
    • 2: Monthly
  5. 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
  6. 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

Example of the stockhistory function with the ticker MSFT

Now, if you only want the Date and the closing quote, you will write this formula.

Stockhistory with the date and the closing

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.
sotckhistory returns only one value

Adding properties

  • Adding properties is intuitive.
  • You can see the meaning of each number in the tooltip.
  • You can add one property or many.
select properties for stockhistory

Tutorial Video

If you want to learn more about the STOCKHISTORY function of Excel, watch the following video.

YouTube video

Leave a Reply

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