Configuring RTD.DB Tables

Tables of Last Values

We have created a simple table in the Concept topic:

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])
);

and configured formulas:

Such tables get the latest values from trading platforms as the DateTime field is not in the primary key.

RTD.DB reads metadata from a database and executes the UPDATE command first:

UPDATE [rtd].[QuotesTOS]
SET
    [DateTime] = @DateTime, [Open] = @Open, [High] = @High, [Low] = @Low, [Close] = @Close, [Volume] = @Volume
WHERE
    [Symbol] = @Symbol;

If the symbol does not exist (@@ROWCOUNT = 0), RTD.DB executes the INSERT command:

INSERT INTO [rtd].[QuotesTOS]
    ([Symbol], [DateTime], [Last], [Open], [High], [Low], [Close], [Volume])
VALUES
    (@Symbol, @DateTime, @Last, @Open, @High, @Low, @Close, @Volume);

As discussed in the Concept topic, RTD.DB loads symbols from the ticker tables, uses the current time for internal functions, and gets other values from a trading platform using RTD formulas.

Tables of End-of-Day Values

Let's create a separate table for end-of-day values:

CREATE TABLE [rtd].[QuoteDayHistoryTOS](
    [Symbol] [varchar](50) NOT NULL,
    [Date] [date] NOT NULL,
    [Time] [time](0) NULL,
    [Last] [smallmoney] NULL,
    [Open] [smallmoney] NULL,
    [High] [smallmoney] NULL,
    [Low] [smallmoney] NULL,
    [Close] [smallmoney] NULL,
    [Volume] [bigint] NULL,
  CONSTRAINT [PK_QuoteDayHistoryTOS_rtd] PRIMARY KEY ([Symbol], [Date])
);

We have split the DateTime field into the Date and Time fields and included the Date field in the primary key.

Let's configure the formulas in the real_time_formulas table:

As you see, we have used the =Date() and =Time() formulas for new columns.

Also, let's add the table configuration in the real_time_tables table:

As you see, the configuration is the same.

For this table, RTD.DB executes the UPDATE command using two primary key fields:

UPDATE [rtd].[QuotesTOS]
SET
    [Time] = @Time, [Open] = @Open, [High] = @High, [Low] = @Low, [Close] = @Close, [Volume] = @Volume
WHERE
    [Symbol] = @Symbol AND [Date] = @Date;

If the symbol does not exist (a new ticker or a new date), RTD.DB executes the INSERT command:

INSERT INTO [rtd].[QuotesTOS]
    ([Symbol], [Date], [Time], [Last], [Open], [High], [Low], [Close], [Volume])
VALUES
    (@Symbol, @Date, @Time, @Last, @Open, @High, @Low, @Close, @Volume);

So, to get end-of-day data in a database, add the date field to the primary key and specify the =Date() function for the date field.

You can find ready-to-use templates in the RTD Databases folder of the downloaded package.

Intraday History Tables

Let's create a table for 15-minute history:

CREATE TABLE [rtd].[Quote15MinuteHistoryTOS](
    [Symbol] [varchar](50) NOT NULL,
    [DateTime] [datetime] NULL,
    [Date] [date] NOT NULL,
    [Time] [time](0) NOT NULL,
    [Last] [smallmoney] NULL,
    [Open] [smallmoney] NULL,
    [High] [smallmoney] NULL,
    [Low] [smallmoney] NULL,
    [Close] [smallmoney] NULL,
    [Volume] [bigint] NULL,
  CONSTRAINT [PK_Quote15MinuteHistoryTOS_rtd] PRIMARY KEY ([Symbol], [Date], [Time])
);

We have added the Time field to the primary key. Also, we have added the DateTime field to have an update time.

Let's configure formulas:

The table contains the well-known formulas except for =Time15().

The =Time15() built-in function rounds the current time to 15 minutes up. As a result, we have 15-minute values in the Time field.

RTD.DB updates the record multiple times during 15-minute intervals. However, a database contains the 15-minute values only.

You can use the built-in functions like =Time1(), =Time5(), =Time10(), =Time15(), =Time20() or =Time60() to get data with the required time interval.

Here is a well-known table configuration:

Tick History Tables

You can also save every tick value to a database.

RTD.DB executes the INSERT commands only for such tables.

Let's create a table:

CREATE TABLE [rtd].[QuoteTickHistoryTOS](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [Symbol] [varchar](50) NULL,
    [DateTime] [datetime] NULL,
    [Date] [date] NULL,
    [Time] [time](0) NULL,
    [Last] [smallmoney] NULL,
    [Open] [smallmoney] NULL,
    [High] [smallmoney] NULL,
    [Low] [smallmoney] NULL,
    [Close] [smallmoney] NULL,
    [Volume] [bigint] NULL,
  CONSTRAINT [PK_QuoteTickHistoryTOS_rtd] PRIMARY KEY ([ID])
);

The primary key contains the identity column only.

Here is a formula configuration for the tick table:

The configuration contains an additional row for the ID field with an empty formula. It is important.

Here is a table configuration:

The configuration differs by the value in the IS_HISTORY field. 1 in this field forces using INSERT commands only.

Conclusion

We have learned how to configure four types of tables:

• Tables of last values
• Tables of end-of-day values
• Intraday history tables
• Tick tables

Of course, you can create such tables for stocks, options, and currencies, for any trading platform and supported database platform.

The RTD Database folder contains ready-to-use templates for several trading platforms that you can use as-is or modify to your needs.