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)
