2017-09-01

Fast Program Trading Strategies Performance Evaluation





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()



No comments:

Post a Comment