Excel STOCKHISTORY Function – Ultimate Traders Guide

Last Updated on September 22, 2023 by Mark Ursell

Traders and investors are now able to use the Excel STOCKHISTORY function. It is currently available for all Excel 365 subscribers.

I’ve been using it for a while, and I am impressed with it. I can see that this will be a handy tool for many people.

Despite the name, you can get more than just stock prices. There are a lot of different financial instruments available, including ETFs, Currency Pairs and Cryptocurrencies.

In this article, I give you a traders guide to using STOCKHISTORY to get free historical price data in Excel.

Watch the Video

So What Is The Excel STOCKHISTORY Function?

The new function is a way of drawing historical data directly into Excel.

You use to function to create customizable historical data. It generates a dynamic range that expands to include all the data.

Once you have the data, the uses are endless, you could set up a personal stock screener, calculate technical indicators or backtest your trading strategy.

What’s Good About It?

• Once you understand the basic syntax, it is straightforward to use.
• It is fast. I have found the data loads into my spreadsheets very quickly.
• It creates an expandable dynamic range. Providing you leave enough blank space in the spreadsheet, the range will automatically expand and contract to fit the data.

What’s Not Good?

• Not all stocks are available. If you find that the STOCKHISTORY function is not working, then that symbol may not be in the database.
• It is only available to Excel 365 Subscribers. If the STOCKHISTORY function is missing then you may need to upgrade.
• You need an active internet connection. If you see the #CONNECT! error message this could be the problem.

How To Use the STOCKHISTORY Function

Syntax

This is the syntax format:

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

Stock, Start Date and End Date

You need to select the stock (or ETF, currency or crypto) you are interested in.

The start date is required but the end date is optional.

Interval

Use this to select the data interval. 0 = daily, 1 = weekly, 2 = monthly

Headers

Use this to select the data headers. 0 = no headers, 1 = headers, 2 = headers plus stock name.

Properties

You can now select which data you want and in which order.

0 = Data, 1= close, 2 = open, 3 = high, 4 = low, 5 = volume.

Tutorial

How To Use the Excel STOCKHISTORY Function

Total Time: 15 minutes

Step 1: Enter Your Stock Ticker Symbols

Firstly put your list of symbols in a blank sheet.

Step 2: Check The Symbols Are Valid

Next, I like to check the symbols are valid by converting them to Stock Data type. If Excel recognizes the symbol, it will convert it to the full name.

This is a useful step because you can check to see if Excel has made any errors.

Step 3: Enter the Start Date and End Date

Enter a start and end date. I have used reference cells in B2 and C2

Step 4: Select the Period and Headings Type

Next, you need to decide the period and whether you want headings. I have entered these in cells D2 and E2.

Step 5: Enter the STOCKHISTORY Formula

Now you need to enter the formula in cell F3.

The most common format for historical price data is: Date – Open – High – Low – Close – Volume.

If you are following my template, the formula you need is:
=STOCKHISTORY(A3,B2,C2,0,1,0,2,3,4,1,5)

If you have done everything correctly, the historical data will appear.

Download My StockHistory Dashboard

I have set up a more advanced automated dashboard that you can download for free.

This version includes a stock picker, date and period selector, as well as a dynamic chart.

Get the Dashboard Spreadsheet

Combining STOCKHISTORY With Stock Data Type

The Excel STOCKHISTORY function works in combination with the Stock Data type. Using them together will add an extra dimension to your trading sheets.

You can find more about this in my video guide about stock data types: How to Get Live Stock and ETF Data in Excel

This is what Microsoft says about it:

[Excel Stock Data is] considered linked data types because [it has] a connection to an online data source. That connection allows you to bring back rich, interesting information that you can work with and refresh.

More from Microsoft: Excel Data Types

Excel Stock Data Type Screeners

It is really easy to create a personal screener using stock price data in Excel. There is price data for technical traders and fundamental data.

Multifactor Investing

Anyone who loves analyzing data will enjoy setting up a multifactor investment screener using Excel stock data.

This is an example that I set up in my dashboard spreadsheet. If you want to get a copy of this, you can get it for free: Get The Dashboard

Using this template you can sort by 52 Week Relative Strength, P/E or Market Cap. You can also arrange your watchlist by currencies or type.