Fog Creek Software
Discussion Board




When is a spreadsheet not enough?

This is strictly a hypothetical question that I was curious about, and this forum seemed to be about the most diverse place I could think to post.  I wish to preface this by saying that I'm not trying to preach in favor of any language or dialect, any recounting of facts is simply how I went about doing it.

I was tinkering around, for fun, one day, trying to understand how to translate numbers into dates and then back into numbers.  For example: July 12, 2003 is the 193rd day of the year.  Granted, I know there are libraries out there to do this, but it was fun and I'm a coder... I'd hope people here would understand. :)  So, I got this to work in an Excel spreadsheet(1) after a bit of kludging, and decided I wanted to see how well it would translate over to CLISP.  My thinking at the time was that the syntax of Lisp was sort of familiar to standard spreadsheet calculation functions.  I did it(2), and it worked.  In doing it, though, I realized that while I could do this in Excel functions, doing it in CLISP made it much more powerful and I could ultimately expand it to calculate numbers in the thousands, figuring out (for a small example, starting in 2003) that the 366th day is actually January 1, 2004.

So, I'm curious, when does someone's spreadsheet, the individual Lotus or Excel file not the application itself, reach the point that it warrants becoming its own application?  Do things like spreadsheets ever reach this point?  Has VBA eliminated this threshhold or pushed it back any?

This is a very hypothetical question, and I offer apologies if it's too cartoonish.  Thanks.

Andrew Burton
Monday, April 21, 2003

I forgot to post my footnote links.

1: http://www.darkbeast.com/files/daycount.xls

2: http://www.darkbeast.com/files/dates.lisp.txt

Andrew Burton
Monday, April 21, 2003

Well, I am not going to say this is lame, but I don’t see the reason why you would not use the built in functions to manage this date calc? That to me is the mystery??

For example, you re-create the whole functionally you have with ONE LINE of code in Excel.

Simple create 3 cells, they are

Start Of year                Days To Add            Result

For start of year, simply place:
=DATE(YEAR(TODAY()),1,1)

For Days To Add, we need nothing

And for result, we place:

=mydadd(A7,B7)

MydAdd is going to be a custom function we place into the Excel sheet. Whack alt-f11,  insert a module, and put in the following code:

Function mydadd(dtStart As Date, lngDays As Long) As Date

  mydadd = DateAdd("d", lngDays, dtStart)
 
End Function

That is it. Not only is the above 1 line of code, but it correctly handles numbers far greater then 365. Hence, we get full leap year control. We also do not have anything hard coded, and your sheet has all kinds of hard codes in it all over the place.

So, at the end of the day, I get more functionally with one line code. To me, this all about using a hot knife to cut butter. While it is interesting to spend time on the lake in a sail boat, often it is better to get the speed boat out, and really do some damage on the lake. 

For me, it is all about maximizing a quality solution in a given amount of time.

Now, back to your question:

So, do spreadsheets actually mean they are a applications of their own? Sure, and with a nice language like VBA behind the scenes, you can even create class objects to tackle some fairly complex applications (I suspect my lack of Excel skills make me resort to a code function, when probably none is needed).

So, I don’t think the question should be “when” does  Excel  become an application, but when one should use it, or when one should not.

One can write a packman game in excel if you want…but it probably is not the best choice.

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Monday, April 21, 2003

That's interesting, one of my day jobs is porting applications developed in excel to lisp. ;-)

"So, I'm curious, when does someone's spreadsheet, the individual Lotus or Excel file not the application itself, reach the point that it warrants becoming its own application? "

In my situation, people do this when they need to have a coherent and safe way of storing the analysis they are doing, and when they need to share the application with others. I work with medical researchers who develop an experimental model in excel, then want to share the model with others. We then move the model to a web-based app  (created using newlisp + postgres, if you are interested) so that other researchers can use the model. Postgres takes care of all the data integrity issues as well as keeping a history of all the experiments. We use lisp for the modeling code as well as rendering the web-based UI.

" Do things like spreadsheets ever reach this point?"

Yes, as explained above.


" Has VBA eliminated this threshhold or pushed it back any?"

Yes, you can get very far with VBA and excel. Quants on wall street often do all of their work this way.

choppy
Monday, April 21, 2003

"When should a spreadsheet become an application?"

1) When you find yourself frustrated by something the spreadsheet can't do
2) When you start thinking procedurally instead of "equationally"
3) "Hey, can I get a copy of that?"

Philo

Philo
Monday, April 21, 2003

Albert, I specifically didn't use the date funtion for two reasons:

1. I didn't know about it. ^_^;;

2. I was mostly interested in learning how the process worked.  I was prototyping in Excel then going to take it to take it to Perl.  However, this was before someone told me how to get the same results with one line of Perl.  Oh, the irony.  I will, however, stand by the fact that I do know how the procedure works now, and could reproduce it in most any language if needed.  (My ego demands I salvade some dignity from it all.

And to everyone, thank you for the answers.  They've answered my question greatly.  I do appreciate it.

I love this place.

Andrew Burton
Monday, April 21, 2003

Your questions are ones often asked too late, so it is good to see the thought come early.  Remember that when you need it for real.  Departmental solutions which become "enterprise critical solutions" have a way of sneaking up on people.  It was just "back office" until it died and they could not bill. 

So criteria to consider:
- If it is mission critical and must be shared with several people or locations.
- Real future growth is being seen. 
- The data being analyzed/reported/captured is taking too long to process.

I think one of the biggest mistakes I see is the desire to create an application before the spreadsheet/database/word document meets its end.  Too often people spend/waste money automating something or converting to an enterprise system, where the return is so minimal that it will take a life time to realize.  So if your spreadsheet works, and you can keep it safely backed up, keep it.

Mike Gamerland
Monday, April 21, 2003

When an accountant has a great idea to grab all the rows out of a 3 million row table and drop them into excel for some ad hoc analysis.

Mike
Monday, April 21, 2003

"When an accountant has a great idea to grab all the rows out of a 3 million row table and drop them into excel for some ad hoc analysis."

You could use an Excel plugin for an OLAP database to reasonably accomplish this.

ODN
Tuesday, April 22, 2003

You could, but the accountant will use cut and paste.

Richard Ponton
Tuesday, April 22, 2003

"when does someone's spreadsheet, the individual Lotus or Excel file not the application itself, reach the point that it warrants becoming its own application?  Do things like spreadsheets ever reach this point?"

More frequently than most people, especially the spreadsheet jocks (which I consider myself to be), would think.  A spreadsheet is a very flexible tool that can be mis-used like any other tool.  VBA both helps and hurts. In cases where the spreadsheet is the appropriate tool to begin with it helps. Otherwise, it just compounds the problem.

"I can do that in Excel!". Yes, so can I...but I know better.

fool for python
Wednesday, April 23, 2003

*  Recent Topics

*  Fog Creek Home