Excel in the Browser - Bringing Your Models to the Web with Streamlit and OpenPyXL

For decades, Excel has been the tool of choice for financial modeling, scenario planning, and data analysis. But it’s traditionally been confined to the desktop—limiting collaboration, requiring manual input, and often causing version control nightmares. Now, with Python and Streamlit, you can bring those powerful Excel models to the browser—transforming them into live, shareable, and interactive web apps.

In this post, we’ll walk through how to deploy Excel-based financial models on the web using Streamlit as your front-end and OpenPyXL to handle Excel automation on the backend. Whether you're modeling investment returns, simulating cash flows, or building KPI dashboards, this approach makes your tools more accessible, scalable, and client-friendly.

Why Bring Excel to the Web?

Eliminate file sharing friction—no more emailing .xlsx files back and forth

Centralize control over calculations and assumptions

Enable real-time collaboration and scenario testing

Hide the complexity of Excel behind a clean UI

What You’ll Need:

Python 3.8 or higher

Streamlit (`pip install streamlit`)

OpenPyXL (`pip install openpyxl`)

Pandas for data handling (`pip install pandas`)

A pre-built Excel template containing your model logic

Step 1: Prepare Your Excel Template

Start with a clean Excel file that includes all necessary inputs, formulas, and output tables. This becomes the core engine for your app.

Structure your template with clear input cells (e.g., interest rates, cash flows, periods) and well-defined output zones (e.g., IRR, NPV, summary tables). OpenPyXL will read and write values directly into these cells, acting as your backend calculator.

Step 2: Build a Streamlit Front-End

Streamlit is a Python framework that makes it incredibly easy to create web apps for data science. In just a few lines of code, you can collect user inputs, trigger back-end calculations, and display results.

```python

import streamlit as st

import openpyxl

st.title("Investment Return Calculator")

initial_investment = st.number_input("Initial Investment", value=100000)

rate = st.slider("Annual Return Rate (%)", 1, 20, 8)

periods = st.slider("Years", 1, 30, 10)

```

Step 3: Automate Excel with OpenPyXL

Use OpenPyXL to programmatically update your Excel template with the inputs provided in the Streamlit UI, recalculate the workbook, and extract output values.

```python

from openpyxl import load_workbook

wb = load_workbook("model_template.xlsx")

sheet = wb.active

sheet["B2"] = initial_investment

sheet["B3"] = rate / 100

sheet["B4"] = periods

wb.save("updated_model.xlsx")

```

After updating, use formulas or cell values from the output section:

```python

output_value = sheet["D10"].value

st.metric("Final Value", f"${output_value:,.2f}")

```

Step 4: Host and Share the App

Once your Streamlit app is working locally, deploy it:

Use Streamlit Cloud for free hosting

Or self-host via Heroku, AWS, or DigitalOcean

Just `streamlit run app.py`, then share the public link with clients or teammates.

Advanced Features to Add:

Upload and download Excel files directly via Streamlit widgets

Include charts or graphs using `st.line_chart()` or `matplotlib`

Cache expensive calculations for performance boosts

Add authentication for secure access

Benefits of Web-Based Excel Models:

Remove dependency on local Excel installations

Protect IP by hiding spreadsheet formulas

Enable dynamic model updates without distributing new files

Provide a clean, user-friendly interface for decision-makers

Example Applications:

Real estate pro forma calculators

Private equity return waterfalls

SaaS churn and growth modeling

Treasury forecasting tools

Cautions and Considerations:

OpenPyXL does not execute VBA/macros—ensure all logic is formula-based

Complex workbooks may need optimization to avoid performance lags

Keep Excel templates well-documented and locked down to prevent corruption

By marrying the calculation power of Excel with the accessibility of the web, you unlock a new tier of usability for your models. Instead of emailing complex files or training users on Excel functions, you can offer a click-and-run experience accessible from any browser.

At CFS Inc., we help companies transform their Excel-based workflows into scalable, automated applications. Whether you're modernizing financial models or building custom analytics portals, our Streamlit + Excel solutions deliver clarity, control, and a competitive edge.

Excel was built for analysis. We build it for the future. Let CFS Inc. help you take your spreadsheets online.

Previous
Previous

Power BI Embedded with Python -Enhanced Excel Dashboards

Next
Next

Excel-Aware APIs - Creating Web Services that Understand and Serve Excel Needs