Financial Analysis and Modeling Using Excel and VBA

Chapter 6. Analyzing Databases

In finance and many other fields we often need to analyze data from flat databases to answer questions, draw conclusions, and make decisions. Excel provides four powerful and highly efficient tools—Sort, Filter, Table, and PtvorTable—for such analysis. Analyzing databases with these tools does not constitute modeling in a strict sense. On the other hand, if these tools were not available, we would have to spend a lot of time creating our own models to do the same type of analysis with the data. So it is important to learn these various database analysis tools and use them wherever possible to avoid having to develop your own models.

Let us start by clearly understanding what a database is. A database is information or data about a number of similar entities, such as stocks, customers, employees, or inventory items (which can number in tens to millions), organized to make it easy to access and work with the data efficiently. In a flat database the data is organized-—conceptually or actually—in rows and columns similar to an Excel worksheet. Each row (called record in the language of database) represents or holds the data for one entity, and each column (called field) holds the same information for the different entities. For example, in an employee database, each row will hold data for one employee, and the employee name, address, and so on, will go into different columns in the same row. The key is that in a flat database each column holds ...