Concept

Concept

For example, we may get data in Microsoft Excel from a trading platform like thinkorswim using formulas like these:

=RTD("tos.rtd",,"LAST","AAPL")
=RTD("tos.rtd",,"OPEN","AAPL")
=RTD("tos.rtd",,"HIGH","AAPL")
=RTD("tos.rtd",,"LOW","AAPL")
=RTD("tos.rtd",,"VOLUME","AAPL")

The formulas in other trading platforms are similar.

We want to get these data in a database table with a structure:

CREATE TABLE [rtd].[QuotesTOS](
    [Symbol] [varchar](50) NOT NULL,
    [DateTime] [datetime] NULL,
    [Last] [smallmoney] NULL,
    [Open] [smallmoney] NULL,
    [High] [smallmoney] NULL,
    [Low] [smallmoney] NULL,
    [Close] [smallmoney] NULL,
    [Volume] [bigint] NULL,
  CONSTRAINT [PK_QuotesTOS_rtd] PRIMARY KEY ([Symbol])
);

We have to configure this task and then just run a trading platform and RTD.DB to update the data in a database daily.

Configuration steps include:

  1. Creating a database
  2. Creating data tables
  3. Creating ticker tables
  4. Creating configuration tables
  5. Configuring formulas
  6. Configuring data tables
  7. Configuring the connection string

Creating Databases

You may create a database on any supported database platform including the most popular free SQL Server Express and MySQL.

We recommend naming the database as "RTD" as this name is used by default in the bonus database templates.

Creating Data tables

You have to create database tables to store required data.

You may see the QuotesTOS table example above.

The downloaded RTD.DB package includes the RTD Databases folder with database templates for all supported database platforms and the tested trading platforms.
You may use the templates to create tables in a couple of minutes. Feel free to modify templates to your needs.

Creating Ticker Tables

You have to create a table that contains tickers to refresh.

In most cases, you may create separate tables for different instruments:

  • Stocks
  • Options
  • Currencies

The tables have a simple structure and must contain a column that contains tickers in the trading platform format.

To have the common column name for all instruments, let's use the 'Code' column name instead of names like 'Symbol' or 'OptionCode'.

So, the ticker table looks like this:

CREATE TABLE [rtd].[QuoteListTOS](
  [Code] [varchar](50) NOT NULL,
  CONSTRAINT [PK_QuoteListTOS_rtd] PRIMARY KEY ([Code])
);

Add first tickers to your table. Later, you may add additional tickers and delete inactive tickers.

You may skip creating special ticker tables if you already have a table with tickers in your database. Just modify the ticker table in the configuration described below.

Add tickers in your trading platform format. For example, thinkorswim accepts stock tickers like AAPL and option tickers like .AAPL180119C150.

Creating Configuration Tables

RTD.DB must "understand" what tables to update and what formulas to use.

You may configure this using two tables, RealTimeTables and RealTimeFormulas, that you have to create once.

The RealTimeTables table has the following structure in SQL Server:

CREATE TABLE [rtd].[RealTimeTables](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128) NOT NULL,
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [LOAD_CODE] [nvarchar](2000) NULL,
    [IS_HISTORY] [tinyint] NULL,
    [IS_DISABLED] [tinyint] NULL,
    [TIME_ZONE] [nvarchar](50) NULL,
    [RTD_INSTANCE_NAME] [nvarchar](50) NULL,
 CONSTRAINT [PK_RealTimeTables_rtd] PRIMARY KEY ([ID]),
 CONSTRAINT [IX_RealTimeTables_Schema_Name_rtd] UNIQUE ([TABLE_SCHEMA], [TABLE_NAME])
);

The RealTimeFormulas table has the following structure in SQL Server:

CREATE TABLE [rtd].[RealTimeFormulas](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128) NOT NULL,
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [COLUMN_NAME] [nvarchar](128) NOT NULL,
    [RTD_FORMULA] [nvarchar](255) NULL,
    [IS_DISABLED] [tinyint] NULL,
 CONSTRAINT [PK_RealTimeFormulas_rtd] PRIMARY KEY ([ID]),
 CONSTRAINT [IX_RealTimeFormulas_Schema_Name_ColumnName_rtd] UNIQUE ([TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME])
);

We fill these tables in the next topics.

You may find the ready-to-use SQL codes to create configuration tables for all supported database platforms in the install.sql file of the database templates.

Configuring Formulas

Let's add the following settings to the RealTimeFormulas table:

You may see that all rows configure the rtd.QuotesTOS table.

Also, you may see known RTD formulas in the RTD_FORMULA column.

However, the ticker position contains the [Symbol] value instead of a ticker like AAPL.

This is a trick. RTD.DB substitutes the [Symbol] value with a real ticker.

The table contains two rows without RTD formulas, for the Symbol and DateTime columns.

The Symbol column gets the value of a real ticker as it has the same name.

The DateTime column gets the current date and time value adjusted to the configured time zone. See below.

The =DateTime() is an internal RTD.DB function. You may use other functions like =UtcNow(), =DateTime5(), or =Time15().

Note that you may disable updating certain columns setting 1 into the IS_DISABLED column.

Configuring Data Tables

Let's add the following settings to the RealTimeTables table:

You may see that we have added the configuration for the rtd.QuotesTOS table and specified two values, LOAD_CODE and TIME_ZONE.

The LOAD_CODE field defines an SQL code used to get tickers.

In our case, the code selects tickers from the rtd.QuoteListTOS table that we discussed above.

Of course, you may use your tables instead. Just modify the SQL code in the LOAD_CODE field.

Note an important point. The SQL code must use the AS clause to select the tickers with the column name used in the RealTimeFormulas table.

In our case, the data table and all related formulas use the Symbol name, not the Code name of the rtd.QuoteListTOS table.

So the final SQL code looks like:

SELECT Code AS Symbol FROM rtd.QuoteListTOS

The second field, TIME_ZONE, defines the time zone used to adjust the current Windows time for functions like =DateTime().

In our case, we use the 'Eastern Standard Time' time zone for the US market.

If you get data from several world's markets, you may create different tables and specify different time zones.

For example, you may create tables like QuotesTOS_US or QuotesTOS_UK.

See available time zones at http://technet.microsoft.com/en-us/library/cc749073(v=ws.10).aspx.

The RTD_INSTANCE_NAME field specifies the RTD.DB instance that must update this table.

RTD.DB Personal and RTD.DB Standard have two predefined instances: x86 for the 32-bit and x64 for 64-bit RTD.DB executables.

You may specify the instance accordingly to the bitness of the trading platform.

RTD.DB Enterprise supports user defined instances configured in the application configuration files.

You may use this feature if you load data from several world's markets and need to turn on and off RTD.DB by a scheduler.

You may disable updating certain tables setting 1 into the IS_DISABLED column.

We discuss the IS_HISTORY field below.

Configuring Connection Strings

As the final configuration step, we must specify the connection string in the RTD.DB.exe.config and RTD.DB32.exe.config files.

RTD.DB connects to the specified databases under the specified credentials.

Here is an example of the configuration file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="rtd-mssql" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=RTD;Password=r#td_2014_abc!;User ID=rtd"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

You may use Connection Manager to configure connection strings in a visual mode.

Starting Application

Now, you may start a trading platform and RTD.DB.

Click the Start button, and you will see the screen like this:

Select data from your table in a database. You have to see new data like this:

Conclusion

We have done all configuration steps:

  1. Creating a database
  2. Creating data tables
  3. Creating ticker tables
  4. Creating configuration tables
  5. Configuring formulas
  6. Configuring data tables
  7. Configuring the connection string

You may repeat the steps for any trading platform that supports RTD or DDE and any supported database platform.

The downloaded RTD.DB package includes the RTD Databases folder that includes database templates for a range of tested trading platforms.

You may use these templates to create tables with preconfigured formulas in a couple of minutes.

Please read Configuring Tables to learn advanced topics.