def assure_required_tables():
if not table_exist("ff_tweets"):
runsql(FF_TWEETS_DDL)
if not table_exist("ff_scan_symbols"):
refresh_sp500()
r = fetch_rawsql(
"select min(datediff(now(),last_updated)) d from ff_scan_symbols")[0]
if r['d'] > 7:
refresh_sp500()
def assure_required_tables():
if table_exist("ff_stock_adj"):
runsql("drop table ff_stock_adj")
runsql(FF_STOCK_ADJ_DDL)
refresh_sp500()
if not table_exist("ff_stock_summary_hist"):
runsql(FF_STOCK_SUMMARY_HIST_DDL)
runsql(FF_STOCK_SUMMARY_HIST_I1)
runsql(FF_STOCK_SUMMARY_HIST_I2)
if not table_exist("ff_stock_summary_term"):
runsql(FF_STOCK_SUMMARY_TERM_DDL)
runsql(FF_STOCK_SUMMARY_TERM_I1)
def assure_required_table():
print("checking")
if not table_exist("ff_scan_symbols"):
print("table ff_scan_symbols not exist; creating...")
runsql(
"create table ff_scan_symbols (symbol varchar(10), last_updated datetime, last_scanned datetime, security varchar(100), sector varchar(100), subsec varchar(200), hq varchar(200), first_added datetime)"
)
def assure_required_tables():
if not table_exist("ff_finnews_iex"):
runsql(FF_FINNEWS_IEX_DDL)
runsql(
"create index ff_finnews_iex_i1 on ff_finnews_iex (symbol, iex_url)"
)
runsql("create index ff_finnews_iex_i2 on ff_finnews_iex (iex_id)")
runsql(
"create index ff_finnews_iex_i3 on ff_finnews_iex (last_updated)")
runsql("create index ff_finnews_iex_i4 on ff_finnews_iex (published)")
def assure_tables():
if not table_exist("ff_status_scan"):
runsql(FF_STATUS_SCAN)
runsql("create index ff_status_scan_i1 on ff_status_scan (batched)")
runsql("create index ff_status_scan_i2 on ff_status_scan (status)")
else:
a = fetch_rawsql("show table status like 'ff_status_scan'")
a = a[0]['Collation']
if a != 'utf8_unicode_ci':
runsql(
"ALTER TABLE ff_status_scan CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci"
)
# maintenance related on collation correction
if table_exist("ff_scan_symbols"):
a = fetch_rawsql("show table status like 'ff_scan_symbols'")
a = a[0]['Collation']
if a != 'utf8_unicode_ci':
runsql(
"ALTER TABLE ff_scan_symbols CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci"
)
def backup_table(tb, d=3, w=2, m=2):
if not table_exist(tb):
print("Backup source " + tb + " does not exist")
return False
cdate = utc.localize(datetime.datetime.utcnow()).astimezone(eastern).date()
if d:
tname = tb + "_bck" + cdate.strftime('%Y%m%d')
runsql("drop table if exists " + tname)
runsql("create table " + tname + " as select * from " + tb)
cur = getcur()
cur.execute("show tables like '" + tb + "_bck________'")
T = [t[0] for t in cur.fetchall()]
for t in T[:-d]:
runsql("drop table " + t)
print("..Backup + cleanup " + tb + " daily ... " +
time.strftime('%Y-%m-%d %H:%M:%S'))
if w:
tname = tb + "_bck" + cdate.strftime('%Yw%W')
runsql("drop table if exists " + tname)
runsql("create table " + tname + " as select * from " + tb)
cur = getcur()
cur.execute("show tables like '" + tb + "_bck____w__'")
T = [t[0] for t in cur.fetchall()]
for t in T[:-w]:
runsql("drop table " + t)
print("..Backup + cleanup " + tb + " weekly ... " +
time.strftime('%Y-%m-%d %H:%M:%S'))
if m:
tname = tb + "_bck" + cdate.strftime('%Ym%m')
runsql("drop table if exists " + tname)
runsql("create table " + tname + " as select * from " + tb)
cur = getcur()
cur.execute("show tables like '" + tb + "_bck____m__'")
T = [t[0] for t in cur.fetchall()]
for t in T[:-m]:
runsql("drop table " + t)
print("..Backup + cleanup " + tb + " monthly ... " +
time.strftime('%Y-%m-%d %H:%M:%S'))
dbcommit()
print("Successfully backed up " + tb)
return True
def do_scan():
status_scan_starttime = datetime.datetime.now()
assure_tables()
symbols = fetch_rawsql(
"select symbol, sector from ff_scan_symbols where datediff(now(),last_updated)<14"
)
s = [i['symbol'] for i in symbols]
r = requests.get(FF_SCAN_HOST + "/api/v1/misc/nonsp/")
if r.status_code != 200:
print("Failed to retrieve list of non-SP500 symbols")
else:
r = r.json()
for i in r['symbol_list']:
if i in s or i in NON_SCAN_LIST:
continue
symbols += [{'symbol': i, 'sector': ''}]
# stop when:
# 1. data not stablized, before 9:45am
# 2. more than 1 hour after trading stopped
# 3. none-trading days
enow = utc.localize(datetime.datetime.utcnow()).astimezone(eastern)
etime = enow.hour + enow.minute / 60
if enow.weekday() < 5 and etime > 9.75 and etime < 17.5:
has_recent_scans = fetch_rawsql(
"select count(1) cnt from ff_status_scan where timestampdiff(minute, scanned, '"
+ get_eastern() + "') < " + str(MINUTES_TRADING_SCANS))[0]['cnt']
if has_recent_scans:
print("Already scanned status in the last " +
str(MINUTES_TRADING_SCANS) +
" minutes. Cooldown even if FORCE_RUN")
dbclose()
sys.exit()
elif not FORCE_RUN:
print("Not during trading hours - skipped status scan")
dbclose()
sys.exit()
# Clear scan cache only when scan actually runs
if table_exist("ff_status_scan_a1"):
runsql("drop table ff_status_scan_a1")
runsql(FF_STATUS_SCAN_A1)
n_rotten = 0
for s0 in symbols:
s = s0['symbol']
print("Scanning " + s + " ...")
try:
q = requests.get("https://api.iextrading.com/1.0/stock/" + s +
"/quote")
q = q.json()
except:
print("- bad IEX call for " + s + " ... skipped")
continue
"""
print("Unexpected error:", sys.exec_info()[0])
raise
"""
# check required keys
if not ('calculationPrice' in q and 'latestPrice' in q
and 'changePercent' in q and 'marketCap' in q
and 'latestUpdate' in q and 'primaryExchange' in q):
print("- bad IEX return format for " + s + " ... skipped")
continue
if 'Arca' in q['primaryExchange']:
print("- not scanning ETF " + s + " ... skipped")
continue
if q['marketCap'] < MIN_MARKETCAP:
print("- MarketCap too low to scan, for " + s + " ... skipped")
continue
# double-check if IEX actually returns the latest quote
updated = utc.localize(
datetime.datetime.utcfromtimestamp(q['latestUpdate'] //
1000)).astimezone(eastern)
print(enow, updated)
diff = enow - updated
dmin = (diff.days * 86400 + diff.seconds) // 60
if dmin > MINUTES_FRESH_SCANS and not FORCE_RUN:
print("- rotten/older IEX return data for " + s + " ... skipped")
n_rotten += 1
if n_rotten >= MAX_ROTTEN:
sys.exit()
continue
# Upon the first IEX fresh data of today, swap out yesterday's benchmark ceil-floor tables (from get_stock_adj)
# *The benchmark tables should only be updated at the time market opens
if s == symbols[0]['symbol'] and q['calculationPrice'] == 'tops':
if table_exist("ff_stock_cf_w00") and table_exist(
"ff_stock_cf_w0"):
runsql("drop table ff_stock_cf_w0")
runsql(
"create table ff_stock_cf_w0 as select * from ff_stock_cf_w00"
)
runsql(
"create index ff_stock_cf_w0_i1 on ff_stock_cf_w0 (symbol)"
)
runsql("drop table ff_stock_cf_w00")
# FF_scans - Volume
while True:
r = requests.get(FF_SCAN_HOST + "/api/v1/famebits/vol/" + s + "/",
headers=headers)
if r.status_code != 429:
break
time.sleep(5)
try:
r = r.json()
if r['I'] > 90 and 'high' in r['rarity']:
msg = VOL_TEMPLATE[np.random.choice(
len(VOL_TEMPLATE),
p=[e['prob'] for e in VOL_TEMPLATE],
size=1)[0]]["tweet"]
msg = msg.replace("&STOCK", "$" + s)
msg = msg.replace("&RARITY", r['rarity'])
msg = msg.replace("&VOLUME", r['volume_human'])
msg = msg.replace("&Z", "{0:.1f}".format(r['z']))
msg = msg.replace("&URL", FF_SCAN_HOST + "fb/vol/" + s + "/")
msg = msg.replace("&FH", FF_SCAN_HOST + "/fh/" + s + "/")
#ctx = str({"v": r['volume_human'], "z": "{0:.1f}".format(r['z'])})
ctx = str(r)
val = (s, q['latestPrice'], q['changePercent'], q['marketCap'],
updated.strftime("%Y-%m-%d %H:%M:%S"), get_eastern(),
enow.strftime("%Y-%m-%d %H:%M:%S"), 'vol', r['rarity'],
ctx, msg, r['I'])
runsql(INSERT_SQL, val)
except Exception as e:
print(e)
print("- error FF vol scan for " + s + " ... skipped")
print(r)
# FF_scans - Volatility + Fluctuation
while True:
r = requests.get(FF_SCAN_HOST + "/api/v1/famebits/pfl/" + s + "/",
headers=headers)
if r.status_code != 429:
break
time.sleep(5)
try:
r = r.json()
if r['I'] > 90 and 'large' in r['rarity']:
msg = PFL_TEMPLATE[np.random.choice(
len(PFL_TEMPLATE),
p=[e['prob'] for e in PFL_TEMPLATE],
size=1)[0]]["tweet"]
msg = msg.replace("&STOCK", "$" + s)
msg = msg.replace("&RARITY", r['rarity'])
msg = msg.replace("&URL", FF_SCAN_HOST + "/fb/vol/" + s + "/")
msg = msg.replace("&FLUX", str(r['hl_ratio']) + "%")
msg = msg.replace("&L", str(r['low']))
msg = msg.replace("&H", str(r['high']))
msg = msg.replace("&FH", FF_SCAN_HOST + "/fh/" + s + "/")
#ctx = str({"f": r['hl_ratio'], "l": r['low'], "h": r['high']})
ctx = str(r)
val = (s, q['latestPrice'], q['changePercent'], q['marketCap'],
updated.strftime("%Y-%m-%d %H:%M:%S"), get_eastern(),
enow.strftime("%Y-%m-%d %H:%M:%S"), 'pfl', r['rarity'],
ctx, msg, r['I'])
runsql(INSERT_SQL, val)
except Exception as e:
print(e)
print("- error FF pfl scan for " + s + " ... skipped")
print(r)
# FF_scans - Single Day Change
while True:
r = requests.get(FF_SCAN_HOST + "/api/v1/famebits/chg/" + s + "/",
headers=headers)
if r.status_code != 429:
break
time.sleep(5)
try:
r = r.json()
if s0['sector']:
val = (s, s0['sector'], q['changePercent'], q['marketCap'],
updated.strftime("%Y-%m-%d %H:%M:%S"),
enow.strftime("%Y-%m-%d %H:%M:%S"))
runsql(INSERT_A1, val)
if r['I'] > 90 and 'large' in r['rarity']:
msg = CHG_TEMPLATE[np.random.choice(
len(CHG_TEMPLATE),
p=[e['prob'] for e in CHG_TEMPLATE],
size=1)[0]]["tweet"]
msg = msg.replace("&STOCK", "$" + s)
msg = msg.replace("&RARITY", r['rarity'])
msg = msg.replace("&CHG", str(abs(round(r['chg'] * 100, 2))))
msg = msg.replace("&MOVE", r['move'])
msg = msg.replace("&URL", FF_SCAN_HOST + "/fb/chg/" + s + "/")
msg = msg.replace("&FH", FF_SCAN_HOST + "/fh/" + s + "/")
msg = msg.replace(
"&HAVE", ("is having" if r['status'] == "live" else "had"))
msg = msg.replace(
"&JS", (("roar" if "extreme" in r['rarity'] else
("jump" if "very" in r['rarity'] else "gain"))
if r['chg'] > 0 else
("crash" if "extreme" in r['rarity'] else
("dive" if "very" in r['rarity'] else "drop"))))
#ctx = str({"c": r['chg'], "m": r['move'], "js": r['js']})
ctx = str(r)
val = (s, q['latestPrice'], q['changePercent'], q['marketCap'],
updated.strftime("%Y-%m-%d %H:%M:%S"), get_eastern(),
enow.strftime("%Y-%m-%d %H:%M:%S"), 'chg', r['rarity'],
ctx, msg, r['I'])
runsql(INSERT_SQL, val)
except Exception as e:
print(e)
print("- error FF chg scan for " + s + " ... skipped")
print(r)
# FF_scans - Ceiling Floor Penetration
while True:
r = requests.get(FF_SCAN_HOST + "/api/v1/famebits/cfp/" + s + "/",
headers=headers)
if r.status_code != 429:
break
time.sleep(5)
try:
r = r.json()
if r['I'] > 90 and r['target'] == 'quoted':
msg = CFP_TEMPLATE[np.random.choice(
len(CFP_TEMPLATE),
p=[e['prob'] for e in CFP_TEMPLATE],
size=1)[0]]["tweet"]
msg = msg.replace("&STOCK", "$" + s)
msg = msg.replace("&TARGET", r['target'])
msg = msg.replace("&MOVE", r['move'])
msg = msg.replace("&URL", FF_SCAN_HOST + "/fb/dme/" + s + "/")
msg = msg.replace("&FH", FF_SCAN_HOST + "/fh/" + s + "/")
#ctx = str({"t": r['target'], "m": r['move']})
ctx = str(r)
val = (s, q['latestPrice'], q['changePercent'], q['marketCap'],
updated.strftime("%Y-%m-%d %H:%M:%S"), get_eastern(),
enow.strftime("%Y-%m-%d %H:%M:%S"), 'cfp', r['rarity'],
ctx, msg, r['I'])
runsql(INSERT_SQL, val)
except Exception as e:
print(e)
print("- error FF cfp scan for " + s + " ... skipped")
print(r)
runsql("drop table if exists ff_status_scan_w1")
runsql(
"create table ff_status_scan_w1 as select a.*, @rn:=@rn+1 rn from (select batched, count(1) n_item, count(distinct symbol) n_symbol from ff_status_scan group by batched order by batched desc) a, (select @rn:=0) b"
)
runsql(
"create index ff_status_scan_w1_i1 on ff_status_scan_w1 (n_symbol, rn)"
)
runsql("drop table if exists ff_status_scan_w2")
runsql(
"create table ff_status_scan_w2 as select stype, if(changePct>=0,'u','d') ch, count(1) n from ff_status_scan a, (select batched as max_batched from ff_status_scan_w1 where n_symbol>=20 order by rn limit 1) b where a.batched=b.max_batched and a.marketcap>"
+ str(MIN_MARKETCAP) + " group by stype, if(changePct>=0,'u','d')")
runsql("drop table if exists ff_status_scan_w3")
runsql("create table ff_status_scan_w3 as select * from ff_status_scan_a1")
runsql("drop table if exists ff_status_scan_w4")
runsql(
"create table ff_status_scan_w4 as select max(batched) batched, sector, count(1) cnt, sum(marketcap) sm, round(sum(changePct*marketcap)) cm, round(sum(changePct*marketcap)/sum(marketcap)*100,2) marketchgPct from ff_status_scan_w3 group by sector having cnt>20 order by marketchgPct desc"
)
if not table_exist("ff_status_scan_a2"):
runsql(
"create table ff_status_scan_a2 as select * from ff_status_scan_w4"
)
runsql(
"create index ff_status_scan_a2_i1 on ff_status_scan_a2 (batched)")
else:
runsql("insert into ff_status_scan_a2 select * from ff_status_scan_w4")
runsql("drop table if exists ff_status_scan_w5")
runsql(
"create table ff_status_scan_w5 as select d.sector, stype, count(1) n, sum(if(changePct>=0,1,0)) n_u, sum(if(changePct<0,1,0)) n_d, any_value(d.cnt) n_sector, any_value(round(count(1)/d.cnt,4)) s_ratio from ff_status_scan a join (select batched as max_batched from ff_status_scan_w1 order by rn limit 1) b on a.batched=b.max_batched join (select symbol, sector from ff_scan_symbols where datediff(now(),last_updated)<60) c on a.symbol=c.symbol right join ff_status_scan_w4 d on c.sector=d.sector group by d.sector, a.stype order by sector, s_ratio desc"
)
runsql(
"create index ff_status_scan_w5_i1 on ff_status_scan_w5 (sector, s_ratio)"
)
runsql("drop table if exists ff_status_scan_w6")
runsql(
"create table ff_status_scan_w6 as select max(batched) batched, sector, any_value(if(locate(' ',sector)>0, concat(substr(sector,1,1),substr(sector,locate(' ',sector)+1,2)), substr(sector,1,3))) sector3, count(1) cnt, sum(if(changePct>=0,1,0)) n_u, sum(if(changePct<0,1,0)) n_d, sum(marketcap) sm, round(sum(changePct*marketcap)) cm, round(sum(abs(changePct)*marketcap)) am, round(sum(changePct*marketcap)/sum(marketcap)*100,2) marketchgPct from ff_status_scan_w3 group by sector having cnt>20 order by marketchgPct desc"
)
runsql("drop table if exists ff_status_scan_w7")
runsql(
"create table ff_status_scan_w7 as select a.security, a.subsec, a.hq, b.*, changePct*marketcap marketeff, abs(changePct*marketcap) marketabs from (select symbol, security, subsec, hq from ff_scan_symbols where datediff(now(),last_updated)<60) a, ff_status_scan_w3 b where a.symbol=b.symbol order by marketabs"
)
runsql(
"create index ff_status_scan_w7_i1 on ff_status_scan_w7 (sector, marketabs)"
)
runsql("drop table if exists ff_status_scan_w8")
runsql(
"create table ff_status_scan_w8 as select b.batched_date, a.* from ff_status_scan_a2 a,(select date(batched) batched_date, max(batched) batched_time from ff_status_scan_a2 group by date(batched)) b where a.batched=b.batched_time"
)
runsql(
"create index ff_status_scan_w8_i1 on ff_status_scan_w8 (sector, batched_date)"
)
runsql(
"create index ff_status_scan_w8_i2 on ff_status_scan_w8 (batched_date)"
)
cache_baking()
status_scan_endtime = datetime.datetime.now()
timedetail = "status_scan job is running from {0} to {1}, and total runging time is {2}".format(
status_scan_starttime, status_scan_endtime,
(status_scan_endtime - status_scan_starttime).seconds)
print(timedetail)
Log.info("status_scan.py", timedetail)
from get_sp500 import refresh_sp500
rym = [(10, 60), (10, 36), (10, 12), (5, 36), (5, 12), (3, 12), (3, 6), (1, 6)]
rmd = [(6, 90), (6, 30), (6, 7), (3, 30), (3, 7), (1, 7)]
cdate = datetime.date.today()
#GoogleDailyReader.url = 'http://finance.google.com/finance/historical'
if ForceRefreshTable:
if cdate.weekday() in (5, 6):
print("today is weekend, ForeceRefreshTable")
cdate = cdate - datetime.timedelta(days=cdate.weekday() - 4)
tname = "ff_stock_" + cdate.strftime('%Y%m%d')
if table_exist(tname):
print("Up to date")
sys.exit()
FF_STOCK_ADJ_DDL = """create table ff_stock_adj (
symbol varchar(10),
close_date date,
high float,
low float,
close float,
volume float
)
""".replace('\n', ' ').replace('\r', '')
FF_STOCK_SUMMARY_HIST_DDL = """create table ff_stock_summary_hist (
score_date date,
class LemmaTokenizer(object):
def penn2morphy(penntag):
""" Converts Penn Treebank tags to WordNet. """
morphy_tag = {'NN':'n', 'JJ':'a', 'VB':'v', 'RB':'r'}
try:
return morphy_tag[penntag[:2]]
except:
return 'n'
def __init__(self):
self.wnl = WordNetLemmatizer()
def __call__(self, doc):
#return [self.wnl.lemmatize(t) for t in word_tokenize(doc)]
return [self.wnl.lemmatize(word.lower(), pos=penn2morphy(tag)) for word, tag in pos_tag(word_tokenize(doc))]
def assure_folder(filename):
if not os.path.exists(os.path.dirname(filename)):
try:
os.makedirs(os.path.dirname(filename))
except OSError as exc: # Guard against race condition
if exc.errno != errno.EEXIST:
raise
if not table_exist("ff_finnews_iex"):
print("Missing required table: ff_finnews_iex")
sys.exit()