def table_base(sql, titl) -> Table:
table = Table()
# engine = create_engine("mysql+pymysql://root:root@localhost:3306/mysql", encoding="utf-8")
# session = sessionmaker(bind=engine)
# df = pd.read_sql(sql, engine)
# rows=df.values.tolist()
# headers=df.columns.tolist()
with SQLPoll() as db:
students = db.fetch_all(sql, None)
headers = []
for student in students:
headers = list(student.keys())
break
c = []
rows = []
for student in students:
c = list(student.values())
rows += [c]
if len(rows) == 0:
headers = ['记录', '数量']
rows = [['无', 0]]
table.add(
headers,
rows).set_global_opts(title_opts=opts.ComponentTitleOpts(title=titl))
return table
def table_base() -> Table:
table = Table()
heardes = [
"课题名称", "课题编号", "年份", "负责人", "负责单位", "课题类型", "领域", "课题方向", "关键词", "URL"
]
res_file = "..\\000LocalData\\caict_k\\research_subject_intergrate.csv"
res_file_read = open(res_file, 'r', encoding='utf-8')
res_list = []
temp_list = []
res_name_list = []
for line in res_file_read.readlines():
line = line.strip().split("|")
if line[1] not in res_name_list:
temp_list.append(line[0])
temp_list.append(line[1].replace("--", "-", 10))
temp_list.append(line[2])
temp_list.append(line[3])
temp_list.append(line[4])
temp_list.append(line[5])
temp_list.append(line[6])
temp_list.append(line[7])
temp_list.append(line[8])
temp_list.append(line[10])
# print(temp_list)
res_list.append(temp_list)
temp_list = []
res_name_list.append(line[1])
table.add(heardes, res_list).set_global_opts(
title_opts=opts.ComponentTitleOpts(title="院软课题知识数据表（2010-2019）"))
return table
def plot_portfolio(self):
# 持仓
total_value = glovar.context.portfolio.total_value
if len(glovar.context.value_history)==0:
day_return = total_value/glovar.context.portfolio.starting_cash-1
else:
day_return = total_value/glovar.context.value_history[-1][-1]-1
headers = ['股票代码','股票名称','持仓数量','开仓时间','收益率','资金占比']
rows = []
value_count = 0
for security in glovar.context.portfolio.positions.index:
position = glovar.context.portfolio.positions.loc[security,:]
if security in self.stock_info.index.values:
name = self.stock_info.loc[security, 'display_name']
else:
name = security[:-5]
position_amount = position.amount
position_value = position.value
profit = (position.price-position.avg_cost)*position.amount*position.multiplier*position.side
init_time = position.init_time.strftime('%Y-%m-%d %H:%M:%S')
returns_rate = '{:.2%}'.format(profit/position_value)
position_rate = '{:.2%}'.format(position_value/total_value)
value_count += position_value
rows.append([security, name, position_amount, init_time, returns_rate, position_rate])
total_position_rate = value_count/total_value
title = '{} 当天收益率：{:.2%} 总资产：{:.0f} 持仓比率：{:.2%} 持股{}只'.format(glovar.context.current_dt.strftime('%Y-%m-%d %H:%M:%S'),
day_return, total_value, total_position_rate, len(glovar.context.portfolio.positions))
(
Table()
.add(headers, rows)
.set_global_opts(title_opts=opts.ComponentTitleOpts(title=title))
.render(sys.path[0]+'/img/{}/{}/持仓.html'.format(self.run_params['mode'], self.run_params['strategy']))
)
def create_base_table(title, x_list, data_list):
headers = [" "] + x_list
rows = []
for key in data_list.keys():
#print(key)
row = []
row.append(key)
for x in data_list[key]:
row.append(x)
#print(row)
rows.append(row)
table = Table()
table.add(headers, rows)
table.set_global_opts(
title_opts=opts.ComponentTitleOpts(title=title)
)
src_path = "./test/"
html_file_name = src_path + title + ".html"
img_file_name = src_path + title + ".png"
table.render(html_file_name)
path_wkimg = r'C:\Program Files\wkhtmltopdf\bin\wkhtmltoimage.exe' # 工具路径
cfg = imgkit.config(wkhtmltoimage=path_wkimg)
imgkit.from_file(html_file_name, img_file_name, config=cfg)
#make_snapshot(snapshot, table.render(html_file_name), img_file_name)
print(img_file_name+"生成完毕...")
def _plot_txt_time(self, stats, ax=None, **kwargs):
"""
Outputs the statistics for various time frames.
"""
returns = stats['returns']
mly_ret = perf.aggregate_returns(returns, 'monthly')
yly_ret = perf.aggregate_returns(returns, 'yearly')
mly_pct = mly_ret[mly_ret >= 0].shape[0] / float(mly_ret.shape[0])
mly_avg_win_pct = np.mean(mly_ret[mly_ret >= 0])
mly_avg_loss_pct = np.mean(mly_ret[mly_ret < 0])
mly_max_win_pct = np.max(mly_ret)
mly_max_loss_pct = np.min(mly_ret)
yly_pct = yly_ret[yly_ret >= 0].shape[0] / float(yly_ret.shape[0])
yly_max_win_pct = np.max(yly_ret)
yly_max_loss_pct = np.min(yly_ret)
header = ["Performance", "Value"]
rows = [["Winning Months %", "{:.0%}".format(mly_pct)],
["Average Winning Month %", "{:.2%}".format(mly_avg_win_pct)],
["Average Losing Month %", "{:.2%}".format(mly_avg_loss_pct)],
["Best Month %", "{:.2%}".format(mly_max_win_pct)],
["Worst Month %", "{:.2%}".format(mly_max_loss_pct)],
["Winning Years %", '{:.0%}'.format(yly_pct)],
["Best Year %", '{:.2%}'.format(yly_max_win_pct)],
["Worst Year %", '{:.2%}'.format(yly_max_loss_pct)]]
table = (Table().add(header, rows).set_global_opts(
title_opts=opts.ComponentTitleOpts(title="Time")))
return table
def table_base(data) -> Table:
table = Table()
level = ['较低:0~3k', '一般:3k~5k', '中等:5k~8k', '较高:8k~12k', '优秀:12k以上']
city = ['beijing', "shanghai", 'guang', 'shen', 'cheng']
table.add(level, data[1]).set_global_opts(
title_opts=opts.ComponentTitleOpts(title="表格", subtitle="城市"))
return table
def table_base(header,row) -> Table:
table = Table()
headers = header
rows = row
table.add(headers, rows).set_global_opts(
title_opts=opts.ComponentTitleOpts(title="Table")
)
return table
def table_base(header,row) -> Table:
table = Table(page_title='未完成项',js_host=r"D:\JGY\600-Data\004-auxiliary辅助文件\\")
headers = header
rows = row
table.add(headers, rows).set_global_opts(
title_opts=opts.ComponentTitleOpts(title="Table")
)
return table
def table_base() -> Table:
table = Table()
headers = ["国家", "2012", "2013", "2014", "2015", "2016", "2017", "2018"]
rows = [
["中国", 3.867, 4.158, 4.301, 3.953, 3.685, 4.107, 4.622],
]
table.add(headers, rows).set_global_opts(
title_opts=opts.ComponentTitleOpts(title="近年中国贸易总额/万亿美元"))
return table
def get_table_base(self, _data):
'''
plot geo-detail
:param _data: gps对应的城市分布(dataframe)
:return: 数据示例
'''
table = Table()
headers = ["City name", "Number"]
rows = _data
table.add(headers, rows).set_global_opts(
title_opts=opts.ComponentTitleOpts(title="Table-Details"))
return table
def get_table_base(self, _df, title_name='数据分布', subtitle_name=''):
'''
数据详情表 （未使用）
:param _df:
:param title_name:
:param subtitle_name:
:return:
'''
_df = _df.reset_index()
table = Table()
headers = _df.columns.tolist()
rows = _df.values
table.add(
headers, rows).set_global_opts(title_opts=opts.ComponentTitleOpts(
title="Table-{}".format(title_name), subtitle=subtitle_name))
return table
def table_base(sql, titl) -> Table:
# for arg in args:
# print(arg)
table = Table()
engine = create_engine(
"mysql+pymysql://root:[email protected]:3306/mysql", encoding="utf-8")
session = sessionmaker(bind=engine)
df = pd.read_sql(sql, engine)
rows = df.values.tolist()
headers = df.columns.tolist()
if len(rows) == 0:
rows = [['无', 0]]
table.add(
headers,
rows).set_global_opts(title_opts=opts.ComponentTitleOpts(title=titl))
return table
def table_base() -> Table:
table = Table()
headers = ["City name", "Area", "Population", "Annual Rainfall"]
rows = [
["Brisbane", 5905, 1857594, 1146.4],
["Adelaide", 1295, 1158259, 600.5],
["Darwin", 112, 120900, 1714.7],
["Hobart", 1357, 205556, 619.5],
["Sydney", 2058, 4336374, 1214.8],
["Melbourne", 1566, 3806092, 646.9],
["Perth", 5386, 1554769, 869.4],
]
table.add(headers, rows).set_global_opts(
title_opts=opts.ComponentTitleOpts(title="Table"))
return table
def _plot_txt_trade(self, stats):
"""
Outputs the statistics for the trades.
"""
if 'positions' not in stats:
num_trades = 0
win_pct = "N/A"
win_pct_str = "N/A"
avg_trd_pct = "N/A"
avg_win_pct = "N/A"
avg_loss_pct = "N/A"
max_win_pct = "N/A"
max_loss_pct = "N/A"
else:
pos = stats['positions']
num_trades = pos.shape[0]
win_pct = pos[pos["trade_pct"] > 0].shape[0] / float(num_trades)
win_pct_str = '{:.0%}'.format(win_pct)
avg_trd_pct = '{:.2%}'.format(np.mean(pos["trade_pct"]))
avg_win_pct = '{:.2%}'.format(
np.mean(pos[pos["trade_pct"] > 0]["trade_pct"]))
avg_loss_pct = '{:.2%}'.format(
np.mean(pos[pos["trade_pct"] <= 0]["trade_pct"]))
max_win_pct = '{:.2%}'.format(np.max(pos["trade_pct"]))
max_loss_pct = '{:.2%}'.format(np.min(pos["trade_pct"]))
max_loss_dt = 'TBD' # pos[pos["trade_pct"] == np.min(pos["trade_pct"])].entry_date.values[0]
avg_dit = '0.0' # = '{:.2f}'.format(np.mean(pos.time_in_pos))
header = ["Performance", "Value"]
rows = [["Trade Winning %", "{}".format(win_pct_str)],
["Average Trade %", "{}".format(avg_trd_pct)],
["Average Win", "{}".format(avg_win_pct)],
["Average Loss", "{}".format(avg_loss_pct)],
["Best Trade", "{}".format(max_win_pct)],
["Worst Trade", '{}'.format(max_loss_pct)],
["Worst Trade Date", '{}'.format(max_loss_dt)],
["Avg Days in Trade", '{}'.format(avg_dit)],
["Trades", '{:.1f}'.format(num_trades)]]
table = (Table().add(header, rows).set_global_opts(
title_opts=opts.ComponentTitleOpts(title="Trade")))
return table
def _plot_txt_curve(self, stats):
"""
Output the statistics for the equity curve
"""
returns = stats["returns"]
cum_returns = stats['cum_returns']
if 'positions' not in stats:
trd_yr = 0
else:
positions = stats['positions']
trd_yr = positions.shape[0] / (
(returns.index[-1] - returns.index[0]).days / 365.0)
tot_ret = cum_returns[-1] - 1.0
cagr = perf.create_cagr(cum_returns, self.periods)
sharpe = perf.create_sharpe_ratio(returns, self.periods)
sortino = perf.create_sortino_ratio(returns, self.periods)
rsq = perf.rsquared(range(cum_returns.shape[0]), cum_returns)
dd, dd_max, dd_dur = perf.create_drawdowns(cum_returns)
header = ["Performance", "Value"]
rows = [["Total Return", "{:.0%}".format(tot_ret)],
["CAGR", "{:.2%}".format(cagr)],
["Sharpe Ratio", "{:.2f}".format(sharpe)],
["Sortino Ratio", "{:.2f}".format(sortino)],
[
"Annual Volatility",
"{:.2%}".format(returns.std() * np.sqrt(252))
], ["R-Squared", '{:.2f}'.format(rsq)],
["Max Daily Drawdown", '{:.2%}'.format(dd_max)],
["Max Drawdown Duration", '{:.0f}'.format(dd_dur)],
["Trades per Year", '{:.1f}'.format(trd_yr)]]
table = (Table().add(header, rows).set_global_opts(
title_opts=opts.ComponentTitleOpts(title="Curve")))
return table
def image_base(img_src, title, subtitle):
image = (Image()
.add(src=img_src)
.set_global_opts(title_opts=opts.ComponentTitleOpts(title=title, subtitle=subtitle)))
return image
def table_base(headers, rows, title="GPU Util") -> Table:
table = Table()
table.add(
headers,
rows).set_global_opts(title_opts=opts.ComponentTitleOpts(title=title))
return table
def image_base(img_src, title, subtitle, width, height):
image = (Image()
.add(src=img_src, style_opts={"width": width, "height": height, "style": "margin-top: 20px"})
.set_global_opts(title_opts=opts.ComponentTitleOpts(title=title, subtitle=subtitle))
)
return image
def radar(student, studentName, course, week, rubrics, studentScore, avgScore, commentor, comment, dir):
page = Page(page_title='%s %s WEEK%d 评分'%(studentName, course, week),layout=Page.SimplePageLayout)
studentRawData = [studentScore]
avgRawData = [avgScore]
rubricsItem = []
for i in range(len(rubrics)):
rubricsItem.append({"name": "%s"%rubrics[i][0], "max": 5, "min": 0})
'''
弃用
rubricsItem = [{"name": "KnowledgeAcquisition", "max": 5, "min": 0},
{"name": "Motivation", "max": 5, "min": 0},
{"name": "Communication", "max": 5, "min": 0},
{"name": "HandsOnSkills", "max": 5, "min": 0},
{"name": "ThinkingSkills", "max": 5, "min": 0},
{"name": "Responsibility", "max": 5, "min": 0},
{"name": "ProjectExecution", "max": 5, "min": 0}]
'''
radarChart = Radar()
radarChart.add_schema(schema=rubricsItem,shape="polygon")
radarChart.add('WEEK%d %s'%(week, studentName), studentRawData, color="#1F1C18")
radarChart.add('WEEK%d Class Average'%week, avgRawData, color="#94060A", is_selected = False)
tableScores = Table()
scoreHeaders = ["Rubrics Item", "Score", "Class Average"]
scoreRows = []
for i in range(len(rubrics)):
print(rubrics)
print(studentScore)
print(avgScore)
scoreRows.append([rubrics[i][0], studentScore[i], avgScore[i]])
'''
弃用
scoreRows = [
["Knowledge Acquisition", studentScore[0], avgScore[0]],
["Motivation", studentScore[1], avgScore[1]],
["Communication", studentScore[2], avgScore[2]],
["Hands-On Skills", studentScore[3], avgScore[3]],
["ThinkingSkills", studentScore[4], avgScore[4]],
["Responsibility", studentScore[5], avgScore[5]],
["Project Execution", studentScore[6], avgScore[6]]
]
'''
tableScores.add(scoreHeaders, scoreRows).set_global_opts(
title_opts=opts.ComponentTitleOpts(title="Scores")
)
tableComments = Table()
commentHeaders = ["Commentor","Comment"]
commentRows = []
for i in range(len(commentor)):
commentRows.append([commentor[i], comment[i]])
tableComments.add(commentHeaders, commentRows).set_global_opts(
title_opts=opts.ComponentTitleOpts(title="Comments")
)
page.add(radarChart)
page.add(tableScores)
page.add(tableComments)
#page.render("%s/%dWEEK%d.html"%(dir, student, week))
page.render("%s\%dWEEK%d.html"%(dir, student, week))
def dong_scatter3(qian_n=10,
data={},
data2=[],
info='',
print_data=[0, 0, 0],
path0=''):
from pyecharts import options as opts
from pyecharts.commons.utils import JsCode
from pyecharts.charts import Scatter, Timeline, Line, Page, Tab
from pyecharts.components import Table
# endlist = ['canshu', 'celue_name', '预测_s_Time', '最终预测值1', 'hg最终预测值1',
# '未来end_3', '未来max_back_3', '未来sharp_rate_3', '未来trade_nums_3', '未来win_rate_3']
data['aim'] = data['canshu'] + data['celue_name']
dict0 = {
'hg最终预测值1': '回归=预测值',
'最终预测值1': '分类=预测值',
'预测_s_Time': 's_Time',
'未来end_3': '未来=月终收益',
'未来max_back_3': '未来=最大回撤',
'未来sharp_rate_3': '未来=夏普率',
'未来trade_nums_3': '未来=交易次数',
'未来win_rate_3': '未来=胜率'
}
data.rename(columns=dict0, inplace=True)
data = data.applymap(lambda x: round(x, 3) if isinstance(x, float) else x)
# print(data.columns)
# print(data.tail())
title = f'动态图:{qian_n}:'
df = data
# data['回归=预测值'] = data['回归=预测值'].apply(lambda x: int(x))
# df['未来=月终收益']=df['未来=月终收益'].apply(lambda x: int(x))
# df['回归=预测值']=df['回归=预测值'].apply(lambda x: int(x))
df['s_Time'] = pd.to_datetime(
df['s_Time']) # .apply(lambda x:x.strftime(format="%Y-%m-%d"))
df.sort_values(by=['s_Time'], ascending=True, inplace=True)
tl = Timeline()
tl_fl = Timeline()
timelist = list(set(df['s_Time'].tolist()))
# sorted(timelist)
# df_date = [time.strftime('%Y-%m-%d',time.localtime(i/1000000000) ) for i in timelist]
# print(df_date)
tjdf = pd.DataFrame()
timelist = sorted(timelist)
# 时间散点图
df_zong = pd.DataFrame()
for k, i in enumerate(timelist):
df1 = df[df['s_Time'] == i]
df0 = df1[df1['分类=预测值'] == df1['分类=预测值'].max()]
tjindex = [x for x in list(df.keys()) if str(x).startswith('未来')]
# print(df[df['s_Time']==i][tjindex].mean())
print(i)
df0.sort_values(by=['回归=预测值'], ascending=True, inplace=True)
print(df0.tail(30))
print(df0.shape)
df0_ = df0.iloc[-1 * qian_n:]
tjdf[i] = df0_[tjindex].mean()
df_zong = df_zong.append(df0_)
xdata = [t for t in df0['回归=预测值'].values.tolist()]
yd1 = df0[['未来=月终收益', '回归=预测值', 'aim']]
if yd1.shape[0] > qian_n:
ydata1 = yd1.iloc[:].values.tolist()
ydata2 = yd1.iloc[-1 * qian_n:].values.tolist()
min_pre = yd1.iloc[-1 * qian_n:]['回归=预测值'].mean()
max_pre = yd1.iloc[-1 * qian_n:]['回归=预测值'].max()
else:
ydata1 = yd1.values.tolist()
# ydata2 = yd1.values.tolist()
min_pre = df0['回归=预测值'].mean()
max_pre = df0['回归=预测值'].max()
xdata2 = [t for t in df1['分类=预测值'].values.tolist()]
yd2 = df1[['未来=月终收益', '分类=预测值', 'aim']].values.tolist()
# print(ydata)
Scatter0 = (
Scatter().add_xaxis(xdata).add_yaxis(
'未来=月终收益',
ydata1,
label_opts=opts.LabelOpts(is_show=False, ),
)
# .add_yaxis('未来=月终收益', ydata2, label_opts=opts.LabelOpts(is_show=False, ), symbol='triangle')
.set_global_opts(
xaxis_opts=opts.AxisOpts(
name='预测值：',
type_="value",
axistick_opts=opts.AxisTickOpts(is_show=True)),
yaxis_opts=opts.AxisOpts(
name='真实值：',
type_="value",
axistick_opts=opts.AxisTickOpts(is_show=True)),
title_opts=opts.TitleOpts(title=f"{title}==:{i}", pos_left=30),
tooltip_opts=opts.TooltipOpts(formatter=JsCode(
"function (params) { return '==真实：'+params.value[1] +' <br/>== 预测：'+ params.value[2]+' <br/>== 策略：'+ params.value[3];}"
), ),
visualmap_opts=opts.VisualMapOpts(min_=min_pre, max_=max_pre),
))
min_pre = df0['未来=月终收益'].mean()
max_pre = df0['未来=月终收益'].max()
Scatter1 = (Scatter().add_xaxis(xdata2).add_yaxis(
'未来=月终收益',
yd2,
label_opts=opts.LabelOpts(is_show=False, ),
).set_global_opts(
xaxis_opts=opts.AxisOpts(
name='预测值：',
type_="value",
axistick_opts=opts.AxisTickOpts(is_show=True)),
yaxis_opts=opts.AxisOpts(
name='真实值：',
type_="value",
axistick_opts=opts.AxisTickOpts(is_show=True)),
title_opts=opts.TitleOpts(title=f"{title}==:{i}", pos_left=30),
tooltip_opts=opts.TooltipOpts(formatter=JsCode(
"function (params) { return '==真实：'+params.value[1] +' <br/>== 预测：'+ params.value[2]+' <br/>== 策略：'+ params.value[3];}"
), ),
visualmap_opts=opts.VisualMapOpts(min_=min_pre, max_=max_pre),
))
tl.add(Scatter0, f"{i}")
tl_fl.add(Scatter1, f"{i}")
tjdf.fillna(0, inplace=True)
tjdf = tjdf.applymap(lambda x: round(x, 3) if isinstance(x, float) else x)
# print(tjdf)
# exit()
tl2 = Timeline()
for wl in tjdf.index:
wl0 = tjdf.loc[wl]
# print(wl0)
line = Line().add_xaxis(list(wl0.index)).add_yaxis(wl, wl0.values.tolist()) \
.set_global_opts(title_opts=opts.TitleOpts(title=f'前{qian_n}名策略\n统计量：{wl}—变化', pos_left=30))
tl2.add(line, wl)
# 统计表格
talble = Table()
# [t.strftime(format="%Y-%m-%d")for t in tjdf.keys()]
tjdf.columns = tjdf.columns.map(lambda x: x.strftime(format="%Y-%m-%d"))
tjdf['统计量名称'] = tjdf.index
tjdf['平均总计'] = tjdf.mean(axis=1)
# print(tjdf['总计'])
# exit()
# print(tjdf.values)
tjdf = tjdf.applymap(lambda x: round(x, 3) if isinstance(x, float) else x)
tjdf_columns = list(tjdf.keys())[-2:] + list(tjdf.keys())[:-2]
tjdf = tjdf[tjdf_columns]
talble.add(list(tjdf.keys()), tjdf.values.tolist()).set_global_opts(
title_opts=opts.ComponentTitleOpts(title=f"前{qian_n}名策略的真实未来，的历史统计表格"))
# 策略推荐表格
talble2 = Table()
tjcelue_data = df[df['s_Time'] == timelist[-1]][[
's_Time', 'canshu', 'celue_name', '分类=预测值', '回归=预测值'
]]
tjcelue_data = tjcelue_data[tjcelue_data['分类=预测值'].notnull()]
tjcelue_data.sort_values(by='分类=预测值', ascending=True, inplace=True)
tjcelue_data = tjcelue_data.iloc[-1 * qian_n:]
talble2.add(
tjcelue_data.columns.tolist(),
tjcelue_data.values.tolist()).set_global_opts(
title_opts=opts.ComponentTitleOpts(title=f"推荐前{qian_n}名策略"))
talble3 = Table()
headers_list = ['介绍：', '本次筛选个数', '过滤因子']
match_df = [print_data]
talble3.add(headers_list, match_df).set_global_opts(
title_opts=opts.ComponentTitleOpts(title=f"整体介绍"))
talble4 = Table()
new_columns = list(data2.keys())
newdata = data2.values.tolist()
talble4.add(new_columns,
newdata).set_global_opts(title_opts=opts.ComponentTitleOpts(
title=f"逐月—策略详情：文件位置：" + path0.split('\\')[0]))
tab0 = Tab()
tab0.add(tl, '回归')
tab0.add(tl_fl, '分类')
tab0.add(tl2, '逐月统计图')
tab0.add(talble, '逐月统计表格')
tab0.add(talble2, '最新月推荐策略')
tab0.add(talble3, '备注描述')
tab0.add(talble4, '每月策略详情表')
tab0.render(path0 + f"{info}.html")
print(path0 + f"{info}.html")
return df_zong
