def get_fi_p1_data(database, table_name_raw, table_name_clean, point_number_arn_list=[5, 10, 25], n_tr = 5, n_tp = 5, n_wcp = 5):
'''
Extract information from table_name_raw and insert important information into table_name_clean
Important information includes:
highest, lowest, closing, mean, and std in previous n_period days
average value of the highest, lowest, closing, mean, and std based on previous 2, 3, 4, and 5 days
Slope of closing rate predicted by Taylor expansion based on previous 2, 3, 4, and 5 days.
Input:
database: the pointer of the database
table_name_raw: table name with raw data
table_name_clean: table name with result to put in
n_period: important information based on previous n_period days
Output:
All the information will be output to table_name_clean
'''
##--create pointer to the database
database_pt = database.cursor()
##--get number of data from table and maximum number needed for the linear regression
sql_comm = 'select count(*) from %(table_name)s'%{'table_name': table_name_raw}
database_pt.execute(sql_comm)
data_num = int(database_pt.fetchone()[0])
##--create column for the aroon up and down in previous several days
point_number_max = max(point_number_arn_list)
for idx in point_number_arn_list:
sql_comm = 'alter table %(table_name)s add pre_fi_arnup%(idx)s float' \
%{'table_name': table_name_clean, 'idx': idx}
try:
database_pt.execute(sql_comm)
except:
pass
database.commit()
for idx in point_number_arn_list:
sql_comm = 'alter table %(table_name)s add pre_fi_arndown%(idx)s float' \
%{'table_name': table_name_clean, 'idx': idx}
try:
database_pt.execute(sql_comm)
except:
pass
database.commit()
##--insert data into database
for idx in xrange(1,data_num+1):
sql_comm = 'select * from %(table_name)s where tradeID = %(id)s'%{'table_name': table_name_raw, 'id': idx}
database_pt.execute(sql_comm)
line = database_pt.fetchone()
if idx == 1:
preC = nlist(point_number_max,line[6])
else:
preC.add(line[6])
arn_list = get_arn(preC, point_number_arn_list)
for i_idx in xrange(len(arn_list)):
value = arn_list[i_idx][0]
sql_comm = 'update %(table_name)s set pre_fi_arnup%(i_idx)s = %(value)s \
where tradeID = %(idx)s'%{'table_name': table_name_clean, 'idx': idx, \
'i_idx': point_number_arn_list[i_idx], 'value': value}
database_pt.execute(sql_comm)
value = arn_list[i_idx][1]
sql_comm = 'update %(table_name)s set pre_fi_arndown%(i_idx)s = %(value)s \
where tradeID = %(idx)s'%{'table_name': table_name_clean, 'idx': idx, \
'i_idx': point_number_arn_list[i_idx], 'value': value}
database_pt.execute(sql_comm)
database.commit()
##--create column for true range in previous several days
for idx in xrange(n_tr):
sql_comm = 'alter table %(table_name)s add pre_fi_tr%(idx)s float' \
%{'table_name': table_name_clean, 'idx': idx}
try:
database_pt.execute(sql_comm)
except:
pass
database.commit()
##--insert data into database
for idx in xrange(1,data_num+1):
sql_comm = 'select * from %(table_name)s where tradeID = %(id)s'%{'table_name': table_name_raw, 'id': idx}
database_pt.execute(sql_comm)
line = database_pt.fetchone()
if idx == 1:
preC = line[6]
else:
curH = line[2]
curL = line[3]
if idx == 2:
preTR = nlist(n_tr, get_tr(curH, curL, preC))
else:
preTR.add(get_tr(curH, curL, preC))
for i_idx in xrange(min(n_tr, len(preTR.element))):
sql_comm = 'update %(table_name)s set pre_fi_tr%(i_idx)s = %(value)s \
where tradeID = %(idx)s'%{'table_name': table_name_clean, 'idx': idx, \
'i_idx': i_idx, 'value': preTR.element[i_idx]}
database_pt.execute(sql_comm)
preC = line[6]
database.commit()
##--create column for true price in previous several days
for idx in xrange(n_tp):
sql_comm = 'alter table %(table_name)s add pre_fi_tp%(idx)s float' \
%{'table_name': table_name_clean, 'idx': idx}
try:
database_pt.execute(sql_comm)
except:
pass
database.commit()
##--insert data into database
for idx in xrange(1,data_num+1):
sql_comm = 'select * from %(table_name)s where tradeID = %(id)s'%{'table_name': table_name_raw, 'id': idx}
database_pt.execute(sql_comm)
line = database_pt.fetchone()
curC = line[6]
curH = line[2]
curL = line[3]
if idx == 1:
preTP = nlist(n_tp, get_tp(curH, curL, curC))
else:
preTP.add(get_tp(curH, curL, curC))
for i_idx in xrange(min(n_tp, len(preTP.element))):
sql_comm = 'update %(table_name)s set pre_fi_tp%(i_idx)s = %(value)s \
where tradeID = %(idx)s'%{'table_name': table_name_clean, 'idx': idx, \
'i_idx': i_idx, 'value': preTP.element[i_idx]}
database_pt.execute(sql_comm)
database.commit()
##--create column for weighted closing price in previous several days
for idx in xrange(n_wcp):
sql_comm = 'alter table %(table_name)s add pre_fi_wcp%(idx)s float' \
%{'table_name': table_name_clean, 'idx': idx}
try:
database_pt.execute(sql_comm)
except:
pass
database.commit()
##--insert data into database
for idx in xrange(1,data_num+1):
sql_comm = 'select * from %(table_name)s where tradeID = %(id)s'%{'table_name': table_name_raw, 'id': idx}
database_pt.execute(sql_comm)
line = database_pt.fetchone()
curC = line[6]
curH = line[2]
curL = line[3]
if idx == 1:
preWCP = nlist(n_wcp, get_wcp(curH, curL, curC))
else:
preWCP.add(get_wcp(curH, curL, curC))
for i_idx in xrange(min(n_wcp, len(preWCP.element))):
sql_comm = 'update %(table_name)s set pre_fi_wcp%(i_idx)s = %(value)s \
where tradeID = %(idx)s'%{'table_name': table_name_clean, 'idx': idx, \
'i_idx': i_idx, 'value': preWCP.element[i_idx]}
database_pt.execute(sql_comm)
database.commit()
def get_lr_fft_data(database, table_name_raw, table_name_clean, point_number_lr_list=[3, 4, 5, 10, 20, 30], point_number_fft_list=[2, 4, 6, 8, 10, 20, 30]):
'''
Extract information from table_name_raw and insert feature into table_name_clean
Feature includes:
Amplitude and wave phase from FFT based on previous points in point_number_fft_list
Slope from linear regression based on previous points in point_number_lr_list
Input:
database: the pointer of the database
table_name_raw: table name with raw data
table_name_clean: table name with result to put in
n_period: important information based on previous n_period days
Output:
All the information will be output to table_name_clean
'''
##--create pointer to the database
database_pt = database.cursor()
##--get number of data from table and maximum number needed for the linear regression
sql_comm = 'select count(*) from %(table_name)s'%{'table_name': table_name_raw}
database_pt.execute(sql_comm)
data_num = int(database_pt.fetchone()[0])
##--create column for the slopes of linear regression in previous several days
point_number_max = max(point_number_lr_list)
for idx in point_number_lr_list:
sql_comm = 'alter table %(table_name)s add pre_lr_slope_C%(idx)s float' \
%{'table_name': table_name_clean, 'idx': idx}
try:
database_pt.execute(sql_comm)
except:
pass
database.commit()
##--insert data into database
for idx in xrange(1,data_num+1):
sql_comm = 'select * from %(table_name)s where tradeID = %(id)s'%{'table_name': table_name_raw, 'id': idx}
database_pt.execute(sql_comm)
line = database_pt.fetchone()
if idx == 1:
preC = nlist(point_number_max,line[6])
else:
preC.add(line[6])
lr_slope_list = get_lr_slope(preC, point_number_lr_list)
for i_idx in xrange(len(lr_slope_list)):
value = lr_slope_list[i_idx]
sql_comm = 'update %(table_name)s set pre_lr_slope_C%(i_idx)s = %(value)s \
where tradeID = %(idx)s'%{'table_name': table_name_clean, 'idx': idx, \
'i_idx': point_number_lr_list[i_idx], 'value': value}
database_pt.execute(sql_comm)
database.commit()
##--create column for the slopes of linear regression in previous several days
point_number_max = max(point_number_fft_list)
for idx in point_number_fft_list:
sql_comm = 'alter table %(table_name)s add pre_fft_amp%(idx)s float' \
%{'table_name': table_name_clean, 'idx': idx}
try:
database_pt.execute(sql_comm)
except:
pass
sql_comm = 'alter table %(table_name)s add pre_fft_theta%(idx)s float' \
%{'table_name': table_name_clean, 'idx': idx}
try:
database_pt.execute(sql_comm)
except:
pass
database.commit()
##--insert data into database
for idx in xrange(1,data_num+1):
sql_comm = 'select * from %(table_name)s where tradeID = %(id)s'%{'table_name': table_name_raw, 'id': idx}
database_pt.execute(sql_comm)
line = database_pt.fetchone()
if idx == 1:
preC = nlist(point_number_max,line[6])
else:
preC.add(line[6])
fft_result = get_fft(preC, point_number_fft_list)
for i_idx in xrange(len(fft_result)):
value = fft_result[i_idx]
sql_comm = 'update %(table_name)s set pre_fft_amp%(i_idx)s = %(value)s \
where tradeID = %(idx)s'%{'table_name': table_name_clean, 'idx': idx, \
'i_idx': point_number_fft_list[i_idx], 'value': value[0]}
database_pt.execute(sql_comm)
sql_comm = 'update %(table_name)s set pre_fft_theta%(i_idx)s = %(value)s \
where tradeID = %(idx)s'%{'table_name': table_name_clean, 'idx': idx, \
'i_idx': point_number_fft_list[i_idx], 'value': value[1]}
database_pt.execute(sql_comm)
database.commit()
def get_previous_data(database, table_name_raw, table_name_clean, n_period = 5):
'''
Extract information from table_name_raw and insert important information into table_name_clean
Important information includes:
highest, lowest, closing, mean, and std in previous n_period days
average value of the highest, lowest, closing, mean, and std based on previous 2, 3, 4, and 5 days
Slope of closing rate predicted by Taylor expansion based on previous 2, 3, 4, and 5 days.
Input:
database: the pointer of the database
table_name_raw: table name with raw data
table_name_clean: table name with result to put in
n_period: important information based on previous n_period days
Output:
All the information will be output to table_name_clean
'''
##--create pointer to the database
database_pt = database.cursor()
##--get number of data from table
sql_comm = 'select count(*) from %(table_name)s'%{'table_name': table_name_raw}
database_pt.execute(sql_comm)
data_num = int(database_pt.fetchone()[0])
##--create column for high, low, mean, close, and std in previous several days
pre_name_list = ['H', 'L', 'M', 'C', 'Std', 'HL']
for pre_name in pre_name_list:
for idx in xrange(n_period):
sql_comm = 'alter table %(table_name)s add pre%(pre_name)s%(idx)s float' \
%{'table_name': table_name_clean, 'pre_name': pre_name, 'idx': idx}
database_pt.execute(sql_comm)
database.commit()
##--Create column for the average high, low, mean ,close, and std in previous several days
for pre_name in pre_name_list[:-2]:
for idx in xrange(2, n_period+1):
sql_comm = 'alter table %(table_name)s add pre_avg_%(pre_name)s%(idx)s float' \
%{'table_name': table_name_clean, 'pre_name': pre_name, 'idx': idx}
database_pt.execute(sql_comm)
database.commit()
##--Create slope of close using rates in previous 2, 3, 4, and 5 days
for idx in xrange(2, n_period+1):
sql_comm = 'alter table %(table_name)s add pre_slope_C%(idx)s float' \
%{'table_name': table_name_clean, 'idx': idx}
database_pt.execute(sql_comm)
database.commit()
##--insert date to table
sql_comm = 'insert into %(table_name)s (trade_date) select trade_date from %(old_table)s'%{'table_name': table_name_clean, \
'old_table': table_name_raw}
database_pt.execute(sql_comm)
database_pt.execute('select * from %(table_name)s'%{'table_name': table_name_clean})
database.commit()
##--insert data into database
for idx in xrange(1,data_num+1):
sql_comm = 'select * from %(table_name)s where tradeID = %(id)s'%{'table_name': table_name_raw, 'id': idx}
database_pt.execute(sql_comm)
line = database_pt.fetchone()
if idx == 1:
preH = nlist(n_period,line[2])
preL = nlist(n_period,line[3])
preM = nlist(n_period,line[4])
preStd = nlist(n_period,line[5])
preC = nlist(n_period,line[6])
preHL = nlist(n_period,(line[2]+line[3])/2.)
else:
preH.add(line[2])
preL.add(line[3])
preM.add(line[4])
preStd.add(line[5])
preC.add(line[6])
preHL.add((line[2]+line[3])/2.)
length_pre = min(n_period,idx)
for pre_name in pre_name_list:
for i_idx in xrange(length_pre):
if pre_name == 'H':
value = preH.element[i_idx]
elif pre_name == 'L':
value = preL.element[i_idx]
elif pre_name == 'M':
value = preM.element[i_idx]
elif pre_name == 'Std':
value = preStd.element[i_idx]
elif pre_name == 'C':
value = preC.element[i_idx]
elif pre_name == 'HL':
value = preHL.element[i_idx]
sql_comm = 'update %(table_name)s set pre%(pre_name)s%(i_idx)s = %(value)s \
where tradeID = %(idx)s'%{'table_name': table_name_clean, 'pre_name': pre_name, 'idx': idx, \
'i_idx': i_idx, 'value': value}
database_pt.execute(sql_comm)
database.commit()
##--insert average values
pre_avg_H = get_average(preH, n_period)
pre_avg_L = get_average(preL, n_period)
pre_avg_M = get_average(preM, n_period)
pre_avg_Std = get_average(preStd, n_period)
pre_avg_C = get_average(preC, n_period)
length_pre = min(n_period-1,idx-1)
for pre_name in pre_name_list[:-2]:
for i_idx in xrange(length_pre):
if pre_name == 'H':
value = pre_avg_H[i_idx]
elif pre_name == 'L':
value = pre_avg_L[i_idx]
elif pre_name == 'M':
value = pre_avg_M[i_idx]
elif pre_name == 'Std':
value = pre_avg_Std[i_idx]
elif pre_name == 'C':
value = pre_avg_C[i_idx]
sql_comm = 'update %(table_name)s set pre_avg_%(pre_name)s%(i_idx)s = %(value)s \
where tradeID = %(idx)s'%{'table_name': table_name_clean, 'pre_name': pre_name, 'idx': idx, \
'i_idx': i_idx + 2, 'value': value}
database_pt.execute(sql_comm)
database.commit()
##--insert taylor-expansion based slope
pre_slope_C = get_talyer_slope(preC)
length_pre = min(4,idx-1)
for i_idx in xrange(length_pre):
value = pre_slope_C[i_idx]
sql_comm = 'update %(table_name)s set pre_slope_C%(i_idx)s = %(value)s \
where tradeID = %(idx)s'%{'table_name': table_name_clean, 'idx': idx, \
'i_idx': i_idx + 2, 'value': value}
database_pt.execute(sql_comm)
database.commit()