Syed Waqas Tayyab · IT Expert & AI Engineer · HiTecH Technology HUB

From Excel to Enterprise Web App: How I Built an IT Asset Manager in Python

"Can you tell me how many unassigned laptops we have?" Simple question. Before I built this app, answering it required opening a shared Excel file, scrolling through hundreds of rows, applying filters, hoping the data hadn't been corrupted by whoever last edited it, and spending 10 minutes to surface a number that should take 10 seconds.

That question — asked by my IT manager on a Monday morning — is why I built the IT Asset Manager.

Syed Waqas Tayyab

Senior IT System Engineer · SAP Saudi Arabia · 15+ Years

Azure Security Certified · SAP AI Hub · MBA · MENA IT Expert

The Excel Problem

The shared Excel file had been the system of record for hardware assets since anyone could remember. On paper, it worked. In practice:

• No audit trail. If a serial number changed, no one knew when or why.
• Duplicate serial numbers. Two people adding the same device at the same time created corrupted records.
• No validation. Every field was free text — typos, inconsistent formatting, missing mandatory data.
• Terrible search. Ctrl+F in a 580-row spreadsheet across 12 columns is not a workflow.
• Broken exports. Sending the Excel file to management meant formatting it manually every time.
• No dashboard. Low stock? End-of-life devices? Warranty expiry? All invisible.

The Excel file wasn't a system — it was a shared text document pretending to be a database.

Building the Replacement

I chose Python + Flask for the backend because I needed something I could build and deploy quickly on my local Mac without any server infrastructure. SQLite for the database — appropriate for a single-location asset register with ~1,500 records and no concurrent write conflicts.

The Data Model

The core assets table captures everything relevant to an enterprise IT asset:

CREATE TABLE assets (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    asset_type    TEXT NOT NULL,        -- Laptop, iPhone, iPad, Monitor, Printer
    status        TEXT NOT NULL,        -- Assigned, Unassigned, In Repair, Retired
    serial_no     TEXT UNIQUE NOT NULL, -- Enforced uniqueness at DB level
    sap_eq        TEXT UNIQUE,          -- SAP equipment number (ERP reference)
    model         TEXT NOT NULL,
    assigned_to   TEXT,
    location      TEXT,
    purchase_date TEXT,
    warranty_exp  TEXT,
    notes         TEXT,
    created_at    TEXT,
    updated_at    TEXT
);

CREATE TABLE asset_history (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    asset_id   INTEGER,
    field      TEXT,
    old_value  TEXT,
    new_value  TEXT,
    changed_at TEXT
);

The UNIQUE constraint on serial_no does what Excel never could — it makes duplicate serial numbers a database error, not a user error.

Smart Excel Import

The most-used feature after the dashboard. The import logic handles two modes:

Add Only — safe for regular use. New records from the Excel file are inserted; existing serial numbers are skipped. Zero risk of overwriting live data.

Add + Update — for bulk refreshes. New records are inserted; existing records are updated if any field has changed. A history entry is written for every change.

Both modes filter garbage headers — Excel files from vendors often have metadata rows at the top. The import skips any row where the first cell doesn't look like an asset type.

def smart_import(file, mode="add_only"):
    wb = load_workbook(file)
    ws = wb.active
    imported, skipped, updated = 0, 0, 0

    for row in ws.iter_rows(min_row=2, values_only=True):
        if not is_valid_asset_row(row):
            skipped += 1
            continue
        
        existing = db.execute(
            "SELECT * FROM assets WHERE serial_no = ?", [row[2]]
        ).fetchone()

        if existing and mode == "add_only":
            skipped += 1
        elif existing and mode == "add_update":
            update_asset_with_history(existing, row)
            updated += 1
        else:
            insert_asset(row)
            imported += 1

    return imported, skipped, updated

The Dashboard

The dashboard answers the questions an IT manager actually asks:

• KPI cards: Total assets, Assigned, Unassigned, In Repair — live counts from SQLite
• 6-month trend chart: Using Chart.js, showing asset additions per month
• Low stock alerts: Categories where available stock drops below threshold
• Oldest stock: Assets with the earliest purchase dates — warranty risk visibility
• Last import: Timestamp of the most recent Excel import

All rendered server-side by Flask + Jinja2, no external API calls, sub-100ms page loads.

The AI Chat Widget

The most impressive feature to demo. A floating chat widget in the bottom-right corner accepts natural language queries and returns card-formatted results:

"Show me all unassigned laptops in Riyadh" "How many iPhones are in repair?" "List assets assigned to employees who left"

Under the hood, the query is sent to the Claude API with a system prompt that includes the database schema. Claude generates a SQLite query. The app executes it safely (read-only, parameterised) and formats the results as cards.

That 10-minute Monday morning question now takes 10 seconds — typed in plain English.

The Colour-Coded Excel Export

Every management request for an asset report used to mean an hour of formatting. Now it's one click.

The export generates a multi-sheet Excel workbook using OpenPyXL:

• Sheet 1: All assets, colour-coded by status (green = assigned, yellow = unassigned, orange = in repair, red = retired)
• Sheet 2: Summary pivot — counts by category and status
• Sheet 3: Assets flagged for warranty review

from openpyxl.styles import PatternFill, Font

STATUS_COLORS = {
    'Assigned':   PatternFill(fgColor='90EE90', fill_type='solid'),  # green
    'Unassigned': PatternFill(fgColor='FFD700', fill_type='solid'),  # yellow
    'In Repair':  PatternFill(fgColor='FFA500', fill_type='solid'),  # orange
    'Retired':    PatternFill(fgColor='FF6B6B', fill_type='solid'),  # red
}

Management gets a formatted report. IT gets 60 minutes back every month.

Results After Deployment

1,500+ assets now tracked with 100% accuracy. The Monday morning question takes 10 seconds.

What This Taught Me

Building this app from scratch — no tutorial, no starter kit, just the Flask docs and a clear problem to solve — taught me more about software engineering than any course I've taken.

The key insight: the database design is the most important decision you make. Getting the schema right (unique constraints, history table, proper field types) took half a day. It saved months of data integrity issues.

The second insight: audit logs are not optional. The first time someone asked "when did this asset's status change?" — three weeks after launch — I was glad I'd built the history table from day one.

Waqas Syed is a Senior IT System Engineer at SAP Saudi Arabia. The IT Asset Manager is one of several tools he has built to replace manual IT workflows with Python-powered web applications.