This function is a great tool for anyone wanting to keep an eye on stocks. Whether you’re watching your investments, working on a project, or just curious about stock trends, the Excel STOCKHISTORY function is here to help. It transforms Excel into your own stock tracker, simplifying the task of following the market’s highs and lows.

In this guide, we’ll cover the essentials of STOCKHISTORY: its purpose, its appeal, and how you can easily start using it. You don’t need to be a finance pro or an Excel guru to follow along—we’ll explain everything in easy-to-understand terms, showing you a straightforward path to tracking stock prices right from your spreadsheet.

Here is a sneak peak. We begin with a list of stock ticker symbols and their respective company names.

Our objective is to display the monthly close stock price from a user-defined date to the present.  We also want to display an in-cell line chart that visualizes the monthly price changes while identifying the monthly high and low over the requested time.

How to Use the STOCKHISTORY Function

The Excel STOCKHISTORY function gets historical data about stocks and loads it in an array in Excel (a list that spreads out).

This means that Excel will dynamically create the appropriately sized array range when you press ENTER.

The syntax for the STOCKHISTORY function is as follows (optional arguments are in square brackets):

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])
  • stock – Enter a ticker symbol in double quotes (g., “MSFT”) or a reference to a cell containing the Stocks data type. You can 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., “XNAS:MSFT”).
  • start_date – The earliest date for which data is retrieved. This can be a reference to a cell containing a date or a date typed in as text (e., within double-quotes).  Note that if the [interval] is not 0 (daily), the first data point may be earlier than the start_date provided – it will be the first date of the period requested.
  • [end_date] – The latest date for which data will be retrieved. The default is start_date.
  • [interval] – Specifies the interval each data value represents as follows: 0 = daily, 1 = weekly, 2 = monthly. The default is 0.
  • [headers] – Specifies whether to display headings as follows: 0 = no headers, 1 = show headers, 2 = show instrument identifier and headers. The default is 1 (e, show headers).  When included, headers are rows of text that are part of the array returned from the function.
  • [property0 – propert5] – The columns that are retrieved for each stock as follows: 0 = Date, 1 = Close, 2 = Open, 3 = High, 4 = Low, and 5 = Volume. If any of them is present, only the indicated columns are returned in the order provided. The default is 0,1 (e., Date and Close).

STOCKHISTORY Basic Example

Imagine you want to know the price of Microsoft stock on December 1, 2020. You just need Microsoft’s stock symbol (MSFT) and enter it into the STOCKHISTORY function like this:

=STOCKHISTORY(“MSFT”, “12/1/2020”)

Here’s what you’d see:

You’ll get the date you asked for and the closing price of the stock.

Featured Course

Fundamentals of Financial Analysis

Whether you’re a newbie or have an MBA in Finance, you’ll FINALLY “get” the big picture. This comprehensive course will equip you with these critical skills – even if you’ve never taken a finance or accounting class.
Learn More
Financial Analysis Course Cover

Retrieving Additional Stock Information

Let’s build on the previous example and return the stock history with the following criterion:

  • Daily (0) stock prices for Microsoft (MSFT)
  • Last quarter of 2020 (ex: 10/1/2020 – 12/31/2020)
  • Generate headers (1) for retrieved data
  • Show values for the Date (0), the Close (1), the High (3), and the Volume (5)
=STOCKHISTORY(“MSFT”, “10/1/2020”, “12/31/2020”, 0, 1, 0, 1, 3, 5)

The results are below.

The order of the columns can be arranged in any way desired by altering the order of the listed “property” arguments.  For example, if we wanted to see Volume, Open, Low, High, and Close for the end day of 2020, we would craft the formula like so.

=STOCKHISTORY(“MSFT”, “12/31/2020”, 0, 1, 5, 2, 4, 3, 1)

Using Regional Dates

One of the potential drawbacks of hardcoding dates into the STOCKHISTORY function is that workbooks opened on devices with different regional settings may misunderstand the defined date.

Therefore, you may wish to consider the use of alternate strategies for populating the start_date and end_date arguments:

  • Consider placing the dates in cells and referring to the cells instead of hard-coding the dates in the function.
  • Consider using the TODAY function to dynamically determine the end_date or start_date if you only want to know today’s stock information.
  • Consider using the DATE function to define the Year, Month, and Day values either statically or as cell references. The advantage to the DATE function is that it is unaffected by differing regional settings.
=STOCKHISTORY(“MSFT”, DATE(2020, 10, 1), TODAY(), 0, 1, 0, 1, 3, 5)

Using cell references for the DATE() function might look something like this:

=STOCKHISTORY(“MSFT”, DATE(A1, B1, C1), TODAY(), 0, 1, 0, 1, 3, 5)

Defining a Date Range

For our example, we want to retrieve the last two years of stock information from the current date.

The end_date argument is easy; we can use the TODAY function.

The start_date is a bit trickier but still easy.  We will use the EDATE function to take the current day (i.e. TODAY ) and deduct 24 months.

=EDATE(TODAY(), -24)

If we have our start_date argument in cell A1, we can write a formula like the following to retrieve monthly Date and Close information.

=STOCKHISTORY(“MSFT”, A1, TODAY(), 2, 1, 0, 1)

Defining a Specific Exchange

If you wish to retrieve stock information from a specific Exchange, you can add the Exchange symbol and a colon before the company stock ticker symbol.

If we wanted to pull data from the Wiener Boerse Exchange in Austria, the formula would appear as follows.

=STOCKHISTORY(“XWBO:MSFT”, A1, TODAY(), 2, 1, 0, 1)

Building your own Stock Report

We want to create a monthly stock close report for the previously listed companies for the previous two years.  We have the start date in cell A1 and stock ticker symbols in cells A3 through A7.

If we write the following formula in cell D3

=STOCKHISTORY(A3, $A$1, TODAY(), 2, 0, 1)

… we see the following results.

We aren’t including headers or dates, just the closing price for each month.

The report will be easier to read if the results are listed across the row instead of down the column.  This will also allow us to repeat the STOCKHISTORY formula down to the adjacent rows for the remaining companies.

We can accomplish this by nesting the STOCKHISTORY formula within a TRANSPOSE function.

=TRANSPOSE(STOCKHISTORY(A3, $A$1, TODAY(), 2, 0, 1) )

Filling the formula down to the adjacent four rows produces the following results.

Creating the Header of Dates

We don’t want to create a header filled with hard-coded dates, so we will generate the dates based on the start_date and end_date values used by the STOCKHISTORY function.

To test our formula, select a cell below our existing results (ex: D11) and enter the following formula:

=STOCKHISTORY(A3, $A$1, TODAY(), 2, 0, 0)

The formula fails.

Even though we defined the STOCKHISTORY arguments as 2 (monthly), 0 (no headers), and 0 (dates), the function failed as it requires the presence of one of the other arguments (high, low, volume, etc.)

If we update the formula to include the date and close information, we will have the formula below.

=STOCKHISTORY(A3, $A$1, TODAY(), 2, 0, 0, 1)

Which could be abbreviated to…

=STOCKHISTORY(A3, $A$1, TODAY(), 2, 0)

… as date and close are the default arguments.

We need the results to spill horizontally instead of vertically, so we will nest the previous formula in a TRANSPOSE function.

=TRANSPOSE(STOCKHISTORY(A3, $A$1, TODAY(), 2, 0) )

As we only want the first row of spilled results (the date row), we can “restrict” the results by nesting the previous formula in an INDEX function.

=INDEX(TRANSPOSE(STOCKHISTORY(A3, $A$1, TODAY(), 2, 0) ), 1)

The use of the INDEX function that uses a 1 for its column reference effectively filters the TRANSPOSE / STOCKHISTORY formula to only retain the first row of results.

We’ll place this formula in cell D2 to produce our needed date header.

Alternate Approach to Date Headings

If you haven’t already thought of this, you could use the STOCKHISTORY function to create the date and desired stock data for the first company.  The date information would act as the header for all companies in the report.

Next, use a separate STOCKHISTORY function to generate just the stock data (no dates) for the remaining companies.

It’s two separate functions to maintain, but then again, it was two separate functions earlier. At least in this version, the logic is almost identical.

Visualizing Stock Data with Sparklines

Our final touch will be to create Sparkline charts before the values (Column C) to visually represent the “ups and downs” of the monthly data.

Start by increasing the height of the company rows and the width of Column C to allow for greater detail of the Sparkline charts.

Next, select a cell to insert a Sparkline (for us, that will be cell C3) and click Insert (tab) -> Sparklines (group) -> Line.

In the Create Sparklines dialog box, in the Data Range field, enter the cell range that holds the values for the first company (cells D3 through AB3).

The results are as follows:

Use the Fill Series handle to repeat the Sparkline to the other companies.

The Sparkline ribbon provides many useful options for customizing the look of the Sparklines.

Some options you may wish to experiment with include:

  • Line color
  • Line thickness
  • High Point color
  • Low Point color
  • Marker color
  • Axis controls

Using the Stocks Data Type

In our examples, we have typed the ticker symbols for the companies of interest.  If you select the cells holding the ticker symbols and convert them to the Stocks data type (Data (tab) -> Data Types (group) -> Stocks), you can take advantage of the features provided when using this data type.

I’ve written a full article on how to get real-time stock metrics with the Stocks data type in Excel. Check it out HERE.

Download the Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.