Abstract
Data is the source of quantitative trading. How to efficiently manage a large amount of data is a very critical link. Database is one of the best solutions. In this article, we will examine the built-in database of Inventor Quantization (FMZ.COM), including how to create data tables, store data, modify data, delete data, reference data, and how to use it in the real world.
How to select a database
Those familiar with the Inventor quantization platform should know that until now, the only way to save data for local reuse was to use the _G() function, which automatically saved the required information each time the policy was stopped. However, if you want to store more and more complex formatted data, the _G() function is obviously not suitable, so many people have come up with their own database to solve this problem.
Oracle, MySQL, KDB, OneTick, NoSQL… These are excellent enterprise-level applications that are powerful in both functionality and performance. However, there are several problems: it is difficult to get started, the configuration is cumbersome and maintenance is troublesome, which is a bit of a cannon to beat flies for the quantitative trading retail investors, even if the start of the use of only a small part of the function.
The inventor quantifies the built-in database
Let’s meet inventors quantitative built-in lightweight database, DBExec is the inventor of quantitative built-in a relational data management system interface, based on the SQLite development, its itself is written in C, not only the small size, low occupancy resources, and the processing speed is fast, very suitable for the quantitative analysis of financial data management in local implementation, This is because you can divide different “objects” (such as exchanges, data sources, prices) into different tables and define relationships between the tables. In addition, users do not need to install and configure separately, just call the DBExec() function can be used directly!
In addition, the SQLite language is cheap to learn, and most of the work performed on the database is done by SQLite statements. Familiarity with the basic syntax should do most of the work, and here is SQLite’s basic syntax.
Basic grammar
SQLite’s syntax is case insensitive, although 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 means: Extract data, INSERT data, UPDATE data, DELETE data, modify database, DELETE data table, respectively. All statements end with a semicolon. The following is a simple database to create, add, delete, change, search and other operations:
Function main() {// create: If the "users" table does not exist, create one, "ID" is an integer and automatically increases, 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(' ')")); Log(DBExec("INSERT INTO users(name) values(' username ')")); Log(DBExec("DELETE FROM users WHERE id=1; )); Log(DBExec("UPDATE users SET name=' username '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")); }Copy the codeA database usually contains one or more tables, each identified by a name. Note that the system retains tables separately: KVDB, CFG, log, Profit, chart. That is, you should avoid system-reserved names when creating tables. Let’s run the above code and output the following:
Strategies for instance
With the basic syntax of SQLite in mind, we hit the hot button and created an instance of collecting and using Tick data using the inventor quantified built-in database.
Step 1: Update your host Make sure you are using the latest version of your host. If you have downloaded and used a host before, remove it and download and deploy it again at www.fmz.com/m/add-node.
Step 2: Create the policy
The function main () {/ / subscription agreement _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); ')); While (true) {let tick = exchange.getTicker (); DBExec(' INSERT INTO tick(High, Low, Sell, Buy, Last, Volume, Time) values(${tick. ${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')); 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 tick = DBExec('select * from tick') Log(tick); }Copy the codeStep 3: Run the policyIn Windows, for example, after running the policy, a folder named after the robot number is generated in the “logs\storage” directory of the host directory. Open the folder, and there is a file with the suffix “.db3 “inside. This file is the file that the inventor quantified the built-in database. As shown in the following figure:
Let table = {type: 'table', title: 'Binance Tick data ', cols: alldate. columns, rows: allDate.values } LogStatus('`' + JSON.stringify(table) + '`');Copy the codeThe code above creates a table of “Binance Tick data” from the data in the database. Columns and Values in the database indicate rows and columns in the status bar. As shown in the figure below:
Complete 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; ')); / / subscription contract _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); ')); While (true) {let tick = exchange.getTicker (); DBExec(' INSERT INTO tick(High, Low, Sell, Buy, Last, Volume, Time) values(${tick. ${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')); 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 tick = DBExec('select * from tick') Log(tick); Let table = {type: 'table', title: 'Binance Tick data ', cols: alldate. columns, rows: allDate.values } LogStatus('`' + JSON.stringify(table) + '`'); }Copy the codeClick on the link www.fmz.com/strategy/26… The complete policy code can be copied.
In-memory database
If you do not want the data to be permanently saved to disk, you can add: to the front of the SQL statement to operate in the memory database, and the data is reset after the robot restarts
DBExec (" : select 1, 2, 3 ");Copy the codeconclusion
The database can not only carry massive data, but also carry the quant dream of many quant trading enthusiasts. The use of databases is by no means limited to the examples in this article. For more information, see the SQLite tutorial and the inventor quantified’s subsequent series of articles.