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)