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