Excel function STOCKHISTORY
|

Excel function STOCKHISTORY

In my last post I showed some charts, some pulled from FRED and others from a Microsoft function called STOCKHISTORY. I am going to write about STOCKHISTORY because it is a new function and somewhat interesting.

You can use STOCKHISTORY to pull historical data for a stock or for an index such as Dow Jones Index, S&P 500 or NASDAQ. There may be limits as to how far back we can pull data. The NASDAQ seem to go back only to 2021 which I find weird.

Data Types

First, I want to talk about data types because it was through the data types that I learned about STOCKHISTORY.

Here’s the image of the Excel menu system where I have highlighted Data and within the Data menu system, I highlighted the section holding the Data Types. I show Stocks, Currencies and Geography. There may be more, but I only have those 3 data types.

I will focus on the Stocks data types.

Before you can make use of this section, you will need to have at least one name of a corporation. Let’s just say I use “Apple”. I place my cursor on a cell containing the word “Apple” and then click on “Stocks” icon found in the Data Types section of the menu.

After you click on the “Stocks” icon, off to the right a “Data Selector” will appear. Here is where you can pick which stock exchange you want to use. In the beginning, I kept getting the version showing Brazilian currencies until I realized there was a data selector off to the right.

I’m not going to go into great detail about this stock data type but if you want to learn more how to use this feature in Excel, go to this link: https://www.howtogeek.com/703580/how-to-use-the-built-in-stocks-feature-in-microsoft-excel/

The stock data type has a limitation in that you cannot get historical data. It just provides the latest data for the stock. If you wanted to create a graph of how the stock moves, you would be unable to do so.

Basic STOCKHISTORY use

The basic structure of the function is as follows:

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

  • The “stock” will be the ticker symbol.
  • Interval will be daily, weekly or monthly
  • Headers will be either it shows or it is left off
  • Property will be whatever you want to see: 0=date, 1=close, 2=open, 3=high, 4=low, 5=volume

Here are a couple of websites to get more information

Using STOCKHISTORY

STOCKHISTORY looks to be a dynamic function which means the range can either expand or shrink depending on the start and end date. You don’t have to do anything to expand or shrink the range of data, other than changing the dates.

The image below is that of the Dow Jones Index. The highlighted cell AF24 says “Date” and in the formula bar, it shows that the cell AF24 contains the STOCKHISTORY function. You cannot see it in the image below but when you place your cursor in cell AF24, a bounding box appears around the range to signify a dynamic function similar to FILTER.

I’m just looking for the daily average closing price for the Dow Jones Index, going all the way back to the inception of the Great Recession.

The reason why I showed this image is because I wanted to show how I set up the formula: I referenced other cells to pull in the stock symbol, the start and end date, and the frequency of the data (daily, weekly or monthly).

Note the end date was set in the form of a formula TODAY().

So far, this set up allows me to automatically update the table every time I open the file.

And thus far, my chart seems to automatically update. I do have to check for a couple of more times just to make sure the chart is including the newest dates, but for right now, it seems to be doing so.

Once you set up the function, it seems to run automatically.

The image below shows the last row of the data being pulled by STOCKHISTORY. April 24th was the day I was taking a snapshot of a portion of the spreadsheet.

Closing

This is my current set up to watch the impact of the tariffs on the stock market. I will see if this function maps to what I read in the news.

That brings to mind, I wonder if there is a function for bond history? It doesn’t look like it.

Similar Posts