Jon Udell on the Nerd's Spreadsheet

rcs1000 writes "Jon Udell has a interesting article on a new type of spreadsheet: one targeted specifically at techies. The skinny is that any spreadsheet is actually a computer program, only in Resolver One, the product profiled in Udell's piece, this is explicit rather than implicit. And the code is IronPython rather than VBA. There are some other cool things it does — allowing cells to contain objects, and allowing spreadsheets to back-end websites." Udell's screencast gives a good demo, though the presenters are a bit hard to hear due to the phone connection. Resolver's own screencast is an alternative.

Can it...

Multiply 850*77.1 correctly?

Logical conclusion

Oh jeez, this reminds me of the last company I worked at, where they tried to do *everything* in spreadsheets, even documents that would have been much better in word processors or databases.

Re:Logical conclusion

Life is a grid with a logic tree, dude.

It's called Tetris.

The old saying is true.

Everything looks like a nail when all you have is a hammer.
Spreadsheets are so useful today that they can do many tasks that are better done with other tools... If you know the other tools.

Why should I use this rather than SQL?

SQL databases have become much lighter and more efficient these days. Why should I use this store data over a lightweight SQL database?

Re:Why should I use this rather than SQL?

i don't think its a matter of storing data (or at least large amounts). thats never what spreadsheets were for. they were based more around displaying data and processing data. yes, they can be used for large amounts, but they never really were meant as storage in the same way a database was. they're just saying these spreadsheets could start serving some of the same purposes.

Re:Why should I use this rather than SQL?

Why use a database or spreadsheet? Why not something like the R Project?

Because you have to learn yet another cryptic set of functions to do what is easily accomplished in Excel (or any other spreadsheet)? Most people couldn't give a fuck less about using any package to its full potential and most people utilize Excel as a database rather than a spreadsheet (almost everyone in my wife's company for example).

A spreadsheet will do just fine for the majority of people and the rest would probably use something like Crystal Reports to do anything more advanced. Why? Because there is professional documentation and training available for those packages and R Project requires posts to mailing lists or forums to get answers outside of your own self research on the web. From what I can see in my own personal experience, people working in the real world don't want to spend the time searching around the Internet through mailing list posts and forums for their answers. They want to plunk down $350 and sit through a 6 hour seminar offering them 1 CEU.

YMMV.

Solution looking for a Problem

In a Resolver spreadsheet, these objects are visually persistent. I haven't yet got my hands on Resolver, but here's an example of what I think that will mean. Suppose that I have a data set I want to transform, against which I'm testing five different versions of a transformation function. I'd put the data in cell A1, the functions in cells B1..B5, and the results in C1..C5. Now I'll see everything at a glance.

That . . . sounds just like a normal spreadsheet to me.
Solution looking for a problem?

2D programming?

Organising code on a spreadsheet... I guess it will resemble Befunge [wikipedia.org]

Allowing spreadsheets to what?

and allowing spreadsheets to back-end websites

munge them?
hack them?
copulate with them?

Misuse of spreadsheets

A good portion of spreadsheets actually should be database tables of some kind. People end up manually grouping and other stuff that report-writers can do automatically. What is needed is a kind of "dynamic" RDBMS tool that has open-ended columns and column widths. A "spreadbase"? The Oracle clones are all too rigid.

As far as spreadsheets for programming, I've experimented a lot with data dictionaries to simplify column management and column sub-sets for regular ol' edit-and-report screens. So far it is tricky because one often wants to tweak something for a particular context and one-size-fits-all hits a wall. The trick is finding a good, clean way to "override" specifics from the table when needed or just make alternative entries of a given column and select them via set notation when needed; but I've yet to find a clean, simple convention. It ends up fairly messy such that regular copy-and-paste is unfortunately the cleaner solution much of the time. Maybe if the toolset and the language was geared toward nimble data dictionaries, these approaches would be smoother. Forcing a non-data-oriented language to act data-oriented is like trying to keep a toddler in line.
       

Re:Misuse of spreadsheets

"A good portion of spreadsheets actually should be database tables of some kind." Databses are good for storing data and spreadsheets are great for analysing and presenting data. Resolver works very well with databases and so makes it easier to keep your data there - and still have a powerful analysis / presentation tool.

Just found a new way to sleep at work

Wow... that screencast is perfect for me to sit facing the screen with my eyes closed, and anyone that walks past my cube will think i'm doing some spreadsheet wizardry....

nice. Now i'll go someway toward meeting the quota for those that sleep at work [slashdot.org].

Spreadsheet, Or...

There are some other cool things it does -- allowing cells to contain objects, and allowing spreadsheets to back-end websites.

Is this a spreadsheet, or a poor man's database?

Does it run on Linux?

Seems like a fairly on-topic question. Anyone know the answer?

Hex handling

Importing and manipulating hex would please me no end.

H.

Great Idea

This is a really interesting idea. If you think about it, every spreadsheet is a computer program. Excel is probably the most widely used development environment in the world. And it sucks. Big time. Watching the screencast, it looks like this is a great halfway house between bad, ad hoc, spreadsheet development, and a traditional IDE. The choice of Python as the language is a great idea - it's much, much easier for someone than VBA, and it's much more semantically powerful. I'd be interested in discovering whether it works existing Python and .NET libraries.

I *think* it's gonna be cool

[quote]I haven't yet got my hands on Resolver, but here's an example of what I think that will mean.[/quote]
I said "WTF?" and stopped reading right there.

Real Interesting point

is that this is the first time i am seeing a demo developed in silverlight. Yeah, i know, there are lots of demos on MS site. But this is the first time i am seeing somebody using SilverLight for some real demo on their own site. is this the sign of things to come? Time will surely tell

Seems kinda cool to me.

I had an interview with these people about 18 months ago (didn't get the job) but I thought there product was pretty cool. Its basically a simple spreadsheet that allowed you run an IronPython script in each cell. So you can run your spreadsheet with all the power the Python and .NET can give you ... say you wanted your spreadsheet of investments to update against a stock exchange web site every 30 seconds

Lotus Improv / Quantrix please

I never, ever want to see A1..A10 again.

Give me an item dispenser, the program should name things sensibly as they're created and all formulas should read as plain text like: profits = sales - expenses.

Please.

William

Windows Only

From the website:

Please note that Resolver is currently a Windows-only application . While we have no immediate plans to produce versions for other platforms, if there is enough interest then that will change.

There may be some exceptions, but I haven't found any... Once a bloger, journalist, programmer, and I'm sure lawyer, goes to work for "the Borg" they lose all sense of objectivity.

Why in this day and age would someone want to lock themselves into yet another Windows-only application?

I would avoid this thing like the plague. Even if Linux hasn't taken over the desktop, there are certainly enough Apple users these days that OS portability should be BUILT-IN to the design process from day one.

The whole point of an OS is to isolate the application from the hardware. But what good does that do us when so many new applications are designed to work with only a single OS, and that single OS is designed to work with only one type of hardware? Has our industry gone mad?

A Better Headline

"Spreadsheets with First-Class Functions and Objects"

This is just a spreadsheet with the ability to put functions and other objects into cells. It's a good idea, but there is no need to dress this up in marketspeak. We understand techno-babel just fine here.

homebrew

Not reading TFA, it reminds me a bit of my custom db/ui solution that I use for generalized information storage: links, books and movies I watch, developer notes, etc... it some web-based Perl CGI and is, in effect, a big old flat table database, the columns defined by text files, and it auto-generates a convenient form with the usual spattering of HTML input types...

The Analyst: the first Nerds spreadsheet

There was a Spreadsheet made for the CIA by Xerox that would easily be the Nerds Nerd of spreadsheets as it enabled full access to the underlying programming language, Smalltalk.

Here are some links to the old version and newer developments.
http://wiki.cs.uiuc.edu/VisualWorks/The+Analyst [uiuc.edu]
http://www.mojowire.com/TravelsWithSmalltalk/DaveThomas-TravelsWithSmalltalk.htm [mojowire.com]
http://www.sunless-sea.net/wiki/SmallTalk [sunless-sea.net]
http://www.google.ca/search?num=100&hl=en&newwindow=1&q=%22the+analyst%22+xerox+smalltalk&btnG=Search&meta= [google.ca]

Hockey stick curve?

No matter how good a tool is, it's only as good as the raw data.

1. $500 per month in sales after 6 months.
2. ???
3. Profit! $320,000,000 per month after 12 months.

These people need a reality resolver instead of a spreadsheet replacement.

VBA already does this...

And does it poorly. And insecurely.

You can already access spreadsheet content from Visual Basic, and include VB script in spreadsheets. The same scripting ability which allows the "wow" features in spreadsheets also creates the potential for abuse - remember macro viruses? Suddenly, documents which formerly contained only data now contained executable code, and it gave rise to a security nightmare.

Yes, today, with VBA, you can do what the article mentions. In fact, it's been possible for years. Problem is that:

1. Very few people use it, and
2. Those who do use it tend to use it poorly.

Yes, you can back end a website with your spreadsheet. But why would you? A spreadsheet is a horrible way to manage data; there's no referential integrity checks, no versioning, no security, and doesn't scale well. Furthermore, your crucial data is tied to a particular application, rather than a database.

Just like VBA, it's a nice nerd's toy, but the wise system programmer recognizes that it has limitations.

And for Gantt charts...

And for Gantt charts, there's a program which lets you express each task in python code (including whatever calculations, remote data, or whatever that it needs to get data from)

http://faces.homeip.net/ [homeip.net]

they have their work cut out for them

and here is why, even moderately smart programmers are more comfortable with a spreadsheet than a database or full blown application. Example which I believe still applies today:

I was a member of a "club" of techies who were investing in the stock market in the 1996 timeframe. We cycled the clubs officer positions through the club membership so everyone knew what others were doing and how. The 2nd year, I got the Treasurers position and my job was to collect "investments" at each meeting and provide a overall club report and reports for every member's position/investment in the club. I was handed a spreadsheet with tons of data entry cells sheets to fill in data. I could have cleaned this up but hey, this was the age of the internet right. So I build a SQL database to handle the accounting, wrote code to drive a CGI interface to a web server and wrote scripting to trigger updates on stock valuation and triggers to update the database on changes. It was all accessible from a web browser with a login. And very repeatable. It worked great after the 3rd months meeting and for the remainder of the year. Because it was cross platform, it would run on anybodies computer and did not need to be on the internet.

Here's the kicker( finally ). The next Treasurer was the guy who started the club, owned his own business, was a Windows software engineer who's product he built over close to 10 years and was a signal processing kit(PC and software appliance ). He knew what I was saying when I described the package I'd built to do the books but he wanted none of it. He wanted the latest report and then he write a spreadsheet to handle the task. And for every meeting from then on until the end of the club 2 years later, we were constantly correcting data in the spreadsheet and sometimes, corrections required pulling out paperwork from previous months meetings.

There is a fools familiarity with spreadsheets which people find comforting. They will go to great extremes to try and get the spreadsheet to work for what they are trying to do. Great extremes. And with the plethora of Neanderthal-ish computer users out there who think they are computer guru's because they can do "=(A2+B2)" in MS Excel, it is a long long road to get movement outside of the spreadsheet.

I just found out a buddy has taken on a 2nd job and is trying to build a business out of it. He's under charging to get experience in more aspects of the field and to construct a spreadsheet to "automate" the task(s). He's thinking that the business will grow around the "application"/spreadsheet.

So I think the title should be, "Jon Udell on the Geek's Spreadsheet" because the nerd is really not that 'into' the technology to really know or want to work outside of a standard spreadsheet. IMO.

LoB

Something like MS Access?

I've always felt that MS Access is a really shitty implementation of a really good idea. Yes, it was backed by an SQL database, but you can do spreadsheet stuff, too, and behind the scenes you could tie it all together and make it look nice. Another nice feature is that managers, with no programming experience and just a bit of SQL knowledge, can create and generate their own reports without having to bother a developer. Alas, the SQL engine has many fatal flaws, the scripting language is junk, and the GUI is just too quirky and weird. The report stuff is pretty nice, though.

Unfortunately nobody else has ever tried to do it right. The pieces are all out there, but they're not integrated.

I may be a diehard Red Sox fan, but I still cheer when a Yankee makes a good play.

Not much new for Openoffice users ...

Python scripting was possible for a long time for Openoffice users ...

Spreadsheets Cause Brain Damage

Like other posters said: half the time they're busted, denormalized lame-ass substitutes for databases. The other half of the time they lead to convoluted algorithms that would be expressed more simply in code. Spreadsheets are the shits-- logic and presentation layer tightly coupled. The only value they give is in sortable and filterable views, and grid controls on a webpage will do that for you nowadays.

Scheme on a Grid, anyone?

I am wondering if we have forgotten this cute little app... Thwe webpage says
2000-12-07, but I think I've played with it long before that. And yes, it had
database connectivity, could serve data over HTTP and, of course, the extension
(and half of implementation, I'd guess) language was Scheme.

http://siag.nu/siag/ [siag.nu]

Paul B.

InstaCalc

(shameless plug)
I'm a nerd as well, and built an online calculator/spreadsheet for myself. It's a new take to an old problem: instant answers, inline units (MB, GB), and shareable results. Yes, it handles 850 * 77.1 and more [tinyurl.com].

Thought you might find it useful.

"Nerds' Spreadsheet"?

Wolfram [wolfram.com] called; they want their concept back.

It isn't as useless as you think

The idea reminds me of "Cells" by Kenny Tilton. From the site [common-lisp.net]:

Cells is a mature, stable extension to CLOS that allows you to create classes, the instances of which have slots whose values are determined by a formula. Think of the slots as cells in a spreadsheet (get it?), and you've got the right idea. You can use any arbitrary Common Lisp expression to specify the value of a cell. The Cells system takes care of tracking dependencies among cells, and propagating values.

Resolver seems to take this idea a step further. It looks like you can write nice reporting tools with this. There is no need to bash Resolver because you don't like Access or Excel.

Distributed, Extensible Spreadsheet

Here's a similar proposal for distributed, extensible, language-neutral spreadsheets [livejournal.com]! Someone at Google, please implement this? Cheers!

from the web site

Every time a user changes a Resolver spreadsheet, the software generates Python code expressing their desires;

Oh yeah? How does Python express hot, naked babes and a sixpack?

Smart, but Don't Get Things Done

Hilarious! Joel wrote flippantly about this exact intellectual exercise here:

http://www.joelonsoftware.com/articles/GuerrillaInterviewing3.html [joelonsoftware.com]

People who are Smart but don't Get Things Done often have PhDs and work in big companies where nobody listens to them because they are completely impractical. [...] For example, they will say, "Spreadsheets are really just a special case of programming language," and then go off for a week and write a thrilling, brilliant whitepaper about the theoretical computational linguistic attributes of a spreadsheet as a programming language. Smart, but not useful."

I'd like to think my project inspired them...

As someone who wrote exactly this kind of thing [sourceforge.net] for Java, I can attest to the idea that it can be useful and not a security issue. The trick is to think of its uses in a completely different way than what we tend to associate with Excel. The way I use Bean Sheet (my programmer's spreadsheet) is in the following ways:

- To hold and manipulate small amounts (usually sub-100,000 rows) of formatted and structured data.

One thing I've never seen anybody do in Excel is script your own sorting algorithms for example.

- To have a format compatible with a version control system so as to see diff's in simple, but formatted, lists of data.

- A more visual way of modeling/testing/debugging API and components.

- To allow programmatic access to the spreadsheets.

This is what this guy was talking about as a good opportunity to seed quickly changing business rules into an otherwise static system. Suppose you have a piece of code that needs to perform some business-rules driven operations on a data set. You can encapsulate those business rules in such a spreadsheet containing no data, test it, and deploy it with your application. The app then loads the data into the spreadsheet (the spreadsheet might even contain information on where to put the data set into it, so the interaction can be quite generic) and the app then queries the spreadsheet's "result" cells. And since these kinds of spreadsheets can contain all manners of data types, integration can be really simple. Then, if need be, pop open the spreadsheet, tweak the business rules, and deploy it back into your app -- perfect component-oriented programming model implementation

.

Re:Python

Neither is VBA ;)

Re:What's the difference?

Aside from having a console window for custom formulae and a debug output window, it is not that much different from a typical spreadsheet program. This can easily be built on top of existing office suites, if they haven't include them already.

While I haven't used this program yet, I believe the major difference is that it allows you to declare new classes and use values of those classes as cells in the sheet. This sounds remarkably useful to me.

Imagine having a CurrencyAmount object. A1 contains =CurrencyAmount(50.00, "USD"). B1 is =A1.ConvertTo("GBP"). CurrencyAmount includes the code to look up the exchange rate on a web server, so whenever you click recalculate you get an up-to-date sheet.

Also consider other cell types you could use: complex numbers, vectors, matrices, value with unit: A1: =UnitValue(50,"meters") B1: =UnitValue(17,"seconds") C1: =(A1/B1).In("miles per hour")

Re:Python

IronPython is not a language!

Actually, I believe it is. There are substantial differences [codeplex.com] between IronPython and the reference Python implementation. C++/CLI is a language, IMO, and so is IronPython.

Re:What's the difference?

Possibly, but we think there significant differences in our approach Resolver expresses a spreadsheet as a Python program. The code pane shows the entire spreadsheet as an executable Python program. User code, data and system code are cleanly modularized. The result, in our experience, is dramatic improvement in programmability and modeling power.

Certainly a nice console window and debug output window would improve, in our opinion, most existing spreadsheet applications. However, this will not fundamentally change the interaction patterns between the cells, user code, and application code, nor the architecture of the application. One of the reasons why we can deploy Resolver spreadsheets to the web so easily is that they are Python programs, one of the benefits of this architecture.

The nightmare of auto-generated code

One thing that is different is that it auto generates python code that fully describes the spreadsheet (structure, display properties, and cell values!) - and which apparently is then executed to actually draw the spreadsheet itself (kinda a weird bootstrapping thing going on there).

Anyway, I wonder how well modifications you make to the generated code are treated by changes made to the spreadsheet itself...
Anyone who has tried to hack auto-generated code - e.g. from UI code generators (VC++?) knows how ugly this can get. Maybe with the closed loop (from generating code, back to displaying the spreadsheet) they have managed to deal with this, but the screencasts only show such trivial examples of editing so you have to wonder.

The thing that seems REALLY cool about this tool (and the real answer to your question I think), is that you can take this auto-generated code + your custom code, and drop it into a .Net app as is, and you have a powerful python-powered spreadsheet engine inside your custom app. This means you can give your users a familiar spreadsheet UI as part of a larger app where appropriate, with all your business logic, without the unconstrained craziness of an excel VB App. Nice!