Use SQLite to Construct FMZ Quant Database

Use SQLite to Construct FMZ Quant Database [TOC]

Abstract

Data is the source of quantitative trading. It is very critical to efficiently manage a large amount of data. Database is one of the best solutions. Nowadays, the application of database has become the standard quantitative configuration of various day trading, high-frequency trading and other strategies. In this article, we will study the built-in database of FMZ Quant (FMZ.COM), including: how to create data tables, save data, modify data, delete data, and reference data, as well as how to use it in practice.

How to Select Database

Those who are familiar with FMZ Quant Trading Platform should have known that before this, if you want to save data to local for reuse, you can only use the _G() function. Every time you stop the strategy, the _G() function will automatically save the required information. But if you want to save more and more complex formatted data, the _G() function is obviously not suitable, so many people think of building their own database to solve the problem.

When it comes to self-built databases, everyone can think of Oracle, MySQL, KDB, OneTick, NoSQL… These are all excellent enterprise-level applications, both in terms of function and performance. But there are also some problems: it is hard to get started, and the configuration and the maintenance are difficult. For retail investors in quantitative trading, it is a bit like breaking a fly on the wheel. Even if you already get started with those applications, only a few functions of them will be used.

FMZ Quant Built-in Database

Next, let’s get to know the built-in lightweight database of FMZ Quant. DBExec is a relational data management system interface built into FMZ Quant. It is developed based on SQLite and written in C. It is not only small in size, low in resource occupation, but also has a very fast processing speed. It is very suitable for financial quantitative analysis enthusiasts to implement data management locally, because it can divide different “objects” (such as platforms, data sources, prices) into different tables, and define the relationships between the tables. In addition, users do not need to install and configure it separately; as long as they call the DBExec() function, it can be used directly!

In addition, the cost of learning the SQLite language is very low, and most of the work performed on the database is done by SQLite statements. Most of the needs can be met by getting familiar with the basic syntax. The following is the basic syntax of SQLite.

Basic Syntax

The syntax of SQLite is case-insensitive, but some commands are case-sensitive, such as GLOB and glob, which have different meanings. SQLite statements can start with any keyword, such as SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, etc., which respectively mean: extracting data, inserting data, updating data, deleting data, modifying database, and deleting data table. All statements are terminated with a semicolon. The following are operations like the creation, addition, deletion, modification, and query of a simple database:

function main() {
    // create: if the table of "users" do not exist, create one; "id" is integer and increases automatically; "name" is in form of text and not null 
    Log(DBExec('CREATE TABLE IF NOT EXISTS "users" (id INTEGER PRIMARY KEY AUTOINCREMENT, name text not NULL);'));
    
    // add: 
    Log(DBExec("INSERT INTO users(name) values('A')"));
    Log(DBExec("INSERT INTO users(name) values('B')"));
    
    // delete: 
    Log(DBExec("DELETE FROM users WHERE id=1;"));
    
    // modify:
    Log(DBExec("UPDATE users SET name='C' WHERE id=2"));
    
    // query:
    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 table has a name identification, it should be noted that the system reserved tables are: kvdb, cfg, log, profit, chart. That is, when creating tables, you should avoid system-reserved names. Let’s run the code above, which exports the following:

Strategy Example

After knowing the basic syntax of SQLite, let’s strike while the iron is hot; use FMZ Quant quantitative built-in database to create an instance of collecting and using Tick data.

Step 1: update docker First, make sure you are using the latest version of a docker. If you have downloaded and used a docker before, you need to delete it first, and download and deploy it again on the page (https://www.fmz.com/m/add-node).

Step 2: create strategy

function main() {
    // set contract 
    _C(exchange.SetContractType, 'swap');
    
    // create the data table 
    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);'
    ));
    
    // obtain 10 tick data 
    while (true) {
        let tick = exchange.GetTicker();
        // add data in the tick table 
        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})`);
        // query all data
        let allDate = DBExec('select * from tick');
        if (allDate.values.length > 10) {
            break;
        }
        Sleep(1000);
    }
    
    // query all data 
    Log(DBExec('select * from tick'));
    
    // query the first data 
    Log(DBExec('select * from tick limit 1'));
    
    // query the first two data 
    Log(DBExec('select * from tick limit 0,2'));
    
    // delete the first data 
    Log(DBExec('DELETE FROM tick WHERE id=1;'));
    
    // modify the second data 
    Log(DBExec('UPDATE tick SET High=10000 WHERE id=2'));
    
    // query all data 
    let allDate = DBExec('select * from tick')
    Log(allDate);
}

Step 3: operate strategy Take Windows as an example. After running the strategy, a folder named with the bot ID will be generated in the dictionary “\logs\storage” of the docker directory; open the folder, and there will be a file with “.db3” as the suffix; this file is the file of FMZ Quant built-in database. As shown in the following figure:

The above code first creates a data table named “tick”, then adds the tick data field to the table, then gets the tick data from the platform in the loop, and inserts this data into the “tick” data table, and at the same time If it is judged that the amount of data in the data table exceeds 10, it will jump out of the loop. Finally, use five SQLite commands to query, delete, and modify the data in the data table. And print it out in the log, as shown in the following image:

Step 4: create status bar At last, we add some pieces of code to create a status bar for the strategy by obtaining the data in FMZ Quant database to display the data more intuitively. The new code is as follows:

    // create a status bar 
    let table = {
        type: 'table',
        title: 'Binance Tick data',
        cols: allDate.columns,
        rows: allDate.values
    }
    LogStatus('`' + JSON.stringify(table) + '`');

The above code creates a “Binance Tick Data” table from the data in the database. The “columns” field in the database represents the “horizontal row” in the status bar, and the “values” field represents the “vertical row” in the status bar. As shown below:

Complete Strategy Source 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;'));
    // set contract 
    _C(exchange.SetContractType, 'swap');

    // establish the data table 
    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);'
    ));

    // obtain 10 tick data 
    while (true) {
        let tick = exchange.GetTicker();
        // add data in the tick table
        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})`);
        // query all data 
        let allDate = DBExec('select * from tick');
        if (allDate.values.length > 10) {
            break;
        }
        Sleep(1000);
    }

    // query all data 
    Log(DBExec('select * from tick'));

    // query the first data 
    Log(DBExec('select * from tick limit 1'));

    // query the first two data 
    Log(DBExec('select * from tick limit 0,2'));

    // delet the first data 
    Log(DBExec('DELETE FROM tick WHERE id=1;'));

    // modify the second data
    Log(DBExec('UPDATE tick SET High=10000 WHERE id=2'));

    // query all data 
    let allDate = DBExec('select * from tick')
    Log(allDate);

    // create the status bar 
    let table = {
        type: 'table',
        title: 'Binance Tick data',
        cols: allDate.columns,
        rows: allDate.values
    }
    LogStatus('`' + JSON.stringify(table) + '`');
}

Click the link: https://www.fmz.com/strategy/265906, and you can copy the complete source code.

In-Memory Database

If the operated data does not want to be permanently saved to the disk, you can add the : symbol before the SQL statement to operate in the in-memory database, and the data will be reset after the bot restarts.

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

Conclusion

The database can not only carry massive data, but also carry the dreams of many quantitative trading enthusiasts. The use of the database is, by no means, limited to the examples mentioned in the article. For more using methods, you can refer to the SQLite tutorial and the series of articles that will be posted on FMZ Quant later.