def write_yeild_and_drawdown():
temp = drawdown_by_time(t)
table['C'+str(nrows+2)].value='最大回撤'
table['D'+str(nrows+2)].value=temp[0]
table['D'+str(nrows+2)].number_format = format_percent
table['E'+str(nrows+2)].value=temp[1]
table['F'+str(nrows+2)].value=temp[2]
yearly_drawdown = temp[5];i=0
for year in yearly_drawdown.keys():
table[nrows+3+i][2].value = year
table[nrows+3+i][3].value = yearly_drawdown[year][0]
table[nrows+3+i][3].number_format = format_percent
table[nrows+3+i][4].value = yearly_drawdown[year][1]
table[nrows+3+i][5].value = yearly_drawdown[year][2]
i+=1
table['A'+str(nrows+2)].value='累计收益率'
table['B'+str(nrows+2)].value=temp[3]
table['B'+str(nrows+2)].number_format = format_percent
yearly_return = temp[4];i=0
for year in yearly_return.keys():
table[nrows+3+i][0].value = year
table[nrows+3+i][1].value = yearly_return[year]
table[nrows+3+i][1].number_format = format_percent
i+=1
rule1 = DataBarRule(start_type='percentile', start_value=0, end_type='percentile', end_value=99,
color="FFFF0000", showValue="None", minLength=None, maxLength=60)
rule2 = DataBarRule(start_type='percentile', start_value=90, end_type='percentile', end_value=0,
color="FF22ae6b", showValue="None", minLength=None, maxLength=60)
table.conditional_formatting.add('B{}:B{}'.format(nrows+3, nrows+2+i), rule1)
table.conditional_formatting.add('D{}:D{}'.format(nrows+3, nrows+2+i), rule2)
def make_databar(col, bar_color):
data_bar_rule = DataBarRule(start_type="num",
start_value=1,
end_type="num",
end_value=360,
color=bar_color)
return data_bar_rule
def create(excelPath, rows):
# 定义数据条件规则
rule = DataBarRule(start_type='percentile',
start_value=10,
end_type='percentile',
end_value='90',
color="FF638EC6",
showValue="None",
minLength=None,
maxLength=None)
# 创建工作簿
wb = Workbook()
# 获取当前sheet页
sheet = wb.active
# 填充数据
for row in rows:
sheet.append(row)
# 设置数据条件规则
sheet.conditional_formatting.add('B2:B5', rule)
wb.save(excelPath)
print("生成的Excel路径是：", excelPath)
def write_tables(self, ws, writer, df_list, spaces=2):
'''
escribe las tablas en el template proporcionado
'''
row = 8
for df in df_list:
ws.insert_rows(idx=row + 1, amount=len(df[0].index))
df[0].to_excel(writer,
sheet_name="rdio",
startrow=row,
startcol=0,
header=False)
if df[1] == 'PARTIDAS':
rule = DataBarRule(start_type='num',
start_value=0,
end_type='num',
end_value=1,
color='FFDD00',
showValue="None",
minLength=None,
maxLength=None)
cells = 'J9:J%s' % (str(9 + len(df[0].index)))
ws.conditional_formatting.add(cells, rule)
for i in range(0, len(df[0].index)):
ws['J' + str(9 + i)].style = "Percent"
ws['G' + str(9 + i)].style = "Percent"
row = row + len(df[0].index) + spaces + 2
def beautify_excel():
'''
美化输出的excel文件，并将最大回撤率等数据写入excel文件。
'''
global result, table, nrows, font, rule, format_number, format_percent
# 打开文件，读取先前输出的内容
result = openpyxl.load_workbook(folder+f+'[OUTPUT].xlsx')
table = result.worksheets[0]
nrows = table.max_row
# 准备样式（处理字体，取消边框）
font = Font(name='dengxian', size=12)
rule = DataBarRule(start_type='min', start_value=0, end_type='max', end_value=90,
color="FFFF0000", showValue="None", minLength=None, maxLength=None)
format_number = numbers.BUILTIN_FORMATS[40]
format_percent = numbers.BUILTIN_FORMATS[10]
# 设置 Sheet name，添加列名
table.title = '收益统计'
table['A1'].value = 'Open Signal'
table['B1'].value = 'Close Signal'
# 去除重复的列名，去除杂乱的边框
for row in range(nrows+1, 0, -1):
for j in range(len(table[row])):
table[row][j].border = Border(outline=False)
if table[row][j].value == table[1][j].value and row > 1:
table[row][j].value = None
# 加入数据条
table.conditional_formatting.add('C1:C'+str(nrows), rule)
# 设置列宽
table.column_dimensions['A'].width = 13
table.column_dimensions['B'].width = 13
table.column_dimensions['C'].width = 14
table.column_dimensions['D'].width = 14
table.column_dimensions['E'].width = 20
table.column_dimensions['F'].width = 20
table.column_dimensions['G'].width = 10
table.column_dimensions['H'].width = 8
table.column_dimensions['I'].width = 9.5
table.column_dimensions['J'].width = 8
table.column_dimensions['K'].width = 10
table.column_dimensions['L'].width = 9
table.column_dimensions['M'].width = 13
table.column_dimensions['N'].width = 13
table.column_dimensions['O'].width = 16
for c in ['E','H','G','J','M','N','O']:
for irow in range(2,nrows+1):
if table[c+str(irow)].value != None:
table[c+str(irow)].number_format = format_number
for c in ['D', 'L']:
for irow in range(2,nrows+1):
if table[c+str(irow)].value != None:
table[c+str(irow)].number_format = format_percent
def beautify_excel():
'''
美化输出的excel文件
'''
from openpyxl.styles import Font, Border
from openpyxl.formatting.rule import DataBarRule
from openpyxl.drawing.image import Image
# 打开文件，读取输出
result = openpyxl.load_workbook(f[:-4] + ' [OUTPUT].xlsx')
table = result.worksheets[0]
nrows = table.max_row
# 准备样式（处理字体，取消边框）
font = Font(name='dengxian', size=12)
rule = DataBarRule(start_type='min',
start_value=0,
end_type='max',
end_value=90,
color="FFFF0000",
showValue="None",
minLength=None,
maxLength=None)
#设置 Sheet name，添加列名
table.title = '收益统计'
table['A1'].value = 'Open Signal'
table['B1'].value = 'Close Signal'
# 去除重复的列名，去除杂乱的边框
for row in range(nrows + 1, 0, -1):
for j in range(len(table[row])):
table[row][j].font = font
table[row][j].border = Border(outline=False)
if table[row][j].value == table[1][j].value and row > 1:
table[row][j].value = None
# 加入数据条
table.conditional_formatting.add('C1:C' + str(nrows), rule)
# 设置列宽
table.column_dimensions['A'].width = 13
table.column_dimensions['B'].width = 13
table.column_dimensions['C'].width = 14
# 插入图表
wg = result.create_sheet(title='收益率平稳性')
# wg.active
graph = Image(f[:-4] + 'AC figure.png')
wg.append([' '])
wg.add_image(graph, 'A1')
result.save(f[:-4] + ' [OUTPUT].xlsx')
print('已完成excel输出 文件路径 ' + f[:-4] + ' [OUTPUT].xlsx')
return 0
def highlight_excel():
workbook = load_workbook(filename=excel_file)
sheet = workbook.active
row_count = sheet.max_row
dimensions = sheet.dimensions
sheet.auto_filter.ref = dimensions
red_background = PatternFill(bgColor='ff4d4d')
yellow_background = PatternFill(bgColor='ffff80')
green_background = PatternFill(bgColor='ccffcc')
diff_style1 = DifferentialStyle(fill=red_background)
diff_style2 = DifferentialStyle(fill=yellow_background)
diff_style3 = DifferentialStyle(fill=green_background)
rule3 = Rule(type="expression", dxf=diff_style3)
rule3.formula = ["$D1>=7"]
sheet.conditional_formatting.add('A1:C' + str(row_count), rule3)
sheet.conditional_formatting.add('E1:J' + str(row_count), rule3)
rule1 = Rule(type="expression", dxf=diff_style1)
rule1.formula = ["$D1<5"]
sheet.conditional_formatting.add('A1:C' + str(row_count), rule1)
sheet.conditional_formatting.add('E1:J' + str(row_count), rule1)
rule2 = Rule(type="expression", dxf=diff_style2)
rule2.formula = (["$D1<7"])
sheet.conditional_formatting.add('A1:C' + str(row_count), rule2)
sheet.conditional_formatting.add('E1:J' + str(row_count), rule2)
data = pd.read_csv(csv_collection)
start_value = min(data['SCORE'])
end_value = max(data['SCORE'])
data_bar_rule = DataBarRule(start_type="num",
start_value=start_value,
end_type="num",
end_value=end_value,
color='53ff4d')
sheet.conditional_formatting.add('D2:D' + str(row_count), data_bar_rule)
red_text = Font(color='a70000')
green_text = Font(color='00bb00')
for element in range(2, row_count + 1):
sheet['F' + str(element)].number_format = numbers.FORMAT_CURRENCY_USD
sheet['G' + str(element)].number_format = numbers.FORMAT_CURRENCY_USD
sheet['H' + str(element)].number_format = numbers.FORMAT_CURRENCY_USD
cell_value = sheet.cell(element, 8).value
if int(cell_value) < 0:
sheet.cell(element, 8).font = red_text
else:
sheet.cell(element, 8).font = green_text
workbook.save(excel_file)
def beautify_excel():
'''
美化输出的excel文件，并将最大回撤率等数据写入excel文件。
'''
global font, rule, format_number, format_percent
# 准备样式（处理字体，取消边框）
font = Font(name='dengxian', size=12)
rule = DataBarRule(start_type='min', start_value=0, end_type='max', end_value=90,
color="FFFF0000", showValue="None", minLength=None, maxLength=None)
format_number = numbers.BUILTIN_FORMATS[40]
format_percent = numbers.BUILTIN_FORMATS[10]
def write_yeild_and_drawdown(nrows):
temp = drawdown_by_time(ans)
table['C'+str(nrows+1)].value='最大回撤'
table['D'+str(nrows+1)].value=temp[0]
table['D'+str(nrows+1)].number_format = format_percent
table['E'+str(nrows+1)].value=temp[1]
table['F'+str(nrows+1)].value=temp[2]
yearly_drawdown = temp[3];i=0
for year in yearly_drawdown.keys():
table[nrows+3+i][2].value = year
table[nrows+3+i][3].value = yearly_drawdown[year][0]
table[nrows+3+i][3].number_format = format_percent
try:
table[nrows+3+i][4].value = yearly_drawdown[year][1].strftime('%Y-%m-%d')
table[nrows+3+i][5].value = yearly_drawdown[year][2].strftime('%Y-%m-%d')
except:
pass
i+=1
temp = yearly_return_AIP(ans)
table['A'+str(nrows+1)].value='累计收益率'
table['B'+str(nrows+1)].value=temp[0]
table['B'+str(nrows+1)].number_format = format_percent
yearly_return = temp[1];i=0
for year in yearly_return.keys():
table[nrows+3+i][0].value = year
table[nrows+3+i][1].value = yearly_return[year]
table[nrows+3+i][1].number_format = format_percent
i+=1
rule1 = DataBarRule(start_type='percentile', start_value=0, end_type='percentile', end_value=99,
color="FFFF0000", showValue="None", minLength=None, maxLength=60)
rule2 = DataBarRule(start_type='percentile', start_value=90, end_type='percentile', end_value=0,
color="FF22ae6b", showValue="None", minLength=None, maxLength=60)
table.conditional_formatting.add('B{}:B{}'.format(nrows+3, nrows+1+i), rule1)
table.conditional_formatting.add('D{}:D{}'.format(nrows+3, nrows+1+i), rule2)
for c in ['A','B','C','D','E','F']:
table.column_dimensions[c].width = 14
sheet.conditional_formatting.add("H2:H100", color_scale_rule)
workbook.save(filename="sample_conditional_formatting_color_scale.xlsx")
color_scale_rule = ColorScaleRule(start_type="num",start_value=1,start_color=colors.RED,
mid_type="num",mid_value=3,mid_color=colors.YELLOW,end_type="num",
end_value=5,end_color=colors.GREEN)
# Again, let's add this gradient to the star ratings, column "H"
sheet.conditional_formatting.add("H2:H100", color_scale_rule)
workbook.save(filename="sample_conditional_formatting_color_scale_3.xlsx")
icon_set_rule = IconSetRule("5Arrows", "num", [1, 2, 3, 4, 5])
sheet.conditional_formatting.add("H2:H100", icon_set_rule)
workbook.save("sample_conditional_formatting_icon_set.xlsx")
data_bar_rule = DataBarRule(start_type="num",start_value=1,end_type="num",end_value="5",color=colors.GREEN)
sheet.conditional_formatting.add("H2:H100", data_bar_rule)
workbook.save("sample_conditional_formatting_data_bar.xlsx")
# Let's use the hello_world spreadsheet since it has less data
workbook = load_workbook(filename="hello_world.xlsx")
sheet = workbook.active
logo = Image("logo.png")
# A bit of resizing to not fill the whole spreadsheet with the logo
logo.height = 150
logo.width = 150
sheet.add_image(logo, "A3")
workbook.save(filename="hello_world_logo.xlsx")
ws.cell(row=2, column=5).value = ws.cell(row=2, column=4).value
for row in range(3, max_row + 1):
ws.cell(row=row, column=5).value = "=E{}+D{}".format(row - 1, row)
#列の幅を変更
ws.column_dimensions["A"].width = 15
ws.column_dimensions["B"].width = 30
ws.column_dimensions["C"].width = 15
ws.column_dimensions["D"].width = 15
ws.column_dimensions["E"].width = 15
#データバーの設定
rule = DataBarRule(start_type="percentile",
start_value=0,
end_type="percentile",
end_value=100,
color="FF638EC6",
showValue="None",
minLength=None,
maxLength=None)
ws.conditional_formatting.add(f"D2:D{max_row}", rule)
ws.conditional_formatting.add(f"E2:E{max_row}", rule)
#累計構成比を値として読み込むために、一度保存してからxlwingsで一度開く
wb.save("ABCanalysis.xlsx")
wbxl = xw.Book("ABCanalysis.xlsx")
wsxl = wbxl.sheets["ABCanalysis"]
#ランク定義
Arank = 0.7
Brank = 0.9
def beautify_excel():
'''
美化输出的excel文件，并将最大回撤率等数据写入excel文件。
'''
# 打开文件，读取先前输出的内容
result = openpyxl.load_workbook(f[:-4]+' [OUTPUT].xlsx')
table = result.worksheets[0]
nrows = table.max_row
# 准备样式（处理字体，取消边框）
font = Font(name='dengxian', size=12)
rule = DataBarRule(start_type='min', start_value=0, end_type='max', end_value=90,
color="FFFF0000", showValue="None", minLength=None, maxLength=None)
format_number = numbers.BUILTIN_FORMATS[40]
format_percent = numbers.BUILTIN_FORMATS[10]
# 设置 Sheet name，添加列名
table.title = '收益统计'
table['A1'].value = 'Open Signal'
table['B1'].value = 'Close Signal'
# 去除重复的列名，去除杂乱的边框
for row in range(nrows+1, 0, -1):
for j in range(len(table[row])):
table[row][j].border = Border(outline=False)
if table[row][j].value == table[1][j].value and row > 1:
table[row][j].value = None
# 加入数据条
table.conditional_formatting.add('C1:C'+str(nrows), rule)
# 设置列宽
table.column_dimensions['A'].width = 13
table.column_dimensions['B'].width = 13
table.column_dimensions['C'].width = 14
table.column_dimensions['D'].width = 14
table.column_dimensions['E'].width = 20
table.column_dimensions['F'].width = 20
table.column_dimensions['G'].width = 10
table.column_dimensions['H'].width = 8
table.column_dimensions['I'].width = 9.5
table.column_dimensions['J'].width = 8
table.column_dimensions['K'].width = 10
table.column_dimensions['L'].width = 9
table.column_dimensions['M'].width = 13
table.column_dimensions['N'].width = 13
table.column_dimensions['O'].width = 16
for c in ['E','H','G','J','M','N','O']:
for irow in range(2,nrows+1):
if table[c+str(irow)].value != None:
table[c+str(irow)].number_format = format_number
for c in ['D', 'L']:
for irow in range(2,nrows+1):
if table[c+str(irow)].value != None:
table[c+str(irow)].number_format = format_percent
temp = drawdown_by_time(t)
table['C'+str(nrows+2)].value='最大回撤'
table['D'+str(nrows+2)].value=temp[0]
table['D'+str(nrows+2)].number_format = format_percent
table['E'+str(nrows+2)].value=temp[1]
table['F'+str(nrows+2)].value=temp[2]
yearly_drawdown = temp[5];i=0
for year in yearly_drawdown.keys():
table[nrows+3+i][2].value = year
table[nrows+3+i][3].value = yearly_drawdown[year][0]
table[nrows+3+i][3].number_format = format_percent
table[nrows+3+i][4].value = yearly_drawdown[year][1]
table[nrows+3+i][5].value = yearly_drawdown[year][2]
i+=1
table['A'+str(nrows+2)].value='累计收益率'
table['B'+str(nrows+2)].value=temp[3]
table['B'+str(nrows+2)].number_format = format_percent
yearly_return = temp[4];i=0
for year in yearly_return.keys():
table[nrows+3+i][0].value = year
table[nrows+3+i][1].value = yearly_return[year]
table[nrows+3+i][1].number_format = format_percent
i+=1
#统计交易次数
def write_frequency(start_row, start_col):
table[start_row-1][start_col].value = '每日交易次数'
fd = frequency.describe()
for i in range(len(fd)):
table[start_row+i][start_col].value = fd.keys()[i]
table[start_row+i][start_col+1].value = fd[i]
table[start_row+i][start_col+1].number_format=format_number
write_frequency(nrows+3, 6)
for row in range(1,table.max_row+1):
for j in range(0,table.max_column):
try:
table[row][j].font = font
except:pass
# 插入日收益详情
ans = 每日浮动收益统计(symbol=global_symbol, Type=global_type)
daysheet = result.create_sheet(title='日收益详情')
daysheet.append(['date','浮动盈亏','已确认收益','累积收益（含浮动收益）','当日盈亏'])
for row in ans.index:
daysheet.append([row]+list(ans.loc[row]))
daygraphsheet = result.create_sheet(title='日收益图')
def generate_profit_curve():
import matplotlib.pyplot as plt
fig = plt.figure()
fig.set_size_inches(18, 12)
ax = fig.add_subplot(211)
ax.plot(ans.index,ans['comfirmed_profit'], linewidth=2, label='确认收益')
ax.plot(ans.index,ans['accumlated_profit'], linewidth=2, label='累积收益')
ax.fill_between(ans.index,ans.accumlated_profit, y2=0,
where=(ans.accumlated_profit<ans.accumlated_profit.shift(1))| \
((ans.accumlated_profit>ans.accumlated_profit.shift(-1))&(ans.accumlated_profit>=ans.accumlated_profit.shift(1))),
facecolor='grey',
alpha=0.3)
#最大回撤标注
ax.legend(fontsize=15)
plt.grid()
bx = fig.add_subplot(212)
bx.bar(ans.index, ans.当日盈亏.where(ans.当日盈亏>0), 2, label='当日盈亏', color='red',alpha=0.8)
bx.bar(ans.index, ans.当日盈亏.where(ans.当日盈亏<0), 2, label='当日盈亏', color='green',alpha=0.8)
bx.legend(fontsize=15)
plt.grid()
fig.savefig(f[:-4]+'Daily Profit.png', dpi=144)
generate_profit_curve()
graph = Image(f[:-4]+'Daily Profit.png')
daygraphsheet.append([' '])
daygraphsheet.add_image(graph, 'F1')
# 插入图表
wg = result.create_sheet(title='收益率平稳性')
graph = Image(f[:-4]+'AC figure.png')
wg.append([' '])
wg.add_image(graph, 'A1')
# 保存
result.save(f[:-4]+' [OUTPUT].xlsx')
print('已完成excel输出 文件路径 '+f[:-4]+' [OUTPUT].xlsx')
return 0
import random
from openpyxl import Workbook
from openpyxl.formatting.rule import DataBarRule
wb = Workbook()
ws = wb.active
values = random.sample(range(100), k=20)
for i, value in enumerate(values, 1):
column_a = ws.cell(i, 1)
column_a.value = value
row_length = len(values)
rule = DataBarRule( #←データバーの設定
start_type='percentile',
start_value=10,
end_type='percentile',
end_value='90',
color='0080FF',
showValue='None',
minLength=None,
maxLength=None)
ws.conditional_formatting.add(f'A1:A{row_length}', rule)
ws.title = 'データバー'
wb.save('data_bar.xlsx')
import openpyxl
from openpyxl.formatting.rule import IconSetRule, DataBarRule
# work_book = openpyxl.load_workbook("zomato_reviews.xlsx")
work_book = openpyxl.load_workbook("zomato-reviews.xlsx")
sheet = work_book.active
icon_set_rule = IconSetRule(icon_style="4Arrows",
type="num",
values=[1, 2, 3, 4])
print(sheet.max_row)
sheet.conditional_formatting.add("Q2:Q9558", icon_set_rule)
# work_book.save("zomato_iconset.xlsx")
work_book = openpyxl.load_workbook("zomato-reviews.xlsx")
sheet = work_book.active
data_bar_rule = DataBarRule(start_type="num",
start_value=1,
end_type="num",
end_value=4,
color="ff0000")
sheet.conditional_formatting.add("Q2:Q9558", data_bar_rule)
# work_book.save("zomato_databar.xlsx")
'''
Author: zhangpeiyu
Date: 2020-08-04 23:56:08
LastEditTime: 2020-08-05 23:57:33
Description: 我不是诗人，所以，只能够把爱你写进程序，当作不可解的密码，作为我一个人知道的秘密。
'''
from openpyxl import Workbook
import os
from openpyxl.styles import Font
# 数据条件格式
from openpyxl.formatting.rule import DataBarRule
rule = DataBarRule(start_type='percentile',
start_value=10,
end_type='percentile',
end_value='90',
color="FF638EC6",
showValue="None",
minLength=None,
maxLength=None)
rule.formula = ['NOT(ISERROR(SEARCH("highlight",A1)))']
'''
根据数据生成excel
'''
def createExcel(header=[],
data=[],
wb_name='wb-name',
sheet_name='sheet-name'):
# 创建Excel工作簿
wb = Workbook()
def create_second_sheet(wb, df: pd.DataFrame):
'''
2番目のシート作成
Paramaters
----------
wb : openpyxl.workbook
ワークブック
df : pd.DataFrame
分析対象のデータフレーム
'''
# 次のページ
ws = wb.create_sheet(title='統計量')
ws.sheet_properties.tabColor = '87cefa'
ee.set_cell(ws, cell=(2, 2), value='統計量', style='style_title')
tmp1 = pd.DataFrame(
{
'欠損': df.isnull().sum().values,
'欠損率(%)': df.isnull().sum().values / len(df) * 100
},
index=df.columns)
tmp2 = df.describe(include='all').T
tmp2.columns = [
'行数', 'ﾕﾆｰｸ数', '先頭行', '最頻値', '平均', '標準偏差', '最小', '25%', '中央値', '75%',
'最大'
]
tmp2 = tmp2.drop(['先頭行'], axis=1)
tmp3 = pd.DataFrame({'メモ': [np.nan] * len(df.columns)}, index=df.columns)
# 統計量記入
describe = pd.concat([tmp1, tmp2, tmp3], axis=1)
for c in describe.columns:
if c not in ['欠損', 'メモ']:
describe[c] = describe[c].astype(float).round(3)
ee.put_dataframe(ws, describe, start=(4, 2), mode='describe')
# データバー
ws.conditional_formatting.add(
f'D5:D{5+len(df.columns)}',
DataBarRule(start_type='num',
start_value=0,
end_type='num',
end_value='100',
color="b94047",
showValue="None",
minLength=None,
maxLength=None))
# 行数、列数
ee.set_cell(ws, cell=(3, 2), value='行', style='style_header')
ee.set_cell(ws, cell=(3, 3), value=df.shape[0], style='style_grid')
ee.set_cell(ws, cell=(3, 4), value='列', style='style_header')
ee.set_cell(ws, cell=(3, 5), value=df.shape[1], style='style_grid')
# 先頭、末尾
#ee.put_dataframe(ws, df.head(20), start=(19,2))
#ee.put_dataframe(ws, df.tail(20), start=(41,2))
# ウィンドウ枠の固定
ws.freeze_panes = 'C5'
# 幅調整
ee.adjust_width(ws)
ws.row_dimensions[1].height = 5
ws.column_dimensions['A'].width = 1
ws.column_dimensions['O'].width = 30
def conditional_formatting(ws, language_dict):
def get_entire_column(index):
return index + '1:' + index + '1048576'
red_color = 'ffc7ce'
green_color = 'c2efcf'
yellow_color = 'ffeba2'
red_fill = PatternFill(start_color=red_color,
end_color=red_color,
fill_type='solid')
green_fill = PatternFill(start_color=green_color,
end_color=green_color,
fill_type='solid')
yellow_fill = PatternFill(start_color=yellow_color,
end_color=yellow_color,
fill_type='solid')
ws.conditional_formatting.add(
get_entire_column(column_index[language_dict['difficulty']]),
CellIsRule(operator='equal',
formula=['"' + language_dict['level'][1] + '"'],
stopIfTrue=False,
fill=green_fill))
ws.conditional_formatting.add(
get_entire_column(column_index[language_dict['difficulty']]),
CellIsRule(operator='equal',
formula=['"' + language_dict['level'][2] + '"'],
stopIfTrue=False,
fill=yellow_fill))
ws.conditional_formatting.add(
get_entire_column(column_index[language_dict['difficulty']]),
CellIsRule(operator='equal',
formula=['"' + language_dict['level'][3] + '"'],
stopIfTrue=False,
fill=red_fill))
ws.conditional_formatting.add(
get_entire_column(column_index[language_dict['paid_only']]),
CellIsRule(operator='equal',
formula=['"' + language_dict['bool'][False] + '"'],
stopIfTrue=False,
fill=green_fill))
ws.conditional_formatting.add(
get_entire_column(column_index[language_dict['paid_only']]),
CellIsRule(operator='equal',
formula=['"' + language_dict['bool'][True] + '"'],
stopIfTrue=False,
fill=red_fill))
ws.conditional_formatting.add(
get_entire_column(column_index[language_dict['status']]),
CellIsRule(operator='equal',
formula=['"' + language_dict['bool'][False] + '"'],
stopIfTrue=False,
fill=red_fill))
ws.conditional_formatting.add(
get_entire_column(column_index[language_dict['status']]),
CellIsRule(operator='equal',
formula=['"' + language_dict['bool'][True] + '"'],
stopIfTrue=False,
fill=green_fill))
ws.conditional_formatting.add(
get_entire_column(column_index[language_dict['acceptance']]),
DataBarRule(start_type='percentile',
start_value=0,
end_type='percentile',
end_value=100,
color="FF638EC6",
showValue='None'))
from openpyxl.formatting.rule import DataBarRule, ColorScaleRule
from openpyxl import Workbook, load_workbook
from datetime import date, datetime
import operator, os
valrule = DataBarRule(
start_type='num',
start_value=0,
end_type='max',
color="FF638EC6",
showValue="None",
)
pcntrule = DataBarRule(
start_type='num',
start_value=0,
end_type='num',
end_value=1,
color="FF638EC6",
showValue="None",
)
humrule = DataBarRule(
start_type='num',
start_value=0,
end_type='num',
end_value=1,
color="FFF79646",
showValue="None",
)
def beautify_excel():
'''
美化输出的excel文件
'''
from openpyxl.styles import Font, Border, numbers
from openpyxl.formatting.rule import DataBarRule
from openpyxl.drawing.image import Image
# 打开文件，读取输出
result = openpyxl.load_workbook(f[:-4] + ' [OUTPUT].xlsx')
table = result.worksheets[0]
nrows = table.max_row
# 准备样式（处理字体，取消边框）
font = Font(name='dengxian', size=12)
rule = DataBarRule(start_type='min',
start_value=0,
end_type='max',
end_value=90,
color="FFFF0000",
showValue="None",
minLength=None,
maxLength=None)
format_number = numbers.BUILTIN_FORMATS[40]
format_percent = numbers.BUILTIN_FORMATS[10]
# 设置 Sheet name，添加列名
table.title = '收益统计'
table['A1'].value = 'Open Signal'
table['B1'].value = 'Close Signal'
# 去除重复的列名，去除杂乱的边框
for row in range(nrows + 1, 0, -1):
for j in range(len(table[row])):
table[row][j].border = Border(outline=False)
if table[row][j].value == table[1][j].value and row > 1:
table[row][j].value = None
# 加入数据条
table.conditional_formatting.add('C1:C' + str(nrows), rule)
# 设置列宽
table.column_dimensions['A'].width = 13
table.column_dimensions['B'].width = 13
table.column_dimensions['C'].width = 14
table.column_dimensions['D'].width = 14
table.column_dimensions['E'].width = 20
table.column_dimensions['F'].width = 20
table.column_dimensions['G'].width = 10
table.column_dimensions['H'].width = 8
table.column_dimensions['I'].width = 9.5
table.column_dimensions['J'].width = 8
table.column_dimensions['K'].width = 10
table.column_dimensions['L'].width = 9
table.column_dimensions['M'].width = 13
table.column_dimensions['N'].width = 13
table.column_dimensions['O'].width = 16
for c in ['E', 'H', 'G', 'J', 'M', 'N', 'O']:
for irow in range(2, nrows + 1):
if table[c + str(irow)].value != None:
table[c + str(irow)].number_format = format_number
for c in ['D', 'L']:
for irow in range(2, nrows + 1):
if table[c + str(irow)].value != None:
table[c + str(irow)].number_format = format_percent
#统计回撤
def total_summary(t: pd.DataFrame):
'''
计算最大回撤比例,以及累计收益率
返回结果：最大回撤率，开始日期，结束日期，总收益率
'''
t['Capital'] = float(t['价格'][1]) + t['Net Profit - Cum Net Profit']
yearly_drawdown = dict()
t['Year'] = pd.Series(t['Date/Time'][i].year
for i in range(len(t)))
t_group = t.groupby('Year')
year_groups = [t_group.get_group(i) for i in t_group.groups.keys()]
for year_group in year_groups:
max_draw_down, temp_max_value = 0, 0
start_date, end_date, current_start_date = 0, 0, 0
continous = False # 是否连续
for i in year_group.index:
if year_group['#'][i] > 0: continue
if temp_max_value < year_group['Capital'][i]:
current_start_date = year_group['Date/Time'][i]
temp_max_value = max(temp_max_value,
year_group['Capital'][i])
if max_draw_down > year_group['Capital'][
i] / temp_max_value - 1:
if not continous:
continous = True
max_draw_down = year_group['Capital'][
i] / temp_max_value - 1
else:
if continous:
continous = False
start_date = current_start_date
end_date = year_group['Date/Time'][i]
yearly_drawdown[year_group['Year']
[i]] = max_draw_down, start_date, end_date
yearly_return = dict() # 记录年收益率
max_draw_down, temp_max_value = 0, 0
start_date, end_date, current_start_date = 0, 0, 0
continous = False # 是否连续
for i in range(2, len(t), 2): # 偶数行的数据
if temp_max_value < t['Capital'][i - 2]:
current_start_date = t['Date/Time'][i]
temp_max_value = max(temp_max_value, t['Capital'][i - 2])
if max_draw_down > t['Capital'][i] / temp_max_value - 1:
if not continous:
continous = True
max_draw_down = t['Capital'][i] / temp_max_value - 1
else:
if continous:
continous = False
start_date = current_start_date
end_date = t['Date/Time'][i]
# 统计年收益率
year = t['Date/Time'][i].year
yearly_return[year] = t['Net Profit - Cum Net Profit'][i]
total_return = t['Capital'][i] / t['Capital'][0] - 1
yearly_return = pd.Series(yearly_return) / t['Capital'][0]
first_year = t['Date/Time'][1].year, yearly_return[t['Date/Time']
[1].year]
yearly_return = yearly_return.diff()
yearly_return[first_year[0]] = first_year[1]
return max_draw_down, start_date, end_date, total_return, pd.Series(
yearly_return), yearly_drawdown
temp = total_summary(t)
table['C' + str(nrows + 2)].value = '最大回撤'
table['D' + str(nrows + 2)].value = temp[0]
table['D' + str(nrows + 2)].number_format = format_percent
table['E' + str(nrows + 2)].value = temp[1]
table['F' + str(nrows + 2)].value = temp[2]
yearly_drawdown = temp[5]
i = 0
for year in yearly_drawdown.keys():
table[nrows + 3 + i][2].value = year
table[nrows + 3 + i][3].value = yearly_drawdown[year][0]
table[nrows + 3 + i][3].number_format = format_percent
table[nrows + 3 + i][4].value = yearly_drawdown[year][1]
table[nrows + 3 + i][5].value = yearly_drawdown[year][2]
i += 1
table['A' + str(nrows + 2)].value = '累计收益率'
table['B' + str(nrows + 2)].value = temp[3]
table['B' + str(nrows + 2)].number_format = format_percent
yearly_return = temp[4]
i = 0
for year in yearly_return.keys():
table[nrows + 3 + i][0].value = year
table[nrows + 3 + i][1].value = yearly_return[year]
table[nrows + 3 + i][1].number_format = format_percent
i += 1
#统计交易次数
def write_frequency(start_row, start_col):
table[start_row - 1][start_col].value = '每日交易次数'
fd = frequency.describe()
for i in range(len(fd)):
table[start_row + i][start_col].value = fd.keys()[i]
table[start_row + i][start_col + 1].value = fd[i]
table[start_row + i][start_col +
1].number_format = format_number
write_frequency(nrows + 3, 6)
for row in range(1, table.max_row + 1):
for j in range(0, table.max_column):
try:
table[row][j].font = font
except:
pass
# 插入图表
wg = result.create_sheet(title='收益率平稳性')
graph = Image(f[:-4] + 'AC figure.png')
wg.append([' '])
wg.add_image(graph, 'A1')
# 保存
result.save(f[:-4] + ' [OUTPUT].xlsx')
print('已完成excel输出 文件路径 ' + f[:-4] + ' [OUTPUT].xlsx')
return 0
def write_yeild_and_drawdown(temp: tuple, tablename: str):
print("Writing into excel file...")
result = openpyxl.Workbook()
table = result.active
# 样式
font = Font(name='fangsong', size=10)
rule1 = DataBarRule(start_type='percentile',
start_value=0,
end_type='percentile',
end_value=99,
color="FFFF0000",
showValue="None",
minLength=None,
maxLength=60)
rule2 = DataBarRule(start_type='percentile',
start_value=90,
end_type='percentile',
end_value=0,
color="FF22ae6b",
showValue="None",
minLength=None,
maxLength=60)
format_number = numbers.BUILTIN_FORMATS[40]
format_percent = numbers.BUILTIN_FORMATS[10]
thin = Side(border_style="thin", color="000000")
columns = ["", "收益率", "累计收益率", "最大回撤", "收益回撤比", "最大回撤发生时间段"]
table.append(columns)
# 写入回撤
yearly_drawdown = temp[5]
i = 0
ratio = dict()
for year in yearly_drawdown.keys():
table[2 + i][3].value = yearly_drawdown[year][0]
ratio[year] = yearly_drawdown[year][0]
table[2 + i][3].number_format = format_percent
try:
table[2 + i][5].value = "{} - {}".format(
yearly_drawdown[year][1].strftime("%Y.%m.%d"),
yearly_drawdown[year][2].strftime("%Y.%m.%d"))
except:
table[2 + i][5].value = "{} - {}".format(yearly_drawdown[year][1],
yearly_drawdown[year][2])
i += 1
# 写入收益
yearly_return = temp[4]
accumulated_return = (yearly_return + 1).cumprod() - 1
i = 0
for year in yearly_return.keys():
during_string = "{} - {}".format(
year_groups[i].index[0].strftime("%Y.%m"),
year_groups[i].index[-1].strftime("%Y.%m"))
table[2 + i][0].value = during_string
table[2 + i][1].value = yearly_return[year] # 写入年收益
ratio[year] = -yearly_return[year] / ratio[year]
table[2 + i][4].value = ratio[year] #写入收入回撤比
table[2 + i][4].number_format = format_number
table[2 + i][2].value = accumulated_return[year] # 写入累计收益
table[2 + i][1].number_format, table[
2 + i][2].number_format = format_percent, format_percent
i += 1
# 添加数据条
for col in range(6):
for row in range(len(year_groups) + 1):
table[row + 1][col].font = font
table.conditional_formatting.add('B{}:B{}'.format(2, i + 1), rule1)
table.conditional_formatting.add('D{}:D{}'.format(2, i + 1), rule2)
# 设置列宽
table.column_dimensions['A'].width = 16
table.column_dimensions['B'].width = 12
table.column_dimensions['C'].width = 12
table.column_dimensions['D'].width = 12
table.column_dimensions['E'].width = 12
table.column_dimensions['F'].width = 23
nrows = table.max_row
for row in range(nrows, 0, -1):
for j in range(len(table[row])):
table[row][j].border = Border(top=thin,
left=thin,
right=thin,
bottom=thin)
print("save to", r"./excel/%s_OUTPUT.xlsx" % tablename)
result.save(r"./excel/%s_OUTPUT.xlsx" % tablename)
return nrows #返回excel表格的最大行数