Spreadsheets functional programming for the masses Simon Peyton

Spreadsheets: functional programming for the masses Simon Peyton Jones Margaret Burnett Alan Blackwell

Q 1: What should a functional programmer in Microsoft Research do?

Q 1: What should a functional programmer in Microsoft Research do? A 1: Persuade developers to implement stuff in Haskell. No more C#! Haskell is better!

Q 1: What should a functional programmer in Microsoft Research do? A 1: Ask Q 2: What is the worlds most widely used functional language, by far?

l! Ex ce Q 1: What should a functional programmer in MSR do? Q 2: What is the worlds most widely used functional language, by far? Violent exothermic reaction

Spreadsheets are functional programs B 1 = A 1*A 1 C 1 = A 2*A 2 D 1 = B 1 -C 1 B 2 = A 1+A 2 C 2 = A 1 -A 2 D 2 = B 2*C 2 n n n Just a big bunch of equations No side effects Order of evaluation controlled by data dependencies

Q 3: What chance does a pointy-headed researcher have of influencing the direction of a Microsoft cash cow?

Q 3: What chance does a pointy-headed researcher have of influencing the direction of a Microsoft cash cow?

Excel’s market is tall 50 m end-user programmers End users Can use Excel Market size 2 m Programmers • Real job is engineering, teaching, financial; NOT programming • Use Excel formulae to build "models" • No need to "sell" functional programming: they are already doing it! 2 m “classic” programmers (write VB, C++, C#) Can use VB, C++

Not much 50 m End users Can use Excel Market size 2 m Programmers Can use VB, C++ 2 m “classic” programmers (write VB, C++, C#) Research effort expended

50 m Tall, but narrow When the task. . . § becomes large or complex § changes over time § rewards re-use § is mission-critical End users Market size . . . cells and formulas are not enough. Current solution: shift programming paradigm Use Excel + VB, C# 2 m Programmers Application requirements

50 m End users Our vision New territory to colonise Market size 2 m Programmers Application requirements Increase Excel’s “reach” by empowering end users to write “programs” without hiring programmers

Excel Research Simon Peyton Jones Functional programming Excel Plus inputs Margaret Burnett End user & Alan Blackwell visual software engineering Psychology of programming Ruthless design-time focus on usability, based on empiricallygrounded research.

Our target end users Of all Excel users n Some just use Excel for lists n Some can type very simple formulae e. g. =SUM(A 1: A 10) n Some use formulae, and understand copy-and-paste of formulae (absolute and relative cell references) n Some can use Visual Basic

Our target end users Of all Excel users n Some just use Excel for lists n Some can type very simple formulae e. g. =SUM(A 1: A 10) n Some use formulae, and understand copy-and-paste of formulae (absolute and relative cell references) n Some can use Visual Basic (professional programmers) This is our target audience. A minority of Excel users, but still extremely numerous

Market size 50 m How? 1. Functions as ordinary spreadsheets End users 2. First class array values 2 m Programmers Application requirements Two complementary ideas

Functions as ordinary spreadsheets

What's missing? B 1 = A 1*A 1 C 1 = A 2*A 2 D 1 = B 1 -C 1 B 2 = A 1+A 2 C 2 = A 1 -A 2 D 2 = B 2*C 2

Functions as ordinary spreadsheets Scenario n teacher types formula to compute student grade n copies and pastes down a column n (much later) wants to change the formula Problem n must alter many cells to implement a single change n impacts re-use, error-proneness, modularity Obvious solution (to a programmer) n Make a named function to encapsulate the formula

User is working on a formula

User brings up the right click menu Cut Copy Paste … Make a function …

A new function is automatically created in a sheet and called Formula replaced by call to function New function worksheet

Now, fill down does not lose sharing Regular fill down

User can see/modify the function definition as desired

Functions as worksheets n Creating a function is fast n Understanding a function requires no new skills: no paradigm shift n Using a function improves quality Named abstraction is our primary weapon in the war against complexity. Imagine conventional programming with no procedures, only smart copy/paste!

Creating a function from scratch Build a worksheet to calculate the distance a ball will travel, when at a particular angle and velocity n Turn it into a function by identifying the input cells (a bit like “scenarios”, only callable) n Call the function many times to see the distance the ball goes for different throwing angles n

Debugging The “call tree” becomes a tree of linked worksheets, laid out in space, not in time. n So debugging is particularly easy. Need new mechanisms for navigating the plethora of worksheets, via the tree structure. n First year programming courses will be taught this way! n

Main program Calls Function Cyl. Vol Calls Function Circ. Area

Domain-specific libraries n Every domain (physics, electronics, statistics, financial, marketing. . . ) has domain-specific abstractions. n Excel’s function libraries are an ideal way of packaging those abstractions for Excel users. n Hence, we want to make it easy for end users to build, encapsulate, and share their own function libraries, without help from professional programmers.

First class data values

First class data values User-defined functions need array arguments. e. g. SUM( A 1: B 9 ) n Simple but powerful idea: anything a scalar can do, an array can do: n n n be the value of a formula be the value of a cell be the argument or result of a function Make Excel’s existing “array formulae” simpler and more powerful.

First class values Currency; units in general (unit-aware arithmetic) n Hyperlink n Matrix (index, add, multiply…) n Relation (filter, select, join…) n XML blob (query, combine) n Picture (generate picture from numbers, combine pictures) n Each value type comes complete with a repertoire of functions over it

Bulk data operations A 1 = …connect to a database relation… A 2 = EXTEND( A 1, [First Name], Get. First( [Name] ) ) A 3 = EXTEND( A 1, [Last Name], Get. Last( [Name] ) ) A 4 = FILTER( A 3, AND( [Age] > 30, [Age] < 50 ) ) A 5 = SELECT( A 4, [First Name], [Last Name], [Age] ) This stuff can be done today, by hand (e. g. Data/Auto. Filter), but it can’t be automated robustly

Extensible types It should be easy for a VB or C# programmer to add a new data type. All Excel needs to know about it is: n How to display it n How to “drill into” it to display its full value n Perhaps, how to downcast it to a number/string The recalc chain and dependency analysis are completely unaffected

Back to the supertanker Small crew, high-value payload, many customer requests, so systemic changes are not easy n Excel 2003 is out -- the next version is being designed n We’re talking to the Excel team regularly (weekly) n

Back to the supertanker Small crew, high-value payload, many customer requests, so systemic changes are not easy n Excel 2003 is out -- the next version is being designed n We’re talking to the Excel team weekly n Next: n higher order functions n assertions, test generation n static type system? n

Summary Functional programming End user & visual software engineering Psychology of programming Empower non-programmer end users (accountants, engineers, salesmen. . . ) to do things they could not do before • Control complexity through building re-usable abstractions • Succeed in more ambitious applications • Encapsulate domain-specific expertise in function libraries • Crush more errors earlier Multi-disciplinary inputs http: //research. microsoft. com/~simonpj/papers/excel