Spreadsheets: functional programming for the masses

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

2. Q1: What should a functional programmer in Microsoft Research do?

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

4. Q1: What should a functional programmer in Microsoft Research do? A1: Ask Q2 Q2: What is the worlds most widely used functional language, by far?

5. Q1: What should a functional programmer in MSR do? Q2: What is the worlds most widely used functional language, by far? Excel! Violent exothermic reaction

6. Spreadsheets are functional programs B1 = A1*A1 C1 = A2*A2 D1 = B1-C1 B2 = A1+A2 C2 = A1-A2 D2 = B2*C2 • Just a big bunch of equations • No side effects • Order of evaluation controlled by data dependencies

7. Q3: What chance does a pointy-headed researcher have of influencing the direction of a Microsoft cash cow?

8. Q3: What chance does a pointy-headed researcher have of influencing the direction of a Microsoft cash cow?

9. Excel’s market is tall 50m Can use Excel • 50m end-user • 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! End users Marketsize 2m “classic” programmers (write VB,C++, C#) 2m Can use VB, C++ Programmers

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

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

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

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

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

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

16. How? Marketsize 50m Two complementary ideas • Functions as ordinary spreadsheets End users 2. First class array values 2m Programmers Application requirements

17. Functions as ordinary spreadsheets

18. What's missing? B1 = A1*A1 C1 = A2*A2 D1 = B1-C1 B2 = A1+A2 C2 = A1-A2 D2 = B2*C2

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

20. User is working on a formula

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

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

23. Now, fill down does not lose sharing Regular fill down

24. User can see/modify the function definition as desired

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

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

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

28. Main program Calls Function CylVol Calls Function CircArea

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

30. First class data values

31. First class data values • User-defined functions need array arguments. e.g. SUM( A1:B9 ) • Simple but powerful idea: anything a scalar can do, an array can do: • 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.

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

33. Bulk data operations A1 = …connect to a database relation… A2 = EXTEND( A1, [First Name], GetFirst( [Name] ) )A3 = EXTEND( A1, [Last Name], GetLast( [Name] ) ) A4 = FILTER( A3, AND( [Age] > 30, [Age] < 50 ) ) A5 = SELECT( A4, [First Name], [Last Name], [Age] ) This stuff can be done today, by hand (e.g. Data/AutoFilter), but it can’t be automated robustly

34. 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: • How to display it • How to “drill into” it to display its full value • Perhaps, how to downcast it to a number/string The recalc chain and dependency analysis are completely unaffected

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

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

37. Summary Functional programming • Empower non-programmerend 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 • Crushmore errors earlier End user & visual software engineering Psychology of programming Multi-disciplinary inputs http://research.microsoft.com/~simonpj/papers/excel