Excel + Python for Finance Dashboards: Bookkeeping Insights ...

Topic starter 28/11/2023 8:15 pm

Excel + Python for Finance Dashboards: Bookkeeping Insights from Lockdown

Back in 2020, when lockdown hit and in-person workflows vanished overnight, I found myself staring at the same problem most small finance teams were dealing with: messy spreadsheets, manual updates, and dashboards that felt outdated by lunchtime. I was working with a small portfolio of client businesses—mostly retail and service-based—helping them track cash flow and bookkeeping during a period of extreme uncertainty. That’s when I started fusing my old Excel comfort with something I’d always been curious about: Python.

At first, I just wanted to stop manually copying figures from bank exports into client P&L templates. By the end of that year, I had built a repeatable workflow that combined Excel for structured layouts and data entry, with Python handling data cleaning, transformation, and automation. What emerged wasn’t just cleaner bookkeeping—it was a version of “finance dashboards as code” that could be rerun on any new set of transaction data without rebuilding a single formula.

Starting with the Excel foundations

I still believe Excel is the right place to start for most finance dashboards, especially in small businesses or consulting work. Templates are easy to review, auditable, and stakeholders already know how to open them. The key is to design your Excel files with automation in mind, not just quick manual entry.

For each client, I created a simple standardized template with three main sections: a raw data sheet (e.g., Transactions), a categorized sheet (Journal), and a reporting sheet (Dashboard). The raw data sheet held all bank and card exports, with only a few fixed columns: Date, Description, Amount, Account, and Category (initially blank). The Journal sheet used formulas to normalize amounts, add running balances, and flag any values that fell outside predefined thresholds. The Dashboard sheet then summarized these into rolling revenue, expenses by category, and month-over-month changes.

This structure gave me two huge advantages. First, I could reuse the same Excel framework across multiple clients with minimal tweaks. Second, I created a clean, predictable “contract” between Excel and Python: the same column names, order, and data types every time, which made the Python scripts composable and much easier to debug.

Using Python to automate bookkeeping work

On the Python side, I started small. I used pandas to read CSV and Excel files, apply basic transformations, and then write the cleaned data back into fresh Excel workbooks. The biggest time-saver was automating the “initial categorization” step. Instead of manually typing categories for every transaction, I created rule-based mapping logic that matched keywords in the description to predefined categories.

For example, any description containing words like “amazon”, “flipkart”, or “amazon pay” would be mapped to “Procurement”, whereas “pubg”, “amazon prime”, or “netflix” would go to “Subscriptions and Entertainment”. The rules were stored in a simple dictionary, which I could later version-control in a categories.yml file or a spreadsheet tab. Over time, I added a small natural-language-style rule engine (using regex patterns and fuzzy matching) to catch edge cases: “AMZN*”, “*AMAZON*”, “PAYTM*”, etc.

For one client, this automation reduced the time to categorize a month’s worth of transactions from roughly 4–5 hours down to under 30 minutes. The script would:

Read the raw exported CSV.
Drop unnecessary columns and parse dates.
Apply the rule-based category mapping.
Identify and flag any transactions that didn’t match any rule (potential new categories or errors).
Write the cleaned data back into a properly formatted Excel file that plugged directly into the existing dashboard template.

That last step was critical. By keeping the Excel layout consistent, I enabled non-technical stakeholders to still open the file, tweak a few formulas, and regenerate figures without touching Python. They didn’t need to understand code; they just needed to trust the process.

Generating finance dashboards programmatically

Once the data pipeline was in place, I expanded the Python script to generate basic dashboards directly in Excel. Using libraries like openpyxl and XlsxWriter, I automated the creation of charts and summary tables so that every new month’s file came with a fresh set of visuals already populated.

I focused on a small core set of views that aligned with typical lockdown-era questions: cash runway, revenue trends, and expense volatility. For each client, the script would automatically:

Create pivot-style summaries of monthly revenue and expenses by category.
Plot line charts of month-over-month revenue and expenses, highlighting any month that dropped more than 20% compared to the prior month.
Calculate and display a simple cash-runway metric: current cash balance divided by average monthly outflows, assuming no change in spending.

Because the data was already in a structured format, these calculations were easy to express in Python and then write into designated “summary” cells that Excel charts referenced. The end result was a dashboard that updated with a single click (or a scheduled script run) instead of a full day of manual reformatting.

This approach also helped me answer questions that were painful in pure Excel. For example, I could quickly run simulations: “What if we cut all non-essential subscriptions by 50%?” By modifying the category list and re-running the script, I could generate a new version of the dashboard with projected savings, and then show the client side-by-side screenshots of the base case and the optimized scenario.

Lessons from lockdown: simplicity, auditability, and iteration

That lockdown period taught me three things about using Excel + Python for finance dashboards.

First, simplicity beats elegance. It’s tempting to build a full-stack web dashboard or a complex notebook, but for many small businesses, the most useful tool is a clean Excel file attached to an automated Python script. The barrier to entry is low, and people already know how to comment on cells or share files via email.

Second, auditability matters more than automation. I made sure that every step in the pipeline left a clear paper trail. The raw export lives in one folder, the Python script writes an intermediate CSV, and that CSV is then imported into Excel. If something looks wrong, it’s easy to open the raw file, compare it to the final dashboard, and see where the logic diverges. This was especially important when working with clients who were nervous about “black-box” code affecting their books.

Third, iteration is possible because of code. Once the initial pipeline was built, adding new features felt nothing like rebuilding an Excel workbook from scratch. I could add a new category, a new chart, or even a basic forecasting model (e.g., exponential smoothing) by writing a few extra lines of Python and then regenerating the Excel file. In one month, I went from a static historical view to a simple 3-month rolling forecast embedded in the same dashboard, just by enhancing the script.

If you’re a finance professional who’s comfortable with Excel but hasn’t yet explored Python, lockdown-era bookkeeping taught me this: you don’t need to start from scratch. Start with one repetitive task—like transaction categorization, month-end reporting, or cash-runway calculation—and automate it with a short script. Then let that script power a slightly smarter Excel dashboard than the one you had before. Over time, those small wins compound into a workflow that feels a lot more resilient than manual bookkeeping ever did.