Spreadsheet Programming

From Grundy
Jump to: navigation, search

Spreadsheet Programming is writing programs using a spreadsheet program such as MS Excel, LibreOffice Calc or Google Sheets. The use of spreadsheets for a massive volume of financial calculations every day is only a timid example of its capabilities. A lot of people fumble with the thought of calling a spreadsheet a program but come to think of it, you will provide it with an input, and it will provide you with an output, based on certain processes (formulae) that the user (programmer) defines. By construction, spreadsheets allow us to do functional programming, with certain limitations.

Introduction

You need to be familiar with basics of any of the popular spreadsheet frameworks. There is no single tutorial that can teach you well. Practice is key. And whenever you need something, just Google it.

If you want a deeper insight into Functional Programming and how it relates to the paradigm of spreadsheets, you need to read more on Lambda Calculus.

Examples

Beginner

Let us start with trying some simple computations.

Multiplying by 2

In cell B1, write =2*A1. The cell A1 is your input cell. You enter a number in it, and you get your output in B1.

Direct Product

Was that too trivial? Let us try to do something a bit complicated. Following is a Haskell code, with the output indicated.

let nouns = ["hobo","frog","pope"]  
let adjectives = ["lazy","grouchy","scheming"]  
[adjective ++ " " ++ noun | adjective <- adjectives, noun <- nouns]
 ["lazy hobo","lazy frog","lazy pope","grouchy hobo","grouchy frog",  
 "grouchy pope","scheming hobo","scheming frog","scheming pope"]

Now let us try to imitate this in a spreadsheet. Follow the steps

  • In cells A2-A4, write the first list of nouns and write the adjectives in cells B2-B4.
  • In cell C1, write =TRANSPOSE(A2:A4). This should arrange your nouns horizontally into C1-E1

Note: In some softwares such as LibreOffice Calc, you might have to press Ctrl-Shift-Enter for transpose to work correctly.

  • In cell C2, write =CONCATENATE($B2," ",C$1). This should display lazy hobo in the cell.
  • Now drag the formula through the range C2:E4, and you have your pairs ready.

Beautiful, isn't it?

Remove Lower Case

Let's ramp up the complexity. You'd soon notice how a simple task for other languages would become a challenge in Spreadsheet Programming, but the moral to be gained here is that there would be other tasks that are challenges in other languages but become simple in this paradigm. Well, a lot of those things are everyday practice to the financial world.

Here is a simple Haskell code removing Non-Uppercase characters from a string.

removeNonUppercase st = [ c | c <- st, c `elem` ['A'..'Z']]

producing results like

 ghci> removeNonUppercase "Hahaha! Ahahaha!"  
 "HA"  
 ghci> removeNonUppercase "IdontLIKEFROGS"  
 "ILIKEFROGS" 
RemoveNonUpperCase.png

To do this in a spreadsheet, color code cell A1 as your input, and C1 as the output. Follow the steps

  • In B1 write =LOWER(B1)
  • In A2 write =IF(A1="","",MID(A$1,ROW()-ROW(A$1),1)), and drag this down and right till B100, that is the formula should be dragged through both columns A and B, and till row 100.

Note: In this example, since we drag the formula to 100 cells, we can only support up to 99 characters. You can drag more, but you'll have to stop somewhere. This is a limitation that we will have to accept in this paradigm.

  • In C2 write =IF(EXACT(A2,B2),"",A2) and drag down to C100
  • In D2 write =CONCATENATE(D1,C2) and drag down to D100

Now, it is time that we display the output

  • In C1 write =D100

Write any string less than 99 characters in cell A1 and let the magic happen in cell C1. You can hide the "processing" cells to make it look like as shown in the adjacent figure.

Making a spreadsheet in Python

References

  • Felienne Hermans - Delft University of Technology - Her research work is concerned with end-user programming and she has done a lot of work on looking at spreadsheets as code. She is, without any doubt the foremost authority on functional programming using spreadsheets.
    • Take a look at her publications, which also include video links to talks.
    • Here is a particularly brilliant video that you might want to start with.
    • Here is a Turing machine written in Excel.