Spreadsheets for Developers
January 26, 2016 7:57 AM Subscribe
Thinking about learning a new programming language? How about a functional language with support for test-driven development and a snazzy visual interface, already deployed on millions of computers around the world? I'm speaking, of course, about Excel. In a 2014 Strange Loop talk, Felienne discusses the virtues of the Excel programming language (which is Turing complete, if you were wondering).
I knew a guy who used Excel to write a program to translate songs written in chord notation to guitar tablature. Excel is apparently pretty complete.
posted by amtho at 8:09 AM on January 26, 2016 [1 favorite]
posted by amtho at 8:09 AM on January 26, 2016 [1 favorite]
I see she covers Excel as a functional language. :-)
posted by clawsoon at 8:11 AM on January 26, 2016 [1 favorite]
posted by clawsoon at 8:11 AM on January 26, 2016 [1 favorite]
She makes the functional programming comment early in the video. This is an amusing talk, but It hasn't convinced me to use Excel for anything complicated. Also, 'Turing complete' is an important threshold, but one that is very easy to pass.
posted by demiurge at 8:14 AM on January 26, 2016 [2 favorites]
posted by demiurge at 8:14 AM on January 26, 2016 [2 favorites]
Festival of the Spoken Nerd's Matt Parker discusses spreadsheets.
posted by zamboni at 8:23 AM on January 26, 2016 [1 favorite]
posted by zamboni at 8:23 AM on January 26, 2016 [1 favorite]
IMO, the problem with spreadsheets is that they merge presentation, business logic, and source data. As a result, it's not immediately obvious what you can modify and what you should leave alone when working with someone else's spreadsheet, or what cells depend on what.
posted by pwnguin at 8:29 AM on January 26, 2016 [13 favorites]
posted by pwnguin at 8:29 AM on January 26, 2016 [13 favorites]
Isn't the mixing just a result of programming habits - just like with any language - rather than a fundamental defect of spreadsheets as a language? It seems like it would be easy enough to label the worksheets clearly - and perhaps even lock individual worksheets as appropriate? - to keep presentation, logic and data separate.
It would probably be straightforward to tweak an existing spreadsheet application to enforce that for you. GoogleProgrammingSheets, coming soon to a beta lab near you...
posted by clawsoon at 8:34 AM on January 26, 2016 [1 favorite]
It would probably be straightforward to tweak an existing spreadsheet application to enforce that for you. GoogleProgrammingSheets, coming soon to a beta lab near you...
posted by clawsoon at 8:34 AM on January 26, 2016 [1 favorite]
This is a neat construction, but I disagree that it shows Turing-completeness. Unless I misunderstand the construction, you need to create new columns by hand. In order to have Turing-completeness, Excel would need a mechanism that creates new rows until it creates a row that contains the halting state. (Otherwise, you could also argue that regular languages are Turing-complete, as they are closed under finite state transductions, and every step of a Turing machine can be modeled with a single transduction).
What the construction proves is that it is undecidable whether there is a row that has an identical copy as successor row. (Also cool, but less powerful than Turing-complete.)
posted by erdferkel at 8:34 AM on January 26, 2016 [3 favorites]
What the construction proves is that it is undecidable whether there is a row that has an identical copy as successor row. (Also cool, but less powerful than Turing-complete.)
posted by erdferkel at 8:34 AM on January 26, 2016 [3 favorites]
I once created an Excel file/program for extracting instrument data from a bunch of ASCII text files and semi-automatically compiling the extracted data into summary tables and charts.
Excel is perfect for infrequent-use / throwaway applications like this because fairly casual users with no "real programming" skills can inherit and use / improve them.
posted by ZenMasterThis at 8:35 AM on January 26, 2016
Excel is perfect for infrequent-use / throwaway applications like this because fairly casual users with no "real programming" skills can inherit and use / improve them.
posted by ZenMasterThis at 8:35 AM on January 26, 2016
What clawsoon said is correct; you can hide and /or lockout cells, rows, columns and tabs to isolate presentation, logic, hardwired data and user data.
posted by ZenMasterThis at 8:42 AM on January 26, 2016 [1 favorite]
posted by ZenMasterThis at 8:42 AM on January 26, 2016 [1 favorite]
the virtues of the Excel programming language (which is Turing complete, if you were wondering).
So is Brainfuck.
posted by indubitable at 8:44 AM on January 26, 2016 [10 favorites]
So is Brainfuck.
posted by indubitable at 8:44 AM on January 26, 2016 [10 favorites]
Fun talk!
If you ever wanted to use a programmable spreadsheet to model uncertainty, be sure to check out Guesstimate.
posted by a snickering nuthatch at 8:45 AM on January 26, 2016 [2 favorites]
If you ever wanted to use a programmable spreadsheet to model uncertainty, be sure to check out Guesstimate.
posted by a snickering nuthatch at 8:45 AM on January 26, 2016 [2 favorites]
Seconding erdferkel's comment. It's a great visual aid, but since the number of rows are fixed it computes primitive recursive functions only -- like Hofstadter's BlooP which was designed to demonstrate this point.
posted by RobotVoodooPower at 8:48 AM on January 26, 2016 [2 favorites]
posted by RobotVoodooPower at 8:48 AM on January 26, 2016 [2 favorites]
There have been repeated studies that show many (most?) spreadsheets have errors and that the likelihood of an error goes up with the size of the spreadsheet (probably unsurprising to most programmers). (Google for lots on the topic.)
In large part this is because spreadsheets are hard to reason about since the updating of cells can happen in ways that are not obvious (update one cell and it triggers updates in others that you don't see). Another factor is that people who build spreadsheets often don't think about verifying/debugging them - sometimes because there's nothing good to test against, sometimes because the authors don't think about that as they're writing them and sometimes because they grow beyond mortal ken. I've been guilty of enough spreadsheet errors, but have been much more aware of the problem since I found one massively awful blooper in one spreadsheet.
posted by Death and Gravity at 8:49 AM on January 26, 2016 [5 favorites]
In large part this is because spreadsheets are hard to reason about since the updating of cells can happen in ways that are not obvious (update one cell and it triggers updates in others that you don't see). Another factor is that people who build spreadsheets often don't think about verifying/debugging them - sometimes because there's nothing good to test against, sometimes because the authors don't think about that as they're writing them and sometimes because they grow beyond mortal ken. I've been guilty of enough spreadsheet errors, but have been much more aware of the problem since I found one massively awful blooper in one spreadsheet.
posted by Death and Gravity at 8:49 AM on January 26, 2016 [5 favorites]
Excel is an amazing application that is so awesomely horribly misused it's difficult to hear anything about it without making me grit my teeth. I am a programmer that works with researchers that are completely devoted to using Excel as their primary Everything tool, and this makes me orders of magnitude less productive than I could be if they kept their data somewhere sane.
I mean, she's right: spreadsheets are code, not data. As soon as you start using them for Very Important Data that needs to be parsed/manipulated/exported to something other than Excel, you are 10 levels of screwed.
Take for example the fact that Excel, by default, considers all cells floating point numbers. Now try to deal with a column of string data that includes legitimate strings like '04015', or worse yet '-01512' (and don't tell me to just format the cell, the problem cuts waaaay deeper than that*).
So yeah, spreadsheets are pretty cool when looked at in certain ways. The problem is people trying to use them in other much more common and insidious ways.
* there's also a famous example from bioinformatics where Excel was reading gene designations as dates, and the auto-formatting for Excel dates is lossy, so loads and loads of really important research was lost. Because of Excel.
posted by mcstayinskool at 8:52 AM on January 26, 2016 [30 favorites]
I mean, she's right: spreadsheets are code, not data. As soon as you start using them for Very Important Data that needs to be parsed/manipulated/exported to something other than Excel, you are 10 levels of screwed.
Take for example the fact that Excel, by default, considers all cells floating point numbers. Now try to deal with a column of string data that includes legitimate strings like '04015', or worse yet '-01512' (and don't tell me to just format the cell, the problem cuts waaaay deeper than that*).
So yeah, spreadsheets are pretty cool when looked at in certain ways. The problem is people trying to use them in other much more common and insidious ways.
* there's also a famous example from bioinformatics where Excel was reading gene designations as dates, and the auto-formatting for Excel dates is lossy, so loads and loads of really important research was lost. Because of Excel.
posted by mcstayinskool at 8:52 AM on January 26, 2016 [30 favorites]
My Excell programming work flow is to export to csv, write a shell or Python script to do what I want to the data and then import back into Excell.
posted by octothorpe at 8:57 AM on January 26, 2016 [21 favorites]
posted by octothorpe at 8:57 AM on January 26, 2016 [21 favorites]
the auto-formatting for Excel dates is lossy
And can't be turned off. It's maddening. From Microsoft's official support document on the subject:
posted by jedicus at 8:58 AM on January 26, 2016 [15 favorites]
And can't be turned off. It's maddening. From Microsoft's official support document on the subject:
Microsoft Excel is preprogrammed to make it easier to enter dates. For example, 12/2 changes to 2-Dec. This is very frustrating when you enter something that you don't want changed to a date. Unfortunately there is no way to turn this off.If there's one feature I would add to Excel it would be a way to completely turn off all automatic data formatting, as a global setting and per-file.
posted by jedicus at 8:58 AM on January 26, 2016 [15 favorites]
I really don't regret rewriting some of my Excel workflows in Python. I can use real version control, have others review my changes in a standard process, commenting code is way more straightforward, there are lots of tools that can automatically flag errors, the floating point model isn't totally broken (has MS fixed that yet?), if I want to work with nontrivial amounts of data there's NumPy, better plotting that doesn't fall over with a lot of data, the list goes on.
posted by indubitable at 9:02 AM on January 26, 2016 [5 favorites]
posted by indubitable at 9:02 AM on January 26, 2016 [5 favorites]
I've had every single accounting export screw up dates, but not consistently -- it will take some as text, some into the wrong date format (it has trouble importing little endian dates somehow). I also particularly hate how you can't easily display multiple spreadsheets.
That said it does have a lot of uses, and overall I mostly do like it.
posted by jeather at 9:03 AM on January 26, 2016
That said it does have a lot of uses, and overall I mostly do like it.
posted by jeather at 9:03 AM on January 26, 2016
"My Excell programming work flow is to export to csv, write a shell or Python script to do what I want to the data and then import back into Excell."
Heh! It was the opposite for me! In the early 90s I worked as a scene planner designing 2d camera moves for animated films using beta versions of the brand new Softimage Toonz on SGI boxes. At the time the software had only rudimentary multiplane functionality (a technique used to simulate depth by shooting each background elent separately traveling at different speeds during pans etc). In early SiT you could set up a camera per element but not declare one the master and drive the others from it. So multiplaning was 100% manual and rather painful to get right. Then I noticed that you could import/export camera data in csv format! Before you know it I had set up Excel spreadsheets that would import the master camera data from csv files, calculate arbitrary multiplane cameras based on depth values I extracted from the artwork by measuring stuff like trees and lamp posts and spit those back out to csv. Worked like a charm. That was my first bit of job related "programming" which evolved into a 20+ year career of writing pipeline code, artist tools and plugins for 3d film productions. And it all started with Excel!
posted by Hairy Lobster at 9:06 AM on January 26, 2016 [23 favorites]
Heh! It was the opposite for me! In the early 90s I worked as a scene planner designing 2d camera moves for animated films using beta versions of the brand new Softimage Toonz on SGI boxes. At the time the software had only rudimentary multiplane functionality (a technique used to simulate depth by shooting each background elent separately traveling at different speeds during pans etc). In early SiT you could set up a camera per element but not declare one the master and drive the others from it. So multiplaning was 100% manual and rather painful to get right. Then I noticed that you could import/export camera data in csv format! Before you know it I had set up Excel spreadsheets that would import the master camera data from csv files, calculate arbitrary multiplane cameras based on depth values I extracted from the artwork by measuring stuff like trees and lamp posts and spit those back out to csv. Worked like a charm. That was my first bit of job related "programming" which evolved into a 20+ year career of writing pipeline code, artist tools and plugins for 3d film productions. And it all started with Excel!
posted by Hairy Lobster at 9:06 AM on January 26, 2016 [23 favorites]
It's always fun when somebody tells you about their database or their application, and then you ask to see it and they break out Excel.
posted by Dr Dracator at 9:09 AM on January 26, 2016 [6 favorites]
posted by Dr Dracator at 9:09 AM on January 26, 2016 [6 favorites]
I used to keep around, for emergency use, an Excel 2000 file that had an implementation of Pac-Man
posted by thelonius at 9:10 AM on January 26, 2016 [2 favorites]
posted by thelonius at 9:10 AM on January 26, 2016 [2 favorites]
You lost me at =IF(E$2=$D4,"X",IF(E$2=$B4,"X",E3))
posted by lipsum at 9:11 AM on January 26, 2016 [2 favorites]
posted by lipsum at 9:11 AM on January 26, 2016 [2 favorites]
You can also play fairly complex German-style board games in Excel via email (or the cloud, I guess). As long as the games are asynchronous, meaning no input is required from the other players on your turn, you're good to go.
On your turn, you're sent the most current version of the game file so you can play your moves, then you save the file and send it to the next opponent. The Excel files are more than just board state snapshots...all the ones I've seen have rules interpreters built in. Therefore, it's not like you're just manually updating cells; you're playing the game.
Some of them are quite advanced. See Puerto Rico Evolver, which actually supports AI opponents!
posted by Ian A.T. at 9:11 AM on January 26, 2016 [5 favorites]
On your turn, you're sent the most current version of the game file so you can play your moves, then you save the file and send it to the next opponent. The Excel files are more than just board state snapshots...all the ones I've seen have rules interpreters built in. Therefore, it's not like you're just manually updating cells; you're playing the game.
Some of them are quite advanced. See Puerto Rico Evolver, which actually supports AI opponents!
posted by Ian A.T. at 9:11 AM on January 26, 2016 [5 favorites]
So what the world needs is strongly-typed Excel.
posted by clawsoon at 9:14 AM on January 26, 2016 [4 favorites]
posted by clawsoon at 9:14 AM on January 26, 2016 [4 favorites]
Without good version control, I remain mildly terrified that so much of the modern world is powered by Excel spreadsheets.
posted by simonw at 9:21 AM on January 26, 2016 [7 favorites]
posted by simonw at 9:21 AM on January 26, 2016 [7 favorites]
Hmmm, strongly-typed Excel with version control...and unit testing? And really really good help files?
Anybody want to make the Kickstarter video with me? And then do all the work?
posted by amtho at 9:26 AM on January 26, 2016 [5 favorites]
Anybody want to make the Kickstarter video with me? And then do all the work?
posted by amtho at 9:26 AM on January 26, 2016 [5 favorites]
Some of them are quite advanced. See Puerto Rico Evolver, which actually supports AI opponents!
Gosh, I looked at that thing and it's quite nutty. It also genetically *evolves* AI opponents... but it's less of a spreadsheet and more a data store and UI for a large set of VBA macros. Which is a sort of cool use case for a spreadsheet, since you can focus on logic and less on presentation (and debugging is easy, since everything is stored in a cell somewhere).
posted by RobotVoodooPower at 9:30 AM on January 26, 2016
Gosh, I looked at that thing and it's quite nutty. It also genetically *evolves* AI opponents... but it's less of a spreadsheet and more a data store and UI for a large set of VBA macros. Which is a sort of cool use case for a spreadsheet, since you can focus on logic and less on presentation (and debugging is easy, since everything is stored in a cell somewhere).
posted by RobotVoodooPower at 9:30 AM on January 26, 2016
If there's one feature I would add to Excel it would be a way to completely turn off all automatic data formatting, as a global setting and per-file.
Not a global switch, but FWIW you can override automatic date formatting at data element level by prepending an apostrophe to the data element. The apostrophe does not appear in the spreadsheet display for the cell.
posted by ZenMasterThis at 9:35 AM on January 26, 2016 [2 favorites]
Not a global switch, but FWIW you can override automatic date formatting at data element level by prepending an apostrophe to the data element. The apostrophe does not appear in the spreadsheet display for the cell.
posted by ZenMasterThis at 9:35 AM on January 26, 2016 [2 favorites]
The corollary to Excel being a programming language is that Excel programs have bugs. Lots of bugs. And because it all just looks like it's working, it's very hard to find logic errors. 88% of spreadsheets have errors, according to a 2008 study.
posted by Nelson at 9:36 AM on January 26, 2016 [1 favorite]
posted by Nelson at 9:36 AM on January 26, 2016 [1 favorite]
What percentage of non-Excel computer programs have errors, I wonder?
posted by clawsoon at 9:42 AM on January 26, 2016 [3 favorites]
posted by clawsoon at 9:42 AM on January 26, 2016 [3 favorites]
The corollary to Excel being a programming language is that Excel programs have bugs. Lots of bugs.
I'm no Excel apologist, but I do have to wonder if this is a classic case of PEBKAC rather than innate language shortcomings. Based on what what I've seen from my exposures to Excel macros (1 < n < 5000), the inclusion of a given program in the set of "Programs that were written by sitting on the keyboard and then hitting save" is not decidable.
posted by Mayor West at 9:45 AM on January 26, 2016
I'm no Excel apologist, but I do have to wonder if this is a classic case of PEBKAC rather than innate language shortcomings. Based on what what I've seen from my exposures to Excel macros (1 < n < 5000), the inclusion of a given program in the set of "Programs that were written by sitting on the keyboard and then hitting save" is not decidable.
posted by Mayor West at 9:45 AM on January 26, 2016
clawsoon: "What percentage of non-Excel computer programs have errors, I wonder?"
Somewhere around 114% according to data-driven studies.
posted by boo_radley at 9:45 AM on January 26, 2016 [20 favorites]
Somewhere around 114% according to data-driven studies.
posted by boo_radley at 9:45 AM on January 26, 2016 [20 favorites]
It's always fun when somebody tells you about their database or their application, and then you ask to see it and they break out Excel.
I dunno, that's still less terrifying than them breaking out Access.
posted by Mayor West at 9:47 AM on January 26, 2016 [7 favorites]
I dunno, that's still less terrifying than them breaking out Access.
posted by Mayor West at 9:47 AM on January 26, 2016 [7 favorites]
Anyone want to collaborate on an open source C++ compiler written in Excel?
posted by sammyo at 9:51 AM on January 26, 2016 [2 favorites]
posted by sammyo at 9:51 AM on January 26, 2016 [2 favorites]
I am a programmer that works with researchers that are completely devoted to using Excel as their primary Everything tool, and this makes me orders of magnitude less productive than I could be if they kept their data somewhere sane.
Researchers are data-centric people. They care about their numbers, much less about their calculation methods. Often they just want to apply a standard tool or transformation or do a visualization or compile a statistic. They don't want to have to care about the tool.
There is a substantial mental cost to switching between what a programmer would consider a sane "development environment", i.e. making the tool used to do the task, and working with the results. Excel solves that problem really well at the cost of hiding everything else. It's a particular tool that works better and is far more lightweight than anything else for easy stuff, but gets into the weeds very quickly as things become more complex. It's like Perl for researchers.
There's not yet been a substantially better tool. Some people use wholely GUI-based packages. Systat has you covered there. Some people do use a traditional dev environment and programming language approach: FORTRAN, matlab, R. There are weirdos like Labview. But nothing that's absolutely clearly better than a spreadsheet for that sort of tabular data tasks.
I see this as currently one of the biggest limitations in science and related fields. The best answer in many of the groups I've run into, from habitat biology to environmental chemistry to atmospheric physics, seems to be to hire a "data scientist" who can do those context switches easily and let the front-line researchers direct them. In that case, the spreadsheet becomes the data entry and result presentation tool, not the analytical engine.
posted by bonehead at 9:54 AM on January 26, 2016 [13 favorites]
Researchers are data-centric people. They care about their numbers, much less about their calculation methods. Often they just want to apply a standard tool or transformation or do a visualization or compile a statistic. They don't want to have to care about the tool.
There is a substantial mental cost to switching between what a programmer would consider a sane "development environment", i.e. making the tool used to do the task, and working with the results. Excel solves that problem really well at the cost of hiding everything else. It's a particular tool that works better and is far more lightweight than anything else for easy stuff, but gets into the weeds very quickly as things become more complex. It's like Perl for researchers.
There's not yet been a substantially better tool. Some people use wholely GUI-based packages. Systat has you covered there. Some people do use a traditional dev environment and programming language approach: FORTRAN, matlab, R. There are weirdos like Labview. But nothing that's absolutely clearly better than a spreadsheet for that sort of tabular data tasks.
I see this as currently one of the biggest limitations in science and related fields. The best answer in many of the groups I've run into, from habitat biology to environmental chemistry to atmospheric physics, seems to be to hire a "data scientist" who can do those context switches easily and let the front-line researchers direct them. In that case, the spreadsheet becomes the data entry and result presentation tool, not the analytical engine.
posted by bonehead at 9:54 AM on January 26, 2016 [13 favorites]
I know someone who is dealing with large data sets, so large that excel is not working out so well. They're interested in learning maybe excel programming, may MS Access, or maybe something else. Any suggestions for what a useful tool for a beginner programmer and data manager to learn?
posted by rebent at 10:04 AM on January 26, 2016
posted by rebent at 10:04 AM on January 26, 2016
Without good version control, I remain mildly terrified that so much of the modern world is powered by Excel spreadsheets.
just in DoD...you have no idea...
posted by j_curiouser at 10:06 AM on January 26, 2016 [4 favorites]
just in DoD...you have no idea...
posted by j_curiouser at 10:06 AM on January 26, 2016 [4 favorites]
saw this linked somewhere here before: Accidentally Turing-Complete!*
---
*If AGI is impossible, you could not exist either. QED. - "Despite this long record of failure, AGI must be possible. And that is because of a deep property of the laws of physics, namely the universality of computation." :P
posted by kliuless at 10:10 AM on January 26, 2016 [1 favorite]
---
*If AGI is impossible, you could not exist either. QED. - "Despite this long record of failure, AGI must be possible. And that is because of a deep property of the laws of physics, namely the universality of computation." :P
posted by kliuless at 10:10 AM on January 26, 2016 [1 favorite]
clawsoon: "and perhaps even lock individual worksheets as appropriate? - to keep presentation, logic and data separate."
The huge, glaring problem with spreadsheets is that they hide your code and logic from you.
Many see this is an extreme viewpoint, but I generally view spreadsheets as being fundamentally and irreparably flawed for this reason. Unfortunately, we don't have better business tools, so they're pretty much ubiquitous now.
There's no good way to follow the logic of an entire spreadsheet without clicking into every single cell. They're impossible to proofread, and it's completely poisoned the business world.
It doesn't need to be this way either. While I'm sympathetic to arguments that programming can be inaccessible to newcomers, there are other disciplines that have resisted the temptation to wall themselves off from anything that even looks like code. The scientific community is a good example of this -- even non-computational scientists write an awful lot of code these days.
When did business analysts stop needing to know how to work with databases and write simple programs (a la FORTRAN)?
posted by schmod at 10:29 AM on January 26, 2016 [6 favorites]
The huge, glaring problem with spreadsheets is that they hide your code and logic from you.
Many see this is an extreme viewpoint, but I generally view spreadsheets as being fundamentally and irreparably flawed for this reason. Unfortunately, we don't have better business tools, so they're pretty much ubiquitous now.
There's no good way to follow the logic of an entire spreadsheet without clicking into every single cell. They're impossible to proofread, and it's completely poisoned the business world.
It doesn't need to be this way either. While I'm sympathetic to arguments that programming can be inaccessible to newcomers, there are other disciplines that have resisted the temptation to wall themselves off from anything that even looks like code. The scientific community is a good example of this -- even non-computational scientists write an awful lot of code these days.
When did business analysts stop needing to know how to work with databases and write simple programs (a la FORTRAN)?
posted by schmod at 10:29 AM on January 26, 2016 [6 favorites]
Not a global switch, but FWIW you can override automatic date formatting at data element level by prepending an apostrophe to the data element. The apostrophe does not appear in the spreadsheet display for the cell.
This causes all sorts of wacky keyboard headbashing fun when your data actually contains apostrophes, or Excel decides to automagically add an apostrophe to protect you from yourself.
posted by Dr Dracator at 10:32 AM on January 26, 2016 [1 favorite]
This causes all sorts of wacky keyboard headbashing fun when your data actually contains apostrophes, or Excel decides to automagically add an apostrophe to protect you from yourself.
posted by Dr Dracator at 10:32 AM on January 26, 2016 [1 favorite]
It doesn't need to be this way either. While I'm sympathetic to arguments that programming can be inaccessible to newcomers, there are other disciplines that have resisted the temptation to wall themselves off from anything that even looks like code. The scientific community is a good example of this -- even non-computational scientists write an awful lot of code these days.
It's not a natural fit for that group either.
We're in a situation where the programming community has that problem space, how to write some code to do something, pretty well figured out. Tools are really nice to use.
In most other analysis fields, the tools are still really, really crude. Excel still is the state of the art for most people. And the way programmers work doesn't really transfer that well to those tasks. You can build an airplane with carpenters tools, but that doesn't mean the task will be easy, quick or without problems. That's what data analysis with current toolsets feels like most of the time, unnatural and awkward.
posted by bonehead at 10:38 AM on January 26, 2016 [2 favorites]
It's not a natural fit for that group either.
We're in a situation where the programming community has that problem space, how to write some code to do something, pretty well figured out. Tools are really nice to use.
In most other analysis fields, the tools are still really, really crude. Excel still is the state of the art for most people. And the way programmers work doesn't really transfer that well to those tasks. You can build an airplane with carpenters tools, but that doesn't mean the task will be easy, quick or without problems. That's what data analysis with current toolsets feels like most of the time, unnatural and awkward.
posted by bonehead at 10:38 AM on January 26, 2016 [2 favorites]
bah. who needs bloated Micro$oft Office. real programmers use only PostScript
posted by ennui.bz at 10:40 AM on January 26, 2016 [1 favorite]
posted by ennui.bz at 10:40 AM on January 26, 2016 [1 favorite]
I know someone who is dealing with large data sets, so large that excel is not working out so well. They're interested in learning maybe excel programming, may MS Access, or maybe something else. Any suggestions for what a useful tool for a beginner programmer and data manager to learn?
you need to provide a lot more info - what field, what kind of data, what kind of processing. consider making an askme post.
posted by andrewcooke at 10:40 AM on January 26, 2016 [2 favorites]
you need to provide a lot more info - what field, what kind of data, what kind of processing. consider making an askme post.
posted by andrewcooke at 10:40 AM on January 26, 2016 [2 favorites]
We're in a situation where the programming community has that problem space, how to write some code to do something, pretty well figured out. Tools are really nice to use.
Heh, yeah...the problem the programming community doesn't have pretty well figured out is how to write some code to do something for a particular thing t that's fixed in advance. Still working on that one.
posted by invitapriore at 10:46 AM on January 26, 2016
Heh, yeah...the problem the programming community doesn't have pretty well figured out is how to write some code to do something for a particular thing t that's fixed in advance. Still working on that one.
posted by invitapriore at 10:46 AM on January 26, 2016
My first spreadsheet was Lotus 1A running on an IBM PC via a 5.25" floppy drive in the mid 80's. I subsequently ate, breathed, slept and defecated spreadsheets for the next three decades. They kept me and my family clothed, fed and housed in the interim. An interesting presentation but almost any dedicated user probably already knows, at least intuitively, the points she touched upon.
BTW, this is my 1000th comment on The Blue. Woo hoo!
posted by jim in austin at 10:46 AM on January 26, 2016 [2 favorites]
BTW, this is my 1000th comment on The Blue. Woo hoo!
posted by jim in austin at 10:46 AM on January 26, 2016 [2 favorites]
It occurs to me you could have a unit test worksheet, with test cells combining formulas from other sheets and test data, that change colour if the result is wrong.
posted by i_am_joe's_spleen at 10:48 AM on January 26, 2016
posted by i_am_joe's_spleen at 10:48 AM on January 26, 2016
*If AGI is impossible, you could not exist either. QED.
Um, I am but a lay-person as regards both quantum physics and quantum computation, but it was definitely not my impression that whether a universal quantum computer can emulate "in arbitrarily fine detail" any physical system is a solved problem.
posted by invitapriore at 10:54 AM on January 26, 2016
Um, I am but a lay-person as regards both quantum physics and quantum computation, but it was definitely not my impression that whether a universal quantum computer can emulate "in arbitrarily fine detail" any physical system is a solved problem.
posted by invitapriore at 10:54 AM on January 26, 2016
When did business analysts stop needing to know how to work with databases and write simple programs (a la FORTRAN)?
If you could only see some of the Fortran that engineers wrote 20-30 years ago, you may conclude that they never knew how in the first place.
(I exaggerate, but Fortran 77 and earlier encouraged some truly awful programming practices.)
posted by indubitable at 11:01 AM on January 26, 2016 [1 favorite]
If you could only see some of the Fortran that engineers wrote 20-30 years ago, you may conclude that they never knew how in the first place.
(I exaggerate, but Fortran 77 and earlier encouraged some truly awful programming practices.)
posted by indubitable at 11:01 AM on January 26, 2016 [1 favorite]
Would BAs have been using FORTRAN or COBOL? Or even ALGOL? (Not that it really matters.)
posted by bonehead at 11:09 AM on January 26, 2016
posted by bonehead at 11:09 AM on January 26, 2016
Um, I am but a lay-person as regards both quantum physics and quantum computation, but it was definitely not my impression that whether a universal quantum computer can emulate "in arbitrarily fine detail" any physical system is a solved problem.
i know, right?
posted by kliuless at 11:11 AM on January 26, 2016
i know, right?
posted by kliuless at 11:11 AM on January 26, 2016
I remember people back in the '80s, maybe it was me, who wrote macros to get Lotus 1-2-3 to do mail merges and spit out form letters.
posted by lagomorphius at 11:28 AM on January 26, 2016
posted by lagomorphius at 11:28 AM on January 26, 2016
I see she covers Excel as a functional language.
My brain is too fried to watch this at the moment from a wildly badly scoped plan to use Excel molish* up my black box testing data for a reasonably large database extract (40 or so output tables, of which I'm testing 10, with appropriate values placed into 20 or so other tables).
Given that, could anyone tell me if she addresses the lack of a λ-function in Excel, which is its biggest weakness as a parallel processed programming environment (as long as you're using tables, that is.)?
*the opposite of demolish, obviously
posted by ambrosen at 11:57 AM on January 26, 2016
My brain is too fried to watch this at the moment from a wildly badly scoped plan to use Excel molish* up my black box testing data for a reasonably large database extract (40 or so output tables, of which I'm testing 10, with appropriate values placed into 20 or so other tables).
Given that, could anyone tell me if she addresses the lack of a λ-function in Excel, which is its biggest weakness as a parallel processed programming environment (as long as you're using tables, that is.)?
*the opposite of demolish, obviously
posted by ambrosen at 11:57 AM on January 26, 2016
I see she covers Excel as a functional language
HAHAHAHAHAHAHA....
(He says, as a developer in functional languages
(among others
(including Lisp)))
Excel is the bane of my life, the Swiss Army chainsaw used by BAs and other non-programmers to hack together "solutions" that then, Frankenstein-like, get a life of their own and REFUSE TO DIE, no matter how much I try to hammer a stake through their hearts.
I still shudder at the thought of the 48MB spreadsheet file, put together by some economists, that took overnight to recalculate. Because they "weren't programmers", but were happy to write bad VBA till the cows came home, with no real idea of what they were doing.
And seriously, most programming languages can be written in a functional style (I have written functional Fortran, and write functional C on a regular basis) but that doesn't make them "functional languages", FHS.
posted by 43rdAnd9th at 12:20 PM on January 26, 2016 [2 favorites]
HAHAHAHAHAHAHA....
(He says, as a developer in functional languages
(among others
(including Lisp)))
Excel is the bane of my life, the Swiss Army chainsaw used by BAs and other non-programmers to hack together "solutions" that then, Frankenstein-like, get a life of their own and REFUSE TO DIE, no matter how much I try to hammer a stake through their hearts.
I still shudder at the thought of the 48MB spreadsheet file, put together by some economists, that took overnight to recalculate. Because they "weren't programmers", but were happy to write bad VBA till the cows came home, with no real idea of what they were doing.
And seriously, most programming languages can be written in a functional style (I have written functional Fortran, and write functional C on a regular basis) but that doesn't make them "functional languages", FHS.
posted by 43rdAnd9th at 12:20 PM on January 26, 2016 [2 favorites]
Um, I am but a lay-person as regards both quantum physics and quantum computation, but it was definitely not my impression that whether a universal quantum computer can emulate "in arbitrarily fine detail" any physical system is a solved problem.i know, right?
But...you quoted that bit without qualification, and then, ...I...what?
posted by invitapriore at 12:21 PM on January 26, 2016
Okay, this probably is as good place as any to mention that there's a project for a running a Clojurescript (a Javascript targeted compiler of Clojure, which is a Lisp) REPL with Excel.
posted by Mister Cheese at 12:47 PM on January 26, 2016
posted by Mister Cheese at 12:47 PM on January 26, 2016
(He says, as a developer in functional languages
(among others
(including Lisp)))
43rdAnd9th
(see i whatyouddidthere)
posted by The Gaffer at 12:55 PM on January 26, 2016
(among others
(including Lisp)))
43rdAnd9th
(see i whatyouddidthere)
posted by The Gaffer at 12:55 PM on January 26, 2016
were happy to write bad VBA till the cows came home
No one's saying VBA's a functional language. It's the Excel formula language which is.
posted by ambrosen at 1:02 PM on January 26, 2016 [1 favorite]
No one's saying VBA's a functional language. It's the Excel formula language which is.
posted by ambrosen at 1:02 PM on January 26, 2016 [1 favorite]
bonehead: "Researchers are data-centric people. They care about their numbers, much less about their calculation methods. Often they just want to apply a standard tool or transformation or do a visualization or compile a statistic. They don't want to have to care about the tool."
I totally get what you are saying here. However, if the tool messes with your data in a way that screws up your research, you damn well better care. Not wanting to care is not a good reason for not caring.
posted by mcstayinskool at 1:36 PM on January 26, 2016 [2 favorites]
I totally get what you are saying here. However, if the tool messes with your data in a way that screws up your research, you damn well better care. Not wanting to care is not a good reason for not caring.
posted by mcstayinskool at 1:36 PM on January 26, 2016 [2 favorites]
Is this the place to post a link about the guy who made an XCom game in Excel VBA? Which was, apparently, named "ExcelCOM".
posted by barnacles at 2:55 PM on January 26, 2016 [1 favorite]
posted by barnacles at 2:55 PM on January 26, 2016 [1 favorite]
I worked on a contract for (LFI) a couple of years back. The Australian Taxation Office (ATO) was bringing in new reporting requirements for tax on certain contributions and LFI had put together a large project team to build a solution.
Problem was, the development team coding the solution got behind, way behind. In the end they took an Excel spreadsheet that had been previously used to process small manual contributions, and made it a centrepiece of their interim solution. Why? Because it worked, and although it wasn't a complete solution in itself, the work it did matched pretty well with the parts of the main solution that didn't work, and the parts of the main solution that did, sorta, work, filled the gaps that the spreadsheet wasn't designed to do.
The spreadsheet (actuallly a template) used VBA to allow users to e.g. select the file to process. It linked to a mainframe database, several Access databases and several other spreadsheets to update parameters such as accounts, exchange rates and interest rates. But the core of the spreadsheet was a massive web of formulas and named ranges that took the data, processed it, applied business rules, and stored it in worksheets. Once that was complete, VBA then took these worksheets and output their information to XML or CSV files ready for transmission to the ATO.
It was a Rube-Goldberg device of stupendous proportions, but it was written by one person (two after I came on board to help the main developer with some of the bits that neither solution was doing) and it got the job done, which is more than could be said for the main solution with its 20 developers.
Sure, Excel overhelpfully messes with some data. Which is why you design your spreadsheets intelligently to deal with that, and you sanitize your inputs and outputs. The system I described above took arbitrary error-prone source input, applied automatic corrections for known distortions (based on past data from the same source), calculated checksums to highlight new errors it couldn't automatically correct (typoes and OCR errors mostly) so that a human operator could identify the issue and apply a correction, and produced output that met the tax office's stringent requirements for accuracy. Not bad for a "mere" spreadsheet!
posted by Autumn Leaf at 3:15 PM on January 26, 2016 [6 favorites]
Problem was, the development team coding the solution got behind, way behind. In the end they took an Excel spreadsheet that had been previously used to process small manual contributions, and made it a centrepiece of their interim solution. Why? Because it worked, and although it wasn't a complete solution in itself, the work it did matched pretty well with the parts of the main solution that didn't work, and the parts of the main solution that did, sorta, work, filled the gaps that the spreadsheet wasn't designed to do.
The spreadsheet (actuallly a template) used VBA to allow users to e.g. select the file to process. It linked to a mainframe database, several Access databases and several other spreadsheets to update parameters such as accounts, exchange rates and interest rates. But the core of the spreadsheet was a massive web of formulas and named ranges that took the data, processed it, applied business rules, and stored it in worksheets. Once that was complete, VBA then took these worksheets and output their information to XML or CSV files ready for transmission to the ATO.
It was a Rube-Goldberg device of stupendous proportions, but it was written by one person (two after I came on board to help the main developer with some of the bits that neither solution was doing) and it got the job done, which is more than could be said for the main solution with its 20 developers.
Sure, Excel overhelpfully messes with some data. Which is why you design your spreadsheets intelligently to deal with that, and you sanitize your inputs and outputs. The system I described above took arbitrary error-prone source input, applied automatic corrections for known distortions (based on past data from the same source), calculated checksums to highlight new errors it couldn't automatically correct (typoes and OCR errors mostly) so that a human operator could identify the issue and apply a correction, and produced output that met the tax office's stringent requirements for accuracy. Not bad for a "mere" spreadsheet!
posted by Autumn Leaf at 3:15 PM on January 26, 2016 [6 favorites]
Many see this is an extreme viewpoint, but I generally view spreadsheets as being fundamentally and irreparably flawed for this reason. Unfortunately, we don't have better business tools, so they're pretty much ubiquitous now.
I regularly encounter two broad kinds of spreadsheets: amortization tables, and row oriented database. We'll leave the amortization to Excel, and focus on the dataset inquiry model. I think society can produce a rounder hole versus the square one Excel provides.
IMO, a workable design could be had following something like the MVC paradigm: data, view, controller. Data can remain tabluar like in Excel and every DB viewer known to man, supporting local and remote data origins, and then use customized blockly for extraction, transformation, calculation, and summarization. View layer could just pass the output to some default Jinja templates, and let advanced users override them. Or have some WSYWIG customizable system for output / layout. File format can be a zip file containing a sqlite database, and XML or something for code.
posted by pwnguin at 3:48 PM on January 26, 2016 [2 favorites]
I regularly encounter two broad kinds of spreadsheets: amortization tables, and row oriented database. We'll leave the amortization to Excel, and focus on the dataset inquiry model. I think society can produce a rounder hole versus the square one Excel provides.
IMO, a workable design could be had following something like the MVC paradigm: data, view, controller. Data can remain tabluar like in Excel and every DB viewer known to man, supporting local and remote data origins, and then use customized blockly for extraction, transformation, calculation, and summarization. View layer could just pass the output to some default Jinja templates, and let advanced users override them. Or have some WSYWIG customizable system for output / layout. File format can be a zip file containing a sqlite database, and XML or something for code.
posted by pwnguin at 3:48 PM on January 26, 2016 [2 favorites]
the problem with spreadsheets is that...it's not immediately obvious what you can modify and what you should leave alone when working with someone else's spreadsheet, or what cells depend on what.
This describes every program of non-trivial size I've ever worked with.
posted by wildblueyonder at 4:29 PM on January 26, 2016 [3 favorites]
This describes every program of non-trivial size I've ever worked with.
posted by wildblueyonder at 4:29 PM on January 26, 2016 [3 favorites]
Meh. I miss the inbuilt flight simulator.
posted by cromagnon at 6:10 PM on January 26, 2016 [1 favorite]
posted by cromagnon at 6:10 PM on January 26, 2016 [1 favorite]
If most people knew even 10% of Excels features bank IT costs could be cut by 50%.
posted by Damienmce at 6:33 PM on January 26, 2016 [1 favorite]
posted by Damienmce at 6:33 PM on January 26, 2016 [1 favorite]
This lady is my queen. "Because we are BAD AT MAKING SOFTWARE!" Yes! But it's not because I think I'm a developer, or because I think I'm better than I am, but because bosses ask for and expect incredible things to be done, have no understanding of the magnitude of what they're even asking for, and provide zero point zero zero resources to get them done except for what was included on your shitty-ass laptop when you got it. Any developer or software person who wants complicated things not done in spreadsheets anymore should get off their ass and provide something better that anyone can use. I don't even know what that would look like actually, if not a spreadsheet.
These days I'm all about Google Sheets though. So much better than Excel - better features and the sharing aspect is really great. I have used Excel and later Google Sheets to do some crazy crazy things. Access too! For the same reasons as above. Bosses want fantastical things and have no idea where they come from.
posted by bleep at 9:43 PM on January 26, 2016 [5 favorites]
These days I'm all about Google Sheets though. So much better than Excel - better features and the sharing aspect is really great. I have used Excel and later Google Sheets to do some crazy crazy things. Access too! For the same reasons as above. Bosses want fantastical things and have no idea where they come from.
posted by bleep at 9:43 PM on January 26, 2016 [5 favorites]
^ And all of this was done as the lowest ranking admin assistant, sometimes as a temp.
posted by bleep at 9:44 PM on January 26, 2016 [1 favorite]
posted by bleep at 9:44 PM on January 26, 2016 [1 favorite]
This is something I've thought a lot about, having to deal with Excel misused as a database a million times.
The problem is that any replacement will never be able to do the exact things that make Excel so over- and mis-used:
1. Everybody already has it
2. Most people at least know enough to get started, and there is always someone in the office you can ask for help. Also lots of existing info and macros you can copy and paste from the internet.
How to get around these? Also any attempt by a competing product to support the Excel file format correctly is pretty daunting. (Though maybe not as bad as Word...)
posted by thefool at 8:35 AM on January 27, 2016
The problem is that any replacement will never be able to do the exact things that make Excel so over- and mis-used:
1. Everybody already has it
2. Most people at least know enough to get started, and there is always someone in the office you can ask for help. Also lots of existing info and macros you can copy and paste from the internet.
How to get around these? Also any attempt by a competing product to support the Excel file format correctly is pretty daunting. (Though maybe not as bad as Word...)
posted by thefool at 8:35 AM on January 27, 2016
Google Docs is about as ubiquitous, particularly with the current generation of kids. They're all learning via Chrome books and Google Docs for Ed. The "post-millenials" are not an MS or Apple generation, they're a Google one in some very important ways.
posted by bonehead at 9:20 AM on January 27, 2016
posted by bonehead at 9:20 AM on January 27, 2016
But...you quoted that bit without qualification, and then, ...I...what?
that we're 'accidentally' AGI, like how the game of life is TC :P /self-referential joke!
but, to continue along the tangent! if i understand deutsch correctly (and my german is v.rusty and was never good to begin with ;) thinking about 'error' and 'abstraction' philosophically -- map/model/simulation vs. arbitrarily fine 'territory' (planck dimensions?) -- renders induction impossible; abstractions are necessarily 'error'-prone, but also simultaneously 'true', and which are hence hard to, err, model... like how you would assign priors to your conditional probability model? maximum entropy techniques? isn't that like giving up? i dunno... also btw, again iirc, deutsch thinks quantum computation is evidence of (access to) a multiverse and if that's the physical system an evolutionary algorithm runs on then maybe one way to bootstrap AGI is by making humans smart enough to understand them? or as deutsch says: "This does not mean that apes who pass the mirror test have any hint of the attributes of 'general intelligence' of which AGI would be an artificial version. Indeed, Richard Byrne's wonderful research into gorilla memes has revealed how apes are able to learn useful behaviours from each other without ever understanding what they are for: the explanation of how ape cognition works really is behaviouristic."
posted by kliuless at 12:21 PM on January 27, 2016
that we're 'accidentally' AGI, like how the game of life is TC :P /self-referential joke!
but, to continue along the tangent! if i understand deutsch correctly (and my german is v.rusty and was never good to begin with ;) thinking about 'error' and 'abstraction' philosophically -- map/model/simulation vs. arbitrarily fine 'territory' (planck dimensions?) -- renders induction impossible; abstractions are necessarily 'error'-prone, but also simultaneously 'true', and which are hence hard to, err, model... like how you would assign priors to your conditional probability model? maximum entropy techniques? isn't that like giving up? i dunno... also btw, again iirc, deutsch thinks quantum computation is evidence of (access to) a multiverse and if that's the physical system an evolutionary algorithm runs on then maybe one way to bootstrap AGI is by making humans smart enough to understand them? or as deutsch says: "This does not mean that apes who pass the mirror test have any hint of the attributes of 'general intelligence' of which AGI would be an artificial version. Indeed, Richard Byrne's wonderful research into gorilla memes has revealed how apes are able to learn useful behaviours from each other without ever understanding what they are for: the explanation of how ape cognition works really is behaviouristic."
posted by kliuless at 12:21 PM on January 27, 2016
I had a molecular biologist colleague who would do horrible things with SNP genotypes in Excel. To this day, I have no idea how he got anything productive done with that system. My technician replaced it all with about 50 lines of SAS (yes, yes, that's a whole other mess, but you got to war with the language you know...)
posted by wintermind at 6:19 PM on January 27, 2016
posted by wintermind at 6:19 PM on January 27, 2016
« Older Have You Tried Just Holding Your Breath? | Dekoven Presents: Super OTW! Newer »
This thread has been archived and is closed to new comments
posted by clawsoon at 8:06 AM on January 26, 2016