We don’t want to create a header filled with hard-coded dates, so we will generate the dates based on the start_date and end_date values used by the STOCKHISTORY function.
To test our formula, select a cell below our existing results (ex: D11) and enter the following formula:
=STOCKHISTORY(A3, $A$1, TODAY(), 2, 0, 0)
The formula fails.
Even though we defined the STOCKHISTORY arguments as 2 (monthly), 0 (no headers), and 0 (dates), the function failed as it requires the presence of one of the other arguments (high, low, volume, etc.)
If we update the formula to include the date and close information, we will have the formula below.
=STOCKHISTORY(A3, $A$1, TODAY(), 2, 0, 0, 1)
Which could be abbreviated to…
=STOCKHISTORY(A3, $A$1, TODAY(), 2, 0)
… as date and close are the default arguments.
We need the results to spill horizontally instead of vertically, so we will nest the previous formula in a TRANSPOSE() function.
=TRANSPOSE(STOCKHISTORY(A3, $A$1, TODAY(), 2, 0) )
As we only want the first row of spilled results (the date row), we can “restrict” the results by nesting the previous formula in an INDEX() function.
=INDEX(TRANSPOSE(STOCKHISTORY(A3, $A$1, TODAY(), 2, 0) ), 1)
The use of the INDEX() function that uses a 1 for its column reference effectively filters the TRANSPOSE/STOCKHISTORY formula to only retain the first row of results.
We’ll place this formula in cell D2 to produce our needed date header.
Alternate Approach to Date Headings
If you haven’t already thought of this, you could use the STOCKHISTORY function to create the date and desired stock data for the first company. The date information would act as the header for all companies in the report.
Next, use a separate STOCKHISTORY function to generate just the stock data (no dates) for the remaining companies.
It’s two separate functions to maintain, but then again, it was two separate functions earlier. At least in this version, the logic is almost identical.