def save_quote(db, quote):
"""Saves quote to database"""
expiration = database.encode_date(quote.expiration)
date = database.encode_date(quote.date)
time = database.encode_time(quote.time)
db.execute('UPDATE futures SET time=?, close=? WHERE symbol=? ' \
'AND expiration=? AND date=?;',
[time, quote.close, quote.symbol, expiration, date])
if db.rowcount == 0:
db.execute('INSERT INTO futures VALUES (?, ?, ?, ?, ?);',
[quote.symbol, expiration, date, time, quote.close])
def test_update_stocks_vol(self):
test_date = date(2015, 12, 18)
expected_row = ('SPX', database.encode_date(test_date),
-1.78, -28.25, 3.15, 28.47, 26.80, 23.70, 24.49, 22.66,
21.15, 19.84, 16.89, 15.69, 15.88, 18.30, 15.39)
with database.connect_db(TEST_DB_NAME) as db:
db.execute('SELECT COUNT(*) FROM volatility WHERE symbol=?;',
['SPX'])
self.assertEqual(db.fetchall(), [(0,)])
structures.update_stocks_vol('SPX', TEST_DB_NAME)
with database.connect_db(TEST_DB_NAME) as db:
db.execute('SELECT * FROM volatility WHERE symbol=? AND date=?;',
['SPX', test_date])
self.assertEqual(db.fetchall()[0], expected_row)
db.execute('DELETE FROM volatility WHERE symbol=? AND date=?;',
['SPX', test_date])
db.execute('SELECT * FROM volatility WHERE symbol=? AND date=?;',
['SPX', test_date])
self.assertEqual(db.fetchall(), [])
structures.update_stocks_vol('SPX', TEST_DB_NAME)
with database.connect_db(TEST_DB_NAME) as db:
db.execute('SELECT * FROM volatility WHERE symbol=? AND date=?;',
['SPX', test_date])
self.assertEqual(db.fetchall()[0], expected_row)
def save_quote(db, quote):
"""Saves quote to database"""
expiration = database.encode_date(quote.expiration)
date = database.encode_date(quote.date)
time = database.encode_time(quote.time)
db.execute('UPDATE options SET time=?, bid=?, ask=?, stock=?, ' \
'iv_bid=?, iv_ask=? ' \
'WHERE symbol=? AND type=? AND expiration=? ' \
'AND strike=? AND date=?;', [time, quote.bid, quote.ask,
quote.stock, quote.iv_bid, quote.iv_ask, quote.symbol,
quote.type, expiration, quote.strike, date])
if db.rowcount == 0:
db.execute('INSERT INTO options VALUES ' \
'(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);',
[quote.symbol, quote.type, expiration, quote.strike,
date, time, quote.bid, quote.ask, quote.stock,
quote.iv_bid, quote.iv_ask])
def get_history_table(): # TODO: check app when markets are open
request_date = get_db_date()
with database.connect_db() as db:
symbols = ["VIX", "VXST", "VXV", "VXMT", "VVIX", "SPX", "XIV", "VXX", "TLT"]
query_dates = [database.encode_date(request_date - timedelta(days=30)), database.encode_date(request_date)]
db.row_factory = stocks.quote_factory
db.execute(
"SELECT * FROM stocks WHERE symbol IN (%s) "
"AND date BETWEEN ? and ? "
"ORDER BY symbol, date;" % ",".join("?" * len(symbols)),
symbols + query_dates,
)
quotes = db.fetchall()
db.row_factory = None
db.execute(
"SELECT vol1d, vol2d, vol3d, vol4d, vol5d, vol6d, "
"vol7d, vol8d, vol9d FROM volatility "
"WHERE symbol=? AND date BETWEEN ? and ? "
"ORDER BY date;",
["SPX"] + query_dates,
)
volatilities = np.array(db.fetchall()).T
data = {}
for symbol, quotes in groupby(quotes, key=itemgetter(0)):
quotes = list(quotes)
data[symbol] = ["%.2f" % quote.close for quote in quotes[-10:]]
data["%s_change" % symbol] = [
"%+.2f %%" % ((quote.close / prev_quote.close - 1) * 100)
for quote, prev_quote in izip(quotes[-10:], quotes[-11:-1])
]
if symbol in ["VIX", "VXST", "VXV", "VXMT"]:
data["%s_color" % symbol] = [colorpicker.get_value_color(abs(quote.close), 5, 80) for quote in quotes[-10:]]
data["Date"] = [quote.date.strftime("%d.%m.%y") for quote in quotes[-10:]]
if (request.args.get("date") == "") and not dates.are_markets_open() and len(data["Date"]) > 0:
data["Date"][-1] = "ETH"
for period, row in enumerate(volatilities, 1):
data["SPX vol. %d" % period] = ["%.2f" % item for item in row[-10:]]
data["SPX vol. %d_color" % period] = [colorpicker.get_value_color(abs(item), 5, 80) for item in row[-10:]]
return jsonify(data)
def get_history(): # TODO: stock history doesn't show ETH
request_date = get_db_date()
current_date = dates.get_current_timestamp().date()
with database.connect_db() as db:
db.row_factory = stocks.quote_factory
symbols = ["VIX", "VXST", "VXV", "VXMT", "VVIX", "SPX", "XIV", "TLT"]
query_dates = [database.encode_date(request_date - timedelta(days=30)), database.encode_date(request_date)]
db.execute(
"SELECT * FROM stocks WHERE stocks.symbol IN (%s) "
"AND stocks.date BETWEEN ? and ? "
"ORDER BY symbol, date;" % ",".join("?" * len(symbols)),
symbols + query_dates,
)
quotes = db.fetchall()
data = {}
for symbol, quotes in groupby(quotes, key=itemgetter(0)):
quotes = list(quotes)
if symbol in ["VIX", "VXST", "VXV", "VXMT", "VVIX"]:
data[symbol] = [
{
"y": quote.close,
"name": quote.date.strftime("%d.%m.%Y"),
"xlabel": _get_xlabel1(current_date, quote),
"suffix": ("%+.2f" % (quote.close - prev_quote.close)),
}
for quote, prev_quote in izip(quotes[-10:], quotes[-11:-1])
]
else:
data[symbol] = [
{
"y": quote.close,
"name": quote.date.strftime("%d.%m.%Y"),
"xlabel": _get_xlabel2(current_date, quote, prev_quote),
"suffix": "%+.2f %%" % ((quote.close / prev_quote.close - 1) * 100),
}
for quote, prev_quote in izip(quotes[-20:], quotes[-21:-1])
]
return jsonify(data)
def query_historical(symbol, date, db_name=None):
"""Queries historical quotes from local database for given symbol and date.
Mimics fetch_realtime.
Args:
symbol (str): Stock symbol.
date (date): Date to query.
db_name (str): Optional database name.
Returns:
See fetch_realtime.
"""
with database.connect_db(db_name) as db:
db.row_factory = quote_factory
db.execute('SELECT * FROM options WHERE symbol=? AND date=?;',
[symbol, database.encode_date(date)])
return db.fetchall()
def test_date(self):
date = datetime.date(2015, 12, 9)
self.assertEqual(database.decode_date(database.encode_date(date)), date)
