Put the logic outside Excel,
into Python and Flet.

Put the logic outside Excel, on the Python side.

2-03: Lay the Foundation — SQLite, PostgreSQL, pgvector, DuckDB, Polars laid down where the data sits. This chapter moves that data. The macros, VBA, charts, and pivots embedded in Excel and Word go out into Python, and where a person needs a screen, Flet goes on top.

The skill you need is using, not writing

Under the old common sense, learning to program meant memorizing syntax, designing algorithms, and becoming able to write code.

It works differently now. You put into words what you want processed, have the AI write the code, run it, and check the result. The skill you need is the *skill of using*.

Compare the time it takes to memorize Excel functions with the time it takes to start having the AI write Python for you. The second is far shorter. And Excel functions work inside Excel, while Python works on any data at all.

The ability to write code is not required. The ability to read it is enough. If you can read it, you can judge whether what came back looks right. When an error appears, paste the error text as it is, and the cause and a corrected version come back. An error is not the end; it is input for the next instruction.

There are three tricks to asking. State the input and the output. Ask one thing at a time. Look at the result and correct it. "Read orders.xlsx, total sales per product, and write it to summary.json" — with the entrance and the exit fixed, the AI does not get lost.

Move the logic embedded in Excel and Word out into Python

When you step away from Office in this part, the last thing that catches is the business logic embedded inside Excel and Word: macros, VBA, charts, pivots. Those four go out into Python. That is the first move.

What sits inside VBA is usually some combination of "read data from cells, calculate something, write to other cells." Polars rewrites that directly.

1. Open Excel's VBA editor (Alt + F11)
2. Copy the module's code and hand it to the AI
3. Ask, "rewrite this in Polars + Python"
4. Paste what comes back into a JupyterLab cell and hit Shift+Enter

import polars as pl

df = pl.read_excel("orders.xlsx", sheet_name="raw")
result = (
    df.filter(pl.col("status") == "confirmed")
      .group_by("customer")
      .agg(total=(pl.col("qty") * pl.col("price")).sum())
      .sort("total", descending=True)
)
result.write_excel("monthly_summary.xlsx")

Excel itself stays. Polars reads and writes .xlsx directly, and openpyxl handles edits that must preserve formatting. There is one rule: keep it as .xlsx. Drop it to CSV and the formatting, the formulas, and the layout go with it.

VBA sits in Word and PowerPoint too

VBA is not only an Excel matter. It is embedded in Word the same way: automated mail merge, documents generated from templates, bulk reformatting, collecting and totaling forms you handed out. Every one of those goes out into Python.

• Mail merge and template documents → python-docx + Jinja2 (recipients read from the customer master with Polars)
• Bulk reformatting → walk the whole text with python-docx and replace
• Totaling distributed forms → read each Word file with python-docx, aggregate in Polars

PowerPoint macros are the same. python-pptx takes them out.

The earlier you move it, the lighter everything after

VBA does not run as-is in an Excel-compatible spreadsheet. That is the biggest catch in moving off Office. Once the logic is out in Python, the file side holds only data and layout, and it sits straight on the way of holding content in 2-07: Take Documents Back — Prose in AsciiDoc, Working Tables in a Grid, Printed Pages from Templates.

Moving off Office is not the only reason to externalize.

• Python code is text, so it keeps history in Git, and it can be tested and reviewed
• The code keeps running when the Office version moves. Python 3 code stays readable for a long time to come

And there is a security reason. *Until 2022, VBA was the main door for malware arriving as mail attachments.* Emotet, the most widespread case in Japan, attached macro-bearing Excel or Word files and infected the machine when the reader clicked "Enable Content" (JPCERT/CC alert, February 2022). From July 2022 Microsoft blocked macros in files from the internet by default in Office on Windows, and attacks using macro attachments fell by about 66% (Proofpoint, July 2022). But the block covers only files that arrived from outside. As long as business logic lives in VBA, the company keeps macros enabled internally, and the habit of clicking "Enable" stays. Move the logic to Python, and the reason to enable macros is gone.

"Isn't rewriting VBA into Python a big job?" The handoff is copy and paste.

Install JupyterLab — uv by default, Miniforge for scientific computing

To use Python you need a runtime. The entrance that fits office work best is JupyterLab, a "spreadsheet for Python" that runs in the browser: write Python in a cell, hit Shift+Enter, and the result appears right there. It feels like typing =SUM(A1:A100) into an Excel cell and pressing Enter.

The difference from an Excel pivot table is in what remains afterward.

• The procedure remains as a few lines of code — next month is a re-run
• "Why we compute it this way" can sit beside it in a Markdown cell — the business knowledge stays in the notebook
• Charts draw inside the cell too (matplotlib / Altair)
• Excel's row limit (2-03) is gone
• Saved as a notebook (.ipynb), it keeps history in Git
• When the person in charge changes, opening the notebook is enough to carry on

How to install Python itself and its libraries is a choice between two, by purpose.

Start with uv. It covers almost every situation.

# Mac / Linux: official installer (Windows has a one-line PowerShell version)
curl -LsSf https://astral.sh/uv/install.sh | sh

uv init tools && cd tools          # one Python environment for the work
uv add jupyterlab polars altair    # install; later chapters' tools go here too
uv run jupyter lab                 # open

The browser opens, you make a new notebook, write in a cell, hit Shift+Enter. That is the whole of it. This environment is where this series' Python lives; 2-03's DuckDB goes in here as well.

Where uv runs into walls is mostly scientific computing: builds of numpy / scipy / pytorch / tensorflow failing locally (linking against BLAS / LAPACK / CUDA), GIS (gdal, rasterio) or bioinformatics libraries with enormous C/C++ dependencies, deep learning on GPUs where CUDA versions must line up. Switch to Miniforge there. It gives you conda's dependency resolution without Anaconda's commercial terms.

# Miniforge (fully FLOSS)
curl -L -O https://github.com/conda-forge/miniforge/releases/latest/download/Miniforge3-$(uname)-$(uname -m).sh
bash Miniforge3-*.sh

# from here on, environments are made like this
conda create -n ds jupyterlab polars numpy scipy scikit-learn
conda activate ds

Turn pivots and VLOOKUP into Polars code

Combine Polars with JupyterLab and what you did in Excel with pivot tables, VLOOKUP, IF, and filters becomes code directly.

A product-by-month cross tab in Excel means dragging "product" to rows, "month" to columns, and "sales" to values with the mouse: a few minutes. Next month you move the same mouse again. In Polars it is two lines.

df = pl.read_excel("orders.xlsx")
df.pivot(values="price", index="item", on="month", aggregate_function="sum")

Shift+Enter in the cell and the cross tab appears below. Next month is a re-run. Replacing VLOOKUP takes the same shape.

orders   = pl.read_excel("orders.xlsx")
products = pl.read_excel("products.xlsx")

orders.join(products, on="item_id", how="left")

Joining on several columns is just on=["item_id", "date"]. Adding a computed column by condition is a stack of when().then() from the top down, so you stop counting nested IFs as you write.

The syntax is something you can leave unlearned. Ask in your own words — "read orders.xlsx, total monthly sales per product, take the top 10 products, and add the month-on-month percentage" — and the Polars code comes back.

Draw charts with matplotlib and Altair

Two libraries cover visualization.

matplotlib is the de facto standard that draws anything: line, bar, scatter, histogram, heatmap, 3D, maps, publication-quality figures, on more than twenty years of accumulation (first released in 2003). The capability is the largest and the writing is the longest.

Altair is declarative: "this column on X, this column on Y, color by this column." Vega-Lite is underneath, and the output is interactive HTML as it stands. Zoom, hover, and selection come with it.

import altair as alt
import polars as pl

df = pl.read_excel("orders.xlsx")
alt.Chart(df).mark_bar().encode(
    x="item",
    y="qty:Q",
    color="month",
)

Shift+Enter in a JupyterLab cell and a stacked bar chart appears below. The same figure in Excel takes a few minutes of pivot, chart, and legend adjustment. Here it is six lines.

Memorizing the syntax of either library is hard work for a person, so don't. Ask for "monthly sales from orders.xlsx as a stacked bar chart colored per product, legend top right, Y axis in millions," look at what comes back, and reply "make the colors calmer." That is the whole loop.

The difference from Excel's chart menu shows up in monthly regeneration and in reproducibility. Mouse work leaves no record, so next month you move the same hand again. Code stays as the record, goes into Git, and python report.py emits PNG, SVG, or HTML.

Peel the human-facing I/O off the core system

This is the pivot of the chapter.

The core systems (ERP, business systems, the data warehouse) stay as the *system of record* for data the organization shares. Leave that alone. But these four are not the record's job.

• The screens for entering data
• The machinery that generates reports and dashboards
• The machinery that builds aggregates
• The machinery that emits forms, invoices, and notification mail

These are input and output for people. Peel them off the core and bring them down to JupyterLab, Python, and SQLite on your own machine. The relationship with the core becomes reading and writing through an API or a JSON / Parquet export.

Load is the other reason to split them

A core system is optimized as the system of record: small fast transactions, pulling one row through an index (OLTP). Forms, charts, and aggregates have the opposite character — scan millions of rows, aggregate, join several tables: long, heavy queries (OLAP).

Run both on the same system and this follows.

• They compete for CPU and memory with ordinary business processing
• Lock contention slows the transactions down
• The month-end report run freezes the sales system — that is the accident that happens

Export the data you need from the core to SQLite / Parquet on a schedule (or read it through an API), then aggregate and produce the forms locally, and the core carries only a light read-only load. The aggregation runs as many times as you like on your own machine. Aggregation is the job of Polars and DuckDB (2-03).

*Bringing human-facing I/O down to your own machine is a productivity move and, at the same time, a load split that protects the core.* Because of that split, the core rewrite in 2-12: Build an API — Expose Core Logic with FastAPI becomes a small job that deals only with the system of record.

What comes down to your own machine

Peel those off and part of the complexity the organization was carrying is no longer needed.

• "I want to see sales data by month and product" — ask the IT department, report from the BI tool, email a few days later — becomes one cell in JupyterLab
• The ERP screen and permission scheme for updating the customer master comes down to SQLite + Python
• In place of BI licenses and a specialist for dashboards, Altair stands: you write it yourself and hand it out as HTML
• The reporting server for monthly reports comes down to cron + a Python script
• The data mart for BI comes down to SQLite / Parquet files

Invoicing comes down the same way

The invoices that came out of accounting software or an ERP screen have the same shape. Accounting itself (journals, tax) stays in the accounting software; what comes down is the issuing side.

• Customer master and transactions → SQLite (2-03)
• The invoice form → AsciiDoc / HTML (2-07)
• The generation script → Python + the AI (this chapter), PDF via pandoc / weasyprint

A hundred invoice PDFs generated at month end in one pass, mail delivery included, all written in Python, scheduled with cron. The data stays on your side. Quotes, contracts, monthly reports, product catalogs, delivery notes — everything with the same structure comes down from the core to your own machine.

Start tools at the CLI and extend to Flet

The logic you brought down now takes the shape of a tool people use. Do not reach for Flutter, React Native, or Swift at the start. Climb three layers from the bottom.

What an app is, at bottom, is: take input, process, produce output. So the first thing to write is a command-line tool. It is easy to test, easy to fix, and easy for the AI to write. Once it runs, you can ship it — uv tool install <your-tool> reaches every OS that has Python. Processing data, converting files, calling APIs: tools of this kind finish at Layer 1.

You go up to Layer 2 when the operator is not an engineer, when visual feedback matters, or when there are several input fields.

Flet is the choice because the same Python runs on every screen

Flet is a GUI framework you write in Python. It uses Flutter's rendering engine inside, but what you write stays Python. The CLI logic from Layer 1 rides onto the screen almost unchanged.

import flet as ft

@ft.component
def Greeting():
    name, set_name = ft.use_state("")
    return ft.Column([
        ft.TextField(label="Name", on_change=lambda e: set_name(e.control.value)),
        ft.Text(f"Hello, {name}" if name else ""),
    ])

ft.run(lambda page: page.render(Greeting))

That gives a GUI with an input field and an output area. *The same code runs on Mac, on Windows, on Linux, in the web browser, and on iOS and Android.* Screens for the tools built in this series default to Flet.

The lightness of the setup counts too. No Flutter development environment (Android Studio, the SDK, Xcode) is needed; Flet needs only the Python environment (the Flutter side is unpacked only when you build for distribution). Adding a Flet screen to logic that already runs at the CLI costs the library install and a few tens of lines.

The shape of tools that run in the field

Some tools that finish at Layer 1 or Layer 2.

CLI is enough

• invoice — month-end invoice batch. Customer master (SQLite, 2-03) plus a form template → a hundred PDFs through pandoc
• minutes — meeting-minutes formatting. A .m4a recording transcribed by Whisper → the AI shapes the Markdown and pulls out the key points
• fieldlog — farm daily log. Photos and one-line notes become a journal, accumulate in SQLite, and total up monthly working hours

Flet puts a screen on

• Care-record entry — runs on a care worker's tablet. Pick a resident, type a note, take a photo → save to local SQLite → sync to your own server overnight (2-02)
• Field-management map — tap a plot on the map to view and enter its planting history
• Small-shop stocktake — photograph a product QR, enter the quantity → aggregate in SQLite → output as .xlsx (2-07)

Flet builds executables for Mac, Windows, and Linux, and the packages the App Store and Play Store take (flet build). You go up to Layer 3, Flutter, only when the screen needs something Flet's controls do not have. For internal or personal tools, Layer 1 or Layer 2 is enough.

All of them use this chapter's Python and 2-03's SQLite as they are. There is no new framework to learn from scratch.

flet-mcp hands over the API of the installed version

Flet has an MCP server, flet-mcp. Add it to the AI's MCP configuration and the AI can look up, on the spot, the documentation for *the version installed on your machine*.

This matters because a GUI framework is where the API moves most between versions. If the AI emits an older idiom that happens to sit in its training data, you start from code that does not run. With flet-mcp in place, the AI checks the current way of writing before it writes. The old idioms stop coming out.

The work is only this: add flet-mcp to the MCP configuration, and tell the AI to "check the Flet API through MCP before writing." That small step is also why Flet can be the default screen for this series.

How to check you are done

This chapter is done when these five hold.

1. JupyterLab opens in the browser, and import polars in a cell passes on Shift+Enter
2. Polars reads an .xlsx from your disk and a product-by-month cross tab prints as a table
3. An Altair figure draws in the same notebook and shows values when the mouse hovers
4. The monthly report can be built from exported data alone, and rebuilt any number of times without touching the core
5. A Flet app starts on your own screen, and the same code opens in a web browser

uv run jupyter lab
uv run python -c "import polars, altair, flet; print('ok')"

What the human holds

Values the human supplies

• The files and the period to aggregate, and the department or product breakdown
• The cut-off values a person decides (e.g. large accounts at 100,000 yen, mid at 50,000)
• Which data may be exported from the core, and how often
• The invoice form, the recipients, and the closing date
• Who the Flet app goes to, and what it runs on (tablet, PC, web)

Actions the AI states before performing

• Overwriting an .xlsx a person made
• Writing into the core system (in work that was supposed to be read-only)
• Actually sending invoices or notification mail
• Replacing or deleting the original file that holds the VBA

Versions checked, and when

• JupyterLab, uv, Miniforge, Polars, matplotlib, Altair — no version pinned
• Flet — no version pinned. Have flet-mcp confirm the API of the version installed
• The procedure was written on 2026-09-21 and reviewed on 2026-10-05
• If a version has moved, have the AI confirm the official procedure before proceeding

Summary

Logic, from inside Excel to the Python side.

• The skill of using — instead of memorizing syntax, put into words what you want processed. Error text becomes input for the next instruction
• Externalization — macros, VBA, charts, and pivots go out into Python. Excel stays as the human's I/O, handled as .xlsx
• JupyterLab + Polars — pivots and VLOOKUP become code, and next month is a re-run
• matplotlib / Altair — figures stay as code, go into Git, and regenerate every month as they are
• Splitting OLTP from OLAP — peel forms, dashboards, and invoices off the core. The month-end report run that freezes the sales system goes away
• CLI → Flet — write the logic, then put the screen on. The same Python runs on Mac, Windows, Linux, the web, and mobile, and flet-mcp hands over the API of the installed version

The Python picked up here gets used as-is in the chapters ahead. The next chapter puts a gate in front of those tools and apps: PocketBase gathers authentication into one place, so every app comes through the same entrance.

Related articles

• 2-03: Lay the Foundation — SQLite, PostgreSQL, pgvector, DuckDB, Polars
• 2-05: Stand Up the Gate — One Login with PocketBase
• 2-07: Take Documents Back — Prose in AsciiDoc, Working Tables in a Grid, Printed Pages from Templates
• 2-12: Build an API — Expose Core Logic with FastAPI
• 1-05: Customers Co-Develop with AI