I've always held Spreadsheets as the most underrated low-code platform. Here's a paradigm that enables users to build calculating machines without actually coding, yet the recent trend is such that we try to build visual-blocks based programming tools and call it no-code, but all the user does is deal with visual blocks instead of (sometimes more efficient) text statements.
Also red[1] is an interesting programming language that connects its reactive paradigms to how spreadsheets work.
Data tools that don't play well with spreadsheets are just fighting their massive potential user base.
Why not build something that helps the poor bastards working in spreadsheets till 3am, rather than telling them 'oh if you only had a couple hundred hours to learn pandas (and command line, and a bit of linux, and maybe some network infrastructure classes), you would be fine!"
My favorite off-label use of spreadsheets is to compose parameterized SQL. Of course, if our data models were better there would be less of a need for parameterized SQL. :)
I am really curious about both sides of your comment, but I don't know where to start asking. How do you write parametrized SQL in a spreadsheet? And what is wrong with our data models? What even _are_ our data models?
If I understand good, the idea is to expand Excel-like Speradsheets to be an IDE/Platform on with build functional programming software.
I think this could be useful for quick prototyping/testing and really small application but for anything a little more complex the Spreadsheet IDE/Platform would be a limiting obstacle, not an helper comparing to learn a text file based coding language. In the past IBM tried to expand Excel formalizing functions with Lotus Improv with no market success (https://en.wikipedia.org/wiki/Lotus_Improv). A Lotus Improv clone is still used today with success but in a very small niche market (financial sector, see https://quantrix.com/)
In my experience, those that use Spreadsheets are not building full-fledge applications but instead more Jupyter/Notebook style "I ran these reports for my next meeting" sorta deal.
For that type of "MVP" or "I can throw it away and start over" deal, it seems like a _great_ tool.
I essentially had a spreadsheet driven CRM tool that could
Ping an internal data warehouse API
Run data validation
Generate email templates
Send emails via outlook integration
So some of that wasn't FP as it used VBA but all the validation and checks were done using vanilla functions and were therefore functional.
But you're right, until I figured out how abstract and actually program, VBA script was bound to report generation before I started hitting unmaintainable complexity
I wish there were a better bridge from spreadsheets to full-blown applications, though. I imagine some kind of tool where you have your prototype spreadsheet and click "Export to application", and a wizard walks you through naming your columns, which then get exported as named arrays/matrices in a declarative/functional dataflow language. You name your formulas, which become named functions. You mark some of the cells as inputs and others as outputs, which then kicks you over to a GUI wizard for laying out the end-user UI. Cells that aren't formulas, inputs, or outputs become named constants in your program. Maybe there's JSON REST, Protobuf gRPC, and Bloomberg market data API integration for inputs and outputs.
I've spent the past 10 years maintaining and improving a finance-centric lazily-evaluated dataflow language with integrated distributed NoSQL database for a Fortune 500 financial services company. My understanding is that most of our competitors use Excel where we use our language/database. Some of our competitors have hired away some of our developers to explicitly write more modern clones of our system. One competitor's clone uses Python with function decorators that inspect the function's AST and transform it into a reactive dataflow language, though I imagine it's very frustrating to write something that looks like Python with very non-Pythonic evaluation semantics. I think starting with something like Haskell or Prolog would be less jarring.
My wife does finance/accounting for another large multinational, and I see the appeal and power of both Excel and our system. I wish there were some easier way to migrate from spreadsheets to more scalable and maintainable systems. (And, I suppose, there's some utility in bidirectional migration.)
Absolutely beautiful! It's not immediately obvious to me how to import an Excel spreadsheet, but something like Observable or AirTable is where spreadsheets need to migrate. Lazily evaluated dataflow like Observable is the right paradigm.
wow it be crazy if you could embed a spreadsheet and trigger observable cells from embedded spreadsheet cells, giving you the opportunity to refactor out the logic from the spreadsheet slowly.
I think we should talk! :) From your description I know exactly where you work and which other Python based system you're talking about )
I have been in the industry for 20 years myself as a quant, mostly using Excel as a "glue" layer indeed, and I worked with the Python based "clone" you mentioned too.
For the last two years I have been working on a platform to fix exactly the pain points you are talking about.
Amazing how your post sounds almost like as if I wrote it myself before I started working on this project :)
Please, take a look at https://ellx.io
I would be more than happy to discuss it in detail: dmitry + at + ellxoft.com
> I've spent the past 10 years maintaining and improving a finance-centric lazily-evaluated dataflow language with integrated distributed NoSQL database for a Fortune 500 financial services company
I think you nail the fundamentals here. I also dream of a access+excel kind of full app but think need to start with the base language (that I doing at http://tablam.org). You need to align the base language + the rest of tool to make easier to scale up whatever the customers create (at least, you can copy paste whatever them wrote and debug it using the same tools as professionals, instead of reverse engineer the kirks of excel...)
Guy at support uses Excel to generate SQL statements.
Typically when we have a request like "oops, we accidentally entered wrong weights for these articles, please fix", where he gets an Excel sheet of article numbers and correct weights. But thanks to the flexible nature, toss in a little logic when needed for example, he can quickly churn out some non-trivial SQL to do the job.
Of course may other ways to do this kinda job, but for him this is quite productive. He starts by writing the query he needs for doing one update, then just stuff it into Excel, replacing fixed values with cell references and copy formula down all rows and copies result back into interactive SQL editor.
The simplicity of the cells makes a vanilla spreadsheet very malleable. Higher-level modeling above the basic cell grid makes some things easier, introduces straightjackets as well, and real-world complexity that’s not handled well by the modeling paradigm makes for complex problems instead of simple problems.
Improv looked cool and made it easy to have horrible calculation errors. I had such fun demoing a comparison to our multidimensional spreadsheet at trade shows. Lotus also couldn’t handle internal threats to 1-2-3 so Improv was never going to go far.
The slides were really fun (I like the charm, and slides with moving images always reminds me of the good times in computer lab)
Can someone correct me, but isnt slide 22 replaced with the ability to just simply change the sheet to a table, and it will auto fill no matter what so you can just change the first cell's formula and it will change the rest of the column?
All good ideas. I doubt this dates from 2014. 2004 perhaps. I can see that the Excel team ignored those suggestions like pretty much any other suggestion to improve the product. Focusing instead on more fundamental problems like changing the color of the user interface or the location of the buttons between versions.
If there were a way to tag cells in a spreadsheet such that input and output cells were known, refactoring tools could be brought to bear that aren't otherwise possible.
Imagine a variant of Basic that did that... like VB/Excel, but far far better. I like the possibilities this opens.
Explicitly tagging cells as I/O would remove all ambiguity that otherwise has to exist in any given spreadsheet. Knowing what could/couldn't me moved by tools opens a lot of possibilities. In fact, you could remove all non-IO cells to a list of formulae, to be editable in an IDE.
I love Excel and the combination of data, functions and custom code. But in 2020 VBA is a joke, surely we can have C# or similar in a spreadsheet already?
I’m a huge fan of spreadsheets as config files due to their ability to compute values from formulae.
I currently do this in F# using ExcelProvider, which allows you to access computed values and types at design/compile time.
This is much handier than using json/yaml/xml etc., again due to formulae, but also the use of Excel/LibreOffice as a config editor. Additionally being able to have multiple spreadsheets within a workbook is a nice way of organizing your configuration data. Beats the pants off other serialized data formats (depending on your use case) and I find it to be preferable to using ‘code as configuration’, especially because non-developer users can edit it.
Spreadsheets are awesome! They're extremely easy to use databases and make computing data really easy. I wish they were even more programmable than they already are. A map function would be nice...
= MAP(A1:A, A + 10)
This would be so much nicer compared to a huge mess of ARRAYFORMULAs I'm using right now.
The ability to store multiple values in one cell like a dictionary would be awesome as well. With that I wouldn't have to implement lookup manually.
Take a look at [0] for a proof of concept how in the future it could be possible to define custom functions inside excel not via textual langauge but via tables inside the sheet.
I work in mega project engineering (O&G, mining, etc.) where it is absolutely frightening how much data is generated via cobbled-together spreadsheets with unverifiable calculations and no change tracking. Excel is a nightmare to me.
Different approaches for different aspects. For discipline calculation sheets, there is not much other than having a basic file-level version control and validation engines for importing the data. For other areas, I have built pipelines that maintain diff-able JSON in a repo and handle converting to-from a spreadsheet for their 'UI'. We are also always using or building integrated solutions for data and calculation capturing. My personal push is to introduce discipline engineers to things like Jupyter notebook for "hand" calculations.
I see these “everyone gush about how great excel is” threads and I always recoil in horror and go in to see if there’s anyone who agrees with my perplexingly minority viewpoint.
For trivial things and certain specific use cases spreadsheets are fine. But it never is left at that. For lack of awareness of any other programming language they get used for programming and analytics platforms and turn into byzantine, unmaintainable, undocumented beasts with hard to read and check formulas and business logic.
My current company is plagued by these built by no-coders over decades. There’s teams of people whose primary job function is to click copy and paste between these to generate regular reports. Excel is a curse.
excel always felt more like a really bad smalltalk machine to me than functional programming, but maybe OOP and FP are more similar than we'd like to admit
41 comments
[ 2.9 ms ] story [ 92.6 ms ] threadAlso red[1] is an interesting programming language that connects its reactive paradigms to how spreadsheets work.
[1] https://www.red-lang.org/2016/07/native-reactive-spreadsheet...
Basically "low code" solutions are meant to improve upon the spreadsheet and fill the gap between that and software.
So "low code" has been with us since the 1980's.
https://en.wikipedia.org/wiki/VisiCalc
Data tools that don't play well with spreadsheets are just fighting their massive potential user base.
Why not build something that helps the poor bastards working in spreadsheets till 3am, rather than telling them 'oh if you only had a couple hundred hours to learn pandas (and command line, and a bit of linux, and maybe some network infrastructure classes), you would be fine!"
For that type of "MVP" or "I can throw it away and start over" deal, it seems like a _great_ tool.
I essentially had a spreadsheet driven CRM tool that could
Ping an internal data warehouse API Run data validation Generate email templates Send emails via outlook integration
So some of that wasn't FP as it used VBA but all the validation and checks were done using vanilla functions and were therefore functional.
But you're right, until I figured out how abstract and actually program, VBA script was bound to report generation before I started hitting unmaintainable complexity
I've spent the past 10 years maintaining and improving a finance-centric lazily-evaluated dataflow language with integrated distributed NoSQL database for a Fortune 500 financial services company. My understanding is that most of our competitors use Excel where we use our language/database. Some of our competitors have hired away some of our developers to explicitly write more modern clones of our system. One competitor's clone uses Python with function decorators that inspect the function's AST and transform it into a reactive dataflow language, though I imagine it's very frustrating to write something that looks like Python with very non-Pythonic evaluation semantics. I think starting with something like Haskell or Prolog would be less jarring.
My wife does finance/accounting for another large multinational, and I see the appeal and power of both Excel and our system. I wish there were some easier way to migrate from spreadsheets to more scalable and maintainable systems. (And, I suppose, there's some utility in bidirectional migration.)
Exactly the example I was looking for! Mind if I re-implement all this on the Ellx platform (https://ellx.io)?
I have been in the industry for 20 years myself as a quant, mostly using Excel as a "glue" layer indeed, and I worked with the Python based "clone" you mentioned too.
For the last two years I have been working on a platform to fix exactly the pain points you are talking about.
Amazing how your post sounds almost like as if I wrote it myself before I started working on this project :)
Please, take a look at https://ellx.io I would be more than happy to discuss it in detail: dmitry + at + ellxoft.com
I think you nail the fundamentals here. I also dream of a access+excel kind of full app but think need to start with the base language (that I doing at http://tablam.org). You need to align the base language + the rest of tool to make easier to scale up whatever the customers create (at least, you can copy paste whatever them wrote and debug it using the same tools as professionals, instead of reverse engineer the kirks of excel...)
Typically when we have a request like "oops, we accidentally entered wrong weights for these articles, please fix", where he gets an Excel sheet of article numbers and correct weights. But thanks to the flexible nature, toss in a little logic when needed for example, he can quickly churn out some non-trivial SQL to do the job.
Of course may other ways to do this kinda job, but for him this is quite productive. He starts by writing the query he needs for doing one update, then just stuff it into Excel, replacing fixed values with cell references and copy formula down all rows and copies result back into interactive SQL editor.
Improv looked cool and made it easy to have horrible calculation errors. I had such fun demoing a comparison to our multidimensional spreadsheet at trade shows. Lotus also couldn’t handle internal threats to 1-2-3 so Improv was never going to go far.
Can someone correct me, but isnt slide 22 replaced with the ability to just simply change the sheet to a table, and it will auto fill no matter what so you can just change the first cell's formula and it will change the rest of the column?
Imagine a variant of Basic that did that... like VB/Excel, but far far better. I like the possibilities this opens.
I hope that makes sense.
Microsoft is instead steadily expanding JS scripting support. They even added JS UDF recently.
I currently do this in F# using ExcelProvider, which allows you to access computed values and types at design/compile time.
This is much handier than using json/yaml/xml etc., again due to formulae, but also the use of Excel/LibreOffice as a config editor. Additionally being able to have multiple spreadsheets within a workbook is a nice way of organizing your configuration data. Beats the pants off other serialized data formats (depending on your use case) and I find it to be preferable to using ‘code as configuration’, especially because non-developer users can edit it.
EDIT: Link to ExcelProvider http://fsprojects.github.io/ExcelProvider/
The ability to store multiple values in one cell like a dictionary would be awesome as well. With that I wouldn't have to implement lookup manually.
[0]: https://www.youtube.com/watch?v=jH2Je6wUvPs
trying to carve a niche out of the orange territory on slide 12 as well
What kind of tools are you currently using to manage the nightmare?
For trivial things and certain specific use cases spreadsheets are fine. But it never is left at that. For lack of awareness of any other programming language they get used for programming and analytics platforms and turn into byzantine, unmaintainable, undocumented beasts with hard to read and check formulas and business logic.
My current company is plagued by these built by no-coders over decades. There’s teams of people whose primary job function is to click copy and paste between these to generate regular reports. Excel is a curse.
[1] http://simplysomethings.de/functional+programming/calc.html