Automate Excel with Python

Doing similar reports daily, weekly or monthly in excel can be exhausting. If doing pivot tables as part of your morning routine is getting boring and frustrating then today is your lucky day. We are going to learn how to do pivot tables, visualize the results and write them to an excel workbook all using python. We will then convert the python file into an executable so that you can share the program with your colleagues. Okay, lets get started.

Firstly, let us install the packages we will need:

pip install openpyxl
pip install pandas
pip install pyinstaller

openpyxl will help us to read and write to excel files,pandas will help us do common excel tasks and we will use pyinstaller to convert our python file into an executable.

Next, this is the data we are going to use finance_sample.xlsx. Download the data and save it into a new directory/folder.

Next, create a new python file in the same folder/directory that you have created above. Now the interesting part starts.

Open the python file and lets code:

# Import the following packages
import pandas as pd
import os
from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference
from openpyxl.styles import Alignment
from openpyxl.utils import get_column_letter
from openpyxl.utils import get_column_letter

We want to get the profits per quarter for the year2014, here is how we will go about it:

# This will be the new generated file where our results will be saved.
file_path = os.getcwd() + '\\financial_reports.xlsx'

Create a function that computes quarterly profits per country

def quartelyProfits(financialdata):
    df = pd.read_excel(financialdata)
    filtered = df[df['Year'] == 2014]
    ### Pivot Tables
    quarterly_profits = pd.pivot_table(filtered, index=df['Date'].dt.quarter, columns='Country', values='Profit', aggfunc='sum')

    ### Creating an Excel Workbook

    quarterly_profits.to_excel(file_path, sheet_name='Quarterly Profits 2014', startrow=3)

From the function above notice that we are using pandas to read the excel workbook. Since we want the data for the year 2014 we create a filter to do so. Also notice we are constructing a pivot table using pd.pivot. In the arguments, the default for aggfunc is mean. So to return the sum always pass “sum”. You can read the documentation to understand more on this pandas_docs. We then save the workbook.

The next step is to edit the new workbook and add a chart

def main():
    filefinance = input("Please enter the financial workbook -- include extension (.xlsx if you are unsure)\n")
    quartelyProfits(filefinance)

    # Load workbook
    wb = load_workbook(file_path)
    sheet = wb['Quarterly Profits 2014']

    # Adjust column widths to make them readable
    for col in sheet.columns:
        max_length = 0
        column = col[0].column # Get the column name
        for cell in col:
            try: # Necessary to avoid error on empty cells
                if len(str(cell.value)) > max_length:
                    max_length = len(cell.value)
            except:
                pass
        adjusted_width = (max_length + 6) * 1.5
        sheet.column_dimensions[get_column_letter(column)].width = adjusted_width

    sheet.merge_cells('A1:F3')
    sheet['A1'] = "Quarterly Profits"
    sheet['A1'].style = 'Title'
    sheet['A1'].alignment = Alignment(horizontal='center')


    for i in range(5, 9):
        sheet[f'B{i}'].style = 'Currency'
        sheet[f'C{i}'].style = 'Currency'
        sheet[f'D{i}'].style = 'Currency'
        sheet[f'E{i}'].style = 'Currency'
        sheet[f'F{i}'].style = 'Currency'

    # Add a Bar Chart
    bar_chart = BarChart()
    data = Reference(sheet, min_col=2,max_col=6, min_row=4,max_row=8)
    categories = Reference(sheet, min_col=1, max_col=1, min_row=5, max_row=8)
    bar_chart.style = 26
    bar_chart.add_data(data, titles_from_data=True)
    bar_chart.set_categories(categories)
    sheet.add_chart(bar_chart, "B11")

    bar_chart.title = 'Profits per Quarter'
    

    wb.save(filename=file_path)


main()

What the above code does is it gets the new file then increases the column sizes so that the report can be readable. It also edits the file by merging cells, styling cells, aligning items and it also adds a chart. All this has been achieved by using openpyxl. You can read the docs here.

Finally, to convert the python file into an executable we will use pyinstaller. Open cmd or powershell (for windows) and run the following script:

pyinstaller --onefile yourfile.py

Pass — onefile so that all the contents are bundled together into one executable.

Wait for the conversion to finish. Once it is done, two folders are created:

- dist
- build

Inside the dist folder is where the executable is located. Here is an example of it working:

The executable must be in the same folder as the workbook

there it is, in 2 or 3 minutes you are done.

And there it is, we’ve been able to automate reports done in excel. It is now your turn to explore because there are endless things you can do. Hope you’ve learnt something.