At the time of this posting (January 2021), there are about 50 supported Exchanges, such as NASDAQ, London, and NYSE stock exchanges. This will likely increase as time goes on.
We begin with a list of stock ticker symbols and their respective company names.
Our objective is to display the monthly close stock price from a user-defined date to the present. We also want to display an in-cell line chart that visualizes the monthly price changes while identifying the monthly high and low over the requested time.
Behold the STOCKHISTORY Function
The STOCKHISTORY function retrieves historical data about a financial instrument and loads it as an array, which will spill if it’s the final result of a formula.
This means that Excel will dynamically create the appropriately sized array range when you press ENTER.
The syntax for the STOCKHISTORY function is as follows (optional arguments are in square brackets):
=STOCKHISTORY(stock, start_date, [end_date], [interval], [headers], [property0], [property1], [property2], [property3], [property4], [property5])- stock – Enter a ticker symbol in double quotes (g., “MSFT”) or a reference to a cell containing the Stocks data type. You can refer to a specific exchange by entering a 4-character ISO market identifier code (MIC), followed by a colon, followed by the ticker symbol (e.g., “XNAS:MSFT”).
- start_date – The earliest date for which data is retrieved. This can be a reference to a cell containing a date or a date typed in as text (e., within double-quotes). Note that if the [interval] is not 0 (daily), the first data point may be earlier than the start_date provided – it will be the first date of the period requested.
- [end_date] – The latest date for which data will be retrieved. The default is start_date.
- [interval] – Specifies the interval each data value represents as follows: 0 = daily, 1 = weekly, 2 = monthly. The default is 0.
- [headers] – Specifies whether to display headings as follows: 0 = no headers, 1 = show headers, 2 = show instrument identifier and headers. The default is 1 (e, show headers). When included, headers are rows of text that are part of the array returned from the function.
- [property0 – propert5] – The columns that are retrieved for each stock as follows: 0 = Date, 1 = Close, 2 = Open, 3 = High, 4 = Low, and 5 = Volume. If any of them is present, only the indicated columns are returned in the order provided. The default is 0,1 (e., Date and Close).
If you were to create a STOCKHISTORY with the least number of arguments (ex: stock symbol and start date)…
=STOCKHISTORY(“MSFT”, “1/1/2021”)…the results would be as follows.
The requested date and Close information are returned.
Featured Course
Fundamentals of Financial Analysis
Retrieving Additional Stock Information
Let’s build on the previous example and return the stock history with the following criterion:
- Daily (0) stock prices for Microsoft (MSFT)
- Last quarter of 2020 (ex: 10/1/2020 – 12/31/2020)
- Generate headers (1) for retrieved data
- Show values for the Date (0), the Close (1), the High (3), and the Volume (5)
=STOCKHISTORY(“MSFT”, “10/1/2020”, “12/31/2020”, 0, 1, 0, 1, 3, 5)The results are below.
The order of the columns can be arranged in any way desired by altering the order of the listed “property” arguments. For example, if we wanted to see Volume, Open, Low, High, and Close for the end day of 2020, we would craft the formula like so.
=STOCKHISTORY(“MSFT”, “12/31/2020”, 0, 1, 5, 2, 4, 3, 1)
Using Regional Dates
One of the potential drawbacks of hardcoding dates into the STOCKHISTORY function is that workbooks opened on devices with different regional settings may misunderstand the defined date.
Therefore, you may wish to consider the use of alternate strategies for populating the start_date and end_date arguments:
- Consider placing the dates in cells and referring to the cells instead of hard-coding the dates in the function.
- Consider using the TODAY() function to dynamically determine the end_date or start_date if you only want to know today’s stock information.
- Consider using the DATE() function to define the Year, Month, and Day values either statically or as cell references. The advantage to the DATE() function is that it is unaffected by differing regional settings.
=STOCKHISTORY(“MSFT”, DATE(2020, 10, 1), TODAY(), 0, 1, 0, 1, 3, 5)Using cell references for the DATE() function might look something like this:
=STOCKHISTORY(“MSFT”, DATE(A1, B1, C1), TODAY(), 0, 1, 0, 1, 3, 5)Defining a Date Range
For our example, we want to retrieve the last two years of stock information from the current date.
The end_date argument is easy; we can use the TODAY() function.
The start_date is a bit trickier but still easy. We will use the EDATE() function to take the current day (i.e. TODAY() ) and deduct 24 months.
=EDATE(TODAY(), -24)
If we have our start_date argument in cell A1, we can write a formula like the following to retrieve monthly Date and Close information.
=STOCKHISTORY(“MSFT”, A1, TODAY(), 2, 1, 0, 1)Defining a Specific Exchange
If you wish to retrieve stock information from a specific Exchange, you can add the Exchange symbol and a colon before the company stock ticker symbol.
If we wanted to pull data from the Wiener Boerse Exchange in Austria, the formula would appear as follows.
=STOCKHISTORY(“XWBO:MSFT”, A1, TODAY(), 2, 1, 0, 1)
Building Our Stock Report
We want to create a monthly stock close report for the previously listed companies for the previous two years. We have the start date in cell A1 and stock ticker symbols in cells A3 through A7.
If we write the following formula in cell D3…
=STOCKHISTORY(A3, $A$1, TODAY(), 2, 0, 1)… we see the following results.
We aren’t including headers or dates, just the closing price for each month.
The report will be easier to read if the results are listed across the row instead of down the column. This will also allow us to repeat the STOCKHISTORY formula down to the adjacent rows for the remaining companies.
We can accomplish this by nesting the STOCKHISTORY formula within a TRANSPOSE function.
=TRANSPOSE(STOCKHISTORY(A3, $A$1, TODAY(), 2, 0, 1) )
Filling the formula down to the adjacent four rows produces the following results.
Creating the Header of Dates
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.
Visualizing the Data with Sparklines
Our final touch will be to create Sparkline charts before the values (Column C) to visually represent the “ups and downs” of the monthly data.
Start by increasing the height of the company rows and the width of Column C to allow for greater detail of the Sparkline charts.
Next, select a cell to insert a Sparkline (for us, that will be cell C3) and click Insert (tab) -> Sparklines (group) -> Line.
In the Create Sparklines dialog box, in the Data Range field, enter the cell range that holds the values for the first company (cells D3 through AB3).
The results are as follows:
Use the Fill Series handle to repeat the Sparkline to the other companies.
The Sparkline ribbon provides many useful options for customizing the look of the Sparklines.
Some options you may wish to experiment with include:
- Line color
- Line thickness
- High Point color
- Low Point color
- Marker color
- Axis controls
Using the Stocks Data Type
In our examples, we have typed the ticker symbols for the companies of interest. If you select the cells holding the ticker symbols and convert them to the Stocks data type (Data (tab) -> Data Types (group) -> Stocks), you can take advantage of the features provided when using this data type.
I have a complete post on the Stocks data type that will demonstrate some amazing features when working with stocks.
Real-Time Stock Metrics with Excel Stock Data Type
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
