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)