Joshua earned an MBA from USF and he writes mostly about software and technology.
Excel's STOCKHISTORY Function
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. When I say interval I mean that it allows you to look at the historical stock prices on a daily, weekly, or monthly basis. In the article, I show the easiest way that I found to use this function.
How to Use the STOCKHISTORY Function
This function has four different arguments that determine how stock data will be displayed. Each argument is listed below:
- The first argument 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.
- The second argument is the starting date or date you want the data to start populating.
- The third argument is the ending date, or the date you want the data to stop populating.
- The fourth argument is the type of interval. This is set by adding a 0 for days, 1 for weeks, & 2 for months.
- The last (optional) 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 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 in such a way as 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.
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 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.
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.
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.
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.
Next, select the insert tab and choose a line graph or bar graph from the area that contains the graph icons with drop-down menus.
After a graph has been selected, you may want to drag the corners of the graph to resize it to fit to the spreadsheet.
Microsoft. (n.d.). STOCKHISTORY function. Office Support. https://support.microsoft.com/en-us/office/stockhistory-function-1ac8b5b3-5f62-4d94-8ab8-7504ec7239a8.
Related Excel Articles
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.
© 2022 Joshua Crowder