With this feature, you can retrieve historical stock prices for stocks by simply entering a few variables into a formula. Moreover, you can retrieve values for a single date or a range of dates. Further, if you choose a range of dates, you can designate daily, weekly, or monthly intervals. STOCKHISTORY displays date and closing price by default. However, you can optionally choose to show opening price, high price, low price, and volume if desired.
Additionally, you can create more sophisticated formulas using STOCKHISTORY if your needs require additional information. Specifically, the full syntax of a formula can include all the following items.
STOCKHISTORY(stock, start_date, [end_date],[interval],[headers], [property0], [property1] [property2], [property3], [property4], [property5])
- stock: The identifier for the financial instrument targeted. This reference 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 the formula returns additional header rows with the array
- property0 – property5 (optional): Specifies which information to include in the result, Date (0), Close (1), Open (2), High (3), Low (4), Volume (5).
Extending the previous example, we can create more powerful formulas that use the new function. For instance, we can use the following formula to use the STOCKHISTORY function to generate a listing of closing prices for a range of dates:
Another Example of STOCKHISTORY
=STOCKHISTORY(“MSFT”, “1/1/2020”, “12/31/2020”,,1,0,1,2,3,4,5)
For example, Figure 2 illustrates an abbreviated set of results using this formula.
Of course, you can use STOCKHISTORY results in the same fashion as if you entered the data manually.