STOCK
The STOCK function returns data from the previous market day’s close about a given stock, retrieved remotely via the Internet.
STOCK(symbol, price)
symbol: An abbreviation that uniquely identifies publicly traded shares of a stock on a particular stock market. symbol is a string enclosed in quotes. symbol can also be a reference to a cell containing a stock symbol.
price: An optional modal value specifying the stock attribute to be returned.
“price” (0 or omitted): The share price of the specified stock at the market close of the previous market day.
“name” (1): The full name of the stock or company.
“change” (2): The difference between the last trade on the previous market day and the closing price on the market day prior to that. If the stock hasn’t traded in that interval, the change reported is “0.”
“percent change” (3): The percentage change in the stock price between the last trade on the previous market day and the closing price on the market day prior to that. If the stock hasn’t traded in that interval, the percent change reported is “0.”
“open” (4): The starting price at which the stock traded at the opening of trading on the previous market day.
“high” (5): The highest price at which the stock traded during the previous market day.
“low” (6): The lowest price at which the stock traded during the previous market day.
“market cap” (7): The total market value of all the outstanding shares of the stock on the previous market day. This is calculated as the total number of outstanding shares multiplied by the price per share.
“volume” (8): The number of shares of the stock that changed hands during the previous market day.
“yield” (9): The ratio of the stock’s annual dividend (cash payout) per share as a percentage of the share price.
“1-year target” (10): The one-year target price estimate, which is the median target price as forecast by analysts covering the stock.
“52-week high” (11): The stock’s highest trade price in the last 52 weeks.
“52-week low” (12): The stock’s lowest trade price in the last 52 weeks.
“ask price” (13): The price sellers are willing to accept for the stock (the offer price). Note: Ask price data is currently unavailable.
“ask size” (14): The number of shares of the stock that sellers are offering at the ask price. Note: Ask size data is currently unavailable.
“avg. 3-month volume” (15): The monthly average of the cumulative trading volume during the last 3 months divided by 22 days.
“beta” (16): The measure of the volatility (systematic risk) of a security or commodity in comparison to the market as a whole.
“bid price” (17): The highest price that buyers are willing to pay for the stock. Note: Bid price data is currently unavailable.
“bid size” (18): The number of shares bid at bid price. Note: Bid size data is currently unavailable.
“currency” (19): The currency in which the stock is priced.
“annual dividend” (20): The yearly dividend (cash payout) amount per share.
“eps” (21): Earnings per share—calculated as a company’s total earnings divided by the number of outstanding shares (the stock currently held by all its shareholders).
“exchange” (22): The stock exchange on which the stock is traded (for example, NYSE, NASDAQ, Euronext, and so on).
“p/e ratio” (23): The price/earnings ratio, calculated by dividing the stock’s market price by trailing 12-month Earnings Per Share.
“previous close” (24): The stock’s closing price for the trading day prior to the last trade reported.
“symbol” (25): The stock symbol (ticker symbol) that uniquely identifies the stock.
Notes
If you enter a formula that includes the STOCK function when the Internet is unavailable, STOCK returns no value. However, when the Internet becomes available, the formula updates with a value returned by STOCK.
The currency shown in this function result depends on your Language & Region settings (in System Preferences in macOS and in Settings in iOS and iPadOS), or on your Time Zone & Region settings in iCloud Settings.
To retrieve data for certain stocks, you must add the suffix for the stock exchange where the stock is traded. For example, add .L to “BLT” for BHP Billiton Plc on the London exchange (“BLT.L”), or add .F to “UBL” for Unibail on the Paris exchange (“UBL.F”).
Examples |
|---|
=STOCK(“AAPL”, “price”) returns the share price of Apple Inc. stock at the market close of the previous market day. =STOCK(“CH”, 1) returns “Aberdeen Chile Fund, Inc. (The),” the name (“name” or 1) of the stock whose symbol is CH. =IFERROR(STOCK(A9, “price”), “-”) returns the previous market day’s closing price of the stock whose symbol is in cell A9 (if the stock symbol and the formula are valid), or “-” if there’s an error in the formula or the data can’t be retrieved. =STOCK(“CARB”, “high”) returns the highest price of Carbonite, Inc. stock during the previous market day. =STOCK(“NKE”, 22) returns “NYSE,” the stock exchange (“exchange” or 22) on which Nike, Inc. Common Stock is traded. |
The STOCK function is powered by 