How and why I built How I built an automated testing harness using Excel

About me

Software architect, developer and speaker for almost twenty-five years.

The problem I wanted to solve

I was designing a very financially complex stock grant modeler for the highly-compensated employees a large insurance firm - it involved hundreds of very complex financial calculations which needed to be verified.

What is How I built an automated testing harness using Excel?

I had an actuary create a complex Excel spreadsheet which served as the specification for the system, and then created a unit test framework that could consume and invoke the calculations from the sheet, and compare them to the results that the system produced.

Tech stack

I used C#, MSTest, and Excel.

The process of building How I built an automated testing harness using Excel

The sheet had a raw input data sheet, a calculation sheet, and a sheet where the results were displayed. I wrote code to capture the raw input data from the sheet and feed it as inputs to the system I created, and then wrote code to invoke the calculations, which I consumed as an expectation set for my unit tests.

Challenges I faced

Given the complexity of the sheet, certain errors were sure to arise, and tracking down the errors IN THE SHEET was very complex and tedious.

Key learnings

By approaching things this way, I was able to test the code in thousands of variations without writing thousands of lines of code. By the time the tests were complete, I could say with certainty that the calculations of the system matched the calculations in the sheet.

Tips and advice

C# can pull the source data easily from Excel using an OleDB provider. It's like ten lines of code. I stored the test files as resources in the application, which made them easier to manage.

Final thoughts and next steps

There's probably a broader, more generic model of specification-generated tests rendered in Excel here - that would be an interesting project to tackle.