Half the finance world runs on Excel. So why not make Excel the front-end? Download the workbook below, click Refresh, and watch it pull live data from a MySQL database on our server. No SharePoint, no Power Query gymnastics — just VBA, HTTP, and JSON. The way we've been doing it for years.
A live-connected Excel workbook (~50KB). Requires Excel on Windows.
The workbook opens with a Films sheet and a Dashboard sheet. Click Refresh and it makes an HTTPS call to crowdata.ca/excel-demo/data.php, gets back JSON, and populates the sheet. The dashboard auto-recalculates: films per category, average rental rate, top 10 titles by rental count.
Change the category dropdown, hit Refresh, and the whole thing repopulates with just that slice. Filter by rating the same way. It's about a hundred lines of VBA, plus the workbook itself doing what workbooks do.
The endpoint powering the workbook is public — you can hit these URLs in a browser and see the JSON come back. This is the same data the .xlsm sees.
The Excel side is a modest chunk of VBA. This is the whole read routine, minus the pivot-refresh boilerplate:
Public Sub RefreshFilms()
Dim http As Object, url As String, json As String
Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
url = "https://crowdata.ca/excel-demo/data.php"
If Range("CategoryFilter").Value <> "" Then
url = url & "?category=" & UrlEncode(Range("CategoryFilter").Value)
End If
http.Open "GET", url, False
http.Send
json = http.responseText
' Parse JSON, populate the Films sheet, refresh pivots
PopulateFilmsSheet ParseJson(json)
ActiveWorkbook.RefreshAll
End SubThat's it. The server-side PHP is a single file — it queries MySQL, casts numbers to actual numbers so Excel treats them right, and hands back JSON. The DB user is read-only and can only SELECT from the sample database, so the endpoint is safe to leave open.
The data behind it is Sakila — the classic MySQL sample database (a fake DVD rental store: 1,000 films, 200 actors, categories, rentals). Public sample data, so no privacy concerns. In a real deployment, you'd swap this for your inventory table, your sales ledger, your project pipeline — anything queryable.
If you've got a database and a team that lives in Excel, we've done this many times before. It scales to real business use — role-based views, write-back (edit a cell, POST it home), authenticated endpoints, offline caching. This demo is deliberately simple; the pattern is not.