← Back to work

Automation · Excel

Hotel Order Form Automation

Replaced a 30+ hour manual process generating 116 personalized hotel order forms with a single Python script that runs in under 5 minutes.

116

Hotels

30hrs → 5min

Time saved

557

Lines of code

0

Hardcoded mappings

The Challenge

Every year, Dr. Hotellato sends each of its 120+ hotel clients a personalized Excel order form containing their specific product catalog, negotiated pricing (with hotel-specific discount rates), and previous year's order history. The data lives across six separate Excel workbooks — product catalogs, discount tables, sales history, customer contacts — totalling over 6,000 rows of data.

For the 2026 cycle, the operations team faced updating all 116 hotel order forms with new prices, updated discount rates, and refreshed sales quantities. At roughly 15–20 minutes per hotel, this represented over 30 hours of repetitive, error-prone copy-paste work.

The complexity came from the pricing logic: each product's price depends on whether it qualifies for a hotel-specific discount, what that hotel's negotiated rate is (which can differ between 2025 and 2026), and whether the hotel operates in EUR or HUF.

First Approach — and Why It Failed

The initial automation strategy seemed straightforward: take the existing 2025 order forms, update prices and quantities in place. Over the course of a week, this produced 15 scripts totalling 6,000+ lines of code — along with fix scripts, validation scripts, audit logs, and patch scripts.

The core problems:

  • Column mapping ambiguity. The source spreadsheets had inconsistent layouts across different sheets, leading to prices being pulled from wrong columns — including a critical bug where already-discounted prices were discounted again.
  • Hotel name matching. The same hotel appears differently across files: “Aqua Helia Kft. Session Hotel” in one file, “Aqua Helia Kft. / Session Hotel” in another. This required 40+ hardcoded name mappings that were fragile and incomplete.
  • Escalating patches. Each fix introduced new edge cases, creating a cycle where more code was written to fix previous code. The team ended up manually completing 58 hotels as a fallback.

The Turning Point

A conversation with the operations manager revealed a critical insight: he had already designed a template that solved 80% of the problem using Excel's native FILTER and VLOOKUP functions. The template was built so that simply typing a hotel name would auto-populate all products and prices.

The limitation? Excel's FILTER function requires the source data file to be open — making it impossible to use in batch processing.

This reframed the entire problem. Instead of replicating complex pricing logic in Python, the automation only needed to replace the FILTER step: look up each hotel's products from the sales history and populate them into the template. Excel's own formulas would handle the rest.

The Solution

A single Python script (557 lines) that:

  1. Loads six source files into memory
  2. Matches hotel folders to sales data using normalized fuzzy matching with Unicode-aware comparison
  3. Computes fallback prices applying the correct discount logic
  4. Clones the .xlsm template for each hotel, preserving VBA macros and Excel formulas
  5. Fills product rows with codes, names, computed prices, quantities, and calculation formulas
  6. Generates a report summarizing successes, failures, and unmatched hotels

A key architectural decision was the hybrid approach: writing both computed price values (so files work immediately without source files open) and keeping the template's VLOOKUP formulas intact (so prices can recalculate when source files are available).

Before & After

  Manual Process First Automation Final Solution
Time to complete 30+ hours ~1 week of iteration Under 5 minutes
Scripts/complexity — 15 scripts, 6,000 lines 1 script, 557 lines
Hardcoded mappings — 40+ manual entries 0
Success rate 100% (but slow) 115/116 with errors 97/97 matched
Manual intervention Every hotel 58 hotels done by hand 19 hotels with no sales data

Technical Challenges Solved

Unicode normalization

normalize.py python 
import unicodedata

def normalize_name(name: str) -> str:
    # macOS: NFD (decomposed) — é = e + ́
    # Excel: NFC (composed)  — é = é
    return unicodedata.normalize('NFC', name.strip().lower())

# Result: match rate doubled from 46 → 94 hotels

macOS stores filenames in NFD Unicode while Excel uses NFC. Hungarian characters like é can be one or two characters — invisible to the eye but causing silent comparison failures.

Merged cell handling

The Excel template uses merged cells for headers and footers. openpyxl raises errors when writing to merged cells, requiring a safe-write wrapper that detects and skips them.

Macro preservation

The templates contain VBA macros (.xlsm format). Using openpyxl's keep_vba=True flag ensures macros survive the template cloning process.

Sample Order Form Output

Order Form
# Code Product Unit Price Qty Total
1 CM-001 Cinq Mondes Body Cream 30ml €2.40 200 €480.00
2 CM-002 Cinq Mondes Shower Gel 30ml €1.80 350 €630.00
3 NX-001 Nuxe Face Cream 15ml €3.20 150 €480.00
4 DM-001 Damana Bath Salt 40g €1.50 400 €600.00
5 TY-001 Typology Serum 10ml €4.10 100 €410.00

Results

The 19 unmatched hotels were not failures — they genuinely had no entries in the sales history, meaning there were no products to populate. These require manual handling regardless of approach.

Key Takeaways

Talk to the domain expert first.

The most impactful technical decision — replacing the FILTER function instead of reimagining the pricing logic — came from understanding how the operations manager had already designed the template.

Clean inputs beat clever code.

The new approach succeeded largely because the operations team prepared purpose-built source files with consistent structure.

Stop patching, start over.

The first approach's “fix spiral” consumed more effort than building the correct solution from scratch. Recognizing when to abandon an approach is as important as the technical work itself.

Tech Stack

Python 3 openpyxl Excel (.xlsm) VBA unicodedata

Let's Talk

I take on 3 new clients per month.

The businesses that move first win. Let's find where you're leaving 20+ hours a week on the table.

Book Your Free Audit

● 2 spots remain for March — next availability: April