Market Data Google Sheets Add-on

https://www.marketdata.app/sheets/
Admin
Market Data Google Sheets Add-on is a Google Sheets extension that provides real-time stock quotes,...
Tokens:17,795
Snippets:158
Trust Score:8.2
Update:7 months ago
Show doc for...
Tokens:
Raw
### OPTIONDATA Function Syntax

Source: https://www.marketdata.app/docs/sheets/options/optiondata

Defines the syntax for the OPTIONDATA function, outlining its parameters: option symbol, attributes, start date, and end date. Explains the requirements and optional nature of each parameter.

```excel
=OPTIONDATA(option symbol, [attributes], start date, end date)
```

--------------------------------

### OPTIONDATA Function Usage Examples

Source: https://www.marketdata.app/docs/sheets/options/optiondata

Demonstrates various ways to use the OPTIONDATA function to fetch option quotes. This includes fetching a specific attribute, multiple attributes, and historical data within a date range.

```excel
=OPTIONDATA("AAPL271217C00250000")
=OPTIONDATA("AAPL271217C00250000", "bid")
=OPTIONDATA("AAPL271217C00250000", "date,bid,ask", "9/1/2022", "1/31/2023")
=OPTIONDATA("AAPL271217C00250000", "all", TODAY()-30, 30)
```

--------------------------------

### Get First Traded Date

Source: https://www.marketdata.app/docs/sheets/dates-and-times

Retrieves the date when a specific option contract was first traded. This endpoint requires the option contract symbol as part of the request.

```APIDOC
## GET /websites/marketdata_app_sheets/first_traded

### Description
Returns the date when the contract was traded for the first time. This must be used in the context of a specific option contract.

### Method
GET

### Endpoint
/websites/marketdata_app_sheets/first_traded

### Parameters
#### Query Parameters
- **contract_symbol** (string) - Required - The unique symbol of the option contract (e.g., AAPL230120C00150000).

### Request Example
```
GET /websites/marketdata_app_sheets/first_traded?contract_symbol=AAPL230120C00150000
```

### Response
#### Success Response (200)
- **first_traded_date** (string) - The date when the contract was first traded (e.g., "2020-09-14").

#### Response Example
```json
{
  "first_traded_date": "2020-09-14"
}
```
```

--------------------------------

### MARKETSTATUS Formula Examples

Source: https://www.marketdata.app/docs/sheets/markets/marketstatus

Demonstrates various ways to use the MARKETSTATUS formula, including default usage, specifying country and date ranges, and requesting specific attributes like 'status' for 'yesterday'.

```excel
=MARKETSTATUS()
=MARKETSTATUS("US","all","1/1/2022","12/31/2022")
=MARKETSTATUS("US","status","yesterday")
```

--------------------------------

### MARKETSTATUS Formula Syntax

Source: https://www.marketdata.app/docs/sheets/markets/marketstatus

Defines the syntax for the MARKETSTATUS formula, outlining its parameters: country, attributes, start date, and end date. Explains the optional nature of parameters and their accepted values.

```excel
=MARKETSTATUS(country, [attributes], start date, end date)
```

--------------------------------

### Get All Data with STOCKDATA

Source: https://www.marketdata.app/docs/sheets/troubleshooting/price-differences

Retrieves comprehensive data for a given stock ticker using the STOCKDATA function. This is useful for detailed analysis and understanding the source of pricing information. It requires a stock ticker as input.

```google-sheets
=STOCKDATA("TICKER","ALL")
```

--------------------------------

### STOCKDATA Function Syntax

Source: https://www.marketdata.app/docs/sheets/stocks/stockdata

Defines the general syntax for the STOCKDATA function, outlining its parameters: symbols, historical/quote attributes, start date, end date, and resolution. Optional parameters are indicated.

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

--------------------------------

### Market Data Formulas with GOOGLEFINANCE Workaround

Source: https://www.marketdata.app/docs/sheets/automatic-refresh

These example formulas demonstrate how to embed the GOOGLEFINANCE function to trigger automatic refreshes for Market Data formulas. The GOOGLEFINANCE function updates every two minutes, forcing the Market Data formula to refresh as well. This workaround is necessary due to Google Sheets' limitations on direct WebSocket support and automatic third-party refreshes.

```google-sheets
=OPTIONDATA("AAPL260116C00150000","all,"&A1)

```

```google-sheets
=STOCKDATA("AAPL","all,"&A1)

```

--------------------------------

### Get Last Trade Price with STOCKDATA

Source: https://www.marketdata.app/docs/sheets/troubleshooting/price-differences

Forces the STOCKDATA function to display the last trade price instead of the real-time quote (midpoint of bid/ask). This is useful if you prefer to see historical trade data, but be aware it might not reflect the most current market price for order fills.

```google-sheets
=STOCKDATA("TICKER","LAST")
```

--------------------------------

### OPTIONDATA Function Documentation

Source: https://www.marketdata.app/docs/sheets/options/optiondata

This section details the OPTIONDATA function, its syntax, parameters, and usage for fetching option quotes.

```APIDOC
## OPTIONDATA Function

Fetches a current or historical option quote from Market Data.

### Sample Usage
```
=OPTIONDATA("AAPL271217C00250000")
=OPTIONDATA("AAPL271217C00250000", "bid")
=OPTIONDATA("AAPL271217C00250000", "date,bid,ask", "9/1/2022", "1/31/2023")
=OPTIONDATA("AAPL271217C00250000", "all", TODAY()-30, 30)
```

### Syntax
```
=OPTIONDATA(option symbol, [attributes], start date, end date)
```

### Parameters
#### Path Parameters
* None

#### Query Parameters
* None

#### Request Body
* None

### Parameters Details
* **option symbol** _(REQUIRED)_: Use the current OCC option symbol format for the option symbol, even for historic options prior to the symbol format change in 2010.
* **attributes** _(OPTIONAL "price" by default)_: Use at least one of the following attributes; separate multiple attributes with commas:
    * "symbol" - The OCC option symbol.
    * "date", "updated" - The date and time of the quote.
    * "price", "mid", "mark" – The midpoint price of the option.
    * "bid" – The bid price of the option.
    * "ask" – The ask price of the option.
    * "last" – The last price of the option. Depending on the liquidity of the symbol, the last traded price could have occurred one second ago or one day ago.
    * "bid size" – The quantity of contracts offered at the bid price.
    * "ask size" – The quantity of contracts offered at the ask price.
    * "volume" – The number of contracts negotiated during the day.
    * "open interest" – The number of open contracts.
    * "underlying price" – The price of the underlying security at the time of this option quote.
    * "in the money" – Will return TRUE if the option contract was in the money at the time of the quote or FALSE if the contract was not in the money at the time of the quote.
    * "intrinsic value" - The intrinsic value of the option, if any.
    * "extrinsic value" - The extrinsic or time value of the option.
    * "iv", "implied volatility" - The implied volatility of the option.
    * "delta" - The delta of the option.
    * "gamma" - The gamma of the option.
    * "theta" - The theta of the option.
    * "vega" - The vega of the option.
    * "all" – Returns all values.
    * "noheaders" – Returns values without column headers.
* **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.
* **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.

### Request Example
```json
{
  "option_symbol": "AAPL271217C00250000",
  "attributes": "date,bid,ask",
  "start_date": "2022-09-01",
  "end_date": "2023-01-31"
}
```

### Response
#### Success Response (200)
* **symbol** (string) - The OCC option symbol.
* **date** (string) - The date and time of the quote.
* **price** (number) - The midpoint price of the option.
* **bid** (number) - The bid price of the option.
* **ask** (number) - The ask price of the option.
* **last** (number) - The last price of the option.
* **bid size** (integer) - The quantity of contracts offered at the bid price.
* **ask size** (integer) - The quantity of contracts offered at the ask price.
* **volume** (integer) - The number of contracts negotiated during the day.
* **open interest** (integer) - The number of open contracts.
* **underlying price** (number) - The price of the underlying security at the time of this option quote.
* **in the money** (boolean) - TRUE if the option contract was in the money at the time of the quote, FALSE otherwise.
* **intrinsic value** (number) - The intrinsic value of the option, if any.
* **extrinsic value** (number) - The extrinsic or time value of the option.
* **implied volatility** (number) - The implied volatility of the option.
* **delta** (number) - The delta of the option.
* **gamma** (number) - The gamma of the option.
* **theta** (number) - The theta of the option.
* **vega** (number) - The vega of the option.

#### Response Example
```json
{
  "symbol": "AAPL271217C00250000",
  "date": "2023-01-31T16:00:00Z",
  "price": 10.50,
  "bid": 10.45,
  "ask": 10.55,
  "last": 10.52,
  "bid_size": 100,
  "ask_size": 150,
  "volume": 5000,
  "open_interest": 20000,
  "underlying_price": 170.00,
  "in_the_money": false,
  "intrinsic_value": 0.00,
  "extrinsic_value": 10.50,
  "implied_volatility": 0.25,
  "delta": 0.50,
  "gamma": 0.10,
  "theta": -0.05,
  "vega": 0.02
}
```

### Notes
* All parameters must be enclosed in quotation marks or be references to cells containing text. A possible exception is when end date is specified as a number of days.
* Results will be returned as a value within a single cell if a single attribute is requested. Multiple attributes or multi-day results will be returned with column headers.
* Dates and times are returned in the same timezone of the exchange.

### Data Availability
* **Current Quotes (No Date Parameters):** Data type depends on user type and OPRA entitlement (real-time, 15-min delayed, or 1-day old historical).
* **Historical Quotes (With Date Parameters):** Available to all users regardless of entitlements. Access limits based on plan (Free Forever/Trial: 1 year, Starter: 5 years, Trader/Quant/Prime: no limit).

### Entitlements
* To get real-time options data, users need to sign the OPRA agreement.
```

--------------------------------

### STOCKDATA Function Documentation

Source: https://www.marketdata.app/docs/sheets/stocks/stockdata

This section details the syntax and parameters for the STOCKDATA function, used for fetching stock market data.

```APIDOC
## STOCKDATA Function

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

### Syntax

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

### Parameters

#### `symbols`
* **Type**: String or Range
* **Required**: Yes
* **Description**: One or more ticker symbols for the stocks. Can be a comma-separated string (e.g., "AAPL,MSFT,TSLA") or a range of cells (e.g., A1:A500).

#### `[historial attributes|quote attributes]`
* **Type**: String
* **Optional**: Yes
* **Description**: Specifies the type of data to retrieve. Defaults to "mid" for quotes and "close" for historical candles.
  * **Quote Attributes**: "mid", "mark", "bid", "ask", "last", "bidSize", "askSize", "volume", "symbol", "all"
  * **Historical Attributes**: "open", "high", "low", "close", "volume", "all", "symbol", "adjusted", "unadjusted"
  * **Special Attribute**: "price" (dual-mode for quotes and candles)

#### `start date`
* **Type**: Date or String
* **Optional**: Yes
* **Description**: The start date for fetching historical data. If only `start date` is provided, single-day data is returned. Accepts various date formats (e.g., "1/1/2021", TODAY()-30).

#### `end date`
* **Type**: Date or String
* **Optional**: Yes
* **Description**: The end date for fetching historical data, or the number of calendar days from `start date` for which to return data.

#### `resolution`
* **Type**: String
* **Optional**: Yes
* **Description**: The duration of each candle for historical data. Defaults to daily resolution if not specified.
  * **Minutely**: "minutely", "1", "3", "5", "15", "30", "45", ...
  * **Hourly**: "hourly", "H", "1H", "2H", ...
  * **Daily**: "daily", "D", "1D", "2D", ...
  * **Weekly**: "weekly", "W", "1W", "2W", ...
  * **Monthly**: "monthly", "M", "1M", "2M", ...
  * **Yearly**: "yearly", "Y", "1Y", "2Y", ...

### Sample Usage

#### Single Quote
```
=STOCKDATA("AAPL")
```

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

#### Single Historical Candles
```
=STOCKDATA("AAPL", "close", "1/1/2021", "1/31/2021", "hourly")
=STOCKDATA("AAPL", "all", TODAY()-30, 30)
```

#### Bulk Historical Candles
```
=STOCKDATA("AAPL,MSFT,TSLA", "all", "today")
=STOCKDATA("AAPL,MSFT,TSLA", "all", "12/20/2023")
=STOCKDATA(A1:A3, "all", "12/20/2023")
```

### Attribute Behavior (Price vs Close vs Last vs Mid)

| Function Call                 | During The Regular Session | During The Extended Session | Market Closed, Holidays, etc. |
|-------------------------------|----------------------------|-----------------------------|-------------------------------|
| `=STOCKDATA("AAPL")`        | Midpoint bid/ask           | Midpoint bid/ask            | Closing price of previous     |
| `=STOCKDATA("AAPL", "PRICE")` | Midpoint bid/ask           | Midpoint bid/ask            | Closing price of previous     |
| `=STOCKDATA("AAPL", "CLOSE")`| Last trade                 | Closing price of previous   | Closing price of previous     |
| `=STOCKDATA("AAPL", "LAST")` | Last trade                 | Last trade                  | Last trade of previous        |
| `=STOCKDATA("AAPL", "MID")`  | Midpoint bid/ask           | Midpoint bid/ask            | No data                       |
```

--------------------------------

### Sample Usage of 'noheaders' in Market Data Formulas

Source: https://www.marketdata.app/docs/sheets/universal-attributes/no-headers

Demonstrates how to apply the 'noheaders' attribute to various Market Data formulas like STOCKQUOTE, INDEXQUOTE, and OPTIONDATA to exclude header rows from the results. This is useful for direct data consumption.

```excel
=STOCKQUOTE("AAPL","all,noheaders")
=INDEXQUOTE("SPX", "all,noheaders")
=OPTIONDATA("AAPL240119C00150000", "bid,ask,noheaders", "9/23/2022", "10/23/2022")
```

--------------------------------

### Syntax for 'noheaders' Attribute in Market Data Formulas

Source: https://www.marketdata.app/docs/sheets/universal-attributes/no-headers

Illustrates the general syntax for incorporating the 'noheaders' attribute within any Market Data formula. It emphasizes that 'noheaders' can be placed anywhere in the comma-separated attribute list.

```excel
=FORMULA("symbol", "attribute1, attribute2, noheaders")
```

--------------------------------

### Enable Sidebar Check for Market Data Formulas

Source: https://www.marketdata.app/docs/sheets/troubleshooting/mobile-devices

This command re-enables the sidebar check for Market Data formulas. Use this if you encounter urlfetch errors after disabling the check for an extended period.

```gsheet
=DEBUG("enable_sidebar_check")
```

--------------------------------

### OPTIONLOOKUP Function Sample Usage in Google Sheets

Source: https://www.marketdata.app/docs/sheets/options/optionlookup

Demonstrates various ways to use the OPTIONLOOKUP function in Google Sheets, including direct text input and cell references. It handles different date formats and call/put specifications.

```google-sheets
=OPTIONLOOKUP("AAPL 7/21/2023 200 Call")
=OPTIONLOOKUP("AAPL 7/2023 $200 Call")
=OPTIONLOOKUP("AAPL July 2023 200 Call")
=OPTIONLOOKUP("AAPL Jan 200 Call")
=OPTIONLOOKUP(A1:D1)
```

--------------------------------

### OPTIONCHAIN Function Syntax - Google Sheets

Source: https://www.marketdata.app/docs/sheets/options/optionchain

This snippet defines the syntax for the OPTIONCHAIN function, used to fetch options data. It requires an underlying symbol and optionally accepts attributes, expiration date, strikes, and filters.

```google-sheets
=OPTIONCHAIN(underlying symbol, [attributes], expiration date, [strikes], [filters])
```

--------------------------------

### STOCKDATA Attribute Comparison - PRICE, CLOSE, LAST, MID

Source: https://www.marketdata.app/docs/sheets/stocks/stockdata

Compares the behavior of the STOCKDATA function with different attributes ('PRICE', 'CLOSE', 'LAST', 'MID') during regular, extended, and closed market sessions. Highlights differences in returned values.

```Excel Formula
=STOCKDATA("AAPL")
```

```Excel Formula
=STOCKDATA("AAPL", "PRICE")
```

```Excel Formula
=STOCKDATA("AAPL", "CLOSE")
```

```Excel Formula
=STOCKDATA("AAPL", "LAST")
```

```Excel Formula
=STOCKDATA("AAPL", "MID")
```

--------------------------------

### Default STOCKDATA Usage

Source: https://www.marketdata.app/docs/sheets/troubleshooting/price-differences

The standard usage of the STOCKDATA function, which by default displays real-time prices based on quotes (the midpoint between the bid and ask). This provides the most up-to-date pricing information.

```google-sheets
=STOCKDATA("TICKER")
```

--------------------------------

### OPTIONLOOKUP Function Syntax in Google Sheets

Source: https://www.marketdata.app/docs/sheets/options/optionlookup

Defines the basic syntax for the OPTIONLOOKUP function, specifying the required 'text' argument. This argument can be a string or a reference to cells containing option details.

```google-sheets
=OPTIONLOOKUP("text")
```

--------------------------------

### Fetch Option Chain Data with OPTIONCHAIN Function

Source: https://www.marketdata.app/docs/sheets/options/optionchain

The OPTIONCHAIN function retrieves option chain data from Market Data. It accepts a stock ticker, filter criteria, expiration date, and optional strike price range and call/put filters. The function is designed for use within Google Sheets.

```google-sheets
=OPTIONCHAIN("AAPL","all","1/17/2025")
=OPTIONCHAIN("AAPL","side,strike,bid,ask,","1/17/2025")
=OPTIONCHAIN("AAPL","all,noheaders","60 DTE","150,160")
=OPTIONCHAIN("AAPL","all","60 DTE","100-200","calls")
```

--------------------------------

### Disable Sidebar Check for Market Data Formulas

Source: https://www.marketdata.app/docs/sheets/troubleshooting/mobile-devices

This command disables the sidebar check, allowing Market Data formulas to execute without the sidebar being opened. This is necessary for mobile use. Be aware that prolonged spreadsheet use may lead to urlfetch errors.

```gsheet
=DEBUG("disable_sidebar_check")
```

--------------------------------

### EARNINGS Formula: Fetch Stock Earnings Data

Source: https://www.marketdata.app/docs/sheets/stocks/earnings

Fetches current or historical earnings data for a given stock ticker. The formula supports various attributes to customize the output, such as 'year', 'quarter', 'eps', and 'surprise eps percent'. It also allows specifying a date range for historical data. This formula is restricted to paid plans.

```google-sheets
 =EARNINGS("AAPL")
 =EARNINGS("AAPL", "all,noheaders")
 =EARNINGS("AAPL", "year,quarter,eps,eps surprise", "1/1/2021", "12/31/2021")
```

--------------------------------

### Fetch Bulk Stock Quotes - STOCKDATA

Source: https://www.marketdata.app/docs/sheets/stocks/stockdata

Fetches current stock quotes for multiple ticker symbols. Supports fetching specific attributes like 'bid', 'ask', and 'last', or all available quote attributes. Can also accept a range of cells containing symbols.

```Excel Formula
=STOCKDATA("AAPL,MSFT,TSLA")
```

```Excel Formula
=STOCKDATA("AAPL,MSFT,TSLA", "all")
```

```Excel Formula
=STOCKDATA(A1:A500, "bid,ask,last")
```
Tokens:
Raw