Use the STOCKHISTORY function to easily pull stock price data and history.
The STOCKHISTORY function gets price data and displays it as a dynamic array.
As a dynamic array, it will spill on to adjacent cells as required. You will therefore need enough empty cells to the right of and below your formula in order to accommodate the data returned.
You will also need a recent software version to take advantage of the STOCKHISTORY function.
Parameters
You can specify the following:
- Stock (via its ticker)
- Exchange (via its ISO 10383 “MIC” code)
- Start and end date
- Interval (daily, weekly or monthly)
- Columns (open, high, low, close, volume)
Use cases
This function looks to be quite useful in many scenarios, although it is not intended for use by professional investment houses and advisors.
Workflows that may benefit from this function could include:
- Weekly or quarter-end investment values
- Updating portfolio valuations and tracking
- Volatility calculations (such as for use in option pricing models)
- Automated verification of period-end pricing for a wide range of publicly quoted equity instruments
It does not look like it is intended for use as a real-time market data source.
Data source and timeliness
Data is sourced from Refinitiv.
Recency of data varies by exchange, ranging from “last trade” to the usual delayed data timeliness, and in some cases end of previous trading day.
We have not tested latency or compared round-trip throughput to other sources, nor have we investigated the exact data path or any limitations that would trigger throttling or rate limiting.
Learn more
To learn more about the STOCKHISTORY function, including its syntax and parameters, see the support article at https://support.microsoft.com/en-us/office/stockhistory-function-1ac8b5b3-5f62-4d94-8ab8-7504ec7239a8.
