Excel Formula Reference: Market Data

Excel Price Feed enables you to stream live market data directly into your Excel spreadsheet via a single, simple Excel formula.

Before requesting any data, you may want to change the Market Data setting depending on what, if any, market data subscriptions you have with Interactive Brokers. You can do this via the Configuration Pane.

There are 4 possible options:

Interactive Brokers Excel portfolio: symbol, position

Live: Streaming data relayed back in real time. Market data subscriptions are required.
Delayed: Free, delayed data is 15 - 20 minutes delayed. This is available for many types of instruments without market data subscriptions. If live data is available a request for delayed data is ignored by Interactive Brokers.
Frozen: Last data recorded at market close. Requires the same market data subscriptions necessary for live data.
Delayed Frozen:Requests delayed "frozen" data for a user without market data subscriptions.

Generally you want to select "Live" if you have any market data subscriptions, otherwise "Delayed".

You can either enter the formula manually in your spreadsheet or use the Configuration Pane to build the formula before inserting into your spreadsheet.

Enter formula using the Configuration Pane

EPF.IB.Stream.MarketData

=EPF.IB.Stream.MarketData(field, symbol, securityType, currency, exchange, conId, primaryExchange, lastTradeDateOrContractMonth, strike, multiplier, right, tradingClass)

The input parameters are:

Parameter	Optional	Description
Field	No	One of the available market data fields, see below for details.
Symbol	No	The symbol of the security.
Security Type	No	The security's type, see below for details.
Currency	No	The security's currency code, such as USD, EUR, JPY etc.
Exchange	No	The destination exchange, such as SMART, NYSE etc
Primary Exchange	Yes	The security's primary exchange.
Last Trade Date or Contract Month	Yes	The contract's last trading day or contract month (for Options and Futures). Strings with format YYYYMM will be interpreted as the Contract Month whereas YYYYMMDD will be interpreted as Last Trading Day.
Strike	Yes	Options only: the option's strike price.
Multiplier	Yes	The security's multiplier.
Right	Yes	Options only: "Put" or "Call" or "P" or "C"
Trading Class	Yes	The security's trading class name.

Field

Field	Description
BidPrice	Highest priced bid for the contract.
AskPrice	Lowest price offer on the contract.
LastPrice	Last price at which the contract traded (does not include some trades in RTVolume).
DelayedBid	Delayed bid price.
DelayedAsk	Delayed ask price.
DelayedLast	Delayed last traded price.
High	High price for the day.
Low	Low price for the day.
Volume	Trading volume for the day for the selected contract (US Stocks: multiplier 100).
ClosePrice	The last available closing price for the previous day. For US Equities, we use corporate action processing to get the closing price, so the close price is adjusted to reflect forward and reverse splits and cash and stock dividends.

Security Type

Security Type	Description
BOND	Bonds.
CASH	FX (Foreign Exchange) currency pairs.
CFD	CFDs (Contract For Difference).
CMDTY	Commodities.
CRYPTO	Cryptocurrencies.
FOP	Futures Options.
FUND	Mutual Funds.
FUT	Futures.
IND	Indexes.
OPT	Options.
STK	Stocks.
WAR	Standard Warrants.

Examples

Stream the bid price of Apple stock:

=EPF.IB.Stream.MarketData("BidPrice","AAPL","STK","USD","SMART")

Stream the ask price of spot gold:

=EPF.IB.Stream.MarketData("BidPrice","AAPL","STK","USD","SMART")

Getting Started

General Formulas

Yahoo Finance

Technical Analysis Toolkit

Federal Reserve Economic Data(FRED)

IG Index / IG Markets

EODHistoricalData.com