1
##############################################################################
3
# An example of converting a Pandas dataframe with stock data taken from the
4
# web to an xlsx file with a line chart using Pandas and XlsxWriter.
6
# Copyright 2013-2015, John McNamara, jmcnamara@cpan.org
10
import pandas.io.data as web
12
# Create some sample data to plot.
14
for ticker in ['AAPL', 'GOOGL', 'IBM', 'YHOO', 'MSFT']:
15
all_data[ticker] = web.get_data_yahoo(ticker, '5/1/2014', '5/1/2015')
17
# Create a Pandas dataframe from the data.
18
df = pd.DataFrame({tic: data['Adj Close']
19
for tic, data in all_data.items()})
21
# Create a Pandas Excel writer using XlsxWriter as the engine.
23
writer = pd.ExcelWriter('pandas_chart_stock.xlsx', engine='xlsxwriter')
24
df.to_excel(writer, sheet_name=sheet_name)
26
# Access the XlsxWriter workbook and worksheet objects from the dataframe.
27
workbook = writer.book
28
worksheet = writer.sheets[sheet_name]
30
# Adjust the width of the first column to make the date values clearer.
31
worksheet.set_column('A:A', 20)
33
# Create a chart object.
34
chart = workbook.add_chart({'type': 'line'})
36
# Configure the series of the chart from the dataframe data.
38
for i in range(len(['AAPL', 'GOOGL'])):
41
'name': ['Sheet1', 0, col],
42
'categories': ['Sheet1', 2, 0, max_row, 0],
43
'values': ['Sheet1', 2, col, max_row, col],
44
'line': {'width': 1.00},
47
# Configure the chart axes.
48
chart.set_x_axis({'name': 'Date', 'date_axis': True})
49
chart.set_y_axis({'name': 'Price', 'major_gridlines': {'visible': False}})
51
# Position the legend at the top of the chart.
52
chart.set_legend({'position': 'top'})
54
# Insert the chart into the worksheet.
55
worksheet.insert_chart('H2', chart)
57
# Close the Pandas Excel writer and output the Excel file.
added
removed
examples/pandas_chart_stock.py
