Excel literally has the power to perform better than multimillion dollar consultant implementations on all kinds of things (having been on those projects and unable to supplant Excel successfully).
But is hard to audit. Web apps are committed in Git and have code reviews and client testing. If Excel made it possible to commit the formulas into Git, it would have taken over a million of accounting apps and SAP for 50 additional years.
But the lack of reliability of “a spreadsheet made by John in Accounting” sorely limits the success it can have.
We built xltrail[0] (cloud and self-hosted SaaS) that lets you see the diffs for sheets, VBA, and a couple other things. You put in your git repo and it just works. You can also have a manual versioning option where you upload new versions of the same file and you get the same result.
We also have the open source git extension Git XL[1] that lets you see VBA diffs locally.
Isn't this why people always save excel files a certain points of time? So you can go back to it and see it's state at certain "commit" points?
For me, it often looks like this.
2023 Budget v3 - 2022.08.01.xlsb
2023 Budget v3 - 2022.08.05.xlsb
2023 Budget v3 - 2022.08.08.xlsb
2023 Budget v3 - 2022.08.12.xlsb
The dates indicate minor updates/changes to the data (eg. the database is different but the logic is the same). Where as the version number is a change of business logic &/ formulas &/ file architecture (so v3 files are usually compatible with v3 files, but v4 files may break everything when comparing back to v3).
Granted, it doesn't help any with collaboration. My team still passes around the "current" file and deal with read-only locking issues (we find shared mode to be highly broken and conducive to file corruption). But, it does help with auditing because we can always go back and find why a number was what it was on any given day. So in that sense, Excel documents are as auditable as open source software. You have to want to read the code but it's all there for the reading.
Same. Also the problems I've encountered when one file tries to autosave, triggering a calculate before save, when I am actually working in something large that is calculation sensitive (manual calculation mode is on), and my whole application freezes/locks because it calculates when I'm not expecting it. Always happens 5 minutes before a deadline too. So yeah, disable that.
It's also a Workbook level setting so, you might have it turned off, but someone sends you a file with it enabled. For that reason, I have a macro that disables it at Open for all files (same for calculate before save, I really dislike that setting too).
The IT department is limited by Turing's famous Halting Problem:
Given a complete set of requirements and a working prototype, will the IT department ever finish writing software that meets those requirements?
Okay, that was sarcastic, but illustrates a real problem, which is that doing it in Excel is making something, whereas getting IT involved is managing something -- a category error. The problems of managing software development are unchanged, half a century after The Mythical Man Month.
Indeed, communicating requirements is one of the hardest parts of managing anything technical. Especially if you're working with people who are a couple steps removed from the background domain knowledge.
I'm in energy trading and wonder what the tech setups are in other companies. Can you tell me something about yours?
We currently migrate everything to the cloud, which breaks many Excels that so far got their inputs from on-prem services or the file system.
What could previously be deployed without second thought now requires extensive, decentralized cloud knowledge in each team. Power is taken from the lay business user. What took seconds in VBA now requires a feature request to a dev team.. it's insane.
Yep. We have all of our backtesting and analysis in the cloud. Python notebooks for analysis. Java for backtesting. C++ for performance critical or for where Java API is not useful.
I'm in HFT so we have software engineers available to our portfolio managers. Everything is in code.
Live strategies are deployed via a script that ultimately ssh to a machine, plops a set of artifacts in from storage, and then runs. But everything needs to restart together.
Looks at the 100-line SQL query that joins across 10 tables with 7 different WITH clauses and several random COALESCE statements sprinkled in to make the query work
You can use git hooks or CI tools to unzip xlsx files when they’re committed and run an autoformatter on the resulting xml files. The xml diffs aren’t the most readable, but they’re usable.
Sounds great until you have multi-million dollar programs running off single point of failure spreadsheets with little or no backup or overwrite protection.
