I think you nail the fundamentals here. I also dream of a access+excel kind of full app but think need to start with the base language (that I doing at http://tablam.org). You need to align the base language + the rest of tool to make easier to scale up whatever the customers create (at least, you can copy paste whatever them wrote and debug it using the same tools as professionals, instead of reverse engineer the kirks of excel...)
I have been in the industry for 20 years myself as a quant, mostly using Excel as a "glue" layer indeed, and I worked with the Python based "clone" you mentioned too.
For the last two years I have been working on a platform to fix exactly the pain points you are talking about.
Amazing how your post sounds almost like as if I wrote it myself before I started working on this project :)
Please, take a look at https://ellx.io I would be more than happy to discuss it in detail: dmitry + at + ellxoft.com
Exactly the example I was looking for! Mind if I re-implement all this on the Ellx platform (https://ellx.io)?
I've spent the past 10 years maintaining and improving a finance-centric lazily-evaluated dataflow language with integrated distributed NoSQL database for a Fortune 500 financial services company. My understanding is that most of our competitors use Excel where we use our language/database. Some of our competitors have hired away some of our developers to explicitly write more modern clones of our system. One competitor's clone uses Python with function decorators that inspect the function's AST and transform it into a reactive dataflow language, though I imagine it's very frustrating to write something that looks like Python with very non-Pythonic evaluation semantics. I think starting with something like Haskell or Prolog would be less jarring.
My wife does finance/accounting for another large multinational, and I see the appeal and power of both Excel and our system. I wish there were some easier way to migrate from spreadsheets to more scalable and maintainable systems. (And, I suppose, there's some utility in bidirectional migration.)