How to Use the Stockhistory Function in Excel: A Step-by-Step Guide

Do you want to know how to get stock price data for historical stock prices in Excel? It's actually relatively easy, and this step-by-step guide will show you how. You can use the STOCKHISTORY function in Excel to get the stock prices for any date range you need. In addition, we'll also show you how to create a graph of the stock price data so that you can see how it has changed over time. Let's get started!

What is the Stockhistory function in Excel, and what does it do?

The STOCKHISTORY function in Excel is used to get stock price data for a given stock ticker symbol. This function can be used to obtain data for multiple stock tickers at once. It is a handy function for financial analysts and investors who need to track stock prices over time on their Excel spreadsheet.

Microsoft released the function in 2020, and it is widely available at no cost for users with any of the following office account types:

• Microsoft 365 Personal
• Microsoft 365 Family
• Microsoft 365 Business Standard
• Microsoft 365 Business Premium subscription

If you don't have any of these account types, don't worry. Here is an alternative you can check out so you can get all the stock data you need regardless on your Excel spreadsheet.

How to use the Stockhistory function in Excel

Now that you know what the Stockhistory function is and what it does, let's look at how to use it in Excel. Then, we'll walk you through each step to get stock price data for any stock ticker symbol you need.

Function syntax

The syntax of the function is =STOCKHISTORY(stock, start_date, [end_date],[interval],[headers], [property0], [property1] [property2], [property3], [property4], [property5])

Lets break down each component:

Stock: The tikcer or symbol of the company you are looking to get data for

Start-date: The eraliest date to get data from

End-date: The lastest date for which you want to get the data

Interval: Daily(0), weekly(1) or monthly(2) depending on your choice

Headers: Specify if you would like the headers of the properties you want, such as close, date, etc. No header(0), show header (1).

Property0-5:Specify what data you want the function to return, such as date (0), Close (1), Open (2), High (3), Low (4), Volume (5).

It is important to mention that the items in [] brackets like end_date, interval, headers, and property are optional and have the following default values:

End-date: The function will return the price data for the start date only

Interval: Daily

Headers: No header

Property: at least one property must be entered

Example of how to use stockhistory on Excel

With this information, you can now get all the stock price data you need right on your Excel spreadsheet. For example, let's say you want to get Apple's daily close price and date between January 1, 2022, and January 30, 2022.

Using the function you need to enter =STOCKHISTORY("AAPL", "01/01/2022", "01/30/2022",0,1,0,1)

As you can see, you will get exactly all the information you need with one simple function call. An essential fact to mention is that for any of the elements in the function, you can reference a cell with the value instead of manually entering the values you are looking for every time.

Using this knowledge, you can set up your spreadsheet like this and simply change the ticker and the dates to get the stock price data you need.

What data is available on the stockhistory function?

The stockhistory function provides the following data for each stock:

• Open price: The stock price when the market opened
• High price: The highest price that the stock was traded for
• Low price: The highest price that the stock was traded for
• Close price: The stock price when the market closed
• Volume: The number of times the stock was traded

You can access each of these available properties in a single function call or individually by entering 1 for the close, 2 for the open, 3 for the high, 4 for the low, and 5 for the Volume in the properties part of the function.

How use the stockhistory function for international stocks

The process of getting data for international stocks is straightforward. All you have to do is look at the following list of the supported stock exchanges and take a note of the market identifier code, such as AU for the Australian stock exchange.

After doing this, simply enter the market identifier code followed by a colon and then the company ticker, which you can get by Googling the company name followed by the word "stock."

For example, to get Telus' a Canadian company's, close price and date between January 1, 2022, and January 30, 2022, all you have to do is enter the following function:

=STOCKHISTORY("XTSE:T", "01/01/2022", "01/30/2022",0,1,0,1)

Tips for using the Stockhistory function in Excel

Use sparklines to quickly visually stock price data

An underrated use of the stockhistory function is the ability to use sparklines to visualize these data in a small format across multiple companies.

As you can see here, you can combine the stockhistory function with the transpose function, so you get the stock price data for these companies horizontally.

From here, all you need to do is click on insert>sparklines>line

Now simply select the cell where you'd like the sparkling to show and enter the range where the price data is displayed for that particular stock.

Once you do this for one company, you can simply autofill this cell down, and voila, you can now easily visualize the stock prices of all these stocks.

Graph of stock prices over time

One of the best uses of the stockhistory function is to graph stock price data over a period of time.

Going back to our previous example of Apple, you must select the cells from A1 to E20 range, click on insert chart, and select the chart type that best suits your purpose.

This will insert a line graph of the stock prices into your Excel spreadsheet.

Now you can see how the stock prices for each stock are displayed on the graph. In addition, you can use this graph to track how the stock prices have changed over time.

You can also add a title and axis labels to the graph by clicking on the chart and selecting Chart Title and Axis Titles from the Layout tab.

Use the =TODAY() function

The today function on Excel is excellent for stockhistory because it automatically returns today's date in the proper date format required for stockhistory. Moreover, it allows you to make your stock data dynamic.

For example, let's say that you are always looking to analyze the close price for Apple in the last 90 days.

In this case, all you have to do is use the stockhistroy function as follows:

=STOCKHISTORY("AAPL", TODAY(), TODAY()-90,0,1,0,1)

At first, you may not notice a difference in the data, but as days go by, you will see that because the TODAY() function updates every day, you always get the latest 90 days of data.

Troubleshooting tips for the Stockhistory function in Excel

If you are having trouble with the stockhistory function in Excel, there are a few things that you can do to troubleshoot the issue.

1. Ensure you have entered the correct stock ticker for the company you want to get data for.
2. Check to make sure the stock ticker is supported by the stockhistory function. You can do this by checking the list of supported stock exchanges.
3. If you still have trouble, try using the =TODAY() function to get today's date in the proper format. This will ensure that you are getting the most up-to-date data possible.

Lastly, here are the most common error messages you can get and what they usually mean:

• #BUSY! Excel is loading the data, and the results should appear soon.
• #CONNECT! The Excel server is not responding; you should try the function later.
• #N/A There is no data for the date or dates requested.
• #VALUE! The ticker entered, or one of the parameters was not found.
• #BLOCKED! It means you do not have one of the following office accounts (Microsoft 365 Personal, Microsoft 365 Family, Microsoft 365 Business Standard, Microsoft 365 Business Premium subscription) and therefore cannot use the stockhistory function.

Sockhistory Excel alternative

There are two critical downsides of using the stockhistory function on Excel; the lack of historical financials and key metrics and the fact that it's limited to premium Office accounts. If you want to get stock financials and key metrics for 50,000+ companies without the pain and hassle of manually copy-pasting the data, then check out the section below.

As for a stockhistory alternative, the best tool you can use is the Google Finance function which works very similar to the stockhistory function, except it's more straightforward to use and only works on Google Sheets.

We have a complete guide you can access here to learn how to use the google finance function to get historical and real-time stock price data on your Google Sheet spreadsheet.

In the future, you will be able to access this data via Wisesheets and all other essential stock data, including financials and key metrics.

How to get stock financials in Excel

Now that you know how to get historical stock price data in Excel, it's very easy to get historical stock financials, key metrics, dividend data, and real-time stock data on your spreadsheet to help you make better stock investments in less time.

The best way to do so is to use Wisesheets. You can see a complete guide here, but here are some of the best things you can do.

Get 19 years of stock financials and key metrics in one click

Simply enter the stock ticker or company name you'd like to retrieve data for, select annual or quarterly, select standardized financials or SEC as reported financials, and get all of the information in different tabs of your spreadsheet.

Build custom models and cherry-pick your data

Instead of getting all the stock data at once, you can get specific items from the financial statements using the WISE function. This allows you to build custom models that enable you to analyze a stock in any way you'd like, such as building stock dashboards, DCFs, or any other stock analysis model.

The WISE function syntax is =WISE(“ticker”, “parameter/s”, “period/s”, “quarter”)

Get all the stock dividend data for a particular company in one function call

If you are looking for stock's dividend data on Excel or Google Sheets, you are going to love this. With the =WISEPRICE function, you can get all the dividend data you need for the companies you need in a single function call. All you have to do is enter =WISEPRICE("ticker", "dividend")

Conclusion

In this article, we learned how to get stock price data in Excel using the stockhistory function. We also learned about some of its limitations and found a better alternative in Google Sheets. Lastly, we showed you how you can get all the stock data you need without manually copying and pasting it into your spreadsheet using Wisesheets.

If you have any questions feel free to reach out via the comment section below 👇 or email us at info@wisesheets.io

Happy stock analysis!