To get the current market price of a stock, you can use the "Stocks" Data Type and a simple formula. In the example shown, Data Types are in column B, and the formula in cell D5, copied down, is:
The result in column C is the current price for each of the stock Data Types in column B. These prices will change when the Data Type is refreshed.
Note: you can use the STOCKHISTORY function to get the last available close price and other historical pricing at daily, weekly, and monthly intervals. The STOCKHISTORY function is a "pure" formula approach, the approach described here requires the Stocks Data Type.
In this example, the goal is to retrieve the current stock price for the companies listed in Column B. Note these cells in the range B5:B16 have already been converted to the "Stocks" Data Type. Once the Stocks Data Type is available on the worksheet, you can retrieve various information using the simple formulas described below. These formulas follow a "dot" syntax like this:
With a valid Data Type in A1, Excel will automatically display available fields once the "." is typed.
Note: the Stocks Data Type is only available in Excel 365.
The formula for "Last close" in column C is:
Notice the field name "Previous close" is enclosed in square brackets. This is a requirement for any field name that contains a space character.
The formula for "Last close" in column D is:
The formula for "Change %" in column E is:
This is equivalent to the manual formula:
=(D5-C5)/C5 // manual change %
The FIELDVALUE function can also be used as an alternative to the "dot" syntax formulas above. The equivalent formulas are:
In this example, the goal is to retrieve the close price on July 1, 2021 for each symbol shown in column B. This can be done with the STOCKHISTORY function , whose main purpose is to retrieve historical information for a financial instrument over...
In this example, the goal is to retrieve historical stock price information for a given stock, provided as a ticker symbol like "MSFT", "AAPL", "MMM", etc. over the past n days, where n is a variable that can be changed as desired. In addition, the...
In this example, the goal is to get monthly closing stock price over the past n months (i.e. last 6 months, last 12 months, last 24 months, etc.) for the list of symbols that appear in column B. In addition, we want a rolling time period, that stays...
In this example, the goal is to retrieve the last available close price for each symbol shown in column B. This can be done with the STOCKHISTORY function . The main purpose of STOCKHISTORY is to retrieve historical stock price information, and we...
The Excel STOCKHISTORY function retrieves historical stock price information based on a given symbol and date range. The primary purpose of STOCKHISTORY is to get the history of a financial instrument over time. The result is an array of values...
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.