The other shadow IT

The term "shadow IT" has come to mean IT products and services used within an organization without the blessing (or even knowledge) of the IT support team. Often such services and products are not on the list of approved software.

In the good old days before the open source movement and when software had to be purchased, it was easy to control purchases of software: the Purchasing Department would verify all purchase requests with the IT Department; any unauthorized requests would be refused.

Even with open source and "free" software, the IT Department could set policies on individual PCs and prevent people from installing software. IT remained the gatekeepers of software.

With cloud computing, those controls can be bypassed. Software can now be used in the browser. Point your browser at a web site, register, supply a credit card, and you're ready to go. No Purchasing Department, no administrator rights. This is the situation that most people associate with the term "shadow IT".

Yet there is another technology that is used without the knowledge of the IT Department. A technology that has been used by many people, to perform many tasks within the organization. Programs have been written, databases have been designed, and systems have been implemented without the involvement of the IT Department. Worse, these systems have been used in production without extensive testing (perhaps no testing), have not been audited, and have no backups or disaster recover plans.

I'm talking about spreadsheets.

Specifically, Microsoft Excel spreadsheets.

Microsoft Excel is a standard among corporate computing. Just about every "business" PC (as opposed to "developer" PC or "sysadmin" PC) runs Windows and has Excel. The technology is available, often mandated by the IT Department as a standard configuration.

Millions of people have access to Excel, and they use it. And why not? Excel is powerful, flexible, and useful. There are tutorials for it. There are web pages with hints and tips. Microsoft has made it easy to use. There is little work needed to use Excel to perform calculations and store data. One can even connect Excel to external data sources (to avoid re-typing data) and program Excel with macros in VBA.

Excel, in other words, is a system platform complete with programming language. It is used by millions of people in thousands (hundreds of thousands?) of organizations. Some small businesses may run completely on Excel. Larger business may run on a combination of "properly" designed and supported systems and Excel.

This is the other shadow IT. The spreadsheets used by people to perform mundane (or perhaps not-so-mundane) tasks. The queries to corporate databases. The programs in VBA that advertise themselves as "macros". All operating without IT's knowledge or support.

Comparing two programming languages is difficult. Different languages have different capabilities and different amounts of programming "power". One line of COBOL can do the work of many lines of assembly. Ten lines of Python can do more work than ten lines of Java.

I suspect that if we could compare Excel to the corporate-approved languages of C# and Java, we would find that there is more Excel code that corporate-approved code. That is a lot of code! It means that Excel is the "dark matter" of the IT universe: existing but not observed. (I realize that this amount is speculation. We have no measurements for Excel code.)

Excel is the shadow technology to watch. Don't ignore file-sharing and browser-based apps; they are risks too. But keep an eye on the technology we already have and use.

Excel is the new BASIC

BASIC is a language that, to quote Rodney Dangerfield, gets no respect.

Some have quipped that "those whom the gods would destroy... they first teach BASIC".

COBOL may be disparaged, but only to a limited extent. People, deep down, know that COBOL is running many useful system. (Things like banking, airline reservations, and perhaps most importantly payroll.) COBOL does work, and we respect it.

BASIC, on the other hand, tried to be useful but never really made it. Despite Microsoft's attempt with its MBASIC product, Digital Research with its CBASIC compiler, Digital Equipment Corporation with its various implementations of BASIC, and others, BASIC was always second place to other programming languages. For microcomputers, those languages were assembly language, Pascal, and C.

(I'm limiting this to the interpreter BASIC, the precursor to Visual Basic. Microsoft's Visual Basic was capable and popular. It was used for many serious applications, some of which are probably still running today.)

BASIC's challenge was its design. It was a language for learning the concepts of programming, not building large, serious programs. The name itself confirms this: Beginner's All-purpose Symbolic Instruction Code.

More than the name, the constructs of the programming language are geared for small programs. This is due to the purpose of BASIC (a better FORTRAN for casual users) and the timing of BASIC (the nascent "structured programming" movement had yet to prove itself).

Without the constructs structured programming ("while" loops and "if/then/else" statements), programmers must either build their programs with structured concepts made of smaller elements, or build unstructured programs. BASIC allows you to build structured programs, but provides no assistance. Worse, BASIC relies on GOTO to build most control flows.

In contrast, modern programming languages such as Java, C#, Python, and Ruby provide the constructs for structured programming and don't offer the GOTO statement.

The people who learned to program in BASIC (and I am one of them) learned to program poorly, and we have paid a heavy price for it.

But what does this have to do with Microsoft Excel?

Excel is the application taught to people for managing data. (Microsoft Word is suitable for documents, and Powerpoint is suitable for presentations, but Excel is *the* application for data. I suspect more people know and use Excel than all of the people using Word, Powerpoint, and Access.)

Excel offers the same undisciplined approach to applications. Spreadsheets contain data and formulas (and VBA macros, but I will ignore those for now).

One might argue that Excel is a spreadsheet, different from a programming language such as BASIC. Yet the differences are small. Excel, with its formulas alone, is a programming system if not a language.

The design of Excel (and other spreadsheets, going back to Visicalc) provides no support for structure or discipline. Formulas can collect data from anywhere in the spreadsheet. There is no GOTO keyword, but one can easily build a tangled mess.

Microsoft Excel is the new BASIC: useful, popular, and undisciplined. Worse than BASIC, since Excel is the premier tool for manipulating data. BASIC, for all of its flaws, was always second to some other language.

In one way, Excel is not as bad as BASIC. Formulas may collect data from any location in the spreadsheet, but they (for the most part) modify only their own contents. This provides a small amount of order to spreadsheet-programs.

We need a new paradigm for data management. Just as programming had its "structured programming" movement which lead to the use of constructs that improved the reliability and readability of programs, spreadsheets need a new approach to the organization of data and the types of formulas that can be used on that data.