Web Log Details

Today we are going to take on the darling of the business world: Excel. One of my guys was on assignment to Bank of America Last year when they came out with a whole new set of rules and regulations centered around Excel spreadsheets. As a younger guy, he was simply amazed that a company who touts its cutting edge technology would use something that was designed in the dim memory of history. I informed him that the Sumerians developed spreadsheets about the same time cuneiform writing was invented somewhere past 5000 years ago and for the same reason: to keep track of trade. Sure Excel does a couple of things clay tablets don't, but that is all making stuff automatic instead of by hand, not a paradigm shift. So that isn't what is wrong with Excel ( ...or Lotus Notes or any other spreadsheet application. Let's dig in a little and figure it out.

When you are using Excel, nobody is checking your work. Maybe you are supposed to enter a value, and you enter in "yes." Maybe someone else does. The fact is that you can. Forbes says Excel Microsoft's Excel Might Be The Most Dangerous Software On The Planet and for this reason. Turns out that the Chief Investment Office at JPMorgan needed a new widget to help them evaluate risk in a new venture with credit cards. This model was based on copying and pasting data into an Excel spreadsheet. Of course, someone not only made a copy and paste error, but there were errors in the formulas as well and they caused the loss of tens of billions of dollars. You'd never make a mistake like that right? They thought the same thing.

The second reason you should never use excel was inherent in the first vignette is data typing. Data Typing is the art of putting like kinds of data together and enforcing that likeness, so we don't get "yes" in the sex column. In Excel there is none. I had a nurse years ago out in the world collecting health information (unbeknownst to me) and saving the data in a spreadsheet. When she got an anomalous reading she literally typed in "beyond machine range." One of the things I was trying to do for this company was give an overall view into the health of the population. I'm not sure about you, but i have zero idea how to average "beyond machine range" into a mean total cholesterol measure. While Excel sits on top of Microsoft's JET database engine, it is not a database, and will allow you to type in anything. more on the JET engine later.

Third, You are locked into doing everything manually. If you want to update the data, you have to type it in or copy and paste it. Humans don't type well. We know this. So maybe a wisenheimer doesn't type "yes" into the sex column, but maybe there is an "N" there, and what does that mean? Manual entry creates these types of problems.

Fourth, Excel locks you into the single user nightmare. Let's discount for a minute that only one person at a time can update the spreadsheet. Let's think about one guy having it open read only and making some kind of dumb decision (tens of billions, remember?) based on old data. You are updating the thing with the NEW data while he is making his decision, and he can't see it. Epic Fail. Altus agrees and makes the 'manual jungle' case as well.

Sixth, Excel isn't secure. Here is another little nugget on the JET data engine: The username and password you so diligently set is kept in plain text in the header of your file and you can do a google search on how to open the file and find that username and password. the JET data engine uses an ISAM database. There is nothing wrong with ISAM model, but there are far better things to use. ISAM uses basically text files though they are indexed. There are several utilities that make ISAMs work like or mimic Relational databases, but you already have that in the Relational Database Engines , or Relational Database Management Systems (RDBMS) like SQL Server and Oracle. Examining the differences between ISAM and RDBMS is beond the scope of this article, but the log file alone is worth switching. The research is left as an exercise for the student.

Seventh, you don't need files. Nobody ever needs files. There is only one good thing to do with files and that is dig the information out of them and then delete them. Yes, there is an argument to be made for images and contracts, but if I can buy a car on eBay without a signature, you probably don't need a contract either. If you are an attorney and you NEED to have things signed, copy and paste the contents of the document into a a DATABASE (more on this later as well) take a picture of the contract and upload IT to the database and destroy the paper copy. You don't need paper and you don't need files. If you have paper and you have files you have to know where the particular file or piece of paper is before you can access it. you can't tell externally what is in it. You can't search for it. It is a nightmare and we curse Microsoft for coming up with SharePiont. SharePoint isn't really a new product either. They took FrontPage and added a document management system to it. You didn't need FrontPage and you do not need documents so you don't need SharePoint.

Eighth, You can't have relationships in Excel. So maybe on one sheet you have students and on another you have classes. How are you going to associate students to classes and throw in a semester and a grade and all the things you need to run your hypothetical school? DBPros has a decent article on this.

Ninth, you can't see the history of a record or a sheet in Excel. Maybe I have a mission critical application and i need to know who has seen a record what they changed, what the old value is and when the change was made. This is impossible in Excel. Before you Excel gurus say differently, and work for three weeks to make some kludgy macro half ass work, I can add auditing with a few lines of code and with far less work, AND do that to all my databases.

So I've convinced you that Excel is absolutely evil, but what do you use? You still have a job to do/department/business to run. We've given you several hints above, but we will spell it out now: Use a Relational Database. We use SQL Server from Microsoft. You get these advantages:

Strong Data Typing and Data validation. There is no more "beyond machine range", "yes" or 168 inch tall women. Yes, that happened.
You can automate processes. Remember Forbes? What they wanted was a report. Reports are a click away.
Multi user. You can type into the same table all day and everyone dcan see everyone else's data. There are even rules to automatically detect when users are updating the same record at the same time and resolve the conflict, but with a well designed database, you would never have that problem.
Security. WE have one application with thousands of users and one doesn't even know the rest exists. Doctors see only their patients, patients see only his or her own records, and can communicate with only his or her own doctor. Yes, it is unhackable. Go Try. I'm not going to wait thought, because it can't be done, not even by the NSA.
You don't need files. Nobody needs files. Take the information out of them and put them in your database, indexed for speed and searchability with a built in reporting capability
Relationships. You can build a report that shows you those students and classes above by dragging and dropping. With a reporting tool, you can even make it look pretty. Now you can have rosters and class schedules with a button click. ...and whatever it is your business does as well
Auditing. There are a couple of ways to go about it in an RDBMS, You can use triggers, but those have their own problems. We here at Sentia use stored procedures to encapsulate chunks of functionality that include the audit trail automatically.
Consistent Data. RDBMSs use a transaction log file to maintain consistency. The discussion of the log file is outside the scope of this article, but if something happens to your database or even your database server, you have a log file full of all the changes to the database since the last backup.
Reporting. If your data is all in one place and everyone is doing what they need to do and entereing their data in you can generate ad hoc reports that look like Excel with a query. if you want graphs and text and that kind of thing you can use a tool like Crystal Reports or SQL Server Reporting Services (SSRS). At Sentia we use SSRS to deliver gorgeous, color, printable reports to the desktop and across the internet.

So yes, Virginia, there is a Santa Claus, it just isn't Excel. Particularly if your data is sensitive, and it is, Excel is a horrible choice. The database is such a better choice that having one designed and built, along with an application to get the data in and out of it and produce the (pretty) reports you want to see will actually save you money, and not just in the long run. All of the people in your office that have Excel and are using it, can generally be automated out of existence. We have completely automated not just one, not even two, but several businesses. We have one guy on our staff that does nothing for one company but to click the button once a month to produce the billing report. If you've been following our sister company Sentia Health you already know that we have automated the entire health insurance industry, making it just an application and getting rid of its associated costs. This saves the average policy holder about 1/3 of his or her average health insurance bill monthly and replaces that an average of $150 per month per user with a $10 subscription fee. So that saves you personally $140 per month, your family $140 per month times the number of family members and the United States about one trillion dollars per year.

And we can do that in part because the insurance companies are still emailing spreadsheets and using manual processes.

We can automate your business as well. Maybe you should call us before your competition does.

Never, EVER Use Excel For Anything. QUIT It!

8/18/2016 10:48 AM