In his recent paper Spreadsheet functional programming, Dave Wakeling discussed the idea of using a functional programming language from within a spread sheet.
It’s an interesting approach, because the implementation effetely allows you to write code (in this case Haskell) in cell comments, in such a way that the results are computed on the fly – i.e. not complied.
As you might expect, there are a number of issues with the implementation from a practical stand point, but that’s not what I’m interested in here – I want to talk about the concept of functional programming in relation to spread sheets, which this paper also focuses on.
It’s my view that a spreadsheet is a functional programming language. This means that I have an issue with the goal of the paper, which is:
“Our hope is that by doing so, we might get spreadsheet programmers to give functional programming a try”
Nothing wrong with that, but my views that spread sheet users already are functional programmers!!! So why them might we want to use Haskell with Excel? Towards the end of the paper Wakeling suggest some possible strengths to this approach.
1. It uses an ordinary spreadsheet. The functionality of, experience with, and Support for a familiar product all carry over.
2. It uses an ordinary functional language. The benefits of such languages also carry over. (Backus, 1978; Turner, 1982; Hughes, 1989).
3. It can accommodate any pace of change. The old way of doing things can coexist with the new one.
Unfortunately I don’t agree that these are real benefits. Oh dear! – Why?
1. This is a case for anything that integrates with Excel; the benefit is independent of what it is that’s being integrated. This is the benefit of using a spread sheet and a programming language over using a programming language on it own. It’s tenuous to claim it as a strength.
2. The benefits are true of Functional v’s Imperative programming languages (lasy, high order functions). Since Excel functions operate in a very similar way to functional programming these benefits don’t really exists between Excel worksheet functions and a functional programming language.
3. This statement is true of any situation where the 2 technologies can co-exist you could say the same about using C, VSTO etc, etc, again it’s really tenuous to claim it as a strength of any like for like approach.
So if these strengths are not in fact all that real, then why would we want to use a functional programming langue with spreadsheets? Perhaps a clue is given in this passage:
“On the positive side, many everyday programming errors are detected that would otherwise lead to incorrect results, including dangling cell references, undefined functions, and functions with the wrong type of arguments.”
I can see how using a more “strongly typed” language could possibly help reduced type errors, not sure how undefined functions would be helped or how “dangling cell references” (in fact, I don’t know what this means at all) would be effected. Generally further discussion and justification of the benefits of using a functional language with a spreadsheet are needed to build a case for doing so.
The paper did not convince me that using a functional language has any really benefits to using worksheet functions or any other programming language. Having said that, the paper does show that its possible to link technologies like this and it’s another interesting approach to programming spreadsheets. The prospect of F# and XLDNA and F# in VSTA/O are also interesting things to start thinking about! The application of functional programming languages within spreadsheets is interesting and Wakeling should be congratulated for opening up the debate for future discussion– as hopefully this post will do too!
Interesting thoughts! I started learning F# recently (my first foray in functional) and remember thinking that an Excel spreadsheet was a (crude) functional program… I am now trying to imagine how a spreadsheet with recursive functions would look like :)
I am assuming that dangling references must be dead code, i.e. pieces of the spreadsheet that are not referenced by anybody? But then, that would include all the “decorative” (titles…) parts, too.
Hi Mathias,
How did you find F#?
recursive functions, I guess that would be a circular referance of some sort?
thanks
Ross
Hi Ross,
I am still a beginner at F#, but I am enjoying it quite a bit. The initial learning curve is steep, it’s a big paradigm change from OO languages (I write mostly C#). For instance, typically in functional style a variable is immutable; once evaluated, you can’t change its value. So classics like “for i = 0; i < 10; i++" are just gone, because you can't modify i. Usually, you end up using lots of recursion, functions which call themselves until a termination condition is attained.
I simplify a lot here – I emphasized hurdles, but it's part of the fun: some complicated things in C# take a line in F#, and vice-versa, it's just a very different take on code, and a great mind-bender. And functional code resembles maths much more than OO, so if you are into math, it's worth looking into it.
As for Excel being functional, it has some of its aspects: each cell is evaluated once, and when changed, the whole book is evaluated. The missing aspect is composing functions. Which is why I brought up recursion, because it would require to declare a function in the worksheet, and call that function in the same place it is declared – an extremely degenerate circular reference :)
Sorry for the long rant, I got a bit carried away here!
Hi,
That’s not a rant!!! ;-)
Interesting insight, I’ve not looked at F# myslef, but am hearing good things about it, it’s always good to hear stuff from people directly too, so thanks for the info!!!
Cheers
Ross
Ross – my reaction was much the same as yours – quite interesting, but I didn’t quite see the point.
Do you have a good introductory reference to functional programming that you can recommend? I know next to nothing about it (other than that code examples I’ve seen look very hard to read, to the untrained eye).
Answering my own question, the Wikipedia article on functional programming seems pretty good. The only problem is that I now need to get my head around “lamda calculus”.
Thinking about the Dave Wakeling article linked in the OP, this seems to be a good example of “spreadsheet cringe”; i.e. spreadsheets are toy applications, not suitable for real programmers, so if we want to do some real programming we need to link to a real programming language, rather than use the built in functionality, even if that means doing crazy stuff like putting code in cell comments, and reading and writing data to text files.
Some interesting comments here:
http://stackoverflow.com/questions/1954470/spreadsheets-that-use-a-functional-programming-language
Some links to what look like they would be interesting papers, but other than the Wakeling one they are all for purchase.
Also a link to:
http://roymacleanvba.wordpress.com/2009/08/04/domain-specific-languages-%e2%80%93-part-2/
which seems to have a fair bit of ovelap and is worth a read.
Doug, try…
http://www.itu.dk/~sestoft/papers/weuse-sestoft.pdf
ftp://ftp.cs.orst.edu/pub/burnett/ForJFP/JFP.final.ps.gz
The stack post is interesting thanks for the link.
>>I know next to nothing about it (other than
>>that code examples I’ve seen look very hard
>>to read, to the untrained eye).
Dito, but I can see why it would be good/makes sense.
thanks Doug,
Ross
I’ll butcher this attempt at explaining functional programming, but here goes…
Functional programming is mainly about computing with routines that *return* values instead of *changing* values. Think of the difference between a function that takes some parameters and returns some value based on them (and only them), and a subroutine that goes off and modifies some data structure declared elsewhere. You’ll see the words “pure” and “side effects” a lot in discussions of functional programming. A “pure” function is one that just returns a value, and has no other “side effects”.
It turns out that there are a lot of benefits to pure functions. Because they depend only on their inputs and not the general state of the rest of the world, you can reason about them more easily. When you’re evaluating an expression, you can substitute a function’s return value in place of the call to the function. You can evaluate functions in any order (or in parallel) and be sure you get the same answer every time.
You can see the connection to spreadsheets now. Any given cell value depends only on the values of the cells it references because the formulas and all the functions they call when evaluated are pure. Side effects are forbidden. That’s essentially what makes it possible to build the spreadsheet engine that in the first place. (Of course, there are exceptions, such as functions that return random numbers, and you can write Excel UDFs that have side effects as long as they aren’t side effects that change any cell values, so spreadsheets aren’t entirely “pure”.)
Now, most functional programming systems actually have, you know, *functions*. (I make that point explicitly because Excel formulas make lousy functions, since you have to repeat them all over the place instead of calling them with parameters. The link to Roy MacLean’s VBA Blog that Doug posted is to a comment thread where I briefly described some work that some co-workers and I have been doing to make functions out of formulas in Excel.)
Not only is functional programming mainly about computing values, but typically in functional systems, *functions are values*. They can be passed to other functions, returned as values from functions, etc. It turns out this is amazingly useful. A good description of why can be found in the classic John Hughes paper “Why Functional Programming Matters” (which can be found here: http://www.cs.chalmers.se/~rjmh/Papers/whyfp.html). From the abstract: “As software becomes more and more complex, it is more and more important to structure it well. Well-structured software is easy to write, easy to debug, and provides a collection of modules that can be re-used to reduce future programming costs. Conventional languages place conceptual limits on the way problems can be modularised. Functional languages push those limits back. In this paper we show that two features of functional languages in particular, higher-order functions and lazy evaluation, can contribute greatly to modularity. … Since modularity is the key to successful programming, functional languages are vitally important to the real world.”
I think we can all see the connection to spreadsheets there – modularity is hard, but it’s almost impossible in raw Excel!
Since that comment on Roy’s blog last summer, we’ve expanded on the functions-as-values stuff (all implemented in VBA!) and have found it quite useful. I still hope to make the source code open source and blog about it or something but I never seem to find the time to start.
Regarding critiques of the Wakeling paper from an Excel perspective, I think Doug’s remark about “spreadsheet cringe” is right on.
(And no post about spreadsheets and functional programming is complete without a mention of Resolver One, a Python-based spreadsheet from Resolver Systems. You can program functionally in Python, and thus get the many advantages when building complex spreadsheet models. Alas for me, for the people we work with, “spreadsheet” means “Excel with no add-ins”…)
For a nice example of some spreadsheet functional programming in action, see Giles Thomas (of Resolver Systems) from just today:
http://www.gilesthomas.com/?p=271
The syntax is Python, but (as is often the case with functional programs and decently named functions) you probably don’t need to be able to code in Python to get roughly what’s going on.
Lots of food for thought there John. Sometimes I wish I didn’t have to earn a living and could devote all my time to doing interesting stuff :)
>>Sometimes I wish I didn’t have to earn a living and could devote all my time to doing interesting stuff :)
Dito!!!!
ditto ditto!
John,
Thanks for that explanation of functional programming. Using a familiar computing tool as an analogy for FP helps a lot.
[...] in Uncategorized by Scott on July 1, 2010 Methods in Excel caught my attention with this piece Spreadsheet functional programming which discusses the uthor responds to ideas from the “recent paper Spreadsheet functional [...]