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