No products in the basket.
Excel Trading
Excel STOCKHISTORY Function – Ultimate Traders Guide
Last Updated on January 26, 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.
Table of Contents
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.
The Next Step: Backtest Your Strategy
I do all my backtesting in Excel. Over many years I have developed a backtesting system that is easy for anyone familiar with Excel to use.
You can use it to test everything from simple investment ideas to complex quantitative strategies.
Tradinformed Backtest Models help you test your strategies, develop new strategies, and find the best settings.
Tradinformed Members get:
- A new backtest model, including my results and analysis every month.
- Direct access to me through the Members Webinars.
- Exclusive tutorials so that you can quickly understand and use the models.
Other Articles You Might Like
