def bubble_chart(element_id):
sectors = database.query_db(
"select distinct sector from stocks where asx200 = 't' order by sector asc"
)
series = []
for sector in sectors:
data = []
stocks = database.query_db(
"select * from stocks where sector = ? and pe_ratio is not null and pe_ratio != ''",
(sector[0], ))
for stock in stocks:
div_yield = 0 if stock['div_yield'] in [None, ''
] else stock['div_yield']
name = "%s (%s)" % (stock['company_name'], stock['asx_code'])
data.append({
'name': name,
'y': div_yield,
'x': stock['pe_ratio'],
'z': stock['market_cap']
})
series.append({'name': sector[0], 'data': data})
response = make_response(
render_template('chart/bubble.js',
element_id=element_id,
series=series))
response.headers['Content-Type'] = 'text/javascript'
return response
def bubble_chart(element_id):
sectors = database.query_db("select distinct sector from stocks where asx200 = 't' order by sector asc")
series = []
for sector in sectors:
data = []
stocks = database.query_db("select * from stocks where sector = ? and pe_ratio is not null and pe_ratio != ''", (sector[0],))
for stock in stocks:
div_yield = 0 if stock['div_yield'] in [None, ''] else stock['div_yield']
name = "%s (%s)" % (stock['company_name'], stock['asx_code'])
data.append({ 'name': name , 'y': div_yield, 'x': stock['pe_ratio'], 'z': stock['market_cap']})
series.append({ 'name': sector[0], 'data': data })
response = make_response(render_template('chart/bubble.js', element_id=element_id, series=series))
response.headers['Content-Type'] = 'text/javascript'
return response
def bin_data_by_div_yield():
data = database.query_db("select id, div_yield from stocks")
db = database.get_db()
for row in data:
if row['div_yield'] == 0 or row['div_yield'] == '':
div_yield_bin = "0"
if row['div_yield'] >= 10:
div_yield_bin = ">=10"
else:
div_yield_bin = "%d-%d" % (int(row['div_yield']), int(row['div_yield']+1))
db.execute("update stocks set div_yield_bin = ? where id = ?", (div_yield_bin, row['id']))
db.commit()
def bin_data_by_franking():
data = database.query_db("select id, franking from stocks")
db = database.get_db()
for row in data:
if row['franking'] == 0:
franking_bin = "No Franking"
elif row['franking'] == 100:
franking_bin = "Fully Franked"
else:
franking_bin = "Partially Franked"
db.execute("update stocks set franking_bin = ? where id = ?", (franking_bin, row['id']))
db.commit()
def bin_data_by_market_cap():
data = database.query_db("select id, market_cap from stocks")
db = database.get_db()
for row in data:
if row['market_cap'] >= LARGE_CAP_THRESHOLD:
market_cap_bin = "Large Cap"
elif row['market_cap'] >= MID_CAP_THRESHOLD:
market_cap_bin = "Medium Cap"
else:
market_cap_bin = "Small Cap"
db.execute("update stocks set market_cap_bin = ? where id = ?", (market_cap_bin, row['id']))
db.commit()
def bin_data_by_eps():
data = database.query_db("select id, eps from stocks")
db = database.get_db()
for row in data:
if row['eps'] == None:
eps_bin = None
elif row['eps'] >= 0:
eps_bin = "Positive Earnings"
else:
eps_bin = "Negative Earnings"
db.execute("update stocks set eps_bin = ? where id = ?", (eps_bin, row['id']))
db.commit()
def bin_data_by_franking():
data = database.query_db("select id, franking from stocks")
db = database.get_db()
for row in data:
if row['franking'] == 0:
franking_bin = "No Franking"
elif row['franking'] == 100:
franking_bin = "Fully Franked"
else:
franking_bin = "Partially Franked"
db.execute("update stocks set franking_bin = ? where id = ?",
(franking_bin, row['id']))
db.commit()
def bin_data_by_market_cap():
data = database.query_db("select id, market_cap from stocks")
db = database.get_db()
for row in data:
if row['market_cap'] >= LARGE_CAP_THRESHOLD:
market_cap_bin = "Large Cap"
elif row['market_cap'] >= MID_CAP_THRESHOLD:
market_cap_bin = "Medium Cap"
else:
market_cap_bin = "Small Cap"
db.execute("update stocks set market_cap_bin = ? where id = ?",
(market_cap_bin, row['id']))
db.commit()
def bin_data_by_eps():
data = database.query_db("select id, eps from stocks")
db = database.get_db()
for row in data:
if row['eps'] == None:
eps_bin = None
elif row['eps'] >= 0:
eps_bin = "Positive Earnings"
else:
eps_bin = "Negative Earnings"
db.execute("update stocks set eps_bin = ? where id = ?",
(eps_bin, row['id']))
db.commit()
def bin_data_by_div_yield():
data = database.query_db("select id, div_yield from stocks")
db = database.get_db()
for row in data:
if row['div_yield'] == 0 or row['div_yield'] == '':
div_yield_bin = "0"
if row['div_yield'] >= 10:
div_yield_bin = ">=10"
else:
div_yield_bin = "%d-%d" % (int(
row['div_yield']), int(row['div_yield'] + 1))
db.execute("update stocks set div_yield_bin = ? where id = ?",
(div_yield_bin, row['id']))
db.commit()
def bin_data_by_tsr_3y():
data = database.query_db("select id, tsr_3y from stocks")
db = database.get_db()
for row in data:
if row['tsr_3y'] == None:
tsr_3y_bin = None
elif row['tsr_3y'] >= 100:
tsr_3y_bin = "#07 TSR >= 100%"
elif row['tsr_3y'] >= 50:
tsr_3y_bin = "#06 50% <= TSR < 100%"
elif row['tsr_3y'] >= 25:
tsr_3y_bin = "#05 25% <= TSR < 50%"
elif row['tsr_3y'] >= 0:
tsr_3y_bin = "#04 0% <= TSR < 25%"
elif row['tsr_3y'] >= -25:
tsr_3y_bin = "#03 -25% <= TSR < 0%"
elif row['tsr_3y'] >= -50:
tsr_3y_bin = "#02 -50% <= TSR < -25%"
else:
tsr_3y_bin = "#01 TSR < -50%"
db.execute("update stocks set tsr_3y_bin = ? where id = ?", (tsr_3y_bin, row['id']))
db.commit()
def bin_data_by_tsr_3y():
data = database.query_db("select id, tsr_3y from stocks")
db = database.get_db()
for row in data:
if row['tsr_3y'] == None:
tsr_3y_bin = None
elif row['tsr_3y'] >= 100:
tsr_3y_bin = "#07 TSR >= 100%"
elif row['tsr_3y'] >= 50:
tsr_3y_bin = "#06 50% <= TSR < 100%"
elif row['tsr_3y'] >= 25:
tsr_3y_bin = "#05 25% <= TSR < 50%"
elif row['tsr_3y'] >= 0:
tsr_3y_bin = "#04 0% <= TSR < 25%"
elif row['tsr_3y'] >= -25:
tsr_3y_bin = "#03 -25% <= TSR < 0%"
elif row['tsr_3y'] >= -50:
tsr_3y_bin = "#02 -50% <= TSR < -25%"
else:
tsr_3y_bin = "#01 TSR < -50%"
db.execute("update stocks set tsr_3y_bin = ? where id = ?",
(tsr_3y_bin, row['id']))
db.commit()
def footer_query(self):
sql = "\n".join([self.select_sql(), self.filter_sql()])
return database.query_db(sql, (), True)
def distinct_values(self, column):
return database.query_db("select distinct %s from %s order by %s" % (column, self.db_table, column))
def stocks():
stocks = database.query_db(
"select * from stocks order by market_cap desc;")
return render_template("stocks.html", stocks=stocks)
def stocks():
stocks = database.query_db("select * from stocks order by market_cap desc;")
return render_template("stocks.html", stocks=stocks)