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.

Nest steps?

Want to learn more?

This is part of the FREE online course on my page. No signup required and 2 hours of free video content with code and Jupyter Notebooks available on GitHub.

Follow the link and read more.

Stochastic Oscillator with Pandas DataFrames

What will we cover?

In this tutorial we will show how to calculate the Stochastic Oscillator with Pandas DataFrames.

Step 1: Retrieve the Data from CSV file with Pandas DataFrames

We first need to read the data from a CSV file into a DataFrame. You can get the CSV file from here or directly from Yahoo! Finance.

Alternatively you can you PandasDataframes as described in this tutorial.

import pandas as pd
import matplotlib.pyplot as plt
%matplotlib notebook
 
data = pd.read_csv("AAPL.csv", index_col=0, parse_dates=True)

Step 2: Calculate the Stochastic Oscillator with Pandas DataFrames

The Stochastic Oscillator is defined as follows.

  • 14-high: Maximum of last 14 trading days
  • 14-low: Minimum of last 14 trading days
  • %K(Last Close – 14-low)*100 / (14-high – 14-low)
  • %D: Simple Moving Average of %K

That can be done as follows.

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

Notice, we only keep the %K and %D. The high14 and low14 are temporary variables to make our calculations easier to read.

Step 3: Visualize the Stochastic Oscillator with Matplotlib

To visualize it.

fig, ax = plt.subplots()
data[['%K', '%D']].loc['2020-11-01':].plot(ax=ax)
ax.axhline(80, c='r', alpha=0.3)
ax.axhline(20, c='r', alpha=0.3)
data['Close'].loc['2020-11-01':].plot(ax=ax, alpha=0.3, secondary_y=True)

Resulting in the following.

Next Steps?

Want to learn more?

This is part of the FREE online course on my page. No signup required and 2 hours of free video content with code and Jupyter Notebooks available on GitHub.

Follow the link and read more.