def create_line_chart(self): c1 = LineChart() c1.title = chart_spend_title c1.style = 12 c1.y_axis.title = "Euros" c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'd-mm-yy' c1.x_axis.majorTimeUnit = "days" self.open_sheet(raw_chart_data_sheet) values = Reference(self.__working_sheet, min_row=1, max_row=self.__working_sheet.max_row, min_col=3, max_col=3) series = Series(values, title_from_data=True) c1.append(series) dates = Reference(self.__working_sheet, min_col=1, max_col=1, min_row=1, max_row=self.__working_sheet.max_row) c1.set_categories(dates) c1.height = 30 c1.width = 50 c2 = LineChart() c2.title = chart_topup_title c2.style = 12 c2.y_axis.title = "Euros" c2.y_axis.crossAx = 500 c2.x_axis = DateAxis(crossAx=100) c2.x_axis.number_category = 'Date' c2.x_axis.number_format = 'd-mm-yy' c2.x_axis.majorTimeUnit = "days" self.open_sheet(raw_chart_data_sheet) values = Reference(self.__working_sheet, min_row=1, max_row=self.__working_sheet.max_row, min_col=2, max_col=2) series = Series(values, title_from_data=True) c2.append(series) dates = Reference(self.__working_sheet, min_col=1, max_col=1, min_row=1, max_row=self.__working_sheet.max_row) c2.set_categories(dates) c2.height = 30 c2.width = 50 self.open_sheet(chart_sheet) self.__working_sheet.add_chart(c1, "C1") self.__working_sheet.add_chart(c2, "C60") self.__wb.save(self.__new_file_name) self.open_sheet(raw_data_sheet)
def mortgage_graphs(max_row_mortgage, wb): ws = wb['mortgage_rates'] ws1 = wb.create_sheet('mortgage_graph', index=0) """Line Chart.""" data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=max_row_mortgage) dates = Reference(ws, min_col=1, min_row=2, max_row=max_row_mortgage) c1 = LineChart() c1.title = "Freddie Mac vs Conforming and Non-Conforming Interest Rates" c1.y_axis.title = "Interest Rate" c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'mm-dd-yyyy' c1.x_axis.majorTimeUnit = "days" c1.x_axis.title = "Date" c1.height = 15 c1.width = 30 c1.add_data(data, titles_from_data=True) c1.set_categories(dates) ws1.add_chart(c1, "A1") """Deltas for line chart.""" ws1 = wb.create_sheet('mortgage_delta_graph', index=1) data = Reference(ws, min_col=5, min_row=1, max_col=6, max_row=max_row_mortgage) c2 = LineChart() c2.title = "Difference in Basis Points" c2.y_axis.title = "Basis Points" c2.y_axis.crossAx = 500 c2.x_axis = DateAxis(crossAx=100) c2.x_axis.number_format = 'mm-dd-yyyy' c2.x_axis.majorTimeUnit = "days" c2.x_axis.title = "Date" c2.height = 15 c2.width = 30 c2.x_axis.tickLblPos = "low" c2.add_data(data, titles_from_data=True) c2.set_categories(dates) ws1.add_chart(c2, "A1") # Saves workbook. wb.save(at.file_name)
def graphPlacer(ticker, stockSheet, stockData, sheet, minCol, minRow, maxCol): data = Reference(stockSheet, min_col=minCol + 1, min_row=minRow, max_col=maxCol + 1, max_row=len(stockData)) cats = Reference(stockSheet, min_col=1, min_row=3, max_col=1, max_row=len(stockData)) chart = LineChart() chart.title = None chart.legend = None chart.y_axis.title = "Stock Price" chart.y_axis.crossAx = 500 chart.x_axis = DateAxis() chart.x_axis.number_format = 'yyyy' chart.x_axis.title = "Date" chart.add_data(data) chart.set_categories(cats) sheet.add_chart(chart, 'E4')
def graph_fca(ws, _len): c1 = LineChart() c1.title = "First Call Activation" c1.y_axis.title = "Number of FCA" c1.x_axis.title = "Date" c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None data = Reference(ws, min_col=2, min_row=3, max_row=_len) c1.add_data(data) dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) c1.width = 20 # Style the lines s1 = c1.series[0] s1.marker.symbol = "circle" s1.marker.graphicalProperties.solidFill = "FF0000" # Marker filling s1.marker.graphicalProperties.line.solidFill = "DDEBF7" # Marker outline s1.graphicalProperties.line.noFill = False ws.add_chart(c1, "D3") return ws
def __init__(self, worksheet, title): self.chart = LineChart() self.ws = worksheet self.chart.title = title self.chart.y_axis.crossAx = 500 self.chart.x_axis = DateAxis(crossAx=100) self.chart.x_axis.number_format = 'd-mmm' self.chart.x_axis.majorTimeUnit = "days"
def modify1002excel(filename='c:/1.xlsx', assets=None, hs300price=None, fundunit=None): wb = load_workbook(filename=filename) ws = wb['基金资产明细'] nrows = len(list(ws.rows)) ncolumns = len(list(ws.columns)) now = datetime.datetime.today().replace(hour=0, minute=0, second=0, microsecond=0) newrow = nrows + 1 ws.cell(row=newrow, column=9).value = hs300price ws.cell(row=newrow, column=8).value = assets ws.cell(row=newrow, column=7).value = 0 ws.cell(row=newrow, column=6).value = 0 ws.cell(row=newrow, column=5).value = assets ws.cell(row=newrow, column=4).value = fundunit ws.cell(row=newrow, column=3).value = hs300price / ws.cell(row=2, column=9).value ws.cell(row=newrow, column=2).value = ws.cell( row=newrow, column=5).value / ws.cell(row=newrow, column=4).value ws.cell(row=newrow, column=1).value = now for cx in range(1, ncolumns + 1): ws.cell(row=newrow, column=cx).number_format = ws.cell(row=nrows, column=cx).number_format data = Reference(ws, min_col=2, min_row=1, max_col=3, max_row=365) # Chart with date axis c2 = LineChart() c2.title = "擎天柱基金净值走势图" c2.style = 2 # c2.y_axis.title = "Size" c2.y_axis.crossAx = 500 c2.x_axis = DateAxis(crossAx=100) c2.x_axis.number_format = 'm-d' c2.x_axis.majorTimeUnit = "months" # c2.x_axis.title = "Date" c2.add_data(data, titles_from_data=True) dates = Reference(ws, min_col=1, min_row=2, max_row=365) c2.set_categories(dates) # ws.add_chart(c2, "B%d" % (nrows+2)) ws1 = wb['净值走势图'] ws1.add_chart(c2, "B3") wb.save(filename) # os.system(filename) #打开excel文件手工确认数据 return 0
def line_graph(): """ 生成折线图 :return: """ # 取某时间段内每天的数据做折线图 start_date = '2020-08-10 00:00:00' end_date = '2020-08-30 23:59:59' # 读取数据 file_path = base_path + file_name # 读取指定文件内的数据 df = pd.read_excel(file_path, sheet_name=sheet_name) # 获取时间段里的数据 filter_data = df[(df['创建日期'] >= start_date) & (df['创建日期'] <= end_date)] # 准备格式化时间,做分组统计 # filter_data['创建日期'] = [datetime.strftime(x, '%Y-%m-%d') for x in filter_data['创建日期']] # filter_data['创建日期'] = pd.to_datetime(filter_data['创建日期']) count_data = filter_data.groupby([filter_data['创建日期']], as_index=False)['ID'].count() # 转换时间格式,并重新组装数据 rows = [('日期', '缺陷数量')] char_index = 1 for row in count_data.itertuples(): date_str = getattr(row, '创建日期') # print(date_str.strftime('%m月%d日'), getattr(row, 'ID')) rows.append((date_str.strftime('%m月%d日'), getattr(row, 'ID'))) char_index = char_index + 1 # 打开文件,创建新的sheet wb = load_workbook(file_path) ws = wb.create_sheet('每日缺陷曲线') # 添加数据 for row in rows: ws.append(row) # 准备画折线图 # Chart with date axis chart = LineChart() chart.title = "每日缺陷曲线" chart.style = 12 # chart.y_axis.title = "Size" chart.y_axis.crossAx = 500 chart.x_axis = DateAxis(crossAx=100) # chart.x_axis.number_format = 'd-mmm' # chart.x_axis.majorTimeUnit = "days" # chart.x_axis.title = "Date" # 图像的数据 起始行、起始列、终止行、终止列 data = Reference(ws, min_col=2, min_row=1, max_col=2, max_row=char_index) chart.add_data(data, titles_from_data=True) dates = Reference(ws, min_col=1, min_row=2, max_row=char_index) chart.set_categories(dates) # 将图表添加到 sheet中 ws.add_chart(chart, "A{0}".format(char_index + 6)) wb.save(file_path)
def create_chart(name, x_axis, values): chart = LineChart() chart.title = name chart.x_axis.title = "Tijd" chart.x_axis.number_format = '%d-%m-%Y %H:%M:%S' chart.x_axis = DateAxis(crossAx=100) chart.x_axis.majorTimeUnit = "days" chart.y_axis.title = "EUR" chart.y_axis.crossAx = 500 chart.legend = None chart.add_data(values) chart.set_categories(x_axis) return chart
def update_excel_graph(self): self.update_excel() x_axis_max_scale = 70 if self.points_added == x_axis_max_scale: self.minrow = self.minrow + self.points_added self.points_added = 0 self.anchor_point = self.anchor_point + 2 * self.chart_height + 4 values = Reference(ws, min_col=2, min_row="%s" % self.minrow, max_row=ws.max_row) chart = LineChart() chart.height = self.chart_height chart.width = self.chart_width chart.style = 13 chart.y_axis.crossAx = 500 chart.y_axis.title = 'Avg Uptime' chart.x_axis = DateAxis(crossAx=100) chart.x_axis.title = 'Date' chart.x_axis.scaling.max = x_axis_max_scale chart.x_axis.scaling.min = 1 chart.x_axis.majorTickMark = 'cross' # as per our scaling.max value the tickers will be shown on x-axis # if scaling.max = 70, then 70 tickers will be shown on x-axis. chart.x_axis.majorGridlines = ChartLines() chart.y_axis.majorGridlines = ChartLines() chart.add_data(values, titles_from_data=True) dates = Reference(ws, min_col=1, min_row="%s" % self.minrow, max_row=ws.max_row) chart.set_categories(dates) # dates set a x-axis try: ws.add_chart( chart, "E%s" % self.anchor_point ) # e.g.'E1' is anchor pint where graph starts in sheet wb.save(filename=dest_filename) self.points_added += 1 except PermissionError: print( 'We didn\'t get permission to write to excel file, may it is open somewhere.' )
def treasury_delta_data(max_row_delta, wb): """Treasury Delta Data.""" # Make sure that there are the same number of items in the list of # titles as well as the list of chart cells (top left hand cell # for each graph) if len(at.title) != len(at.chart_cell): print('The length of the title list is not same as \ the length of the chart_cell list.') return ws = wb['treasury_delta_data'] ws1 = wb.create_sheet('delta_graphs', index=2) dates = Reference(ws, min_col=1, min_row=2, max_row=max_row_delta) for i in range(len(at.title)): data = Reference(ws, min_col=i + 2, min_row=1, max_row=max_row_delta) c1 = LineChart() c1.x_axis.title = "Date" c1.y_axis.title = "Daily Delta" c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'mm-dd-yyyy' c1.x_axis.majorTimeUnit = "days" c1.x_axis.tickLblPos = "low" c1.add_data(data, titles_from_data=True) c1.set_categories(dates) c1.legend = None # Get title information and top left hand corner cell info # from the lists in at.py c1.title = at.title[i] ws1.add_chart(c1, at.chart_cell[i]) # Save workbook wb.save(at.file_name)
def add_chart(chart_sheet, data_key, mean_col, avg_col, write_sheet, date_range, data_types, col_choice, offset): c = LineChart() c.display_blanks = 'span' c.title = data_key.upper() c.style = 2 c.x_axis.title = 'Date' c.x_axis = DateAxis(crossAx=100) c.y_axis.crossAx = 500 c.x_axis.number_format = 'm/d/y' c.x_axis.majorTimeUnit = 'months' avg_mean_data = Reference(write_sheet, min_col=avg_col, max_col=avg_col, min_row=1, max_row=date_range + 1) mean_data = Reference(write_sheet, min_col=mean_col, max_col=mean_col, min_row=1, max_row=date_range + 1) c.add_data(avg_mean_data, titles_from_data=True) c.add_data(mean_data, titles_from_data=True) dates = Reference(write_sheet, min_col=1, max_col=1, min_row=2, max_row=date_range + 1) c.set_categories(dates) # Add to either col A or col J if not col_choice: chart_sheet.add_chart( c, 'A' + str(15 * (data_types[data_key] - 3) + 1 - offset * 14)) else: chart_sheet.add_chart( c, 'J' + str(15 * (data_types[data_key] - 4) + 1 - offset * 14)) offset += 1 return offset
def graph_rbs(ws, _len): # create chart c1 = LineChart() c1.title = 'Total Recharge (SBD)' c1.y_axis.title = 'Recharge (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=3, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'F2') return ws
def plot_graph(rows, name): wb = Workbook() ws = wb.active for row in rows: ws.append(row) c2 = LineChart() c2.height = 12 c2.width = 25 c2.title = "LTP growth" c2.style = 7 #7,8,9 c2.y_axis.title = "LTP" c2.y_axis.crossAx = 500 c2.x_axis = DateAxis(crossAx=100) c2.x_axis.number_format = 'd-mmm' c2.x_axis.majorTimeUnit = "days" c2.x_axis.title = "Date" data = Reference(ws, min_col=2, min_row=1, max_col=ws.max_column, max_row=ws.max_row) c2.add_data(data, titles_from_data=True) dates = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row) c2.set_categories(dates) x = 0 while x != ws.max_column - 1: s2 = c2.series[x] s2.graphicalProperties.line.dashStyle = "sysDot" #s2.smooth = True # Make the line smooth x += 1 ws.add_chart(c2, "A1") wb.save(name)
def graph_sd(ws, _len): _len = _len + 1 #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'Global' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=3, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'A38') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'Moa Day ($7)' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=6, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'I38') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'Moa 2 Days ($14)' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=9, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'Q38') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'Moa 3 Days ($21)' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=12, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'A56') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'Moa Week ($42)' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=15, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'I56') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'Moa Month ($500)' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=18, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'Q56') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'D6 ($6)' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=21, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'A74') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'Hour Data ($10)' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=24, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'I74') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'D15 ($15)' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=27, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'Q74') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'D20 ($20)' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=33, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'A92') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'Movie Night ($35)' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=36, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'I92') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'Week Data ($50)' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=39, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'Q92') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'D90 ($90)' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=42, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'A110') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'D220 ($220)' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=45, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'I110') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'D500 ($500)' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=48, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'Q110') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'Roaming Bundle' c1.y_axis.title = 'Amount (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=51, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'A128') #-----------------------------------------------| return ws
def graph_im(ws, _len): _len = _len + 1 #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'Incoming Onnet' c1.y_axis.title = 'Minutes' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=2, min_row=4, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=4, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'I2') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'Incoming Offnet' c1.y_axis.title = 'Minutes' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=4, min_row=4, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=4, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'I18') #-----------------------------------------------| # create chart c1 = LineChart() c1.title = 'Incoming PSTN' c1.y_axis.title = 'Minutes' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=6, min_row=4, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=4, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False # add chart to worksheet ws.add_chart(c1, 'I34') return ws
[(datetime.now() + timedelta(seconds=4)).strftime("%m/%d/%Y-%H:%M:%S"), 25, 35, 30], [(datetime.now() + timedelta(seconds=5)).strftime("%m/%d/%Y-%H:%M:%S"), 20, 40, 35], ] for row in rows: ws.append(row) data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=ws.max_row) c2 = LineChart() c2.title = "Date Axis" c2.style = 13 c2.y_axis.title = "Size" c2.y_axis.crossAx = 500 # this is mandatory c2.x_axis = DateAxis(crossAx=100) # this ia mandatory c2.x_axis.number_format = 'dd/mm/yy-HH:MM' c2.x_axis.majorTimeUnit = "days" c2.x_axis.title = "Date" c2.x_axis.scaling.min = 2 c2.x_axis.scaling.max = 10 c2.x_axis.minorGridlines = ChartLines() c2.x_axis.majorTickMark = 'cross' c2.add_data(data, titles_from_data=True) # in below we tell where the dates are, in our example dates are in # 1st column dates = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row) c2.set_categories(dates)
def line_dates( self, ws: Worksheet, li_dates_md_created: list = None, li_dates_md_modified: list = None, cell_start_table: str = "O1", cell_start_chart: str = "S1", ): """Calculates metadata creation and modification dates repartition and add a \ Line chart to the wanted sheet of Workbook. :param Worksheet ws: sheet of a Workbook to write analisis :param list li_dates_md_created: list of metadatas'creation dates. If not specified, the class attribute will be used instead. :param list li_dates_md_modified: list of metadatas'modification dates. If not specified, the class attribute will be used instead. :param str cell_start_table: cell of the sheet where to start writing table :param str cell_start_chart: cell of the sheet where to start writing the chart """ # use passed lists or class ones if li_dates_md_created is None: li_dates_md_created = self.li_dates_md_created if li_dates_md_modified is None: li_dates_md_modified = self.li_dates_md_modified # compare lists # length if len(li_dates_md_created) != len(li_dates_md_modified): logger.warning( "Dates lists should have the same length. Creation: {} | Modification: {}" .format(len(li_dates_md_created), len(li_dates_md_modified))) total_dates = len(set(li_dates_md_created + li_dates_md_modified)) # common logger.debug( "{}/{} dates with both metadata creation and modification.".format( len( set(li_dates_md_created).intersection( li_dates_md_modified)), total_dates, )) # difference logger.debug("{}/{} dates with only metadata creation.".format( len(set(li_dates_md_created).difference(li_dates_md_modified)), total_dates, )) logger.debug("{}/{} dates with only metadata modification.".format( len(set(li_dates_md_modified).difference(li_dates_md_created)), total_dates, )) # use a named tuple DateFrequency = namedtuple( "DateFrequency", ["date", "count_md_created", "count_md_modified"]) # parse dates count_creation = Counter(li_dates_md_created) count_update = Counter(li_dates_md_modified) itr_dates_frequency = [] for crea, mod in zip_longest(sorted(count_creation), sorted(count_update), fillvalue=0): if crea == mod: # means a day with both metadata creation and modification itr_dates_frequency.append( DateFrequency(crea, count_creation.get(crea), count_update.get(mod))) elif crea == 0: print("creation empty: {}".format(count_creation.get(crea))) itr_dates_frequency.append( DateFrequency(mod, 0, count_update.get(mod))) elif mod == 0: print("modification empty: {}".format(count_update.get(mod))) itr_dates_frequency.append( DateFrequency(crea, count_creation.get(crea), 0)) else: itr_dates_frequency.append( DateFrequency(crea, count_creation.get(crea), 0)) itr_dates_frequency.append( DateFrequency(mod, 0, count_update.get(mod))) # get starting cells min_cell_start_table = ws[cell_start_table] # write headers ws.cell( row=min_cell_start_table.row, column=min_cell_start_table.column, value=self.tr.get("date", "Date"), ) ws.cell( row=min_cell_start_table.row, column=min_cell_start_table.column + 1, value="{} - {}".format(self.tr.get("occurrences"), self.tr.get("_created")), ) ws.cell( row=min_cell_start_table.row, column=min_cell_start_table.column + 2, value="{} - {}".format(self.tr.get("occurrences"), self.tr.get("_modified")), ) # write data into worksheet row = min_cell_start_table.row for date_freq in sorted(itr_dates_frequency): row += 1 ws.cell(row=row, column=min_cell_start_table.column, value=date_freq.date) ws.cell( row=row, column=min_cell_start_table.column + 1, value=date_freq.count_md_created, ) ws.cell( row=row, column=min_cell_start_table.column + 2, value=date_freq.count_md_modified, ) # Chart with date axis dates_chart = LineChart() labels = Reference( worksheet=ws, min_col=min_cell_start_table.column, min_row=min_cell_start_table.row + 1, max_row=row, ) data = Reference( worksheet=ws, min_col=min_cell_start_table.column + 1, max_col=min_cell_start_table.column + 2, min_row=min_cell_start_table.row, max_row=row, ) dates_chart.add_data(data, titles_from_data=1) dates_chart.set_categories(labels) # custom chart dates_chart.title = self.tr.get("date", "Date") # dates_chart.style = 2 # dates_chart.smooth = True dates_chart.height = 10 # default is 7.5 dates_chart.width = 30 # default is 15 dates_chart.y_axis.title = self.tr.get("occurrences") dates_chart.y_axis.crossAx = 500 dates_chart.x_axis = DateAxis(crossAx=100) dates_chart.x_axis.number_format = "mmm-y" dates_chart.x_axis.majorTimeUnit = "days" dates_chart.x_axis.title = "Date" # insert chart into the worksheet at the specified anchor ws.add_chart(dates_chart, cell_start_chart)
stacked.grouping = "stacked" stacked.title = "Stacked Line Chart" ws.add_chart(stacked, "A27") percent_stacked = deepcopy(c1) percent_stacked.grouping = "percentStacked" percent_stacked.title = "Percent Stacked Line Chart" ws.add_chart(percent_stacked, "A44") # Chart with date axis c2 = LineChart() c2.title = "Date Axis" c2.style = 12 c2.y_axis.title = "Size" c2.y_axis.crossAx = 500 c2.x_axis = DateAxis(crossAx=100) c2.x_axis.number_format = 'd-mmm' c2.x_axis.majorTimeUnit = "days" c2.x_axis.title = "Date" c2.add_data(data, titles_from_data=True) dates = Reference(ws, min_col=1, min_row=2, max_row=7) c2.set_categories(dates) ws.add_chart(c2, "A61") wb.save("Church_Report.xlsx")
def dataFocus(hempBenchmarksFile, monthAndYear, prevMonthFile): # i.e. 'dataFocus('HempBenchmarksApril2020.xlsx', 'September 2020', 'NovHempPricing.xlsx') # open both excel files hb = openpyxl.load_workbook(hempBenchmarksFile, data_only = True) prevReport = openpyxl.load_workbook(prevMonthFile) newBook = openpyxl.Workbook() newBook.save(filename = 'HempPricing.xlsx') openpyxl.load_workbook('HempPricing.xlsx') # save sheet 1 as activeSheet activeSheet = hb['Sheet1'] # edit new workbook sheet names newSheet1 = newBook['Sheet'] newSheet1.title = monthAndYear[0:3] + ' ' + monthAndYear[-4:] + ' Data' newBook.create_sheet('Sheet2') newSheet2 = newBook['Sheet2'] newSheet2.title = monthAndYear[0:3] + ' ' + monthAndYear[-4:] + ' Low Price Graph' newBook.create_sheet('Sheet3') newSheet3 = newBook['Sheet3'] newSheet3.title = monthAndYear[0:3] + ' ' + monthAndYear[-4:] + ' High Price Graph' newBook.create_sheet('Sheet4') newSheet4 = newBook['Sheet4'] newSheet4.title = 'Updated Line Graph' # edit prevMonthFile sheet names prevSheet4 = prevReport[prevReport.sheetnames[3]] newSheet4.column_dimensions['A'].width = 20 # Writing to cells in newSheet1 newSheet1['A1'] = 'U.S. Region Products' newSheet1['A1'].font = Font(bold = True) newSheet1['B1'] = 'Assessed Price' newSheet1['B1'].font = Font(bold = True) newSheet1['C1'] = 'Low' newSheet1['C1'].font = Font(bold = True) newSheet1['D1'] = 'High' newSheet1['D1'].font = Font(bold = True) newSheet1.column_dimensions['A'].width = 28 newSheet1.column_dimensions['B'].width = 13 # Writing to cells in newSheet2 newSheet2['A1'] = 'U.S. Region Products' newSheet2['A1'].font = Font(bold = True) newSheet2['B1'] = 'Low' newSheet2['B1'].font = Font(bold = True) newSheet2['C1'] = 'Assessed Price' newSheet2['C1'].font = Font(bold = True) newSheet2['D1'] = 'High' newSheet2['D1'].font = Font(bold = True) newSheet2.column_dimensions['A'].width = 28 newSheet2.column_dimensions['C'].width = 13 # Writing to cells in newSheet3 newSheet3['A1'] = 'U.S. Region Products' newSheet3['A1'].font = Font(bold = True) newSheet3['B1'] = 'Low' newSheet3['B1'].font = Font(bold = True) newSheet3['C1'] = 'Assessed Price' newSheet3['C1'].font = Font(bold = True) newSheet3['D1'] = 'High' newSheet3['D1'].font = Font(bold = True) newSheet3.column_dimensions['A'].width = 28 newSheet3.column_dimensions['C'].width = 13 # Writing to cells in newSheet4 newSheet4.column_dimensions['A'].width = 11 newSheet4.column_dimensions['B'].width = 14 newSheet4.column_dimensions['C'].width = 14 newSheet4.column_dimensions['D'].width = 20 newSheet4.column_dimensions['E'].width = 18 # Find the range of rows that the data is contained in (minRow to maxRow) for i in range(1, 45+1): if activeSheet.cell(row = i, column = 1).value == 'CBD Biomass (Aggregate)': minRow = i if activeSheet.cell(row = i, column = 1).value == 'CBG Isolate': maxRow = i # Populate data into newSheet1 (all data) lowIterNum = 0 highIterNum = 0 y = 2 lowRow = 2 highRow = 2 for i in range(minRow,maxRow+1): # cycles through each row with data if activeSheet.cell(row = i, column = 4).value != None: # removes empty rows if activeSheet.cell(row = i, column = 4).value != 'Assessed Price': # removes title row # print(activeSheet.cell(row = i, column = 4).value) if activeSheet.cell(row = i, column = 4).value < 50: # for all rows cheaper than $50 lowIterNum += 1 lowProduct = activeSheet.cell(row = i, column = 1).value lowPriceAssessed = activeSheet.cell(row = i, column = 4).value lowPriceLow = activeSheet.cell(row = i, column = 5).value lowPriceHigh = activeSheet.cell(row = i, column = 6).value newSheet1.cell(row = y, column = 1).value = lowProduct newSheet1.cell(row = y, column = 2).value = lowPriceAssessed newSheet1.cell(row = y, column = 3).value = lowPriceLow newSheet1.cell(row = y, column = 4).value = lowPriceHigh y+=1 newSheet2.cell(row = lowRow, column = 1).value = lowProduct newSheet2.cell(row = lowRow, column = 2).value = lowPriceLow newSheet2.cell(row = lowRow, column = 3).value = lowPriceAssessed newSheet2.cell(row = lowRow, column = 4).value = lowPriceHigh lowRow+=1 else: highIterNum += 1 highProduct = activeSheet.cell(row = i, column = 1).value highPriceAssessed = activeSheet.cell(row = i, column = 4).value highPriceLow = activeSheet.cell(row = i, column = 5).value highPriceHigh = activeSheet.cell(row = i, column = 6).value newSheet1.cell(row = y, column = 1).value = highProduct newSheet1.cell(row = y, column = 2).value = highPriceAssessed newSheet1.cell(row = y, column = 3).value = highPriceLow newSheet1.cell(row = y, column = 4).value = highPriceHigh y+=1 newSheet3.cell(row = highRow, column = 1).value = highProduct newSheet3.cell(row = highRow, column = 2).value = highPriceLow newSheet3.cell(row = highRow, column = 3).value = highPriceAssessed newSheet3.cell(row = highRow, column = 4).value = highPriceHigh highRow+=1 # At this point newSheet1 is complete! # At this point newSheet2 and newSheet3 have the correct data, just need graphs! # SHEET 4 STUFF... # Copy data from prevMonthFile to newSheet4 for x in range(1,9): for y in range(1,6): newSheet4.cell(row = x, column = y).value = prevSheet4.cell(row = y, column = x).value # Save the month being pushed out off to the side for y in range(1,6): newSheet4.cell(row = 15, column = y).value = newSheet4.cell(row = 2, column = y).value # Update the cells by moving them all back 1 month for x in range(1,6): for y in range(3,9): newSheet4.cell(row = y-1, column = x).value = newSheet4.cell(row = y, column = x).value # Put in the new month's data for all 4 benchmarks newSheet4.cell(row = 7, column = 1).value = ' ' + monthAndYear[0:3] + ' ' + monthAndYear[-4:] for i in range(1,25): if newSheet1.cell(row = i, column = 1).value == 'Industrial Seeds': indSeedRow = i if newSheet1.cell(row = i, column = 1).value == 'CBD Clones': cloneRow = i if newSheet1.cell(row = i, column = 1).value == 'CBD Seeds (Feminized)': femSeedRow = i if newSheet1.cell(row = i, column = 1).value == 'CBD Biomass (Aggregate)': agMassRow = i indSeedPrice = newSheet1.cell(row = indSeedRow, column = 2).value clonePrice = newSheet1.cell(row = cloneRow, column = 2).value femSeedPrice = newSheet1.cell(row = femSeedRow, column = 2).value agMassPrice = newSheet1.cell(row = agMassRow, column = 2).value newSheet4.cell(row = 7, column = 2).value = indSeedPrice newSheet4.cell(row = 7, column = 3).value = clonePrice newSheet4.cell(row = 7, column = 4).value = femSeedPrice newSheet4.cell(row = 7, column = 5).value = agMassPrice # Place holders for the forecast newSheet4.cell(row = 8, column = 1).value = 'Forecasted Month' newSheet4.cell(row = 8, column = 2).value = 2.5 newSheet4.cell(row = 8, column = 3).value = 2.5 newSheet4.cell(row = 8, column = 4).value = 2.5 newSheet4.cell(row = 8, column = 5).value = 2.5 # Now delete extra data that I dont want graphed in newSheet2 for i in range(1,15): if newSheet2.cell(row = i, column = 1).value == 'CBD Biomass (0 - 25K pounds)': newSheet2.delete_rows(i) if newSheet2.cell(row = i, column = 1).value == 'CBD Biomass (25K - 100K pounds)': newSheet2.delete_rows(i) if newSheet2.cell(row = i, column = 1).value == 'CBD Biomass (100K - 1M pounds)': newSheet2.delete_rows(i) if newSheet2.cell(row = i, column = 1).value == 'CBD Biomass (1M+ pounds)': newSheet2.delete_rows(i) if newSheet2.cell(row = i, column = 1).value == 'CBG Seeds': newSheet2.delete_rows(i) if newSheet2.cell(row = i, column = 1).value == 'CBG Clones': newSheet2.delete_rows(i) # Now delete extra data that I dont want graphed in newSheet3 for i in range(1,15): if newSheet3.cell(row = i, column = 1).value == 'Distillate - THC Free': newSheet3.delete_rows(i) if newSheet3.cell(row = i, column = 1).value == 'Distillate - Broad Spectrum': newSheet3.delete_rows(i) # At this point the data for ALL 4 SHEETS ARE COMPLETE # Now we need graphs for sheets 2, 3, and 4... # Making the graph for sheet2 chart1 = BarChart() chart1.type = 'bar' chart1.style = 10 chart1.y_axis.title = 'USD' data = Reference(newSheet2, min_col=2, min_row=1, max_row=6, max_col=4) cats = Reference(newSheet2, min_col=1, min_row=2, max_row=7) chart1.add_data(data, titles_from_data=True) chart1.set_categories(cats) chart1.shape = 4 y = chart1.height x = chart1.width chart1.height = y * 1.25 chart1.width = x * 1.25 newSheet2.add_chart(chart1, 'C9') # Making the graph for sheet3 chart2 = BarChart() chart2.type = 'bar' chart2.style = 10 chart2.y_axis.title = 'USD' data = Reference(newSheet3, min_col=2, min_row=1, max_row=9, max_col=4) cats = Reference(newSheet3, min_col=1, min_row=2, max_row=10) chart2.add_data(data, titles_from_data=True) chart2.set_categories(cats) chart2.shape = 4 y2 = chart2.height x2 = chart2.width chart2.height = y2 * 1.25 chart2.width = x2 * 1.25 newSheet3.add_chart(chart2, 'C12') # At this point, SHEETS 1, 2, AND 3 ARE NOW COMPLETED! now for pesky sheet 4... # All we have left is the sheet 4 graph: lineChart = LineChart() lineChart.style = 12 lineChart.y_axis.title = 'USD' lineChart.y_axis.crossAx = 500 lineChart.x_axis = DateAxis(crossAx=100) lineChart.x_axis.number_format = 'd-mmm' lineChart.x_axis.majorTimeUnit = 'days' prices = Reference(newSheet4, min_col=2, min_row=1, max_col=5, max_row=8) lineChart.add_data(prices, titles_from_data=True) dates = Reference(newSheet4, min_col=1, min_row=2, max_row=8) lineChart.set_categories(dates) newSheet4.add_chart(lineChart, "G2") # Save the new file newBook.save(monthAndYear[0:3] + 'HempPricing.xlsx') # close all files hb.close() newBook.close() prevReport.close()
#### chart.x_axis = DateAxis() #### chart.x_axis.majorTimeUnit = "days" ## chart.x_axis.number_format ='mm/dd/yyyy' ## data = Reference(sheet, min_col=4, min_row=2, max_row=sheet.max_row,) ## title = Reference(sheet, min_col=3, min_row=2, max_row=sheet.max_row) ## chart.add_data(data) ## chart.set_categories(title) ## sheet.add_chart(chart, "A10") chart = BarChart() chart.title = name chart.style = 10 chart.x_axis.title = 'Date' chart.y_axis.title = 'Count' chart.y_axis.crossAx = 500 chart.x_axis = DateAxis(crossAx=100) chart.x_axis.number_format = 'yyyy/mm/dd' chart.x_axis.majorTimeUnit = "days" data = Reference(sheet, min_col=4, min_row=1, max_row=sheet.max_row) chart.add_data(data, titles_from_data=True) dates = Reference(sheet, min_col=3, min_row=2, max_row=sheet.max_row) chart.set_categories(dates) sheet.add_chart(chart, "G9") sheet[ 'G2'].value = 'SUM(D:D)/NETWORKDAYS(MIN(C:C),TODAY(),DATEVALUE({"2017/09/04","2017/09/21","2017/10/19","2017/10/20","2017/11/10","2017/11/22","2017/11/23","2017/11/24","2017/12/22","2017/12/25","2017/12/26","2017/12/27","2017/12/28","2018/12/29","2018/01/01","2018/01/02","2018/01/03","2018/01/04","2018/01/08","2018/01/15","2018/02/19","2018/03/23","2018/03/26","2018/03/27","2018/03/28","2018/03/29","2018/03/30","2018/05/25","2018/05/28","2018/09/03","2018/09/10","2018/09/19","2018/10/19","2018/11/06","2018/11/12","2018/11/22","2018/11/21","2018/11/23","2018/12/24","2018/12/25","2018/12/26","2018/12/27","2018/12/28","2018/12/31","2019/01/01","2019/01/02","2019/01/03","2019/01/04","2019/01/07","2019/01/21","2019/02/18","2019/03/22","2019/03/28","2019/03/27","2019/03/25","2019/03/26","2019/03/27","2019/03/29","2019/04/19","2019/05/27","2018/06/06","2018/06/07","2018/06/08","2018/06/09","2018/06/10","2018/06/11","2018/06/12","2018/06/13","2018/06/14","2018/06/15","2018/06/16","2018/06/17","2018/06/18","2018/06/19","2018/06/20","2018/06/21","2018/06/22","2018/06/23","2018/06/24","2018/06/25","2018/06/26","2018/06/27","2018/06/28","2018/06/29","2018/06/30","2018/07/01","2018/07/02","2018/07/03","2018/07/04","2018/07/05","2018/07/06","2018/07/07","2018/07/08","2018/07/09","2018/07/10","2018/07/11","2018/07/12","2018/07/13","2018/07/14","2018/07/15","2018/07/16","2018/07/17","2018/07/18","2018/07/19","2018/07/20","2018/07/21","2018/07/22","2018/07/23","2018/07/24","2018/07/25","2018/07/26","2018/07/27","2018/07/28","2018/07/29","2018/07/30","2018/07/31","2018/08/01","2018/08/02","2018/08/03","2018/08/04","2018/08/05","2018/08/06","2018/08/07","2018/08/08","2018/08/09","2018/08/10","2018/08/11","2018/08/12","2018/08/13","2018/08/14","2018/08/15","2019/06/05","2019/06/06","2019/06/07","2019/06/08","2019/06/09","2019/06/10","2019/06/11","2019/06/12","2019/06/13","2019/06/14","2019/06/15","2019/06/16","2019/06/17","2019/06/18","2019/06/19","2019/06/20","2019/06/21","2019/06/22","2019/06/23","2019/06/24","2019/06/25","2019/06/26","2019/06/27","2019/06/28","2019/06/29","2019/06/30","2019/07/01","2019/07/02","2019/07/03","2019/07/04","2019/07/05","2019/07/06","2019/07/07","2019/07/08","2019/07/09","2019/07/10","2019/07/11","2019/07/12","2019/07/13","2019/07/14","2019/07/15","2019/07/16","2019/07/17","2019/07/18","2019/07/19","2019/07/20","2019/07/21","2019/07/22","2019/07/23","2019/07/24","2019/07/25","2019/07/26","2019/07/27","2019/07/28","2019/07/29","2019/07/30","2019/07/31","2019/08/01","2019/08/02","2019/08/03","2019/08/04","2019/08/05","2019/08/06","2019/08/07","2019/08/08","2019/08/09","2019/08/10","2019/08/11","2019/08/12","2019/08/13","2019/08/14","2019/08/15","2019/08/16","2019/08/17","2019/08/18","2019/08/19","2019/08/20"}))' sheet['F1'].value = 'Active Rate' sheet['F2'].value = 'SUM(D:D)/SUMPRODUCT(1/COUNTIF(%s[Date],%s[Date]))' % ( newname, newname) sheet['G1'].value = 'Overall Rate' sheet['H1'].value = 'Closing Speed'
tableStyleInfo=mediumStyle) # add the table to the worksheet # sheet.add_table(table) # del sheet._tables[0] if (len(sheet._charts) > 0): del sheet._charts[0] # Make Graph => Referenced: https://openpyxl.readthedocs.io/en/stable/charts/line.html coronaChart = LineChart() coronaChart.title = "Corona Death Stats" coronaChart.style = 13 coronaChart.y_axis.title = sheet["B1"].value coronaChart.y_axis.crossAx = 500 coronaChart.x_axis = DateAxis(crossAx=100) coronaChart.x_axis.number_format = 'mm-dd-yy' coronaChart.x_axis.majorTimeUnit = "days" coronaChart.x_axis.title = "Date" data = Reference(sheet, min_col=2, min_row=1, max_col=3, max_row=current_max_row) coronaChart.add_data(data, titles_from_data=True) dates = Reference(sheet, min_col=1, min_row=2, max_row=current_max_row) coronaChart.set_categories(dates) deathLine = coronaChart.series[0] deathLine.marker.symbol = "triangle"
cell.font = cell.font.copy(bold=True, italic=True) for row in ws['A8':'B8']: for cell in row: cell.font = cell.font.copy(bold=True) #line chart c1 = LineChart() c1.title = "Sales" c1.y_axis.title = 'Size' #crossAx is must (this from excel specification) #Specifies the axId(axis ID) of axis that this axis cross. c1.y_axis.crossAx = 500 #this is for dateAxis c1.x_axis = DateAxis( crossAx=100 ) #this is for Numeric Axis , for TextAxis, it is 10, for SeriesAxis, it is 1000 c1.x_axis.number_format = 'd-mmm' c1.x_axis.majorTimeUnit = "days" c1.x_axis.title = "Date" #index starts from 1, data is B1 to D7 including titles data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7) c1.add_data(data, titles_from_data=True) #now add date , A2 to A7 dates = Reference(ws, min_col=1, min_row=2, max_row=7) c1.set_categories(dates) #By default the top-left corner of a chart is anchored to cell A15 #and the size is 15 x 7.5 cm (approximately 5 columns by 14 rows). #This can be changed by setting the anchor, width and height properties of the chart. ws.add_chart(c1, "A15")
def excel(self): df = self.df vxx = self.vxx vix = self.vix if path.exists("yahooxslx.xlsx"): wb = load_workbook("yahooxslx.xlsx") # grab the active worksheet ws = wb.create_sheet() else: wb = Workbook() # grab the active worksheet ws = wb.active ws['A1'] = 'Fecha' ws['B1'] = 'VXX' ws['C1'] = 'VIX' ws['D1'] = 'Equity' ws['E1'] = '%Max Drawdown' ws['F1'] = '%Expocicion' ws['G1'] = 'Posicion' ws['H1'] = 'Max Drawdown' ws['I1'] = 'Max Equity' ws['J1'] = 'Parametros' ws['K1'] = 'Capital Inicial' ws['L1'] = '%Rebote' ws['M1'] = 'Aumento Volumen Umbral' ws['N1'] = '%Inicial' ws['O1'] = 'Stop Profit VIX' ws['P1'] = 'Stop Loss' ws['Q1'] = 'Stop Loss Expocicion' ws['R1'] = 'Stop Loss Dias' ws['S1'] = 'Stop Aumento posicion-exposicion' ws['K2'] = self.capital ws['L2'] = self.primer_rebote * 100 ws['M2'] = self.avu * 100 ws['N2'] = self.ino * 100 ws['O2'] = self.stop_prof ws['P2'] = self.stop_loss * 100 ws['Q2'] = self.exp_stop_loss * 100 ws['R2'] = self.dias_stop_loss ws['I2'] = np.max(df[0]) ws['H2'] = -np.min(self.max_drawdawns) ws['S2'] = self.pos_exposicion i = 2 for vxx, vix, equity, dw, exp, pos in zip(vxx, vix, df[0], self.max_drawdawns, df[2], df[3]): ws[f'A{i}'] = vxx[0] ws[f'B{i}'] = float(vxx[1]) ws[f'C{i}'] = float(vix[1]) ws[f'D{i}'] = float(equity) ws[f'E{i}'] = "{0:0.1f}".format(abs(dw * 100)) ws[f'F{i}'] = "{0:0.1f}".format(exp * 100) ws[f'G{i}'] = int(pos) i += 1 chart = LineChart() chart.title = "Curva Equity" chart.style = 13 chart.x_axis.title = 'Fecha' chart.y_axis.title = 'Equity' chart.height = 10 # default is 7.5 chart.width = 20 chart.y_axis.crossAx = 500 chart.x_axis = DateAxis(crossAx=100) chart.x_axis.number_format = 'yyyy/mm/dd' chart.x_axis.majorTimeUnit = "days" data = Reference(ws, min_col=4, min_row=2, max_row=ws.max_row) chart.add_data(data, titles_from_data=True) dates = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row) chart.set_categories(dates) ws.add_chart(chart, "J8") wb.save('yahooxslx.xlsx')
def generate_future_detail_report(code, start_date): """ 生成单个交易品种的分析 :param code: 期货简码 :param start_date: 分析数据的起始日期 :return: """ wb = Workbook() ws = wb.active ws.title = code symbol = code + '88' # hq_df = get_price(symbol=symbol, start_date=start_date) # ws_hq = wb.create_sheet(title='hq') # # for r in dataframe_to_rows(hq_df, index=False, header=True): # ws_hq.append(r) basis_df = get_roll_yield(code=code, start_date=start_date) basis_df.reset_index(inplace=True) last_domain_close = basis_df[symbol].iloc[-1] last_domain_basis = basis_df.domain_basis.iloc[-1] basis_df['last_domain_close'] = last_domain_close basis_df['last_domain_basis'] = last_domain_basis ws_basis = wb.create_sheet(title='basis') for r in dataframe_to_rows(basis_df, index=False, header=True): ws_basis.append(r) # 基差图 chart_basis = LineChart() chart_basis.title = "Basis" chart_basis.height = 15 # default is 7.5 chart_basis.width = 45 # default is 15 chart_basis.style = 2 chart_basis.y_axis.title = "%" chart_basis.y_axis.crossAx = 500 chart_basis.y_axis.number_format = PERCENT0_FORMAT chart_basis.x_axis = DateAxis(crossAx=100) chart_basis.x_axis.number_format = DATE_FORMAT chart_basis.x_axis.majorTimeUnit = "days" chart_basis.x_axis.title = "Date" data = Reference(ws_basis, min_col=6, min_row=1, max_col=8, max_row=ws_basis.max_row) chart_basis.add_data(data, titles_from_data=True) values = Reference(ws_basis, min_col=12, min_row=1, max_row=ws_basis.max_row) series = Series(values, title_from_data=True) chart_basis.series.append(series) dates = Reference(ws_basis, min_col=1, min_row=2, max_row=ws_basis.max_row) chart_basis.set_categories(dates) # 收盘价图 chart_close = LineChart() chart_close.y_axis.title = "close" chart_close.y_axis.axId = 200 chart_close.y_axis.majorGridlines = None close_min_str = str(int(basis_df[symbol].min())) close_axis_min = int(close_min_str[0] + '0' * (len(close_min_str) - 1)) chart_close.y_axis.scaling.min = close_axis_min values = Reference(ws_basis, min_col=4, min_row=1, max_row=ws_basis.max_row) chart_close.add_data(values, titles_from_data=True) s = chart_close.series[0] s.graphicalProperties.line.dashStyle = "sysDash" # s.graphicalProperties.line.width = 3.0 chart_basis.y_axis.crosses = 'max' chart_basis += chart_close ws.add_chart(chart_basis, "B35") ws_hist = wb.create_sheet('histogram') # 直方图 histogram chart_basis_dist = histogram(ws_hist, basis_df['domain_basis'].values, title="Basis Distribution", bins=30) chart_basis_dist.title = "Basis Distribution" ws.add_chart(chart_basis_dist, "B2") chart_basis_dist = histogram(ws_hist, basis_df[symbol].values, title="Prices Distribution", bins=30) chart_basis_dist.title = "Prices Distribution" chart_basis_dist.x_axis.number_format = COMMA0_FORMAT chart_basis_dist.x_axis.scaling.min = close_axis_min ws.add_chart(chart_basis_dist, "N2") # ws.add_chart(chart_close, "B32") report_dir = REPORT_DIR / 'future' future_filepath = report_dir / "{}{}.xlsx".format( code, datetime.today().strftime('%Y%m%d')) wb.save(future_filepath)
def graph(wb, tc, sheet_name, sheet_i, data_ref): """Creates a dashboard worksheet for the Excel report. data_ref: The graph assumes that the first row of the data are the data lables +-----------+-----------+-------------------------------------------+ | Member | type | Description | +===========+===========+===========================================+ | title | str | Chart title | +-----------+-----------+-------------------------------------------+ | sheet | pointer | Pointer to worksheet object returned from | | | | create_sheet() | +-----------+-----------+-------------------------------------------+ | type | str | Chart type. See chart_types. | +-----------+-----------+-------------------------------------------+ | x | dict | See Axis dictionary below. | +-----------+-----------+-------------------------------------------+ | y | dict | Same as x but for the y axis. | +-----------+-----------+-------------------------------------------+ Axis dictionary +-----------+-----------+-------------------------------------------+ | Member | type | Description | +===========+===========+===========================================+ | title | str | Axis title. | +-----------+-----------+-------------------------------------------+ | min_col | int | Starting column. | +-----------+-----------+-------------------------------------------+ | max_col | int | Last column. Not used for the x axis. | +-----------+-----------+-------------------------------------------+ | min_row | int | Starting row. Should include the labels. | +-----------+-----------+-------------------------------------------+ | max_row | int | Row number for the last sample. | +-----------+-----------+-------------------------------------------+ :param wb: Workbook object :type wb: dict :param tc: Table of context page. A link to this page is place in cell A1 :type tc: str, None :param sheet_name: Sheet (tab) name :type sheet_name: str :param sheet_i: Sheet index where page is to be placed. :type sheet_i: int :param data_ref: Chart type. See chart_types :type data_ref: dcit :rtype: None """ # Create the worksheet, add the headers, and set up the column widths sheet = wb.create_sheet(index=sheet_i, title=sheet_name) sheet.page_setup.paperSize = sheet.PAPERSIZE_LETTER sheet.page_setup.orientation = sheet.ORIENTATION_LANDSCAPE ref_sheet = data_ref['sheet'] y = data_ref['y'] x = data_ref['x'] if isinstance(tc, str): sheet['A1'].hyperlink = '#' + tc + '!A1' sheet['A1'].font = report_fonts.font_type('link') sheet['A1'] = 'Contents' data = Reference(ref_sheet, min_col=y['min_col'], min_row=y['min_row'], max_col=y['max_col'], max_row=y['max_row']) # Chart with date axis chart = LineChart() chart.title = data_ref['title'] chart.y_axis.title = y['title'] chart.y_axis.crossAx = 500 chart.x_axis = DateAxis(crossAx=100) chart.x_axis.title = x['title'] chart.add_data(data, titles_from_data=True) dates = Reference(ref_sheet, min_col=x['min_col'], min_row=x['min_row'], max_row=x['max_row']) chart.set_categories(dates) sheet.add_chart(chart, 'A2')
def graph_srev(ws, _len): # create chart c1 = LineChart() c1.title = 'SI Summary Revenue' c1.y_axis.title = 'Total Revenue (SBD)' c1.x_axis.title = 'Date' # customize chart c1.style = 2 c1.y_axis.crossAx = 500 c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'yyyy-mm-dd' c1.x_axis.majorTimeUnit = "days" c1.legend = None # y axis data data = Reference(ws, min_col=10, min_row=3, max_row=_len) c1.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c1.set_categories(dates) # set chart width c1.width = 20 # Style chart s1 = c1.series[0] s1.marker.symbol = 'circle' s1.marker.graphicalProperties.solidFill = 'FF0000' s1.marker.graphicalProperties.line.solidFill = 'DDEBF7' s1.graphicalProperties.line.noFill = False #----------- # create chart c2 = LineChart() c2.title = 'Revenue Item Comparision' c2.y_axis.title = 'Total Revenue (SBD)' c2.x_axis.title = 'Date' # customize chart c2.style = 2 c2.y_axis.crossAx = 500 c2.x_axis = DateAxis(crossAx=100) c2.x_axis.number_format = 'yyyy-mm-dd' c2.x_axis.majorTimeUnit = "days" c2.legend = None # y axis data data = Reference(ws, min_col=9, min_row=3, max_row=_len) c2.add_data(data) # y axis data data = Reference(ws, min_col=8, min_row=3, max_row=_len) c2.add_data(data) # y axis data data = Reference(ws, min_col=7, min_row=3, max_row=_len) c2.add_data(data) # y axis data data = Reference(ws, min_col=6, min_row=3, max_row=_len) c2.add_data(data) # y axis data data = Reference(ws, min_col=5, min_row=3, max_row=_len) c2.add_data(data) # y axis data data = Reference(ws, min_col=4, min_row=3, max_row=_len) c2.add_data(data) # y axis data data = Reference(ws, min_col=3, min_row=3, max_row=_len) c2.add_data(data) # y axis data data = Reference(ws, min_col=2, min_row=3, max_row=_len) c2.add_data(data) # x axis data dates = Reference(ws, min_col=1, min_row=3, max_row=_len) c2.set_categories(dates) # Style chart s2 = c2.series[0] s2.marker.symbol = 'circle' s2.marker.graphicalProperties.solidFill = 'FF0000' s2.marker.graphicalProperties.line.solidFill = 'DDEBF7' s2.graphicalProperties.line.noFill = False #----------- # add chart to worksheet ws.add_chart(c1, 'L2') ws.add_chart(c2, 'L20') return ws
thin = Side(border_style="thin", color="000000") for i in range(len(data.columns)): data_header = ws_data.cell(row=1, column=i + 1) data_header.fill = PatternFill("solid", fgColor="DDDDDD") data_header.border = Border(top=thin, left=thin, right=thin, bottom=thin) ws_data.freeze_panes = 'A2' # A2 위쪽을 고정 ############################################################### ######################### Make Line Chart ######################### line_chart = LineChart() line_chart.title = "Line Chart Title" line_chart.style = 12 line_chart.y_axis.title = "Y_title" line_chart.y_axis.crossAx = 500 #date 표시를 위해 필요 line_chart.x_axis = DateAxis(crossAx=100) #date 표시를 위해 필요 line_chart.x_axis.number_format = 'YY-mm-dd' #date 표시를 위해 필요 line_chart.x_axis.majorTimeUnit = "days" #date 표시를 위해 필요 line_chart.x_axis.title = "date" chart_data = Reference(ws_data, min_col=2, min_row=1, max_col=3, max_row=8) line_chart.add_data(chart_data, titles_from_data=True) dates = Reference(ws_data, min_col=1, min_row=2, max_row=8) #date 표시를 위해 필요 line_chart.set_categories(dates) # style for chart style_fst_val = line_chart.series[0] style_fst_val.graphicalProperties.line.solidFill = "0000FF" style_scd_val = line_chart.series[1] style_scd_val.graphicalProperties.line.solidFill = "FF0000"
def write(self, data): # writes the instaCrawl object to xlsx self.console.log('Writing to Excel... \,') self.allHashtags = {} hashtags = data['hashtags'].keys() final = self.prepareData(data) organisedFinal = { 'posts': final, 'users': self.userAnalytics(final), 'dates': self.dateAnalytics(final) } fileName = self.out_path + arrow.now().format('YYYY_MM_DD') + '.xlsx' wb = Workbook() sheetFirst = wb.active sheetFirst.title = 'Stats' chart_position = 2 topList_position = 17 # write the graphs to the first sheet for hashtag in hashtags: try: print '# ', for key, section in organisedFinal.items(): sheet = wb.create_sheet(title='#%s %s' % (hashtag, key)) for row in section[hashtag]: try: sheet.append(row) except: print row raise Exception('oops') # initialise the chart chart = LineChart() chart.title = '#' + hashtag chart.style = 12 chart.y_axis.title = 'Number of posts' chart.y_axis.crossAx = 500 chart.x_axis = DateAxis(crossAx=100) chart.x_axis.number_format = 'd-mmm' chart.x_axis.majorTimeUnit = 'days' chart.x_axis.title = 'Date' # add the data chartData = Reference(wb['#%s %s' % (hashtag, 'dates')], min_col=2, min_row=1, max_col=2, max_row=len( organisedFinal['dates'][hashtag])) dates = Reference(wb['#%s %s' % (hashtag, 'dates')], min_col=1, min_row=2, max_row=len( organisedFinal['dates'][hashtag])) chart.add_data(chartData, titles_from_data=True) chart.set_categories(dates) # style the chart line1 = chart.series[0] # line1.smooth = True sheetFirst.add_chart(chart, 'A' + str(chart_position)) # increase chart position for next one chart_position += 18 # insert top hastags list sheetFirst.cell(row=topList_position, column=1).value = 'Top Hashtags' sheetFirst.cell( row=topList_position + 1, column=1).value = ', '.join([ n[0] for n in Counter( self.allHashtags[hashtag]).most_common(20) ]) # increase position for next one topList_position += 18 except Exception as e: self.console.log('Error writing Excel file: %s' % (e)) wb.save(fileName) self.console.log('done.')
def writeReport(self, filename, trades): if not isinstance(trades, ExtendedTradesAnalyzer): raise Exception( "trades should be an instance of ExtendedTradesAnalyzer") wb = Workbook() names = wb.sheetnames for name in names: wb.remove_sheet(wb.get_sheet_by_name(name)) summarySheet = wb.create_sheet(title="Summary") trades_sheet = wb.create_sheet(title="Trades") equityGraph_sheet = wb.create_sheet(title="EquityChart") detailedEquityGraph_sheet = wb.create_sheet( title="DetailedEquityChart") # ----- Trades sheeet ----- numFormat = "[BLACK][>=0]#,##0.0000;[RED][<0]\\(#,##0.0000\\);General" perFormat = "[BLACK][>=0]#0.00%;[RED][<0]\\(#0.00%\);General" headerFont = Font(name="Arial", bold=True) headerAlign = Alignment(horizontal='center') header_fill = PatternFill(start_color='AAAAAA', end_color='AAAAAA', fill_type='solid') highlightFill = PatternFill(start_color='EEEE99', end_color='EEEE99', fill_type='solid') highlightBorder = Border( bottom=Side(border_style="thin", color="000000")) standardFont = Font(name="Arial", size="10") for col in range(1, 10): trades_sheet.cell(row=1, column=col).font = headerFont trades_sheet.cell(row=1, column=col).fill = header_fill trades_sheet.cell(row=1, column=col).alignment = headerAlign trades_sheet['A1'] = "Trade #\nType" trades_sheet['B1'] = "Date" trades_sheet['C1'] = "Time" trades_sheet['D1'] = "Price" trades_sheet['E1'] = "Contracts\nProfit" trades_sheet['F1'] = "% Profit\nCum Profit" trades_sheet['G1'] = "Run-up\nDrawdown" trades_sheet['H1'] = "Entry Eff.\nExit Eff." trades_sheet['I1'] = "Total\nEfficiency" allTrades = trades.getAll() allReturns = trades.getAllReturns() allEntryDates = trades.allEnterDates allExitDates = trades.allExitDates longFlags = trades.allLongFlags entryPrices = trades.allEntryPrices exitPrices = trades.allExitPrices allContracts = trades.allContracts allCommissions = trades.getCommissionsForAllTrades() excelRow = 2 cumulativeProfit = 0 cumulativePnL = 0 cumulativeLosses = 0 # ----- Equity graph sheet ----- equityGraph_sheet.cell(row=1, column=1, value="Trade #") equityGraph_sheet.cell(row=1, column=2, value="Equity") # -----Detailed Equity graph sheet ----- detailedEquityGraph_sheet.cell(row=1, column=1, value="Timestamp") detailedEquityGraph_sheet.cell(row=1, column=2, value="Equity") r = 2 for x in sorted(trades.cumPnlDict): detailedEquityGraph_sheet.cell(row=r, column=1, value=x) detailedEquityGraph_sheet.cell(row=r, column=2, value=trades.cumPnlDict[x]) r += 1 # Add chart c1 = LineChart() c1.title = "Equity curve" c1.style = 13 c1.y_axis.title = 'Equity' c1.x_axis.title = 'Date' c1.y_axis.auto = True c1.y_axis.delete = False c1.x_axis = DateAxis(crossAx=100) c1.x_axis.number_format = 'd-mmm' c1.x_axis.majorTimeUnit = "days" c1.x_axis.delete = False # c1.x_axis.auto = True c1.legend = None x = Reference(detailedEquityGraph_sheet, min_col=1, min_row=2, max_row=len(trades.cumPnlDict) + 2) y = Reference(detailedEquityGraph_sheet, min_col=2, min_row=2, max_row=len(trades.cumPnlDict) + 2) c1.add_data(y) c1.series[0].smooth = False c1.series[0].graphicalProperties.line.solidFill = "000000" c1.series[0].graphicalProperties.line.width = 10000 # width in EMUs c1.width = 30 c1.height = 15 detailedEquityGraph_sheet.add_chart(c1, "D3") for i in range(0, trades.getCount()): # --- Trades sheet --- for col in range(1, 10): trades_sheet.cell(row=excelRow, column=col).font = standardFont trades_sheet.cell(row=excelRow + 1, column=col).font = standardFont trades_sheet.cell(row=excelRow, column=1, value=i + 1) trades_sheet.cell( row=excelRow, column=1).alignment = Alignment(horizontal='center') if longFlags[i]: buySell = "Buy" else: buySell = "Sell" trades_sheet.cell(row=excelRow + 1, column=1, value=buySell) trades_sheet.cell( row=excelRow + 1, column=1).alignment = Alignment(horizontal='center') entryDate = allEntryDates[i] exitDate = allExitDates[i] trades_sheet.cell(row=excelRow, column=2, value=entryDate.strftime("%Y-%m-%d")) trades_sheet.cell(row=excelRow + 1, column=2, value=exitDate.strftime("%Y-%m-%d")) trades_sheet.cell(row=excelRow, column=3, value=entryDate.strftime("%H:%M")) trades_sheet.cell(row=excelRow + 1, column=3, value=exitDate.strftime("%H:%M")) trades_sheet.cell(row=excelRow, column=4, value=entryPrices[i]) trades_sheet.cell(row=excelRow + 1, column=4, value=exitPrices[i]) trades_sheet.cell(row=excelRow, column=5, value=abs(allContracts[i])) trades_sheet.cell(row=excelRow + 1, column=5, value=allTrades[i]) # TODO(max): Should this include or exclude commissions? trades_sheet.cell(row=excelRow + 1, column=5).number_format = numFormat # TODO(max): Check formula with commissions! profitPerc = ( (exitPrices[i] - allCommissions[i] / allContracts[i]) / entryPrices[i]) - 1 if not longFlags[i]: # TODO(max): Check formula with commissions! profitPerc = -profitPerc trades_sheet.cell(row=excelRow, column=6, value=profitPerc) trades_sheet.cell(row=excelRow, column=6).number_format = perFormat # if longFlags[i]: # profit = (exitPrices[i]-entryPrices[i])*allContracts[i] # else: # profit = -(exitPrices[i]-entryPrices[i])*allContracts[i] cumulativePnL = cumulativePnL + allTrades[i] if allTrades[i] > 0: cumulativeProfit = cumulativeProfit + allTrades[i] else: cumulativeLosses = cumulativeLosses + allTrades[i] trades_sheet.cell(row=excelRow + 1, column=6, value=cumulativePnL) trades_sheet.cell(row=excelRow + 1, column=6).number_format = numFormat # Runup & Drawdown trades_sheet.cell(row=excelRow, column=7, value=trades.allRunups[i]) trades_sheet.cell(row=excelRow, column=7).number_format = numFormat trades_sheet.cell(row=excelRow + 1, column=7, value=trades.allDrawDowns[i]) trades_sheet.cell(row=excelRow + 1, column=7).number_format = numFormat # Entry & Exit efficiencies trades_sheet.cell(row=excelRow, column=8, value=trades.allEntryEff[i]) trades_sheet.cell(row=excelRow, column=8).number_format = perFormat trades_sheet.cell(row=excelRow + 1, column=8, value=trades.allExitEff[i]) trades_sheet.cell(row=excelRow + 1, column=8).number_format = perFormat # Total efficiency trades_sheet.cell(row=excelRow + 1, column=9, value=trades.allTotalEff[i]) trades_sheet.cell(row=excelRow + 1, column=9).number_format = perFormat # Set standard font, and highlight style for 2nd row of trade for col in range(1, 10): # 1st row trades_sheet.cell(row=excelRow, column=col).font = standardFont # 2nd row trades_sheet.cell(row=excelRow + 1, column=col).font = standardFont trades_sheet.cell(row=excelRow + 1, column=col).fill = highlightFill trades_sheet.cell(row=excelRow + 1, column=col).border = highlightBorder excelRow = excelRow + 2 # ----- Equity graph sheet ----- equityGraph_sheet.cell(row=i + 2, column=1, value=i + 1) equityGraph_sheet.cell(row=i + 2, column=2, value=trades.initialEquity + cumulativePnL) if trades.openPosition is not None: pos = trades.openPosition.getPosition() if pos != 0: i += 1 for col in range(1, 10): trades_sheet.cell(row=excelRow, column=col).font = standardFont trades_sheet.cell(row=excelRow + 1, column=col).font = standardFont trades_sheet.cell(row=excelRow + 1, column=col).fill = highlightFill trades_sheet.cell(row=excelRow + 1, column=col).border = highlightBorder isLong = trades.openPosition.isLong trades_sheet.cell(row=excelRow, column=1, value=i + 1) trades_sheet.cell( row=excelRow, column=1).alignment = Alignment(horizontal='center') if isLong: buySell = "Buy" else: buySell = "Sell" trades_sheet.cell(row=excelRow + 1, column=1, value=buySell) trades_sheet.cell( row=excelRow + 1, column=1).alignment = Alignment(horizontal='center') entryDate = trades.openPosition.entryDate trades_sheet.cell(row=excelRow, column=2, value=entryDate.strftime("%Y-%m-%d")) trades_sheet.cell(row=excelRow + 1, column=2, value=entryDate.strftime("Open")) trades_sheet.cell(row=excelRow, column=3, value=entryDate.strftime("%H:%M")) entryPrice = trades.openPosition.entryPrice trades_sheet.cell(row=excelRow, column=4, value=entryPrice) trades_sheet.cell(row=excelRow + 1, column=4, value="--") trades_sheet.cell(row=excelRow, column=5, value=abs(pos)) trades_sheet.cell(row=excelRow + 1, column=5, value="--") trades_sheet.cell(row=excelRow, column=6, value="--") trades_sheet.cell(row=excelRow + 1, column=6, value="--") trades_sheet.cell(row=excelRow, column=8, value="--") trades_sheet.cell(row=excelRow + 1, column=8, value="--") trades_sheet.cell(row=excelRow + 1, column=9, value="--") # ----- Equity graph sheet ----- # Add chart c1 = LineChart() c1.title = "Equity curve" c1.style = 13 c1.y_axis.title = 'Equity' c1.x_axis.title = 'Trade #' c1.x_axis.scaling.min = 0 c1.x_axis.scaling.max = len(allTrades) + 3 c1.x_axis.auto = True c1.y_axis.auto = True c1.x_axis.delete = False c1.y_axis.delete = False c1.legend = None x = Reference(equityGraph_sheet, min_col=1, min_row=2, max_row=len(allTrades) + 2) y = Reference(equityGraph_sheet, min_col=2, min_row=2, max_row=len(allTrades) + 2) c1.add_data(y) c1.series[0].smooth = False c1.series[0].graphicalProperties.line.solidFill = "000000" c1.series[0].graphicalProperties.line.width = 10000 # width in EMUs c1.width = 30 c1.height = 15 equityGraph_sheet.add_chart(c1, "D3") # ----- Summary sheeet ----- titleFont = Font(name="Arial", size=18, bold=True) titleAlign = Alignment(horizontal='center') headerFont = Font(name="Arial", size=14, bold=True) headerAlign = Alignment(horizontal='left') standardFont = Font(name="Arial", size=10) summarySheet['A1'] = "Strategy Performance Report" summarySheet.merge_cells("A1:I1") summarySheet['A1'].font = titleFont summarySheet['A1'].alignment = titleAlign summarySheet["B6"] = "Performance Summary: All Trades" summarySheet["B6"].font = headerFont summarySheet["B6"].alignment = headerAlign summarySheet["B8"] = "Net Profits" summarySheet["D8"] = cumulativeProfit + cumulativeLosses summarySheet["D8"].number_format = numFormat summarySheet["F8"] = "Open position P/L" summarySheet["H8"] = "" summarySheet["B9"] = "Gross Profits" summarySheet["D9"] = cumulativeProfit summarySheet["D9"].number_format = numFormat summarySheet["D9"].comment = Comment( "Net profits - Gross losses, i.e. Net profits + Abs(Gross losses)", "Report") summarySheet["F9"] = "Gross Losses" summarySheet["H9"] = cumulativeLosses summarySheet["H9"].number_format = numFormat summarySheet["B11"] = "Total num. of trades" summarySheet["D11"] = trades.getCount() summarySheet["F11"] = "Percent profitable" if trades.getCount() > 0: summarySheet["H11"] = float(trades.getProfitableCount()) / float( trades.getCount()) else: summarySheet["H11"] = 0 summarySheet["H11"].number_format = perFormat summarySheet["B12"] = "Num. of winning trades" summarySheet["D12"] = trades.getProfitableCount() summarySheet["F12"] = "Num. of losing trades" summarySheet["H12"] = trades.getUnprofitableCount() summarySheet["B14"] = "Largest winning trade" if trades.getProfitableCount() > 0: summarySheet["D14"] = allTrades.max() else: summarySheet["D14"] = 0 summarySheet["D14"].number_format = numFormat summarySheet["F14"] = "Largest losing trade" if trades.getUnprofitableCount() > 0: summarySheet["H14"] = allTrades.min() else: summarySheet["H14"] = 0 summarySheet["H14"].number_format = numFormat def negativeToZero(x): if x > 0: return x else: return 0 def positiveToZero(x): if x >= 0: return 0 else: return x avgWin = 0 avgLoss = 0 if trades.getProfitableCount() > 0: avgWin = cumulativeProfit / trades.getProfitableCount() summarySheet["B15"] = "Average winning trade" summarySheet["D15"] = avgWin summarySheet["D15"].number_format = numFormat if trades.getUnprofitableCount() > 0: avgLoss = cumulativeLosses / trades.getUnprofitableCount() summarySheet["F15"] = "Average losing trade" summarySheet["H15"] = avgLoss summarySheet["H15"].number_format = numFormat summarySheet["B16"] = "Ratio avg. win/avg. loss" if avgLoss != 0: summarySheet["D16"] = -avgWin / avgLoss else: summarySheet["D16"] = 'NaN' summarySheet["D16"].number_format = numFormat summarySheet["F16"] = "Avg trade (win & loss)" if trades.getCount() > 0: summarySheet["H16"] = cumulativePnL / trades.getCount() else: summarySheet["H16"] = 0 summarySheet["H16"].number_format = numFormat summarySheet["B21"] = "Max intraday drawdown" summarySheet["D21"] = "" summarySheet["B22"] = "Profit factor" if cumulativeLosses != 0: summarySheet["D22"] = -cumulativeProfit / cumulativeLosses summarySheet["D22"].number_format = numFormat else: summarySheet["D22"] = "Inf" summarySheet["D22"].comment = Comment("- Gross profits / Gross losses", "Report") summarySheet["F22"] = "Max contracts held" summarySheet["H22"] = "" summarySheet["B23"] = "Account size required" summarySheet["D23"] = "" # ABS(max intraday drawdown) summarySheet["F23"] = "Return on account" summarySheet["H23"] = "" # net profit / account size required # Save the file wb.save(filename)