def test_update_stocks_vol_last(self): structures.update_stocks_vol('SPX', TEST_DB_NAME) with database.connect_db(TEST_DB_NAME) as db: db.execute('SELECT MAX(date) FROM stocks WHERE symbol=?;', ['SPX']) test_date = database.decode_date(db.fetchall()[0][0]) db.execute('SELECT * FROM volatility WHERE symbol=? AND date=?;', ['SPX', test_date]) original_row = db.fetchall()[0][3:] db.execute('UPDATE volatility SET change=?, vol1d=?, vol2d=?, ' \ 'vol3d=?, vol4d=?, vol5d=?, vol6d=?, vol7d=?, vol8d=?, '\ 'vol9d=?, vol1m=?, vol2m=?, vol3m=?, vol6m=?, vol1y=? ' \ 'WHERE symbol=? AND date=?;', [0] * 15 + ['SPX', test_date]) db.execute('SELECT * FROM volatility WHERE symbol=? AND date=?;', ['SPX', test_date]) corrupted_row = db.fetchall()[0][3:] self.assertTrue(all(abs(item) > 1e-5 for item in original_row)) self.assertTrue(all(abs(item) < 1e-5 for item in corrupted_row)) structures.update_stocks_vol_last('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]) updated_row = db.fetchall()[0][3:] self.assertTrue(all(abs(item) > 1e-5 for item in updated_row))
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 _parse_historical_data(symbol, expiration, data, db_name=None): """Parses historical (EOD) quotes from CBOE and saves them to database. Args: symbol (str): Symbol. expiration (date): Expiration. data (str): Raw quotes for the symbol. db_name (str): Optional database name. Returns: int: Number of quotes fetched. """ assert symbol == 'VX' lines = 0 with database.connect_db(db_name) as db: for line in data.splitlines(): values = line.strip().split(',') if len(values) != 11: continue if values[0] == 'Trade Date': continue date = datetime.strptime(values[0], '%m/%d/%Y').date() settle = utils.to_float(values[6]) quote = FutureQuote(symbol, expiration, date, None, settle) save_quote(db, quote) lines += 1 return lines
def _check_database_quotes(self, symbol, total, check_date, check_close): with database.connect_db(TEST_DB_NAME) as db: db.execute("SELECT COUNT(*) FROM stocks WHERE symbol=?;", [symbol]) self.assertEqual(db.fetchall(), [(total,)]) if total > 0: database_quotes = stocks.query_historical([symbol], check_date, TEST_DB_NAME) self._check_quotes(database_quotes, symbol, check_date, check_close)
def test_save_and_factory(self): quote1 = stocks.StockQuote(NON_EXISTING_SYMBOL, date(2015, 1, 2), time(15, 16), 17.2) with database.connect_db(TEST_DB_NAME) as db: db.row_factory = stocks.quote_factory stocks.save_quote(db, quote1) db.execute("SELECT * FROM stocks;") quote2 = db.fetchone() self.assertEqual(quote1, quote2)
def _check_database_quotes(self, symbol, total, check_date, total_date, is_eod, stock): with database.connect_db(TEST_DB_NAME) as db: db.execute("SELECT COUNT(*) FROM options WHERE symbol=?;", [symbol]) self.assertEqual(db.fetchall(), [(total,)]) if total > 0: database_quotes = options.query_historical(symbol, check_date, TEST_DB_NAME) self.assertEqual(len(database_quotes), total_date) self._check_quotes(database_quotes, symbol, check_date, is_eod, stock)
def test_save_and_factory(self): quote1 = futures.FutureQuote( 'XXX', date(2015, 3, 4), date(2015, 1, 2), time(15, 16), 17.2) with database.connect_db(TEST_DB_NAME) as db: db.row_factory = futures.quote_factory futures.save_quote(db, quote1) db.execute('SELECT * FROM futures;') quote2 = db.fetchone() self.assertEqual(quote1, quote2)
def test_save_and_factory(self): quote1 = options.OptionQuote( "XXX", consts.CALL, date(2015, 3, 4), 51.2, date(2015, 1, 2), time(15, 16), 1.1, 2.2, 40.3, 12.1, 13.2 ) with database.connect_db(TEST_DB_NAME) as db: db.row_factory = options.quote_factory options.save_quote(db, quote1) db.execute("SELECT * FROM options;") quote2 = db.fetchone() self.assertEqual(quote1, quote2)
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 _parse_realtime_data(symbol, data, db_name=None): """Parses realtime (delayed) futures quotes from CBOE website and saves them to database. Args: symbol (str): Symbol. data (str): Raw quotes for the symbol. db_name (str): Optional database name. Returns: list: List of FutureQuote objects. """ assert symbol == 'VX' logger = logging.getLogger(__name__) timestamp = dates.get_database_timestamp() date = timestamp.date() time = timestamp.time() base_symbol = _future_to_base_symbol(symbol) quotes = [] with database.connect_db(db_name) as db: try: soup = BeautifulSoup(data, 'html5lib') table = soup.find('a', {'name': symbol}).parent.parent.find('table') for row in table.find_all('tr'): cols = row.find_all('td') if len(cols) != 9: continue row_symbol = cols[0].text.strip().split('/')[0] if row_symbol != base_symbol: continue expiration = datetime.strptime( cols[1].text.strip(), '%m/%d/%Y').date() last = utils.to_float(cols[2].text.strip()) if last is None: continue quote = FutureQuote(symbol, expiration, date, time, last) save_quote(db, quote) quotes.append(quote) except AttributeError: logger.error('Cannot parse futures quotes from CBOE for %s ...', symbol) return quotes
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 _parse_data(symbol, data, is_eod, db_name=None, timestamp=None): """Parses realtime (delayed) options quotes from CBOE and saves to database. Args: symbol (str): Symbol. data (str): Raw quotes for the symbol. is_eod (bool): If True: mark received quotes as EOD (time=None), if False: store actual time. db_name (str): Optional database name. timestamp (datetime): Optional datetime for the data. Returns: list: List of OptionQuote objects. """ logger = logging.getLogger(__name__) if timestamp is None: timestamp = dates.get_database_timestamp() date = timestamp.date() time = None if is_eod else timestamp.time() quotes = [] stock_price = None expirations = dates.get_expirations(symbol) with database.connect_db(db_name) as db: for line in data.splitlines(): values = line.strip().split(',') if (len(values) == 4) and (stock_price is None): stock_price = utils.to_float(values[1]) continue if len(values) != 15: continue if values[0] == 'Calls' or values[0].find('-') >= 0: continue code_values = values[0].split(' ') if len(code_values) != 4: continue position = code_values[3].find(code_values[0]) if code_values[3][1:position] in SKIP_SYMBOLS: continue expiration_year = 2000 + int(code_values[0]) expiration_month = MONTHS[code_values[1]] expiration_day = int(code_values[3][position + 2:position + 4]) expiration = datetime(expiration_year, expiration_month, expiration_day).date() if expiration not in expirations: continue strike = utils.to_float(code_values[2]) for type_, bid, ask in [ (consts.CALL, values[3], values[4]), (consts.PUT, values[10], values[11]), ]: bid = utils.to_float(bid) ask = utils.to_float(ask) quote = OptionQuote( symbol, type_, expiration, strike, date, time, bid, ask, stock_price, None, None) iv_bid = math.calc_iv(quote, bid) * 100 iv_ask = math.calc_iv(quote, ask) * 100 quote = OptionQuote( symbol, type_, expiration, strike, date, time, bid, ask, stock_price, iv_bid, iv_ask) save_quote(db, quote) quotes.append(quote) logger.info('... quotes parsed: %d', len(quotes)) return quotes
def test_connect(self): with database.connect_db() as db: db.execute('select 1;') value, = db.fetchone() self.assertEqual(value, 1)