This post is originally published on Excel Blog articles
We have added many needed improvements to support service-side errors and data retrieval, including new errors like #BUSY! that show in Excel while data is being processed. We also added a way for the function to spill formats dynamically instead of stamping a format on a cell.
Easily pulling stock prices into Excel has been one of the most requested features on UserVoice. The Stocks data type began this journey by enabling refreshable quotes for stocks, bonds, funds, and currency pairs. We know that this wasn’t enough to complete the goals you have for analyzing your portfolio in Excel. Getting the history of a financial instrument over time is crucial information you need. We are thrilled to be able to bring this functionality into Excel.
Getting started is simple
Let’s look at an overview of the function signature:
STOCKHISTORY(stock, start_date, [end_date],[interval],[headers], [property0], [property1] [property2], [property3], [property4], [property5])
- stock: The identifier for the financial instrument targeted. This can be a ticker symbol or a Stocks data type.
- start_date: The earliest date for which you want information.
- end_date (optional): The latest date for which you want information.
- interval (optional): Daily (0), Weekly (1), or Monthly (2) interval options for data
- headers (optional): Specifies if additional header rows are returned with the array.
- property0 – property5 (optional): Specifies which information should be included in the result, Date (0), Close (1), Open (2), High (3), Low (4), Volume (5).
You can find more detailed explanations of these arguments on the STOCKHISTORY support page.
Using Basic Text Inputs
=STOCKHISTORY("MSFT", "6/1/2020", "6/5/2020")
You can get started by using the ticker symbol as a textual representation of the stock. The start_date and end_date input will be interpreted by Excel the same way the DATEVALUE function would interpret date_text today. This means that the date inputs respect your system settings for datetime.
Debugging Tip: Make sure you have any text values surrounded with quotation marks in your function inputs.
Choosing a Specific Exchange
If the ticker symbol is used as input without an exchange specified, the values will normally default to the XNAS (NASDAQ) stock exchange. There are two ways to help Excel know which financial instrument you are targeting.
Using prepended text
First, you can use the Market Identifier Code, followed by a colon, followed by the ticker symbol to get data from a specific exchange.
=STOCKHISTORY(“XMIL:MSFT”, TODAY()-7, TODAY())
In this example, I am specifying the MSFT symbol on the Borsa Italiana Exchange ("XMIL:MSFT") and I am requesting the last 7 days of data using the TODAY() function. This function updates when the date changes and so as a part of calculation the dates shown in the grid will update as well. Notice how the dates shown skip non-trading days like weekends, and the prices shown are in euros.
Using a Stocks data type
Second, you can use a cell containing a Stocks data type as a reference in the function, just like any other cell references.
=STOCKHISTORY(B1, B3, B4)
Below I have converted MSFT to a Stocks data type and chosen the data type for Microsoft from the Mexican Stock Exchange. I passed the cell containing the data type to the STOCKHISTORY function (B1). I also passed in references to cells containing dates (B3, and B4). If I edit any of these cells (B1, B3, or B4) the STOCKHISTORY function will recalculate based on the new values in the cells.
Additional benefits to using a Stocks data type includes allowing you to see which currency the prices represent. Try the formula ‘=B1.Currency’ where B1 references a cell containing a Stocks data type.
Debugging Tip: To verify what exchange your stock history values are coming from, input the [header] argument of “2” (Show instrument identifier and header) into the formula.
Let’s see an example using every argument and property in the function.
This function uses every input field to create a wonderful 2D array of data for me to analyze. This data represents Microsoft stock from January to December 2019 in a monthly overview for the Volume, Open, High, Low, and Close for that month. My goal is to chart this data in Excel.
Let me give a refresher on what this signature represents:
“MSFT” – The Microsoft ticker symbol,
“1/1/2019” – The start date,
“12/31/2019” – The end date,
2 – Indicates a choice to show the Monthly overview,
2 – Indicates a choice to include both the ticker symbol and the label row in the resulting array,
0,5,2,3,4,1 – Indicates the properties I want, in the order I want them in the resulting array. In this case, Date, Volume, Open, High, Low, Close.
All together this is the resulting formula in Excel with the data spilled into the grid.
Then I can select this data, go to the Insert tab, click Recommended Charts >All Charts>Stock and insert a Volume-Open-High-Low-Close chart.
I can use this function to produce many charts and graphs, as well as use the data as inputs into other Excel functions. Try out different uses and combinations and let me know what you think!
About our Data Sources
As with Excel’s Stocks data type we are sourcing the historical data from Refinitiv. You can read more about what exchanges are currently supported in Excel. While Stocks data type values can mostly be refreshed intraday on minor delay, STOCKHISTORY values are updated once daily for all supported exchanges at a minor delay after market close.
The STOCKHISTORY function also supports inputs of Currency Pairs, ETFs, Index Funds, Mutual Funds, and Bonds. The easiest way to specify these inputs is to convert them to the Stocks data type and use them as references to the function.
Please note that while some financial instruments may be available as Stocks data types, the historical information will not yet be available. For example, this is the case for most popular Index Funds including the S&P 500.
Numbers with Format Hints
STOCKHISTORY is the first function that provides formatting hints for its number. It does so by returning an enhanced number type that we refer to as a Formatted Number Value (“FNV”). FNVs are just like any other number in Excel but they are accompanied by a helpful formatting hint. Excel uses this format hint when the value lands in a cell with “General” number formatting applied, the default for unformatted cells.
In the example below, the STOCKHISTORY function is being used to retrieve the exchange rate for a currency pair between Jan 1st and Jan 7th, 2020. All the cells have the default “General” number format applied. Notice how the FNV’s (indicated with colored overlays) allow the Close column to seamlessly change from Pound (£) to Dollar ($) to Rand (R). In the example, FNVs are also used to have date serials (e.g. 43831) show using date formatting (e.g. 1/1/2020).
=STOCKHISTORY(C2, "1/1/2020", "1/7/2020")
FNVs can pass through lookup style functions such as XLOOKUP, INDEX, IF, CHOOSE, or simple references (e.g. =A1) but the hint is simply ignored by other function which just see them as their number value.
You can learn more about STOCKHISTORY by reviewing the STOCKHISTORY support article. This article goes in depth on the function definition and clarifies how monthly and weekly intervals are determined.
STOCKHISTORY is now available to 100% of Microsoft 365 Subscribers, on Mac, Windows, and Excel Online.
Please continue to provide feedback either in the comments below or through our in-app feedback. Please let us know what you think!
Kaycee Reineke (@KayceeSue)
Program Manager, Excel