**Quarterly "F" Data File Submission - Description and Formats**

In an effort to streamline FMS quarterly intragovernmental reporting
while granting agencies flexibility, FMS will accept either of two
formats for submission of quarterly intragovernmental data for fiscal
2008.

The two formats are:

-   Text file format: Submissions in this format are usually downloads
    from the agency's automated accounting systems. All text files
    should be sent using the Windows Notepad application, with a ".txt"
    filename extension (see Illustration 1).

-   Excel file format: Excel submissions are more convenient for some
    agencies that need to modify particular records.

**For fiscal 2009, agencies will be required to use unified standard
text format for submitting intragovernmental balances.**

**Illustration 1: Example of a Text File Submission**

(Headings would appear in a separate attachment.)

![](media/image1.jpeg){width="3.220833333333333in"
height="4.917361111111111in"}

Regardless of which format is used, all of the elements in the table
below entitled, "Quarterly 'F' Data File Submission -- File Format
(Input)," must be included except where indicated. In addition, all
columns (that is, fields) should be labeled either in the heading or in
a separate accompanying file with the column position numbers.

The following table is an example of an agency's data file layout. The
table outlines nine required columns for text files and eight required
columns for Excel files.

# Quarterly "F" Data File Submission -- File Format (Input)

+------------------+-----+--------+------+---------------------------+
| **Field Name**   | *   | **     | **C  | ### Description           |
|                  | *Le | Type** | hoic |                           |
|                  | ngt |        | es** |                           |
|                  | h** |        |      |                           |
+------------------+-----+--------+------+---------------------------+
| [Department      | 2   | Text   |      | Treasury Department code  |
| C                |     |        |      | associated with the       |
| ode]{.underline} |     |        |      | adjusted trial-balance    |
|                  |     |        |      | fund group and consistent |
|                  |     |        |      | with the ATB Code         |
+------------------+-----+--------+------+---------------------------+
| [Bureau          | 2   | Text   |      | Bureau code associated    |
| C                |     |        |      | with the adjusted         |
| ode]{.underline} |     |        |      | trial-balance fund group  |
|                  |     |        |      | and consistent with the   |
|                  |     |        |      | ATB code (required). Use  |
|                  |     |        |      | "00" if the agency does   |
|                  |     |        |      | not have any              |
|                  |     |        |      | subdivisions.             |
+------------------+-----+--------+------+---------------------------+
| [Fund            | 4   | Text   |      | MAF fund group            |
| Gr               |     |        |      |                           |
| oup]{.underline} |     |        |      |                           |
+------------------+-----+--------+------+---------------------------+
| [USSGL           | 4   | Text   |      | USSGL account number      |
| Acco             |     |        |      |                           |
| unt]{.underline} |     |        |      |                           |
+------------------+-----+--------+------+---------------------------+
| [Fed             | 1   | Text   | F    | Federal/Non-Federal       |
| eral/Non-Federal |     |        |      | Indicator (use only "F")  |
| Attrib           |     |        |      |                           |
| ute]{.underline} |     |        |      |                           |
+------------------+-----+--------+------+---------------------------+
| [Federal Trading | 2   | Text   |      | Two-digit Federal Trading |
| Part             |     |        |      | Partner codes             |
| ner]{.underline} |     |        |      |                           |
+------------------+-----+--------+------+---------------------------+
| [Sign            | 1   | Text   | "-"  | Standard letter/symbol to |
| Indica           |     |        | or   | indicate debit or credit. |
| tor]{.underline} |     |        |      | Note: for Excel files, a  |
|                  |     |        | "D"  | negative sign is          |
| **(Required Only |     |        | &    | considered part of the    |
| for Text         |     |        | "C"  | Amount field.             |
| Files)**         |     |        |      |                           |
+------------------+-----+--------+------+---------------------------+
| [Dollar          | 17  | N      | Num  | **Text files**: Leading   |
| Amo              |     | umeric | eric | zeros required. Last two  |
| unt]{.underline} |     |        |      | positions assumed         |
|                  |     |        |      | decimal.                  |
| Agencies must    |     |        |      |                           |
| indicate in the  |     |        |      |                           |
| e-mail whether   |     |        |      |                           |
| amounts are      |     |        |      |                           |
| presented as     |     |        |      |                           |
| Normal or        |     |        |      |                           |
| Standard         |     |        |      |                           |
| balances (see    |     |        |      |                           |
| Notes on Dollar  |     |        |      |                           |
| Amounts, below). |     |        |      |                           |
+------------------+-----+--------+------+---------------------------+
|                  | **( |        |      | **Excel files**: Amounts  |
|                  | Len |        |      | must be presented in a    |
|                  | gth |        |      | numeric or currency       |
|                  | re  |        |      | format without leading    |
|                  | qui |        |      | zeros and with the        |
|                  | rem |        |      | negative sign as          |
|                  | ent |        |      | applicable. If no decimal |
|                  | for |        |      | point is displayed the    |
|                  | T   |        |      | amount is considered to   |
|                  | ext |        |      | be rounded to the nearest |
|                  | fi  |        |      | dollar.                   |
|                  | les |        |      |                           |
|                  | o   |        |      |                           |
|                  | nly |        |      |                           |
|                  | )** |        |      |                           |
+------------------+-----+--------+------+---------------------------+
| [Exch            | 1   | Text   | X or | Exchange/nonexchange      |
| ange/Nonexchange |     |        | T    | attribute                 |
| Attrib           |     |        |      |                           |
| ute]{.underline} |     |        | (w   |                           |
|                  |     |        | here |                           |
|                  |     |        | app  |                           |
|                  |     |        | lica |                           |
|                  |     |        | ble) |                           |
+------------------+-----+--------+------+---------------------------+
| [Duplicate       | 1   | Text   | "R"  | Attribute for certain     |
| Partner Code     |     |        | (w   | reporting entities that   |
| Identifier       |     |        | here | use duplicate two-digit   |
| (DP              |     |        | app  | department codes.         |
| CI)]{.underline} |     |        | lica |                           |
|                  |     |        | ble) |                           |
+------------------+-----+--------+------+---------------------------+

# 

# Other Required Information to Accompany Agency Submission

The following information, discussed elsewhere in this document, must
accompany the quarterly data file submission. This information is to be
provided in a standard checklist that FMS will send to the agencies
before the due date for file submission:

-   Include column/field heading titles, if not provided in the file
    itself (for text files it may not be practical to place them within
    the file).

-   State whether the amounts are provided as Normal or Standard (debits
    as positive, credits as negative) balances.

-   State the total number of data records contained in the file.

-   Provide a point of contact for any technical problems with the data
    file.

# Processing Notes 

> **Note:** Federal agencies use a variety of accounting systems and
> database applications. The following guidelines will assist agencies
> in developing the database structure. Unlike the previous FACTS I
> process, agencies will not upload bulk files via GOALS II or any other
> FMS on-line systems. Rather, the verifying agencies must use standard
> e-mail for sending the quarterly data files. The Intragovernmental
> Reporting and Analysis System (IRAS) will then upload each agency's
> individual data file.

-   The data file can contain multiple departments, bureaus, and fund
    groups.

-   The record file format is defined based upon currently designated
    attributes. Additional attributes may be added at a future date. For
    agencies that submit text files, they may wish to add filler at the
    end of the record and increase the record length to provide for
    future attributes. FMS can accept files with longer records if the
    defined fields conform exactly to the specified format.

-   Decentralized agencies should consolidate all submissions from
    subagencies, departments, or bureaus. Also, the resulting
    consolidated file format and data layout must be consistent.

-   Descriptions and definitions of USSGL account attributes and the
    domain values of the attributes are published in the USSGL TFM
    release (available to readers on the Internet Web site at
    **http://www.fms.treas.gov/ussgl**).

-   Edits for duplicate records are conducted based on a key consisting
    of department code, bureau code, fund group, USSGL account,
    Federal/Non-Federal attribute, Federal trading partner, and
    exchange/nonexchange attribute. Additional validations also are
    conducted. FMS will return any exceptions via e-mail to the agency
    for correction. **The agency must return corrected entries within 1
    business day.**

-   Indicate in the transmission e-mail the total number of records in
    the agency's data file, whether the dollar amounts are presented as
    Normal or Standard balances, and whether the last two positions are
    cents (see Notes on Dollar Amounts, below).

**Notes on Dollar Amounts**

As noted above, verifying agencies must indicate whether the dollar
amounts are presented as Normal or Standard balances. Also, as noted,
leading zeros are required for text files but are not to be used for
Excel files. For text files, both the Sign field and the Amount field
are combined when considering Normal and Standard balances. These terms
are defined as follows:

-   Normal balance: All amounts that are reported as their normal
    balance are to be shown with a **positive** sign. All abnormal
    balances are to be shown with a **negative** sign. The only
    exceptions are for accounts that do not have a normal balance, such
    as amortization of discount and premium.

-   Standard balance (previously referred to as "trial-balance"): All
    balances, whether normal or abnormal, are shown with their
    respective sign, similar to how they would appear in a standard
    trial-balance report.

The following table demonstrates several examples between the two
methods.

**Note:** In the examples, the Sign field and the Amount field are
combined.

+------------------------------+--------+--------+----------+---------+
| **\                          | **\    | **\    | **IRAS   |         |
| USSGL Account**              | Normal | **     | Presen   |         |
|                              | or     |        | tation** |         |
|                              | Abno   | **Am   |          |         |
|                              | rmal** | ount** |          |         |
+==============================+========+========+==========+=========+
|                              |        |        | **\      | **\     |
|                              |        |        | Normal   | S       |
|                              |        |        | Method** | tandard |
|                              |        |        |          | M       |
|                              |        |        |          | ethod** |
+------------------------------+--------+--------+----------+---------+
| 2110 (Accounts Payable)      | Normal | \$100  | \$100    | -\$100  |
+------------------------------+--------+--------+----------+---------+
| 2110 (Accounts Payable)      | Ab     | \$100  | -\$100   | \$100   |
|                              | normal |        |          |         |
+------------------------------+--------+--------+----------+---------+
| 1310 (Accounts Receivable)   | Normal | \$100  | \$100    | \$100   |
+------------------------------+--------+--------+----------+---------+
| 1310 (Accounts Receivable)   | Ab     | \$100  | -\$100   | -\$100  |
|                              | normal |        |          |         |
+------------------------------+--------+--------+----------+---------+
| 5310 (Interest Revenue)      | Normal | \$100  | \$100    | -\$100  |
+------------------------------+--------+--------+----------+---------+
| 5310 (Interest Revenue)      | Ab     | \$100  | -\$100   | \$100   |
|                              | normal |        |          |         |
+------------------------------+--------+--------+----------+---------+
| 6100 (Expense)               | Normal | \$100  | \$100    | -\$100  |
+------------------------------+--------+--------+----------+---------+
| 1613 (Amortization)          | N/A    | -\$100 | -\$100   | -\$100  |
+------------------------------+--------+--------+----------+---------+
| 1319 (Allowance for Loss on  | Normal | \$100  | \$100    | -\$100  |
| Receivables)                 |        |        |          |         |
+------------------------------+--------+--------+----------+---------+

**Notes for Excel File Submissions**

Data formatting: All data, with the exception of the Amount column, must
be in text format. For example, without text formatting, fund code
"0990" will appear as "990." To achieve text formatting, perform the
following procedure **before** entering data in the Excel worksheet:

1.  Select the entire column(s) with the mouse (for example, column "F"
    or columns "A-G")

2.  Click "Format" from the menu bar.

3.  Select "Cells" from the "Format" submenu.

4.  Under "Category" select "Text."

5.  Click "OK."

Homogeneous rows: An Excel file submission must be in the form of a data
table. Therefore, all rows, after the header row, must contain the same
type of information with no breaks (that is, no empty rows). For
example, there should be no totals, subtotals, or subheadings in the
data file. Additionally, after the last row of data and after the last
column of data in each record, there should not be any entries in the
Excel worksheet. See Illustration 2, below for an example of a properly
prepared Excel file submission.

**Illustration 2: Example of an Excel File Submission**

![](media/image2.jpeg){width="4.122916666666667in"
height="3.810416666666667in"}

**Duplicate Partner Code Identifier (DPCI)**

The DPCI is a single-digit code to be applied only to a reporting
trading partner agency sharing a two-digit agency code with another
agency. The DPCI field is blank for all other trading partner agencies.
The rules for using the DPCI identifier are contained in the following
table: