Recently, FMZ quantitative trading platform supports the database interface, so the use of JavaScript language can also be very convenient to achieve a K line market data collector. When you have a need, act immediately

Before building the JavaScript version of the ticker collector, let’s get familiar with FMZ’s database interface, DBExec.

DBExec function

Familiarize yourself with the following operations:

  • Create a table
  • Write data to a table
  • Query the data in the table
function main() {
    Var strSql = ["CREATE TABLE RECORDS_MIN1(", "TS INT PRIMARY KEY NOT NULL,", "HIGH REAL NOT NULL,", "OPEN REAL NOT NULL,", "LOW REAL NOT NULL,", "CLOSE REAL NOT NULL,", "VOLUME REAL NOT NULL)"]. Join ("") {"rowsAffected":1,"lastInsertId":18} */    
    
    / * write data table / / INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) / / VALUES (1, 'Paul, 32, "California", 20000.00); Var strSql = ["INSERT INTO RECORDS_MIN1 (TS, HIGH, OPEN, LOW, CLOSE, VOLUME)", "VALUES (111111, 111.11, 111.12, 111.13, 111.14, 111.16); and "] .join("") // DBExec returns: {"rowsAffected":1,"lastInsertId":1} */
    
    // SELECT * FROM RECORDS_MIN1; var strSql = ["SELECT * FROM RECORDS_MIN1;"] .join("") // returns: {" columns ": [" TS", "HIGH", "OPEN", "LOW", "CLOSE", "VOLUME"], "values" : [[111111111.11, 111.12, 111.13, 111.14, 111.16]]} * /
    
    var ret = DBExec(strSql)    // Execute the SQL statement
    Log(ret)    
}
Copy the code

Design market collector

The database interface DBExec of FMZ can be used to collect the K line data of the exchange. For example, some policies calculate indicators based on long K-line data, and it is not easy to collect data to make the k-line data length enough to calculate indicators. However, when the policy program design is not perfect, the real disk stops abnormally, the code is temporarily adjusted, and the policy parameters are temporarily adjusted, and the real disk needs to be restarted. Once the real disk is restarted, the data collected is lost (saved in program variables). So using the database interface, save the collected market data is a very good solution.

Our requirements are very simple:

  • Program polling to get quotes
  • Judge the BAR update and write the completed BAR data into the database table for saving.
  • Query data for tables in the database
  • In order to display the data, this example added drawing (using the drawing library), through the policy interaction button, update the K-line chart.
  • Drop a database table
  • Initialize, recreate the database table, and write the latest data.

Simple data collector source:

var collecter = {} collecter.init = function(tableName) { this.preBarTS = 0 this.tableName = tableName Enclosing tableAvaliable = false / / testing table tableName if (typeof (tableName) = = "undefined" | | typeof (tableName)! = "string") { Log(tableName) throw "tableName error!" } // SELECT * FROM RECORDS_MIN1 LIMIT 1 var strSql = "SELECT * FROM " + tableName + " LIMIT 1" var ret = DBExec(strSql) if (! Ret) {// table does not exist, create table Log(" try to read table ", this.tableName, "data failed, Var strSql = ["CREATE TABLE "+ tableName + "(", "TS INT PRIMARY KEY NOT NULL,", "HIGH REAL NOT NULL,", "OPEN REAL NOT NULL,", "LOW REAL NOT NULL,", "CLOSE REAL NOT NULL,", "VOLUME REAL NOT NULL)" ].join("") ret = DBExec(strSql) if (! Ret) {throw "create" + tableName + "table failed!" } Log(" create ", tableName, ret)} Log(this.tablename, ret) ret) this.tableAvaliable = true } collecter.run = function(records) { if (! this.tableAvaliable) { return } var len = records.length var lastBar = records[len - 1] var beginBar = records[0] var Ret = null if (this.preBarTS == 0) {// init /* DELETE FROM table_name WHERE [condition]; */ var strSql = "DELETE FROM " + this.tableName + " WHERE TS >= " + beginBar.Time + ";" // Write ret = DBExec("BEGIN") Log("BEGIN:", ret) for (var I = 0; i < len - 1 ; i++) { var strSql = [ "INSERT INTO " + this.tableName + " (TS, HIGH, OPEN, LOW, CLOSE, VOLUME) ", `VALUES (${records[i].Time}, ${records[i].High}, ${records[i].Open}, ${records[i].Low}, ${records[i].Close}, ${records[i].Volume}); ` ].join("") DBExec(strSql) } ret = DBExec("COMMIT") Log("COMMIT:", ret) this.preBarTS = lastBar.Time } else if(this.preBarTS ! Var strSql = ["INSERT INTO "+ this.tableName +" (TS, HIGH, OPEN, LOW, CLOSE, VOLUME) ", `VALUES (${records[len-2].Time}, ${records[len-2].High}, ${records[len-2].Open}, ${records[len-2].Low}, ${records[len-2].Close}, ${records[len-2].Volume}); ` ].join("") ret = DBExec(strSql) Log("INSERT:", Ret) this.prebarts = lastbar.time}} collecter.getRecords = function() {var strSql = "SELECT * FROM "+ this.tableName + ";" var ret = DBExec(strSql) // Log("SELECT * FROM .. :", ret) // SELECT * FROM .. : {" columns ": [" TS", "HIGH", "OPEN", "LOW", "CLOSE", "VOLUME"], "values" : [[1616110200000580, 85.9, 57716.2, 57664.3, 57757.6, 24.216]. ...]. } var arr = ret.values var r = [] for (var i = 0 ; i < arr.length ; i++) { r.push({ Time : arr[i][0], High : arr[i][1], Open : arr[i][2], Low : arr[i][3], Close : arr[i][4], Volume : arr[i][5] }) } return r } collecter.deleteTable = function() { // DROP TABLE database_name.table_name; var strSql = "DROP TABLE " + this.tableName var ret = DBExec(strSql) if (! Ret) {Log(" delete table ", this.tableName, "failed: ", ret)} else {Log(" DROP TABLE:", this.tableName, "DROP TABLE:", ret) this.tableAvaliable = false } } function main() { collecter.init(tableName) while(true) { var r = _C(exchange.getRecords) // records TBL var rTbl = {type: "table", title: "data ", cols: ["strTime", "Time", "High", "Open", "Low", "Close", "Volume"], rows : [] } var arrR = [] if (collecter.tableAvaliable) { arrR = collecter.getRecords() } for (var i = arrR.length - 1; (i > arrR.length - 1 - 9) && (i >= 0); i--) { var bar = arrR[i] rTbl.rows.push([_D(bar.Time), bar.Time, bar.High, bar.Open, bar.Low, bar.Close, Bar.volume])} LogStatus(_D()," ", arrR.length, ", collecter.tableAvaliable:", collecter.tableAvaliable, "\n", "'" + json.stringify (rTbl) + "' ") collecter.run(r) var CMD = GetCommand() if(CMD) { ", CMD) var arr = cmd.split(":") If (arr[0] == "refreshRecords") {if(collecter.tableavaliable) {var records = collecter.getRecords() $. PlotRecords (records, collecter. TableName)/draw/using line drawing class library} else {Log (" corresponding database table does not exist collecter. TableAvaliable: ", Collecter.tableavaliable)} else if (arr[0] == "deleteBDTable") {collecter.deleteTable()} else if (arr[0] == "deleteBDTable") == "initCollecter") {Log(" initCollecter") collecter.init(tableName)}} Sleep(5000)}}Copy the code

test

Compare the data

Policy address: www.fmz.com/strategy/26…