Fast Program Trading Strategies Performance Evaluation

BigObject-Performance with 100x Speedup 

In the program trading area,traders develop different algorithms based on the TA indicators to generate buy-sell orders automatically. Before applying this strategy to the real world, a full performance evaluation with historical data is the first thing must do. BigObject allows users to do this easily. We first load the us historical stock data into a BigObject table.

Then we run TA functions by the APPLY commands:

APPLY ta('sma','stock','Inst','Close',ta1, 120, 0,0,0,0)

APPLY ta('ema','stock','Inst','Close',ta2, 120, 0,0,0,0)

APPLY ta('var','stock','Inst','Close',ta3, 120, 0,0,0,0)

APPLY ta('sd','stock','Inst','Close',ta4, 120, 0,0,0,0)

The simple moving average sma will be stored in the ta1 field, ema in the ta2 field, variance in the ta3 field and the standard deviation in the ta4 field.

Then we can detect the buy signal by the SQL command:

create table orders as ( select Inst, Date, Close*-1 as Amt,1 As Unit from stockta where PrevClose<ta1+ta4*2 and Close>ta1+ta4*2 )

This command detects whether the close price crosses sma+2 times the standard deviation. If yes, put it into orders table. The sma+2*sd the the upper bound of the band.

For the timing of sell, we use the command:

insert into orders select Inst,Date,Close as Amt,-1 As Unit from stockta where PrevClose>ta1-ta4*2 and Close<ta1-ta4*2

We check whether the close price passes the lower bound of  the band,i.e. sma-2*sd.

This is just a simple demonstration. Actually, we should add the signals when close price returns to the band.

When we want to know the performance, we simply group the Inst of the orders table to calculate the balance of the Amt and stock values still in our portfolio from the Unit.

For all 13,910,328 historical records with a ln() operation each, here is the BigObject throughput of this example:

bigobject inserted 13,910,328 rows: 167.60 seconds, rate= 82,992.7 /s

bigobject 4 ta indicators of period 20: 97.14 seconds

bigobject generated buying orders: 2.989 seconds

bigobject generated selling orders: 2.523 seconds

Source Code:

import datetime

import time

import mysql.connector

import random

import pickle

import os

cnx = mysql.connector.connect(user='scott', password='tiger',host='192.168.1.163')

cursor = cnx.cursor()

sql="create table stockta as (select Inst, Date, Open, High, Low, Close, PrevClose, CloseLn, PrevCloseLn, Volume, OpenInt, 0.0 as ta1, 0.0 as ta2, 0.0 as ta3, 0.0 as ta4 from stocks)"

cursor.execute(sql)

sql="APPLY ta('sma','stockta','Inst','Close','ta1', 20, 0,0,0,0)"

cursor.execute(sql)

sql="APPLY ta('ema','stockta','Inst','Close','ta2', 20, 0,0,0,0)"

cursor.execute(sql)

sql="APPLY ta('var','stockta','Inst','Close','ta3', 20, 0,0,0,0)"

cursor.execute(sql)

sql="APPLY ta('sd','stockta','Inst','Close', 'ta4', 20, 0,0,0,0)"

cursor.execute(sql)

# Generate Buy Orders

sql="create table orders as (select Inst, Date, Close*-1 as Amt, 1 As Unit from stockta where PrevClose<ta1+ta4*2 and Close>ta1+ta4*2)"

cursor.execute(sql)

# Generate Sell Orders

sql="insert into orders select Inst, Date, Close as Amt,-1 As Unit from stockta where PrevClose>ta1-ta4*2 and Close<ta1-ta4*2"

cursor.execute(sql)

# Make sure data is committed to the database

cnx.commit()

cursor.close()

cnx.close()