Configuring Tables

Configuring 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 to the Date and Time fields, and included the Date field to 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, just add the date field to the primary key and specify the =Date() function for the date field.

You may 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 interval. However, a database contains the 15-minute values only.

You may 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 may also save every tick values 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. This is important.

Here is a table configuration:

The configuration differs with 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 may 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 may use as is or modify them to your needs.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.