Configuring RTD DB Tables

Tables of Last Values

The example in the Concept topic defines a simple table:

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

The following formulas are configured:

These tables store the latest values from trading platforms, as the datetime field is not part of the primary key.

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

UPDATE [rtd].[tos_last_stock_quotes]
SET
    [datetime] = @datetime, [open] = @open, [high] = @high, [low] = @low, [close] = @close, [volume] = @volume
WHERE
    [symbol] = @symbol;

If no matching symbol is found (@@ROWCOUNT = 0), RTD DB executes the INSERT command:

INSERT INTO [rtd].[tos_last_stock_quotes]
    ([symbol], [datetime], [last], [open], [high], [low], [close], [volume])
VALUES
    (@symbol, @datetime, @last, @open, @high, @low, @close, @volume);

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

Tables of End-of-Day Values

Create a separate table for end-of-day values:

CREATE TABLE [rtd].[tos_historical_prices](
    [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_tos_historical_prices] PRIMARY KEY ([symbol], [date])
);

The datetime field is split into date and time fields, with the date field included in the primary key.

Configure the formulas in the real_time_formulas table:

The configuration uses the =Date() and =Time() formulas for the new columns.

Additionally, add the table configuration in the real_time_tables table:

The configuration is consistent across both tables.

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

UPDATE [rtd].[tos_historical_prices]
SET
    [time] = @time, [open] = @open, [high] = @high, [low] = @low, [close] = @close, [volume] = @volume
WHERE
    [symbol] = @symbol AND [date] = @date;

If no matching symbol is found (indicating a new ticker or a new date), RTD DB executes the INSERT command:

INSERT INTO [rtd].[tos_historical_prices]
    ([symbol], [date], [time], [last], [open], [high], [low], [close], [volume])
VALUES
    (@symbol, @date, @time, @last, @open, @high, @low, @close, @volume);

To store end-of-day data in a database, include the date field in the primary key and specify the =Date() function for the date field.

Ready-to-use templates are available in the RTD Databases folder of the downloaded package.

Intraday History Tables

Create a table for 15-minute history:

CREATE TABLE [rtd].[tos_stock_quotes_15m](
    [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_tos_stock_quotes_15m] PRIMARY KEY ([symbol], [date], [time])
);

The configuration adds the time field to the primary key and includes the datetime field for update time.

Configure formulas:

The table contains standard formulas, except for =Time15().

The built-in =Time15() function rounds the current time to the next 15-minute interval. Consequently, the table stores 15-minute values in the time field.

RTD DB updates the record multiple times during 15-minute intervals, but the database retains only the 15-minute values.

You can use built-in functions such as =Time1(), =Time5(), =Time10(), =Time15(), =Time20(), or =Time60() to obtain data at the desired time interval.

The following is a standard table configuration:

Tick History Tables

You can also save every tick value to a database.

RTD DB executes the INSERT commands only for these tables.

Create a table:

CREATE TABLE [rtd].[tos_stock_quotes_ticks](
    [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_tos_stock_quotes_ticks] PRIMARY KEY ([id])
);

The primary key consists solely of the identity column.

The following is the formula configuration for the tick table:

The configuration includes an additional row for the id field with an empty formula, which is required.

The following is a table configuration:

The configuration differs by the value in the is_history field. A value of 1 in this field enforces the use of INSERT commands only.

Conclusion

This document outlines the configuration of four types of tables:

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

These tables can be created for stocks, options, and currencies across any trading platform and supported database platform.

The RTD Database folder contains ready-to-use templates for several trading platforms that can be utilized as-is or modified to suit specific needs.