Sunday Nibble #3

At various points in my professional life, I’ve been a heavy user of spreadsheets, Excel in particular, and as I’ve indicated in occasional pieces on things like using index and match together, or sneaking IF into unexpected places, that I like to stretch what Excel can do so that I can automate data processes as much as possible.

Currently, one of my job functions involves entering commission statements into spreadsheets, and when I got there, the method had been to print the things out and then manually enter the data, and that just would not do — especially not when some of the statements would run to more than 20 pages, with hundreds of entries.

I eventually figured out how to suck the data out of the PDFs, paste it into Excel, use consistent quirks of how that data came out in order to correct it en masse, pass it through Word in order to tab delimit it, then put it back into Excel for a few more tweaks until the data was ready to transfer, either via cut and paste or, as I’m working towards, using a formula to have the destination spreadsheet update directly from the data.

Of course, there’s one very important factor here, and it’s one I build into every single step. I am constantly verifying that the balance of the data I’m working with at the moment always matches the total on the original to the penny. If it suddenly goes off, I know that something didn’t go right, and I can catch and fix it immediately instead of having to go through the tedious balancing process at the end.

And trust me, I’m over that. It used to take me two or three days to go through the entire process of entering and balancing a huge statement. Now? Maybe an hour or two — time much better spent.

But the moral of the story is “Verify constantly.” One time, when that didn’t happen, it led to a very costly mistake. You may or may not have heard of or remember the London Whale. He was a trader who made a little boo boo in his spreadsheet formula, leading to a loss of six or seven billion dollars to JP Morgan, with additional fines in the hundreds of millions.

How did it happen? Forensic analysis showed that he used a series of spreadsheets, and data had to be manually copied and pasted from one to the other. It was a process that should have been automated but never was. Ultimately, a formula was messed up at some point, and it started dividing the difference between return rates by their sum instead of their average.

This figure led to the London Whale making increasingly aggressive trades, causing other traders to place put calls against them — i.e. betting that he was making the wrong decision. Guess which group was right?

And in case that division error seems trivial, here’s an example with real numbers. Let’s say that the old rate was 15% and the new one was 25%. The difference is 10%. But here’s the outcome when you divide by the sum and the average. Ten percent over the sum of 40 is .25. Ten percent over the average of 20 is .50.

In other words, one number is half of the other, and that is consistent for any combination of new and old rates you pick. The error is always a factor of 200%, Imagine that kind of error in construction — “Oh. Your doorways are all three feet wide, but we ordered eighteen-inch wide doors. Sorry!”

“The minimum safe height for skydiving out of this plane is 5,000 feet… Oh, crap. He jumped, and we’re at 2,500.”

As the old carpenter’s adage goes, “Measure twice and cut once.” When it comes to dealing with numbers, always be verifying.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.