def seed_asx200_list():
db = database.get_db()
csv = parse_remote_csv(ASX200_LIST_URL)
for row in csv:
if len(row) >= 18 and len(row[4]) == 3: # Only take rows with valid ASX codes
db.execute("update stocks set asx200 = 't' where asx_code = ?", (row[4],))
db.commit()
def seed_asx200_list():
db = database.get_db()
csv = parse_remote_csv(ASX200_LIST_URL)
for row in csv:
if len(row) >= 18 and len(
row[4]) == 3: # Only take rows with valid ASX codes
db.execute("update stocks set asx200 = 't' where asx_code = ?",
(row[4], ))
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_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 seed_securities_list():
db = database.get_db()
db.execute("delete from stocks")
csv = parse_remote_csv(SECURITIES_LIST_URL)
for row in csv:
prog = re.compile('[A-Z]+\/[A-Z]')
if len(row) >= 24 and prog.match(row[8]): # Match ASX codes like "ONT/S". Ignore derivatives and non-stock securities.
db.execute("insert into stocks (asx_code, company_name, sector, market_cap, div_yield, pe_ratio, franking, eps, tsr_3y) values (?,?,?,?,?,?,?,?,?)", (row[8][:3], row[7], sector_for_gics_code(row[9]), row[6], row[17], row[22], row[18], row[21], tsr_for_value(row[3])))
db.commit()
bin_data_by_market_cap()
bin_data_by_franking()
bin_data_by_div_yield()
bin_data_by_eps()
bin_data_by_tsr_3y()
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 seed_securities_list():
db = database.get_db()
db.execute("delete from stocks")
csv = parse_remote_csv(SECURITIES_LIST_URL)
for row in csv:
prog = re.compile('[A-Z]+\/[A-Z]')
if len(row) >= 24 and prog.match(
row[8]
): # Match ASX codes like "ONT/S". Ignore derivatives and non-stock securities.
db.execute(
"insert into stocks (asx_code, company_name, sector, market_cap, div_yield, pe_ratio, franking, eps, tsr_3y) values (?,?,?,?,?,?,?,?,?)",
(row[8][:3], row[7], sector_for_gics_code(row[9]), row[6],
row[17], row[22], row[18], row[21], tsr_for_value(row[3])))
db.commit()
bin_data_by_market_cap()
bin_data_by_franking()
bin_data_by_div_yield()
bin_data_by_eps()
bin_data_by_tsr_3y()
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 query(self):
db = database.get_db()
return db.execute(self.sql())