def check_instr_is_obsolete(symbol, connection):
"""
Check if a particular symbol did not get price update for more than 7 days.
If yes, the symbol will be logged.
Args:
String: Instrument symbol
Returns:
None
"""
k = 7
module = '{symbol} = No data collected since more than 7 days.'
status = 1
date_range = datetime.datetime.now() - timedelta(days=k)
date_range = date_range.strftime("%Y%m%d")
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = 'SELECT COUNT(*) FROM price_instruments_data WHERE symbol = "'+\
symbol + '" AND date >=' + date_range
cursor.execute(sql)
res = cursor.fetchall()
count_record = 0
for row in res:
count_record = row[0]
if count_record == 0:
log_this(module.replace('{symbol}', symbol), status)
cursor.close()
def insert_db_price_data():
"""
Collect and import instruments price data from Oanda into the database
Args:
None
Returns:
None
"""
log_this('1. oanda_insert_db_price_data', 0)
csvdir = SETT.get_path_r_oanda_src()
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT symbol, uid FROM symbol_list WHERE oanda<>'' "
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
symbol = row[0]
uid = row[1]
file_str = csvdir + str(uid) + '.csv'
filepath = Path(file_str)
if filepath.exists():
with open(file_str) as csvfile:
csv_file = csv.reader(csvfile, delimiter=',')
i = 0
inserted_values = ''
for row in csv_file:
time.sleep(0.2)
price_date = row[0]
price_date = price_date.replace('.', '-')
price_date = price_date.replace('X', '')
price_date = price_date.replace('-', '')
price_date = '%.8s' % price_date
price_close = row[1]
if price_close != "NA" and i > 0:
if i == 1:
sep = ''
else:
sep = ','
inserted_values = inserted_values + sep +\
"('"+symbol+"',"+price_date+","+price_close+")"
debug(symbol + ": " + os.path.basename(__file__) +
" - " + inserted_values)
i += 1
cr_q_ins = connection.cursor(pymysql.cursors.SSCursor)
sql_q_ins = "INSERT IGNORE INTO price_instruments_data "+\
"(symbol, date, price_close) VALUES " + inserted_values
cr_q_ins.execute(sql_q_ins)
connection.commit()
cr_q_ins.close()
cursor.close()
connection.close()
log_this('1. oanda_insert_db_price_data', 1)
def check_price_inconsist_price_move(symbol, connection):
"""
Check if a particular symbol experienced an unexpected price movement
greater than 40% due to error or possible stock split.
In that case add symbol to log.
Args:
String: Instrument symbol
Returns:
None
"""
k = 7
module = '{symbol} = Price inconsistent or stock split'
status = 1
date_range = datetime.datetime.now() - timedelta(days=k)
date_range = date_range.strftime("%Y%m%d")
cr_c = connection.cursor(pymysql.cursors.SSCursor)
sql_c = "SELECT AVG(price_close) FROM price_instruments_data WHERE symbol = '"+\
symbol + "' AND date >=" + date_range
cr_c.execute(sql_c)
res_c = cr_c.fetchall()
average_price = 0
for row in res_c:
average_price = row[0]
sql_c = "SELECT price_close FROM price_instruments_data WHERE symbol = '"+\
symbol +"' ORDER BY date DESC LIMIT 1"
cr_c.execute(sql_c)
res_c = cr_c.fetchall()
last_price = 0
for row in res_c:
last_price = row[0]
cr_c.close()
debug(str(average_price) + " ::: " + str(last_price))
if average_price is not None:
if abs(get_pct_change(average_price, last_price)) >= 0.4:
log_this(module.replace('{symbol}', str(symbol)), status)
else:
log_this(module.replace('{symbol}', str(symbol)), status)
def output_prediction(force_full_update, uid, order):
"""
Main function to get prediction calculated and update tables in database
Args:
Boolean: if True, update tables regardless of flag is_ta_calc
Integer: Instrument unique id
String: order of update desc, asc. by symbol.
Returns:
None
"""
log_this('2. output_prediction', 0)
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
if uid == 0:
sql = "SELECT uid FROM symbol_list WHERE symbol NOT LIKE '%"+\
get_portf_suffix() +"%' AND disabled=0 ORDER BY symbol "+ order
else:
sql = "SELECT uid FROM symbol_list WHERE uid = " + str(uid)
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
uid = row[0]
set_all_prediction_model_target_price_n_score(uid, force_full_update,
connection)
cursor.close()
connection.close()
gc.collect()
log_this('2. output_prediction', 1)
def get_update_instr_data(extended_scan, is_update_all, specific_symbol):
"""
Main function to update all data
Args:
Integer: if 1 then update up to number of day
specified in variable extended_scanelse up to 10 days.
Boolean: if 1 then update all data regardless flag is_ta_calc
String: if not '' then update specific instrument (symbol)
Returns:
None
"""
log_this('3. get_update_instr_data', 0)
if extended_scan == 1:
nd_scan = 370
else:
nd_scan = 10
if specific_symbol == '':
sql_parse_list = "SELECT symbol_list.symbol, symbol_list.uid, instruments.asset_class "+\
"FROM symbol_list JOIN instruments ON symbol_list.symbol = instruments.symbol "+\
"WHERE symbol_list.symbol NOT LIKE '"+get_portf_suffix()+\
"%' AND symbol_list.disabled = 0 ORDER BY symbol"
else:
sql_parse_list = "SELECT symbol_list.symbol, symbol_list.uid, instruments.asset_class "+\
"FROM symbol_list JOIN instruments ON symbol_list.symbol = instruments.symbol "+\
"WHERE symbol_list.symbol = '"+ str(specific_symbol) +"' AND symbol_list.disabled = 0"
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
clear_chart_table(specific_symbol, connection)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = sql_parse_list
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
symbol = row[0]
uid = row[1]
asset_class = row[2]
debug(
str(uid) + ' - ' + str(symbol) + '------------------------------')
date_minus_ten = datetime.datetime.now() - timedelta(days=10)
date_minus_ten = date_minus_ten.strftime("%Y%m%d")
date_minus_seven = datetime.datetime.now() - timedelta(days=7)
date_minus_seven = date_minus_seven.strftime("%Y%m%d")
date_num_day_scan = datetime.datetime.now() - timedelta(days=nd_scan)
date_num_day_scan = date_num_day_scan.strftime("%Y%m%d")
sentiment = update_instruments_data(symbol, is_update_all,
date_num_day_scan,
date_minus_seven, connection)
if is_update_all:
get_trades(symbol, uid, nd_scan, True, connection)
else:
get_trades(symbol, uid, nd_scan, False, connection)
""" These functions needs to run before all else """
get_trend_line_data(symbol, uid, connection)
gen_recomm(symbol, uid, connection)
gen_chart(symbol, uid, connection)
""" ******************************************** """
get_forecast_pnl(symbol, nd_scan, is_update_all, connection)
get_instr_sum(symbol, uid, asset_class, date_minus_ten, sentiment,
connection)
set_signals_feed(symbol, connection)
set_widgets_feed(symbol, connection)
check_instr_is_obsolete(symbol, connection)
check_price_inconsist_price_move(symbol, connection)
gc.collect()
cursor.close()
connection.close()
log_this('3. get_update_instr_data', 1)
def collect_crypto_data():
"""
Collect and import crypto price data into the database
Args:
None
Returns:
None
"""
log_this('1. collect_crypto_data', 0)
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT symbol_list.symbol, symbol_list.uid, symbol_list.fsym, symbol_list.tsym "+\
"FROM symbol_list INNER JOIN instruments ON "+\
"symbol_list.symbol = instruments.symbol WHERE symbol_list.symbol "+\
"NOT LIKE '%"+ get_portf_suffix() +"%' AND instruments.asset_class = 'CR:' AND disabled =0"
cursor.execute(sql)
res = cursor.fetchall()
i = 1
for row in res:
symbol = row[0]
fsym = row[2]
tsym = row[3]
debug(symbol+": "+ os.path.basename(__file__))
### Cryptocompare API ######################################################
url = "https://min-api.cryptocompare.com/data/histoday?"+\
"fsym="+fsym+"&tsym="+tsym+"&limit=30&aggregate=1&e=CCCAGG"
############################################################################
request = urllib.request.Request(url)
opener = urllib.request.build_opener()
fil = opener.open(request)
j = json.loads(fil.read())
k = len(j['Data'])
i = 1
inserted_values = ''
cr_i = connection.cursor(pymysql.cursors.SSCursor)
while i < k:
price_close = j['Data'][i]['close']
date = j['Data'][i]['time']
date_today = datetime.utcfromtimestamp(int(date))
if i == 1:
sep = ''
else:
sep = ','
inserted_values = inserted_values + sep +\
"('"+symbol+"','"+date_today.strftime('%Y%m%d')+"','"+str(price_close)+"')"
debug(inserted_values)
i += 1
sql_i = "INSERT IGNORE INTO price_instruments_data(symbol, date, price_close) VALUES " +\
inserted_values
cr_i.execute(sql_i)
connection.commit()
cr_i.close()
cursor.close()
log_this('1. collect_crypto_data', 1)
def insert_db_price_data_asc():
"""
Insert collected price data from Quantmod csv into database in
ascending order.
Args:
None
Returns:
None
"""
log_this('1. quantmod_insert_db_price_data', 0)
csvdir = SETT.get_path_r_quantmod_src()
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cr_cnt = connection.cursor(pymysql.cursors.SSCursor)
sql_cnt = "SELECT COUNT(*) FROM symbol_list"
cr_cnt.execute(sql_cnt)
rs_cnt = cr_cnt.fetchall()
for row in rs_cnt:
j = int(row[0])
cr_cnt.close()
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT symbol, uid FROM symbol_list ORDER BY symbol ASC"
cursor.execute(sql)
res = cursor.fetchall()
k = 1
for row in res:
if k <= j:
uid = row[1]
symbol = row[0]
file_str = csvdir + str(uid) + '.csv'
debug(
str(uid) + ' - ' + str(symbol) +
'------------------------------')
filepath = Path(file_str)
if filepath.exists():
with open(file_str) as csvfile:
csv_file = csv.reader(csvfile, delimiter=',')
i = 1
inserted_values = ''
for row in csv_file:
time.sleep(0.2)
price_date = row[0]
price_date = price_date.replace('.', '-')
price_date = price_date.replace('X', '')
price_date = price_date.replace('-', '')
price_date = '%.8s' % price_date
price_close = row[4]
if price_close != "open" and price_close != "NA" and i > 1:
if i == 2:
sep = ''
else:
sep = ','
inserted_values = inserted_values + sep +\
"('"+symbol+"',"+price_date+","+price_close+")"
debug(symbol +": ("+str(i)+"/"+str(j)+"): "+price_date+": "+\
os.path.basename(__file__) + " - " + inserted_values)
i += 1
cr_q_ins = connection.cursor(pymysql.cursors.SSCursor)
sql_q_ins = "INSERT IGNORE INTO price_instruments_data "+\
"(symbol, date, price_close) VALUES " + inserted_values
cr_q_ins.execute(sql_q_ins)
connection.commit()
gc.collect()
cr_q_ins.close()
k += 1
else:
break
cursor.close()
connection.close()
log_this('1. quantmod_insert_db_price_data', 1)
""" Collect and compute strategy portfolio data """
# Copyright (c) 2018-present, Taatu Ltd.
#
# This source code is licensed under the MIT license found in the
# LICENSE file in the root directory of this source tree.
import sys
import os
PDIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.append(os.path.abspath(PDIR))
from settings import SmartAlphaPath
SETT = SmartAlphaPath()
sys.path.append(os.path.abspath(SETT.get_path_core()))
from sa_logging import log_this
sys.path.append(os.path.abspath(SETT.get_path_feed()))
from get_portf_alloc import get_portf_alloc
from get_portf_perf import get_portf_perf
from set_portf_feed import set_portf_feed
from rm_portf_underpf import rm_portf_underpf
log_this('4. portf_main_get_data', 0)
rm_portf_underpf(250)
get_portf_alloc()
get_portf_perf()
set_portf_feed()
log_this('4. portf_main_get_data', 0)