Determine YTD Stock Growth With Excel’s STOCKHISTORY Function
Summarizing Stock Data
Microsoft Excel now has some great built-in functionalities to track stocks whether it be current or historical pricing. With all this data at your fingertips, you might be lost on how to organize or summarize it. In this article, I’m going to show you how you can use the STOCKHISTORY function to pull growth metrics of a particular stock based on the desired interval. Below is a picture of what we’ll be creating.
Setting Up The Stock Table
The first thing we are going to do is setup some stock data types. This will effectively tell Excel we want the cell values to reference certain stocks from their database. To do this follow these steps
- Type out the ticker symbols of the stocks you want to summarize (you can add more later)
- Select the cell range with the ticker symbols
- Navigate to the Data tab in the Excel Ribbon
- Within the Data Types button group, click the Stocks button
Once, you’ve completed those steps, your cell values should be converted to a data type and have the stock symbol in front of the stock name.
Once we have the stock data types connected, it’s type to bring in the current pricing. You can do this by referencing each stock cell and adding a dot price to the cell reference (see below). The latest price for each stock should automatically populate after you have entered the formula.
I also added a cell with the current date using the Today() function. We’ll be using this date reference coming up shortly.
Finally, to complete our setup, let’s add in some heading for the interval metrics we want to calculate. In this example, we’ll be calculating:
- YTD (year-to-date) growth
- Growth over the past year
- Growth over the past 5 years
Calculating The Start Price
To make this example easier to consume, I’m going to use some helper cells as we calculate the beginning price for each interval. You can definitely merge the formulas we will be building in this section into the blue table formulas to make the spreadsheet a bit cleaner.
To pull in these stock prices at a particular point in time we are going to utilize the STOCKHISTORY function. If you are unfamiliar with this new function, you can check out my overview article that covers all the inputs to STOCKHISTORY.
To get the STOCKHISTORY function to output the precise date’s pricing we want, we are going to need to provide it with a few inputs:
- Stock Ticker Symbol
- Start Date
- End Date
Now you might be asking why we need both a start AND end date. The reason for this is if the start date happens to occur on a non-trading date (ie holiday/weekend), the function will error out. If we provide it with a date range, the function will simply provide data for all the trading days within that range.
YTD Example
Let’s start with our first desired point in time where we will want to pull the first trading day of the year. To do this, we will have StockHistory pull every trading day in month of January. We can use the following formulas (with Cell B2 being our current date) to ensure we have the proper dates to feed the STOCKHISTORY function:
- Start Date = “1/1/” & Year($B$2) —Outputs —> “1/1/2022”
- End Date = “1/31/” & Year($B$2) —Outputs —> “1/31/2022”
Putting this all together, our full STOCKHISTORY formula will look something like this (assuming the above pictured spreadsheet):
=STOCKHISTORY($B$4,"1/1/" & YEAR($B$2),"1/31/"& YEAR($B$2))As you can see, a whole bunch of data is populated from this formula. But we don’t want all the data, we just want one specific piece of information: the first price of the year. This is where we can utilize another function to grab just one specific cell within this Spill Range.
To point to one specific cell of the Spill Range the STOCKHISTORY function is producing, we can utilize the INDEX function. The INDEX function allows us to reference a specific row and column number of a specific range of cells. Since we know the desired price is always going to be in the 2nd row and the 2nd column, we can hardcode our coordinates within our INDEX function.
The final function looks like this:
=INDEX(STOCKHISTORY($B$4,"1/1/" & YEAR($B$2),"1/31/"& YEAR($B$2)),2,2)1 Year Ago Example
Assuming the current date is in cell B2, we can reference it to create a new date that is exactly one year ago by subtracting one from the current dates year.
=INDEX(STOCKHISTORY($B4,DATE(YEAR($B$2)-1, MONTH($B$2), DAY($B$2)), DATE(YEAR($B$2)-1, MONTH($B$2), DAY($B$2)+30)),2,2)5 Years Ago Example
Assuming the current date is in cell B2, we can reference it to create a new date that is exactly five years ago by subtracting five from the current dates year.
=INDEX(STOCKHISTORY($B4, DATE(YEAR($B$2)-5, MONTH($B$2), DAY($B$2)), DATE(YEAR($B$2)-5, MONTH($B$2), DAY($B$2)+30)),2,2)Adding Percent Change
Now that you have your starting and current prices, you can begin to calculate your growth percentages. To do this, you can take your Current Price / Starting Price – 1.
Adding Up/Down Arrows
Finally, I got a little fancy and chose to add up/down arrows to indicate positive/negative growth.
I used a pretty basic IF formula to give a down arrow symbol if the percentage is less than zero and an up are if it is greater than or equal to zero. I found the two triangle symbols by going to Insert Tab >> Symbols. If you don’t want to go searching, you can just copy the formula below to get the symbols:
=IF(D5<0,”▼”,”▲”)
For the final touch, I defaulted all the symbols to have a red font and created a conditional formatting rule scoped to the entire sheet that stated if there was an up arrow to the left of the cell, to turn that cell green.
Final Result
When you put all these steps together you get something that looks like this:
Download The Excel Example File
If you would like to get a copy of the Excel file I used throughout this article, feel free to directly download the spreadsheet by clicking the download button below.
I Hope This Helped!
Hopefully, I was able to explain how you can use Excel’s new stock pulling functionality to create a summary table of price growth. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
![4 PRO Ways to Create A Filtering Search Box [Excel Download]](https://www.thespreadsheetguru.com/wp-content/uploads/2022/10/ExcelFilteringSearchBox.png)
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.