def plot_sentiment(tickers): df = scored_news(tickers) #getrange of headlines dates and daily scores dates = date_range(df) daily = daily_sentiment(tickers) df = tally_scores(df).T blank = [] columns = list(df) path = "C:\\Users\\matth\\Desktop\\stock-sentiment-analyzer\\pie.xlsx" sf = StyleFrame(daily.T) col_list = list(daily.columns) writer = pd.ExcelWriter(path, engine='xlsxwriter') daily.T.to_excel(excel_writer=writer, sheet_name='Daily Sentiment Summary') worksheet = writer.sheets['Daily Sentiment Summary'] worksheet.set_column('B:B', 15) writer.save() wb = load_workbook("pie.xlsx") for i in range(0, len(df.columns)): ws = wb.create_sheet(tickers[i]) score = 1 for index, row in df.iterrows(): vals = [] if index == 'tickers': continue vals.append(df.loc[index, i]) vals.insert(0, df.index[score]) score = score + 1 ws.append(vals) pie = PieChart() labels = Reference(ws, min_col=1, max_col=1, min_row=1, max_row=3) data = Reference(ws, min_col=2, max_col=2, min_row=1, max_row=3) pie.add_data(data) pie.set_categories(labels) pie.title = tickers[i] + ' Sentiment' pie.layout = Layout(manualLayout=ManualLayout( h=0.7, w=0.7, )) ws.add_chart(pie, "E3") wb.save("pie.xlsx")
def excel_create_chart(excel_file): """ 生成图表 :param excel_file: :return: """ wb = load_workbook(excel_file) print(f'{datetime.now()} | 信息 | 开始Excel图表渲染') # 进行净值走势图渲染 net_worth_sheet = wb['账户净值'] net_worth_chart_sheet = wb.create_chartsheet(title='净值走势图') chart1 = LineChart() dates1 = Reference(net_worth_sheet, min_col=1, min_row=2, max_row=len(net_worth_sheet['A'])) data1 = Reference(net_worth_sheet, min_col=3, min_row=1, max_row=len(net_worth_sheet['C'])) chart1.add_data(data1, titles_from_data=True) chart1.y_axis = NumericAxis(title='净值', majorTickMark='out') chart1.x_axis = TextAxis(majorTickMark='out', tickLblSkip=10, tickMarkSkip=10, noMultiLvlLbl=True, numFmt='yyyy-mm-dd') chart1.legend = None chart1.title = str() chart1.style = 1 chart1.set_categories(dates1) net_worth_chart_sheet.add_chart(chart1) # 进行权益走势图渲染 account_sheet = wb['账户统计'] account_chart_sheet = wb.create_chartsheet(title='权益走势图') dates2 = Reference(account_sheet, min_col=1, min_row=2, max_row=len(account_sheet['A'])) chart2 = LineChart(varyColors=True) data2 = Reference(account_sheet, min_col=8, min_row=1, max_row=len(account_sheet['H'])) chart2.add_data(data2, titles_from_data=True) chart2.y_axis = NumericAxis(title='权益', majorTickMark='out') chart2.legend = None chart2.set_categories(dates2) chart3 = BarChart() data3 = Reference(account_sheet, min_col=10, min_row=1, max_row=len(account_sheet['J'])) chart3.add_data(data3, titles_from_data=True) chart3.y_axis = NumericAxis(axId=200, title='风险度', majorGridlines=None, majorTickMark='out', crosses='max') chart3.x_axis = TextAxis(majorTickMark='out', tickLblSkip=10, tickMarkSkip=10, noMultiLvlLbl=True, numFmt='yyyy-mm-dd') chart3.legend = None chart3.set_categories(dates2) chart2 += chart3 account_chart_sheet.add_chart(chart2) # 进行交易分布图的渲染 categories_analysis_sheet = wb['交易分析(按品种)'] trading_frequency_analysis_sheet = wb.create_chartsheet(title='交易分布图') labels = Reference(categories_analysis_sheet, min_col=1, min_row=2, max_row=len(categories_analysis_sheet['A'])) chart4 = PieChart(varyColors=True) chart4.style = 34 data4 = Reference(categories_analysis_sheet, min_col=4, min_row=2, max_row=len(categories_analysis_sheet['D'])) chart4.add_data(data4) chart4.set_categories(labels) chart4.legend = None # chart4.series[0].data_points = [DataPoint(idx=i, explosion=8) # for i in range(len(categories_analysis_sheet['D']) - 1)] chart4.series[0].dLbls = DataLabelList(dLblPos='bestFit', showPercent=True, showCatName=True, showVal=True, showLeaderLines=True) chart4.layout = Layout(manualLayout=ManualLayout( x=0, y=0, h=0.75, w=0.75, xMode='factor', yMode='factor')) trading_frequency_analysis_sheet.add_chart(chart4) # 进行品种盈亏图的渲染 categories_win_and_loss_chart_sheet = wb.create_chartsheet(title='品种盈亏图') chart5 = BarChart(barDir='col') chart5.style = 18 data5 = Reference(categories_analysis_sheet, min_col=2, min_row=2, max_row=len(categories_analysis_sheet['B'])) chart5.add_data(data5) chart5.set_categories(labels) chart5.legend = None chart5.series[0].dLbls = DataLabelList(showVal=True) chart5.y_axis = NumericAxis(title='平仓盈亏', majorTickMark='out', minorTickMark='out') categories_win_and_loss_chart_sheet.add_chart(chart5) # 进行交易盈亏图的渲染 trading_win_and_loss_chart_sheet = wb.create_chartsheet(title='交易盈亏图') chart6 = RadarChart() chart6.style = 24 data6 = Reference(categories_analysis_sheet, min_col=8, max_col=9, min_row=1, max_row=categories_analysis_sheet.max_row) chart6.add_data(data6, titles_from_data=True) chart6.set_categories(labels) trading_win_and_loss_chart_sheet.add_chart(chart6) # 图表保存 wb.save(excel_file) wb.close() # 输出信息 print(f'{datetime.now()} | 信息 | 已生成Excel图表')