13 ms·

Excel as Code

166 points by LukeEF 5y ago

kyberias 5y ago
> Because Excel is not a source file. Well, it is a zip-archive with XML files, so it's close.
- cxr 5y ago
  I recommended exploring this approach here <https://news.ycombinator.com/item?id=27998733 https://news.ycombinator.com/item?id=27998733>: > Hot tip for handling office file formats or anything that uses a ZIP container: just unzip them and commit _that_ to the repo. Even modern (zipped XML-based) office file formats do make some limited use of binary blobs. You can either keep these intact, or write a small objdump-like tool that serializes them to text†. For portability, it might be best to write the serializer/deserializer in JS dumped into a thin HTML wrapper, so you pretty much anyone can double click to "run" it. (My experiments on roundtrippability with including that file in the ZIP container yielded poor results.) † I've used this strategy for Oberon .rsc binaries. Due to Wirth's affinity for single-pass compilers, the Oberon toolchain doesn't involve a discrete assembler or AOT linker tool, so there is no assembly format or linker scripts. However, Wirth's distribution of the Oberon system does have an ORTool utility <https://people.inf.ethz.ch/wirth/ProjectOberon/Sources/ORTool.Mod.txt https://people.inf.ethz.ch/wirth/ProjectOberon/Sources/ORToo...> (in the vein of objdump/readelf/nm) that will dump a textual description of the binary you give it. I realized that with some slight tweaks, you can use the output of ORTool.DecObj as a de facto "assembly" format—just write a tool capable of parsing it and then write out the corresponding binary.
  - inshadows 5y ago
    >> Hot tip for handling office file formats or anything that uses a ZIP container: just unzip them and commit _that_ to the repo. What is the point if that? I think neither binary nor XML output would be meaningful in the diff output.
- WorldMaker 5y ago
  I built a tool to explore version control of files like that by decompressing their contents and version controlling those. It was an interesting experiment.
- banana_giraffe 5y ago
  Notably: The VBA stuff is stored as a binary OLE2 blob thing inside of the xlsm file. (Or at least it is in the few spreadsheets I checked, no clue if there's some way to change that behavior)
  - tyingq 5y ago
    The VBA blob is documented: https://interoperability.blob.core.windows.net/files/MS-OVBA/%5bMS-OVBA%5d.pdf https://interoperability.blob.core.windows.net/files/MS-OVBA... 111 pages, and it looks non-trivial to implement something to tear it apart. But, I give MS some credit for documenting it.
jagged-chisel 5y ago
> Git was not built for this - ... 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?
- wcerfgba 5y ago
  You can still use 'straight Git', maybe with some PR management system like GitHub/GitLab/... . The difference is you can't rely on the diff to be useful, instead you'd need to provide a good commit message summarising the changes (which you should do anyway!) and then reviewers will need to check out the relevant version to poke it directly in Excel. But I agree that being able to manage an XLS(X) as plain text and having a proper diff would be incredibly useful. :)
  - da_chicken 5y ago
    I highly doubt you'd ever get diff for XLS in a general or universal case. That format is so old and crusty that it's only really defined by what Excel will do with it. XLSX, on the other hand, at least has to follow XML conventions and basic ZIP file structure, even if the open specification for the XML is really now a strict subset of what the current version of Excel will accept.
- marklit 5y ago
  Git supports extension-specific overrides which enables things like textual comparison of Office files. https://tech.marksblogg.com/git-track-changes-in-media-office-documents.html https://tech.marksblogg.com/git-track-changes-in-media-offic...
- WorldMaker 5y ago
  I explored storing file types like XLSX as the deconstruction of their zip file into individual XML/etc files. In my cases my focus was DOCX rather than XLSX, and I originally targeted a different VCS than git so I built it as precommit/postmerge hooks rather than git's diff hooks/attributes plugins. I got some interesting results with my tool and it wasn't a bad experience. Just not one I could suggest to novice users (fixing XML in a merge conflict is not entirely fun and very different from say Word's own review tools designed for higher level merge fixing).
amirhirsch 5y ago
I offer my master's thesis "Compiling and optimizing spreadsheets for FPGA and multicore execution" https://dspace.mit.edu/handle/1721.1/45983 https://dspace.mit.edu/handle/1721.1/45983
- breck 5y ago
  I'm really enjoying reading this. Ahead of its time. I really like your RISC CPU in a spreadsheet in Table 1-4. Have you seen others do this since you wrote it?
  - amirhirsch 5y ago
    Thanks. It is still crazy to me that this was 15 years ago since I still have nightmares about completing it. I used the approach to design a PDP-11 Floating Point Unit commercially, but haven't really seen more Spreadheet-to-FPGA work or RISC emulation -- it is clearly my thing to do. Microsoft just added LAMBDA to Excel but didn't copy my approach of capturing a table as the formula--Like most people if I start with a big data table to analyze, I start by spreading my formulas across a row with short intermediate results, and then test it on a few rows before applying it to the whole table. My lambda would let you select your input cells and define your output cells and capture the dataflow graph between them, with any external references as globals and then spare you the results in intermediate cells. I spent some time trying to sell this to Wall Street folks in 2007-08. I remember presenting on this at a supercmputing conference on Wall Street exactly today 13 years ago, and the Lehman guy on the panel didn't show up because they shut down that day.
    - mcbishop 5y ago
      Your step-by-step approach would be vastly more user friendly than the current LAMBDA implementation (where the user needs to put the entire nested function in the Name Manager). Simon Peyton Jones advocated for a similar step-by-step approach: https://www.microsoft.com/en-us/research/uploads/prod/2018/11/elastic-sdfs-jfp2020.pdf https://www.microsoft.com/en-us/research/uploads/prod/2018/1.... At least, step-by-step can be facilitated with a programming language (Visual Basic, C++, Python, JavaScript). ...That could be a sweet Excel add-in.
spoonjim 5y ago
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.
- WorldMaker 5y ago
  Sounds like you've never seen advanced Accounting spreadsheets because Excel definitely does not have left-to-right/top-to-bottom or even intermediate values restrictions. There are some amazing Gordian knots people have programmed in Excel. You haven't really seen the horrors of programming in Excel until you've needed to use the "Formula Auditing" group of the Formulas tab in the Excel ribbon. Admittedly "Trace Dependents" and "Trace Precedents" are still rather more visual tools than their source code equivalents, but they are their own sort of fun.
  - azalemeth 5y ago
    I share your pain and have seen people doing everything from numerical integration to curve fitting in Excel, all of it terribly. I worry what new special versions of recursive hell the new lambda functions will unleash upon us.
- Someone 5y ago
  That’s true for only a subset of spreadsheets. There’s no requirement for formulas to work left to right and top to bottom. Also, the moment you write = (A1 + A2)/2, not all intermediate values are visible anymore (although Excel has support for temporarily making them visible (https://support.microsoft.com/en-us/office/evaluate-a-nested-formula-one-step-at-a-time-59a201ae-d1dc-4b15-8586-a70aa409b8a7 https://support.microsoft.com/en-us/office/evaluate-a-nested...)) Also, in my experience, it’s fairly normal to have hidden rows or columns (https://support.microsoft.com/en-us/office/hide-or-show-rows-or-columns-659c2cad-802e-44ee-a614-dde8443579f8 https://support.microsoft.com/en-us/office/hide-or-show-rows...) or hide entire sheets (https://support.microsoft.com/en-us/office/hide-or-unhide-worksheets-69f2701a-21f5-4186-87d7-341a8cf53344 https://support.microsoft.com/en-us/office/hide-or-unhide-wo...) And of course, the ultimate “not all intermediate values are visible” is the use of macro functions or iterations (https://support.microsoft.com/en-us/office/change-formula-recalculation-iteration-or-precision-in-excel-73fc7dac-91cf-4d36-86e8-67124f6bcce4 https://support.microsoft.com/en-us/office/change-formula-re...)
haney 5y ago
I've been tasked with migrating an excel model to a "real language" (usually by breaking it apart and re-implementing it via a combination of ETL and data warehouse jobs). I've never found a great way to run excel in a headless way, so in addition to not having version control for it, it's hard to "deploy" it when it grows beyond a single person's machine. I wish there was more of a gradient between Excel and "real systems".
- hugi 5y ago
  A few years back, I was tasked with a similar thing. A government ministry was creating a complex calculator for a (very anticipated) public project that was supposed to go on their website. We started out using pure JS but the mathematician working on the project kept giving us new Excel documents with extremely heavy changes to the algorithms. In the end I gave him a location where he could upload the document and told him to just make sure inputs and outputs were always in the same predefined cells. Then we used Java and Apache POI to load the Excel document and run the actual calculations on the website. Best decision ever.
  - antris 5y ago
    >In the end I gave him a location where he could upload the document and told him to just make sure inputs and outputs were always in the same predefined cells. Then we used Java and Apache POI to load the Excel document and run the actual calculations on the website. Best decision ever. This is the kind of simple and effective solution that programmers who think they know everything would scoff at. Love it.
    - tehbeard 5y ago
      It's simple and effective until a part of the rube goldberg machine breaks...
      - deleted 5y ago
        [deleted]
    - 5y ago
LukeEF 5y ago
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'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!).
croes 5y ago
"virtually nobody treats Excel seriously like a programming language." Because Excel was not Turing complete until recently.
- WorldMaker 5y ago
  VBA was not recent. Also, you'd be amazed by Turing Complete things like what someone determined can do with just VLOOKUP(). That's even before you get into truly abstract models of things proven Turing Complete such as Rule 110 of Cellular Automata and how easy/hard you can implement them in Excel without VBA Macros or "advanced functions".
- thefifthsetpin 5y ago
  Can't you just implement a turing machine in Excel by using the cells in a row as your tape? * Store the initial internal state in A1. * Store the initial head position B1. * Store the initial state as boolean values in the rest of row 1. * Write simple lookup formulas in row 2 to compute the next state from the previous row. * Fill down. Look for the halting state in column A and your output will be written in that row. What am I missing?
  - felienne 5y ago
    Yeah you can, I made one here a long time ago: https://www.felienne.com/archives/2974 https://www.felienne.com/archives/2974 :)
behnamoh 5y ago
It'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. When it comes to making programming approachable for the masses, it's actually kinda funny to think that Excel (and spreadsheets in general) have been way ahead of traditional programming software. I hoped that new tech (AR/VR/etc) would help shift the focus from "typing" programs to "drawing" programs. But efforts to visualize programming only remain at the conceptual level and never gained traction. It's hard to imagine 100 years from now we will still be typing code.
- piyh 5y ago
  Typing speed is not my bottleneck for generating code, it's renaming variables and rewriting it 5 times until it's no longer a mess. A Vulcan mind meld would be nice but lacks precision.
- surfingdino 5y ago
  Musicians have been happy with their simple keyboards for hundreds of years. Why wouldn't software developers be using theirs in a hundred years?
- analog31 5y ago
  Creating complex things using drawing tools is physically laborious, and suffers from readability problems when things get too complicated to fit on one screen. I've seen this with mechanical and electrical CAD.
- Existenceblinks 5y ago
  As long as human still use a list of characters to represent things. Typing textual code is inevitable, even if you have a fancy editor to edit its semantic, there will always text here and there in whatever kind of language.
ianhorn 5y ago
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 do make that you wouldn’t make in normal programming). 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.
john_alan 5y ago
Yep Excel is great, actually working on a Minix like Kernel in it.
ivyirwin 5y ago
I get the spirit of the document, but disagree with the goal. I'm biased, I've kind of made my career writing web applications for people reliant on Excel. While I've come to respect it's power – I had a colleague in architecture school design buildings using excel and I've seen some ridiculous formulas based on crazy pivot tables and conditionals. I'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 "programming language" 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. It's a nice thought experiment, but in my mind I think the world would be a better place without excel.
- qsort 5y ago
  > When the interface is the code and the output, the lack of consistent implementation is something I find frustrating This is the reason why spreadsheets are popular in the first place, though. I won't ever defend them - I'm on a project right now that's been working on Excel for years, I know the pain! - but this is something that's worth thinking about. See also Jupyter Notebooks, yet another invention from the deep pits of hell. The popularity of the interactive paradigm is undeniable. Would the world be better if everyone started using something sane instead? Definitely so. But the world would also be better if every day was Christmas and that's not going to happen either. So while I share most of your concerns, I'm mostly sympathetic with the OP.
- swypych 5y ago
  "It's a nice thought experiment, but in my mind I think the world would be a better place without excel. " I agree with most everything you said, however, proliferation of programming and automation is a net win in my books, no matter the medium, and good spreadsheet software does this incredibly well. It makes programming in its very basic form accessible to a wide amount of users with a relative gradual and easy to grasp learning curve. Sure you can always improve on it, but I think the world would most definitely not be better off without it. I do agree that the work is hidden, they can be a nightmare to audit, and I think it would scare a lot of people on this board the amount of business critical functions that are completed by excel and other spreadsheets. However, I like to think this a short term problem, and to the authors point, the industry and the sw needs and will improve, and we should all be trying to eventually close the gap.
croes 5y ago
>What needs to change is the idea that they are not programmers, so they can join us in using modern software practices. Most of them don't want to use modern software practices, the want their formulas and their macros no matter the security risks. They don't remove unnecessary code because they don't want to read and learn what others had done before in the spreadsheet. Excel is easy and successful because you don't need to follow any software practice in the first place and that's also the reason why it's a pain in the ass for all that have to maintain them and keep them secure.
- Jtsummers 5y ago
  You keep saying what they don't want to, but remember that most users of Excel are not programmers in the traditional sense. I'd wager that most of them aren't even aware of the things you say they don't want. It's not that you don't need to follow modern software practices. It's that they don't know about them to follow them or not. Further, Excel is almost pure thought stuff. The distance between the user's idea and their implementation is almost as small as we can get without investing in a lot of educational outreach. And then they end up going crazy with macros because they don't know there are other or better ways to do it. Also, "modern" Excel (not sure which version, maybe 2007 or 2010?) has largely obviated the need for macros with the addition of tables and functions for interacting with them. It turns Excel into a kind of relational database permitting something close to functional relational programming as described in "Out of the Tar Pit" [0]. [0] http://curtclifton.net/papers/MoseleyMarks06a.pdf http://curtclifton.net/papers/MoseleyMarks06a.pdf
  - hashkb 5y ago
    Just to add on to this - remember the first time you saw syntax highlighting? And before that, the code was all in one color? You didn't know you needed it before, but you didn't go back, did you?
    - mst 5y ago
      Yes, yes I did. As fast as humanly possible. Syntax highlighting seems to help most people but I find it a horrible impediment to reading code.
mongol 5y ago
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 "cell editor". That would support all version control use cases that developers are familiar with and that have been best practice for decades. Perhaps all that is needed is to port OpenOffice to the sc format (and extend it in the spirit it works now)
- deleted 5y ago
  [deleted]
- sneak 5y ago
  This might be able to be achieved with a new serialization format for xls files. Something line-based, with canonicalized sorting of cells.
- breck 5y ago
  At my last job at OurWorldInData we made something like this. One of the head researchers would build sophisticated spreadsheets containing all the transformations and views our users could do, and rather than re-implement that logic in Typescript, we saved it as TSV and built a spreadsheet editor for the researchers to use. From a code perspective it was just a tree to traverse. Demo: https://www.youtube.com/watch?v=0l2QWH-iV3k https://www.youtube.com/watch?v=0l2QWH-iV3k Changes in the spreadsheet UI then work really well with git. For example: https://github.com/owid/owid-content/commit/37ef12d65655fa144ed1d9dc53cf8f5bbb952c67 https://github.com/owid/owid-content/commit/37ef12d65655fa14...
  - mongol 5y ago
    I did not really understand that demo. What was different compared to a regular spreadsheet?
    - breck 5y ago
      It is a strongly typed grammar backed spreadsheet that maps to a tree. The grammar enables the autocomplete, error checking, secondary notations, and so forth. Not sure if that explains it. From a different perspective: other programmers can work with the programs generated by it without knowing that it’s a spreadsheet.
rmbeard 5y ago
No-one is going to pay to version control Excel.
- Jtsummers 5y ago
  Actually, people do. But it's not terribly fine-grained. SharePoint offers version control of MS Office documents and is used in many businesses as an improvement over shared drives and files named: Foo_Report_v3_FINAL_20210928_FINAL_DRAFT_FINAL.xlsx I don't think you get branching with SharePoint, though.
- xupybd 5y ago
  I'm about to pitch this to my manager. We have automated manufacturing going through Excel. It allows the domain experts to tweak the manufacturing process without having to learn to code. The price is going to make this a difficult sell. If it was one off at $1000 easy but monthly per user...
- pjmlp 5y ago
  They do, it is called Sharepoint.
aarreedd 5y ago
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. Last time I looked into Dolt there were no commit hooks either. That would let you add linting or other data validation.
mozey 5y ago
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.
- onychomys 5y ago
  I work in a lab where there are lots of excel sheets floating around. I went one step further - when I save an xltm (an excel template), the code is exported and then a bash script automatically uploads it into my git repo. The VBA asks for a commit message and then all the rest is automatic. It's worked pretty well, all things considered.
- pessimizer 5y ago
  When I was doing a lot of VBA work like this, I used an OSS tool that would export all of the code and check it into SVN. I can't remember the name of it for the life of me, though, but it's probably hiding on a drive somewhere. edit: This was probably it https://www.codeproject.com/Articles/18029/SourceTools-xla https://www.codeproject.com/Articles/18029/SourceTools-xla
- hi5dev 5y ago
  I once wrote an MSBuild script that decompiled a workbook into XML and VBA code. It used a CLI tool I wrote that opened the workbook using VSTO to access the VBA objects. I never finished it, though. I only spent enough time on it to realize that it was going to be too big of a project to make it worth it. So now I just rename the workbook to a zip file, extract it, and check that into git. Only drawback is that the VBA macros are in an OLE container. But I stay away from VBA, so it's not that big of a deal for me.
xyzzy21 5y ago
Excel IS code. It's a dataflow language combined with a visual/spatial language. It is hard to migrate or transfer to other languages because other language don't have these features/architecture. 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't at all.
- mcbishop 5y ago
  >spreadsheets have NOT BEEN IMPROVED significantly since VisiCalc Not true. Excel added dynamic-array formulas a few years ago (where a single formula automatically spills into applicable cells below the edited cell) — game changer. And LAMBDA functions are currently in the Excel beta version (create your own (recursive) functions directly in Excel) — another game changer.
  - Mengkudulangsat 5y ago
    The improvements you mentioned are honestly esoteric. I'm more curious on why we can't have built-in version control, or have unlimited rows, or have a linter in the formula bar in excel by now.
  - pjmlp 5y ago
    You forgot about the F# flavoured query language as well.
- CRConrad 5y ago
  > spreadsheets have NOT BEEN IMPROVED significantly since VisiCalc. False -- they have indeed been improved. Unfortunately the Improv-ement didn't stick: https://en.wikipedia.org/wiki/Lotus_Improv https://en.wikipedia.org/wiki/Lotus_Improv
CivBase 5y ago
> Unfortunately, none of this applies to Excel because Excel doesn'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. This is where the author lost me. The "path to enlightenment" 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. 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's all you need and Excel is a fine tool for those situations. But it has limitations. Stop trying to force Excel as the solution to all your problems and don't be afraid to learn a new tool once in a while.
greenreptar 5y ago
Surprised nobody has mentioned this. There is a company called Boardwalktech with a tool called "Excel Cloud" which adds a native extension into Excel which includes a change log and (i think) realtime collaboration, among other things. They call their underlying tool a "digital ledger" which sounds very blockchain-y, but it's not a distributed public ledger so there's no crypto here, just a centralized, Boardwalktech controlled ledger. https://www.boardwalktech.com/products/boardwalk-excel-cloud https://www.boardwalktech.com/products/boardwalk-excel-cloud They're already integrated with some very big companies like Accenture, Ernst and Young, Coca-Cola, Mars, Facebook, etc etc. Personally, I can'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'm not running all of the companies mentioned above.
- Frost1x 5y ago
  How is this different than the Office 365 version of Excel? It produces change logs/version management and real time collaboration.
jayd16 5y ago
So this is an ad for some new merge tool I suppose. Is there a solid open source tool for merging Excel files? Or CSVs or SQLite files for that matter? I think this is probably best seen as a shortcoming of our current general VCS. At the moment we'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.
Zababa 5y ago
> People refuse to stop using Excel because it empowers them and they simply don't want to be disempowered. That is not always true in my experience. Many people use Excel because it'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's useless. And distributing executables is usually not tolerated either. So you use excel, and share Excel files. I'll add that another big problem I have with Excel is usually the lack of database support. Moving data around by copy/pasting it in Excel with macros is a pain, and IT didn't allow Microsoft Access either so I can't comment on that. But I think it would have made my life easier.
- JohnnyHerz 5y ago
  If you substitute "spreadsheet" for "Excel" than i agree. But i have tried everything possible to avoid Excel as every iteration just brings new problems instead of fixes. I used Clarisworks years after it was EOL and now am trying very hard to convert to Libreworks. Admittedly i can't completely escape Excel yet, but i am hopeful and it's getting to the point where the bugs in Libreworks are no worse the bugs in Excel. If not for all the Legacy Excel sheets i have, i'd be off it completely.
- dan-robertson 5y ago
  I somewhat disagree. I work with a lot of excel power users. We have some massive spreadsheets which are collaboratively worked on and do complicated things. The first thing to say about them is that they are very valuable to the business so it is important to be able to do some of the things excel does. Excel has a lot of advantages compared to regular programming: - It is quick to change. The programs I work on take nearly an hour to go from code review completion to production, even with manual poking to speed up continuous deployment. It can be valuable to be able to change things quickly. - In excel the main thing you interact with is the data. If you are a domain expert then you should be able to look at outputs and see if they seem right. When you change a formula or add a column, you are, in some sense, also getting to run it on realistic data instead of needing to try to construct realistic tests. - There isn’t much difference between config parameters and hard coded values. In the programming language I use, you can’t really have globally readable configs so any new parameter must be threaded through from app startup to the place you want to use it, discouraging configuration parameters. Which means it is often slow to change something that ought to have been configurable. In excel you can make a quick cell for some Config parameter (changing a lot of formulas is not so fun though.) - Functional and declarative, Excel tends to give you internally consistent output. There is less need to worry about incorrect state updates. - Its maybe better for producing graphs. I never really liked making graphs in excel and I thought the defaults were bad for good data visualisation but then other systems have bad defaults (when I draw a graph I often use GNU Calc with gnuplot…) - Pivot tables are great for ad-hoc analysis (indeed Excel is pretty good for as-hoc analysis in general.) The pivoting operation is trivial in excel and a big pain to with tools like grep or awk or sed. These Excel users are generally capable of programming too and may use jupyter notebooks with python or R, or something more fully featured when required. And some things will get outsourced to software engineers, but excel is still clearly useful (so long as it scales) and people don’t just use it because they are desperate for some kind of ‘real’ programming language.
fzumstein 5y ago
At https://www.xltrail.com https://www.xltrail.com, 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's free Git client. You can see some screenshots on my blog post: https://dev.to/fzumstein/how-to-diff-excel-vba-code-in-sourcetree-git-client-36k https://dev.to/fzumstein/how-to-diff-excel-vba-code-in-sourc...
PicassoCTs 5y ago
Excel as code is a main spreading vector for bad practices like copy & paste, monolithic procedural monsters, bad databases with duplicate entries and so forth. 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.. If you ever wondered, why management does not blink and recoil one description of coding horrors..
theonlybutlet 5y ago
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'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.
- wvenable 5y ago
  Microsoft borked it with the migration to .NET. Instead of making VB.NET 100% compatible with VBA they created a unnecessary C# clone with a VB skin. That decision ended VB as a viable product and any migration path for VBA in Office. If they had made VB.NET fully compatible, then we'd all just have the CLR in Office and we could be using any number of languages to write Office integrated software.
  - theonlybutlet 5y ago
    That's interesting.
surfingdino 5y ago
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'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'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.
- MeinBlutIstBlau 5y ago
  I worked in a bank where we still used paper because the lead supervisor told us we had to. I'm not talking like paper that was needed so we just kept it in the file, I'm talking "Print out the entire loan profile in paper simply because the supervisor refused to learn how to do use a computer" paper. We're talking thousands of pages a day for ONE LOAN! All because this woman was lapsed by technology and HR had no clue she was so out of touch.
  - surfingdino 5y ago
    I know exactly what you mean. MS Office, even on the web, is the equivalent of that kind of mentality.
- conductr 5y ago
  I’ve been using VBA for a long time. It’s not great but allows for some things that wouldn’t otherwise be possible. I like to think I use it as a last resort. That said, I’ve been following the development around using JS and others within excel and I just don’t get it. Or, when I do, it seems ass backwards to me (like a hosted app on onedrive). I don’t want that. And I don’t really see what good using JS is if it’s just a wrapper for the quirky VBA I already am familiar with.
bob1029 5y ago
Only for a lack of imagination would you fail to perfectly model your target problem domain in terms of tables & columns... You would have a fucking monster of a time trying to describe to me a practical problem that I could not hypothetically wrangle & demonstrate with Excel. Just think about it. You can model a ray tracer in Excel if you have the patience for it. The magic of Excel is that it runs everywhere and is very intuitive to work with. I honestly can'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. 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. 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. I will conclude my rant by saying that there is no logical determination/interpolation/projection of facts which is unachievable in an ideal SQL representation. It is very 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.
- sokoloff 5y ago
  It seems like most graph traversal and high-dimension problems would be difficult to model in Excel’s 2D data structures. (Don’t get me wrong, I think more of the world runs on Excel than most people think and it’s perfectly well-suited for it, but “it can do anything” does not seem practically true to me.)
  - gh02t 5y ago
    Actual tensor math would be fun to see. Definitely possible but it'd be ugly.
badhombres 5y ago
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't believe the creators ever intended. Adding a scripting capability to it has unlocked the spirit of challenge in all SME's of finance related fields to make Excel the sole software they will use for all problems.
- OzCrimson 5y ago
  This depends on the evidence you want to highlight. There are a lot of truly amazing things people use Excel for. And they work. There's no denying that.
jacobdi 5y ago
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. 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's benefits without needing to know how to code. [1] https://docs.trymito.io/ https://docs.trymito.io/
OzCrimson 5y ago
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. One thing to remember is that the vast majority of Excel users aren't fully in IT or tech. We have to deal with data but the roles aren't primarily data roles. - Customer Service Reps - Admin Assistants - Warehouse Managers - Non-profit Fundraisers - Sales Reps - Realtors - Inventory Managers - Insurance Agents I'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? === 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. Here'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? Personally, I hate true programming--and I'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's this wall of letters, numbers, indents, curly-brackets ... WOAAAHHHHHHH! No. HELL NO! Even with WordPress and the templates that are supposedly drag-&-drop, I still found myself writing CSS and HTML. === One other thing. Don't forget looking the opposite way. Too many coders don'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.
- sokoloff 5y ago
  I think a lot more automation/computing should be done in these more approachable “citizen programming” tools. “Job done”, “I did it myself”, and “I understand how it works” are three qualities that are often undervalued when “real programmers” look at the work of “citizen programmers”. I say this as someone who loves and makes a living at “real programming”. We need more not less sub-real programming.
  - OzCrimson 5y ago
    WOW! Excellent perspective. And I've never heard the term "citizen programmer" before. You're right. "I did it myself" and "I understand how it works" are definitely undervalued. And that plays into a lot of the empowerment/disempowerment conversation. I had a client who would have me build prototypes in Excel, then he'd hand them over to his in-house development team. I asked him why he uses me in the middle. He explained that he can guide me and kinda understand what I'm doing, and we can test and tweak formulas really easily. He can stop me and ask questions if I start doing something that seems wrong. Then he said, "but, when my devs open that code editor, I don't know what the hell I'm looking at." That was a different kind of disempowerment that he felt vis-a-vis his own devs.
bla15e 5y ago
Excel is a language alright, a hideous and perverse one!
shrubby 5y ago
Nice perspective. Thanks.
kull 5y ago
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.

Excel as Code

166 points by LukeEF 5y ago

kyberias 5y ago

> Because Excel is not a source file. Well, it is a zip-archive with XML files, so it's close.

cxr 5y ago
I recommended exploring this approach here <https://news.ycombinator.com/item?id=27998733 https://news.ycombinator.com/item?id=27998733>: > Hot tip for handling office file formats or anything that uses a ZIP container: just unzip them and commit _that_ to the repo. Even modern (zipped XML-based) office file formats do make some limited use of binary blobs. You can either keep these intact, or write a small objdump-like tool that serializes them to text†. For portability, it might be best to write the serializer/deserializer in JS dumped into a thin HTML wrapper, so you pretty much anyone can double click to "run" it. (My experiments on roundtrippability with including that file in the ZIP container yielded poor results.) † I've used this strategy for Oberon .rsc binaries. Due to Wirth's affinity for single-pass compilers, the Oberon toolchain doesn't involve a discrete assembler or AOT linker tool, so there is no assembly format or linker scripts. However, Wirth's distribution of the Oberon system does have an ORTool utility <https://people.inf.ethz.ch/wirth/ProjectOberon/Sources/ORTool.Mod.txt https://people.inf.ethz.ch/wirth/ProjectOberon/Sources/ORToo...> (in the vein of objdump/readelf/nm) that will dump a textual description of the binary you give it. I realized that with some slight tweaks, you can use the output of ORTool.DecObj as a de facto "assembly" format—just write a tool capable of parsing it and then write out the corresponding binary.
- inshadows 5y ago
  >> Hot tip for handling office file formats or anything that uses a ZIP container: just unzip them and commit _that_ to the repo. What is the point if that? I think neither binary nor XML output would be meaningful in the diff output.
WorldMaker 5y ago
I built a tool to explore version control of files like that by decompressing their contents and version controlling those. It was an interesting experiment.
banana_giraffe 5y ago
Notably: The VBA stuff is stored as a binary OLE2 blob thing inside of the xlsm file. (Or at least it is in the few spreadsheets I checked, no clue if there's some way to change that behavior)
- tyingq 5y ago
  The VBA blob is documented: https://interoperability.blob.core.windows.net/files/MS-OVBA/%5bMS-OVBA%5d.pdf https://interoperability.blob.core.windows.net/files/MS-OVBA... 111 pages, and it looks non-trivial to implement something to tear it apart. But, I give MS some credit for documenting it.

jagged-chisel 5y ago

> Git was not built for this - ... 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?

wcerfgba 5y ago
You can still use 'straight Git', maybe with some PR management system like GitHub/GitLab/... . The difference is you can't rely on the diff to be useful, instead you'd need to provide a good commit message summarising the changes (which you should do anyway!) and then reviewers will need to check out the relevant version to poke it directly in Excel. But I agree that being able to manage an XLS(X) as plain text and having a proper diff would be incredibly useful. :)
- da_chicken 5y ago
  I highly doubt you'd ever get diff for XLS in a general or universal case. That format is so old and crusty that it's only really defined by what Excel will do with it. XLSX, on the other hand, at least has to follow XML conventions and basic ZIP file structure, even if the open specification for the XML is really now a strict subset of what the current version of Excel will accept.
marklit 5y ago
Git supports extension-specific overrides which enables things like textual comparison of Office files. https://tech.marksblogg.com/git-track-changes-in-media-office-documents.html https://tech.marksblogg.com/git-track-changes-in-media-offic...
WorldMaker 5y ago
I explored storing file types like XLSX as the deconstruction of their zip file into individual XML/etc files. In my cases my focus was DOCX rather than XLSX, and I originally targeted a different VCS than git so I built it as precommit/postmerge hooks rather than git's diff hooks/attributes plugins. I got some interesting results with my tool and it wasn't a bad experience. Just not one I could suggest to novice users (fixing XML in a merge conflict is not entirely fun and very different from say Word's own review tools designed for higher level merge fixing).

amirhirsch 5y ago

I offer my master's thesis "Compiling and optimizing spreadsheets for FPGA and multicore execution" https://dspace.mit.edu/handle/1721.1/45983 https://dspace.mit.edu/handle/1721.1/45983

breck 5y ago
I'm really enjoying reading this. Ahead of its time. I really like your RISC CPU in a spreadsheet in Table 1-4. Have you seen others do this since you wrote it?
- amirhirsch 5y ago
  Thanks. It is still crazy to me that this was 15 years ago since I still have nightmares about completing it. I used the approach to design a PDP-11 Floating Point Unit commercially, but haven't really seen more Spreadheet-to-FPGA work or RISC emulation -- it is clearly my thing to do. Microsoft just added LAMBDA to Excel but didn't copy my approach of capturing a table as the formula--Like most people if I start with a big data table to analyze, I start by spreading my formulas across a row with short intermediate results, and then test it on a few rows before applying it to the whole table. My lambda would let you select your input cells and define your output cells and capture the dataflow graph between them, with any external references as globals and then spare you the results in intermediate cells. I spent some time trying to sell this to Wall Street folks in 2007-08. I remember presenting on this at a supercmputing conference on Wall Street exactly today 13 years ago, and the Lehman guy on the panel didn't show up because they shut down that day.
  - mcbishop 5y ago
    Your step-by-step approach would be vastly more user friendly than the current LAMBDA implementation (where the user needs to put the entire nested function in the Name Manager). Simon Peyton Jones advocated for a similar step-by-step approach: https://www.microsoft.com/en-us/research/uploads/prod/2018/11/elastic-sdfs-jfp2020.pdf https://www.microsoft.com/en-us/research/uploads/prod/2018/1.... At least, step-by-step can be facilitated with a programming language (Visual Basic, C++, Python, JavaScript). ...That could be a sweet Excel add-in.

spoonjim 5y ago

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.

WorldMaker 5y ago
Sounds like you've never seen advanced Accounting spreadsheets because Excel definitely does not have left-to-right/top-to-bottom or even intermediate values restrictions. There are some amazing Gordian knots people have programmed in Excel. You haven't really seen the horrors of programming in Excel until you've needed to use the "Formula Auditing" group of the Formulas tab in the Excel ribbon. Admittedly "Trace Dependents" and "Trace Precedents" are still rather more visual tools than their source code equivalents, but they are their own sort of fun.
- azalemeth 5y ago
  I share your pain and have seen people doing everything from numerical integration to curve fitting in Excel, all of it terribly. I worry what new special versions of recursive hell the new lambda functions will unleash upon us.
Someone 5y ago
That’s true for only a subset of spreadsheets. There’s no requirement for formulas to work left to right and top to bottom. Also, the moment you write = (A1 + A2)/2, not all intermediate values are visible anymore (although Excel has support for temporarily making them visible (https://support.microsoft.com/en-us/office/evaluate-a-nested-formula-one-step-at-a-time-59a201ae-d1dc-4b15-8586-a70aa409b8a7 https://support.microsoft.com/en-us/office/evaluate-a-nested...)) Also, in my experience, it’s fairly normal to have hidden rows or columns (https://support.microsoft.com/en-us/office/hide-or-show-rows-or-columns-659c2cad-802e-44ee-a614-dde8443579f8 https://support.microsoft.com/en-us/office/hide-or-show-rows...) or hide entire sheets (https://support.microsoft.com/en-us/office/hide-or-unhide-worksheets-69f2701a-21f5-4186-87d7-341a8cf53344 https://support.microsoft.com/en-us/office/hide-or-unhide-wo...) And of course, the ultimate “not all intermediate values are visible” is the use of macro functions or iterations (https://support.microsoft.com/en-us/office/change-formula-recalculation-iteration-or-precision-in-excel-73fc7dac-91cf-4d36-86e8-67124f6bcce4 https://support.microsoft.com/en-us/office/change-formula-re...)

haney 5y ago

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

hugi 5y ago
A few years back, I was tasked with a similar thing. A government ministry was creating a complex calculator for a (very anticipated) public project that was supposed to go on their website. We started out using pure JS but the mathematician working on the project kept giving us new Excel documents with extremely heavy changes to the algorithms. In the end I gave him a location where he could upload the document and told him to just make sure inputs and outputs were always in the same predefined cells. Then we used Java and Apache POI to load the Excel document and run the actual calculations on the website. Best decision ever.
- antris 5y ago
  >In the end I gave him a location where he could upload the document and told him to just make sure inputs and outputs were always in the same predefined cells. Then we used Java and Apache POI to load the Excel document and run the actual calculations on the website. Best decision ever. This is the kind of simple and effective solution that programmers who think they know everything would scoff at. Love it.
  - tehbeard 5y ago
    It's simple and effective until a part of the rube goldberg machine breaks...
    - deleted 5y ago
      [deleted]
  - 5y ago

LukeEF 5y ago

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'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!).

croes 5y ago

"virtually nobody treats Excel seriously like a programming language." Because Excel was not Turing complete until recently.

WorldMaker 5y ago
VBA was not recent. Also, you'd be amazed by Turing Complete things like what someone determined can do with just VLOOKUP(). That's even before you get into truly abstract models of things proven Turing Complete such as Rule 110 of Cellular Automata and how easy/hard you can implement them in Excel without VBA Macros or "advanced functions".
thefifthsetpin 5y ago
Can't you just implement a turing machine in Excel by using the cells in a row as your tape? * Store the initial internal state in A1. * Store the initial head position B1. * Store the initial state as boolean values in the rest of row 1. * Write simple lookup formulas in row 2 to compute the next state from the previous row. * Fill down. Look for the halting state in column A and your output will be written in that row. What am I missing?
- felienne 5y ago
  Yeah you can, I made one here a long time ago: https://www.felienne.com/archives/2974 https://www.felienne.com/archives/2974 :)

behnamoh 5y ago

It'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. When it comes to making programming approachable for the masses, it's actually kinda funny to think that Excel (and spreadsheets in general) have been way ahead of traditional programming software. I hoped that new tech (AR/VR/etc) would help shift the focus from "typing" programs to "drawing" programs. But efforts to visualize programming only remain at the conceptual level and never gained traction. It's hard to imagine 100 years from now we will still be typing code.

piyh 5y ago
Typing speed is not my bottleneck for generating code, it's renaming variables and rewriting it 5 times until it's no longer a mess. A Vulcan mind meld would be nice but lacks precision.
surfingdino 5y ago
Musicians have been happy with their simple keyboards for hundreds of years. Why wouldn't software developers be using theirs in a hundred years?
analog31 5y ago
Creating complex things using drawing tools is physically laborious, and suffers from readability problems when things get too complicated to fit on one screen. I've seen this with mechanical and electrical CAD.
Existenceblinks 5y ago
As long as human still use a list of characters to represent things. Typing textual code is inevitable, even if you have a fancy editor to edit its semantic, there will always text here and there in whatever kind of language.

ianhorn 5y ago

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 do make that you wouldn’t make in normal programming). 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.

john_alan 5y ago

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

ivyirwin 5y ago

I get the spirit of the document, but disagree with the goal. I'm biased, I've kind of made my career writing web applications for people reliant on Excel. While I've come to respect it's power – I had a colleague in architecture school design buildings using excel and I've seen some ridiculous formulas based on crazy pivot tables and conditionals. I'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 "programming language" 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. It's a nice thought experiment, but in my mind I think the world would be a better place without excel.

qsort 5y ago
> When the interface is the code and the output, the lack of consistent implementation is something I find frustrating This is the reason why spreadsheets are popular in the first place, though. I won't ever defend them - I'm on a project right now that's been working on Excel for years, I know the pain! - but this is something that's worth thinking about. See also Jupyter Notebooks, yet another invention from the deep pits of hell. The popularity of the interactive paradigm is undeniable. Would the world be better if everyone started using something sane instead? Definitely so. But the world would also be better if every day was Christmas and that's not going to happen either. So while I share most of your concerns, I'm mostly sympathetic with the OP.
swypych 5y ago
"It's a nice thought experiment, but in my mind I think the world would be a better place without excel. " I agree with most everything you said, however, proliferation of programming and automation is a net win in my books, no matter the medium, and good spreadsheet software does this incredibly well. It makes programming in its very basic form accessible to a wide amount of users with a relative gradual and easy to grasp learning curve. Sure you can always improve on it, but I think the world would most definitely not be better off without it. I do agree that the work is hidden, they can be a nightmare to audit, and I think it would scare a lot of people on this board the amount of business critical functions that are completed by excel and other spreadsheets. However, I like to think this a short term problem, and to the authors point, the industry and the sw needs and will improve, and we should all be trying to eventually close the gap.

croes 5y ago

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

Jtsummers 5y ago
You keep saying what they don't want to, but remember that most users of Excel are not programmers in the traditional sense. I'd wager that most of them aren't even aware of the things you say they don't want. It's not that you don't need to follow modern software practices. It's that they don't know about them to follow them or not. Further, Excel is almost pure thought stuff. The distance between the user's idea and their implementation is almost as small as we can get without investing in a lot of educational outreach. And then they end up going crazy with macros because they don't know there are other or better ways to do it. Also, "modern" Excel (not sure which version, maybe 2007 or 2010?) has largely obviated the need for macros with the addition of tables and functions for interacting with them. It turns Excel into a kind of relational database permitting something close to functional relational programming as described in "Out of the Tar Pit" [0]. [0] http://curtclifton.net/papers/MoseleyMarks06a.pdf http://curtclifton.net/papers/MoseleyMarks06a.pdf
- hashkb 5y ago
  Just to add on to this - remember the first time you saw syntax highlighting? And before that, the code was all in one color? You didn't know you needed it before, but you didn't go back, did you?
  - mst 5y ago
    Yes, yes I did. As fast as humanly possible. Syntax highlighting seems to help most people but I find it a horrible impediment to reading code.

mongol 5y ago

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 "cell editor". That would support all version control use cases that developers are familiar with and that have been best practice for decades. Perhaps all that is needed is to port OpenOffice to the sc format (and extend it in the spirit it works now)

deleted 5y ago
[deleted]
sneak 5y ago
This might be able to be achieved with a new serialization format for xls files. Something line-based, with canonicalized sorting of cells.
breck 5y ago
At my last job at OurWorldInData we made something like this. One of the head researchers would build sophisticated spreadsheets containing all the transformations and views our users could do, and rather than re-implement that logic in Typescript, we saved it as TSV and built a spreadsheet editor for the researchers to use. From a code perspective it was just a tree to traverse. Demo: https://www.youtube.com/watch?v=0l2QWH-iV3k https://www.youtube.com/watch?v=0l2QWH-iV3k Changes in the spreadsheet UI then work really well with git. For example: https://github.com/owid/owid-content/commit/37ef12d65655fa144ed1d9dc53cf8f5bbb952c67 https://github.com/owid/owid-content/commit/37ef12d65655fa14...
- mongol 5y ago
  I did not really understand that demo. What was different compared to a regular spreadsheet?
  - breck 5y ago
    It is a strongly typed grammar backed spreadsheet that maps to a tree. The grammar enables the autocomplete, error checking, secondary notations, and so forth. Not sure if that explains it. From a different perspective: other programmers can work with the programs generated by it without knowing that it’s a spreadsheet.

rmbeard 5y ago

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

Jtsummers 5y ago
Actually, people do. But it's not terribly fine-grained. SharePoint offers version control of MS Office documents and is used in many businesses as an improvement over shared drives and files named: Foo_Report_v3_FINAL_20210928_FINAL_DRAFT_FINAL.xlsx I don't think you get branching with SharePoint, though.
xupybd 5y ago
I'm about to pitch this to my manager. We have automated manufacturing going through Excel. It allows the domain experts to tweak the manufacturing process without having to learn to code. The price is going to make this a difficult sell. If it was one off at $1000 easy but monthly per user...
pjmlp 5y ago
They do, it is called Sharepoint.

aarreedd 5y ago

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. Last time I looked into Dolt there were no commit hooks either. That would let you add linting or other data validation.

mozey 5y ago

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.

onychomys 5y ago
I work in a lab where there are lots of excel sheets floating around. I went one step further - when I save an xltm (an excel template), the code is exported and then a bash script automatically uploads it into my git repo. The VBA asks for a commit message and then all the rest is automatic. It's worked pretty well, all things considered.
pessimizer 5y ago
When I was doing a lot of VBA work like this, I used an OSS tool that would export all of the code and check it into SVN. I can't remember the name of it for the life of me, though, but it's probably hiding on a drive somewhere. edit: This was probably it https://www.codeproject.com/Articles/18029/SourceTools-xla https://www.codeproject.com/Articles/18029/SourceTools-xla
hi5dev 5y ago
I once wrote an MSBuild script that decompiled a workbook into XML and VBA code. It used a CLI tool I wrote that opened the workbook using VSTO to access the VBA objects. I never finished it, though. I only spent enough time on it to realize that it was going to be too big of a project to make it worth it. So now I just rename the workbook to a zip file, extract it, and check that into git. Only drawback is that the VBA macros are in an OLE container. But I stay away from VBA, so it's not that big of a deal for me.

xyzzy21 5y ago

Excel IS code. It's a dataflow language combined with a visual/spatial language. It is hard to migrate or transfer to other languages because other language don't have these features/architecture. 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't at all.

mcbishop 5y ago
>spreadsheets have NOT BEEN IMPROVED significantly since VisiCalc Not true. Excel added dynamic-array formulas a few years ago (where a single formula automatically spills into applicable cells below the edited cell) — game changer. And LAMBDA functions are currently in the Excel beta version (create your own (recursive) functions directly in Excel) — another game changer.
- Mengkudulangsat 5y ago
  The improvements you mentioned are honestly esoteric. I'm more curious on why we can't have built-in version control, or have unlimited rows, or have a linter in the formula bar in excel by now.
- pjmlp 5y ago
  You forgot about the F# flavoured query language as well.
CRConrad 5y ago
> spreadsheets have NOT BEEN IMPROVED significantly since VisiCalc. False -- they have indeed been improved. Unfortunately the Improv-ement didn't stick: https://en.wikipedia.org/wiki/Lotus_Improv https://en.wikipedia.org/wiki/Lotus_Improv

CivBase 5y ago

> Unfortunately, none of this applies to Excel because Excel doesn'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. This is where the author lost me. The "path to enlightenment" 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. 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's all you need and Excel is a fine tool for those situations. But it has limitations. Stop trying to force Excel as the solution to all your problems and don't be afraid to learn a new tool once in a while.

greenreptar 5y ago

Surprised nobody has mentioned this. There is a company called Boardwalktech with a tool called "Excel Cloud" which adds a native extension into Excel which includes a change log and (i think) realtime collaboration, among other things. They call their underlying tool a "digital ledger" which sounds very blockchain-y, but it's not a distributed public ledger so there's no crypto here, just a centralized, Boardwalktech controlled ledger. https://www.boardwalktech.com/products/boardwalk-excel-cloud https://www.boardwalktech.com/products/boardwalk-excel-cloud They're already integrated with some very big companies like Accenture, Ernst and Young, Coca-Cola, Mars, Facebook, etc etc. Personally, I can'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'm not running all of the companies mentioned above.

Frost1x 5y ago
How is this different than the Office 365 version of Excel? It produces change logs/version management and real time collaboration.

jayd16 5y ago

So this is an ad for some new merge tool I suppose. Is there a solid open source tool for merging Excel files? Or CSVs or SQLite files for that matter? I think this is probably best seen as a shortcoming of our current general VCS. At the moment we'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.

Zababa 5y ago

> People refuse to stop using Excel because it empowers them and they simply don't want to be disempowered. That is not always true in my experience. Many people use Excel because it'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's useless. And distributing executables is usually not tolerated either. So you use excel, and share Excel files. I'll add that another big problem I have with Excel is usually the lack of database support. Moving data around by copy/pasting it in Excel with macros is a pain, and IT didn't allow Microsoft Access either so I can't comment on that. But I think it would have made my life easier.

JohnnyHerz 5y ago
If you substitute "spreadsheet" for "Excel" than i agree. But i have tried everything possible to avoid Excel as every iteration just brings new problems instead of fixes. I used Clarisworks years after it was EOL and now am trying very hard to convert to Libreworks. Admittedly i can't completely escape Excel yet, but i am hopeful and it's getting to the point where the bugs in Libreworks are no worse the bugs in Excel. If not for all the Legacy Excel sheets i have, i'd be off it completely.
dan-robertson 5y ago
I somewhat disagree. I work with a lot of excel power users. We have some massive spreadsheets which are collaboratively worked on and do complicated things. The first thing to say about them is that they are very valuable to the business so it is important to be able to do some of the things excel does. Excel has a lot of advantages compared to regular programming: - It is quick to change. The programs I work on take nearly an hour to go from code review completion to production, even with manual poking to speed up continuous deployment. It can be valuable to be able to change things quickly. - In excel the main thing you interact with is the data. If you are a domain expert then you should be able to look at outputs and see if they seem right. When you change a formula or add a column, you are, in some sense, also getting to run it on realistic data instead of needing to try to construct realistic tests. - There isn’t much difference between config parameters and hard coded values. In the programming language I use, you can’t really have globally readable configs so any new parameter must be threaded through from app startup to the place you want to use it, discouraging configuration parameters. Which means it is often slow to change something that ought to have been configurable. In excel you can make a quick cell for some Config parameter (changing a lot of formulas is not so fun though.) - Functional and declarative, Excel tends to give you internally consistent output. There is less need to worry about incorrect state updates. - Its maybe better for producing graphs. I never really liked making graphs in excel and I thought the defaults were bad for good data visualisation but then other systems have bad defaults (when I draw a graph I often use GNU Calc with gnuplot…) - Pivot tables are great for ad-hoc analysis (indeed Excel is pretty good for as-hoc analysis in general.) The pivoting operation is trivial in excel and a big pain to with tools like grep or awk or sed. These Excel users are generally capable of programming too and may use jupyter notebooks with python or R, or something more fully featured when required. And some things will get outsourced to software engineers, but excel is still clearly useful (so long as it scales) and people don’t just use it because they are desperate for some kind of ‘real’ programming language.

fzumstein 5y ago

At https://www.xltrail.com https://www.xltrail.com, 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's free Git client. You can see some screenshots on my blog post: https://dev.to/fzumstein/how-to-diff-excel-vba-code-in-sourcetree-git-client-36k https://dev.to/fzumstein/how-to-diff-excel-vba-code-in-sourc...

PicassoCTs 5y ago

Excel as code is a main spreading vector for bad practices like copy & paste, monolithic procedural monsters, bad databases with duplicate entries and so forth. 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.. If you ever wondered, why management does not blink and recoil one description of coding horrors..

theonlybutlet 5y ago

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'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.

wvenable 5y ago
Microsoft borked it with the migration to .NET. Instead of making VB.NET 100% compatible with VBA they created a unnecessary C# clone with a VB skin. That decision ended VB as a viable product and any migration path for VBA in Office. If they had made VB.NET fully compatible, then we'd all just have the CLR in Office and we could be using any number of languages to write Office integrated software.
- theonlybutlet 5y ago
  That's interesting.

surfingdino 5y ago

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'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'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.

MeinBlutIstBlau 5y ago
I worked in a bank where we still used paper because the lead supervisor told us we had to. I'm not talking like paper that was needed so we just kept it in the file, I'm talking "Print out the entire loan profile in paper simply because the supervisor refused to learn how to do use a computer" paper. We're talking thousands of pages a day for ONE LOAN! All because this woman was lapsed by technology and HR had no clue she was so out of touch.
- surfingdino 5y ago
  I know exactly what you mean. MS Office, even on the web, is the equivalent of that kind of mentality.
conductr 5y ago
I’ve been using VBA for a long time. It’s not great but allows for some things that wouldn’t otherwise be possible. I like to think I use it as a last resort. That said, I’ve been following the development around using JS and others within excel and I just don’t get it. Or, when I do, it seems ass backwards to me (like a hosted app on onedrive). I don’t want that. And I don’t really see what good using JS is if it’s just a wrapper for the quirky VBA I already am familiar with.

bob1029 5y ago

Only for a lack of imagination would you fail to perfectly model your target problem domain in terms of tables & columns... You would have a fucking monster of a time trying to describe to me a practical problem that I could not hypothetically wrangle & demonstrate with Excel. Just think about it. You can model a ray tracer in Excel if you have the patience for it. The magic of Excel is that it runs everywhere and is very intuitive to work with. I honestly can'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. 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. 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. I will conclude my rant by saying that there is no logical determination/interpolation/projection of facts which is unachievable in an ideal SQL representation. It is very 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.

sokoloff 5y ago
It seems like most graph traversal and high-dimension problems would be difficult to model in Excel’s 2D data structures. (Don’t get me wrong, I think more of the world runs on Excel than most people think and it’s perfectly well-suited for it, but “it can do anything” does not seem practically true to me.)
- gh02t 5y ago
  Actual tensor math would be fun to see. Definitely possible but it'd be ugly.

badhombres 5y ago

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't believe the creators ever intended. Adding a scripting capability to it has unlocked the spirit of challenge in all SME's of finance related fields to make Excel the sole software they will use for all problems.

OzCrimson 5y ago
This depends on the evidence you want to highlight. There are a lot of truly amazing things people use Excel for. And they work. There's no denying that.

jacobdi 5y ago

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. 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's benefits without needing to know how to code. [1] https://docs.trymito.io/ https://docs.trymito.io/

OzCrimson 5y ago

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. One thing to remember is that the vast majority of Excel users aren't fully in IT or tech. We have to deal with data but the roles aren't primarily data roles. - Customer Service Reps - Admin Assistants - Warehouse Managers - Non-profit Fundraisers - Sales Reps - Realtors - Inventory Managers - Insurance Agents I'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? === 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. Here'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? Personally, I hate true programming--and I'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's this wall of letters, numbers, indents, curly-brackets ... WOAAAHHHHHHH! No. HELL NO! Even with WordPress and the templates that are supposedly drag-&-drop, I still found myself writing CSS and HTML. === One other thing. Don't forget looking the opposite way. Too many coders don'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.

sokoloff 5y ago
I think a lot more automation/computing should be done in these more approachable “citizen programming” tools. “Job done”, “I did it myself”, and “I understand how it works” are three qualities that are often undervalued when “real programmers” look at the work of “citizen programmers”. I say this as someone who loves and makes a living at “real programming”. We need more not less sub-real programming.
- OzCrimson 5y ago
  WOW! Excellent perspective. And I've never heard the term "citizen programmer" before. You're right. "I did it myself" and "I understand how it works" are definitely undervalued. And that plays into a lot of the empowerment/disempowerment conversation. I had a client who would have me build prototypes in Excel, then he'd hand them over to his in-house development team. I asked him why he uses me in the middle. He explained that he can guide me and kinda understand what I'm doing, and we can test and tweak formulas really easily. He can stop me and ask questions if I start doing something that seems wrong. Then he said, "but, when my devs open that code editor, I don't know what the hell I'm looking at." That was a different kind of disempowerment that he felt vis-a-vis his own devs.

bla15e 5y ago

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

shrubby 5y ago

Nice perspective. Thanks.

kull 5y ago

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.