Building a Quantitative Database of FMZ with SQLite

Abstract

Data is the source of quantitative trading, how to manage a large amount of data efficiently is a very critical link, database is one of the best solutions, nowadays the application of database is the quantitative standard for all kinds of daily trading, high frequency trading and other strategies. In this article, we will study the built-in database of FMZ Quant (https://www.fmz.com), including: how to create data tables, store data, modify data, delete data, reference data and how to apply it into practice.

How to choose the database

Those who are familiar with the FMZ Quant platform should know that before saving data for local reuse, you can only use the _G() function, which saves the information needed automatically every time you stop the strategy. But if you want to save more and more complex formatted data, the _G() function is obviously not very applicable, therefore, many people made up his or her mind of building their own database to solve this problem.

When it comes to self-built databases, you must think of Oracle, MySQL, KDB, OneTick, NoSQL… These are very excellent enterprise-level applications in both function and performance. However, there are also several problems: it is difficult to get started, and the configuration is cumbersome and maintenance is difficult. For retail quantitative traders, it is a bit like shooting flies with cannon. Even if they get started, they only use a small part of the functions.

Built-in database of FMZ Quant

Next, let’s take a look at the light database built-in by FMZ Quant. DBExec is a built-in relational data management system interface of FMZ Quant. It is developed based on SQLite and is written in C. It is not only small in size, little in resource consumption, but also fast in processing. It is very suitable for financial quantitative analysis enthusiasts to implement data management locally, because different “objects” (such as exchanges, data sources, and prices) can be divided into different tables, and relationships between tables can be defined. In addition, users do not need to install and configure them separately. They can use them directly by calling the DBExec() function!

In addition, it’s very easy to learn SQLite language, and most of the work performed on the database is completed by SQLite statements. If you are familiar with the basic grammar, you can meet most of the requirements. The following is the basic grammar of SQLite.

Basic grammar

The grammar of SQLite is case-insensitive, although there are some commands that are case-sensitive, such as GLOB and glob, which represent different meanings. SQLite statements can start with any keyword, such as SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, etc., which mean: extract data, insert data, update data, delete data, modify database, and delete data table. All statements are terminated by English semicolons. The following is a simple database creation, add, delete, change, and check operations:

function main() {
    // Create: If the "users" table does not exist, create one, "id" is an integer and is incremented automatically, "name" is in text form and is not empty
    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('Zhang San')"));
    Log(DBExec("INSERT INTO users(name) values('Li Si')"));
    
    // Delete:
    Log(DBExec("DELETE FROM users WHERE id=1;"));
    
    // Modify:
    Log(DBExec("UPDATE users SET name='Wang Wu' WHERE id=2"));
    
    // Search:
    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 is identified by a name, note that the system reserved tables are: kvdb, cfg, log, profit, chart. i.e. when creating tables, you should avoid the system reserved names. Let’s run the above code and output the following:

Strategy examples

Having learned the basic grammar of SQLite, we strike while the iron is hot to create an instance of collecting and using Tick data by using the built-in database of FMZ Quant.

Step 1: Update the docker

First, make sure you are using the latest version of the docker. If you have downloaded and used the docker before, you need to delete it first, and then redownload and redeploy it on https://www.fmz.com/m/add-node page.

Step 2: Create the strategy

function main() {
    // Subscribe contracts
    _C(exchange.SetContractType, 'swap');
    
    // Create 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);'
    ));
    
    // Get 10 pieces of tick data
    while (true) {
        let tick = exchange.GetTicker();
        // Add data to 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})`);
        // Search all data
        let allDate = DBExec('select * from tick');
        if (allDate.values.length > 10) {
            break;
        }
        Sleep(1000);
    }
    
    // Search all data
    Log(DBExec('select * from tick'));
    
    // Search the first data
    Log(DBExec('select * from tick limit 1'));
    
    // Search first two pieces of 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'));
    
    // Search all data
    let allDate = DBExec('select * from tick')
    Log(allDate);
}

Step 3: Run the strategy

Take Windows as an example, after running the strategy, a folder named after the robot number will be created in the “\logs\storage” directory of the docker directory. Open the folder, and there is a file with the suffix “. db3”, which is the file of the FMZ Quant built-in database. As shown in the following figure:

The above code creates a data table named “tick” first, then adds the tick data field to the table, then gets the tick data from the exchange in the loop, and inserts the data into the “tick” data table. At the same time, we judge that the amount of data in the data table exceeds 10, then we jump out of the loop. Finally, we use 5 SQLite commands to search, delete and modify the data in the data table respectively. And print them out in the logs, as shown in the following picture:

Step 4: Create status bar

Finally, we add some code to create a status bar for the strategy by obtaining the data in the FMZ Quant database to display the data more visually, the adding code shows as follows:

    // Create 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 through the data in the database. The “columns” field in the database represents the “rows” in the status bar, and the “values” field represents the “columns” in the status bar. As shown in the figure below:

Complete strategy 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;'));
    // Subscribe contracts
    _C(exchange.SetContractType, 'swap');

    // Create 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 pieces of tick data
    while (true) {
        let tick = exchange.GetTicker();
        // Add data to 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})`);
        // Search all data
        let allDate = DBExec('select * from tick');
        if (allDate.values.length > 10) {
            break;
        }
        Sleep(1000);
    }

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

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

    // Search first two pieces of 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'));

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

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

Click on this link https://www.fmz.com/strategy/388963 to copy the full strategy code.

Memory database

If you don’t want to save the data to the disk permanently, you can add the “:” symbol before the SQL statement to operate in the memory database, and the data will be reset after the robot restarts.

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

Summary

The database can not only carry massive data, but also carry the dream of many quantitative trading enthusiasts. The use of databases is by no means limited to the examples in this article. For more usage methods, please refer to the SQLite tutorial and the follow-up articles of FMZ Quant.