The STOCKHISTORY function recently showed up in my non-beta version of Excel 365.
Its appearance was a surprise because it wasn’t announced as a What’s New feature in my monthly updates for 365.
At least a few people at Microsoft must have been surprised, as well. This is because, as I write this, the function hasn’t been added to Excel’s alphabetical list of functions. Nor have its new error codes been added to Microsoft’s documentation for Excel’s ERROR.TYPE function.
An Overview of the STOCKHISTORY Function
I tested the STOCKHISTORY function against a list I have of 7398 ticker symbols from around the world—including tickers for bonds and ETFs—and it found 6731 of them.
I checked the 500 stocks with the largest market caps and learned that the price history for the older companies—like Ford and IBM—generally begins in March 1980. But I see that the price history for American International Group (AIG) begins in 1973, and for FedEx (FDX) begins in 1978.
The STOCKHISTORY function has four error codes that you’ll quickly notice:
- #BUSY! tells you that Excel is working on an answer and the results will appear soon—or, perhaps, eventually.
- #CONNECT! tells you that the server is not responding, may be undergoing maintenance, and that you should try again later. (Instead of trying again, I treated #CONNECT! like #BUSY! That is, I took a long lunch and found that both error values had cleared by the time I returned.)
- #N/A tells you that there was no data for the date or dates you requested.
- #VALUE! tells you that the ticker you asked for wasn’t found.
I have one warning, however. I found that using the STOCKHISTORY functions slowed my computer significantly…which is why I decided to take a long lunch. This was true even after I set manual calculation. On the other hand, when I checked several forums about the function I saw no other complaints about this issue. So your mileage may differ.
STOCKHISTORY Syntax
The function’s actual syntax—according to IntelliSense—is…
=STOCKHISTORY( stock, start_date, [end_date], [interval], [headers], [properties1], [properties2], [properties3], [properties4], [properties5], [properties6] )
…which isn’t quite what Microsoft’s documentation for the function tell us. Specifically, the current online documentation tells us that the numbering for the “properties” arguments starts with “properties0”. But IntelliSense starts the numbering at “properties1”. That’s an important difference, as you’ll see shortly.
Here are the options for each argument:
- stock is the ticker symbol—like “MSFT”—and case doesn’t matter. You also can specify an exchange by entering a 4-character ISO Market Identifier Code (MIC), followed by a colon, followed by the ticker symbol, like this: “XNAS:MSFT”
- start_date is the earliest date for the data retrieved. The date can be either a date serial number or text like “3/1/1986”, “March 1969”, “1986-03-01”, and so on.
- end_date (optional) is the latest date for which data is retrieved. Its default value is the start_date. And like start_date, the date can be either a date serial number or text.
- interval (optional) can have these values: 0=daily, 1=weekly, 2=monthly. The default is 0.
- headers (optional) can have these values: 0=no header, 1=show header, 2=show header with ticker and exchange. The default is 1.
- properties1 – properties6 are optional. The arguments can contain any of the following values in any order. (The default is 0, 1.) As you can see, although the names of the properties are numbered 1 though 6—according to IntelliSense—the values they can contain are numbered 0 through 5.
- 0 = date
- 1 = close
- 2 = open
- 3 = high
- 4 = low
- 5 = volume
So, for example, this formula…
A1: =STOCKHISTORY(“MSFT”, “3/1/1986”, DATE(1986,3,19), 0, 2, 0,1,2,3,4,5)
…returns Microsoft’s first week of stock prices:
That’s right. During Microsoft’s first week on the stock market, you could have bought Microsoft shares for about a dime per share.
Notice that although the formula specified prices from March 1, and that the date range included weekends, only prices for the five actual trading days were included in the results.
Also notice that the STOCKHISTORY can accept dates either as text or as a date serial number.
To be clear, Excel generated the figure above after I entered only the one formula shown for cell A1. This is because Excel used 365’s new dynamic array functionality to return its results to as many cells as necessary to display the full array.
STOCKHISTORY Example Formulas
I’ll close with examples of how to return single values from the STOCKHISTORY function. For easy reference, here is its syntax again:
=STOCKHISTORY( stock, start_date, [end_date], [interval], [headers], [properties1], [properties2], [properties3], [properties4], [properties5], [properties6] )
To return the average daily price during a period of time, we can use:
=AVERAGE( STOCKHISTORY(“MSFT”, DATE(1986,3,1), DATE(1986,3,19), 0, 0, 1) )
Here, the first zero after the dates specifies a daily interval; the second zero specifies no headers; and the one specifies the price at the close. And then AVERAGE returns the average of the five daily prices that STOCKHISTORY returns.
To return the first value in a range of dates, when we don’t know which date has the first value, we can use two different formulas:
=INDEX( STOCKHISTORY(“MSFT”, DATE(1986,3,1), DATE(1986,3,19), 0, 0, 0, 1), 1,2 )
=INDEX( STOCKHISTORY(“MSFT”, DATE(1986,3,1), DATE(1986,3,19), 0, 0, 1), 1)
In the first formula, the third zero after the DATE functions specifies a date, and the value of 1 that follows the third zero specifies the closing value. In the INDEX function that surrounds the STOCKHISTORY function, the 1 specifies the first row and the 2 specifies the second column.
In the second formula, the last argument in the STOCKHISTORY function returns only one column of closing values, and the INDEX function returns the value from the first row of that column.
To return the first date in a range of values, when we don’t know which date actually contains that first value, we can try to use:
=INDEX( STOCKHISTORY(“MSFT”, DATE(1986,3,1), DATE(1986,3,19), 0, 0, 0, 1), 1,1)
=INDEX( STOCKHISTORY(“MSFT”, DATE(1986,3,1), DATE(1986,3,19), 0, 0, 0), 1)
In the first formula, the STOCKHISTORY function is identical to the first formula in the previous pair. We merely use INDEX to return the first value from the date column, rather than from the second column, which contains the column of closing values.
The second formula, however, returns the #VALUE! error. This is because STOCKHISTORY refuses to return a single column of date values, for some reason.
To return the closing value for the last date in a range of values, when we don’t know which date actually contains that last value, we can use:
=LET(
Array, STOCKHISTORY(“MSFT”,DATE(1986,3,1),DATE(1986,3,19),0,0,0,1),
NumRows, ROWS(Array),
Result, INDEX(Array,NumRows,2),
Result )
The easiest way to find this value is to use the LET function. In the formula, we first define Array to contain the array that STOCKHISTORY returns. We define NumRows to contain the number of rows in Array. We define Result to contain the last value in the second column of Array. And then we return the Result.
And finally, to return the the last date in a range of values, we use this formula:
=LET(
Array, STOCKHISTORY(“MSFT”,DATE(1986,3,1),DATE(1986,3,19),0,0,0,1),
NumRows, ROWS(Array),
Result, INDEX(Array,NumRows,1),
Result )
With one exception, this formula is identical to the previous one. The exception is that the INDEX function in this formula returns the value from Array’s first column rather than from its second column.
