Spreadsheet Model Automated Testing

February 26, 2015 5:13 AM

I'm looking for recommendations on software or practices to do automated testing of spreadsheet models.

I've been doing a lot of work on big big spreadsheet models.
They tend to be huge complicated things with database backends and multiple books doing different things. Although we conform to good auditable standards what I am looking for is automated testing.
We use test driven development for data warehousing stuff, and increasingly (although not as much as we should) for other software development.

Although not one of mine (and the kind of internal checktotals we use would have totally caught this), but the famous austerity is good error is totally the kind of thing we could do and I want to ensure that we don't.

posted by Just this guy, y'know to Computers & Internet (2 answers total)

I've heard of people hacking together Excel testing processes by using testing frameworks from various programming languages (python, ruby, java) and using Excel connection libraries. It's hacky as heck, but it should work. You could also probably build something out of VB. But I don't know of anything offhand (or from some quick googling) that's the "cucumber of excel" or the "junit of excel;" by the time things get that complex it's advisable to do your development in a programming language and test that, with the spreadsheet cut out entirely or switched to being purely a front end.
posted by Tomorrowful at 6:24 AM on February 26, 2015

The JUnit equivalent is VBAUnit.

However, doing real modeling with spreadsheets is, at least in my area, widely considered a Bad Idea, as described in various postmortems of the error you reference.
posted by wnissen at 10:10 AM on February 26, 2015