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()
