def insert_history(self, quotes): try: conn = MyDB().get_db() c = conn.cursor() c.executemany( 'INSERT OR REPLACE INTO ohlc(symbol, business_date, open, high, low, close, volume) VALUES(?,?,?,?,?,?,?)', quotes) except Exception as err: self.logger.error('error dayo. {0}'.format(err)) if conn: conn.rollback() finally: if conn: conn.commit() conn.close
def update_maxdrawdown(self, symbols, strategy_id): (end_date, start_date, start_date_3month, start_date_1year, start_date_3year, start_date_15year) = self.get_dates() #バックテスト結果を取得 conn = MyDB().get_db() c = conn.cursor() c.execute( """ select symbol ,strategy_id ,strategy_option from backtest_result where symbol in ({symbols}) and strategy_id = {strategy_id} """.format(symbols=', '.join('?' for _ in symbols), strategy_id=strategy_id), symbols) rs = c.fetchall() conn.close() #ドローダウン算出 for r in rs: symbol = r[0] strategy_id = r[1] strategy_option = r[2] drawdown = self.get_maxdrawdown(symbol, strategy_id, strategy_option, start_date, end_date) drawdown_3month = self.get_maxdrawdown(symbol, strategy_id, strategy_option, start_date_3month, end_date) drawdown_1year = self.get_maxdrawdown(symbol, strategy_id, strategy_option, start_date_1year, end_date) drawdown_3year = self.get_maxdrawdown(symbol, strategy_id, strategy_option, start_date_3year, end_date) drawdown_15year = self.get_maxdrawdown(symbol, strategy_id, strategy_option, start_date_15year, end_date) #DB更新 conn = MyDB().get_db() c = conn.cursor() c.execute(""" update backtest_result set drawdown = {drawdown} ,drawdown_3month = {drawdown_3month} ,drawdown_1year = {drawdown_1year} ,drawdown_3year = {drawdown_3year} ,drawdown_15year = {drawdown_15year} where symbol = '{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' """.format(symbol=symbol, strategy_id=strategy_id, strategy_option=strategy_option, drawdown=drawdown, drawdown_3month=drawdown_3month, drawdown_1year=drawdown_1year, drawdown_3year=drawdown_3year, drawdown_15year=drawdown_15year)) self.logger.info( "update_drawdown() {symbol},{strategy_id},{strategy_option}". format(symbol=symbol, strategy_id=strategy_id, strategy_option=strategy_option)) conn.commit() conn.close()
def update_expected_rate(self, symbols, strategy_id): self.logger.info("update_expected_rate()") (end_date, start_date, start_date_3month, start_date_1year, start_date_3year, start_date_15year) = self.get_dates() #backtest_result table取得 conn = MyDB().get_db() c = conn.cursor() c.execute( """ select symbol ,strategy_id ,strategy_option from backtest_result where symbol in ({symbols}) and strategy_id = {strategy_id} """.format(symbols=', '.join('?' for _ in symbols), strategy_id=strategy_id), symbols) rs = c.fetchall() conn.close() for r in rs: self.logger.info("{symbol},{strategy_id},{strategy_option}".format( symbol=r[0], strategy_id=r[1], strategy_option=r[2])) conn = MyDB().get_db() c = conn.cursor() c.execute(""" update backtest_result set profit_rate_3month = ( select round(sum(profit_rate) ,4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_3month}' and '{end_date}' group by symbol, strategy_id, strategy_option ) ,profit_rate_1year = ( select round(sum(profit_rate) ,4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_1year}' and '{end_date}' group by symbol, strategy_id, strategy_option ) ,profit_rate_3year = ( select round(sum(profit_rate) ,4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_3year}' and '{end_date}' group by symbol, strategy_id, strategy_option ) ,profit_rate_15year = ( select round(sum(profit_rate) ,4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_15year}' and '{end_date}' group by symbol, strategy_id, strategy_option ) ,long_profit_rate_3month = ( select round(sum(profit_rate) ,4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_3month}' and '{end_date}' and execution_order_type in (5,7,11) group by symbol, strategy_id, strategy_option ) ,long_profit_rate_1year = ( select round(sum(profit_rate) ,4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_1year}' and '{end_date}' and execution_order_type in (5,7,11) group by symbol, strategy_id, strategy_option ) ,long_profit_rate_3year = ( select round(sum(profit_rate) ,4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_3year}' and '{end_date}' and execution_order_type in (5,7,11) group by symbol, strategy_id, strategy_option ) ,long_profit_rate_15year = ( select round(sum(profit_rate) ,4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_15year}' and '{end_date}' and execution_order_type in (5,7,11) group by symbol, strategy_id, strategy_option ) ,short_profit_rate_3month = ( select round(sum(profit_rate) ,4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_3month}' and '{end_date}' and execution_order_type in (6,8,12) group by symbol, strategy_id, strategy_option ) ,short_profit_rate_1year = ( select round(sum(profit_rate) ,4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_1year}' and '{end_date}' and execution_order_type in (6,8,12) group by symbol, strategy_id, strategy_option ) ,short_profit_rate_3year = ( select round(sum(profit_rate) ,4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_3year}' and '{end_date}' and execution_order_type in (6,8,12) group by symbol, strategy_id, strategy_option ) ,short_profit_rate_15year = ( select round(sum(profit_rate) ,4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_15year}' and '{end_date}' and execution_order_type in (6,8,12) group by symbol, strategy_id, strategy_option ) ,expected_rate_3month = ( select round(sum(profit_rate) / count(profit_rate), 4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_3month}' and '{end_date}' group by symbol, strategy_id, strategy_option ) ,expected_rate_1year = ( select round(sum(profit_rate) / count(profit_rate), 4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_1year}' and '{end_date}' group by symbol, strategy_id, strategy_option ) ,expected_rate_3year = ( select round(sum(profit_rate) / count(profit_rate), 4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_3year}' and '{end_date}' group by symbol, strategy_id, strategy_option ) ,expected_rate_15year = ( select round(sum(profit_rate) / count(profit_rate), 4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_15year}' and '{end_date}' group by symbol, strategy_id, strategy_option ) ,long_expected_rate_3month = ( select round(sum(profit_rate) / count(profit_rate), 4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_3month}' and '{end_date}' and execution_order_type in (5,7,11) group by symbol, strategy_id, strategy_option ) ,long_expected_rate_1year = ( select round(sum(profit_rate) / count(profit_rate), 4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_1year}' and '{end_date}' and execution_order_type in (5,7,11) group by symbol, strategy_id, strategy_option ) ,long_expected_rate_3year = ( select round(sum(profit_rate) / count(profit_rate), 4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_3year}' and '{end_date}' and execution_order_type in (5,7,11) group by symbol, strategy_id, strategy_option ) ,long_expected_rate_15year = ( select round(sum(profit_rate) / count(profit_rate), 4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_15year}' and '{end_date}' and execution_order_type in (5,7,11) group by symbol, strategy_id, strategy_option ) ,short_expected_rate_3month = ( select round(sum(profit_rate) / count(profit_rate), 4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_3month}' and '{end_date}' and execution_order_type in (6,8,12) group by symbol, strategy_id, strategy_option ) ,short_expected_rate_1year = ( select round(sum(profit_rate) / count(profit_rate), 4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_1year}' and '{end_date}' and execution_order_type in (6,8,12) group by symbol, strategy_id, strategy_option ) ,short_expected_rate_3year = ( select round(sum(profit_rate) / count(profit_rate), 4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_3year}' and '{end_date}' and execution_order_type in (6,8,12) group by symbol, strategy_id, strategy_option ) ,short_expected_rate_15year = ( select round(sum(profit_rate) / count(profit_rate), 4) from backtest_history where symbol='{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date_15year}' and '{end_date}' and execution_order_type in (6,8,12) group by symbol, strategy_id, strategy_option ) where symbol = '{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' """.format(symbol=r[0], strategy_id=r[1], strategy_option=r[2], end_date=end_date, start_date_3month=start_date_3month, start_date_1year=start_date_1year, start_date_3year=start_date_3year, start_date_15year=start_date_15year)) conn.commit() conn.close()
def save_history(self, backtest_history): try: mylock.lock.acquire() conn = MyDB().get_db() c = conn.cursor() c.executemany( """ insert or replace into backtest_history ( symbol, strategy_id, strategy_option, business_date, open, high, low, close, volume, sma, upper_sigma1, lower_sigma1, upper_sigma2, lower_sigma2, vol_sma, vol_upper_sigma1, vol_lower_sigma1, order_create_date, order_type, order_vol, order_price, call_order_date, call_order_type, call_order_vol, call_order_price, execution_order_date, execution_order_type, execution_order_status, execution_order_vol, execution_order_price, position, cash, pos_vol, pos_price, total_value, profit_value, profit_rate, leverage ) values ( ? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ) """, backtest_history) except Exception as err: if conn: conn.rollback() self.logger.error(err) finally: if conn: conn.commit() conn.close mylock.lock.release()
def save_simulate_result( self, symbol, strategy_id, strategy_option, start_date, end_date, market_start_date, market_end_date, backtest_period, trading_period, average_period_per_trade, initial_assets, last_assets, rate_of_return, win_count, loss_count, win_value, loss_value, win_rate, payoffratio, expected_rate, expected_rate_per_1day, long_win_count, long_loss_count, long_win_value, long_loss_value, long_win_rate, long_payoffratio, long_expected_rate, long_expected_rate_per_1day, short_win_count, short_loss_count, short_win_value, short_loss_value, short_win_rate, short_payoffratio, short_expected_rate, short_expected_rate_per_1day, regist_date): try: mylock.lock.acquire() conn = MyDB().get_db() c = conn.cursor() c.execute( """ insert or replace into backtest_result ( symbol ,strategy_id ,strategy_option ,start_date ,end_date ,market_start_date ,market_end_date ,backtest_period ,trading_period ,average_period_per_trade ,initial_assets ,last_assets ,rate_of_return ,win_count ,loss_count ,win_value ,loss_value ,win_rate ,payoffratio ,expected_rate ,expected_rate_per_1day ,long_win_count ,long_loss_count ,long_win_value ,long_loss_value ,long_win_rate ,long_payoffratio ,long_expected_rate ,long_expected_rate_per_1day ,short_win_count ,short_loss_count ,short_win_value ,short_loss_value ,short_win_rate ,short_payoffratio ,short_expected_rate ,short_expected_rate_per_1day ,regist_date ) values ( ? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ,? ) """, (symbol, strategy_id, strategy_option, start_date, end_date, market_start_date, market_end_date, backtest_period, trading_period, average_period_per_trade, initial_assets, last_assets, rate_of_return, win_count, loss_count, win_value, loss_value, win_rate, payoffratio, expected_rate, expected_rate_per_1day, long_win_count, long_loss_count, long_win_value, long_loss_value, long_win_rate, long_payoffratio, long_expected_rate, long_expected_rate_per_1day, short_win_count, short_loss_count, short_win_value, short_loss_value, short_win_rate, short_payoffratio, short_expected_rate, short_expected_rate_per_1day, regist_date)) except Exception as err: if conn: conn.rollback() self.logger.error(err) finally: if conn: conn.commit() conn.close mylock.lock.release()