Spreadsheet import - chart of accounts

Alerts and notices

Use the Spreadsheet Import wizard to import chart of accounts data (account number, account description, and balance information) from a spreadsheet file using .XLS or .XLSX file format. You can use this method to add new accounts to a client's chart of accounts or to update their existing chart of accounts.

Be sure that the spreadsheet is not password protected, closed, and remains closed during the import process.

For non-segmented clients that have accounts with subcodes, you must format the spreadsheet column as text.

A client with an account mask of xxx-xx appear as 100 instead of 100-00 if the formatting for the column is set up to display as two decimals. Sub-accounts are retained when the column is formatted as a text column.

Selecting the source file

In the first screen of the wizard, select the spreadsheet file and worksheet (tab) to import.

  1. Choose File > Import > Spreadsheet.

    Note: To include subcodes for a non-segmented client in the spreadsheet import you must format columns as text columns in Microsoft Excel. Example

    Example

    A client with an account mask of xxx-xx appears as 100 instead of 100-00 if the formatting for the column is set to display as two decimals. Sub-accounts are retained when the column is formatted as a text column.

  2. In the Source Data screen, select the appropriate client from the Client name field.
  3. Select Chart of Accounts from the drop-down list in the Data type field.
  4. In the Import File section, enter the path and filename of the spreadsheet file to import, or click the Browse button to navigate to the file.
  5. Select the worksheet within the spreadsheet file to import.
  6. Click Next.

Mapping spreadsheet columns

In the Column Mappings screen, the grid displays data from the selected spreadsheet. Use this screen to map the spreadsheet columns to specific data fields in myPay Solutions Direct.

  1. If you saved mapping information from a prior import as a mapping template, that template will be included in the drop-down list in the Template field. If applicable, select the appropriate template.
  2. If the spreadsheet includes column headings or other rows of data that should not be imported, mark the checkbox in the Omit row column for that row. The application will not validate or import data in that row.
  3. For each column, click the column heading in the grid, and then select the applicable mapping item from the drop-down list in the Column <x> field above the grid.
    Mapping item Additional info Additional info 2 Required?
    Account Number Yes
    Account Description --
    Account Grouping

    Account Classification Code
    Account Classification Subcode
    Leadsheet Schedule Code
    Leadsheet Schedule Subcode

    (includes Code and Subcode for each account grouping set up for the client)

    Yes
    --
    --
    --

    Tax Information

    Tax Code
    Tax Code Unit
    M-3 Tax Code

    --
    --
    --

    Beginning Balance <Year>

    Dr/Cr
    Debit
    Credit

    --
    --
    --

    Unadjusted Balance <Period end date>

    Dr/Cr
    Debit
    Credit

    --
    --
    --

    Budget <Period end date>

    Dr/Cr
    Debit
    Credit

    --
    --
    --

    Adjusted Budget <Period end date>

    Dr/Cr
    Debit
    Credit

    --
    --
    --

    Budget 3 <Period end date>

    Dr/Cr
    Debit
    Credit

    --
    --
    --

    Budget 4 <Period end date>

    Dr/Cr
    Debit
    Credit

    --
    --
    --

    Budget 5 <Period end date>

    Dr/Cr
    Debit
    Credit

    --
    --
    --

    Note: If you map any budget columns and select the year option (as opposed to a specific period-end date) in the second Column <x> field, the application will equally distribute the amount among all periods within the current year. (For example, if you import a balance of $1,200 for a monthly client and select 2015, the application will import a $100 balance in each period for the account.) If you select a specific period end date from the second Column <x> field, the application will import the full budget amount into the selected period only. Show me.

    Budget column mapping
  4. After you have mapped all applicable columns, click Next.
  5. The application validates the spreadsheet data. If any issues are found, the invalid items are highlighted. If necessary, correct the data and then click Next.

Mapping additional data types

myPay Solutions Direct requires a code for the Account Classification account grouping for all new accounts. If your spreadsheet does not include a classification code, the application opens the Classification Assignment screen and lists all new account numbers.

Use this screen to assign a code to each new account. If applicable, you can also assign subcodes to each account.

  1. In the Classification Code column for each account, select the appropriate classification code.
  2. In the Classification Subcode column, select the appropriate subcode for each account, if applicable.
  3. Click Next.

Choosing import options

The Import Options screen opens only if you mapped at least one balance-based column. Use this screen to specify how the spreadsheet data should be imported.

  1. Choose the applicable Chart of Accounts import option.
    • Append to existing chart. The application adds new accounts and their balances to the existing Chart of Accounts. If the spreadsheet includes data for any existing accounts, the application updates data for those accounts with the new information.
    • Zero existing balances and import new balances. The application adds new accounts and their balances to the Chart of Accounts. If the spreadsheet includes data for an existing account, the application zeroes the balances for that account for the selected dates, and imports only the balances in the spreadsheet file. If the spreadsheet does not include data for an existing account, the application zeroes the balances for that account.
  2. Choose the applicable balance import option.
    • Current period balances. The application imports the balances from the spreadsheet directly into the period selected for the balance column.

      Example

      The application adds the balance from the spreadsheet to the existing balance in the client data to create year-to-date balances.

      Date Starting account balance Balance in spreadsheet data Balance for activity journal entry * Year-to-date unadjusted account balance
      01/31/15
      100.00
      100.00
      100.00
      200.00
      02/28/15
      200.00
      200.00
      200.00
      400.00
      03/31/15
      400.00
      300.00
      300.00
      700.00
      04/30/15
      700.00
      400.00
      400.00
      1100.00
      05/31/15
      1100.00
      500.00
      500.00
      1600.00
      06/30/15
      1600.00
      600.00
      600.00
      2200.00
      07/31/15
      2200.00
      700.00
      700.00
      2900.00
      08/31/15
      2900.00
      800.00
      800.00
      3700.00
      09/30/15
      3700.00
      900.00
      900.00
      4600.00
      10/31/15
      4600.00
      1000.00
      1000.00
      5600.00
      11/30/15
      5600.00
      1100.00
      1100.00
      6700.00
      12/31/15
      6700.00
      1200.00
      1200.00
      7900.00

      * During the import process, myPay Solutions Direct creates an activity journal entry to store balances that are imported from the spreadsheet.

    • Year-to-date balances. The application calculates current-period activity based on existing prior-period balances and creates a journal entry with the net change for the period.

      Example

      The balance in the spreadsheet data should be the current year-to-date balance in the client data. The application calculates the difference between the current year-to-date balance in the client data and the balance in the spreadsheet data.

      Date Starting account balance Balance in spreadsheet data Balance for activity journal entry * Year-to-date unadjusted account balance
      01/31/15
      100.00
      100.00
      0.00
      100.00
      02/28/15
      100.00
      200.00
      100.00
      200.00
      03/31/15
      200.00
      300.00
      100.00
      300.00
      04/30/15
      300.00
      400.00
      100.00
      400.00
      05/31/15
      400.00
      500.00
      100.00
      500.00
      06/30/15
      500.00
      600.00
      100.00
      600.00
      07/31/15
      600.00
      700.00
      100.00
      700.00
      08/31/15
      700.00
      800.00
      100.00
      800.00
      09/30/15
      800.00
      900.00
      100.00
      900.00
      10/31/15
      900.00
      1000.00
      100.00
      1000.00
      11/30/15
      1000.00
      1100.00
      100.00
      1100.00
      12/31/15
      1100.00
      1200.00
      100.00
      1200.00

      * During the import process, myPay Solutions Direct creates an activity journal entry to store balances that are imported from the spreadsheet.

  3. Click Import to begin the data import.
  4. The application validates the spreadsheet data. If any issues are found, the invalid items are highlighted. If necessary, correct the data and then click Next.

Note: The maximum amount limit when importing balances into the Chart of Accounts via the spreadsheet import is 999,999,999.99.

Reviewing import diagnostics

The Data Analysis screen displays a list of the information that will be imported from the spreadsheet and the analysis results for the data. If necessary, you can click the Back button to make changes to any of the mapping and options screens. To view a diagnostic report for any of the items listed, mark the checkbox next to that item, and then click the Preview Selected or Print Selected button.

When you are satisfied with the data that will be imported, click the Finish button.

The Import Complete screen displays a summary of the information that was imported from the spreadsheet. Review the diagnostic messages. You can click the Print button to display a simple report of the import results, or you can click the Close button to close the Spreadsheet Import wizard.

Below are some of the diagnostic messages you might see.

  • Number of accounts read: displays the number of accounts in the spreadsheet.
  • Number of new accounts added: displays the number of accounts added to myPay Solutions Direct.
  • Number of accounts not matched in the import: displays a list of accounts that currently exist in myPay Solutions Direct but were not included in the spreadsheet.
  • <date> Unadjusted balances successfully imported: informational message to let you know that myPay Solutions Direct imported unadjusted balances for that date.

Note: The application creates an activity journal entry using the reference AA99 in the Actions > Enter Transactions screen. If the spreadsheet includes data for multiple periods, the application creates one AA99 entry for each period.

Related articles

Spreadsheet import, overview