Understanding the RTD DB Configuration for Data Storage

Data can be retrieved in Microsoft Excel from trading platforms like thinkorswim desktop desktop using the following RTD formulas:

=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")

Formulas in other trading platforms are similar.

The objective is to store this data in a database table structured as follows:

CREATE TABLE [rtd].[tos_last_stock_quotes](
    [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_tos_last_stock_quotes] PRIMARY KEY ([symbol])
);

This task requires configuring the system and running both the trading platform and RTD DB to update the 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

Databases can be created on SQL Server, MySQL, or PostgreSQL.

It is recommended to name the database "RTD," as this name is used by default in the database templates.

Creating Data Tables

Create database tables to store the required data.

This example illustrates the tos_historical_prices table.

The downloaded RTD DB package includes a folder with database templates. You can use these templates to create tables quickly and modify them as needed.

Creating Ticker Tables

Create a table to store tickers for refreshing data.

In most cases, separate tables can be created for different instruments:

• Stocks
• Options
• Currencies

These tables should have a simple structure, containing a column for tickers in the trading platform format.

The ticker table structure is as follows:

CREATE TABLE [rtd].[tos_stock_list](
  [symbol] [varchar](50) NOT NULL,
  CONSTRAINT [PK_tos_stock_list] PRIMARY KEY ([symbol])
);

Start by adding initial tickers to your table. Additional tickers can be added later, and inactive tickers can be removed.

If a ticker table already exists in your database, you can modify it instead of creating a new one.

Add tickers in the trading platform format. For example, thinkorswim desktop accepts stock tickers like AAPL and option tickers like .AAPL260116C200.

Creating Configuration Tables

RTD DB must recognize which tables to update and which formulas to use.

This can be configured using two tables, real_time_tables and real_time_formulas, which need to be created once.

The real_time_tables table structure in SQL Server is as follows:

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] PRIMARY KEY ([id]),
 CONSTRAINT [IX_real_time_tables_schema_name] UNIQUE ([table_schema], [table_name])
);

The real_time_formulas table structure in SQL Server is as follows:

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] PRIMARY KEY ([id]),
 CONSTRAINT [IX_real_time_formulas_schema_name_columnname] UNIQUE ([table_schema], [table_name], [column_name])
);

These tables will be configured in the subsequent sections.

You can find the SQL code to create configuration tables for all supported database platforms in the install.sql file of the database templates.

Configuring Formulas

Add the following settings to the real_time_formulas table:

This example configures the tos_historical_prices table.

The rtd_formula column contains known RTD formulas.

The [symbol] placeholder is used instead of a specific ticker like AAPL. RTD DB substitutes the [symbol] placeholder with the actual ticker.

The table includes two rows without RTD formulas for the symbol and datetime columns.

The symbol column receives the value of the actual ticker, while the datetime column receives the current date and time adjusted to the configured time zone. The =DateTime() function is an internal RTD DB function. Other functions such as =UtcNow(), =DateTime5(), or =Time15() can also be used.

To disable updates for specific columns, set the is_disabled column to 1.

Configuring Data Tables

Add the following settings to the real_time_tables table:

This example adds configuration for the tos_last_stock_quotes table, specifying the load_code and time_zone values.

The load_code field defines the SQL code used to retrieve tickers. In this case, it selects tickers from the rtd.tos_stock_list table.

You may use your own tables; simply modify the SQL code in the load_code field.

It is important to note that the SQL code must use the AS clause to select tickers with the column name used in the real_time_formulas table.

The time_zone field defines the time zone used to adjust the current Windows time for functions like =DateTime(). In this case, the Eastern Standard Time time zone is used for the US market.

For data from multiple world markets, create different tables and specify different time zones. For example, you might create tables like tos_historical_prices_us or tos_historical_prices_uk.

Refer to the 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 responsible for updating this table.

RTD DB Personal and Standard editions have two predefined instances: x86 for the 32-bit and x64 for the 64-bit RTD DB executables. Specify the instance according to the bitness of the trading platform.

RTD DB Enterprise supports user-defined instances configured in the application configuration files. This feature is useful for loading data from multiple world markets and managing RTD DB operation via a scheduler.

To disable updates for specific tables, set the is_disabled column to 1.

The following sections will describe the is_history field.

Configuring Connection Strings

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

RTD DB connects to the specified databases using the provided 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 also use Connection Manager to configure connection strings in a visual mode.

Starting Application

Start the trading platform and RTD DB.

Launch the application using the Start button. The interface will display as follows:

Select data from your database table. You should see new data displayed as follows:

Conclusion

All configuration steps have been completed:

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

These steps can be repeated for any trading platform that supports RTD or DDE and any supported database platform.

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

These templates can be used to create tables with preconfigured formulas quickly.

Refer to Configuring Tables for advanced topics.