def get_lowest_buy_price_quantity(symbol, conn=None): ''' get quantity of the lowest buy price transaction ''' if conn is None: conn = get_default_db_connection() session = conn.get_sessionmake()() sell_count = session.query(StockTransaction).\ filter(StockTransaction.symbol == symbol).\ filter(StockTransaction.buy_or_sell == StockTransaction.SELL_FLAG).\ count() if sell_count > 0: session.close() raise Exception("sell count > 0") stock_transaction = session.query(StockTransaction).\ filter(StockTransaction.symbol == symbol).\ filter(StockTransaction.buy_or_sell == StockTransaction.BUY_FLAG).\ order_by(asc(StockTransaction.price)).\ first() if stock_transaction is None: result = 0 else: result = stock_transaction.quantity session.close() return result
def split_transaction(conn=None): ''' split_transaction ''' if conn is None: db_connection = get_default_db_connection() else: db_connection = conn session = db_connection.create_session() query = session.query(StockTransaction).\ filter(StockTransaction.quantity > 100) transactions = query.all() for transaction in transactions: quantity = transaction.quantity i = 100 while i <= quantity: trans = StockTransaction() trans.buy_or_sell = transaction.buy_or_sell trans.date = transaction.date trans.price = transaction.price trans.symbol = transaction.symbol trans.quantity = 100 session.add(trans) print("ADD") i = i + 100 session.delete(transaction) session.commit() break session.close() return
def complete_buy_transaction(symbol, price, quantity, conn=None): ''' update DB after buy transaction ''' if conn is None: conn = get_default_db_connection() session = conn.get_sessionmake()() stock_cash = session.query(StockCash).\ filter(StockCash.symbol == symbol).\ one_or_none() if stock_cash is None: session.close() raise Exception("Save buy transaction failed") LOGGER.debug("Amount before: {0}".format(stock_cash.amount)) stock_cash.amount = stock_cash.amount - \ price * quantity - \ buy_fee(symbol, price, quantity) LOGGER.debug("Amount after: {0}".format(stock_cash.amount)) stock_transaction = StockTransaction() stock_transaction.symbol = symbol stock_transaction.buy_or_sell = "Buy" stock_transaction.quantity = quantity stock_transaction.price = price stock_transaction.date = datetime.utcnow() session.add(stock_transaction) session.commit() session.close() return
def __init__(self, conn = None): if (conn is None): conn = get_default_db_connection() self.logger = logging.getLogger(__name__ + ".StockClosedTransactionTable") self.conn = conn return
def main(): ''' main module ''' conn = get_default_db_connection() session = conn.create_session() stock_infos = session.query(StockInfo).all() total = 0.0 for stock_info in stock_infos: symbol = stock_info.symbol total_amount = update_total_history(symbol, session) if "美元" in symbol: continue else: total += total_amount all_investments_history = AllInvestmentsHistory(date=datetime.utcnow(), total_value=total) session.add(all_investments_history) session.commit() session.close() return
def test_lowest_buy_price_quantity3(self): ''' test lowest buy price quantity with sell transaction ''' stock_db_connection = get_default_db_connection() reset_table(stock_db_connection) stock_transaction_table = StockTransactionTable(stock_db_connection) # init transaction 1 stock_transaction_1 = StockTransaction() stock_transaction_1.symbol = "601398" stock_transaction_1.buy_or_sell = StockTransaction.BUY_FLAG stock_transaction_1.date = date(2016, 5, 15) stock_transaction_1.quantity = 200 stock_transaction_1.price = 4.51 stock_transaction_table.add_stock_transaction(stock_transaction_1) stock_transaction_1.trans_id # init transaction 2 stock_transaction_2 = StockTransaction() stock_transaction_2.symbol = "601398" stock_transaction_2.buy_or_sell = StockTransaction.SELL_FLAG stock_transaction_2.date = date(2016, 5, 16) stock_transaction_2.quantity = 100 stock_transaction_2.price = 4.81 stock_transaction_table.add_stock_transaction(stock_transaction_2) stock_transaction_2.trans_id with self.assertRaises(Exception): StockTransaction.get_lowest_buy_price_quantity("601398") return
def complete_buy_transaction(symbol, price, quantity, conn=None): ''' update DB after buy transaction ''' if conn is None: conn = get_default_db_connection() session = conn.get_sessionmake()() total_amount = price * quantity stock_cash = session.query(StockCash).\ filter(StockCash.symbol == symbol).\ one_or_none() if stock_cash is None: session.close() raise Exception("Save buy transaction failed") stock_cash.amount = stock_cash.amount - total_amount stock_transaction = StockTransaction() stock_transaction.symbol = symbol stock_transaction.buy_or_sell = "Buy" stock_transaction.quantity = quantity stock_transaction.price = price stock_transaction.date = datetime.utcnow() session.add(stock_transaction) session.commit() session.close() return
def test_lowest_buy_price_quantity(self): ''' test lowest buy price quantity ''' stock_db_connection = get_default_db_connection() reset_table(stock_db_connection) stock_transaction_table = StockTransactionTable(stock_db_connection) # init transaction 1 stock_transaction_1 = StockTransaction() stock_transaction_1.symbol = "601398" stock_transaction_1.buy_or_sell = StockTransaction.BUY_FLAG stock_transaction_1.date = date(2016, 5, 15) stock_transaction_1.quantity = 200 stock_transaction_1.price = 4.9 stock_transaction_table.add_stock_transaction(stock_transaction_1) stock_transaction_1.trans_id # init transaction 2 stock_transaction_2 = StockTransaction() stock_transaction_2.symbol = "601398" stock_transaction_2.buy_or_sell = StockTransaction.BUY_FLAG stock_transaction_2.date = date(2016, 5, 16) stock_transaction_2.quantity = 100 stock_transaction_2.price = 4.81 stock_transaction_table.add_stock_transaction(stock_transaction_2) stock_transaction_2.trans_id quantity = StockTransaction.get_lowest_buy_price_quantity("601398") self.assertEqual(quantity, 100) return
def test_stock_transaction_sanity(self): stock_db_connection = get_default_db_connection() reset_table(stock_db_connection) stock_transaction_table = StockTransactionTable(stock_db_connection) stock_transaction = StockTransaction() stock_transaction.set_symbol("601398") stock_transaction.set_buy_or_sell("buy") stock_transaction.set_quantity(100) stock_transaction.set_price(4.51) stock_transaction.set_date(date(2015, 11, 10)) stock_transaction_table.add_stock_transaction(stock_transaction) stock_transaction = StockTransaction() stock_transaction.set_symbol("601857") stock_transaction.set_buy_or_sell("buy") stock_transaction.set_quantity(100) stock_transaction.set_price(4.51) stock_transaction.set_date(date(2015, 11, 10)) stock_transaction_table.add_stock_transaction(stock_transaction) stock_transaction = \ stock_transaction_table.get_stock_transaction_by_trans_id(1) stock_transaction.set_quantity("500") stock_transaction_table.update_stock_transaction(stock_transaction) stock_transaction = \ stock_transaction_table.get_stock_transaction_by_trans_id(1) stock_transaction_table.delete_stock_transaction(stock_transaction) return
def update_keep_alive(app_name=None, conn=None): if app_name is None: return if conn is None: conn = get_default_db_connection() session = conn.create_session() trade_keep_alive = session.query(TradeKeepAlive).\ filter(TradeKeepAlive.app_name == app_name).\ one_or_none() if trade_keep_alive is None: LOGGER.debug("No entry in TradeKeepAlive table, create a new one.") trade_keep_alive = TradeKeepAlive(app_name=app_name, refresh_time=datetime.utcnow()) session.add(trade_keep_alive) else: LOGGER.debug("Existing entry found, refresh time") trade_keep_alive.refresh_time = datetime.utcnow() session.commit() session.close() return
def __init__(self): self.link = \ "http://www.icbc.com.cn/ICBCDynamicSite/" + \ "Charts/GoldTendencyPicture.aspx" self.driver = webdriver.Firefox() self.table_id = "TABLE1" self.conn = get_default_db_connection() return
def complete_sell_transaction(symbol, price, quantity, conn=None): ''' update DB after sell transaction ''' if conn is None: conn = get_default_db_connection() session = conn.get_sessionmake()() stock_cash = session.query(StockCash).\ filter(StockCash.symbol == symbol).\ one_or_none() if stock_cash is None: session.close() raise Exception("Save sell transaction failed") LOGGER.debug("Amount before: {0}".format(stock_cash.amount)) stock_cash.amount = stock_cash.amount + \ price * quantity - \ sell_fee(symbol, price, quantity) LOGGER.debug("Amount after: {0}".format(stock_cash.amount)) # get lowest buy transaction stock_transaction = session.query(StockTransaction).\ filter(StockTransaction.buy_or_sell == StockTransaction.BUY_FLAG).\ filter(StockTransaction.symbol == symbol).\ order_by(asc(StockTransaction.price)).\ first() if stock_transaction is None: session.close() raise Exception("Cannot find lowest buy price transaction") if stock_transaction.quantity < quantity: session.close() raise Exception("stock_transaction.quantity < quantity") if stock_transaction.price > price: session.close() raise Exception("stock_tranaction price > sell_price") stock_closed_transaction = StockClosedTransaction( symbol=symbol, quantity=quantity, buy_price=stock_transaction.price, buy_date=stock_transaction.date, sell_price=price, sell_date=datetime.utcnow()) session.add(stock_closed_transaction) if stock_transaction.quantity == quantity: session.delete(stock_transaction) else: stock_transaction.quantity = stock_transaction.quantity - quantity session.commit() session.close() return
def stock_info_view(request): conn = get_default_db_connection() session = conn.create_session() results = [] stock_infos = session.query(StockInfo).all() for stock_info in stock_infos: stock_info_dict = {"symbol": stock_info.symbol, "name": stock_info.name} results.append(stock_info_dict) session.close() return JsonResponse({"results": results})
def test_reset_table(self): # test logging.info("new StockDbConnection") stock_db_connection = get_default_db_connection() logging.info("reset table") reset_table(stock_db_connection) stock_info_table = StockInfoTable(stock_db_connection) stock_info_list = stock_info_table.get_all_stock_info() self.assertEqual(len(stock_info_list), 7) return
def test_lowest_buy_price2(self): ''' test lowest buy price ''' stock_db_connection = get_default_db_connection() reset_table(stock_db_connection) StockTransactionTable(stock_db_connection) lowest_price = StockTransaction.get_lowest_buy_price("601398") self.assertEqual(lowest_price, 9999.00) return
def test_close_stock_transaction(self): ''' test_close_stock_transaction ''' stock_db_connection = get_default_db_connection() reset_table(stock_db_connection) stock_transaction_table = StockTransactionTable(stock_db_connection) # init transaction 1 stock_transaction_1 = StockTransaction() stock_transaction_1.symbol = "601398" stock_transaction_1.buy_or_sell = StockTransaction.BUY_FLAG stock_transaction_1.date = datetime(2016, 5, 15, 0, 0, 0) stock_transaction_1.quantity = 200 stock_transaction_1.price = 4.51 stock_transaction_table.add_stock_transaction(stock_transaction_1) trans_id_1 = stock_transaction_1.trans_id # init transaction 2 stock_transaction_2 = StockTransaction() stock_transaction_2.symbol = "601398" stock_transaction_2.buy_or_sell = StockTransaction.SELL_FLAG stock_transaction_2.date = datetime(2016, 5, 16, 0, 0, 0) stock_transaction_2.quantity = 200 stock_transaction_2.price = 4.81 stock_transaction_table.add_stock_transaction(stock_transaction_2) trans_id_2 = stock_transaction_2.trans_id stock_closed_transaction = \ StockClosedTransactionTable.close_transaction(stock_transaction_1, stock_transaction_2) self.assertEqual(stock_closed_transaction.symbol, "601398") self.assertEqual(stock_closed_transaction.buy_price, 4.51) self.assertEqual(stock_closed_transaction.sell_price, 4.81) self.assertEqual(stock_closed_transaction.buy_date, datetime(2016, 5, 15, 0, 0, 0)) self.assertEqual(stock_closed_transaction.sell_date, datetime(2016, 5, 16, 0, 0, 0)) self.assertEqual(stock_closed_transaction.quantity, 200) stock_transaction = \ stock_transaction_table.get_stock_transaction_by_trans_id( trans_id_1) self.assertIsNone(stock_transaction, "stock_transaction_1 is not deleted") stock_transaction = \ stock_transaction_table.get_stock_transaction_by_trans_id( trans_id_2) self.assertIsNone(stock_transaction, "stock_transaction_2 is not deleted") return
def test_lowest_buy_price_quantity2(self): ''' test lowest buy price quantity ''' stock_db_connection = get_default_db_connection() reset_table(stock_db_connection) StockTransactionTable(stock_db_connection) quantity = StockTransaction.get_lowest_buy_price_quantity("601398") self.assertEqual(quantity, 0) return
def clean_transaction_by_symbol(symbol, conn=None): ''' clean_transaction_table_by_symbol ''' if conn is None: db_connection = get_default_db_connection() else: db_connection = conn session = db_connection.create_session() query = session.query(StockTransaction).\ filter(StockTransaction.symbol == symbol).\ filter(StockTransaction.buy_or_sell == StockTransaction.SELL_FLAG).\ order_by(desc(StockTransaction.date)) sell_transaction = query.first() if (sell_transaction is None): session.close() raise Exception("no need to clean up transaction table") query = session.query(StockTransaction).\ filter(StockTransaction.symbol == symbol).\ filter(StockTransaction.buy_or_sell == StockTransaction.BUY_FLAG).\ order_by(desc(StockTransaction.price)) found = False buy_transaction = None for trans in query: if (trans.quantity == sell_transaction.quantity) and \ (trans.price < sell_transaction.price): buy_transaction = trans found = True break if not found: session.close() raise Exception("no matched buy transaction found") print(buy_transaction) print(sell_transaction) make_transient(buy_transaction) make_transient(sell_transaction) session.close() StockClosedTransactionTable.close_transaction(buy_transaction, sell_transaction) return
def recreate_db(conn=None): ''' recreate_db ''' if conn is None: db_connection = get_default_db_connection() else: db_connection = conn base = stock_db.db_stock.Base engine = db_connection.get_engine() base.metadata.create_all(engine) return
def close_transaction(stock_transaction_1, stock_transaction_2): ''' close_transaction ''' if stock_transaction_1.symbol != \ stock_transaction_2.symbol: raise ValueError("not same symbol") if stock_transaction_1.quantity != \ stock_transaction_2.quantity: raise ValueError("not same quantity") if stock_transaction_1.buy_or_sell == \ stock_transaction_2.buy_or_sell: raise ValueError("same buy or sell flag") conn = get_default_db_connection() Session = conn.get_sessionmake() session = Session() stock_closed_transaction = StockClosedTransaction() stock_closed_transaction.symbol = stock_transaction_1.symbol stock_closed_transaction.quantity = stock_transaction_1.quantity if stock_transaction_1.buy_or_sell == \ StockTransaction.BUY_FLAG: stock_closed_transaction.buy_price = stock_transaction_1.price stock_closed_transaction.buy_date = stock_transaction_1.date stock_closed_transaction.sell_price = stock_transaction_2.price stock_closed_transaction.sell_date = stock_transaction_2.date else: stock_closed_transaction.buy_price = stock_transaction_2.price stock_closed_transaction.buy_date = stock_transaction_2.date stock_closed_transaction.sell_price = stock_transaction_1.price stock_closed_transaction.sell_date = stock_transaction_1.date session.add(stock_closed_transaction) new_stock_transaction = session.merge(stock_transaction_1) session.delete(new_stock_transaction) new_stock_transaction = session.merge(stock_transaction_2) session.delete(new_stock_transaction) session.commit() session.refresh(stock_closed_transaction) make_transient(stock_closed_transaction) session.close() return stock_closed_transaction
def __init__(self, symbol=None, start_price=None, stop_price=None, current_price=None, conn=None): if conn == None: self.conn = get_default_db_connection() else: self.conn = conn self.symbol = symbol self.start_price = start_price self.stop_price = stop_price self.current_price = current_price self.stock_quantity = -1 self.suggested_buy_or_sell = None self.suggested_amount = 0
def test_stock_closed_transaction_sanity(self): ''' test_stock_closed_transaction_sanity() ''' stock_db_connection = get_default_db_connection() reset_table(stock_db_connection) transaction_table = StockClosedTransactionTable(stock_db_connection) # new a stock closed transaction stock_closed_transaction = StockClosedTransaction() stock_closed_transaction.symbol = "601398" stock_closed_transaction.buy_price = 4.51 stock_closed_transaction.sell_price = 4.61 stock_closed_transaction.buy_date = datetime(2015, 11, 10, 0, 0, 0) stock_closed_transaction.sell_date = datetime(2015, 12, 30, 0, 0, 0) stock_closed_transaction.quantity = 200 transaction_table.add_stock_closed_transaction( stock_closed_transaction) # query and compare stock_closed_transaction_list = \ transaction_table.get_all_stock_closed_transaction() self.assertEqual(len(stock_closed_transaction_list), 1, "There should be only 1 item") stock_closed_transaction = stock_closed_transaction_list[0] self.assertEqual(stock_closed_transaction.symbol, "601398") self.assertEqual(stock_closed_transaction.buy_price, 4.51) self.assertEqual(stock_closed_transaction.sell_price, 4.61) self.assertEqual(stock_closed_transaction.buy_date, datetime(2015, 11, 10, 0, 0, 0)) self.assertEqual(stock_closed_transaction.sell_date, datetime(2015, 12, 30, 0, 0, 0)) self.assertEqual(stock_closed_transaction.quantity, 200) # delete the newly created item transaction_table.delete_stock_closed_transaction( stock_closed_transaction) stock_closed_transaction_list = \ transaction_table.get_all_stock_closed_transaction() self.assertEqual(len(stock_closed_transaction_list), 0, "The list should be an empty list") return
def reset_table(conn=None): ''' reset_table ''' if conn is None: db_connection = get_default_db_connection() else: db_connection = conn base = stock_db.db_stock.Base engine = db_connection.get_engine() base.metadata.drop_all(engine) base.metadata.create_all(engine) # import stock info from csv file import_stock_info() return
def test_stock_cash_sanity(self): stock_db_connection = get_default_db_connection() reset_table(stock_db_connection) stock_cash_table = StockCashTable(stock_db_connection) stock_cash = StockCash("601398", 1000) stock_cash_table.add_stock_cash(stock_cash) # test the new created line stock_cash = stock_cash_table.get_stock_cash_by_symbol("601398") self.assertEqual(stock_cash.get_symbol(), "601398") self.assertEqual(stock_cash.get_amount(), 1000) # test update stock cash stock_cash.set_amount(23.456) stock_cash_table.update_stock_cash(stock_cash) stock_cash = stock_cash_table.get_stock_cash_by_symbol("601398") self.assertEqual(stock_cash.get_amount(), 23.456) # test an unavailable line stock_cash = stock_cash_table.get_stock_cash_by_symbol("XXXXXX") self.assertEqual(stock_cash, None) # insert a new line, and test get_all function stock_cash = StockCash("601857", 5000) stock_cash_table.add_stock_cash(stock_cash) stock_cash_list = stock_cash_table.get_all_stock_cash() self.assertEqual(len(stock_cash_list), 2) stock_cash = stock_cash_list[0] self.assertEqual(stock_cash.get_symbol(), "601398") self.assertEqual(stock_cash.get_amount(), 23.456) stock_cash = stock_cash_list[1] self.assertEqual(stock_cash.get_symbol(), "601857") self.assertEqual(stock_cash.get_amount(), 5000) # delete a line stock_cash = StockCash("601398", 0) stock_cash_table.delete_stock_cash(stock_cash) stock_cash = stock_cash_table.get_stock_cash_by_symbol("601398") self.assertEqual(stock_cash, None)
def get_lowest_gain(symbol, conn=None): """ Get lowest gain by symbol """ if conn is None: db_connection = get_default_db_connection() else: db_connection = conn session = db_connection.create_session() stock_lowest_gain = session.query(StockLowestGain).\ filter(StockLowestGain.symbol == symbol).\ one_or_none() if stock_lowest_gain is None: lowest_gain = None else: lowest_gain = stock_lowest_gain.lowest_gain session.close() return lowest_gain
def __init__(self): self.conn = get_default_db_connection() return