Do you see an use for "Spreadsheet programming"? [closed]

Question

A while ago I stumbled upon the concept of using spreadsheets (I mean cells and formulas not Macro code) as a way to specify programming logic. The idea is:

• create a spreadsheet with a cleanly defined flow of calculations (which sometimes are better suited to the "dataflow" paradigm of spreadsheets instead of procedural or object oriented programming styles)

• define input cells

• define output cells

• compile the whole thing into a stand-alone executable class (or function, procedure, ...)

• use it in normal code within a broader software project

• use the spreadsheet as source code to be maintained over time

The idea is to use this technique for problems the really fit into the model and that this would lead to naturally well-documented and easy maintainable code. I am interested in knowing whether you have experienced using the technique and what for. An example application that came to my mind are insurance tariff calculators, that are typically drafted, created and validated by actuaries on Excel sheets and only later coded (it's a painful process) in some hard-to-maintain programming logic.

Answer 1

Though it is not precisely the style of "build a spreadsheet, compile it into code" you were asking about, the Cells dataflow extension to CLOS uses a similar model: That formulas expressing data flows and transformations be the "source material" / "representation of record" for object/class design. Consider it an alternative way to build what you were asking about.

And just for fun: spreadsheet macro

Answer 2

Ever since I read this article I have been thinking on and off about the concept. I think there is definitely a use for it.

One thing about optimizing such a thing, a spread sheet is very similar to memory space. It is also very similar to display and print space (as in x,y). Lots of benefits there too.

When you noted maintainability, that opened up alot of ideas for me. I don't know what is out there to compile with really and language features, libraries etc.. might be quite a pain. Still, there could be a future in it somewhere.

I have really only written VB scripts for spreadsheets, gradebooks and accounting "software" mostly. Never made executable apps out of anything spreadsheet based except excel file interface from a C++ app.

Answer 3

which sometimes are better suited to the "dataflow" paradigm of spreadsheets instead of procedural or object oriented programming styles

Honestly, I can hardly think of any real-world calculations where this applies. "Dataflow" programming can be easily done a lot of modern programming languages (look at LINQ in the .NET world, or the list processing operators of Perl and Python), and to my experience, that results in much more maintainable code than a bunch of "spreadsheet formulas" with hard-to-maintain cell references.

On the other hand, me and my colleagues have created lots of spreadsheet-based (MS-Excel, to be precise) applications, where Excel was used either as a user-friendly tool for entering input data / creating input masks very quickly, or for creating formatted output, or both. In all those cases, the calculation or processing of that data was not done (or only partially done) by Excel formulas, but by program code, since Excel formulas were not powerful enough or the completely wrong tool (and believe me, we have a lot of knowledge what is possible with Excel formulas and what is not).

Answer 4

Yes, however I think of it more as "spreadsheet testing" rather than "spreadsheet programming". For example, recently I was working on a feature for our project that involves performing a calculation on large numbers of database records. The calculation was relatively simple yet important and so needed particular testing attention. Furthermore, the formula we were using required a small amount of adaptation to be applicable to our situation.

I ran through some calculations by hand to write my unit tests, while the tester I was working with created a spreadsheet like you have described for use in his end-to-end tests. Due to the adaptation of the source formula we didn't have any independent test data to compare against, therefore the spreadsheet provided a kind of "double-entry bookkeeping" style check that gave us confidence that the code was correct.

So yes, I see this technique as being very useful as a source of test data for cases where the calculations involved are easy to implement in a spreadsheet but are otherwise required to be implemented in code. However, such a spreadsheet shouldn't be used to "specify programming logic" per se, but merely the required end result(s).

Answer 5

Spreadsheet "programming" is a type of dataflow programming.

We have a linguistic problem with it, we should not call it "programming", because it's far less than we call programming, but it's definitelly more than entering data to a program.

Dataflow programming is an architecture and discipline, where the application is a network of independent modules, they sends messages (data) each other. This model is not applicable to every problem, only for ones, where there is a source data or stream (or there're more), which goes trhu the processing network, and produces output data/streams. See list below.

Dataflow programming has several types, let's see some:

• Spreadsheet: input numbers are processed by formulas, then results numbers and graphs. Special characteristics: exectuion time is "one-shot", when input value (component) changes, the appropiate part of the processing graph re-runs and produces output.
• Unix pipe: the shell launches up several programs, and links stdout->stdin. Special characteristics: only pipe-style linking is allowed, the graph is a single queue.
• Synchronized execution: there is a clock, which triggers the processing of a frame or sample in a specified frequency. Every component runs once a clock cycle. Video and audio processing systems the examples, they works at a specified frame/sample rate.
• Asynchronous execution: the graph is in idle, until an external event occurs. Then it processes the event, generates some output (or not), and goes to idle state.

Back to your question: I think yes, it's good idea to publish a dataflow application as a standalone app. I have already made it. Twice.

Me and a friend of mine has created a prototype DF system for home automation. We have no graph editor, so the app is not editable by the user, some parameters are stored in a config file, but nothing else. We have a DF script language, which is "compiled" to C++ code (a list of component creation and message definitions), which gets compiled to a native executable. The modules are C++ classes (other classes, just to get some info about our system: Message, Dispathcer, Component (abstract), Port (abstract), ConsumerPort, ProducerPort).

Also, we were amazed of the advantages of a DF system provide: we've made serial sniffer app within 2 minute, or we've made a test program on-site, which blinks lamps one-by-one (there were no documentation on hardware IDs). We've created MIDI and joypad components just for fun, I've also made a light organ with it (see http://homeaut.com/under_construction/ ).

I can see only one difficulty in case of spreadsheets: as every number and formula (potentially: every cell) is a component, your graph is not final. When you add a line to your simple sum() app, it means that the dataflow graph is changed. So, you have to "reprogramming" the graph in run-time, or we should call it "metaprogramming". In Excel, a macro would do the job, but then we loose the purity of dataflow.

I have a not-too-bad-but-not-perfect solution. I've made a spreadsheet, an AJAX app with PHP backend. The vertical axis is time (days), the lines are components. There are components, like input (the line can be edited by user), vertical average, horizontal average/sum, and some domain-specific statistical calculations. There is only one problem with it: this is "one-dimensional". As long as I want just sum and avg and whatsoever, I can add new lines, and create the component, which calculates the stuff. But there is a strong constraint: the columns are always days (I've made week and month "views", which displays daily data as sum/avg, but it's still one-dimensional). I can't show it, it's collaborative and requires PHP backend task to run 7/24, it's not supported by my host provider.

So, my model (which can be best described as: "days horizontally") is not able to handle other kind of problems.

I have an idea, how to solve this problem: tabs.

When you get stuck in Excel, and have to create another table, you can use a distinct area on the same tab, or open another tab. Also, referencing between tabs is uncomfortable, I prefer first method. I think, tabs should be displayed on the same screen, like non-overlapping windows.

Every table should have its growing axis: vertical, horizontal or fixed. Vertical growing tables has line components (as my day-based spreadsheet), where all the columns have the "same" formula, horizontal components have column components, fixed-size tables are just like any spreadsheet.

So, when the user adds a new line/column, the new line/column will have the same formula.

Also, in spreadsheets, I hate the thing, that I need to copy very same formulas 1000 times, if I have 1000 lines. It's a source of bugs (keeping old version of formula in some lines), waste of memory (storing same formula 1000x).

Maybe I'm wrong, and there're concept bugs in this model, but I hope it was a good thought-provoking.

Answer 6

My thought would be to use the spreadsheet to define the logic for the calculations and while doing this try to setup the spreadsheet in such a way as to make it programming language friendly. By friendly I mean -> using name ranges/references instead of cellXY coordinates, and breaking down formula's into smaller pieces.