def get_maxdrawdown(self, symbol, strategy_id, strategy_option, start_date, end_date): conn = MyDB().get_db() c = conn.cursor() #cash+建玉(取得価格) c.execute(""" select business_date ,cash ,pos_price ,pos_vol from backtest_history where symbol = '{symbol}' and strategy_id = {strategy_id} and strategy_option = '{strategy_option}' and business_date between '{start_date}' and '{end_date}' order by business_date """.format(symbol=symbol, strategy_id=strategy_id, strategy_option=strategy_option, start_date=start_date, end_date=end_date)) rs = c.fetchall() conn.close() maxv = 0 minv = 0 max_drawdown = 0 business_date = '' drawdown = 0 count = 0 if rs: for r in rs: v = r[1] + (r[2] * r[3]) if count == 0: maxv = v minv = v elif maxv < v: maxv = v minv = v elif minv > v: minv = v diff = maxv - minv drawdown = self.round(diff / maxv) if max_drawdown < drawdown: max_drawdown = drawdown business_date = r[0] count += 1 self.logger.info( "maxdrawdown:{symbol},{strategy_id},{strategy_option},{start_date},{end_date},{business_date},{max_drawdown}" .format(symbol=symbol, strategy_id=strategy_id, strategy_option=strategy_option, start_date=start_date, end_date=end_date, business_date=business_date, max_drawdown=max_drawdown)) return max_drawdown
def get_max_businessdate_from_ohlc(symbols): conn = MyDB().get_db() c = conn.cursor() #ohlcの最終登録日を取得 c.execute( """ select max(business_date) from ohlc where symbol in ({0})""".format(', '.join('?' for _ in symbols)), symbols) max_date = c.fetchone() conn.close() return max_date[0]
def get_bollingerband_closeondaily_settings(symbol): conn = MyDB().get_db() c = conn.cursor() c.execute(""" select symbol ,sma ,sigma1 from bollingerband_closeondaily where symbol = '{symbol}' """.format(symbol=symbol)) rs = c.fetchall() conn.close() return rs
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()
from mydb import MyDB from pprint import pprint src_host = '127.0.0.1' src_user = '******' src_pwd = 'JING$%^789jing' src_port = 3306 src_db = 'jmedia_ali' try: suffix = {} db = MyDB(src_host, src_user, src_pwd, src_port, src_db) sql = "select user_email from wp_users" data = db.query(sql) for one_row in data: one = one_row[0] s = one.split('@')[-1] c = suffix.get(s, 0) suffix[s] = c + 1 pprint(sorted(suffix.items(), key=lambda d: d[1])) finally: db.close()