IB Option Pricer (Excel)

Discussion in 'Online Brokers' started by Bruno, Nov 23, 2016.

  1. Bruno

    Bruno Moderator Staff Member

    Here is the initial release of BIBOP (Bruno's IB Option Pricer) that uses the IB TWS API to retrieve realtime options quotations for either SPX, RUT or NDX underlying symbols. It is fairly self-explanatory in my view. :)
    Download link in later post below in this same thread.
    It is recommended to install the IB API (https://www.interactivebrokers.com/en/index.php?f=5041) to have the ActiveX (OCX) properly registered in Windows.

    Note:
    1. Content (i.e. macros) must be enabled. Feel free to scan for viruses.
    2. Dropdown lists are coded for most cells to prevent wrong entries.
    3. The General tab serves to Connect/Disconnect to the IB TWS.
    4. The Tickers tab has a few editable (green) cells (many are commented) to determine which option chains to load (Populate button) and to request data for. The workbook supports 2 expirations in yyyymmdd format.
    5. The Pricer page is a tool to calculate Mid and Mark prices for any combo (Put/Call on either expiration). Combo size can be increased and the number of combos is not limited (just copy/paste new combo tables below). The first row contains a column number used in a VLOOKUP formula to access data from the Tickers page so other information e.g. Theta, Gamma, Vega etc can be added.
    6. The IV page is only a IV curve chart for the time being.
    BIBOP is free to use and distribute among CD members (VBA code is protected though). There will be a more comprehensive version at a nominal fee.
    I hope you'll enjoy it !
     
    Last edited: Nov 30, 2016
    Murphy Tan and Erik like this.
  2. Bruno

    Bruno Moderator Staff Member

    The spreadsheet won't open if the TWS OCX is not properly registered. Please test with the sample spreadsheet (TWS ActiveX.xls).
    If the problem persists, then try the following:

    Registering Third-Party ActiveX Controls
    To use a third-party ActiveX control in Visual Basic it must be registered first.
    To register the ActiveX control with Visual Basic, follow these instructions:
    1. From the Components menu in your VB project, select the TWS ActiveX control (Tws.ocx, located in the bin/ActiveX folder in your API Installation folder).
    2. Click Apply.
    3. Verify that the TWS control appears in the toolbar with all standard controls.
    If it still doesn't load, you may have to register the OCX manually using regsrvr32 on a command prompt.
     
  3. Bruno

    Bruno Moderator Staff Member

    Fixed a few bugs in the Pricer when adding combos and added comments here or there.
    New version in the works...
    Some of you have had trouble with installing the API and/or registering the OCX. The IB support is quite helpful in that regard. I suggest always trying to connect using the sample TWS ActiveX.xls provided with the API. OV users have the OCX already registered.
     

    Attached Files:

  4. stevegee58

    stevegee58 Well-Known Member

    I'm getting the error "Compile error in hidden module cTWSControl" I'm running Excel 2013 and I'm pretty sure it's the 64 bit version. I've read that there are some incompatibility issues between 32 and 64 bit Excel macros.
    I'm positive the TWS API is working since I use it with my own C++ applications.
     
  5. Bruno

    Bruno Moderator Staff Member

    Please try this test version. It uses the same code to connect to the TWS.
     

    Attached Files:

  6. Bruno

    Bruno Moderator Staff Member

    Minor update with version number (v1.01) and additional error control (maybe applicable to older Excel versions).
    Test spreadsheet also included.
     

    Attached Files:

  7. Bruno

    Bruno Moderator Staff Member

  8. Bruno

    Bruno Moderator Staff Member

    Hi Steve, is the latest build working for you ?
     
  9. stevegee58

    stevegee58 Well-Known Member

    Still not working. The Test OCX file says:

    Capture.PNG
     
  10. Bruno

    Bruno Moderator Staff Member

    Hi Steve, where does it stop ? Have you tried pressing the Debug button first ? Although the error is not popping up here, that one should be easy to fix.
     
  11. stevegee58

    stevegee58 Well-Known Member

    I suspect my tws.ocx has not been registered. I tried registering it but that fails too.
    I do like 90% of my development on Linux so I'm really not up on this Windows stuff. :D
     
  12. Bruno

    Bruno Moderator Staff Member

    Linux is certainly another ball game altogether ! Open the TEST OCX sample, go to the VBA Editor and check in Tools - References for the IB TWS Control. If it isn't then it is definitely not registered. It is the same as the one supplied with OptionVue, and I assume ONE also if it uses it rather than the older less reliable DDE interface.
    You can also register it manually with regsvr32 on a command line. Not sure the OCX was compiled for 32 or 64 bit environments but I guess doing it twice will do no harm.
     
  13. stevegee58

    stevegee58 Well-Known Member

    I checked in tools->references and it's definitely in the list with a check mark next to it.
    The error dialog is implying that it can't find TWSLib
     
  14. Bruno

    Bruno Moderator Staff Member

    Then the reference to the DLL is incorrectly recorded for some odd reason. I would either register it again manually or re-install the API.
     
  15. Bruno

    Bruno Moderator Staff Member

    Here attached is the REGTWS utility prog provided by OptionVue which I took the liberty to copy (hopefully not infringing anything here...).
    I believe it automates the OCX registration in Windows (the OCX file should be in the same folder as it is for OV).
     

    Attached Files:

  16. Bruno

    Bruno Moderator Staff Member

    Note that OV only supports the API v9.71. Contrary to what is said on IB's site, 9.72 is in production but since OV does not support it, BIBOP will not be recompiled for 9.72 until OV also upgrades. If the API 9.72 has been installed by mistake, please run the REGTWS utility again and OV will operate normally.
    Order management is a little different in 9.72 so the next features e.g. sending combo orders straight through to the TWS will be postponed.
    The next release will include a B&S theoretical pricer.
     
  17. stevegee58

    stevegee58 Well-Known Member

    Hi Bruno, I finally got that spreadsheet working with the REGTWS installer. Turns out I had an *ancient* tws.ocx file and the REGTWS installed a newer one.
     
  18. Bruno

    Bruno Moderator Staff Member

    A small bug was brought to my attention. When populating chains, the reference ATM price to base strikes distribution was SPX by default. This has been fixed.
    Note that it is recommended to populate chains (Tickers) and build combos (Pricer) either offline or at least before market data is requested. The TWS gateway can indeed hang Excel for a while (that issue is fixed in API 9.72 but BIBOP still uses 9.71 to remain compatible with OV). It is also advisable to use the "Other" item in "Select Range" to reduce calls to the TWS. All other choices (PUTS EXP1 etc.) will preselect the full list of 50 strikes.
    You can also notice a ATM Override cell (top right on Tickers page) if you wish to use your own base price for options chains.
     

    Attached Files:

  19. Bruno

    Bruno Moderator Staff Member

  20. Bruno

    Bruno Moderator Staff Member

    After testing BIBOP on a computer with limited resources (4GB RAM) and considering the IB API restricts the number of calls to the TWS, BIBOP has been streamlined to reduce price updates, calculations and screen updates.
    BIBOP V1.11 https://www.dropbox.com/s/ymndl4mau8ck8ie/BIBOP (v1.11).xlsm
    I am now working on an add-on spreadsheet (in test phase) to calculate vol surface and greeks behaviour (https://www.dropbox.com/s/bcgyn0bboexf0lc/Screenshot%202018-06-04%2021.03.00.png). It models European options hence it is only applicable for index options trading.
    The next project will focus on analysing the vol curve better e.g. determine vol regimes (sticky delta, sticky strike, other) to perform more adequate analysis (for instance Heston).

    BIBOP is freeware (VBA code is password protected) and it is offered without any warranty whatsoever.
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice