The amazing data connection.

How often have you wished you could use a client's or a colleague's computer data in some other computer applications? Wouldn't it be convenient to combine a spreadsheet analysis with a management report or memo to produce a single new document? With today's software, such things are easy to do and this article tells how they're done.

Many popular accounting, spreadsheet, database and word processing programs can convert files configured in their formats for use in many other application programs. With few exceptions, they can do so whether your computer environment is DOS, Windows, Unix, OS/2, a network, a mainframe or a Macintosh.

Data transfers and combinations generally occur in one of two ways. The more common is data exchange, or data import, in which data are moved from one program to another in a standardized format. Data linking creates live data links between applications so the shared data can be updated, in real time, when data in either program change.

A WINDOWS WONDER

A little-used feature of Microsoft Windows is its ability to transfer data between Windows and non-Windows applications easily. The main function of Windows' Clipboard feature (clipbrd.exe) is to hold selected data for transfer to another program. The simple "copy and paste" technology of word processors works in a similar manner in Windows. A block of data is selected and copied to the Clipboard from either a DOS or Windows application and then pasted into the program of your choice. The ability to make such a transfer can be quite handy, for example, when you want to copy part of a Lotus 1-2-3 spreadsheet analysis into a word processor file for inclusion in a memo.

To copy and paste data using non-windows applications during Windows sessions, you must first set up the non-windows application program as an icon in a program group (see the Microsoft Windows User's Guide for detailed setup procedures). For this procedure to work, Windows must be running in 386 enhanced mode--not standard mode--and the source and destination programs each must be running in a window--not a full screen.

The following steps sound much more complicated than they really are; only a few keystrokes or mouse movements are required. In addition, if you're doing the same type of transfer repeatedly, consider recording the steps and storing them as a macro.

Start the non-Windows application from the Program Manager or File Manager by clicking on its icon, its program file, a document associated with the program file or the application's program information file (PIF). The non-windows program will appear as a full screen. To shrink the full screen to a window press the ALT and ENTER keys together. Then click on the control menu (that's the dash in the extreme upper left corner of the window) or press ALT and the space bar. Then, from the menu that appears, choose Edit and then Mark. Select the block of data you want to copy (hold down the left mouse button until all of it is selected, or use SHIFT and the arrow keys). Press ENTER or the right mouse button to copy the data to the Clipboard. To change back to a full screen, simply press ALT and ENTER at the same time.

Now switch to the next non-windows application, which is the destination for the data copied to the Clipboard. Go through the steps above to launch the program or, if it's already loaded, press ALT and TAB simultaneously to cycle through the loaded program or press CTRL and ESC together to access the Windows Task List. Again, before you can paste the copied data into the non-windows application, that application must be running in a window. If necessary, repeat the above steps by pressing ALT and ENTER together. Then place the cursor where you want the Clipboard data to appear. Open the application's control menu (that dash in the upper left of the window), choose Edit and then Paste. The final step is to return the application to a full screen by pressing ALT and ENTER together. Once you get the hang of it, the whole process takes only a few seconds.

USING WINDOWS APPLICATIONS

Nearly every Windows program provides the cut, copy and paste options in the Edit menu selection. These functions use the Clipboard to transport data from one application to another. To copy data to the Clipboard, use the left mouse key or the SHIFT and arrow keys to select the data. Then, choose Copy in the Edit menu to copy the selected data into Clipboard. Finally, move to the destination application, position the cursor where you wish the data to appear, choose Edit and then Paste from the menu. If at any time you wish to review what has been copied to the Clipboard, select the Clipboard icon from the Program Manager.

Linking data in Windows applications uses the Clipboard and Dynamic Data Exchange (DDE) features to let one Windows application create and insert objects (such as graphs or ranges of values from a spreadsheet) from another Windows application. If you use Windows linking, you can open a constant DDE connection between the two Windows applications and objects. Linking makes it convenient to update data used in more than one application. An example is a net cash flow calculation in a spreadsheet that you also wish to include in a memo. With Windows linking, the numbers in your memo can be updated automatically every time you update them in your spreadsheet. Linking also can be used in Windows programs to link data from one document to another or from one spreadsheet to another.

A linked object maintains a constant connection between the data shown and the original data in a file created in the source application. Any change to the original data is made simultaneously in the linked application. Also, from within the document, Windows can open your spreadsheet program and edit the linked file; you don't even have to go back to Program Manager to select and run the spreadsheet.

To create a link between two Windows applications, open the source application, select the original block of data and copy it to the Clipboard (using the Edit and Copy commands, as appropriate). Then open the destination

application and position the cursor where you wish the linked data to appear. Select Edit and Paste Link from the menu to create the link. You usually can elect to update the link automatically or only when you select the Update Link menu item.

LINKING WITHOUT WINDOWS

DOS data exchange and linking. In non-Windows applications, data are exchanged in specific formats. Nearly all computer applications, whether they run on micro-, mini-or mainframe computers, can both read and write files saved in formats using either American Standard Code for Information Interchange (ASCII), spreadsheet (which is usually Lotus 1-2-3) or dBASE database formats. More and more software vendors are supporting these formats to allow users to exchange data between diverse applications. Using ASCII text, spreadsheet or database formats, you can move data from spreadsheets and databases to word processors and to depreciation, trial balance, audit and tax programs (and vice versa).

Simple DOS text exchange. One application for simple DOS text exchange, or data importing, is creating a sales budget by client using your spreadsheet, accounts receivable program and your accounting program's report writer. Most accounts receivable programs can either print a list of clients or write ("print") the report to an ASCII text file. Many accounting programs also include a report writer that can create ASCII text files.

To create a client list for the budget, first create the text file from your accounting program, listing all your clients' names. For this example we'll use State of the Art's MAS 90, but the same procedure can be done using practically any accounting program. First, choose Accounts Receivable, Reports and Customer Listing. Then, choose to print only the customers' names and ID numbers. Change the Printer setting to print the report to a text file rather than a printer. Save the file to a directory where you can find it easily (such as C:\123\DATA).

Next, launch your spreadsheet. In this part of the example, we'll use Lotus 1-2-3. With a blank spreadsheet, select /File Import Text and type in the path and file name of the report file you just created (such as C:\123\DATA\MAS90AR.TXT). Use /Worksheet Delete Rows to remove any unnecessary rows from the spreadsheet. A copy of the spreadsheet then can be distributed to the responsible individuals to prepare the budgeted sales by client.

Text splitting in Lotus 1-2-3. To separate text that contains values you want to work with into different columns in Lotus, you must use the program's /Data Parse command. Data parsing lets you turn a useless line of text into a series of data columns by separating the various labels and values within the text line.

After the text is in the spreadsheet, put the cell pointer on the first line of text data to be split and select /Data Parse Format-line Create. Lotus creates a format line, guessing whether the data in the text row are either a Label (text) or a Value figures). If the line is not formatted the way you want it, you can select /DPF Edit and edit the L and V characters in the format line to design your own column splits. Then select Input-Column and highlight the format line, the first column and all rows of data to be split into columns. Next, select Output-Range and indicate the upper left cell of the blank range in the spreadsheet for the data to be split. Select Go and Lotus will place the split text into the formatted columns you selected for the output range. The original text and format line remain the same in your spreadsheet in case your results are not exactly what you wanted. A practical use of text importing and splitting is turning a client's text-format trial balance data into a spreadsheet file with the account balances in separate columns.

THE POWER OF WORD PROCESSORS

Linking documents and spreadsheets. While many spreadsheet programs have come a long way in their ability to present reports and memos, they usually d have the word processing power to produce anything more complex than simple reports. Most spreadsheets lack a spelling checker and features to change formats such as line spacing, tabs and indents easily. To create a management or client report, you probably are better off creating the document in your word processor and then bringing in the spreadsheet data with data import or data linking.

With most of today's Windows word processors--such as AmiPro, Word and WordPerfect--you can transfer or link spreadsheet tables to your documents. You can reach into your spreadsheet, grab a range of rows and columns and drop the block anywhere in the document you wish, creating a professional-looking report. In addition, with sophisticated data linking, you can update the figures in your document automatically each time your spreadsheet data change.

Importing spreadsheet data into a document. If you want to add spreadsheet data that will not change to a word processing document, a simple data import into your spreadsheet will work best. To import data from your Lotus spreadsheet file into your word processor, position the cursor where you want the data to appear in your document. In WordPerfect for DOS, select File, Text In, Spreadsheet and Import, and then select and enter the spreadsheet file's path and file name. You can limit the range of cells of the data imported and choose whether to bring in the data as common text or as a WordPerfect table. After your options are set, select Perform Import to bring in the data. After data are imported, any changes made to the data in the original spreadsheet file will have to be made manually in the document.

Linking spreadsheet data within a document. Data linking is particularly handy when you use spreadsheet data that are likely to change. Can you imagine the convenience of having your monthly management break-even analysis report updated automatically every time you revise the budget in your spreadsheet?

To create a live link with spreadsheet data in WordPerfect for DOS, select File, Text In, Spreadsheet and Create Link. Then select and enter the spreadsheet file's path and file name. You can limit the range of the cells to be linked (by listing the range coordinates or range name) and choose whether to bring in the data as common text or as a table. Select Perform Link to link your document. To update the linked data, you can choose to update the spreadsheet link every time you retrieve that document (select Link Options, Update on Retrieve, Yes), or update the link periodically through WordPerfect menu selection (select Link Options and Update Links).

STEPPING UP OR DOWN

Using accounting data. Nearly all accounting software vendors provide options to export accounting data to other programs in spreadsheet, text, database or other formats. It's possible, for example, to move data from programs such as Quicken, Peachtree, MAS 90 and even mainframe programs to your spreadsheet, audit and tax programs. A practical use of this option is moving general ledger trial balance or historical account data to another program for financial analysis, budgeting or workpaper and tax return preparation.

From accounting to spreadsheet. Transferring general ledger balances and historical accounting data to your spreadsheet helps you avoid having to rekey figures when performing a financial analysis. Many CPAs use the desktop publishing features of some of the newer spreadsheet programs to generate financial statements that look more professional. Transferring accounting data directly from an accounting program to a spreadsheet can make the compilation of such presentations much quicker and more effective.

Here, for example, is a way to move MAS 90 general ledger account histories to a Lotus file for month-by-month or budget comparison. MAS 90's Report Master feature lets you create custom reports based on MAS 90 data and print such reports directly to a file with a Lotus data format. From the MAS 90 opening menu, select the Report Master menu and then Maintenance, enter a report name and select the data file with which you wish to work, such as GL.BUDG, which holds all account budget and history figures. Select the particular periods you wish to include in the report. Then select Print and choose Print to a File as a printer option. Type in the spreadsheet directory (C:\123\DATA) as the destination and a file name such as MAS90GL.WK1. At the file type prompt, select Lotus to make the report transfer the data into Lotus 1-2-3 format.

Once the MAS 90 report is printed to the file, you can enter Lotus and retrieve the newly created spreadsheet, which should have the items and periods you selected in separate columns for your review and manipulation in Lotus 1-2-3. Other accounting programs have similar report writers or other "printer" options to export accounting report data in Lotus-compatible file formats.

From accounting to audit and tax. For CPA firms using computerized workpaper and tax preparation software, data exchange from a client's accounting software can be a great convenience. By transferring the trial balance data from accounting software in a text or spreadsheet format, you can import the accounts and balances to your program instead of rekeying the trial balance. This can save time and avoid input mistakes.

Most of the trial balance software packages feature conversion modules that import accounting data into trial balance programs and export grouped balances to tax return preparation programs. In addition to supporting specific accounting and tax packages, such modules can translate accounting data to ASCII text, spreadsheet data and a .DBF database format. These import/export programs can access an accounting data file on the same computer or on a backup disk from another DOS system, importing as much as the account number, description and previous and current years' balances. These import/export options make yearend work much easier for the CPA.

OTHER FINANCIAL DATA TRANSFERS

Banking. Would you like to do your company's banking by computer? Many banks offer banking interface programs for personal computers (PCs). Such software lets you download bank account data to a PC for transaction analysis. Because they allow you to make account inquiries on-line, such programs can be used to reconcile bank accounts automatically through an interface with many accounting software payables/payroll programs. They also can be used to export transaction details to spreadsheets for analysis of items such as daily balances and cash flows. Check your bank or your clients' banks for details.

New program setups. Other data transfers can save time in setting up new computer applications. CPA firms can transfer their clients' accounting software or spreadsheet data to set up charts of accounts and depreciation schedules in the firms' software programs. Many accounting software programs can import data in ASCII text or Lotus data formats to set up vendor, account, employee, job and customer lists. Such data can even be imported from other accounting programs.

Transferring data between spreadsheet files also can be useful. Most major spreadsheet programs let you combine or link data in different spreadsheet files--for example, to consolidate divisional income statements. Data in Lotus 1-2-3 can be imported or exported using the /File Combine menu. Your options are to Copy, Subtract or Add the formulas and figures from a range of another spreadsheet to the cursor position in your current spreadsheet. Caution: Add and Subtract options should be used only for two or more spreadsheets that have the same row and column formats and headings.

Spreadsheet cell linking. Most spreadsheets also permit live links of cells in one file to cells in another file. With direct linking, you don't have to remember to update one file after you changed another one. For example, you can link budget data in one spreadsheet file to cash flow data in another. With a live link, every change in your budget automatically updates your cash flow. In Lotus 1-2-3 versions 2.2 and above, a live link can be created using a linking formula. The formula format is +<<fllename>>cr, where "filename" is the spreadsheet file name (and path, if it's in a different directory) to be linked and "cr" is the column and row cell reference to be linked.

Database to spreadsheet. Most database programs can create Lotus-1-2-3-compatible .wks files based on users' data export menu selections. In Rbase, select Tools, Import/Export, Export and Lotus 1-2-3.wks format. Enter the name of the spreadsheet file to receive the data and then select the database table and columns to be exported. The export feature will create a spreadsheet file with each database field in a separate column in the spreadsheet file (each database record will be in a separate row). This is a simple way to get due date or mailing list data out of your database and into your spreadsheet.

With so much computer data available, it's good to know we can move information between programs without having to rekey it each time. Software vendors are learning that users want to use the same data in more than one program, so data compatibility and data exchange will be important features in future program upgrades. Expect data transfers to become easier in the future and program upgrades to give you increased flexibility and control over your data.

EXECUTIVE SUMMARY

* MANY OF TODAY'S popular accounting, spreadsheet, database and word processing programs can convert files configured in their formats for use in many other application programs.

* IT DOESN'T MATTER whether your computer environment is DOS, Windows, Unix, OS/2, a network, a mainframe or a Macintosh. While Windows conversions are easier, non-windows conversions certainly are not hard.

* DATA FILES CAN BE LINKED so a change made in one is reflected automatically and simultaneously in the other. Such linking can be done both in Windows and non-windows application programs.

* MANY BANKS HAVE software that lets you bank by computer. With such software, you can download account data to a computer for transaction analysis. Such programs' online account inquiry functions can be used to reconcile bank accounts automatically through an interface with many accounting software payables/payroll programs.

* CPA FIRMS ALSO CAN transfer their clients' accounting software or spreadsheet data to set up charts of accounts and depreciation schedules in the CPAs' software programs.

MAC CONNECTION

If some of your clients do their bookkeeping on a Macintosh computer--and many smaller businesses do--it's probably prevented you from importing their data into your DOS-based accounting software. Linda M. Williams, CPA, a partner of Williams Blair & Williams of St. Albans, West Virginia, suggests a solution: Use a program called Mac-In-DOS, which converts text, data and graphics files between Mac and DOS systems.

The DOS version of Mac-In-DOS costs $99; the Windows version is $129. A Unix version also is available; it costs $299. For more details, write to the publisher, Pacific Micro, 201 San Antonio Circle, C250, Mountain View, California 94040, or call [phone]. GREGORY H. TOMAN, CPA, is the consulting services partner of Kentner Sellers, CPAs, Dayton, Ohio. A member of the American Institute of CPAs and the Ohio Society of CPAs (OSCPA), he is chairman of the OSCPA computers and technology committee and a member of its litigation services committee.