Excel as Code

&gt; People refuse to stop using Excel because it empowers them and they simply don&#x27;t want to be disempowered.<p>That is not always true in my experience. Many people use Excel because it&#x27;s one of the two programming tools allowed by the IT department, the other being a web browser. Even if you manage to install Python or something (good luck getting the package management working from behind your corporate proxy), your collegue will not have it, so it&#x27;s useless. And distributing executables is usually not tolerated either. So you use excel, and share Excel files.<p>I&#x27;ll add that another big problem I have with Excel is usually the lack of database support. Moving data around by copy&#x2F;pasting it in Excel with macros is a pain, and IT didn&#x27;t allow Microsoft Access either so I can&#x27;t comment on that. But I think it would have made my life easier.

I offer my master&#x27;s thesis &quot;Compiling and optimizing spreadsheets for FPGA and multicore execution&quot;<p><a href="https:&#x2F;&#x2F;dspace.mit.edu&#x2F;handle&#x2F;1721.1&#x2F;45983" rel="nofollow">https:&#x2F;&#x2F;dspace.mit.edu&#x2F;handle&#x2F;1721.1&#x2F;45983</a>

I&#x27;ve been tasked with migrating an excel model to a &quot;real language&quot; (usually by breaking it apart and re-implementing it via a combination of ETL and data warehouse jobs). I&#x27;ve never found a great way to run excel in a headless way, so in addition to not having version control for it, it&#x27;s hard to &quot;deploy&quot; it when it grows beyond a single person&#x27;s machine. I wish there was more of a gradient between Excel and &quot;real systems&quot;.

I get the spirit of the document, but disagree with the goal. I&#x27;m biased, I&#x27;ve kind of made my career writing web applications for people reliant on Excel. While I&#x27;ve come to respect it&#x27;s power – I had a colleague in architecture school design buildings using excel and I&#x27;ve seen some ridiculous formulas based on crazy pivot tables and conditionals.<p>I&#x27;ve seen more spreadsheets than I would care to admit, and what drives me crazy about each and everyone is that it is not readily apparent where the work is being done. I think you could say the same about a &quot;programming language&quot; except that the programming language is usually not also the product. When the interface is the code and the output, the lack of consistent implementation is something I find frustrating.<p>It&#x27;s a nice thought experiment, but in my mind I think the world would be a better place without excel.

Excel is kind of WYSIWYG programming. I use it for quick stuff frequently and I’m amazed at what it makes easier than e.g. numpy. There’s a whole class of error you don’t make because you see the whole intermediate state all together (there are also whole classes of error you <i>do</i> make that you wouldn’t make in normal programming).<p>I have been using it for character sheets in tabletop RPGs I’m playing lately, and it’s great. With a line of js, you can add an arbitrary button to google sheets, and then it turns into a quick, dirty UI that’s transparent (click on the cell and see that AC=10 plus dexterity modifier) and on-the-fly editable by everyone together.

&gt;What needs to change is the idea that they are not programmers, so they can join us in using modern software practices.<p>Most of them don&#x27;t want to use modern software practices, the want their formulas and their macros no matter the security risks. They don&#x27;t remove unnecessary code because they don&#x27;t want to read and learn what others had done before in the spreadsheet. Excel is easy and successful because you don&#x27;t need to follow any software practice in the first place and that&#x27;s also the reason why it&#x27;s a pain in the ass for all that have to maintain them and keep them secure.

A lot of comments are criticizing Excel users as if we are resistant to learning more about other programming languages. Resistant as in hard-headed or lazy.<p>One thing to remember is that the vast majority of Excel users aren&#x27;t fully in IT or tech. We have to deal with data but the roles aren&#x27;t primarily data roles.<p>- Customer Service Reps<p>- Admin Assistants<p>- Warehouse Managers<p>- Non-profit Fundraisers<p>- Sales Reps<p>- Realtors<p>- Inventory Managers<p>- Insurance Agents<p>I&#x27;ve taught at non-profit conferences and saw how people were torn. The fundraiser who uses Excel every day has to decide: do I spend 4 hours in an Excel session or 4 hours in a session on fundraising trends?<p>===<p>So many roles require some kind of data use, and Excel is immediately accessible, even if all it is is typing numbers into a cell, hand-coloring certain values and getting a sum.<p>Here&#x27;s the question: WHEN is a person best served to put in the time and effort required to learn Python, JavaScript or another formal programming language? WHEN should a Warehouse Manager be sent to a Python class? What would that situation look like?<p>Personally, I hate true programming--and I&#x27;ve done a lot of it. But true programming is a whole different mindset. I like the visual aspect of Excel. But when I open a code editor and there&#x27;s this wall of letters, numbers, indents, curly-brackets ... WOAAAHHHHHHH! No. HELL NO!<p>Even with WordPress and the templates that are supposedly drag-&amp;-drop, I still found myself writing CSS and HTML.<p>===<p>One other thing. Don&#x27;t forget looking the opposite way. Too many coders don&#x27;t know what Excel can do. I watched a presentation on 6 hours of JavaScript that someone wrote to accomplish a task. That same task would have taken less than 5 minutes in Excel.

A killer app would be a spreadsheet format that worked as well as source code as storage format for a spreadsheet application. Something that was designed for manual editing in two different ways, in text editors and in a &quot;cell editor&quot;. That would support all version control use cases that developers are familiar with and that have been best practice for decades.<p>Perhaps all that is needed is to port OpenOffice to the sc format (and extend it in the spirit it works now)

Excel IS code. It&#x27;s a dataflow language combined with a visual&#x2F;spatial language. It is hard to migrate or transfer to other languages because other language don&#x27;t have these features&#x2F;architecture.<p>The other side of this coin is that spreadsheets have NOT BEEN IMPROVED significantly since VisiCalc. Excel has some window dressing and intentional obfuscation by moving UI elements around to make it seem improved but it really isn&#x27;t at all.

Only for a lack of imagination would you fail to perfectly model your target problem domain in terms of tables &amp; columns... You would have a fucking monster of a time trying to describe to me a practical problem that I could not hypothetically wrangle &amp; demonstrate with Excel. Just think about it. You can model a <i>ray tracer</i> in Excel if you have the patience for it.<p>The magic of Excel is that it runs everywhere and is very intuitive to work with. I honestly can&#x27;t recall any users who were simply unable to function in a basic read-only way with Excel. Iterating complex problem domains in excel workbooks is a low-friction way to collaborate with your business stakeholders.<p>Once you get it nice in Excel, the next steps are compelling. Using an obvious 1:1 mapping between Excel worksheets and SQL tables, you trivially move all data items into a realm to be easily queried using a declarative, domain-specific language. You can also sprinkle in views and user-defined functions for maximum happiness on the business-side of the house.<p>The richer and better-normalized the relational model, the better your SQL interface will be. If you ignore the performance equation for just a few seconds, you might see the blinding luminosity of cleanliness that emerges from normal forms beyond the 3rd one. We are going to investigate a variation on 6NF for the next major version of our product.<p>I will conclude my rant by saying that there is no logical determination&#x2F;interpolation&#x2F;projection of facts which is unachievable in an ideal SQL representation. It is <i>very</i> easy to teach SQL to non-wizards by way of the mighty example. Excel is the most important starting point on this journey, because it defines the common language and relations that you and the business will use to refer to all of the things.

&gt; Unfortunately, none of this applies to Excel because Excel doesn&#x27;t work well with revision control. Why? Because Excel is not a source file. It is a database coupled with code. [...] The path to enlightement is a more sophisticated revision control systems - ones that can understand Excel.<p>This is where the author lost me. The &quot;path to enlightenment&quot; is not to build new VCS software. The solution is simply to stop coupling your database with your code. Embrace the Unix philosophy and stop perpetuating monolithic software.<p>Excel is a spreadsheet editor. It was never designed to be a database. It can act as a quick-and-dirty database with minimal setup and training required. Sometimes that&#x27;s all you need and Excel is a fine tool for those situations. But it has limitations.<p>Stop trying to force Excel as the solution to all your problems and don&#x27;t be afraid to learn a new tool once in a while.

I used to work with someone who refused to learn another programming language besides VBA in Excel. He slowed everyone down and it got to the point where he had implemented a JSON parser and generator in Excel 97. Badly. It&#x27;s one of the worst experiences of my professional life. I dislike VBA because it convinces those who learn it that it is a programming language and that Excel is a programming environment just like Python or another popular programming language with their standard libraries. That&#x27;s just not the case, but its very hard to convince business people who have spent their whole professional life using MS Office that there are better choices for building their business apps than MS Office and VBA. Just let Excel and VBA die.

Years ago I wrote some VBA that exports all the VBA in an Excel file. I ran this script manually from time to time so I could add my code to version control. Excel should make it easier to separate the code from the data. For the former you probably want the entire commit history, for the latter you usually only want the current state.

Excel makes programming easy because all of the intermediate values are visible left to right and the loop iterations are visible top to bottom. This makes it easy to iterate towards a solution by visual inspection, but also creates spreadsheets as buggy as you’d expect if you only tested by visual inspection.

Lots of SaaS services, like Google sheets, go the quick and dirty route: one central database and the UI displays a view which you all work on together. That&#x27;s not collaboration imho - and no dev shop would accept that as a reasonable way to work (lets work on the code in a google doc!).

It&#x27;s sad that after nearly 50 years, the way we write programs has not changed. We still use keyboards and write code one line at a time. Sure, there are auto-complete extensions and helpers, but the basic idea is still the same: write your instructions for the computer to perform them.<p>When it comes to making programming approachable for the masses, it&#x27;s actually kinda funny to think that Excel (and spreadsheets in general) have been way ahead of traditional programming software.<p>I hoped that new tech (AR&#x2F;VR&#x2F;etc) would help shift the focus from &quot;typing&quot; programs to &quot;drawing&quot; programs. But efforts to visualize programming only remain at the conceptual level and never gained traction.<p>It&#x27;s hard to imagine 100 years from now we will still be typing code.

At <a href="https:&#x2F;&#x2F;www.xltrail.com" rel="nofollow">https:&#x2F;&#x2F;www.xltrail.com</a>, we wrote an open-source Git extension that allows you to diff the VBA part of your Excel workbooks. The extension also integrates with SourceTree, Atlassian&#x27;s free Git client. You can see some screenshots on my blog post: <a href="https:&#x2F;&#x2F;dev.to&#x2F;fzumstein&#x2F;how-to-diff-excel-vba-code-in-sourcetree-git-client-36k" rel="nofollow">https:&#x2F;&#x2F;dev.to&#x2F;fzumstein&#x2F;how-to-diff-excel-vba-code-in-sourc...</a>

So this is an ad for some new merge tool I suppose.<p>Is there a solid open source tool for merging Excel files? Or CSVs or SQLite files for that matter?<p>I think this is probably best seen as a shortcoming of our current general VCS. At the moment we&#x27;re stuck with newlines as the main means of merge semantics. That really restricts what we can put in VCS. Even with custom merge tools, its quite cumbersome as git does not allow this to be preconfigured.

&gt; Git was not built for this - ...<p>But it does have a sort of plugin system to support other formats, right? Does an Excel format lend itself to being supported in this way?

&gt; Because Excel is not a source file.<p>Well, it is a zip-archive with XML files, so it&#x27;s close.

Surprised nobody has mentioned this. There is a company called Boardwalktech with a tool called &quot;Excel Cloud&quot; which adds a native extension into Excel which includes a change log and (i think) realtime collaboration, among other things.<p>They call their underlying tool a &quot;digital ledger&quot; which sounds very blockchain-y, but it&#x27;s not a distributed public ledger so there&#x27;s no crypto here, just a centralized, Boardwalktech controlled ledger.<p><a href="https:&#x2F;&#x2F;www.boardwalktech.com&#x2F;products&#x2F;boardwalk-excel-cloud" rel="nofollow">https:&#x2F;&#x2F;www.boardwalktech.com&#x2F;products&#x2F;boardwalk-excel-cloud</a><p>They&#x27;re already integrated with some very big companies like Accenture, Ernst and Young, Coca-Cola, Mars, Facebook, etc etc.<p>Personally, I can&#x27;t imagine company leaders really investing tens to hundreds of thousands of dollars leaving their processes in Excel and not instead buying a real system, but I&#x27;m not running all of the companies mentioned above.

There is dolthub.com which is Git for data. But there is only an SQL interface. No way to source control the style of the data in Excel.<p>Last time I looked into Dolt there were no commit hooks either. That would let you add linting or other data validation.

I think this is spot on. I agree that Excel users want to stick with Excel, but they do run into major issues that are solved by code. Namely: their data size is too large, Excel is too slow, and they struggle to get repeatability from their work.<p>I am building Mito[1], a spreadsheet interface for Python. Every edit you make in the spreadsheet generates the equivalent Python. It is a bridge between the workflows of Excel users and Python users, and allows Excel users to reap Python&#x27;s benefits without needing to know how to code.<p>[1] <a href="https:&#x2F;&#x2F;docs.trymito.io&#x2F;" rel="nofollow">https:&#x2F;&#x2F;docs.trymito.io&#x2F;</a>

Excel as code is a main spreading vector for bad practices like copy &amp; paste, monolithic procedural monsters, bad databases with duplicate entries and so forth.<p>The reason why management cant perceive code-quality, is because there main tool, does not allow for good code-quality. In fact it does not even allow for abstractions..<p>If you ever wondered, why management does not blink and recoil one description of coding horrors..

I truly believe that Excel is the most abused software of all time. It has been mangled, malformed, smashed, and manipulated to do stuff that I don&#x27;t believe the creators ever intended. Adding a scripting capability to it has unlocked the spirit of challenge in all SME&#x27;s of finance related fields to make Excel the sole software they will use for all problems.

It mind-boggles me that microsoft are not investing in VBA more, its userbase is massive. Sure its old and has its problems but I&#x27;m sure continuing to develop it alongside more modern solutions would help them rather than hinder their efforts. Make it more similar to other things out there and eventually people will change over.

I have recently discovered macros on google sheets. With an option of scheduling them in the cloud and simplicity of writing them, even with my limited coding skills , it allows me to put together pretty complex dashboards.

Yep Excel is great, actually working on a Minix like Kernel in it.

Excel is a language alright, a hideous and perverse one!

No-one is going to pay to version control Excel.

Nice perspective. Thanks.

&quot;virtually nobody treats Excel seriously like a programming language.&quot;<p>Because Excel was not Turing complete until recently.