The STOCKHISTORY in Excel allows for historic stock prices to be generated.
Download File
If you would like to follow along download the attachment below.
What is the STOCKHISTORY function?
The STOCKHISTORY function in Excel fetches historical market data for a specified stock, index, or financial instrument.
This function is available in Microsoft 365 versions of Excel and can show opening, closing, high, low prices and volume over a specific date range.
The syntax of the function is:
=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property1], [property2], …)
Using STOCKHISTORY to find a stock price for a single day
If you are looking for a stock price on one day you will only need to enter a value in the two required parameters:
stock – The stock ticker value
start_date – A date that the market was open (if entering a day that the stock market
was closed the function will return an error)
In order to make these parameters more dynamic and easy to change, as well as make it easier for a user of the report to quickly see what stock and date are being produced it is better to add these values to cells and reference these cells in the STOCKHISTORY function.
The function above returned the value of $410.92, which was the value of Microsoft stock at the market close on February 3rd 2025.
If you enter a date when the market was closed, the function will return an error.
Understanding dynamic array nature of the STOCKHISTORY function
Despite only entering the function in cell A4 the output of STOCKHISTORY spilled across cells A4 to B5.
This is because STOCKHISTORY is a dynamic array function, so the output from one function can spill into multiple cells.
You can see the spill range of the function by clicking on a cell in the range.
If you want to make a change to the STOCKHISTORY function you will only be able to edit it by clicking on the cell that you originally entered the formula.
You will be able to tell which option allows you to make changes by the colour of the text in the function in the formula bar.
Using STOCKHISTORY to find stock price for a range of days
Now that you have seen the dynamic array capability of the STOCKHISTORY function, you can add a date in the optional end_date parameter to see the impact this has.
If you enter a date range that includes days the market was closed these will automatically be omitted from the output.
Using Intervals
Using the STOCKHISTORY function allows you to see stock values in one of three intervals:
Changing Headers
There is also an optional criteria for STOCKHISTORY to change the header output.
Adding properties
The final optional criteria is to add properties. These are:
0 - Date
1 - Close (Default)
2 - Open
3 - High
4 - Low
5 - Volume
These can either be entered individually or all together. When all five are added the output of the function will be:
Conclusion
The STOCKHISTORY function allows you to see a historic stock price on either one day or a range of days.
If selecting a range of days you can change the output to see the values daily, weekly or monthly.
It will default to showing you the closing value of the stock, but you can use optional properties to see the opening value, the daily high, the daily low and the volume.
Komentar