Updated date:

Using the STOCKHISTORY Function of Excel to Display Stock Data on a Mac

James likes to learn about technology and share what he learns through his articles.

The graph shows Nike historical closing prices of a weekly period.

The graph shows Nike historical closing prices of a weekly period.

The STOCKHISTORY function is a helpful feature in Excel that allows you to view the stock history of a company with your choice of interval. In the article, I show the easiest way that I found to use this function.

How to Use the STOCKHISTORY Function

This function has 4 different arguments that determine how stock data will be displayed. Each argument is listed below:

  1. The first argument that needs to be entered for this. Function is a ticker symbol. When typing the function manually, you must be sure the symbol has quotation marks around it (e.g., "AZNG"). The ticker can also be added as a cell reference. Adding the ticker will ensure that the proper historical data is pulled for the selected dates.
  2. The second argument is the starting date or date you want the data to start populating.
  3. The third argument is the ending date or the date you want the data to stop populating.
  4. The fourth argument is the type of interval. This is set by adding a 0 for days, 1 for weeks, & 2 for months.
  5. The last argument determines what data you want to appear in the header. This is completely optional, and you may add as many as five different criteria where 0 displays Date, 1 displays Close, 2 displays Open, 3 displays High, 4 displays Low, and 5 displays Volume.

The full syntax of this function is shown in bold below.

=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])

Yes, this can be typed out but I show you how to insert this function in the next section. This method decreases the risk of error in functions that require multiple criteria.

Inserting the STOCKHISTORY Function

The best way that I found to use this function is to have the data laid out to select from to avoid errors. This was completed in the illustration below with headers to describe the data.

Start the process of inserting the function by clicking into the cell where you want the top left-hand corner of your data to appear.

Note that the more criteria that you select, the more columns will populate to the right of this area. The number of rows that populate is dependent on the date range and intervals chosen.

The illustration shows all of the data that is needed to display the stock history of Apple for a specific date range with weekly intervals.

The illustration shows all of the data that is needed to display the stock history of Apple for a specific date range with weekly intervals.

Next, the formulas tab can be chosen, and the drop-down labeled financials can be clicked on. Select STOCKHISTORY from the list of financial functions.

The illustration shows how to navigate through the Excel ribbon to insert the STOCKHISTORY function.

The illustration shows how to navigate through the Excel ribbon to insert the STOCKHISTORY function.

The formula builder should appear on the right side of the screen. Click in each of the blanks one at a time, and select the references related to the arguments that they represent.

After all the blanks are filled, select the done button.

The illustration shows the arguments of the STOCKHISTORY function added to the formula.

The illustration shows the arguments of the STOCKHISTORY function added to the formula.

Avoid the Spill Error

A common error that occurs with these kinds of functions is a spill error. This may occur if you used this function more than once in the same area of cells.

To remedy the error, delete all the rows where the last function displayed data and try again. Another option would be to add the function to another cell.

using-the-stockhistory-function-of-excel-to-display-stock-data-on-a-mac

The data should appear below and to the right of the cell where the formula was added as long as you are connected to the internet.

You should be able to change the criteria in the referenced cells to manipulate the outcome of the function.

The illustration shows the results of the function where the stock history of Apple is displayed.

The illustration shows the results of the function where the stock history of Apple is displayed.

Adding a Graph

One option that can be used to give you a better visual of your data is to insert a graph. Excel has a wide variety of graphs and can add value by showing trends in the data and provide you with a quicker way to analyze a large data set.

To add a graph, start by selecting each column of data.

using-the-stockhistory-function-of-excel-to-display-stock-data-on-a-mac

Next, select the insert tab and chose a line graph or bar graph from the area that contains the graph icons with drop-down menus.

using-the-stockhistory-function-of-excel-to-display-stock-data-on-a-mac

After a graph has been selected, you may want to drag the corners of the graph to resize it to fit to the spreadsheet.

using-the-stockhistory-function-of-excel-to-display-stock-data-on-a-mac

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2021 James Smith

Related Articles