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. 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.
Previous close
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.
Current Price
The formula for “Last close” in column D is:
Change %
The formula for “Change %” in column E is: This is equivalent to the manual formula:
FIELDVALUE function
The FIELDVALUE function can also be used as an alternative to the “dot” syntax formulas above. The equivalent formulas are: Notice square brackets are not required, but the field names are enclosed in double quotes ("").
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
