In a previous post,
I showed a way of automating and/or simplifying Excel by processing
many data records through a single sheet using data tables, rather like
a merge. If you liked that, you should love this more advanced tool,
written in VBA.
It is generic enough to be used on a wide range of projects without
modification, so I'll call it an automation "engine" below. If you find
it useful, it is free to use and modify, providing you don't claim
undue credit or sell it.
It was built to power an actuarial valuation system which can be
extremely complex, because each client implementation is unique and has
to be configured separately. The complexity and non-standard
configurations make checking very difficult, so this has been a good
test of the software.
Importantly, the engine allows every single formula to be laid out
clearly and simply on worksheets, and checked step by step at every
level, from the lowest level to the highest. So if you want, you can
see all the calculations for product X, in a specific future year Y,
laid out in detail across a whole sheet. It also means non-experts can
configure the system entirely in worksheets, because the engine only
provides automation and doesn't contain any business logic or client
The engine also allows us to nest as many calculation loops as we
like, eg we can run 6 scenarios, each involving processing 1,000
products, each of which requires 15 future years of calculations, each
of which requires several calculations - all by pressing one button.
All of this made our system simpler, much safer, more accessible and
highly flexible, which is exactly what we needed. The only downside is
that it isn't as fast as a pure VBA program, but it's still fast enough
for us, and a small price to pay.How it works
I'm going to pitch this explanation at the level of experienced
business spreadsheet users who, like myself, work with nasty
spreadsheets and who are motivated to automate or simplify them.
The section below introduces the engine, then I have a workbook which
explores its features step by step.
The engine is like a souped up data table. It puts a sequence of
numbers in a cell (ie 1, 2, 3,..), calculates, and stores results
chosen by the user. However, it can nest calculation loops, so that for
example, each time sheet A calculates, sheet B will calculate Y times,
and each time sheet B calculates, sheet C calculates Z times. This
means that sheet C will calculate Y x Z times as often as sheet A. If
you are familiar with programming, we are talking about nested
For...Next loops and subroutines, within worksheets, which is something
I've always wanted in Excel.
Each calculation loop requires its own worksheet,
which has special range names that tell the engine what to do on that
sheet. For example, there are range names that tell the engine
how many times to loop through this sheet (can be a formula)
the name of the child sheet, if any, which should
be run every time this sheet runs (that child sheet will then have its
own fields which tell it how many times to run, etc) - this is how the
loops are nested
what results to store
and more. It can also keep running totals of results, and copy hardcoded results to separate sheets.
The code engine comes with a worksheet template with
about 10 named cells which are all you need to configure your system.
You should not have to touch or even look at the code.
It's hard to explain it in much more detail without looking at it, so I suggest you do that now. I've provided 3 files
- explanation of the features with several examples using a simple financial model (Engine Demo.xls)
- a non-financial example showing how to solve a word puzzle game (WordPuzzle.zip)
- the template for your own use (Template.xls)
The documentation is not as complete as I'd like, but please bear in
mind this is all done in my spare time. Comments and suggestions are
Jan 28 2008, 09:26 PM