Yet Another Blog in Statistical Computing

I can calculate the motion of heavenly bodies but not the madness of people. -Isaac Newton

Create Highly Customized Excel Chart with Python

import pandas as pd
import pandas.io.data as web

data = web.get_data_yahoo('FITB', '1/1/2009', '9/1/2014')
df = pd.DataFrame({'FITB': data['Adj Close']})

excel_file = 'fitb.xlsx'
sheet_name = 'Sheet1'

writer = pd.ExcelWriter(excel_file, engine = 'xlsxwriter')
df.to_excel(writer, sheet_name = sheet_name)

workbook = writer.book
worksheet = writer.sheets[sheet_name]
worksheet.set_column('A:A', 20)

chart = workbook.add_chart({'type': 'line'})
max_row = len(df) + 1
chart.add_series({
    'name':       ['Sheet1', 0, 1],
    'categories': ['Sheet1', 2, 0, max_row, 0],
    'values':     ['Sheet1', 2, 1, max_row, 1],
    'line':       {'width': 1.00},
})

chart.set_x_axis({'name': 'Date', 'date_axis': True})
chart.set_y_axis({'name': 'Price', 'major_gridlines': {'visible': False}})
chart.set_legend({'position': 'top'})

worksheet.insert_chart('D1', chart)
writer.save()

Screenshot

Advertisements

Written by statcompute

September 1, 2014 at 10:41 am

Posted in PYTHON

%d bloggers like this: