Ejemplo n.º 1
0
    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))
Ejemplo n.º 2
0
    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)
Ejemplo n.º 3
0
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
Ejemplo n.º 4
0
 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)
Ejemplo n.º 5
0
 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)
Ejemplo n.º 6
0
 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)
Ejemplo n.º 7
0
 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)
Ejemplo n.º 8
0
 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)
Ejemplo n.º 9
0
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()
Ejemplo n.º 10
0
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
Ejemplo n.º 11
0
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)
Ejemplo n.º 12
0
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)
Ejemplo n.º 13
0
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
Ejemplo n.º 14
0
 def test_connect(self):
     with database.connect_db() as db:
         db.execute('select 1;')
         value, = db.fetchone()
         self.assertEqual(value, 1)