Microsoft has announced a new function for Excel, which is designed to help users visualize the historical data data for a company over time. Called STOCKHISTORY, the new function leverages the Stocks data type, and the dynamic arrays feature that was added recently to grab stock values for different dates and list them all.
For those unfamiliar, dynamic arrays made it possible for a function to be introduced in a single cell but spill out data in multiple cells. This capability launched with functions such as SORT, FILTER, and more, but now STOCKHISTORY is also making use of it to display historical stock data.
The function can be used with a multitude of arguments that let you set what data you want to see. The stock argument defines which company or financial instrument you want the data on, and it can also be used to define which exchange to get the data from; start_date defines the first point in time when data should be collected; end_date defines the last point you want data on, but it's optional; interval defines whether you want daily, weekly, or monthly data, and it's also optional, setting to daily by default; and headings lets users create additional header rows are needed for the results returned by the function. There are also five properties that can be added to select which information to be includes, such as the date, the opening or closing value, high, low, and volume.
The new STOCKHISTORY function is currently available to Microsoft 365 users in the Beta channel, which used to be known as the Office Insider Fast ring on Windows devices prior to a name change announced yesterday. It should make its way to everyone over time.