Skip to main content

STOCKDATA
High Usage

Fetches a current stock quote or historical stock candles from Market Data. It can also fetch a single historical candle for multiple stocks.

Sample Usage For Quotes​

=STOCKDATA("AAPL")

Bulk Quotes​

=STOCKDATA("AAPL,MSFT,TSLA")
=STOCKDATA("AAPL,MSFT,TSLA", "all")
=STOCKDATA("A1:A500", "bid,ask,last")

Sample Usage For Candles​

=STOCKDATA("AAPL", "close", "1/1/2021", "1/31/2021", "hourly")
=STOCKDATA("AAPL", "all", TODAY()-30, 30)

Bulk Candles​

=STOCKDATA("AAPL,MSFT,TSLA", "all", "today")
=STOCKDATA("AAPL,MSFT,TSLA", "all", "12/20/2023")
=STOCKDATA(A1:A3, "all", "12/20/2023")

Syntax​

=STOCKDATA(symbols, [historial attributes|quote attributes], start date, end date, resolution)

  • symbols (REQUIRED) One or more ticker symbols for the stocks.

  • [historial attributes | quote attributes] (Optional) Use a historical or quote attribute:

    • "price" (OPTIONAL) Special dual-mode quote/candle attribute.

      • Displays the midpoint price during the regular and sessions.
      • Displays the closing price of the previous session outside of market hours.

    • historical attributes (OPTIONAL – "close" by default) Use one of the following attributes when requesting historical candles:

      • "open" – The opening price of the stock.
      • "high" – The high price of the stock.
      • "low" – The low price of the stock.
      • "close" – The closing price of the stock.
      • "volume" – The number of shares traded.
      • "all" – Returns all values.
      • "symbol" - The ticker symbol of the stock. Only returned when using a bulk candles formula.
      • "adjusted" - Adjust intraday data for splits.
      • "unadjusted" - Do not adjust daily data for splits.

    • quote attributes (OPTIONAL – "mid" by default) Use one of the following attributes when requesting a quote:

      • "mid", "mark" – The midpoint price of the stock.
      • "bid" – The bid price of the stock.
      • "ask" – The ask price of the stock.
      • "last" – The last price of the stock.
      • "bidSize" – The quantity of shares offered at the bid price.
      • "askSize" – The quantity of shares offered at the ask price.
      • "volume" – The quantity of shares traded.
      • "symbol" - The ticker symbol of the stock. Only returned when using a bulk quotes formula.
      • "all" – Returns all values.

  • start date (OPTIONAL) The start date when fetching historical data. If start date is specified, but endDate is not, only the single day’s data is returned. Multiple tickers may be used if only a single day's data is being returned.

  • end date (OPTIONAL) The end date when fetching historical data, or the number of calendar days (not trading days) from startDate for which to return data.

  • resolution (OPTIONAL) The duration of each candle when fetching historical candles. Daily resolution is default if no value is specified. Use one of the following resolutions:

    • Minutely Resolutions: (minutely, 1, 3, 5, 15, 30, 45, ...)
    • Hourly Resolutions: (hourly, H, 1H, 2H, ...)
    • Daily Resolutions: (daily, D, 1D, 2D, ...)
    • Weekly Resolutions: (weekly, W, 1W, 2W, ...)
    • Monthly Resolutions: (monthly, M, 1M, 2M, ...)
    • Yearly Resolutions: (yearly, Y, 1Y, 2Y, ...)

Price vs Close vs Last vs Mid​

When you are looking for a single price for a stock, depending on what data you would like the STOCKDATA formula to return during the extended session (or when the market is closed), it may make sense to use the "close", "last", or "mid" attributes instead of STOCKDATA's default "price" attribute.

Function CallDuring The Regular SessionDuring The Extended SessionMarket Closed, Holidays, etc.
=STOCKDATA("AAPL")Midpoint bid/ask of the regular sessionMidpoint bid/ask of the extended sessionClosing price of the previous regular session
=STOCKDATA("AAPL", "PRICE")Midpoint bid/ask of the regular sessionMidpoint bid/ask of the extended sessionClosing price of the previous regular session
=STOCKDATA("AAPL", "CLOSE")Last trade of the regular sessionClosing price of the previous sessionClosing price of the previous regular session
=STOCKDATA("AAPL", "LAST")Last trade of the regular sessionLast trade of the extended sessionLast trade of the previous extended session
=STOCKDATA("AAPL", "MID")Midpoint bid/ask of the regular sessionMidpoint bid/ask of the extended sessionNo data

Notes​

  • All parameters must be enclosed in quotation marks or be references to cells containing text. A possible exception is when endDate is specified as a number of days.
  • Results with a single data point will be returned as a value within a single cell. Multiple data points will be returned as an expanded array with column headers.
  • If any date parameters are specified, the request is considered historical and only the historical attributes are allowed.
  • Dates and times are returned in the same timezone of the exchange.
  • When writing bulk candles formulas, only a single date is allowed. Date ranges are not permitted. Only daily candles are supported for bulk candles.