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.
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.
Let us start with trying some simple computations.
Multiplying by 2
=2*A1. The cell
A1 is your input cell. You enter a number in it, and you get your output in B1.
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
nounsand write the
- In cell
=TRANSPOSE(A2:A4). This should arrange your
Note: In some softwares such as LibreOffice Calc, you might have to press
Ctrl-Shift-Enter for transpose to work correctly.
- In cell
=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"
To do this in a spreadsheet, color code cell
A1 as your input, and
C1 as the output. Follow the steps
=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.
=IF(EXACT(A2,B2),"",A2)and drag down to
=CONCATENATE(D1,C2)and drag down to
Now, it is time that we display the output
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
- 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.