def rm_portf_underpf(limit_max):
"""
Remove underperforming strategy portfolio that are auto-generated.
Auto-generated portfolio strategy are used as example.
Args:
Int: Maximum number of example to keep.
Returns:
None
"""
total = 0
quant_to_rm = 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 COUNT(*) FROM instruments JOIN users ON instruments.owner = users.id'
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
total = row[0]
quant_to_rm = int(total) - int(limit_max)
if quant_to_rm > 0:
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = 'SELECT instruments.symbol, users.is_bot '+\
'FROM instruments JOIN users ON instruments.owner = users.id '+\
'WHERE users.is_bot=1 AND instruments.symbol LIKE "%'+\
get_portf_suffix() +'%" ORDER BY instruments.y1 '+\
'LIMIT '+ str(quant_to_rm)
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
symbol = row[0]
rm_portf_from('feed', 'symbol', symbol)
rm_portf_from('chart_data', 'symbol', symbol)
rm_portf_from('portfolios', 'portf_symbol', symbol)
rm_portf_from('instruments', 'symbol', symbol)
rm_portf_from('symbol_list', 'symbol', symbol)
cursor.close()
connection.close()
def __init__(self, symbol, uid, connection):
""" Select and initialize instrument data according to args """
self.symbol_selection = symbol
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT symbol from symbol_list WHERE uid=" + str(uid)
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
symbol_is_portf = row[0]
if symbol_is_portf.find(get_portf_suffix()) > -1:
self.sql_select = "SELECT price_close, date FROM chart_data "+\
"WHERE symbol='"+ self.symbol_selection +"' "
else:
self.sql_select = "SELECT price_close, date "+\
"FROM price_instruments_data WHERE symbol='"+ self.symbol_selection + "' "
self.sql_select_signal = "SELECT signal_price, date FROM chart_data "+\
"WHERE symbol='"+ self.symbol_selection +"' AND forecast = 0 "
sql = self.sql_select_signal + " ORDER BY Date DESC LIMIT 1"
debug(sql)
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
self.lp_signal = row[0]
sql = self.sql_select + " ORDER BY Date DESC LIMIT 1"
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
self.last_price = row[0]
self.last_date = row[1]
cursor.close()
self.uid = uid
self.d_1_year_perf = self.last_date - (timedelta(days=365))
self.d_6_month_perf = self.last_date - (timedelta(days=180))
self.d_3_month_perf = self.last_date - (timedelta(days=90))
self.d_1_month_perf = self.last_date - (timedelta(days=30))
self.d_1_week_perf = self.last_date - (timedelta(days=7))
self.d_1_day_perf = get_prev_session_date(self.symbol_selection)
self.d_1_week_forcast = 0
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 set_portf_feed():
"""
Import all the portfolio to table feed.
Args:
None
Returns:
None
"""
feed_id = 9
feed_type = "portfolios"
add_feed_type(feed_id, feed_type)
#Date [Today date]
date_today = datetime.datetime.now()
date_today = date_today.strftime("%Y%m%d")
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cr_i = connection.cursor(pymysql.cursors.SSCursor)
sql_i = "DELETE FROM feed WHERE type= "+ str(feed_id)
cr_i.execute(sql_i)
connection.commit()
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT instruments.symbol, instruments.fullname, instruments.asset_class, "+\
"instruments.market, instruments.w_forecast_change, "+\
"instruments.w_forecast_display_info, symbol_list.uid, instruments.owner, "+\
"instruments.romad_st, instruments.stdev_st, instruments.y1, instruments.m6, "+\
"instruments.m3, instruments.m1 "+\
"FROM instruments "+\
"JOIN symbol_list ON instruments.symbol = symbol_list.symbol "+\
"WHERE instruments.symbol LIKE '"+ get_portf_suffix() +"%'"
cursor.execute(sql)
res = cursor.fetchall()
i = 0
inserted_value = ''
for row in res:
symbol = row[0]
fullname = row[1].replace("'", "")
asset_class = row[2]
market = row[3]
w_forecast_display_info = row[5]
uid = row[6]
owner = row[7]
romad_st = row[8]
y1_performance = row[10]
m6_performance = row[11]
m3_performance = row[12]
m1_performance = row[13]
short_title = fullname
short_description = symbol
content = get_portf_content(owner)
url = "{burl}p/?uid="+str(uid)
ranking = str(get_portf_ranking(symbol, romad_st, y1_performance,
m6_performance, m3_performance, m1_performance))
feed_type = str(feed_id)
hash_this = get_hash_string(str(url))
badge = w_forecast_display_info
search = asset_class + market + symbol + " " + fullname
debug(search +": "+ os.path.basename(__file__))
if i == 0:
sep = ''
else:
sep = ','
inserted_value = inserted_value + sep +\
"('"+date_today+"','"+short_title+"','"+short_description+"','"+content+"','"+url+"',"+\
"'"+ranking+"','"+symbol+"','"+feed_type+"','"+badge+"',"+\
"'"+search+"','"+asset_class+"','"+market +"','"+hash_this+"'" +")"
i += 1
sql_i = "INSERT IGNORE INTO temp_feed"+\
"(date, short_title, short_description, content, url,"+\
" ranking, symbol, type, badge, "+\
"search, asset_class, market, hash) VALUES " + inserted_value
debug(sql_i)
cr_i.execute('''CREATE TEMPORARY TABLE temp_feed
SELECT * FROM feed
LIMIT 0;''')
cr_i.execute(sql_i)
connection.commit()
cr_i.execute('SELECT @i := 0')
cr_i.execute('UPDATE temp_feed SET globalRank = (SELECT @i := @i +1) ORDER BY ranking DESC')
connection.commit()
cr_i.execute('INSERT INTO feed SELECT * FROM temp_feed')
connection.commit()
cr_i.close()
gc.collect()
cursor.close()
connection.close()
def get_portf_perf():
"""
Description
Args:
None
Returns:
None
"""
portf_symbol_suffix = get_portf_suffix()
day_last_year = datetime.datetime.now() - timedelta(days=370)
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, instruments.fullname, "+\
"instruments.account_reference "+\
"FROM `symbol_list` INNER JOIN instruments ON symbol_list.symbol = instruments.symbol "+\
"WHERE symbol_list.symbol LIKE '"+portf_symbol_suffix+"%' ORDER BY symbol_list.symbol"
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
portf_symbol = row[0]
portf_uid = row[1]
account_reference = row[3]
i = 0
j = 370
date_last_year = day_last_year
inserted_value = ''
portf_nav = account_reference
cr_i = connection.cursor(pymysql.cursors.SSCursor)
sql_i = "DELETE FROM chart_data WHERE uid = " + str(portf_uid)
debug(sql_i)
cr_i.execute(sql_i)
connection.commit()
cr_i.close()
while i <= j:
date_last_year = date_last_year + timedelta(days=1)
date_last_year_str = date_last_year.strftime("%Y%m%d")
portf_pnl = 0
#get portfolio allocations
#for each item get the pnl
if date_last_year < datetime.datetime.now():
portf_pnl = get_portf_pnl(portf_symbol, date_last_year_str,
connection)
portf_nav = round(portf_nav + portf_pnl, 2)
if i > 0:
sep = ', '
else:
sep = ''
inserted_value = inserted_value + sep + "(" + str(portf_uid) +\
",'"+ str(portf_symbol) +"','" + str(date_last_year_str) + "'," +\
str(portf_nav) + ")"
i += 1
cr_i = connection.cursor(pymysql.cursors.SSCursor)
sql_i = "INSERT IGNORE INTO chart_data(uid, symbol, date, price_close) VALUES "+\
inserted_value
debug(sql_i)
cr_i.execute(sql_i)
connection.commit()
cr_i.close()
get_portf_perf_summ(portf_symbol, portf_uid, connection)
cursor.close()
connection.close()
def set_widgets_tradingview_chart(symbol, feed_id, connection):
"""
Create tradingview chart widget for each symbol as per args
Args:
String: Instrument symbol
Integer: Id of feed type to identfy as widget
Returns:
None
"""
date_today = datetime.datetime.now()
date_today = date_today.strftime("%Y%m%d")
disabled = True
cursor = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT instruments.symbol, instruments.fullname, "+\
"instruments.asset_class, instruments.market, sectors.sector, "+\
"symbol_list.uid, symbol_list.disabled FROM instruments "+\
"JOIN sectors ON instruments.sector = sectors.id JOIN symbol_list ON "+\
"instruments.symbol = symbol_list.symbol "+\
"WHERE instruments.symbol = '"+ symbol +"' AND instruments.symbol NOT LIKE '"+\
get_portf_suffix() +"%' "
cursor.execute(sql)
res = cursor.fetchall()
i = 0
inserted_values = ''
for row in res:
symbol = row[0]
fullname = row[1].replace("'", "")
asset_class = row[2]
market = row[3]
sector = row[4]
uid = row[5]
disabled = row[6]
short_title = fullname
short_description = symbol
content = sector
url = "{burl}w/?funcname=get_tradingview_chart(" + str(uid) + ",0,0,1)"
badge = ''
ranking = '-1'
feed_type = str(feed_id)
search = set_feed_function(
'GP', symbol,
'label') + fullname + ' - Interactive Chart / Historical Graphs'
sa_function = set_feed_function('GP', symbol, 'value')
hash_this = get_hash_string(str(url))
debug(search + ": " + os.path.basename(__file__))
cr_i = connection.cursor(pymysql.cursors.SSCursor)
sql_i = "DELETE FROM feed WHERE (symbol ='"+symbol+"' AND date<='"+\
date_today+"' AND type="+ feed_type +")"
cr_i.execute(sql_i)
connection.commit()
if i == 0:
sep = ''
else:
sep = ','
inserted_values = inserted_values + sep +\
"('"+date_today+"','"+short_title+"','"+short_description+"','"+content+"','"+url+"',"+\
"'"+ranking+"','"+symbol+"','"+feed_type+"','"+badge+"',"+\
"'"+search+"','"+asset_class+"','"+market+"','"+sa_function+"','"+hash_this+"'"+")"
cr_i.close()
cursor.close()
cr_i = connection.cursor(pymysql.cursors.SSCursor)
sql_i = "INSERT IGNORE INTO feed"+\
"(date, short_title, short_description, content, url,"+\
" ranking, symbol, type, badge, "+\
"search, asset_class, market, sa_function, hash) VALUES " + inserted_values
if not disabled:
cr_i.execute(sql_i)
connection.commit()
cr_i.close()
def get_portf_alloc():
"""
Description
Args:
None
Returns:
None
"""
portf_symbol_suffix = get_portf_suffix()
connection = pymysql.connect(host=db_srv,
user=db_usr,
password=db_pwd,
db=db_name,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cr = connection.cursor(pymysql.cursors.SSCursor)
sql = "SELECT instruments.symbol, instruments.fullname, symbol_list.uid, instruments.unit, instruments.market FROM instruments "+\
"INNER JOIN symbol_list ON instruments.symbol = symbol_list.symbol "+\
"WHERE instruments.symbol LIKE '"+portf_symbol_suffix+"%' ORDER BY instruments.symbol"
cr.execute(sql)
rs = cr.fetchall()
for row in rs:
portf_symbol = row[0]
portf_market = row[4]
portf_currency = get_market_currency(portf_market)
portf_forc_return = 0
portf_perc_return = 0
portf_nav = 0
alloc_forc_pnl = 0
portfd = portf_data(portf_symbol)
cr_pf = connection.cursor(pymysql.cursors.SSCursor)
sql_pf = "SELECT symbol, quantity, strategy_conviction FROM portfolios WHERE portf_symbol ='"+ portf_symbol +"' ORDER BY portf_symbol"
cr_pf.execute(sql_pf)
rs_pf = cr_pf.fetchall()
for row in rs_pf:
debug(sql_pf+": "+ os.path.basename(__file__) )
portf_item_symbol = row[0]
portf_initial_item_quantity = row[1]
portf_item_conviction = row[2]
portf_item_conviction_coef = get_conviction_coef(portf_item_conviction)
if portf_initial_item_quantity == 1:
portf_item_quantity = portfd.get_quantity(portf_item_symbol,portf_item_conviction_coef)
else:
portf_item_quantity = portf_initial_item_quantity
cr_p = connection.cursor(pymysql.cursors.SSCursor)
sql_p = "SELECT price_close, date FROM price_instruments_data WHERE symbol ='"+portf_item_symbol+"' ORDER BY date DESC LIMIT 1"
cr_p.execute(sql_p)
rs_p = cr_p.fetchall()
debug(sql_p+": "+ os.path.basename(__file__) )
for row in rs_p:
alloc_price = row[0]
alloc_date = row[1]
alloc_expiration = alloc_date + timedelta(days=7)
alloc_expiration = alloc_expiration.strftime("%Y%m%d")
cr_p.close()
cr_t = connection.cursor(pymysql.cursors.SSCursor)
sql_t = "SELECT instruments.symbol, instruments.fullname, instruments.decimal_places, "+\
"instruments.w_forecast_change, instruments.pip, symbol_list.uid, instruments.market FROM instruments "+\
"INNER JOIN symbol_list ON instruments.symbol = symbol_list.symbol WHERE instruments.symbol ='"+portf_item_symbol+"'"
cr_t.execute(sql_t)
rs_t = cr_t.fetchall()
debug(sql_t+": "+ os.path.basename(__file__) )
for row in rs_t:
alloc_symbol = row[0]
alloc_fullname = row[1]
alloc_decimal_places = row[2]
alloc_w_forecast_change = row[3]
alloc_pip = row[4]
alloc_uid = row[5]
alloc_market = row[6]
if alloc_w_forecast_change >= 0:
alloc_entry_level_sign = '<'
alloc_order_type = 'buy'
else:
alloc_entry_level_sign = '>'
alloc_order_type = 'sell'
if portf_currency != 'USD':
alloc_conv_rate = 1
else:
alloc_conv_rate = get_market_conv_rate(alloc_market)
alloc_dollar_amount = round( portf_item_quantity * alloc_price * alloc_conv_rate, int(alloc_decimal_places) ) * alloc_pip
portf_item_quantity = round(portf_item_quantity / alloc_conv_rate,2)
if portf_item_quantity < 0.01: portf_item_quantity = 0.01
entry_level = alloc_entry_level_sign + ' ' + str( round( float(alloc_price), alloc_decimal_places) )
debug(portf_symbol +": " + alloc_symbol )
cr_x = connection.cursor(pymysql.cursors.SSCursor)
sql_x = 'UPDATE portfolios SET quantity='+ str(portf_item_quantity) +', alloc_fullname="'+ alloc_fullname +'", order_type="' + alloc_order_type + '", '+\
'dollar_amount='+ str(alloc_dollar_amount) +', entry_level="'+ entry_level +'", expiration='+ alloc_expiration +' '+\
'WHERE symbol ="'+ alloc_symbol+'" AND portf_symbol ="' + portf_symbol + '" '
debug(sql_x)
cr_x.execute(sql_x)
connection.commit()
cr_x.close()
alloc_forc_data = ForecastData(alloc_uid, connection)
alloc_forc_pnl = abs( (alloc_price - float(alloc_forc_data.get_frc_pt() )) * portf_item_quantity * alloc_pip )
portf_forc_return = portf_forc_return + alloc_forc_pnl
portf_nav = portf_nav + alloc_dollar_amount
cr_t.close()
cr_pf.close()
### Updatedb
try:
portf_perc_return = (100/(portf_nav/portf_forc_return))/100
except:
portf_perc_return = 0
w_forecast_display_info = str(round(portf_perc_return,2))+ "%"
cr_f = connection.cursor(pymysql.cursors.SSCursor)
sql_f = "UPDATE instruments SET w_forecast_change=" + str(portf_perc_return) + ", w_forecast_display_info='" + w_forecast_display_info + "' " +\
"WHERE symbol='"+portf_symbol+"' "
cr_f.execute(sql_f)
connection.commit()
cr_f.close()
cr.close()
connection.close()