Stop Using Excel Macros for Unstructured Ops Data

Every Tuesday morning, an operations coordinator downloads 40 supplier reports, opens a master Excel workbook, and clicks a button labelled 'Run Macro'. Half the time, the script works. The other half, it crashes entirely because a freight partner in Melbourne added an extra column to their packing list, or a venue in London changed their date format from DD/MM/YYYY to text.

Macros assume a perfect world. Operations happen in a messy one.

For years, operations teams have relied on Visual Basic for Applications (VBA) to duct-tape their workflows together. It was the only way to avoid manually rekeying hundreds of rows of data across different spreadsheets. But as transaction volumes grow, and as the data entering your business becomes less structured—think PDF commercial invoices, poorly formatted email briefs, and WhatsApp messages—Excel macros hit a hard limit. They cannot read context. They cannot triage. They simply expect data to be in cell C4, and if it is not, they fail.

This is why commercial leaders are stepping away from in-sheet macros and moving towards Python to handle their Excel automation. Python sits outside the spreadsheet, acts as a staging area for messy data, introduces AI to read the unstructured parts, and only writes clean, validated information back into the grid for human review.

Here is how that workflow actually functions in practice, and why it is the bridge between manual data entry and commissioning expensive custom software.

The Problem with Inside-Out Automation

VBA macros operate from inside the spreadsheet looking out. They are restricted by the limitations of the workbook. If you need to pull data from an email inbox, extract line items from a PDF invoice, and cross-reference them against a live database before updating your tracking sheet, VBA requires an unstable tower of workarounds.

We see this constantly in event operations and logistics. An exhibition organiser running a 200-stand show might have three people managing an inbox full of health and safety documents, contractor details, and stand graphics. They try to use routing rules to sort the emails, but fixing the document triage bottleneck in operations requires more than basic filters.

When a contractor emails a scanned public liability insurance certificate, Excel cannot read it. Your team falls back to opening the attachment, finding the expiry date, and typing it into the master tracker by hand. If you are processing 500 of these a month, you are burning dozens of expensive hours on pure transcription.

CRM systems are equally terrible at this. If you try to force unstructured attachments into a standard customer database, you will quickly discover why CRM workflows choke on unstructured ops data. They want clean, categorised inputs. The real world rarely provides them.

The Python and AI Workflow

Moving your automation logic to Python changes the architecture. Instead of the spreadsheet trying to pull data in, Python acts as an external engine that prepares the data and pushes it to the spreadsheet when it is ready.

Crucially, because Python is a modern programming language, it easily connects to large language models (LLMs) to handle the unstructured mess before it ever reaches your Excel file.

Here is what a practical, AI-assisted Python workflow looks like for an importer handling supplier packing lists:

1. Ingestion and Triage

The Python script watches a designated inbox (e.g., invoices@yourcompany.com.au). When an email arrives from a supplier, the script downloads the attachment. It does not matter if the supplier sent a CSV, a PDF, or just wrote the container details in the body of the email. Python grabs the raw material.

2. AI Extraction

Instead of looking for fixed coordinates on a page, the script sends the document to an AI model with strict instructions: "Find the container number, the total gross weight in kilograms, and the port of origin. Return this data as JSON format only."

The AI reads the document contextually. It knows that "Gross Wt: 4500kg" and "Total Weight: 4.5t" mean the same thing, extracting the value accurately regardless of the specific phrasing the supplier used.

3. Validation

Before anything touches your spreadsheet, Python runs a hard logic check. Did the AI return a valid date? Is the weight a number? Does the port of origin match your approved list of trade lanes? If the data fails these checks, it is flagged for manual review.

4. The Spreadsheet Handoff

Finally, Python uses an API (or libraries like pandas and openpyxl) to write the extracted, validated data into a specific tab in your Excel file or Google Sheet—often called the 'Triage' or 'Awaiting Approval' tab.

It writes the data cleanly, adds a direct hyperlink to the original source document, and highlights any low-confidence extractions in yellow.

Designing the Human Review Stage

The most important part of this workflow is not the code; it is the handoff. Do not build systems that overwrite live operational data automatically.

When you use AI to extract data from messy documents, it will occasionally make mistakes. A blurry scan might cause it to read an '8' as a '3'. A supplier might write something completely ambiguous. If your system pushes this straight into your active freight tracking sheet, you will ship the wrong container or pay the wrong tariff.

Your operators need a clear boundary between 'proposed data' and 'active data'.

The Python script should populate a staging area. Your operations team then logs in, looks at the new rows, clicks the link to view the original PDF on their second monitor, and verifies the extraction. If it is correct, they change a dropdown status from 'Pending' to 'Approved'.

Only then does a secondary script (or a simple formula) move that data into the main operational pipeline.

This keeps the human firmly in the loop. The AI does the heavy lifting of reading the document and typing out the data, saving the operator ten minutes per file. The operator acts as an editor, applying commercial judgement and QA.

The Cost and Speed of Deployment

One of the main reasons B2B product studios recommend Python for early-stage workflow automation is speed.

If you have a manual process that touches five spreadsheets before a quote goes out, you do not need to wait six months and spend £80,000 to build a custom web application from scratch. You can test the logic far faster by leaving the spreadsheets in place as the user interface, and building Python scripts to handle the data movement in the background.

AI-assisted product delivery can drastically shorten discovery-to-release cycles. Because the heavy lifting of parsing text is handled by the LLM, developers no longer need to write hundreds of regular expressions to catch every possible date format a supplier might use. They write a clean prompt, wrap it in a Python script, and connect it to your Excel file via API.

This approach allows you to stand up a working prototype in weeks. You can run it parallel to your manual process, measure the error rate, and calculate the actual time saved before you commit to further development. (If you want to map out the financial viability of this, run your numbers through the AI Project Cost Calculator to compare the build cost against your current manual baseline).

When to Abandon the Spreadsheet Entirely

Python can extend the life of your Excel-based operations by years. It stops the crashes, ends the manual rekeying, and systemises your inbox.

But it is still a halfway house.

Spreadsheets are fundamentally designed for individual analysis, not multi-user operational databases. If you have ten people trying to update the same 'Awaiting Approval' tab simultaneously, you will run into locking issues, accidental deletions, and version control chaos.

You should use Python to prove the automation logic works. You use it to find out exactly what data you need to extract, where the edge cases are, and how your team prefers to review the AI's output.

Once that workflow is stable—once your script is processing 1,000 supplier documents a week with a 98% accuracy rate on the first pass—that is when you commission a custom web application. You take the exact same Python and AI logic you have already built, and you put a proper, permission-controlled, multi-tenant interface on top of it.

You graduate from the spreadsheet only when the volume demands it, not because you are chasing the idea of new software.

Useful tool

Try Samvara's Document Readiness Checklist — Export/import docs by mode.