Integrating General Ledger Account Balances With Spreadsheets

The following discussion of spreadsheet integration attempts to identify the generic components of the integration process. The specifics of this process depend on the integration tools that are provided by the spreadsheet product and the hardware environment.

There are two possible scenarios. The spreadsheet product may be running on a DEC VAX, concurrent with APPX General Ledger, or it may be running on a PC, independent of APPX General Ledger. The integration process will differ accordingly. Knowledge of the spreadsheet product is essential. The procedures may vary for each spreadsheet product.

Perform the following steps in order presented.

1) Enter the APPX General Ledger application. Select “Prepare Balances for Spreadsheet” from the “Graphs and Spreadsheets” menu. During the record selection portion of the sort, select the range of fiscal years and accounts that you want. This function creates an RMS consecutive ASCII file called ALPHABAL, which contains the account number and description, the fiscal year, the Startof-Year and End-of-Year amounts, and all 13 monthly amounts. All amount fields are rounded to the nearest whole dollar.

The ALPHABAL file is located in director “appx.ccc.tgl.data”, where “ccc” corresponds to your database ID. You will need to know the location of the ALPHABAL file when performing the integration.

The format of the ALPHABAL file is described below. Each record has fixed format of 18 columns. All numeric values less than zero have a leading negative (-) symbol. Each column may contain one or more embedded blanks.

Column 1: Bytes 1 — 13 Account Number - Alpha 12 + space Column 2: Bytes 14 — 16 Fiscal Year - Numeric 2 + space Column 3: Bytes 17 — 48 Account Description - Alpha 31 + space Column 4: Bytes 49 — 60 SOY Amount - Numeric 11 + space Column 5: Bytes 61 — 72 EOY Amount - Numeric 11 + space Column 6: Bytes 73 — 84 Month 1 Balance - Numeric 11 + space Column 7: Bytes 85 — 96 Month 2 Balance - Numeric 11 + space Column 8: Bytes 97 — 108 Month 3 Balance - Numeric 11 + space Column 9: Bytes 109 — 120 Month 4 Balance - Numeric 11 + space Column 10: Bytes 121 — 132 Month 5 Balance - Numeric 11 + space Column 11: Bytes 133 — 144 Month 6 Balance - Numeric 11 + space Column 12: Bytes 145 — 156 Month 7 Balance - Numeric 11 + space Column 13: Bytes 157 — 168 Month 8 Balance - Numeric 11 + space Column 14: Bytes 169 — 180 Month 9 Balance - Numeric 11 + space Column 15: Bytes 181 — 192 Month 10 Balance - Numeric 11 + space Column 16: Bytes 193 — 204 Month 11 Balance - Numeric 11 + space Column 17: Bytes 205 — 216 Month 12 Balance - Numeric 11 + space Column 18: Bytes 217 — 227 Month 13 Balance - Numeric 11

2) The next varies depending on the import capabilities of the spreadsheet software. Some utilities will directly convert the ASCII file ALPHABAL to the spreadsheet format according to the predefined record format on the previous page. Other utilities may require an intermediate conversion step, such as first converting to a DIF file.

Perform the import or translation process to convert the ASCII file to a worksheet file (such as .WK1).

The columns in the newly created worksheet contain the following data (with their respective column widths):

Account Number (13)\ Fiscal Year (3) Account Description (32) SOY Amount (12) EOY Amount (12) 13 Monthly Amounts (13 columns of 12 each).

3) Now you can work with the amount fields. You can do whatever formatting you may like within the spreadsheet program.

Since the APPX General Ledger Account Balances data changes with each daily posting, it is necessary to repeat this conversion or integration process each time a spreadsheet requires current data.

APPX Software, Inc.

General Ledger User Manual

Published 5/95