7 ms·

Banks do crazy stuff with excel. I embedded a higher order functional language natively in excel 10 years back.... http://cufp.org/2009/fmd-functional-develop

Banks do crazy stuff with excel. I embedded a higher order functional language natively in excel 10 years back.... http://cufp.org/2009/fmd-functional-development-excel.html http://cufp.org/2009/fmd-functional-development-excel.html

tluyben2 8y ago
Banks, insurers, accountants; I know a company with 100M euro rev per year that runs entirely on Excel with VBA. Their office car park gate is opened, closed and managed with Excel. It sounds crazy but the CTO is a cofounder and he found it is much cheaper to just do everthing that way. They have been running like that for over 20 years.
- 4thaccount 8y ago
  It isn't the language for everything, but I've found you can do so much with a spreadsheet and a little VBA. Optimization, graphics, math...whatever. I also figure the company you refer to has some real benefit by focusing on one technology everyone knows. But how and the heck do they manage a gate in a spreadsheet?
  - tluyben2 8y ago
    I know about it because my friend (son of the ceo) provided the electronics for the gate and he showed me all the software in 1999 because we were pitching a rewrite in ‘web tech’ (my go to was Perl in those times). The gate software wrote to the parallel port via the win32 api to open and close the gate after the day/night watch entered the numberplate and name into the sheet.
    - 4thaccount 8y ago
      That is indeed pretty cool. I wonder how the system works now.
      - tluyben2 8y ago
        Last I heard (few years ago) nothing changed; just more was added in the same way. If it ain’t broke...
  - wenc 8y ago
    I've done some pretty mindbending things with VBA on Excel. They work but they are not pretty and not easy to maintain. VBA is technically a "complete" language (I want to say Turing-complete but that is not a meaningful trait), so it is possible to do a lot with it, but one ends up having to re-implement (sometimes badly) stuff found in other languages in order to write the main parts of the code. Part of what makes VBA deceptively easy is the control over the interactive elements of Excel (a lot of stuff is done with the Range object), but unfortunately that also introduces state that you can't always control, which entails write extra checks to make sure the state is correct before proceeding. This is especially true if your users are on different versions of Excel (I once wrote something in 2010 that doesn't work in 2016). There are now other options like QueryStorm [1], which lets you write C# in Excel and connect to SQL databases. There are also a bunch of Python-Excel solutions that are based on manipulating COM objects, but I've learned that when dealing with a Microsoft stack, there are advantages to using Microsoft-native languages like C#. Coming back to the article, it mentions adding arrays, vectors, and records to Excel itself; this will make Excel much more powerful because it has traditionally been a cell-based computation system, which has disadvantages that higher-level abstractions overcome (like vectors and tables). It also mentions writing Excel functions in a first class manner instead of relying on a separate procedural language like VBA. Operations on arbitrary sized arrays will also help it transcend Excel's issue of operating on fixed size arrays -- this will clean up a lot of very messy formulas. These developments will be interesting to watch, because it brings Excel much closer to a true functional computing system, and gets closer to Quantrix [2] territory. [1] https://www.querystorm.com/ https://www.querystorm.com/ [2] Quantrix is a multidimensional spreadsheet, and a commercial successor to Lotus Improv.
    - vba 8y ago
      "but I've learned that when dealing with a Microsoft stack, there are advantages to using Microsoft-native languages like C#." curious. can you elaborate?
      - raddan 8y ago
        You can add custom functions to Excel (e.g., the ribbon) and other Office apps using Visual Studio Tools for Office (VSTO). There’s almost nothing you can’t do once you have C# or other .NET languages in the mix (I like to use F#). One of my collaborators (Ben Zorn) is one of the people featured in the linked article. We’ve been working on visual debuggers (and other things) for non programmers in Excel for a few years now.
- z3t4 8y ago
  I think software is often more complicated then it needs to be. I used to write a lot of vbScript, but now I use Node/JavaScript whenever I need to glue something together. And there are many modules in Node.JS that you can glue together in order to do something useful. I find it even easier then vbScript/Excel/VBA!
  - tluyben2 8y ago
    I am (as a matter of principle) only working on projects which need to run for 10+ years or cannot be upgraded (firmware for very constrained devices) so yes, software is often far more complicated than it needs to be, but writing it in VBA or JS is not helping imho. Carefully designing, typing and formally proving (parts) is what makes me have applications running on cheap servers with basically 10+ years uptime (besides OS security updates) that give me income without work. I run software (SAAS) more complicated and with more clients on one 15 euro per month server than many run on complex aws setups with node/js/mongo. Also with higher uptime; although aws etc can give you 100% uptime, the quality of most software is so bad that it goes down. Ofcourse maybe you are different and take care to make things robust; that is not the common vba/nodejs hacker though in my experience.
    - z3t4 8y ago
      Managing complexity is not so much about the language, it's more about software development process. But I think it helps to use a safe and simple language. Engineers often forget about their own cost.
    - chris1993 8y ago
      I'm fascinated. What sort of software needs to run with ten years uptime? How do you avoid downtime for operating system updates on those cheap servers?
      - jacquesm 8y ago
        Telco, HVAC, message switches, various embedded systems, alarm systems and so on.
- ramraj07 8y ago
  One possibility is this - the people who mess with Excel VBA are generally very smart folk who just never went too techy, but then got really good at Excel and just learned vba as the next logical step. That means the code might not be kosher but it will be thoughtfully written and encompassing all the practical use cases. Contrast that with a 20something can grad who while competent isn't as smart as that non tech guy, and has indoctrinated the cs way of doing things w.r.t. test coverage and unit tests but often will lack the insight of what the program is actually trying to do. Frankly I would also like to choose the former than the latter.
  - chii 8y ago
    I challenge you to make changes to a spreadsheet or VBA program that isn't using modern programming methods like unit testing and version control. They would turn into a huge mess unless the coder is very diligent and understand every part. CS 'indoctrination' is not a failure of education. Surgeons have been 'indoctrinated' to know pre-op procedures.
    - ramraj07 8y ago
      CS indoctrination is most definitely not a failure of education. Mediocre kids thinking they are writing good code because they've written unit tests is. EDIT: Let me explain my thinking a bit more - we can try to categorize software development into two categories: 1. People write messy, unorganized code that is often in the head of just one or a few folks, who are smart but unorganized and often without formal CS training. The code often has almost no tests and doesn't follow any of the standard best practices of software engineering. The code is often impossible to pass down to new people, often ending up forcing the new people to redo a large fraction of the work. 2. People write clean, modular, testable code with good unit and integration tests, a robust build framework, etc. The code is written in a manner thats super easily transferable, most devs don't even have to understand the entire codebase to start meaningfully contributing. Obviously, the second category is the preferred category. A good SDE with a CS bg should follow (and often do follow) the second method. However, category 2 could, at least from my experience, be split into two sub-categories: 2A. The framework for both the code and the dev ecosystem was laid down by (often just one) really good engineers who think through what the problem really is and make sure that the fundamental structure and architecture of the codebase works towards solving that real world problem. This kind of code is absolute pleasure to work with and extend. 2B. The framework and the majority of the code are written by average engineers; often the first few eng hires in the company put the groundwork and make poor design choices and the engineering team that follows never wants to change anything fundamental because that's "tech debt" which the company can never afford to take a step back and look at. The average engineers have a good heart, but often their test cases never test real-world edge cases, they often don't even remember the architecture of the code they themselves wrote a few months back, and the code breaks all the time. Furthermore, the engineers would generally balk at adding any new feature because the codebase is fundamentally evolved into something that just cannot be extended without significant rework, and often they cant even see how they can rework it to add the required feature. In the end, good SDE practices and testing doesn't do shit if the person who wrote it didn't think hard enough. Here again, 2A is the preferred method of doing dev, but unfortunately, the thoughtful smart SDEs aren't that many, and would often be found in a well-paid job in a big company. Most regular devs can't step up to that level and the end result is 2B happens. Now the question is, which is the better of the two evils, if they are the only choices? 1 or 2B? I'd choose 1. My experience has been that while the scrappy code is unmaintenable by anyone new, at the least the guys who wrote it (assuming you can keep them long enough) will at least own up to it and make sure it keeps running, and they can at least try (and practically, generally succeed) to ship a new feature as opposed to the 2B case where often the categorical answer would be no.
- vba 8y ago
  I'm a dev on XL at Microsoft and took a trip a few years back to meet some of our advanced users. I was blown away by their 'accidental' CS education through Excel. They built sheets (debuggers) to debug other sheets (programs) on their OS (excel), and did this not even using VBA. In my own time, I've visited manufacturing plants where I've seen customers send the factory a spreadsheet containing nothing but a VBA project to exercise a (hardware) test jig for quality control. This just reminded me that when I was young, I couldn't afford VB5/6, so I would program in VBA in the office products.
  - lph 8y ago
    I used to work in the aerospace industry. The engineers had spreadsheets that consisted of three visual elements: Cells for the input and output filenames, and a "Run" button. It had once been company policy that engineers didn't have compilers or other programming tools on their computers (because "we have programmers for that"). But engineers are a resourceful lot, and they did have Excel with VBA.
    - cm2187 8y ago
      I still find it useful to use Excel as a GUI. I created all sorts of models that takes lots of configuration tables as inputs. There is no faster UI for someone to fill and modify a table than Excel, so all configuration files are excel spreadsheets. On the “we have programmers for that”, I see another culprit appearing: IT security, who decided unilaterally that everone else in the 100k people organisation only needs Office and are trying to impose security solutions based on that assumption. That includes application whitelisting, or automatically encrypting all office documents (as a result non office programs can’t consume spreadsheets anymore), etc.
    - m0zg 8y ago
      >> because "we have programmers for that" I hope it was long time ago. That's the most idiotic policy I've ever heard. :-)
- mandeepj 8y ago
  If you can do something, it does not mean you "should" do it
tedmiston 8y ago
This is really interesting. I wonder if some people that did this have migrated towards hybrid models? Particularly I'm thinking of something like Jupyter notebooks where charts, tables, and code can live side by side and be iteratively developed in a really tight REPL feedback loop as well.
- deleted 8y ago
  [deleted]
stcredzero 8y ago
This sounds like a joke, but I actually met a guy in the early 90's who wrote a Quicken/Microsoft Money competitor as a series of VB Excel macros, who then joined in on the Microsoft anti-trust suit, when Microsoft made moves to try and squash him.
- quickthrower2 8y ago
  YNAB started off as a spreadsheet for sale.
clausok 8y ago
Crazy stuff indeed. I saw a type of "click-once", auto-update deployment for vba code at an IBank in 2000 that worked wonderfully. Excel models built by trading desks and investment banking groups themselves are often constructed under a lot of time pressure and one thing about Excel is you can do it wrong and it may still work reasonably well. I have seen some specialty Excel software teams in these environments that refactored those models and made them remarkably robust and coherent. Much of it is just having the time to study alternatives: what part of the model should be on the sheet in formulas, what part in vba, what part in another language via an xll? What questions are the users going to ask and what calculations do they need to be able to see and understand? Where does the flexibility need to be? If a user moves a sheet to another workbook, will the button on it still work? How do you deploy vba code so that if you fix a bug all the models that use that vba code, and all their 'save as' descendants, run the fixed code. There are clever ways to answer these questions but if you condescend to Excel as a development platform, and believe you can't build anything great with it, you may never brainstorm enough to uncover them.
_the_inflator 8y ago
Consider every excel sheet as an app and you are in business. ;)

tluyben2 8y ago

Banks, insurers, accountants; I know a company with 100M euro rev per year that runs entirely on Excel with VBA. Their office car park gate is opened, closed and managed with Excel. It sounds crazy but the CTO is a cofounder and he found it is much cheaper to just do everthing that way. They have been running like that for over 20 years.

4thaccount 8y ago
It isn't the language for everything, but I've found you can do so much with a spreadsheet and a little VBA. Optimization, graphics, math...whatever. I also figure the company you refer to has some real benefit by focusing on one technology everyone knows. But how and the heck do they manage a gate in a spreadsheet?
- tluyben2 8y ago
  I know about it because my friend (son of the ceo) provided the electronics for the gate and he showed me all the software in 1999 because we were pitching a rewrite in ‘web tech’ (my go to was Perl in those times). The gate software wrote to the parallel port via the win32 api to open and close the gate after the day/night watch entered the numberplate and name into the sheet.
  - 4thaccount 8y ago
    That is indeed pretty cool. I wonder how the system works now.
    - tluyben2 8y ago
      Last I heard (few years ago) nothing changed; just more was added in the same way. If it ain’t broke...
- wenc 8y ago
  I've done some pretty mindbending things with VBA on Excel. They work but they are not pretty and not easy to maintain. VBA is technically a "complete" language (I want to say Turing-complete but that is not a meaningful trait), so it is possible to do a lot with it, but one ends up having to re-implement (sometimes badly) stuff found in other languages in order to write the main parts of the code. Part of what makes VBA deceptively easy is the control over the interactive elements of Excel (a lot of stuff is done with the Range object), but unfortunately that also introduces state that you can't always control, which entails write extra checks to make sure the state is correct before proceeding. This is especially true if your users are on different versions of Excel (I once wrote something in 2010 that doesn't work in 2016). There are now other options like QueryStorm [1], which lets you write C# in Excel and connect to SQL databases. There are also a bunch of Python-Excel solutions that are based on manipulating COM objects, but I've learned that when dealing with a Microsoft stack, there are advantages to using Microsoft-native languages like C#. Coming back to the article, it mentions adding arrays, vectors, and records to Excel itself; this will make Excel much more powerful because it has traditionally been a cell-based computation system, which has disadvantages that higher-level abstractions overcome (like vectors and tables). It also mentions writing Excel functions in a first class manner instead of relying on a separate procedural language like VBA. Operations on arbitrary sized arrays will also help it transcend Excel's issue of operating on fixed size arrays -- this will clean up a lot of very messy formulas. These developments will be interesting to watch, because it brings Excel much closer to a true functional computing system, and gets closer to Quantrix [2] territory. [1] https://www.querystorm.com/ https://www.querystorm.com/ [2] Quantrix is a multidimensional spreadsheet, and a commercial successor to Lotus Improv.
  - vba 8y ago
    "but I've learned that when dealing with a Microsoft stack, there are advantages to using Microsoft-native languages like C#." curious. can you elaborate?
    - raddan 8y ago
      You can add custom functions to Excel (e.g., the ribbon) and other Office apps using Visual Studio Tools for Office (VSTO). There’s almost nothing you can’t do once you have C# or other .NET languages in the mix (I like to use F#). One of my collaborators (Ben Zorn) is one of the people featured in the linked article. We’ve been working on visual debuggers (and other things) for non programmers in Excel for a few years now.
z3t4 8y ago
I think software is often more complicated then it needs to be. I used to write a lot of vbScript, but now I use Node/JavaScript whenever I need to glue something together. And there are many modules in Node.JS that you can glue together in order to do something useful. I find it even easier then vbScript/Excel/VBA!
- tluyben2 8y ago
  I am (as a matter of principle) only working on projects which need to run for 10+ years or cannot be upgraded (firmware for very constrained devices) so yes, software is often far more complicated than it needs to be, but writing it in VBA or JS is not helping imho. Carefully designing, typing and formally proving (parts) is what makes me have applications running on cheap servers with basically 10+ years uptime (besides OS security updates) that give me income without work. I run software (SAAS) more complicated and with more clients on one 15 euro per month server than many run on complex aws setups with node/js/mongo. Also with higher uptime; although aws etc can give you 100% uptime, the quality of most software is so bad that it goes down. Ofcourse maybe you are different and take care to make things robust; that is not the common vba/nodejs hacker though in my experience.
  - z3t4 8y ago
    Managing complexity is not so much about the language, it's more about software development process. But I think it helps to use a safe and simple language. Engineers often forget about their own cost.
  - chris1993 8y ago
    I'm fascinated. What sort of software needs to run with ten years uptime? How do you avoid downtime for operating system updates on those cheap servers?
    - jacquesm 8y ago
      Telco, HVAC, message switches, various embedded systems, alarm systems and so on.
ramraj07 8y ago
One possibility is this - the people who mess with Excel VBA are generally very smart folk who just never went too techy, but then got really good at Excel and just learned vba as the next logical step. That means the code might not be kosher but it will be thoughtfully written and encompassing all the practical use cases. Contrast that with a 20something can grad who while competent isn't as smart as that non tech guy, and has indoctrinated the cs way of doing things w.r.t. test coverage and unit tests but often will lack the insight of what the program is actually trying to do. Frankly I would also like to choose the former than the latter.
- chii 8y ago
  I challenge you to make changes to a spreadsheet or VBA program that isn't using modern programming methods like unit testing and version control. They would turn into a huge mess unless the coder is very diligent and understand every part. CS 'indoctrination' is not a failure of education. Surgeons have been 'indoctrinated' to know pre-op procedures.
  - ramraj07 8y ago
    CS indoctrination is most definitely not a failure of education. Mediocre kids thinking they are writing good code because they've written unit tests is. EDIT: Let me explain my thinking a bit more - we can try to categorize software development into two categories: 1. People write messy, unorganized code that is often in the head of just one or a few folks, who are smart but unorganized and often without formal CS training. The code often has almost no tests and doesn't follow any of the standard best practices of software engineering. The code is often impossible to pass down to new people, often ending up forcing the new people to redo a large fraction of the work. 2. People write clean, modular, testable code with good unit and integration tests, a robust build framework, etc. The code is written in a manner thats super easily transferable, most devs don't even have to understand the entire codebase to start meaningfully contributing. Obviously, the second category is the preferred category. A good SDE with a CS bg should follow (and often do follow) the second method. However, category 2 could, at least from my experience, be split into two sub-categories: 2A. The framework for both the code and the dev ecosystem was laid down by (often just one) really good engineers who think through what the problem really is and make sure that the fundamental structure and architecture of the codebase works towards solving that real world problem. This kind of code is absolute pleasure to work with and extend. 2B. The framework and the majority of the code are written by average engineers; often the first few eng hires in the company put the groundwork and make poor design choices and the engineering team that follows never wants to change anything fundamental because that's "tech debt" which the company can never afford to take a step back and look at. The average engineers have a good heart, but often their test cases never test real-world edge cases, they often don't even remember the architecture of the code they themselves wrote a few months back, and the code breaks all the time. Furthermore, the engineers would generally balk at adding any new feature because the codebase is fundamentally evolved into something that just cannot be extended without significant rework, and often they cant even see how they can rework it to add the required feature. In the end, good SDE practices and testing doesn't do shit if the person who wrote it didn't think hard enough. Here again, 2A is the preferred method of doing dev, but unfortunately, the thoughtful smart SDEs aren't that many, and would often be found in a well-paid job in a big company. Most regular devs can't step up to that level and the end result is 2B happens. Now the question is, which is the better of the two evils, if they are the only choices? 1 or 2B? I'd choose 1. My experience has been that while the scrappy code is unmaintenable by anyone new, at the least the guys who wrote it (assuming you can keep them long enough) will at least own up to it and make sure it keeps running, and they can at least try (and practically, generally succeed) to ship a new feature as opposed to the 2B case where often the categorical answer would be no.
vba 8y ago
I'm a dev on XL at Microsoft and took a trip a few years back to meet some of our advanced users. I was blown away by their 'accidental' CS education through Excel. They built sheets (debuggers) to debug other sheets (programs) on their OS (excel), and did this not even using VBA. In my own time, I've visited manufacturing plants where I've seen customers send the factory a spreadsheet containing nothing but a VBA project to exercise a (hardware) test jig for quality control. This just reminded me that when I was young, I couldn't afford VB5/6, so I would program in VBA in the office products.
- lph 8y ago
  I used to work in the aerospace industry. The engineers had spreadsheets that consisted of three visual elements: Cells for the input and output filenames, and a "Run" button. It had once been company policy that engineers didn't have compilers or other programming tools on their computers (because "we have programmers for that"). But engineers are a resourceful lot, and they did have Excel with VBA.
  - cm2187 8y ago
    I still find it useful to use Excel as a GUI. I created all sorts of models that takes lots of configuration tables as inputs. There is no faster UI for someone to fill and modify a table than Excel, so all configuration files are excel spreadsheets. On the “we have programmers for that”, I see another culprit appearing: IT security, who decided unilaterally that everone else in the 100k people organisation only needs Office and are trying to impose security solutions based on that assumption. That includes application whitelisting, or automatically encrypting all office documents (as a result non office programs can’t consume spreadsheets anymore), etc.
  - m0zg 8y ago
    >> because "we have programmers for that" I hope it was long time ago. That's the most idiotic policy I've ever heard. :-)
mandeepj 8y ago
If you can do something, it does not mean you "should" do it

tedmiston 8y ago

This is really interesting. I wonder if some people that did this have migrated towards hybrid models? Particularly I'm thinking of something like Jupyter notebooks where charts, tables, and code can live side by side and be iteratively developed in a really tight REPL feedback loop as well.

deleted 8y ago
[deleted]

stcredzero 8y ago

This sounds like a joke, but I actually met a guy in the early 90's who wrote a Quicken/Microsoft Money competitor as a series of VB Excel macros, who then joined in on the Microsoft anti-trust suit, when Microsoft made moves to try and squash him.

quickthrower2 8y ago
YNAB started off as a spreadsheet for sale.

clausok 8y ago

Crazy stuff indeed. I saw a type of "click-once", auto-update deployment for vba code at an IBank in 2000 that worked wonderfully. Excel models built by trading desks and investment banking groups themselves are often constructed under a lot of time pressure and one thing about Excel is you can do it wrong and it may still work reasonably well. I have seen some specialty Excel software teams in these environments that refactored those models and made them remarkably robust and coherent. Much of it is just having the time to study alternatives: what part of the model should be on the sheet in formulas, what part in vba, what part in another language via an xll? What questions are the users going to ask and what calculations do they need to be able to see and understand? Where does the flexibility need to be? If a user moves a sheet to another workbook, will the button on it still work? How do you deploy vba code so that if you fix a bug all the models that use that vba code, and all their 'save as' descendants, run the fixed code. There are clever ways to answer these questions but if you condescend to Excel as a development platform, and believe you can't build anything great with it, you may never brainstorm enough to uncover them.

_the_inflator 8y ago

Consider every excel sheet as an app and you are in business. ;)