def set_portf_fullname(symbol, asset_class, market, strategy_type):
""" xxx """
#portf: symbol, asset_class_name, market_label, strategy_type
return_data = ''
fullname = symbol.replace(get_portf_suffix(), '')
return_data = fullname + ' ' + market + ' ' + asset_class + ' ' + strategy_type
return return_data
def portf_gen_portf_t_instruments():
""" xxx """
return_data = ''
portf_symbol = get_portf_suffix() + set_portf_symbol()
portf_asset_class_id = get_portf_asset_class('i')
portf_asset_class_name = get_portf_asset_class('n')
portf_market_id = get_portf_market('i')
portf_market_name = get_portf_market('n')
portf_strategy_type = get_portf_strategy_type()
portf_fullname = set_portf_fullname(portf_symbol, portf_asset_class_name,
portf_market_name, portf_strategy_type)
portf_owner = portf_get_user_numeric_id()
portf_description = get_portf_description(portf_asset_class_name,
portf_market_name,
portf_strategy_type)
portf_account_reference = 1000
portf_decimal_place = get_portf_decimal_place()
portf_pip = 1
portf_sector = 0
portf_unit = get_portf_market('cur')
portf_creation_date = set_portf_date()
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 = "INSERT INTO symbol_list(symbol) VALUES('" + str(portf_symbol) + "')"
cursor.execute(sql)
connection.commit()
sql = "INSERT INTO instruments(symbol,fullname,description,"+\
"asset_class,market,decimal_places,pip, sector,unit,"+\
"account_reference,owner,creation_date) "+\
"VALUES ('"+\
str(portf_symbol) +"','"+\
str(portf_fullname) +"','"+\
str(portf_description) +"','"+\
str(portf_asset_class_id) +"','"+\
str(portf_market_id) +"','"+\
str(portf_decimal_place) +"','"+\
str(portf_pip) +"','"+\
str(portf_sector) +"','"+\
str(portf_unit) +"','"+\
str(portf_account_reference) +"','"+\
str(portf_owner) +"','"+\
str(portf_creation_date) +"')"
cursor.execute(sql)
connection.commit()
cursor.close()
connection.close()
return_data = portf_symbol
return return_data
def __init__(self, symbol, uid):
""" xxx """
self.s = symbol
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 symbol from symbol_list WHERE uid=" + str(uid)
cr.execute(sql)
rs = cr.fetchall()
for row in rs:
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.s + "' "
else:
self.sql_select = "SELECT price_close, date FROM price_instruments_data WHERE symbol='" + self.s + "' "
self.sql_select_signal = "SELECT signal_price, date from chart_data WHERE symbol='" + self.s + "' AND forecast = 0 "
sql = self.sql_select_signal + " ORDER BY Date DESC LIMIT 1"
cr.execute(sql)
rs = cr.fetchall()
for row in rs:
self.lp_signal = row[0]
sql = self.sql_select + " ORDER BY Date DESC LIMIT 1"
cr.execute(sql)
rs = cr.fetchall()
for row in rs:
self.lp = row[0]
self.ld = row[1]
cr.close()
connection.close()
self.uid = uid
self.d_1Yp = self.ld - (timedelta(days=365))
self.d_6Mp = self.ld - (timedelta(days=180))
self.d_3Mp = self.ld - (timedelta(days=90))
self.d_1Mp = self.ld - (timedelta(days=30))
self.d_1Wp = self.ld - (timedelta(days=7))
self.d_1Wf = 0
def get_trades_tbl(uid, what, burl, type_trade):
""" xxx """
return_data = ''
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)
selected_symbol = ''
selected_is_portf = False
is_user_prf = False
list_limit = 50
sql = "SELECT symbol FROM symbol_list WHERE uid=" + str(uid)
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
selected_symbol = row[0]
if selected_symbol.find(get_portf_suffix()) != -1:
selected_is_portf = True
if uid == '0' or uid == 0:
is_user_prf = True
portf_symbol_selection = ''
i = 0
if selected_is_portf:
sql = "SELECT portfolios.symbol, portfolios.portf_symbol "+\
"FROM symbol_list JOIN portfolios ON symbol_list.symbol = portfolios.portf_symbol "+\
"WHERE symbol_list.uid = "+ str(uid)
cursor.execute(sql)
res = cursor.fetchall()
portf_symbol = ''
for row in res:
portf_symbol = row[1]
if i == 0:
portf_symbol_selection = portf_symbol_selection +\
" AND (trades.symbol = '"+ str(row[0]) +"' "
else:
portf_symbol_selection = portf_symbol_selection +\
" OR trades.symbol = '"+ str(row[0]) +"' "
i += 1
portf_symbol_selection = portf_symbol_selection +\
') AND portfolios.portf_symbol ="'+ str(portf_symbol) +'" '
single_selection = ''
if not selected_is_portf and not is_user_prf:
single_selection = 'AND trades.uid = ' + str(uid)
date_now = datetime.datetime.now()
dnstr = date_now.strftime("%Y%m%d")
date_now = date_now.strftime("%d-%b-%Y")
if selected_is_portf:
sql = "SELECT trades.order_type, "+\
"trades.symbol, "+\
"trades.entry_date, "+\
"trades.entry_price, "+\
"trades.close_price, "+\
"trades.expiration_date, "+\
"trades.pnl_pct, "+\
"trades.url, "+\
"instruments.unit, "+\
"portfolios.strategy_order_type, "+\
"trades.uid "
sql = sql +\
"FROM trades JOIN portfolios ON portfolios.symbol = trades.symbol "+\
"JOIN instruments ON trades.symbol = instruments.symbol WHERE trades.entry_date <=" +\
dnstr + " AND "
elif is_user_prf and what != 'today':
sql = "SELECT trades.order_type, "+\
"trades.symbol, "+\
"trades.entry_date, "+\
"trades.entry_price, "+\
"trades.close_price, "+\
"trades.expiration_date, "+\
"trades.pnl_pct, "+\
"trades.url, "+\
"a_alloc.unit, "+\
"trades.status, "+\
"trades.uid "+\
"FROM instruments "+\
"JOIN portfolios ON instruments.symbol = portfolios.portf_symbol "+\
"JOIN trades ON trades.symbol = portfolios.symbol "+\
"JOIN instruments as a_alloc ON a_alloc.symbol = trades.symbol " +\
"WHERE instruments.owner = " + str(get_user_numeric_id()) + " "+\
"AND ((portfolios.strategy_order_type = 'long' AND trades.order_type = 'buy') "+\
"OR (portfolios.strategy_order_type = 'short' AND trades.order_type = 'sell') "+\
"OR (portfolios.strategy_order_type = 'long/short') ) AND trades.entry_date <=" +\
dnstr + " AND "
elif what == 'today':
type_status_filter = "" +\
"((trades.entry_date = " +\
dnstr + " AND instruments.owner = " +\
str(get_user_numeric_id()) + " AND status = 'active') OR "+\
"(trades.expiration_date <= " +\
dnstr + " AND instruments.owner = "+\
str(get_user_numeric_id()) +" AND status = 'active') OR "+\
"(trades.expiration_date = " +\
dnstr + " AND instruments.owner = "+\
str(get_user_numeric_id()) +" AND status = 'expired')) "
if type_trade == 'expired':
type_status_filter = "((trades.expiration_date <= " +\
dnstr + " AND instruments.owner = "+\
str(get_user_numeric_id()) +" AND status = 'active') OR "+\
"(trades.expiration_date = " +\
dnstr + " AND instruments.owner = "+\
str(get_user_numeric_id()) +" AND status = 'expired')) "
sql = "SELECT "+\
"trades.order_type, "+\
"trades.symbol, "+\
"trades.entry_date, "+\
"trades.entry_price, "+\
"trades.close_price, "+\
"trades.expiration_date, "+\
"trades.pnl_pct, "+\
"trades.url, "+\
"a_alloc.unit, "+\
"trades.status, "+\
"trades.uid "+\
"FROM trades "+\
"JOIN portfolios ON portfolios.symbol = trades.symbol "+\
"JOIN instruments ON instruments.symbol = portfolios.portf_symbol "+\
"JOIN instruments as a_alloc ON a_alloc.symbol = portfolios.symbol "+\
"WHERE "+\
"((portfolios.strategy_order_type = 'long' AND trades.order_type = 'buy') "+\
"OR (portfolios.strategy_order_type = 'short' AND trades.order_type = 'sell') "+\
"OR (portfolios.strategy_order_type = 'long/short') ) AND "+\
type_status_filter
else:
sql = "SELECT trades.order_type, "+\
"trades.symbol, "+\
"trades.entry_date, "+\
"trades.entry_price, "+\
"trades.close_price, "+\
"trades.expiration_date, "+\
"trades.pnl_pct, "+\
"trades.url, "+\
"instruments.unit, "+\
"trades.status, "+\
"trades.uid "
sql = sql +\
"FROM trades JOIN instruments ON trades.symbol = instruments.symbol "+\
"WHERE trades.entry_date <=" + dnstr + " AND "
if what == 'active':
sql = sql + " trades.status = 'active' "
if what == 'expired':
sql = sql + " trades.status = 'expired' "
sql = sql + single_selection
sql = sql + portf_symbol_selection
sql = sql + ' order by trades.entry_date DESC'
print(sql)
cursor.execute(sql)
res = cursor.fetchall()
l_order = 'Order'
l_instrument = 'Instrument'
l_entry_date = 'Entry date'
l_open_price = 'Open price'
l_close_price = 'Close price'
l_expiration_date = 'Expires on'
l_pnl = 'PnL'
return_data = ''+\
'<table class="table table-hover table-sm sa-table-sm">'+\
' <thead>'+\
' <tr>'+\
' <th scope="col">'+ l_order +'</th>'+\
' <th scope="col">'+ l_instrument +'</th>'+\
' <th scope="col">'+ l_entry_date +'</th>'+\
' <th scope="col">'+ l_open_price +'</th>'
if what == 'expired':
return_data = return_data +\
'<th scope="col">'+ l_close_price +'</th>'
if what != 'today':
return_data = return_data +\
' <th scope="col">'+ l_expiration_date +'</th>'+\
' <th scope="col">'+ l_pnl +'</th>'
return_data = return_data+\
' </tr>'+\
' </thead>'+\
' <tbody>'
i = 0
for row in res:
order_type = row[0]
symbol = row[1]
entry_date = row[2].strftime("%d-%b-%Y")
entry_price = row[3]
close_price = row[4]
expiration_date = row[5].strftime("%d-%b-%Y")
expiration_date_str = str(row[5].strftime("%Y%m%d"))
pnl_pct = row[6]
unit = row[8]
alloc_uid = row[10]
if selected_is_portf:
strategy_order_type = row[9]
if date_now == entry_date:
badge_today = ' <span class="badge badge-primary">today</span>'
elif int(dnstr) >= int(expiration_date_str) and\
(what == 'active' or what == 'today') and (close_price == -1):
badge_today = ' <span class="badge badge-secondary">close @market</span>'
elif int(dnstr) == int(expiration_date_str) and\
(what == 'expired' or what == 'today'):
badge_today = ' <span class="badge badge-warning">closed</span>'
else:
badge_today = ''
if order_type == 'buy':
badge_class = 'badge badge-success'
else:
badge_class = 'badge badge-danger'
if pnl_pct >= 0:
text_class = 'text text-success'
else:
text_class = 'text text-danger'
if unit == 'pips':
pnl_pct = round(pnl_pct * 10000, 2)
if pnl_pct > 1:
pnl_pct = str(pnl_pct) + " pips"
else:
pnl_pct = str(pnl_pct) + " pips"
else:
pnl_pct = str(round(pnl_pct * 100, 2)) + "%"
if selected_is_portf:
if (order_type == 'buy' and strategy_order_type == 'long') or\
(order_type == 'sell' and strategy_order_type == 'short') or\
(strategy_order_type == 'long/short'):
return_data = return_data +\
' <tr>'+\
' <td>'+ place_trade_link(symbol, '<span class="'+\
badge_class +'">' + str(order_type) +'</span>') +\
badge_today +'</td>'+\
' <td><a href="'+\
burl + 's/?uid='+\
str(alloc_uid) +'">'+\
str(symbol) +'</a></td>'+\
' <td>'+ str(entry_date) +'</td>'+\
' <td>'+ str(entry_price) +'</td>'
if what == 'expired':
return_data = return_data +\
'<td>'+ str(close_price) +'</td>'
return_data = return_data +\
' <td>'+ str(expiration_date) +'</td>'+\
' <td><span class="'+ text_class +'">'+ str(pnl_pct) +'</span></td>'
' </tr>'
i += 1
else:
return_data = return_data +\
' <tr>'+\
' <td>'+ place_trade_link(symbol, '<span class="'+\
badge_class +'">'+ str(order_type) +'</span>') +\
badge_today +'</td>'+\
' <td><a href="'+\
burl + 's/?uid='+\
str(alloc_uid) +'">'+\
str(symbol) +'</a></td>'+\
' <td>'+ str(entry_date) +'</td>'+\
' <td>'+ str(entry_price) +'</td>'
if what == 'expired':
return_data = return_data +\
'<td>'+ str(close_price) +'</td>'
if what != 'today':
return_data = return_data +\
' <td>'+ str(expiration_date) +'</td>'+\
' <td><span class="'+ text_class +'">'+ str(pnl_pct) +'</span></td>'
return_data = return_data +\
' </tr>'
i += 1
if i == list_limit:
break
return_data = return_data +\
' </tbody>'+\
'</table>'
cursor.close()
connection.close()
return return_data
def get_trailing_returns(uid):
""" Get trailing return chart """
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 instruments.fullname, instruments.is_benchmark, "+\
"instruments.market, instruments.symbol, instruments.asset_class "+\
"FROM instruments JOIN symbol_list ON symbol_list.symbol = instruments.symbol "+\
"WHERE symbol_list.uid=" + str(uid)
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
fullname = row[0].replace("'", "")
is_benchmark = row[1]
market = row[2]
symbol_is_portf = row[3]
asset_class = row[4]
if symbol_is_portf.find(get_portf_suffix()) > -1:
sql = "SELECT date FROM chart_data WHERE uid=" + str(uid) + " ORDER BY date DESC LIMIT 1"
else:
sql = "SELECT price_instruments_data.date FROM price_instruments_data JOIN symbol_list "+\
"ON symbol_list.symbol = price_instruments_data.symbol "+\
"WHERE symbol_list.uid=" + str(uid) +" ORDER BY date DESC LIMIT 1"
cursor.execute(sql)
res = cursor.fetchall()
as_date = ''
l_as_date = ''
for row in res:
as_date = row[0]
if as_date != '':
l_as_date = 'Trailing returns as of '+ as_date.strftime("%d-%b-%Y")
font_size = 10
l_y1 = '1-Year'
l_m6 = '6-month'
l_m3 = '3-month'
l_m1 = '1-month'
l_w1 = '1-week'
minb = 0
mini = 0
maxb = 0
maxi = 0
benchmark_header = ''
benchmark_data_y1 = ''
benchmark_data_m6 = ''
benchmark_data_m3 = ''
benchmark_data_m1 = ''
benchmark_data_w1 = ''
if not is_benchmark:
sql = "SELECT symbol_list.uid, instruments.fullname "+\
"FROM symbol_list JOIN instruments "+\
"ON symbol_list.symbol = instruments.symbol "+\
"WHERE instruments.market='"+\
str(market) +"' AND instruments.asset_class='"+\
str(asset_class) +"' AND instruments.is_benchmark=1"
cursor.execute(sql)
res = cursor.fetchall()
benchmark_uid = 0
for row in res:
benchmark_uid = row[0]
benchmark_fullname = row[1].replace("'", "")
if benchmark_uid != 0:
benchmark_header = ", ' " +\
benchmark_fullname +\
" ', {type: 'string', role: 'annotation'}"
benchmark_data_y1 = ','+ get_chart_data(benchmark_uid, 'y1')
benchmark_data_m6 = ','+ get_chart_data(benchmark_uid, 'm6')
benchmark_data_m3 = ','+ get_chart_data(benchmark_uid, 'm3')
benchmark_data_m1 = ','+ get_chart_data(benchmark_uid, 'm1')
benchmark_data_w1 = ','+ get_chart_data(benchmark_uid, 'w1')
minb = get_minmax(benchmark_uid, 'min')
maxb = get_minmax(benchmark_uid, 'max')
data = ''+\
'["'+ l_y1 + '",' + get_chart_data(uid, 'y1') + benchmark_data_y1 +']' + ',' +\
'["'+ l_m6 + '",' + get_chart_data(uid, 'm6') + benchmark_data_m6 + ']' + ',' +\
'["'+ l_m3 + '",' + get_chart_data(uid, 'm3') + benchmark_data_m3 + ']' + ',' +\
'["'+ l_m1 + '",' + get_chart_data(uid, 'm1') + benchmark_data_m1 + ']' + ',' +\
'["'+ l_w1 + '",' + get_chart_data(uid, 'w1') + benchmark_data_w1 + ']'
mini = get_minmax(uid, 'min')
maxi = get_minmax(uid, 'max')
if minb < mini:
mini = minb
if maxb > maxi:
maxi = maxb
header = " ['x', ' " +\
fullname + " ', {type: 'string', role: 'annotation'}"+\
benchmark_header +" ],"
chart_content = "" +\
"<script>" +\
"google.charts.load('current', {packages: ['corechart', 'bar']});" +\
"google.charts.setOnLoadCallback(drawAnnotations);" +\
"function drawAnnotations() {" +\
" var data = google.visualization.arrayToDataTable([" +\
header +\
data +\
" ]);" +\
" var options = {" +\
" fontSize: "+ str(font_size) + "," +\
" legend: {position:'top', textStyle: {color:"+\
theme_return_this("'black'", "'white'") +"} }," +\
" title: ''," +\
" backgroundColor: 'transparent',"+\
" chartArea: {width: '50%'}," +\
" annotations: {" +\
" alwaysOutside: true," +\
" textStyle: {" +\
" auraColor: 'none'," +\
" color: '#555'" +\
" }," +\
" boxStyle: {" +\
" stroke: '#ccc'," +\
" strokeWidth: 1," +\
" gradient: {" +\
" color1: 'yellow'," +\
" color2: 'white'," +\
" x1: '0%', y1: '0%'," +\
" x2: '100%', y2: '100%'" +\
" }" +\
" }" +\
" }," +\
" series: {0:{color: "+\
theme_return_this("'blue'", "'orange'") +"}, 1:{color: '#c9d6ea'} }," +\
" chartArea: {width:'80%',height:'80%'}," +\
" hAxis: {" +\
" title: '" + l_as_date + "', " +\
" titleTextStyle:{ color:"+\
theme_return_this("'black'", "'white'") +"},"+\
" viewWindow:{min:"+\
str(mini) +",max:"+\
str(maxi) +"}," +\
" gridlines: { color: 'transparent' },"+\
" textStyle: { color: "+\
theme_return_this("'black'", "'white'") +" } "+\
" }," +\
" vAxis: {" +\
" title: '', " +\
" textStyle: { color: "+\
theme_return_this("'black'", "'white'") +" } "+\
" }" +\
" };" +\
" var chart = "+\
"new google.visualization.BarChart(document.getElementById('trail_chart'));" +\
" chart.draw(data, options);" +\
" }" +\
" </script>" +\
" <div id='trail_chart' class='sa-chart-hw-290'></div>"
cursor.close()
connection.close()
return chart_content
def get_details_header(uid, burl):
""" xxx """
connection = pymysql.connect(host=DB_SRV,
user=DB_USR,
password=DB_PWD,
db=DB_NAME,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
cr_s = connection.cursor(pymysql.cursors.SSCursor)
sql_s = "SELECT symbol_list.symbol, feed.short_title, "+\
"feed.content, feed.badge, feed.asset_class, feed.market, symbol_list.isin "+\
"FROM symbol_list "+\
"JOIN feed ON symbol_list.symbol = feed.symbol WHERE symbol_list.uid =" +\
str(uid) + " LIMIT 1"
cr_s.execute(sql_s)
rs_s = cr_s.fetchall()
for row in rs_s:
symbol = row[0]
instr_name = row[1]
content = row[2].replace('{burl}', burl)
badge = row[3]
asset_class = row[4]
market = row[5]
isin = row[6]
if len(isin) > 1:
isin = ' | ISIN: '+ str(isin)
else:
isin = ''
if (badge.find('-0') == -1 and badge.find('-1') == -1 and
badge.find('-2') == -1 and badge.find('-3') == -1 and
badge.find('-4') == -1 and badge.find('-5') == -1 and
badge.find('-6') == -1 and badge.find('-7') == -1 and
badge.find('-8') == -1 and badge.find('-9') == -1):
badge_class = 'badge badge-success'
else:
badge_class = 'badge badge-danger'
badge_tooltip = 'Expected returns in the next 7 days'
header_float_right = ''
header_portfolio_info = ''
if symbol.find(get_portf_suffix()) == -1:
# Strategy porttfolio
header_float_right = '<div style="margin: 0px; height: 100%; overflow: hidden;">' +\
get_tradingview_symbol_info(uid) + '</div>'
else:
# Any other instruments
header_portfolio_info = ''+\
' <span class="title"><font style="font-size: x-large;">'+\
instr_name +' <span class="'+\
badge_class+'" data-toggle="tooltip" data-placement="right" title="'+\
badge_tooltip +'" >'+\
badge+'</span></font></span><br />'+\
' <span class="text"><span class="desc">'+\
content + ' | ' +\
asset_class +\
market +\
symbol +\
isin +'</span></span>'
p_header = '' +\
' <div class="col-lg-12 col-md-12 col-sm-12 col-xs-12">'+\
' <div class="box-part rounded" style="'+\
theme_return_this('', 'border-style:solid; border-width:thin; border-color:#343a40;') +'">'+\
header_float_right +\
header_portfolio_info +\
' </div>'+\
' </div>'
cr_s.close()
connection.close()
return p_header
def gen_portf_user_example(burl, acm, notstart):
""" xxx """
resp = make_response(
redirect(burl + 'genportf?step=2¬start=' + str(notstart)))
if acm is None:
asset_class = '%%'
else:
asset_class = acm
if user_is_login() == 1:
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.uid FROM instruments "+\
"JOIN symbol_list ON symbol_list.symbol = instruments.symbol "+\
"WHERE instruments.symbol NOT LIKE '%"+\
get_portf_suffix() +"%' AND symbol_list.disabled=0 AND "+\
"(instruments.y1_signal > 0 ) AND (instruments.asset_class LIKE '"+\
asset_class +"' OR instruments.market LIKE '"+\
asset_class +"') ORDER BY instruments.volatility_risk_st ASC, "+\
"instruments.m6_signal DESC, instruments.m3_signal DESC LIMIT 5"
cursor.execute(sql)
res = cursor.fetchall()
i = 1
for row in res:
resp.set_cookie('portf_s_' + str(i),
str(row[0]),
expires=datetime.datetime.now() +
datetime.timedelta(days=1))
resp.set_cookie('portf_s_' + str(i) + '_conv',
str('weak'),
expires=datetime.datetime.now() +
datetime.timedelta(days=1))
resp.set_cookie('portf_s_' + str(i) + '_type',
str('long/short'),
expires=datetime.datetime.now() +
datetime.timedelta(days=1))
i += 1
if i < 5:
sql = "SELECT symbol_list.uid FROM instruments "+\
"JOIN symbol_list ON symbol_list.symbol = instruments.symbol "+\
"WHERE instruments.symbol NOT LIKE '%"+\
get_portf_suffix() +"%' AND (instruments.m6_signal > 0 ) AND "+\
"(instruments.asset_class LIKE '"+\
asset_class +"' OR instruments.market LIKE '"+\
asset_class +"') AND symbol_list.disabled = 0 "+\
"ORDER BY instruments.volatility_risk_st ASC, "+\
"instruments.m6_signal DESC, "+\
"instruments.m3_signal DESC LIMIT 5"
cursor.execute(sql)
res = cursor.fetchall()
i = 1
for row in cursor:
resp.set_cookie('portf_s_' + str(i),
str(row[0]),
expires=datetime.datetime.now() +
datetime.timedelta(days=1))
resp.set_cookie('portf_s_' + str(i) + '_conv',
str('weak'),
expires=datetime.datetime.now() +
datetime.timedelta(days=1))
resp.set_cookie('portf_s_' + str(i) + '_type',
str('long/short'),
expires=datetime.datetime.now() +
datetime.timedelta(days=1))
i += 1
if i < 5:
add_additional_asset = "FX:"
sql = "SELECT symbol_list.uid FROM instruments "+\
"JOIN symbol_list ON symbol_list.symbol = instruments.symbol "+\
"WHERE instruments.symbol NOT LIKE '%"+\
get_portf_suffix() +"%' AND (instruments.y1_signal > 0 ) AND "+\
"(instruments.asset_class LIKE '"+\
add_additional_asset +"') AND symbol_list.disabled = 0 "+\
"ORDER BY instruments.volatility_risk_st ASC, "+\
"instruments.m6_signal DESC, "+\
"instruments.m3_signal DESC LIMIT 5"
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
resp.set_cookie('portf_s_' + str(i),
str(row[0]),
expires=datetime.datetime.now() +
datetime.timedelta(days=1))
resp.set_cookie('portf_s_' + str(i) + '_conv',
str('weak'),
expires=datetime.datetime.now() +
datetime.timedelta(days=1))
resp.set_cookie('portf_s_' + str(i) + '_type',
str('long/short'),
expires=datetime.datetime.now() +
datetime.timedelta(days=1))
i += 1
return resp
def draw_instr_table(burl, mode, step, maxrow, sel):
""" xxx """
return_data = '<script>$(document).ready(function($) {'+\
'$(".sa-table-click-row").click(function() {'+\
'window.document.location = $(this).data("href");'+\
'});'+\
'});</script>'
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.uid, instruments.w_forecast_change, "+\
"instruments.fullname, instruments.volatility_risk_st, "+\
"instruments.y1_signal, instruments.m6_signal, "+\
"instruments.m3_signal, instruments.m1_signal, instruments.w1_signal, "+\
"instruments.w_forecast_display_info, instruments.unit, "+\
"instruments.symbol FROM instruments "+\
"JOIN symbol_list ON instruments.symbol = symbol_list.symbol "+\
"WHERE symbol_list.symbol NOT LIKE '%"+\
str(get_portf_suffix()) +"%' AND ( instruments.market LIKE '%"+\
str(sel) +"%' OR instruments.asset_class LIKE '%"+ str(sel) +"%') "+\
"AND symbol_list.disabled=0 ORDER BY instruments.y1_signal DESC LIMIT "+\
str(maxrow)
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
uid = row[0]
w_forecast_change = row[1]
fullname = row[2]
volatility_risk_st = row[3]
y1_signal = row[4]
m6_signal = row[5]
m3_signal = row[6]
m1_signal = row[7]
w1_signal = row[8]
w_forecast_display_info = row[9]
unit = row[10]
symbol = row[11]
volatility_risk_st = str(round(volatility_risk_st * 100, 2)) + '%'
if y1_signal >= 0:
class_y1 = "text text-success"
else:
class_y1 = "text text-danger"
if m6_signal >= 0:
class_m6 = "text text-success"
else:
class_m6 = "text text-danger"
if m3_signal >= 0:
class_m3 = "text text-success"
else:
class_m3 = "text text-danger"
if m1_signal >= 0:
class_m1 = "text text-success"
else:
class_m1 = "text text-danger"
if w1_signal >= 0:
class_w1 = "text text-success"
else:
class_w1 = "text text-danger"
if w_forecast_change > -999:
if w_forecast_change >= 0:
class_forecast = "bg bg-success text-white"
else:
class_forecast = "bg bg-danger text-white"
else:
class_forecast = ""
if unit == 'pips':
y1_signal = str(round(y1_signal, 0)) + ' pips'
m6_signal = str(round(m6_signal, 0)) + ' pips'
m3_signal = str(round(m3_signal, 0)) + ' pips'
m1_signal = str(round(m1_signal, 0)) + ' pips'
w1_signal = str(round(w1_signal, 0)) + ' pips'
else:
y1_signal = str(round(y1_signal * 100, 2)) + '%'
m6_signal = str(round(m6_signal * 100, 2)) + '%'
m3_signal = str(round(m3_signal * 100, 2)) + '%'
m1_signal = str(round(m1_signal * 100, 2)) + '%'
w1_signal = str(round(w1_signal * 100, 2)) + '%'
if not mode == "portf_select":
if w_forecast_change > -999:
if w_forecast_change >= 0:
order_type = '<span class="badge badge-success">buy</span>'
else:
order_type = '<span class="badge badge-danger">sell</span>'
else:
order_type = '<span class="text-secondary">wait</span>'
column_order_type = '<td style="text-align: left" scope="row">' + order_type + '</td>'
column_y1 = ' <td class="' + class_y1 + '">' + str(
y1_signal) + '</td>'
column_m6 = ' <td class="' + class_m6 + '">' + str(
m6_signal) + '</td>'
column_m3 = ' <td class="' + class_m3 + '">' + str(
m3_signal) + '</td>'
column_m1 = ' <td class="' + class_m1 + '">' + str(
m1_signal) + '</td>'
column_w1 = ' <td class="' + class_w1 + '">' + str(
w1_signal) + '</td>'
else:
order_type = ''
column_order_type = ''
column_y1 = ''
column_m6 = ''
column_m3 = ''
column_m1 = ''
column_w1 = ''
if mode == 'portf_select':
target_url = burl + 'p/?ins=2&step='+\
str(step) +'&uid='+ str(uid) + '&x=' + str(sel)
if mode == 'view':
target_url = burl + 's/?uid=' + str(uid)
return_data = return_data +\
' <tr class="sa-table-click-row" data-href="'+ target_url +'">'+\
column_order_type +\
' <td style="text-align: left">'+ '<strong>'+\
str(fullname)+ '</strong> (' + str(symbol) + ')' + '</td>'+\
' <td>'+ str(volatility_risk_st) +'</td>'+\
column_y1 +\
column_m6 +\
column_m3 +\
column_m1 +\
column_w1 +\
' <td class="'+ class_forecast +'">'+\
str(w_forecast_display_info) +'</td>'+\
' </tr>'
cursor.close()
connection.close()
return return_data
def draw_portf_table(burl, maxrow, sel, user_portf):
""" xxx """
return_data = '<script>$(document).ready(function($) {'+\
'$(".sa-table-click-row").click(function() {'+\
'window.document.location = $(this).data("href");'+\
'});'+\
'});</script>'
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 user_portf:
sql = "SELECT symbol_list.uid, instruments.w_forecast_change, "+\
"instruments.fullname, instruments.volatility_risk_st, "+\
"instruments.y1, instruments.m6, instruments.m3, instruments.m1, "+\
"instruments.w1, "+\
"instruments.w_forecast_display_info, instruments.unit, "+\
"instruments.symbol, feed.globalrank, feed.content FROM instruments "+\
"JOIN symbol_list ON instruments.symbol = symbol_list.symbol "+\
"JOIN feed ON instruments.symbol = feed.symbol "+\
"WHERE instruments.owner = "+\
str(get_user_numeric_id()) +" AND symbol_list.symbol LIKE '%"+\
str(get_portf_suffix())+ "%'" + " ORDER BY feed.globalrank LIMIT "+\
str(maxrow)
else:
sql = "SELECT symbol_list.uid, instruments.w_forecast_change, "+\
"instruments.fullname, instruments.volatility_risk_st, "+\
"instruments.y1, instruments.m6, instruments.m3, "+\
"instruments.m1, instruments.w1, "+\
"instruments.w_forecast_display_info, instruments.unit, "+\
"instruments.symbol, feed.globalrank, feed.content FROM instruments "+\
"JOIN symbol_list ON instruments.symbol = symbol_list.symbol "+\
"JOIN feed ON instruments.symbol = feed.symbol "+\
"WHERE symbol_list.symbol LIKE '%"+\
str(get_portf_suffix()) +\
"%' AND feed.globalrank <> 0 AND ( instruments.market LIKE '%"+\
str(sel) +"%' OR instruments.asset_class LIKE '%"+\
str(sel) +"%') "+\
"AND symbol_list.disabled=0 ORDER BY feed.globalrank LIMIT "+\
str(maxrow)
cursor.execute(sql)
res = cursor.fetchall()
for row in res:
uid = row[0]
fullname = row[2]
volatility_risk_st = row[3]
y_1 = row[4]
m_6 = row[5]
m_3 = row[6]
m_1 = row[7]
w_1 = row[8]
unit = row[10]
globalrank = row[12]
portf_owner = row[13]
if user_portf:
volatility_risk_st = ''
else:
volatility_risk_st = str(round(volatility_risk_st * 100, 2)) + '%'
if globalrank == 0:
globalrank = 'Not ranked'
if y_1 >= 0:
class_y1 = "text text-success"
else:
class_y1 = "text text-danger"
if m_6 >= 0:
class_m6 = "text text-success"
else:
class_m6 = "text text-danger"
if m_3 >= 0:
class_m3 = "text text-success"
else:
class_m3 = "text text-danger"
if m_1 >= 0:
class_m1 = "text text-success"
else:
class_m1 = "text text-danger"
if w_1 >= 0:
class_w1 = "text text-success"
else:
class_w1 = "text text-danger"
if user_portf:
class_row_style = ""
else:
class_row_style = ""
if unit == 'pips':
y_1 = str(round(y_1, 0)) + ' pips'
m_6 = str(round(m_6, 0)) + ' pips'
m_3 = str(round(m_3, 0)) + ' pips'
m_1 = str(round(m_1, 0)) + ' pips'
w_1 = str(round(w_1, 0)) + ' pips'
else:
y_1 = str(round(y_1 * 100, 2)) + '%'
m_6 = str(round(m_6 * 100, 2)) + '%'
m_3 = str(round(m_3 * 100, 2)) + '%'
m_1 = str(round(m_1 * 100, 2)) + '%'
w_1 = str(round(w_1 * 100, 2)) + '%'
column_globalrank = '<td scope="row" class="'+ class_row_style +\
'" style="text-align: left"><i class="fas fa-trophy"></i> '+\
str(globalrank) +'</td>'
target_url = burl + 'p/?uid=' + str(uid)
return_data = (
return_data +
set_modal_delete_n_view_popup(fullname, uid, burl, user_portf))
if not user_portf:
column_fullname = '<td style="text-align: left" class="'+\
class_row_style +'">'+\
str(portf_owner.replace('{burl}', burl)) + ' | ' +\
str(fullname)+ '</td>'
data_href = 'data-href="' + target_url + '"'
else:
column_fullname = '<td style="text-align: left" class="'+\
class_row_style +'">'+\
'<button type="button" class="btn btn-danger btn-sm active" '+\
get_portf_delete_data_toggle(uid) +'><i class="far fa-trash-alt"></i></button>' +\
' <button type="button" class="btn btn-info btn-sm active" '+\
'data-toggle="modal" data-target="#popup_view_'+\
str(uid) +'">' +\
'<i class="far fa-folder-open"></i></button>'+\
' ' + get_allocation_for_table(uid, connection) +'</td>'
data_href = 'data-href="#"'
column_y1 = ' <td class="' + class_y1 + " " + class_row_style + '">' + str(
y_1) + '</td>'
column_m6 = ' <td class="' + class_m6 + " " + class_row_style + '">' + str(
m_6) + '</td>'
column_m3 = ' <td class="' + class_m3 + " " + class_row_style + '">' + str(
m_3) + '</td>'
column_m1 = ' <td class="' + class_m1 + " " + class_row_style + '">' + str(
m_1) + '</td>'
column_w1 = ' <td class="' + class_w1 + " " + class_row_style + '">' + str(
w_1) + '</td>'
return_data = return_data +\
' <tr class="sa-table-click-row" '+data_href+'>'+\
column_globalrank +\
column_fullname +\
' <td class="'+ class_row_style +'">'+ str(volatility_risk_st) +'</td>'+\
column_y1 +\
column_m6 +\
column_m3 +\
column_m1 +\
column_w1 +\
' </tr>'
cursor.close()
connection.close()
return return_data