Oh it's real programming, as far as I'm concerned. My problem with Excel is that it has the worst tooling out of any application platform I've ever encountered, and it's subject to the hideous whims of Microsoft on the quality front.
When one imports a CSV into Excel, Excel has a nasty tendency to actually change the data based on formatting rules. When you open up a CSV file in Excel, it silently corrupts the information. You need to back up your file before doing any work on it.
Excel refuses to do proper Unicode, or at least I haven't found the right way to reliably get a Unicode CSV out of it that won't break in third-party tools.
Excel does not do versioning natively, and you have to rely on a buggy mishmash of Microsoft-based solutions to do file-based version management. Excel files tend to get emailed around everywhere, and so there's never a single source of data. It has the worst properties of a decentralized architecture without any of the benefits.
Data belongs in a relational database. You can use a spreadsheet to manipulate it, but the manipulated data needs to go back into the database. The spreadsheets that do this are programs and they need to be managed as code, going into version control systems.
Countless, countless businesses use Excel and they all suffer from what you correctly put as the "worst tooling out of any application platform". They're suffering every day and yet where is the desire to alleviate their pain? It doesn't exist. I would expect to see hundreds of competing projects. I see just a few and they're all crippled in some form.
I think it's a failure of venture capital. What's funded is often what looks good rather than what is needed. That's my guess but I don't know.
An article someone linked to me a few days ago made a distinction that I think is critical to understand. People don't buy what they need, they only buy what they want. If you want to sell them something they need, you have to make it look like something they want.
Nobody wants Excel programming workflow management. But they definitely need it.
I can tell you why they don't want it. Most people do not think in terms of infrastructure. Their job is a series of one-off tasks and they see little commonality between these tasks. Constantly fighting fires, never thinking to design a fire-proof building or procedures for preventing fires.
You ask them why and the answer's always the same. No time. They're too busy. It's wrong, but they don't have the skill set they need to understand why it's wrong.
It's sort of a practical manifestation of the Dunning-Kruger effect. People underestimate their knowledge of processes and their ability to change them, so they don't.
There is something else going on, too, I think. One of the core strengths of Excel is that it frees a lot of people from dependency on "real" programmers. Any toolset that looks to replace excel (not an easy task) absolutely has to have this feature, or it will be a non-started in many of the core excel niches.
However, very few programmers can successfully go into a project that is fundamentally a programming tool, and maintain the mind set of "how I make sure that my own way of looking at this remains completely unnecessary?"
If you find yourself having a bunch of conversations with finance types and analysts that all look like: "but if you just approach it this way, it's much more powerful because X" you've probably already failed.
There are many startups which exist to replace specific excel-oriented workflows. It's much easier to build and sell a full-featured, vertically-targeted app than to build and sell a better Excel.
I've seen this for CRM, accounting, liquidity, ad-purchasing, security vulnerability data. People were thrilled to be rid of Excel and use something that took development out of their hands. The software ends up being more useful, too, when a dedicated team is building a tested, version-controlled application for hundreds of customers.
It might be that, but it's also a failure on the part of the sufferers to imagine that anything better is possible. They've paid the M$ tax for decades, but spending $50/seat for 3rd-party tooling around excel is a non-starter.
I always liked the fact that Trello consider themselves to be competing with Excel, and in one of its main usecases, which is storing lists and project tracking, it does a great job of that. Of course, they are NOT taking on the excel as application platform usecase. but it does suggest that the replacement for that probably doesn't have to be a spreadsheet, either.
Nobody ever got fired for buying Microsoft Office. I honestly think that's as far as it goes for most people.
I've felt the exact same pain as you - having the non-technical people work in Excel and moving it back and forth to a database is a nightmare. I've started creating Flyway migrations for each import/export sequence just so we can keep track of what's live in our DB. If you were working in CSV you could even script an import as part of a build process or something.
In some cases better products even exist. For example LaTeX is a fantastic tool - you can create complex layouts (not just for documents, but also Illustrator-type tasks), automagically manage cross-document references/citations and generate tables-of-content and bibliographies, lay out images and tables and formulas, modularize documents (eg by chapter) and best of all the documents are a plain-text "source" that you can easily check into revision control or merge between multiple simultaneous users. Zero adoption outside the academic world and certain publishing niches (eg math textbooks).
Side note: one unfilled niche I've noticed is that there isn't a good word- or character-level diff tool that's appropriate for LaTeX. Ideally you would want to be able to type natural paragraphs and have the diff pick out changes by word instead of line (but preferably also identify command tokens more greedily up to a line). You can break a paragraph across several lines, but if you're making heavy revisions it just ends up being a solid block of line changes.
One more question: is there a "worksheet" or "notebook" concept in something like Mathematica that might be a better model for the type of work you're considering? I know Mathematica has a pretty complete feature set, it very well might have database hookups and an appropriate workflow for that type of thing.
I think I was accidentally unclear: it's not pain I'm experiencing but pain that I've noticed. My project, Kayia.org, is working to alleviate it but I'm surprised at the lack of enthusiasm for ways to mitigate something so clearly painful.
I've developed a tool (http://diff.so) that might be a good fit for your LaTeX diffing problem. It's pretty buggy and slow right now, but I'd be interested in your thoughts.
Agreed that Excel is real programming and that data belongs in a database - so why not give it a real programming language with a sufficiently expressive query language?
This was my reasoning behind a spreadsheet prototype backed by kdb+ and the q language (which is a superset of SQL): https://github.com/srpeck/kxl
There is value in this simple combination, but tons of work required to gain feature parity and market share from Excel. I think the latter is why research in this space is lacking.
> Excel is real programming and that data belongs in a database - so why not give it a real programming language with a sufficiently expressive query language?
Because now you've removed the primary thing that makes Excel so popular: its approachability. You don't have to learn anything special before you can start getting value out of Excel. You don't even have to understand how to write a simple formula! It's useful even as a simple, dumb rows-and-columns data store.
This is how Excel makes so many people into accidental programmers: they start using it in a naïve, low-knowledge way, and then eventually wonder if there's some easier way to do what they're doing. ("I'm tired of adding all these figures up by hand. Is there some way Excel can do that for me?") So they dip a toe in, learn a little bit, and become even more productive. Then they repeat this cycle over and over, and years later they have become Excel gurus without ever actually setting out to.
(Of course, the downside to all that is that they've now built a ton of stuff in Excel in hacky, incremental fashion, and are quite possibly using Excel to do things a real database would be much better at. But they don't know that -- and besides, if the only tool on offer was a real database they probably would never have gotten around to building anything at all.)
Anything with a "real programming language" is going to lose these people, because learning that language is a big chunk up-front work they have to do before ever seeing any benefit. Anything that wants to compete with Excel needs first and foremost to offer the same kind of immediate productivity benefit and gentle, incremental learning curve to reach the Excel audience.
Completely agree on the gentle introduction and discoverability - you never tell users they are using a 'real' language and database. However, the Excel function language and API are relatively terrible - I think users succeed in spite of them, not because they are approachable.
The spreadsheet format coupled datatypes made me sit for a few moment. I was trying to make sense of the rows in VBA when I realized that changing the appearance would change the kind.
btw, the last company I worked in used Excel as a disconnected editor', extraction, load, edit, save, pushback was ugly. You'd need a careful and solid bridge between the two, and hopefully a semantically adapted backend.
When one imports a CSV into Excel, Excel has a nasty tendency to actually change the data based on formatting rules. When you open up a CSV file in Excel, it silently corrupts the information. You need to back up your file before doing any work on it.
Excel refuses to do proper Unicode, or at least I haven't found the right way to reliably get a Unicode CSV out of it that won't break in third-party tools.
Excel does not do versioning natively, and you have to rely on a buggy mishmash of Microsoft-based solutions to do file-based version management. Excel files tend to get emailed around everywhere, and so there's never a single source of data. It has the worst properties of a decentralized architecture without any of the benefits.
Data belongs in a relational database. You can use a spreadsheet to manipulate it, but the manipulated data needs to go back into the database. The spreadsheets that do this are programs and they need to be managed as code, going into version control systems.