LeapFrogBI SQL Server Historical Data Provider

The Wealth-Lab 8 LeapFrogBI SQL Server Historical Data Provider (HDP) enabled users to configure and extract data from a SQL Server database to be used in back testing trading strategies. This provider is designed generically to allow for custom implementation. Note that users will need database development skills to make use of this extension.

Capabilities

• Connect to a SQL Server instance (local or remote)
• Custom connection properties are supported
• Supports any number of custom or standard symbols
• Supports scales ranging from one minute to yearly
• Supports Tick data
• Timeseries supports OHLCV values
• Historical data corrections are supported

Connection Configuration

The SQL Server HDP extension configuration enables users to specify information required to establish a connection to SQL Server database and describe the source for core data requests. Test configuration values using a SQL client such as SQL Server Management Studio by establishing a connection to your SQL Server instance.

A full list of connection string properties available can be found at the following location. The extension uses Microsoft.Data.SqlClient.dll to establish a connection. Configuration definitions below associated with connection properties are sourced from the below linked article.

SqlConnection.ConnectionString Property

SQL Server Instance Name – Provide the name of your SQL Server instance. This can be a default instance or a named instance (ServerName\InstanceName). The name should be found in, select * from sys.servers. Use (local) to refer to your local default SQL instance.

Database Name – Provide the name of the database that will serve as the initial catalog. For all SQL related configuration values, this is the database that will be targeted unless you use a three-part name to override this setting [database].[schema].[table].

SQL Auth User ID & SQL Auth Password – If you will be using SQL authentication, provide the User ID and Password values.

Integrated Security – Enable to use windows integrated security. When checked the SQL Auth User ID and SQL Auth Password are ignored.

Encrypt – When enabled TLS encryption is used for all data sent between the client and server if the server has a certificate installed.

Trust Server Certificate – When enabled TLS is used to encrypt the channel when bypassing walking the certificate chain to validate trust. If Trust Server Certificate is set to true and Encrypt is set to false, the channel is not encrypted.

Connection Timeout - The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.

Command Timeout - The default wait time (in seconds) before terminating the attempt to execute a command and generating an error.

Conn Prop (optional) – Add 1 or more connection property name/value pairs. Separate each name/value pair using “;”.

• Example: Application Name=WealthLab;Application Intent=ReadOnly

Conn String (override) - Optionally, you can provide a full connection string. If you provide a value for the Conn String, this will override all other connection related values. Below are a couple connection string examples.

• SQL Auth Connection String Example: server=LeapFrogBI;initial catalog=WLData;user id=myUser;password=myPass;TrustServerCertificate=True

• Integrated Security Connection String Example: server=LeapFrogBI;initial catalog=WLData;Integrated Security=SSPI;TrustServerCertificate=True

Data Access Configuration

The below three configuration values are required as they enable a common method for extracting key data elements from your custom database. The described parameters will be replaced with values dynamically at runtime. The provided configuration must include all parameters described. When the provided SQL statement is executed (after replacing parameterization with actual values), it must return the described schema. Parameterization will be in this format . Such as {0} and {1}.

SQL – Get Scales

Provide a SQL statement that returns a distinct list of scales that are supported by your database. No parameterization is required. This will limit the scale drop down to the provided values after initial configuration is complete.

Example: SELECT [ScaleName] From udf_Scale()

Parameters: – None. Return all supported scales.

Schema:

• [ScaleName] varchar(100) NOT NULL

Supported Values: (exact match required)

• Quarterly
• Monthly
• Weekly
• Daily
• 60 Minute
• 30 Minute
• 15 Minute
• 10 Minute
• 5 Minute
• 1 Minute
• Yearly
• 7 Minute
• 1 Tick

Sample Values

SQL - Get Symbol

Provide a SQL statement that returns a single record for the provided Symbol. This script is used to retrieve the symbol name and determine whether the provided symbol is supported. If a record is returned, this denotes that the symbol is supported and can support a historical data request.

Example: SELECT Symbol, SymbolName From udf_Symbol('{0}')

Parameters:

• {0} – Symbol. Example: LFBI.A1

Schema:

• [Symbol] varchar(100) NOT NULL,
• [SymbolName] varchar(500) NOT NULL,

Sample Values:

SQL - Get Timeseries

Data requests will be sent to the configured database using the provided TimeSeries parameterized SQL statement. All requests from Wealth-Lab will contain a scale & symbol value. Bar count, start date, end date, and last modified date will default to 1,000,000,000, 1900-01-01, 9999-12-31, and 1900-01-01 respectively unless the data request provides specific values.

Example:

SELECT top {2} [DateTime], [Open], [High], [Low], [Close], [Volume] From udf_TimeSeries('{0}','{1}') Where [DateTime] between '{3}' and '{4}' and isnull(ModifyDateTime, CreatedDateTime) >= '{5}' Order by [DateTime] desc

Parameters:

• {0} - Scale Name. Example: Daily
• {1} – Symbol. Example: LFBI.A1
• {2} – Bar Limit. Example: 5000
  • Note: In the sample query this is implemented with top as ordered by DateTime desc (get the last X bars).
  • If WL does not specify a value, 1,000,000,000 is set as the default value (1 billion).
• {3} – Start Date. Example: 2024-01-01
  • If WL does not specify a start date, 1900-01-01 is set as the default value.
• {4} – End Date. Example: 2024-08-01
  • If WL does not specify an end date, 9999-12-31 is set as the default value.
• {5} – LastModifiedDateTime. Example: 2024-01-01 16:25:12
  • Note: This field is used to support data corrections. When corrections are enabled in WL, WL will provide the datetime of the user’s last data request (lastRequestDate). The SQL script should return only records where the LastModifiedDate on or after the lastRequestDate. In the example above, this is accomplished here; isnull(ModifyDateTime, CreatedDateTime) >= ‘{5}’

Schema:

• [DateTime] datetime NOT NULL
  • DateTime of tick for tick data
• [Open] float NOT NULL
  • Set to 0 for tick data (ignored)
• [High] float NOT NULL
  • Set to 0 for tick data (ignored)
• [Low] float NOT NULL
  • Set to 0 for tick data (ignored)
• [Close] float NOT NULL
  • Contains price for tick data
• [Volume] int NOT NULL
  • Contains size for tick data

Sample Values:

Database Design

Your database can be designed to support your unique requirements. The only requirements for using the SQL Server extension are described in the above configuration sections.

The attached generated t-SQL script can be used to create the required core objects in your database (update as needed). Also attached is a full backup of a SQL Server 2022 database that can be restored and modified as needed.

Note that this starter script uses user defined table valued functions for each of the three SQL configuration values required by the extension. This is not required. You can use any source you like for each script such as a view or a table. Using a table valued UDF provides a level of flexibility and abstraction that may come in handy, however. For example, you may want to store your time series data in many different tables. You can use a table valued UDF to direct the request to the appropriate source based on the requested symbol and scale.

Database design is beyond the scope of this configuration document but consider that you have the full power of SQL Server at your fingertips. You can build your database in a way that supports your trading strategy ideas!

WLData.bak WDataDDL.sql