RTD.DB Concept

RTD.DB Concept

For example, we can 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 can create a database on any supported 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 the required data.

You can 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 can 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 can 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 can add additional tickers and delete inactive tickers.

You can 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 can configure this using two tables, real_time_tables and real_time_formulas, that you have to create once.

The real_time_tables table has the following structure in SQL Server:

CREATE TABLE [rtd].[real_time_tables](
    [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_real_time_tables_rtd] PRIMARY KEY ([ID]),
 CONSTRAINT [IX_real_time_tables_Schema_Name_rtd] UNIQUE ([TABLE_SCHEMA], [TABLE_NAME])
);

The real_time_formulas table has the following structure in SQL Server:

CREATE TABLE [rtd].[real_time_formulas](
    [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_real_time_formulas_rtd] PRIMARY KEY ([ID]),
 CONSTRAINT [IX_real_time_formulas_Schema_Name_ColumnName_rtd] UNIQUE ([TABLE_SCHEMA], [TABLE_NAME], [COLUMN_NAME])
);

We fill these tables in the next topics.

You can 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 real_time_formulas table:

TOS Quote formulas

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

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

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

It 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 can use other functions like =UtcNow(), =DateTime5(), or =Time15().

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

Configuring Data Tables

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

RealTimeTable configuration (1 table)

You can 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 can 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 in the real_time_formulas 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 this:

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 markets, you can create different tables and specify different time zones.

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

See available time zones at https://learn.microsoft.com/en-us/windows-hardware/manufacture/desktop/default-time-zones?view=windows-11.

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

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

You can 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 can use this feature if you load data from several world markets and need to turn on and off RTD.DB by a scheduler.

You can 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 can use Connection Manager to configure connection strings in a visual mode.

Starting Application

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

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

RTD.DB Interface

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

AAPL quotes

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 can 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 contains database templates for a range of tested trading platforms.

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

Read Configuring Tables to learn advanced topics.