Exporting general ledger transactions

Quick Tutorial Full Training

General ledger transactions are generated as a byproduct of processing commerce activities. Creating general ledger transaction export files enable staff users to easily transfer important financial information from iMIS into separate accounting software system.

You have the ability to select the file format, file summarization level, and date range for each export. If you are licensed for deferred income, running the general ledger export will also generate the deferred income transaction information necessary for the selected date range. In addition, all due to/due from transaction information is transferred to the appropriate accounts when the export is run.

When the GL export procedure is run, the following actions are performed:

  • Only transactions in a posted batch are exported; if the transaction is included in a batch that is not posted, or is in the process of being posted, the transaction is not exported.
  • The transactions are marked as exported so they are not picked up by any future exports
  • If you are licensed for deferred income, entries are created to move deferred income to current income in the appropriate accounting period in which the income is earned.
  • Due to/due from journal entries are created as needed.
Selecting a summarization option

Before you export general ledger transactions, you must set a summarization option and define the file format. The summarization options allow you to specify how the exported general ledger transactions will be organized, and the file format in which the transactions will be exported into.

To choose a summarization option, go to Settings > Finance > General. From the General ledger interface section, define the following configuration options:

  • Summarize exported journal entries by - Choose the summarization option for general ledger transaction exports:
    • Month - One summarized journal entry per month will be exported, sorted, and summarized by GL Account, Financial entity, and where applicable, Product Code. Each summary journal entry will be dated the first day of the month and year the transactions were dated. Each journal entry line item represents a summarized amount. A separate summary journal entry will be created for each month and year the transactions are exported within the specified date range.
    • Day - One summarized journal entry per transaction date will be exported, sorted, and summarized by GL Account, Financial entity, and where applicable, Product Code. Each journal entry line item represents a summarized amount. A separate summary journal entry will be created for each day the transactions are exported within the specified date range.
    • Batch - Within the requested date range, this posts a journal entry for each batch that contains the batch number and a summary on a per GL account/pseudo-account basis.
    • Detail - Each journal entry (debit and credit) line will be exported and organized by date, Financial entity, and then by overall journal entry in the order the transactions were processed in iMIS. The greatest amount of information is returned using this option.
  • Default export file format - Exported general ledger transactions will be downloaded to your computer in the selected file format:
    • CSV - Useful for various accounting softwares. Each line of the file is a data record, and each record consists of one or more fields, separated by commas. This file format might need to be converted to a format your accounting software recognizes before being imported. Depending on the selected GL summarization option, the information returned in the CSV export file will vary.

      • Note: Excel might require additional steps to import all data in the CSV file correctly. For more information, see Viewing General Ledger data in Excel.

    • IIF - Select this option if you use QuickBooks Desktop. The file can be directly imported into the QuickBooks Desktop software. The information returned for the IIF format is organized into columns that hold direct meaning for QuickBooks.

      • Note: QuickBooks Online might require an additional application to import the IIF file. It is recommended you use either Zed Axis or Guru Importer. Both of these applications are available in the QuickBooks App Store.

  • Create separate files for each financial entity - Allows the general ledger export file to be split up by financial entity. When this option is enabled and the export is processed, several files will be downloaded to the web browser in a single zip folder. Each file contains an individual financial entity's general ledger transactions. When this option is disabled, the general ledger export will be a single file with all financial entities.

Information generated in the export file

The type of information that appears in the export file depends on the combination of the selected file format (Month, Day, Batch, or Detail) and the selected summarization option (CSV or IIF).

For CSV and IIF files, all of the information returned is the same for Month, Day, and Batch. The only difference is the way in which the information is organized. The Detail export for a CSV and IIF files returns many more columns of information.

The following columns appear when exporting a CSV file:

  1. iMIS - Default name for each line
  2. Run Number
  3. Line Number
  4. Transaction Date - The date that appears depends on the selected summarization option
  5. Date - Formats the date as MMDDYY
  6. Date - Formats the date as YYMMDD
  7. Financial Entity - Detail level only
  8. GL Account - The GL Account Code
  9. Contact Id - Detail level only
  10. Pay type - Detail level only
  11. Batch Id
  12. Description - Additional description about the line item
  13. Amount - The total amount
  14. Debit - Total amount that is debit
  15. Credit - Total amount that is credit

The following columns appear when exporting an IIF file:

  • TRNSTYPE - Always shows "GENERAL JOURNAL"
  • DATE
  • ACCNT - The GL Account Code
  • NAME - Set to iMIS for all lines
  • MEMO - Populated with the most meaningful description for the transaction detail or summary line
  • AMOUNT - Set to a positive amount for a debit or negative amount for a credit

Deferred income

If you are licensed for deferred income and there are deferred income transactions exported for the specified date range, there are three additional rows included in the GL export file.

The following information is displayed for deferred income transactions:

  • A row reporting the income to the deferred income account as of the transaction date.
  • Two rows transferring the income from the deferred income account to the income account when the income is eligible to be recognized. See Deferred income in iMIS for more information.

For dues items, 1/12 of the income is eligible to be transferred each month from the deferred account to the income account.

Due to/Due from

Rows are added to the export file for each due to/due from transaction within the specified date range. If there are due to/due from accounts missing, iMIS will prompt you to add the accounts before the export file is downloaded.

Creating a general ledger export file

Before exporting, review the Default export file format and Summarize exported journal entries by options to verify the appropriate options are selected.

Do the following to generate a general ledger export file:

  1. Go to Finance > Closing procedures> General ledger export.
  2. Select a Begin date and an End date. Any type of transaction dated within the specified range will be included in the export file. If a transaction was already exported, it will not be included in the new export file. For more information on regenerating a previously exported file, see Regenerating a previously exported file.
  3. Click Create Export File. The file will be downloaded to your computer. The filename will contain the Run Number and Begin date used to generate the export. This will enable you to reconcile against previously exported files listed under the Export log tab. For example, if the Run Number is 4, and the Begin date is November 2nd 2016, the file will be named GL Export R4 11-2-2016.csv.

    4. Note: As a best practice, it is recommended you retain all export files for audit trail purposes. Also, it is recommended that you include the Run Number in a comment field for the group of entries as you import each file. You can find the Run Number on the Export log tab or in the export filename. This allows you to clearly tie each GL journal entry back to a specific set of transactions in iMIS for audit purposes.

    Note: If there are undefined due to/due from accounts, you will be prompted to define the accounts. For more information about these accounts, see Due to/due from.

If all of the transactions in the specified date range have already been downloaded, no file will be downloaded. The Export log will display the attempted export with No transactions in the Download column.

Viewing general ledger data in Excel

Excel files may interpret data in the #-#### format, such GL account codes, as dates. To ensure correct formatting of GL account codes, export general ledger transactions, custom batches, and related IQA queries to CSV files. Manually import the CSV files in Excel, and during the import, format the GL account codes as text.

Do the following:

  1. Export the general ledger transaction or IQA query to a CSV file.
  2. In Excel, go to File > Open.
  3. Select the CSV file.
  4. From the Text Import Wizard, select Delimited and enable My data has headers. Click Next.

    5. The Text Import Wizard shows Step 1 of 3, where the user is prompted to choose the data type for the file they are importing. Delimited is selected and the My data has headers option is checked.

  5. In the Delimiters area, select Comma only. Deselect any other delimiters. Click Next.

    6. The second step of the Text Import Wizard displays options for delimiters. The Comma delimiter is selected.

  6. Select the column with data in the #-#### format, then select Text as the Column data format. Repeat for each column with this format.

    7. The final step of the Text Import Wizard, Step 3 of 3, presents options to set the data format for each column. The GL account column is selected in the Data preview and the Text option is selected for column data format,

  7. Click Finish.

For more information, see the related Excel documentation.

Regenerating a previously exported file

Do the following to regenerate a previously exported file:

  1. Go to Finance > Closing procedures> General ledger export.
  2. Click the Export log tab.
  3. Locate the export, then select Download. The file will be automatically downloaded to your computer.

Note: To regenerate the export file into a different file format or summarization option, change the Default export file format or Summarize exported journal entries by options before downloading the file.