Stock History Template

Several weeks ago, I discovered that Excel had a new function called STOCKHISTORY. It’s able to pull stock prices and a great way to track stock prices and can help calculate returns. Excel does make it clear that it is not for trading purposes. However, it’s still a great way to stay on top of tracks and see how they’re performing. Below, I’ve created a template that will allow you to track stock prices and arrange them from best-to-worst.

Note that for this template to work, you need to have the STOCKHISTORY function on your computer, otherwise you’ll get nothing but errors. So your first step will be to check if it works on your file. Refer to the original post on the function as it will also explain how you can get it on your computer if you don’t already have it. If you’re running on old versions of Excel, you’re out of luck.

But for those that aren’t and that have access to the function, read on.

Using the template

You can download the template here.

There are three main inputs on this template:

• Selecting the stocks you want to track.
• Setting the date ranges you want to look at.
• Entering the ranges that you want the macro to sort.

Let’s start with the first one, selecting stocks. I’ve already created three stock sections in this template, which you can of course change. Let’s look at one of them as an example:

The Start, End, and Return values are formulas. The only things you need to enter are the ticker symbols. Off to the left, shaded in light grey, I’ve also entered the code for the exchange. For the New York Stock Exchange, it’s XNYS, while the NASDAQ is XNAS. For a full list of the codes, refer to the original post on the STOCKHISTORY function. If it’s a popular stock that’s on one of the major exchanges, you may not need to enter it. I’ve included the exchange code for the sake of avoiding errors as it’s possible Excel might not know which ticker you’re looking for and select the wrong one.

You can extend the ranges to accommodate more tickers, you’ll just need to copy the formulas down in the Start, End, and Return sections.

Next: the date ranges.

Off to the right of the template, there’s a section where you can enter the start and end dates.

The template will adjust for weekends but not for holidays. If you see a #VALUE! error in the values, that likely means there’s an issue with the date, so you’ll just need to change one of the dates to ensure it doesn’t fall on a holiday.

Lastly: the ranges to sort.

To the right of the dates, there’s another area where you can enter which cells to sort:

Cells E8, K8, and Q8 on this template are where my ‘RETURN’ headers are located, and where the percentages are. If you add sections or modify this template, you’ll need to update the cells to sort. When you update the start or end dates, the template won’t automatically re-sort until you click on this button:

If you get an error on the re-sort button, make sure you check which cells are in the Cells to Sort area and ensure that they’re correct.

#CONNECT! errors

One thing you may run into on this template are #CONNECT errors. I’ve noticed this happens once you start adding too many ticker symbols. Sometimes it’s hit or miss and you’ll get all the prices updated, but if you’re planning to list every ticker out there, just be forewarned that you might run into issues here. It’s a separate error from the #VALUE! error and one that can’t be fixed through the template, without removing some ticker symbols, anyway.

If you liked this post on the StockHistory Template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.