Build inventor quantified databases with SQLite

[TOC]

Summary

Data is the source of quantitative trading, how to efficiently manage large amounts of data is a very critical link, databases are one of the best solutions, and today the application of databases has become a quantitative standard configuration for all kinds of intraday trading, high-frequency trading and other strategies.FMZ.COMThe built-in database includes: how to create tables, store data, modify data, delete data, reference data, and how to apply it in real-world situations.

How to choose a database

Inventors familiar with quantification platforms should know that before trying to save data to local duplication, only the _G() function was used, and each time the policy was stopped, the _G() function automatically saved the information needed. But if you want to save more and more complex formatted data, the _G() function is obviously not very useful, so many people thought of building their own database to solve this problem.

When it comes to building your own databases, you can probably think of Oracle, MySQL, KDB, OneTick, NoSQL... these are all very good enterprise-level applications, both in terms of functionality and performance. But they also face several problems: difficult to use, cumbersome to configure and maintain, which is a bit of a cannonball for quantitative trading retailers, even if they only use a small part of the functionality.

Inventors quantify built-in databases

DBExec is a relational data management system interface based on SQLite. It is written in C. It is small in size, low in resources, and fast. It is very suitable for financial analytics enthusiasts to implement data management locally, as it can divide different arrays of objects (e.g. exchanges, data sources, prices) into different tables and define relationships between tables.

In addition, SQLite has a low learning cost, and most of the work performed on the database is done by SQLite statements. Familiarity with the basic syntax will meet most of the requirements.

Basic grammar

SQLite's syntax is not capitalized, although there are some commands that are sensitive to capitalization, such as GLOB and globes. SQLite statements can begin with any keyword, such as SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, etc. They represent: extract data, insert data, update data, delete data, modify database, delete data table.

function main() {
    // 创建：如果“users”表不存在就创建一个，“id”是整数且自动增加，“name”是文本形式且不为空
    Log(DBExec('CREATE TABLE IF NOT EXISTS "users" (id INTEGER PRIMARY KEY AUTOINCREMENT, name text not NULL);'));
    
    // 增加：
    Log(DBExec("INSERT INTO users(name) values('张三')"));
    Log(DBExec("INSERT INTO users(name) values('李四')"));
    
    // 删除：
    Log(DBExec("DELETE FROM users WHERE id=1;"));
    
    // 修改
    Log(DBExec("UPDATE users SET name='王五' WHERE id=2"));
    
    // 查询
    Log(DBExec('select 2, ?, ?, ?, ?', 'ok', true,9.8,null));
    Log(DBExec('select * from kvdb'));
    Log(DBExec('select * from cfg'));
    Log(DBExec('select * from log'));
    Log(DBExec('select * from profit'));
    Log(DBExec('select * from chart'));
    Log(DBExec("selEct * from users"));
}

A database usually contains one or more tables, each with a name tag. It is important to note that the system reserves tables: kvdb, cfg, log, profit, chart. That is, the system reserves names should be avoided when creating tables.

Strategic examples

Having learned the basic syntax of SQLite, we took the opportunity to use the inventor's built-in database to create an instance of collecting and using Tick data.

Step one: Update the hostFirst, make sure you're using the latest version of the host, and if you've downloaded a host before, you'll need to remove it first, and you'll need to install a new host.https://www.fmz.com/m/add-nodeThe page is being downloaded and deployed again.

Step two: Create a strategy

function main() {
    // 订阅合约
    _C(exchange.SetContractType, 'swap');
    
    // 创建数据表
    DBExec('CREATE TABLE IF NOT EXISTS "tick" (id INTEGER PRIMARY KEY AUTOINCREMENT,'.concat(
        'High FLOAT not NULL,', 
        'Low FLOAT not NULL,', 
        'Sell FLOAT not NULL,', 
        'Buy FLOAT not NULL,', 
        'Last FLOAT not NULL,', 
        'Volume INTEGER not NULL,', 
        'Time INTEGER not NULL);'
    ));
    
    // 获取10个tick数据
    while (true) {
        let tick = exchange.GetTicker();
        // 在tick表中增加数据
        DBExec(`INSERT INTO tick(High, Low, Sell, Buy, Last, Volume, Time) values(${tick.High}, ${tick.Low}, ${tick.Sell}, ${tick.Buy}, ${tick.Last}, ${tick.Volume}, ${tick.Time})`);
        // 查询所有数据
        let allDate = DBExec('select * from tick');
        if (allDate.values.length > 10) {
            break;
        }
        Sleep(1000);
    }
    
    // 查询所有数据
    Log(DBExec('select * from tick'));
    
    // 查询第一个数据
    Log(DBExec('select * from tick limit 1'));
    
    // 查询前两个数据
    Log(DBExec('select * from tick limit 0,2'));
    
    // 删除第一个数据
    Log(DBExec('DELETE FROM tick WHERE id=1;'));
    
    // 修改第二个数据
    Log(DBExec('UPDATE tick SET High=10000 WHERE id=2'));
    
    // 查询所有数据
    let allDate = DBExec('select * from tick')
    Log(allDate);
}

Step 3: Implement the strategyIn the Windows example, after running the policy, a folder named with a robot number is generated in the folder "logs\storage" in the administrator directory and opened, containing a file with the suffix "logs.db3", which is the inventor's file for quantifying the built-in database.The above code first creates a table of data named tick tick, then adds tick data fields to the table, then retrieves tick data from the exchange in the loop, and inserts this data into the tick tick data table, and jumps out of the loop if the data in the table is more than 10, and then uses 5 SQLite commands to query, delete, or modify the data in the table, respectively. Step four: Create a status barFinally, we added some code to create a status bar for the policy by taking inventor quantized data from the database and presenting the data in a more intuitive way.

    // 创建状态栏
    let table = {
        type: 'table',
        title: '币安Tick数据',
        cols: allDate.columns,
        rows: allDate.values
    }
    LogStatus('`' + JSON.stringify(table) + '`');

The above code uses data from the database to create a CoinTick data array. The array of columns in the array represents the array of columns in the state array, and the array of values represents the array of columns in the state array.

The full policy code

/*backtest
start: 2020-07-19 00:00:00
end: 2020-08-17 23:59:00
period: 15m
basePeriod: 15m
exchanges: [{"eid":"Binance","currency":"LTC_USDT"}]
*/

function main() {
    Log(DBExec('DROP TABLE tick;'));
    // 订阅合约
    _C(exchange.SetContractType, 'swap');

    // 创建数据表
    DBExec('CREATE TABLE IF NOT EXISTS "tick" (id INTEGER PRIMARY KEY AUTOINCREMENT,'.concat(
        'High FLOAT not NULL,',
        'Low FLOAT not NULL,',
        'Sell FLOAT not NULL,',
        'Buy FLOAT not NULL,',
        'Last FLOAT not NULL,',
        'Volume INTEGER not NULL,',
        'Time INTEGER not NULL);'
    ));

    // 获取10个tick数据
    while (true) {
        let tick = exchange.GetTicker();
        // 在tick表中增加数据
        DBExec(`INSERT INTO tick(High, Low, Sell, Buy, Last, Volume, Time) values(${tick.High}, ${tick.Low}, ${tick.Sell}, ${tick.Buy}, ${tick.Last}, ${tick.Volume}, ${tick.Time})`);
        // 查询所有数据
        let allDate = DBExec('select * from tick');
        if (allDate.values.length > 10) {
            break;
        }
        Sleep(1000);
    }

    // 查询所有数据
    Log(DBExec('select * from tick'));

    // 查询第一个数据
    Log(DBExec('select * from tick limit 1'));

    // 查询前两个数据
    Log(DBExec('select * from tick limit 0,2'));

    // 删除第一个数据
    Log(DBExec('DELETE FROM tick WHERE id=1;'));

    // 修改第二个数据
    Log(DBExec('UPDATE tick SET High=10000 WHERE id=2'));

    // 查询所有数据
    let allDate = DBExec('select * from tick')
    Log(allDate);

    // 创建状态栏
    let table = {
        type: 'table',
        title: '币安Tick数据',
        cols: allDate.columns,
        rows: allDate.values
    }
    LogStatus('`' + JSON.stringify(table) + '`');
}

Click on this linkhttps://www.fmz.com/strategy/388963You can copy the entire policy code.

The memory database

If the data from the operation is not intended to be saved permanently to disk, it can be added before the SQL statement:Symbols can be operated in the memory database, and the data reset when the robot is restarted

DBExec(":select 1,2,3");

Summary

Databases can not only hold huge amounts of data, but also the dreams of many quantitative trading enthusiasts. The use of databases is by no means limited to the examples in this article, and more uses can be found in the SQLite tutorial, as well as the series of articles published by the inventors of quantitative trading.