def StatsCurrentHighest():
mics = [{
'name': ['X', 'ALL']
}, {
'name': ['XBRU', 'BE - Euronext Brussels']
}, {
'name': ['XPAR', 'FR - Euronext Paris']
}, {
'name': ['XAMS', 'NL - Euronext Amsterdam']
}, {
'name': ['XLIS', 'PT - Euronext Lisbon']
}]
if request.method == 'POST':
mic = request.form['myMic']
query = ''' SELECT PRETTY_NAME, LATEST_INTEREST, GRAPH_NAME, ISSUER
FROM Issuers
WHERE DELETED = 0 AND MIC like '%{}%'
ORDER BY LATEST_INTEREST DESC
LIMIT 10; '''.format(mic)
# select ALL
else:
query = ''' SELECT PRETTY_NAME, LATEST_INTEREST, GRAPH_NAME, ISSUER
FROM Issuers
WHERE DELETED = 0
ORDER BY LATEST_INTEREST DESC
LIMIT 10; '''
mic = 'X'
# same for if & else
query_data = QueryDB(query, 'SummaryDB.db')
graphs = [(g[0], g[1], g[2], g[3])
for g in query_data] #incl issuer for notifications (see below)
notifs_dict = {}
for g in graphs:
qry = '''SELECT HOLDER, ISSUER, ISIN, INTEREST, POSITION_DATE
FROM Latest_Notifications
WHERE ISSUER = "{}"
ORDER BY POSITION_DATE DESC;'''.format(g[3])
qry_data = QueryDB(qry, 'SummaryDB.db')
data = [(d[0], d[1], d[2], d[3], d[4]) for d in qry_data]
notifs_dict[g[3]] = data
# return template
return render_template('statscurrent.html',
graphs=graphs,
notifs_dict=notifs_dict,
mics=mics,
selection=mic)
def Interactive():
if request.method == 'POST' and request.form['name'] != "":
string = request.form['name'].replace('"', '')
name = ''.join([i for i in string.replace('_', ' ') if i.isalnum()])
query = """SELECT PRETTY_NAME, LATEST_INTEREST, GRAPH_NAME, ISSUER
FROM Issuers
WHERE UPPER(PRETTY_NAME) LIKE "{}%" AND DELETED = 0
ORDER BY PRETTY_NAME
LIMIT 1;""".format(string.upper())
else: # current highest SI
query = """ SELECT PRETTY_NAME, LATEST_INTEREST, GRAPH_NAME, ISSUER
FROM Issuers
WHERE DELETED = 0
ORDER BY LATEST_INTEREST DESC
LIMIT 1;"""
# same for if & else
query_data = QueryDB(query, 'SummaryDB.db')
graphs = [(g[0], g[1], g[2].replace('.png', '.html'), g[3])
for g in query_data] #incl issuer for notifications (see below)
return render_template('interactive.html', graphs=graphs[:1])
def StatsAllTimeHighest():
query = ''' SELECT PRETTY_NAME, MAX_SI, GRAPH_NAME
FROM Issuers
WHERE DELETED = 0
ORDER BY MAX_SI DESC
LIMIT 10;'''
query_data = QueryDB(query, 'SummaryDB.db')
graphs = [(g[0], g[1], g[2]) for g in query_data]
return render_template('statsalltime.html', graphs=graphs)
def Notifications():
mics = [{
'name': ['XBRU', 'BE - Euronext Brussels']
}, {
'name': ['XPAR', 'FR - Euronext Paris']
}, {
'name': ['XAMS', 'NL - Euronext Amsterdam']
}, {
'name': ['XLIS', 'PT - Euronext Lisbon']
}]
if request.method == 'POST':
mic = request.form['myMic']
string = request.form['name']
name = ''.join([i for i in string if i.isalnum()])
query = '''SELECT HOLDER, ISSUER, ISIN, INTEREST, POSITION_DATE, MIC
FROM {1}
WHERE (ISSUER LIKE "%{0}%" OR HOLDER LIKE "%{0}%" OR ISIN LIKE "%{0}%") AND (COMMENT not like '%_%' or COMMENT is null or COMMENT like '%_PDF')
ORDER BY POSITION_DATE DESC
LIMIT 50;'''.format(name.upper(), mic)
query_data = QueryDB(query, 'SummaryDB.db')
else:
mic = 'XAMS'
query = '''SELECT HOLDER, ISSUER, ISIN, INTEREST, POSITION_DATE, MIC
FROM {0}
WHERE COMMENT not like '%_%' or COMMENT is null
ORDER BY POSITION_DATE DESC
LIMIT 50;'''.format(mic)
query_data = QueryDB(query, 'SummaryDB.db')
return render_template('notifications.html',
data=query_data,
mics=mics,
selection=mic)
def HomePage():
if request.method == 'POST' and request.form['name'] != "":
string = request.form['name'].replace('"', '').replace(';', '')
name = ''.join([i for i in string.replace('_', ' ') if i.isalnum()])
query = """SELECT PRETTY_NAME, LATEST_INTEREST, GRAPH_NAME, ISSUER
FROM Issuers
WHERE UPPER(PRETTY_NAME) LIKE "%{}%" AND DELETED = 0
ORDER BY LATEST_INTEREST DESC;""".format(string.upper())
else: # shows current highest SI
query = """SELECT PRETTY_NAME, LATEST_INTEREST, GRAPH_NAME, ISSUER
FROM Issuers
WHERE DELETED = 0
ORDER BY LATEST_INTEREST DESC
LIMIT 5;"""
# same for if & else
query_data = QueryDB(query, 'SummaryDB.db')
graphs = [(g[0], g[1], g[2], g[3])
for g in query_data] #incl issuer for notifications (see below)
notifs_dict = {}
for g in graphs[:5]:
qry = '''SELECT HOLDER, ISSUER, ISIN, INTEREST, POSITION_DATE
FROM Latest_Notifications
WHERE ISSUER = "{}"
ORDER BY POSITION_DATE DESC;'''.format(g[3])
qry_data = QueryDB(qry, 'SummaryDB.db')
data = [(d[0], d[1], d[2], d[3], d[4]) for d in qry_data]
notifs_dict[g[3]] = data
# return template
return render_template('homepage.html',
graphs=graphs[:5],
notifs_dict=notifs_dict)
def StatsDecliningThreeMonths():
query = ''' SELECT PRETTY_NAME, LATEST_INTEREST - CHANGE_3M "Declining3M", GRAPH_NAME
FROM Issuers
WHERE DELETED = 0
ORDER BY "Declining3M" ASC
LIMIT 10;'''
query_data = QueryDB(query, 'SummaryDB.db')
graphs = [(g[0], "%.2f" % g[1], g[2]) for g in query_data]
return render_template(
'statschanges.html',
graphs=graphs,
sign='',
header="Strongest Declining Short Interest (Three Months)")
def StatsRisingOneMonth():
query = ''' SELECT PRETTY_NAME, LATEST_INTEREST - CHANGE_1M "Rising1M", GRAPH_NAME
FROM Issuers
WHERE DELETED = 0
ORDER BY "Rising1M" DESC
LIMIT 10;'''
query_data = QueryDB(query, 'SummaryDB.db')
graphs = [(g[0], "%.2f" % g[1], g[2]) for g in query_data]
return render_template(
'statschanges.html',
graphs=graphs,
sign='+',
header="Strongest Rising Short Interest (One Month)")