6Utilities
Integrating Budget Analysis 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 Budget Analysis, or it may be running on a PC, independent of APPX Budget Analysis. 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 the order presented.
1) Enter the APPX Budget Analysis application. Select “Prepare Budgets for Spreadsheet” from the “Graphs and Spreadsheets” menu. During the record selection portion of the sort, select the range of budget types, fiscal years, and accounts that you want. This function creates an RMS consecutive ASCII file called ALPHABGT, which contains the budget type, account number, description, the fiscal year, the annual amount, budget type, and all 13 monthly amounts. All budget amount fields are rounded to the nearest whole dollar.
The ALPHABGT file is located in directory “appx.ccc.tgl.data”, where “ccc” corresponds to your database ID. You will need to know the location of the ALPHABGT file when performing the integration.
The format of the ALPHABGT file is described below. Each record has a 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 Budget Type - Alpha 12 + space Column 2: Bytes 14 — 26 Account Number - Alpha 12 + space Column 3: Bytes 27 — 29 Fiscal Year - Numeric 2 + space Column 4: Bytes 30 — 61 Account Description - Alpha 31 + space Column 5: Bytes 62 — 73 Annual Budget - Numeric 11 + space Column 6: Bytes 74 — 85 Month 1 Budget - Numeric 11 + space Column 7: Bytes 86 — 97 Month 2 Budget - Numeric 11 + space Column 8: Bytes 98 — 109 Month 3 Budget - Numeric 11 + space Column 9: Bytes 110 — 121 Month 4 Budget - Numeric 11 + space Column 10: Bytes 122 — 133 Month 5 Budget - Numeric 11 + space Column 11: Bytes 134— 145 Month 6 Budget - Numeric 11 + space Column 12: Bytes 146 — 157 Month 7 Budget - Numeric 11 + space Column 13: Bytes 158 — 169 Month 8 Budget - Numeric 11 + space Column 14: Bytes 170— 181 Month 9 Budget - Numeric 11 + space Column 15: Bytes 182 — 193 Month 10 Budget - Numeric 11 + space Column 16: Bytes 194 — 205 Month 11 Budget - Numeric 11 + space Column 17: Bytes 206 — 217 Month 12 Budget - Numeric 11 + space Column 18: Bytes 218 — 228 Month 13 Budget - Numeric 11
2) The next step varies depending on the import capabilities of the spreadsheet software. Some utilities will directly convert the ASCII file ALPHABGT 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):
Budget Type (13)
Account Number (13)
Fiscal Yea (3)
Account Description (32)
Annual Budget 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.
Retrieving Budgets from Spreadsheet Files
If the edited budgets are to be rewritten back to the APPX Budgets file, follow these steps:
- If you changed the worksheet column widths, set them back to the exact widths created during step 1 from the previous procedure.
- Columns 1 through 4 must be the same as the original spreadsheet; only the annual budget amount and monthly budget amounts may be changed. If you added a decimal figure to the amounts, you must format the amounts to whole dollars.
- If you changed the sequence of the columns, you must now return them to the original sequence; however, rows may be moved. If a row is deleted, the Budgets record for the corresponding account will not be updated.
- Delete any blank lines, page and column headings, or bus totals.
NOTE: The final format of the spreadsheet must be one row of data per record to be converted, in the same sequence and spacing as the original conversion file. The update will not succeed if the spreadsheet file is in the wrong format.
- The next step is also dependent on your hardware configuration and the interface utilities available in your spreadsheet product. The basic idea is to print your spreadsheet to a “print file”. Each line of the print file should be considered a record with its destination being the APPX ALPHABGT file.
- Return to the APPX Budget Analysis application.
- Select “Update Budgets from Spreadsheet” from the “Graphs and Spreadsheets” menu.
When this function is completed, you can review the newly changed Budget records by selecting “Budgets” from the “File Maintenance” menu, or by printing a report.
This page intentionally left blank.
APPX Software, Inc.
Budget Analysis User Manual
Published 5/95