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表格的最大行数