Modernizing Legacy Spreadsheets into Cloud Applications with Amazon Q Developer

Some of the most critical business logic in enterprises lives in spreadsheets. Not in applications. Not in databases. In .xlsb files that one person understands, everyone depends on, and nobody dares touch.

I recently had to modernize exactly this kind of beast — a proof-of-value index calculator that ran the business. Here’s how I used Amazon Q Developer to turn it into a Python module with a Streamlit UI, and the exact prompts and iteration strategy that actually worked.

The Problem

A proof-of-value index calculator. Multiple interconnected sheets. Formulas referencing across sheets. Two output columns — idxDate and index — that the business relies on daily.

The spreadsheet is a .xlsb file (Excel Binary Workbook). It’s fast for Excel but opaque for everything else. No one can review the logic without opening it in Excel and tracing formulas cell by cell.

The goal: convert this into a Python program that produces the same outputs, is testable, and can be deployed as a web application.

The Strategy: Iterative Prompting

I didn’t try to do everything in one shot. That never works with complex transformations. Instead, I broke it into five steps, each building on the previous one.

Step 1: Extract the Formulas

The first challenge — .xlsb files don’t export formulas easily. CSV export gives you values, not formulas. But we need the formulas to understand the business logic.

Prompt:

I need to convert the proof_of_value_index.xlsb into a document that retains the different sheets as CSV with as a mandatory requirement to preserve the formula and not the values in the spreadsheet.

Amazon Q generated a Python script using openpyxl that scans each cell, detects formulas, and exports them as strings instead of computed values.

First iteration failed — it only exported one sheet.

Step 2: Fix Multi-Sheet Export

Prompt:

The Excel contains several sheets, and I get only one.

LibreOffice’s default CSV conversion only handles the first sheet. Amazon Q rewrote the script to handle multiple sheets properly.

Step 3: Cell-Level Formula Detection

Prompt:

The script is incorrect. I want for each cell, if it is a formula, to export the Formula string itself, not the value. The script has to manually scan all cells and identify if it is a formula or a value and output accordingly into a Python dataframe and then export as CSV.

This is where the real work happened. The script now inspects every cell individually and preserves the formula text for formula cells, values for data cells.

It worked.

Step 4: Generate the Equivalent Python Program

This is the big one — turning the extracted formulas into executable Python logic.

Prompt:

You received a folder csv_output_with_formulas containing several CSVs that may have data dependencies between each other. The final spreadsheet is calculator.csv that contains multiple steps of data transforms and two output columns: idxDate, index.

Your goal is to:

• Build a functionally equivalent Python program that generates idxDate and index
• Identify CSV dependencies by reading the Excel formulas to build a dependency graph
• CSV sources should be provided as parameters to the script
• CSV metadata (like holidays.csv) should also be parameters
• Use pandas and dataframes
• Keep CLI code separate from functional code for reuse as a module

Amazon Q analyzed the formula dependencies, built a DAG of sheet relationships, and generated a calculator_generator.py that reproduces the spreadsheet logic in pure Python.

Step 5: Build the Streamlit Demo

Prompt:

Build a Streamlit application that reuses the calculator_generator.py. Refactor the script to separate CLI from functional code. All input parameters should be document uploads in the UI. Identify the best graphical representation of results. Simple parameters like tenor and nu_days should be input fields.

The result: a web UI where business users upload their input files, configure parameters, and get the same index output — with visualizations that make the data actually understandable.

What Made This Work

1. Iterative refinement. Each prompt built on the previous result. I didn’t try to solve everything at once.
2. Precise requirements. “Preserve formulas not values” and “separate CLI from functional code” gave the AI clear constraints.
3. Immediate validation. After each step, I verified the output before moving to the next prompt.
4. Architecture decisions up front. Deciding early that the functional code would be a reusable module made Step 5 trivial.

The Pattern

This workflow generalizes beyond this specific case:

Legacy artifact (Excel/Access/VBA)
    ↓ Step 1: Extract business logic in machine-readable form
    ↓ Step 2: Fix edge cases in extraction
    ↓ Step 3: Generate equivalent modern code (Python/JS)
    ↓ Step 4: Add proper interfaces (CLI, module API)
    ↓ Step 5: Build user-facing application (Streamlit, React)

Each step is a single, focused prompt. Each output is verifiable before proceeding.

Key Takeaways

• Critical business logic hiding in spreadsheets is a modernization target — treat it seriously
• Don’t try to convert complex spreadsheets in one prompt — break it into extraction, generation, and UI
• Preserve formulas as strings first, then convert to code — going directly from values to code loses the logic
• Amazon Q Developer handles the mechanical translation well if you give it precise, scoped instructions
• Separate functional code from UI/CLI code immediately — it pays off when you need to reuse it
• The resulting Python module is testable, versionable, and deployable — the spreadsheet was none of these

Any opinions in this article are my own.