|
|
||
|
Data can be extracted from 99% of the databases. The same is possible from specialized programs (financial, accounting, manufacturing, etc.) because they store the data in a database. These extracts can be made available to analysts in the form of a TXT, CSV, XLS file or any other type of files readable with Excel. The set of data delivered can be a simple extract from the database, a set of subtotals and can even include some calculated fields. When you negotiate with the IT people see if they can do as much crunching of the data as possible. Generating data files is very easy and the task can be scheduled so that automatically the data file is created daily, weekly or for whatever time interval. |
|
Most of my work consist in acquiring data from databases (Access to Oracle), ERP systems (JDE to SAP) and from all kinds of other applications that all have their own database. I then design analysis and/or reports. The import process can be automated through VBA (macros) and the reports' and analyses' contents are refreshed with SUMPRODUCT formulas. |
|
Coming out from the database (XLS, CSV or TXT files, Focus, Microsoft Query, Virtual Views, EssBase Retrievals, Interactive Excel (SAP)), columns of data (fields) are quite static. Rows (records) on the other hand can vary a lot (number of records, order of records) so SUMPRODUCT formulas and INDEX/MATCH formulas are indispensable. Create a special sheet in the workbook to receive the data coming from the outside, a sheet dedicated to such purpose. On this sheet NEVER USE DELETE, instead use Edit/Clear/Contents so that the formulas downstream are not destroyed. |
| #1 Copy/Paste |
|
You can extract data from 99% of specialized programs (financial, accounting, manufacturing, etc.) because they store the data in a database. These extracts can be made available to analysts in the form of a TXT, CSV, XLS or any other type of file readable by Excel. The cheapest and best way to bring data within an Excel workbook to generate one or many reports is to copy/paste a set of data. The set of data delivered to you can be a simple extract from the database, a set of subtotals and can even include some calculated fields. When you negotiate with the IT people see if they can do as much crunching of the data as possible. You simply open the externeal file and copy/paste the data in your own workbook. |
|
#2 EssBase (retrieve) |
|
EssBase (Hyperion) and its Excel add-in are among the best tools on the market to bring the data to the analysts and the report developers. If you consider buying Essbase ($25,000) don't buy their reporting and analyzing applications. USE EXCEL as your reporting and analyzing interface. The add-in is a little heavy (a lot of useless functions) because its creators wanted users to be able to import data directly into a report with a perfect layout. Don't do it. IMPORT YOUR DATA ON ONE EXCEL SHEET AND BUILD YOUR REPORT ON ANOTHER ONE, use SUMPRODUCT formulas to carry the data from the "DATA SHEET" to the "REPORT SHEET" with the perfect layout. You can use VBA (macros) (and it is very easy) to automate the importation of data hence developing multiple retrieves in a single spreadsheet to either assemble this information into a relational report or to create a "multiple retrieves/multiple reports" spreadsheet. |
|
#3 SAP (Interactive Excel) |
|
If your ERP system is SAP, the Interactive Excel add-in is also great tool to bring data into an Excel spreadsheet. You can bring the data from the data warehouse directly into an Excel worksheet by creating a simple MATRIX. The add-in is a little heavy (a lot of useless functions) because its creator wanted users to be able to import data directly into a report with a perfect layout. Don't do it. IMPORT YOUR DATA ON ONE EXCEL SHEET AND BUILD YOUR REPORT ON ANOTHER ONE, use SUMPRODUCT formulas to carry the data from the "DATA SHEET" to the "REPORT SHEET" with the perfect layout. |
|
#4 Formulas |
|
On the local network You can use a formula to bring into a spreadsheet data that is in another spreadsheet locally. The basic formulas looks
like this if the source spreadsheet and the destination spreadsheet
are in the same directory, on the same hard drive: If the Excel source spreadsheet
is not in the same directory, the formula looks like this: From the WWW Tired
of sending the same Excel reports to tens of people each week Open a new Excel workbook. Copy3Paste the formula above in any cell. Save it and re-open it. The result is 1,729.75 because the value in the cell of the workbook on the WWW is 1,729.75. Click here to download the source report "DATA SHEET" or "DATA workbook"? If many people on the network need a certain report, you can develop a "DATA workbook" rather than a "DATA SHEET". In the many copies of the report that you send ONCE to all your users, use SUMPRODUCT formulas pointing at the centralized "DATA workbook". No more Emailing of reports, when your user opens his copy of the report, it is automatically refreshed. This approach is specially interesting with reports that need to be updated daily. With SUMPRODUCT formulas, you can have a "DATA workbook" holding all the information and each department has a REPORT WORKBOOK extracting exclusively its information. |
|
#5 Microsoft Query (non Web) |
|
Microsoft Query is not very user friendly but to extract data from almost any source (text files, Word file, other Excel spreadsheet, web pages or any table or query in any database), you can use Microsoft Query (a component of Excel). Go to Menu/Data/External Data/Create Query. Note: Microsoft Query is not automatically installed when you install Office. You might have to use you Office CD to add this component. Note: If you want to query data from another Excel spreadsheet with Microsoft Query, the database must be a named field. |
|
After you have created and executed a query with Microsoft Query (web or other) you can have the data refreshed periodically. Right click in the table or go to "Menu/Data/Get External Data/Data Range Properties" and check "Refresh data on file open" for example. |
|
#6 Microsoft Query (Web) |
|
You can
query data from any web page on WWW. This is how
some of my clients automate the extraction of stock prices
and other financial information from the WWW. "Data/External
Date/New Query on the Web". This procedure
will create a IQY file that you can edit with
WordPad and that looks like this: |
|
After you have created and executed a query with Microsoft Query (web or other) you can have the data refreshed periodically. Right click in the table or go to "Menu/Data/Get External Data/Data Range Properties" and check "Refresh data on file open" for example. |
|
#7 Manual Entry |
|
There is nothing wrong with manual entry of data in your worlbooks if one forgets about the cost of errors and about the time and money spent doing so. Other elements of cost exist in manual entries: - the turnover of manpower and the training of replacements. Employees who have to perform such a task day in day out get frustrated and leave their job, - the process is slow and decision makers miss deadlines to make the right decision, - because of its lenght, the reporting process takes place only once a month rather than once a week or once a day to make these crucial decisions more timely, to save more money. But if there is no other way, make sure that the data is entered in your workbooks only once and on a single sheet. If you have data for 12 months, there is no need to have 12 sheets in a spreadsheet, the data can be entered on a single sheet using the database functions and aids to make the job much easier (see the database functionalities). The reporting can be developed with SUMPRODUCT formulas and you end up with a 3 sheets spreadsheet, the "DATA" sheet, the annual summary and a dynamic report showing all the twelve months one at the time at the click of a button. |
|
The secret for automating reports feeding on external data is the SUMPRODUCT function. |
|
|
||