Spreadsheets exist at the heart of every company and every business department. Spreadsheets are used to help the company make fundamental decisions about it’s future. They are used to monitor current performance and predict future trends. At a departmental level, spreadsheets are used to reconcile figures from business applications, produce periodic budget figures and generally track and maintain the current status of business processes.

The information in the spreadsheet needs to be accurate and up to date. However the maintenance of spreadsheets is largely manual, but “routine” in that the information used to update the spreadsheets is typically the latest version of the same figure from the same input source every time the spreadsheet is updated. The person whose job it is to maintain the spreadsheet will usually take an amount of time each day/month manually repeating the same process. Problems will occur when this person is not at work and someone else steps in (or the process is forgotten).

PTC’s Data Consolidation module is an optional component of the PTC Scheduler which allows spreadsheets to be maintained dynamically and automatically. PTC enables you to identify each of the input sources required to maintain the spreadsheet and describe the method to extract the required data from each individual input source.

PTC can populate Excel spreadsheets from virtually any input source, including: Reports (txt, pdf, crystal) Files (xml, csv, tsv) Any database, via a SQL command Excel Web query (internet, intranet, web process) Generated spreadsheets can include all features of Excel, including “objects” such as charts, pictures, shapes etc.

PTC uses an intuitive, interactive graphical user interface to help you to configure the data consolidation task. Once the process is complete, the resulting “consolidation document” can be included in the overnight batch schedules, or run periodically and automatically from PTC’s inbuilt diary, with the resulting spreadsheet optionally emailed to the appropriate user on completion

PTC allows you to describe the format for the output spreadsheet from within the module. Alternatively, you can copy and paste the format from an existing spreadsheet. By building the template within PTC, it is not required to install a copy of Microsoft Office on the server that runs the PTC component. Cell formats, excel formulae and spreadsheet formatting are all maintained within the PTC template.

.

For each individual input source, you need to tell PTC how to identify the data that you wish to place in the output spreadsheet. For reports, such as pdf, crystal and text, PTC provide a parser to identify the correct data using intuitive string recognition techniques. For database extracts, PTC enables multiple data items to be linked directly for rapid extraction and updating. For web extracts, PTC uses an inbuilt macro recorder to enable you to build a script to navigate to the correct web page and extract the desired data. The recorded script can be replayed through PTC whenever required.

Once the input sources have been described, the required data elements can be placed into the output spreadsheet using simple “drag and drop” techniques. Advanced options allow multiple values to be placed individually in a column or row, or summed, counted, averaged or even linked to other information within the spreadsheet. The completed Data Consolidation can then be executed as frequently as is required by the business, or included in the overnight schedules.

Click here for more details