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 A1A1 C1 A2A2 D1 B1-C1 B2 A1A2 C2
A1-A2 D2 B2C2

• 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
Excels 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 Excels 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(A1A10)
• 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(A1A10)
• 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

1. 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 A1A1 C1 A2A2 D1 B1-C1 B2 A1A2 C2
A1-A2 D2 B2C2
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 function improves 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.
• Excels 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( A1B9 )
• 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 Excels 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 gt 30, Age lt 50 ) )
• A5 SELECT( A4, First Name, Last Name, Age
  )

This stuff can be done today, by hand (e.g.
Data/AutoFilter), but it cant 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
• Were 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
• Were talking to the Excel team weekly
• Next
• higher order functions
• assertions, test generation
• static type system?

37
Summary
Functional 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

End user visual software engineering
Psychology of programming
Multi-disciplinary inputs
http//research.microsoft.com/simonpj/papers/exce
l