Excel Options Tracker

Guide
Interactive Brokers

Updated on: 10 January 2024.

1. Getting Started
1. Getting started
1.1. Prerequisite

To use the Excel tracker with Interactive Brokers, ensure you have met all the prerequisites below before continuing.
Note: Failure to ful l these prerequisites will result in extreme distress in nding out why the Excel tracker is not working.

Operating Systems:

1. You are using Windows.

Note: If you are using a Mac, you can use Parallels Desktop to install Windows.

2. You are using Microsoft Excel Desktop (latest version).

• The tracker may not work if you are using an older Excel version.
• The tracker will not work if you use Excel online (on the browser).

IBKR Installations:
Do not change the default installation location and folders (the default is C: drive).
If you change any of these, the portfolio tracker will not work.

3. You have installed Trader Workstation (TWS) Platform (Stable).

Download link: https://www.interactivebrokers.com/en/trading/tws.php#tws-software

4. You have installed TWS API (Stable) for Windows.

Download link: https://interactivebrokers.github.io/
Note: TWS API is NOT the same as TWS Platform; ensure you have installed both.

5. Have enabled Socket Clients in TWS Platform.

See 1.2. TWS Platform - Enable IBKR API Connections or see https://interactivebrokers.github.io/tws-api/initial_setup.html#enable_api

Market Data Subscriptions:

6. Have subscribed to IBKR Live market data subscriptions.

See 1.3. IBKR Live Market Data Subscriptions for the subscriptions required.
fi
fi
 1. Getting started
1.2. TWS Platform - Enable IBKR API Connections

After ful lling the pre-requisites, you will need to con gure the IBKR TWS Platform

1. Open your TWS Platform.

2. Enable API Connections.

1. Go to File > Global Con guration.

2. Go to API > Settings, and tick “Enable ActiveX and Socket Clients”. Ensure
the socket port is 7496.

3. Re-open the IBKR Excel portfolio tracker le.

fi
fi
fi
fi
1. Getting started
1.2. TWS Platform - Enable IBKR API Connections

Verify your Excel portfolio tracker can connect to the TWS platform.

1. Open your TWS platform; wait until the TWS platform is fully open before opening the tracker.

2. Open the IBKR Excel portfolio tracker.

On the top left of your TWS, click on DATA.

1. Getting started
1.2. TWS Platform - Enable IBKR API Connections

3. When the Excel portfolio tracker

successfully connects to the TWS
Platform, a row will appear under the
“API Connections” section. You are
good to go!

4. If the row doesn’t appear, ensure you

have ful lled all the prerequisites and
followed the setup guide carefully
without missing the installation steps.
fi
1. Getting started
1.3. IBKR Live Market Data Subscriptions

Note: The tracker will NOT work with delayed data / without live market data subscriptions.
You will require the following market data subscriptions for the tracker to work correctly:

• US Stocks market data subscription:

1.NASDAQ (Network C/UTP)

2.NYSE American, BATS, ARCA, IEX and Regional Exchanges (Network B)

3.NYSE (Network A/CTA)

• US Options market data subscription:

4.OPRA (US Option Exchanges)

2. Recording Your
Journal
2. Recording Your Journal
2.1. Mandatory Information

The IBKR Excel portfolio tracker requires users to input the options code in the IBKR format.
For the tracker to get the correct information, it requires the following information:

1. Symbol: The option's underlying ticker symbol.

2. Security Type: ie. OPT = Stock options, FOP = Future Options, STK = Stock, or IND = Index.

3. Last Trading Date: Options expiration date, following the IBKR date format, e.g. If the option expires on
30 April 2021, the date will be 20210430. STK or IND security types do not need expiration data.

4. P/C: Put or Call, ie. P = Put, C = Call.

5. Strike: Options strike.

6. Exchange: The exchange where the option is trading; we can use SMART most of the time.

7. Primary Exchange: The primary exchange where the option and its underlying are; we can leave it empty
most of the time. We must ll this in for indexes (IND security type) and overseas stocks (e.g. ASHR, GXC,
etc.).

8. Underlying Security Type: The underlying options type, ie. STK = Stock, FUT = Futures, or IND = Index.

9. Underlying Expiration: The expiration for the underlying; we must ll this in for FUT security type.
fi
fi
2. Recording Your Journal
2.2. Getting your information

eg. Diagonal Spread on Boeing.

• Leg 1 =
• Long Call,
• Strike = 175,
• Expiration = 10 November 2023.
• Leg 2 =
• Short Call,
• Strike = 200,
• Expiration = 20 October 2023.
 Leg 1 = Long call, 175 Strike, 10 Nov 2023 Expiration.

1. Input BA into
Option Chain

2. Select
expiration date,
in this example,
Nov 10 ‘23

3. Right click on
the strike, in this
example, 175
Long Call.
Leg 1 = Long call, 175 Strike, 10 Nov 2023 Expiration.

3. These are the

information we need
to input to the journal.

1. Search for 2. Click on

“Description” Description.
 Leg 2 = Short call, 200 Strike, 20 Oct 2023 Expiration.

1. Input BA into
Option Chain

2. Select
expiration date,
in this example,
Oct 20 ‘23

3. Right click on
the strike, in this
example, 200
Short Call.
Leg 2 = Short call, 200 Strike, 20 Oct 2023 Expiration.

3. These are the

information we
need to input to
the journal.

1. Click on
Description.
Recording Your Journal
Filling in the Journal - Stock Options
LEG 1 LEG 2
Recording Your Journal
Filling in the Journal - Future Options

To get the
underlying
information,
click on the
“Underlying”

For last
trading date,
ignore the
time.
3. Troubleshooting
3. Troubleshooting
3.1. Introduction

To troubleshoot any errors, whoever is troubleshooting your issues

will need to know what kind of errors you are facing (i.e. what is the
error message?).

You can nd the TWS error message in the Excel Portfolio tracker
under the AJ, AK and AL columns, which starts with:

TwsRtdServer error: <<error message>>

When contacting the coaches or Bang for help, please attach the
screenshot containing your options inputs and the error message
above.
fi
3. Troubleshooting
3.2. Common Errors

Error 1: “I see many #N/A in the portfolio tracker.”

Possible Cause:

• The prerequisite is not ful lled (e.g. skip steps, change the
installation folders, using Mac, using Excel online, etc.).

Possible Solutions:

• Follow the prerequisite steps carefully and do not miss any

steps.

• Re-install IBKR TWS Platform and API installations.

fi
3. Troubleshooting
3.2. Common Errors

Error 2: “I do not see the new row under the API Connections in
TWS Platform”.

Possible Cause:

• Same as Error 1.
Possible Solutions:

• Same as Error 1.
3. Troubleshooting
3.2. Common Errors

Error 3: “I see many #REF! in the portfolio tracker.”

Possible Cause:

• You accidentally modi ed the formula in the cell.

Possible Solutions:

• Find the cell that says `=#REF!` and x it!

• Re-download the Excel tracker and start afresh.
fi
fi
3. Troubleshooting
3.2. Common Errors

Error 4: “I see many #DIV/0! in the portfolio tracker.”

Possible Cause:

• TWS Platform is not running, check the TWS Error.

• Your options input is incorrect.
• You have accidentally deleted a required input for the calculation.
Possible Solutions:

• Ensure your TWS Platform is running before opening the Excel portfolio tracker.
• Check the “description” of your options in the TWS Platform and ensure your
options input is the same.

• Find the cell that you may have deleted and x it!
fi
3. Troubleshooting
3.2. Common Errors

TWS Errors: