That is, Excel is a Universal Data Processor (UDP). It's the only program I know that can serve this purpose.

This figure illustrates the structural problem that Excel solves. Whether data comes from a General Ledger or from the pages of a trade magazine, Excel doesn't care; it can use the data to produce business information.

Competing vendors of business software try to ignore the key aspect of this figure. If their programs were to present complete business information, their data would have to come from many sources: internal, external, and projected. And those sources would need to be able to change easily and frequently.

This is a problem for most other business software because their systems would have to rely on data that your IT department doesn't fully control, nor ever will. To illustrate:

• One client has used five major GL systems for years. (Each system came as part of an acquisition.) In spite of the best efforts of their IT department, the parent company is no closer to using one IT system to consolidate that data than it was five years ago. And more acquisitions are planned.
   
• Several years ago, another client completed a merger on the 26th of the month. Their Board wanted to see fully consolidated financials by the 10th of the following month. But the IT department said, "If we rush, it'll take two years." More acquisitions are planned.
   
• I've never seen a system for business information that offers an easy way to include external data. Most such systems ignore critical categories of information, including the financial performance of publicly traded customers and competitors, economic and demographic data for key markets, prices for key commodities, trends in exchange rates, and so on.
   
• What I call Original Thought, including most budgets and forecasts, has no place in most business information systems. True, most systems offer a place to park the final budgets and forecasts. But few systems offer places to store July's updates of March's two competing sales forecasts. And few such systems can display the trends in our many past forecasts of how much we expected to sell and spend next month.

To my knowledge, Excel is the only program that can meet these challenges. It's the only UDP. Not only can Excel process and report infinitely diverse data, it can report that data in any combination, using virtually any calculation and presentation method. In contrast, most other business-information tools can report only one view of the data at a time, and in limited ways.

Excel might not do these things easily, but Excel can do them. The challenge for Excel users, of course, is to find ways to do them more easily.

Excel-friendly OLAP programs can help. This is because they can contain multidimensional data from any source, and give Excel formulas the power to link to that data from spreadsheets. But no Excel-friendly OLAP, nor any Excel-surly OLAP for that matter, offers a complete and user-friendly solution to this problem.

As I learn of advances in this area, I'll let you know. But in the mean time, think twice before you buy business-information software designed to replace Excel. Instead, look for ways to enhance Excel.

Web Queries

CEOs who concentrate on data only from internal sources are being foolishly short-sighted.

Those CEOs are like the sea captain during World War II, a captain who cares only about the internal workings of his ship during a sea battle. Having spotless decks is a good thing, no doubt, but it's also important to note that people are shooting at you!

One problem for CEOs and the reporting staff is that few information systems provide easy access to useful external data. The data can't be analyzed and reported if it's not available.

To help companies deal with this issue I recently posted Import Web Data Into Excel. It explains how you can use Excel's Web Queries feature to pull financial data about public companies from web tables into Excel.

In addition to explaining how to import the data, the article lists several sources of business data. It lists three sources for quarterly and annual financial statements for public companies, and three sources for daily summaries of stock performance by company. It tells where to find real-time quotes, lists of competitors for public companies, current exchange rates, and more.

I end the article by asking readers to send me other sources of business data on the web. If enough people send enough good sources, I'll set up a page that lists the sources.

So, please email your recommendations for web data sources to me today.

If you've spent much time with Excel users in business, you've probably seen your share of user-written macros. Many of these macros, perhaps most of them, had a few problems. Typically, user-written macros are error-prone and difficult to support. Such macros often are useful, but risky.

In VBA Standards for Excel Users, I offer four easy ways to make macros easier to read and understand:

1. Use Option Explicit to require that all variables be explicitly defined.
    
2. Use Title Blocks for each macro, blocks of text that document who wrote the macro, when, and why.
    
3. Comment your code extensively. Even incomplete, hurriedly written, and misspelled comments are better than no comments at all.
    
4. Use Hungarian Notation for all variables. This naming convention reduces coding errors and makes code much easier to understand.

By following these rules, your company's user-written macros will become more of an asset, and less of a problem.

Enough for now.