Excel and statistics

This spring I worked through an entire statistics class in Excel (well, mostly in Excel). This is mostly because I somehow never actually learned any statistics in college and still have to understand the concepts for translation, but also partly because I've done some other math-oriented stuff in Excel and wanted to learn more about that.

Here's the thing: like many Microsoft tools, Excel can be incredibly powerful - as long as you're using it the way its designers conceived. Go off the reservation and abruptly things get very difficult.

But it's worse. Turns out that specifically for statistics, Excel kind of sucks. Like, wrong-answer-level sucks. The recommendation of the article in the link: use R (or some commercial statistics package).

So here's an interesting idea: wouldn't it be interesting to build a spreadsheet tool that translates to R behind the scenes? Plus has somewhat better handling of tabular data? (That part drives me crazy in Excel: I have to know the shape of my data in advance.)

There might well be such a tool already. If not, it would be fun to build it.

HNN thoughts on declarative specification of Excel

Just a note for later. I've been using a lot of Excel lately - it's a great tool, as far as it goes, but when you reach its limits, you've just plain reached them. There are some interesting points made in this HNN thread, some of which I'll enumerate at a later date.

Using a spreadsheet as data for templating

The Python copytext module takes a spreadsheet and loads it as a data structure for expression in a template; that's kind of a neat ... I'm trying to think of the phrase I want to use ... "dataflow component" seems to be as close as I can come tonight.

Anyway, this is kind of a neat idea and is probably a way forward for the Data::Table::Lazy module. Note also that D::T::L should directly know how to work with Excel and with Google spreadsheets for maximum fun and profit.

Spreadsheet in JS

Tiny little thang.

Optimization with the Excel solver

This is a fascinating little article about how to set up and solve optimization problems in Excel.

Tangle.js

I'm just fascinated by interactive documents and how they compare to Excel and that sort of interactive dataflow management tools; Tangle.js has been around for a while but it's still incredibly cool. [hnn]

DataNitro: Python from Excel

Neat little article using DataNitro to link SQL databases to Excel.  Neat.

The future of Excel

An interesting article, the key insights being: Excel is important to a lot of people because it lets business people do the modeling, you hit a limit pretty quick, VBA is not the answer because it's underpowered, real programming languages are not the answer because the model isn't useful to the business person, there may be a path forward in integrating Python and other open-source tools directly into Excel.

Or, you know, adopt the Excel paradigm as one front-end for a larger open-source project; that could probably be done in Wx without a whole lot of bending over backwards.

Excel and its ongoing importance

Here's a fascinating look at Excel and the fact that its enables people to make programming errors that can bring down financial giants.  HNN thread and one man's way to convert the knowledge in Excel spreadsheets into more conventionally (and safely) maintained software.

There's a big market for recovering and formalizing knowledge currently encoded in amateur spreadsheets.  It would be a reasonable thing to investigate.  Not sure how best to market it.