I’m often faced with the need to do a one off crunch of data to provide answers to questions management ask about raw data. Not the kind of thing they’ll be asking for on a regular basis. Just a need to scratch an individual itch. One off reports on specific aspects of code metrics. Calculate some predictions of data growth in the application across different aspects of it’s user base.

On these occasions, I either turn to Query Analyser to mine our SQL Server databases directly, or use the data import tool in Excel and try and crunch the data swiftly in that. Sometimes, those needs become a long running need to manage some data, where Excel is often the preferred format, because I can do some initial crunching and manage the data in there and the rest of management can then take a copy and further manipulate it and play with it to get additional information as and when it occurs to them they need it.

The problem I face is that Excel is designed for accountants and management types with no programming knowledge to manage spreadsheets of data they understand. It’s too damn user friendly. I find it very hard sometimes to find a good way to manage my data in Excel. I often throw my hands up in dispaire and lash up a software tool specifically to manage the data. I’m talking about a full on database driven web application in most cases. It’s so much faster for me to work with the data that way, and I can then use the Data Import tool in Excel to shove the data in raw forms into spreadsheets if the people asking for the data want to take it away and play with it.

There has to be a better way. There has to be a more productive way for me to do this. A more developer focussed tool for doing this, that allows you to achieve with scripting/programming what you would achieve in Excel by mucking around with excessively user friendly wizards and obscure dialogue screens.

John Udell thinks the answer might look something like Resolver, which is a new spreadsheet application written in Python that allows you to use Python directly in cells and to have full access to .NET and IronPython through the whole application.

This just seems to be the wrong solution to me. With Excel we have a spreadsheet product that is so good it’s destroyed all competition that non-programmers use and love. It can be extended by programmers with add-ins and macros. You can write .NET code or VBA code (easy for non-programmers to learn) in the Macros etc. However, the formulae are restricted to the old style “icky” functions. Stick=if(condition,forumlae,formulae) in, which just makes programmers recoil from the keyboard in horror.

The right answer to the question is to have a simple option to enable direct access to the .NET runtime in cells. Then people can code formulae in any .NET enabled language they choose, including IronPython.

Do not throw the baby out with the bathwater.

Popularity: 72% [?]