Questions? We have answers.

Best practices: Before submitting a data formatting request

  • Updated


Learn what to do before submitting a data formatting request to MindBridge.

Quick integrity check

The engagement team should always check the client data before raising a data formatting request. Make sure to verify basic integrity of the data to get your data formatting request turned around as quickly as possible and minimize the chance of error or rework.

  • Make sure your general ledger balances (Total of Debits=Total of Credits)

    • Sometimes the engagement team already knows that the general ledger is out of balance and this can be ignored taking into consideration materiality threshold. In this case, the engagement team should let our Data Specialist know about it and provide details on how much is the general ledger out by so that the data team doesn’t have to spend time investigating and writing all the observations.
  • The general ledger has some way of identifying which entries go in one transaction (transaction ID)
  • The general ledger has all required fields (account number, amount/debit/credit, dates)

  • The general ledger can be formatted and is:

  • Account numbers are consistent among the general ledger, trial balance, and chart of accounts files
    • If account numbers in the GL look like 01-23456-78-9 while the ones in the TB or chart of accounts are 1-23456, MindBridge will read them literally and treat them as different accounts
    • In some cases, you can use our split columnsjoin columns, and/or duplicate columns features during column mapping to make the account numbers consistent (see also under Pro Tips below), but if you need us to standardize the account numbers (including mapping in account numbers from account descriptions of GLs that have only account descriptions and no account numbers), please let us know what format you would like to use and how to convert into that format if applicable.
      Note: If your client's system does not output account numbers, you can simply map the account description to Account ID during column mapping. But the account descriptions would still need to be consistent among GL, TB, and chart of accounts.
  • The engagement team should double check that they have the correct opening and closing TBs, if including TBs.

    • The opening TB should have all income statement accounts zeroed out and posted to retained earnings. This process will ensure that the completeness check report is accurate.

    • The closing TB should be the final TB before closing out income statement accounts to retained earnings
    • The general ledger should not contain opening or closing journal entries (i.e., do not include any journal entries carrying forward amounts or closing income statement accounts to retained earnings)

Pro tips

  • If an engagement team has multiple files that need to be combined: 

    • As of the December 2021 release, use the new Merge tool to combine multiple files if they do not need any other formatting!
    • If additional formatting (for example, ungrouping) is needed, please try to send the files when you have them all and after you’re reasonably sure they are the final version you want to use. Sending multiple revisions will slow down the process.

    • Each file must have the same columns in the same order. Differences will dramatically slow down the process, as the data team would need to match up columns manually, often making best guesses. If possible, please indicate which columns in one set of files should match up with those of the others, especially if there are substantial differences, such as when the client changed ERP systems in the middle of the year.

  • If an engagement has big files and/or files with a lot of columns - to maximize the chances of successful ingestion as well as to save time overall, please indicate which columns you would like to keep. Also, if the engagement team already knows which columns they need to map (especially the required ones like account number, amount, date, transaction ID), please indicate that as well in the support ticket.
  • Try to get an export with headers. MindBridge expects the first row of each GL and TB file to be a header row with column names. This will also help us decipher files that look cryptic to someone unfamiliar with your client.
  • Use split/join tools directly in MindBridge to extract Account ID/Account Description from one column and place them into their own columns, extract sub-accounts from longer account numbers, join different sub-account numbers in different columns into one account number, or extract unique transactions ID/identifier

  • If the general ledger doesn’t balance, the best approach is to get totals by account and comparing with subtotals in the general ledger (if present, and they often are), or getting total by month/quarter if the client sent the files by month/quarter.

Common mistakes

  • General ledgers with wildly out of range dates in years like in the 1900s or 2200s are likely typos or missing dates that got converted in some system.

  • Debit/Credit/Amount column not formatted as a number or with incorrect signs

    • For example: With parentheses, <>s, negative signs at the end, currency symbols, non-North American formats, instead of decimal point and . as thousands separator.

    • Amounts need to be default positive balance if there are separate debit and credit columns, but debits must be positive and credits negative if there is only one Amount column. See this guide for instructions on how to flip signs for a range of accounts in Excel: Data Formatting Guide: Flipping signs for a range of accounts or based on condition(s).

  • Date column not formatted as date

    • In some instances the format changes mid-file (for example, most dates are in YYYY-MM-DD format but a section has Excel numeric dates).

    • Some of these date issues, as well as issues with the debit/credit/amount discussed above, can indicate shifted columns or broken lines in some parts of the file.

Anything else on your mind?

Head over to the community with your thoughts and inquiries! You can also chat with us, or, submit a request for further assistance.

Was this article helpful?