Content area
Full Text
An unavoidable fact of life is that data must be gathered before analysis can begin. Banking and other industries measure effectiveness using financial data, found on the General Ledger (GL) system, and non-financial data, found on core application systems. Data must be collected from different computer systems, and therein ties the problem. How to blend the data together fast? One way to combine financial and non-financial data is to use Microsoft's Visual Basic for Applications (VBA), a.k.a. Excel macros.
To demonstrate how easily Excel macros can manage such an analytical challenge, this article examines a macro that combines financial and non-financial data for each item in a list. Here a list of bank branches is used, but with simple modifications the same macro will cycle through any type of list: customers, contacts, products, regions, departments, lots, assets, dates, file-names, etc., etc. The many purposes to which you can apply this macro are limited only by your needs and imagination.
FOUR BASIC STEPS
The macro has four basic steps:
(1) Startup chores,
(2) Selecting each item in a list, one item at a time,
(3) For each item performing one or more operations, and
(4) Shutdown chores (See Figure 1 ).
Macro automation does only part of the job. The macro retrieves data for each branch and saves the results, but all calculations are performed using normal Excel formulas. This way data is pulled into the familiar, and powerful, spreadsheet environment. Further, the list of items - in this case bank branches - is easily maintained in its own worksheet.
Most importantly, data files are accessed in whatever structure they happen to arrive. ASCII or text files received from mainframe systems must be converted to Excel workbooks, but the structures of these files do not need to be altered.
First Things First
Before coding the VBA language you must attend to some preliminary tasks. For starters prepare the list of items - or branches - that the macro will cycle through. Branches are represented by cost center numbers, which are maintained in the sheet CC_List (see Table 1 ). The CC List sheet also contains each cost center's branch name and save sheet name.
The next preparatory task is setting up the main workbook, which...