Monday, March 4, 2013
The Importance of Excel (commentary)
http://baselinescenario.com/2013/02/09/the-importance-of-excel/
This article is a nice summary of the advantages and pitfalls of Excel by James Kwak. It focuses on the financial world, but the lessons could apply anywhere. It’s not going to solve any problems, but it’s still a good reminder of things to look out for when using a tool like Microsoft Excel. This article is part of my recent idea to save short articles for later when I have some quiet time.
I have to admit I use a spreadsheet all the time for various debugging needs. I can store some calculated data, summaries, grouping, etc. I recently used it to prototype a simple algorithm I needed to build an Android app. It turned out that my original "this is perfect" algorithm fell flat on its face when I decided to piece it together in Excel and ran a bunch of numbers through it (Fill-Down is SO powerful). I'm glad I decided to try it out there before discovering there was a problem after building the app itself.
I've also seen the dark side of using a spreadsheet for important work. The article mentions it and I have to agree, I think every SAP project I've ever been on has some component of it dedicated to getting data out of SAP and into a spreadsheet.
One SAP project I was on I had to create a long list of macros in Excel (VBA: Visual Basic for Applications) to achieve a task. As I propped up more parts of the application, the more I had to rework the functionality into easy to digest functions with tons of in-line documentation. You can find lots of articles stating your code should document itself without lots of comments, but I assure you when you are a consultant on a project who is going to hand an application to someone not as technically skillful, your only hope is that they will ramp-up slowly by reading your over-documented functions.
Anyway, my write-up is almost as long as the article itself! If I were to say anything constructive, and really I'm nitpicking, but I feel he takes unnecessary stabs at Microsoft software as if trying to appease some underground software zealots.
http://baselinescenario.com/2013/02/09/the-importance-of-excel/
Subscribe to:
Post Comments (Atom)
think Excel gets trotted out in a lot of places where it's not the best solution, just the easiest hammer within reach.
ReplyDeleteFor example, when you say you write calculations in a spreadsheet, I think: Why not TDD? Or at least Unit Tests? Here's my logic:
When you're doing calculations in VBA in a spreadsheet, what if you could apply those calculations in stand-alone code, and then do the comparison to the 'expected' value in the code as well?
And then run those tests every time you change something?
And read these running, working tests in the future when you're trying to figure out what's gone wrong, or what the original author intended?
Offhand, I don't know if the VBA from excel can be extracted as a library: that might be a special case, but if you're really doing a lot of sophisticated work in there I'd hope there's a way to stay DRY. And if there is, you can test it.
Was there a reason you didn't go with a test-based solution here?
I think a version of your first sentence would make a nice byline summary for the article.
ReplyDeleteAfter your first sentence, it seems your comments are mixing up my examples. I was just providing some personal experiences (good and bad) related to the subject of the article.
As far as using Test Driven Design in a VBA project, there are unit test frameworks that will allow you to build tests for Excel/VBA. While none of them were available back in 2000 when I was on that particular project, it’s not like I didn’t have my own test scripts available to help me.