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.