Add formula to cell in Excel – Python Pandas

Post Views: 1,181

Are you tired of manually calculating your business numbers at the end of each month? I’ve created a simple tool that does the heavy lifting for you, giving you instant insights into your financial data.

What Does This Tool Do?

This automation takes your Excel spreadsheet and instantly calculates five key metrics:

Total Sum: The total of all your transactions
Average: The typical transaction amount
Maximum: Your largest transaction
Minimum: Your smallest transaction
Count: The total number of transactions

Technical Stack

Python 3.x
Pandas for data manipulation
OpenPyXL for Excel handling
Google Colab integration for cloud execution

Code Architecture Breakdown

# Import the tools we need
import pandas as pd # For working with Excel data easily
from openpyxl import load_workbook # For making Excel files look pretty
from openpyxl.styles import Font, PatternFill # For formatting (bold text, colors, etc.)
from google.colab import files # For downloading files in Google Colab

def create_excel_summary(file_path):
# This is our main function that does all the work
try:
# Step 1: Open and read your Excel file
# Think of this like opening your Excel file and looking at all the data
df = pd.read_excel(file_path)

# Make sure we have an 'Amount' column - if not, tell the user
if 'Amount' not in df.columns:
raise ValueError("Hey! We couldn't find the 'Amount' column in your Excel file!")

# Step 2: Calculate all the important numbers
# This is like using a calculator, but automatic
summary = {
'Metric': [
'Sum', # Adds up all the numbers
'Average', # Finds the typical amount
'Max', # Finds the biggest number
'Min', # Finds the smallest number
'Count' # Counts how many entries you have
],
'Value': [
df['Amount'].sum(), # Total of all amounts
df['Amount'].mean(), # Average amount
df['Amount'].max(), # Highest amount
df['Amount'].min(), # Lowest amount
df['Amount'].count() # Number of entries
]
}

# Turn our calculations into a neat table
summary_df = pd.DataFrame(summary)

# Step 3: Write everything back to Excel and make it look nice
with pd.ExcelWriter(file_path, engine='openpyxl', mode='a', 
if_sheet_exists='overlay') as writer:
# Get the Excel file ready for editing
workbook = writer.book
worksheet = workbook.active

# Add a title for our summary section
# This goes two rows below your data
start_row = len(df) + 2
worksheet.cell(start_row, 2, 'Summary Statistics').font = Font(bold=True)

# Write our summary table below the title
summary_df.to_excel(writer, index=False, startrow=start_row + 1, startcol=1)

# Make it look professional:
# - Make the labels bold
# - Format numbers to show decimals and commas
for row in range(start_row + 2, start_row + 7):
worksheet.cell(row, 2).font = Font(bold=True) # Bold text for labels
worksheet.cell(row, 3).number_format = '#,##0.00' # Nice number formatting

# Step 4: Save and download the file
files.download(file_path)
return True # Let us know everything worked

except Exception as e:
# If anything goes wrong, tell us what happened
print(f"Oops! Something went wrong: {str(e)}")
return False

# How to use this tool:
# 1. Put your Excel file name here
file_path = 'testcopy.xlsx'
# 2. Run the program
create_excel_summary(file_path)

Result

Before After

Performance

It is vectorized with pandas to perform operations as fast as possible

Memory efficiency using context managers

Least number of I/O operations as only one file write

O(n) time complexity for computations

Integration

Compatible with any Excel file that contains an ‘Amount’ column
Integration with Google Colab to run in the cloud
Extendable for extra statistical computations
Compatible with various Excel versions using OpenPyXL

Example in Real Life

You have a coffee shop. You have an Excel sheet that has all your sales on daily basis. This tool will directly tell you the total revenue for a month, your average sales on a daily basis, the highest and lowest selling days, the number of sales you made in a day.

Get all the Code from Github : https://github.com/alwaysashutosh/p1-automated_excel