Export DataFrames to Excel with Charts in Multiple Sheets

What will we cover in this tutorial?

In this tutorial we will learn how to export Financial data from DataFrames (Pandas/Python) into an Excel sheet. It will be in multiple sheets with colored rows and charts. And of course, all automated from Python.

Step 1: Read financial data into a DataFrame

First we need some data. We will use the data from our CSV file. Feel free to use any other data. The CSV used here is available in my GitHub.

import pandas as pd
 
data = pd.read_csv("AAPL.csv", index_col=0, parse_dates=True)

Step 2: Calculate the Moving Average, MACD, and Stochastic Oscillator with DataFrames

Moving Average as done previously.

data['MA10'] = data['Close'].rolling(10).mean()

The MACD as calculated follows.

exp1 = data['Close'].ewm(span=12, adjust=False).mean()
exp2 = data['Close'].ewm(span=26, adjust=False).mean()
data['MACD'] = macd = exp1 - exp2
data['Signal line'] = exp3 = macd.ewm(span=9, adjust=False).mean()

The Stochastic Oscillator.

high14 = data['High'].rolling(14).max()
low14 = data['Low'].rolling(14).min()
data['%K'] = pct_k = (data['Close'] - low14)*100/(high14 - low14)
data['%D'] = pct_d = data['%K'].rolling(3).mean()

Step 3: Adjust the time period and reverse the DataFrame

Adjust the time period we need. This is needed as the first calculations will not be available (NaN) or incorrect.

data = data.loc['2020-01-01':]
data = data.iloc[::-1]

Also notice, we reverse the data by .iloc[::-1]. This is just to have the most recent data on the top of our Excel sheet.

Step 4: Export DataFrame to Excel with multiple sheets and chars

The for generating our Excel sheet we need the XlsxWriter.

If you don’t have it installed already you can install it by running this in a cell: !pip install XlsxWriter

The code that generated the Excel sheet.

writer = pd.ExcelWriter("technical.xlsx", 
                        engine='xlsxwriter', 
                        date_format = 'yyyy-mm-dd', 
                        datetime_format='yyyy-mm-dd')
 
workbook = writer.book
 
# Create a format for a green cell
green_cell = workbook.add_format({
    'bg_color': '#C6EFCE',
    'font_color': '#006100'
})
 
# Create a format for a red cell
red_cell = workbook.add_format({
    'bg_color': '#FFC7CE',                            
    'font_color': '#9C0006'
})
 
# **
# ** MA
# **
sheet_name = 'MA10'
data[['Close', 'MA10']].to_excel(writer, sheet_name=sheet_name)
worksheet = writer.sheets[sheet_name]
 
# Set column width of Date
worksheet.set_column(0, 0, 15)
 
 
for col in range(1, 3):
    # Create a conditional formatted of type formula
    worksheet.conditional_format(1, col, len(data), col, {
        'type': 'formula',                                    
        'criteria': '=B2>=C2',
        'format': green_cell
    })
 
    # Create a conditional formatted of type formula
    worksheet.conditional_format(1, col, len(data), col, {
        'type': 'formula',                                    
        'criteria': '=B2<C2',
        'format': red_cell
    })
 
 
# Create a new chart object.
chart1 = workbook.add_chart({'type': 'line'})
 
# Add a series to the chart.
chart1.add_series({
        'name': 'AAPL',
        'categories': [sheet_name, 1, 0, len(data), 0],
        'values': [sheet_name, 1, 1, len(data), 1],
})
 
# Create a new chart object.
chart2 = workbook.add_chart({'type': 'line'})
 
# Add a series to the chart.
chart2.add_series({
        'name': sheet_name,
        'categories': [sheet_name, 1, 0, len(data), 0],
        'values': [sheet_name, 1, 2, len(data), 2],
})
 
# Combine and insert title, axis names
chart1.combine(chart2)
chart1.set_title({'name': sheet_name + " AAPL"})
chart1.set_x_axis({'name': 'Date'})
chart1.set_y_axis({'name': 'Price'})
 
# Insert the chart into the worksheet.
worksheet.insert_chart('E2', chart1)
 
 
# **
# ** MACD
# **
 
sheet_name = 'MACD'
data[['Close', 'MACD', 'Signal line']].to_excel(writer, sheet_name=sheet_name)
worksheet = writer.sheets[sheet_name]
 
# Set column width of Date
worksheet.set_column(0, 0, 15)
 
for col in range(1, 4):
    # Create a conditional formatted of type formula
    worksheet.conditional_format(1, col, len(data), col, {
        'type': 'formula',                                    
        'criteria': '=C2>=D2',
        'format': green_cell
    })
 
    # Create a conditional formatted of type formula
    worksheet.conditional_format(1, col, len(data), col, {
        'type': 'formula',                                    
        'criteria': '=C2<D2',
        'format': red_cell
    })
 
# Create a new chart object.
chart1 = workbook.add_chart({'type': 'line'})
 
# Add a series to the chart.
chart1.add_series({
        'name': 'MACD',
        'categories': [sheet_name, 1, 0, len(data), 0],
        'values': [sheet_name, 1, 2, len(data), 2],
})
 
# Create a new chart object.
chart2 = workbook.add_chart({'type': 'line'})
 
# Add a series to the chart.
chart2.add_series({
        'name': 'Signal line',
        'categories': [sheet_name, 1, 0, len(data), 0],
        'values': [sheet_name, 1, 3, len(data), 3],
})
 
# Combine and insert title, axis names
chart1.combine(chart2)
chart1.set_title({'name': sheet_name + " AAPL"})
chart1.set_x_axis({'name': 'Date'})
chart1.set_y_axis({'name': 'Value'})
 
# To set the labels on x axis not on 0
chart1.set_x_axis({
    'label_position': 'low',
    'num_font':  {'rotation': 45}
})
 
# Insert the chart into the worksheet.
worksheet.insert_chart('F2', chart1)
 
 
# **
# ** Stochastic
# **
 
sheet_name = 'Stochastic'
data[['Close', '%K', '%D']].to_excel(writer, sheet_name=sheet_name)
worksheet = writer.sheets[sheet_name]
 
# Set column width of Date
worksheet.set_column(0, 0, 15)
 
for col in range(1, 4):
    # Create a conditional formatted of type formula
    worksheet.conditional_format(1, col, len(data), col, {
        'type': 'formula',                                    
        'criteria': '=C2>=D2',
        'format': green_cell
    })
 
    # Create a conditional formatted of type formula
    worksheet.conditional_format(1, col, len(data), col, {
        'type': 'formula',                                    
        'criteria': '=C2<D2',
        'format': red_cell
    })
 
 
# Create a new chart object.
chart1 = workbook.add_chart({'type': 'line'})
 
# Add a series to the chart.
chart1.add_series({
        'name': '%K',
        'categories': [sheet_name, 1, 0, len(data), 0],
        'values': [sheet_name, 1, 2, len(data), 2],
})
 
# Create a new chart object.
chart2 = workbook.add_chart({'type': 'line'})
 
# Add a series to the chart.
chart2.add_series({
        'name': '%D',
        'categories': [sheet_name, 1, 0, len(data), 0],
        'values': [sheet_name, 1, 3, len(data), 3],
})
 
# Combine and insert title, axis names
chart1.combine(chart2)
chart1.set_title({'name': sheet_name + " AAPL"})
chart1.set_x_axis({'name': 'Date'})
chart1.set_y_axis({'name': 'Value'})
 
# To set the labels on x axis not on 0
chart1.set_x_axis({
    'label_position': 'low',
    'num_font':  {'rotation': 45}
})
 
# Insert the chart into the worksheet.
worksheet.insert_chart('F2', chart1)
 
# End of sheets
 
 
# Close
writer.close()

For a walkthrough of the code, please see the video to this lesson.

This will generate an Excel sheet in called technical.xlsx. It will contain 3 sheets (MA10, MACD, Stochastic Oscillator).

A sheet will look similar to this.

Want to learn more?

This was the last part of the 8 part course of Master Technical Analysis with pandas.

If you want to check the first part start here where you learn about pandas DataFrames for financial analysis.

12% Investment Solution

Would you like to get 12% in return of your investments?

D. A. Carter promises and shows how his simple investment strategy will deliver that in the book The 12% Solution. The book shows how to test this statement by using backtesting.

Did Carter find a strategy that will consistently beat the market?

Actually, it is not that hard to use Python to validate his calculations. But we can do better than that. If you want to work smarter than traditional investors then continue to read here.

Python Circle

Do you know what the 5 key success factors every programmer must have?

How is it possible that some people become programmer so fast?

While others struggle for years and still fail.

Not only do they learn python 10 times faster they solve complex problems with ease.

What separates them from the rest?

I identified these 5 success factors that every programmer must have to succeed:

Collaboration: sharing your work with others and receiving help with any questions or challenges you may have.
Networking: the ability to connect with the right people and leverage their knowledge, experience, and resources.
Support: receive feedback on your work and ask questions without feeling intimidated or judged.
Accountability: stay motivated and accountable to your learning goals by surrounding yourself with others who are also committed to learning Python.
Feedback from the instructor: receiving feedback and support from an instructor with years of experience in the field.

I know how important these success factors are for growth and progress in mastering Python.

That is why I want to make them available to anyone struggling to learn or who just wants to improve faster.

With the Python Circle community, you can take advantage of 5 key success factors every programmer must have.

Learn more about the Python Circle

Be part of something bigger and join the Python Circle community.

Learn how you can become a Python programmer in just 12 weeks.