Knowledge Base | How to Load Option Chains from thinkorswim to Databases

thinkorswim is a very popular and awesome trading platform.

thinkorswim has fantastic features for traders with a technical background and allows easily getting real-time data for stocks, options and pairs in Microsoft Excel.

If you have no opened account, definitely try. You are welcome to point me as a referrer. My account name is Vaselenko.

The RTD.DB product allows updating databases using thinkorswim data via Excel formulas and has preconfigured databases.

Here is an example of loaded option data:

Option data from thinkorswim

As you may see, thinkorswim has not only standard option chain data but also greeks, calculated implied volatility, probabilities, etc.

Here is a screenshot of the RTD.DB main screen:

Loading option chains from thinkorswim

As you may see, RTD.DB updates 4008 option contracts (rows) in the OptionDayHistoryTOS table.

We have successfully tested loading data for 40K option contracts also.

Below you may find steps to configure your database to get option data from thinkorswim:

  1. Install and test RTD.DB.
  2. Create an RTD database on your server.
  3. Configure a connection to your server.
  4. Configure updated database tables.
  5. Configure a list of option contracts to refresh.

Installing and Testing RTD.DB

Quick start steps:

  1. Download and install RTD.DB.
  2. Run RTD.DB using the link from 'Start,  All Programs, Gartle, RTD.DB'.

You will see the following application screen:

RTD.DB Screenshot

RTD.DB will start updating the preconfigured local Microsoft SQL Server Compact database with data from Yahoo Finance for currencies, stocks, and options.

For example, you may see the OptionDayHistoryYahoo table.

Click the Stop button.

Creating RTD Database

Steps:

  1. Open the folder of the unzipped download package.
  2. Open the RTD Database folder and open a folder for the target database platform.
  3. Run the 1-edit-config.cmd batch file and edit the connection strings to your database server.
  4. Run the 2-create-database.cmd batch file.
  5. Run the 3-rtd-setup.cmd batch file.
  6. Run the 4-rtd-list.cmd batch file.
  7. Run the 5-rtd-grant.cmd batch file.

As a result, you will have the RTD database on your target server.

If the files have other names, please, read the readme.txt in the setup folder.

Configuring Connections

Steps:

  1. Run RTD.DB if it is not started.
  2. Click the Connections button.
  3. Edit and enable the connection string to your server and disable the default SQL Server Compact database.
  4. Click the Save button and close the application.
  5. Click the RTD.DB Stop button and then click the Start button.

Here is a screenshot of the gConnectionManager application used for editing connecting strings.

Configuring RTD.DB connections using gConnectionManager

Configuring Updated Database Tables

The RTD database contains preconfigured tables for loading various data from various data sources with various time frames.

Steps to activate or deactivate loading data:

  1. Open the RTD database in your database manager.
  2. Open the rtd.RealTimeTables (rtd.real_time_tables) table in edit mode.
  3. Set 1 into the IS_DISABLED column to disable loading and set the value to NULL to enable loading.
    For thinkorswim, activate the OptionDayHistoryTOS table.
  4. Save changes.
  5. Click the RTD.DB Stop button and then click the Start button.

Here is an example of the table for Microsoft SQL Server:

RTD.DB Configuration Table for Microsoft SQL Server

See RTD.DB Database Configuration about configuration tables.

Editing Configuration Tables in Microsoft Excel

You may use Microsoft Excel to edit configuration tables if you have SaveToDB add-in installed.

Preparation steps:

  1. Open Microsoft Excel.
  2. Open the RTD Workbooks folder in the downloaded package.
  3. Open the workbook for your target server. For example, rtd-mysql.xlsx for MySQL.
  4. Open the SaveToDB tab, click Wizards menu, Change Connection Wizard.
  5. Specify a server, a database, and database credentials for your database. Click Next.
  6. Click Finish to change connections for the selected tables.
  7. Save the workbook to your location.

Configuration steps:

  1. Open the prepared workbook connected to your database.
  2. Open the RealTimeTables worksheet.
  3. Open the SaveToDB tab and click the Reload button.
  4. Set 1 into the IS_DISABLED column to disable loading and clear the value to enable loading.
  5. Open the SaveToDB tab and click the Save button.

Configuring List of Option Contracts to Refresh

As you may see above, option tables for thinkorswim are configured with the following code in the LOAD_CODE field:

SELECT Code FROM rtd.OptionListTOS

You may change this SQL code for loading option codes from your existing table.

Or you may use the preconfigured rtd.OptionListTOS table.

The rtd.OptionListTOS table contains test option codes:

Option list for loading from thinkorswim

You may insert required option codes into the table using any available method.

Note that you may use the workbook prepared in the previous step.
Copy the OptionListYahoo worksheet to a new one, rename it to OptionListTOS, click SaveToDB, Wizards, Data Connection Wizard, and connect to the rtd.OptionListTOS table.

Note that you have to remove expired option codes from the table also.

Different editions of RTD.DB have different limits for a number of updated option codes.

The Personal edition ($100) allows updating option codes for up to 1000 stocks.

See RTD.DB Edition Comparison for details.

Getting Option Codes

RTD.DB loads option data for specified option codes. It does not load entire option chains automatically.

So if you need to load all option contracts for the specified symbols, you may use any solution to get option codes, for example, from Yahoo Finance, and to insert them into the rtd.OptionListTOS table.

You may solve this task using Market Data Downloader.

For example, for SQL Server, you may add the following SQL code to insert option codes from the OptionContacts table:

INSERT INTO RTD.rtd.OptionListTOS
    (Code)
SELECT
    c.Code
FROM
    (
        SELECT
            '.' + c.Symbol
            + CONVERT(char(6), c.ExpDate, 12)
            + c.[Type]
            + CAST(CAST(c.Strike AS float) AS varchar) AS Code
        FROM
            MarketData.yahoo.OptionContracts c
        WHERE
            c.ExpDate > GETDATE()
    ) c
    LEFT OUTER JOIN RTD.rtd.OptionListTOS t
        ON t.Code = c.Code COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE
    t.Code IS NULL

Downloads

Products for Traders and Investors
RTD.DB
The app saves data from trading platforms like Thinkorswim to databases
Version: 5.6 | 11/03/2017 | 9.6MB | Home | What's New | Edition Comparison
Download
MARKET.CSV
The app loads data from Yahoo Finance, Google Finance, and MSN Money to CSV and SQL Server
Version: 2.7 | 12/13/2017 | 0.9MB | Home | What's New | Edition Comparison
Download

Disclaimer

thinkorswim is a registered trademark of TD Ameritrade IP Company.