Converting Excel Workbook to Robust Full‑Stack Web Application

Overview

A mid‑sized financial institution was running a core internal process—portfolio exposure tracking and approvals—on a sprawling Excel workbook shared across teams. The file had grown over years into a de facto application, with complex formulas, hidden sheets, and manual macros. This created operational risk, version chaos, and made audits painful. The institution engaged our team to convert this spreadsheet into a robust, browser‑based full‑stack solution.

The Challenge

The Excel "application" was originally built by a subject‑matter expert and gradually evolved into a mission‑critical tool used by risk, finance, and operations. Over time, several issues became acute:

• Multiple versions in circulation, leading to inconsistent numbers and rework.
• Fragile formulas and macros that only a few people understood, increasing key‑person risk.
• Manual copy‑paste steps to feed other systems and reports, introducing data‑entry errors.
• Limited security and access control—anyone with the file could see or change sensitive data.
• Difficulty meeting audit and regulatory expectations for traceability and change control.

Leaders recognised these are common pain points for organisations whose "applications" live in Excel or MS Access, especially in regulated sectors like financial services.

Goals and Success Metrics

Together with the client, the key objectives were defined:

• Replace the Excel‑based application with a secure, role‑based web system.
• Preserve all critical calculations and business rules while eliminating manual steps.
• Provide a single source of truth for data, with full audit trails and reporting.
• Reduce operational risk tied to spreadsheets and ad‑hoc Access databases.

Success would be measured by reduction in manual effort, improved data quality, audit outcomes, and user satisfaction.

Our Approach

1. Discovery and Model Extraction

The first step was to "reverse‑engineer" the living Excel workbook:

• Mapped all input sheets, intermediate tables, and output reports.
• Documented formulas, VBA/macros, and business rules in plain language.
• Identified data entities (e.g. portfolios, positions, limits, approvals) and how they related.

This formed the basis of a clean data model and a clear specification of required functionality, which is equally applicable for Access‑based tools.

2. Target Architecture and UX Design

Next, a modern full‑stack architecture was defined:

• Database layer: Normalised schema to store positions, limits, users, approvals, and reference data, replacing hidden sheets and Access tables.
• Back‑end services: APIs implementing the business rules previously embedded in formulas and macros.
• Front‑end web app: Role‑aware screens for data entry, validation, approvals, and reporting, designed for non‑technical users.

Wireframes were validated with users to ensure that the new interface matched and improved their daily workflow, rather than just "rebuilding Excel in a browser."

3. Incremental Build and Migration

To minimise disruption:

• The core calculation engine and data capture flows were implemented first, then run in parallel with the existing spreadsheet for a period.
• Automated tests compared outputs from the new system with results from the legacy Excel model using historical scenarios, ensuring numerical accuracy.
• Data migration scripts were created to import existing Excel data into the new database with validation and error reporting.

This gave stakeholders confidence that the new system was trustworthy before decommissioning the spreadsheet.

4. Security, Audit, and Reporting

Regulatory expectations were addressed explicitly:

• Role‑based access control ensured users only saw and edited data relevant to their function.
• Full audit trails captured who changed what and when, replacing opaque cell edits in Excel or Access.
• Standard and ad‑hoc reports were built into the application, eliminating manual extraction and manipulation in spreadsheets.

The result was a solution that auditors could understand and rely on, and that internal teams could operate confidently.