How do I import the Chart of Accounts?

Q: We want to import the chart of accounts from our old accounting software that we have been using for 10 years?

The accounts lists in MoneyWorks are very customizable and typically customers choose to modify one of the existing account templates that they select when creating their company file. See the Tips article  on our North American Support page: Modifying your Chart of Accounts, before going ahead with importing your existing one. It might save you time and be easier than you think.

However if you are using MoneyWorks Express, Gold or Datacentre you can import your charts with some caveats and modifications. A knowledge of spreadsheets is required.

Preparing the new company file

The first step is to choose the Minimal Account template when creating your company file:

Clicking Accounts [Red Arrow], the third step, the Accounts QuickStep Window opens, where you select your localization/country [Green Arrow] and your Company Type: select Minimal [Blue Arrow]. Click Use and complete the set up.

Seriously good accounting software for the Mac

The MoneyWorks Navigator for your new company will open at Setting Up view [Red Arrow], click the Accounts List Button [Blue Arrow] …

Seriously Good Accounting software for the MacThe Accounts list will open showing the system accounts that MoneyWorks requires to function. These accounts are coded with simplistic names to assistance in recognition of their purpose. The codes and descriptions can be changed once you have imported your chart of accounts.

Seriously Good Accounting softwareSince you will be importing your own accounts, it is advised that you mark these ones as being the default that MoneyWorks created, so highlight all accounts, Cmd key + A )Mac) or CONTRL = A (Windows) or go to Edit menu > Select All.

With the accounts highlighted go to the Command menu and choose Set Colour > Red, so that they will be distinguishable when you import your accounts.

good accounting software

Preparing the exported chart of accounts

Typically you can export the chart of accounts from most accounting software (e.g. Simply/PeachTree, Quickbooks. MYOB, BusinessVision etc.). In most cases the file can be exported in text formats, e.g. tab-delimited (preferred), c.s.v. (comma separated) or in the case of Quickbooks, their proprietary .iff format.

Note if you have been forced to select the Quickbooks .iff format, when you open it in Excel you will see that there are 20 or so rows of gibberish code, either at the top or bottom of the file (depending upon which version you are using). Delete those rows and you aleft with the tab-delimited text format.

In this example we have a chart exported from Simply Accounting, known as PeachTree in the US, which if you open your text edit or Preview application will look something like this, similar to a report. Since we only want the actual account information Code, Names etc., you should delete the header information as shown below, so that you are left with just the row of codes and descriptions. Just highlight in Preview and delete (under Edit).

importing accounting softwareNote: the exported list will look different for each legacy accounting system and depending upon the format you export in, you might not need to massage the text file before opening in Excel or Numbers.

In this example you can open the text file in Excel and set it to Fixed Width which will create the four columns we need, which will then open in Excel as shown below. Or if using Numbers as a c.s.v. file, you will have a bit more work to do in order to maintain the descriptions.

The bottom line is you want to end up with a text field that lists the account codes (ridiculously called categories in QB) and the account description. The account type doesn’t hurt either although we are going to change them.

e.g. a text file with just the columns we can use, the id or code numbers, the description, in this case a coding for Headers or H etc and account type descriptions.

Seriously Good Accounting Software for the Mac

Since obviously all accounting software uses different descriptions to identify types of accounts, mark headers, categories etc. we will have to change this import file to match what MoneyWorks is expecting.

However unlike import maps for Names or Items, the import mapping for accounts is very strict. Go to the MW File menu at the top of your screen choose Import > Accounts and the follow non flexible Accounts import Map will open.

The accounts import file must be lined up with the columns in the same order as the fields are above e.g. left to right equals top to bottom. The first 7 fields/columns are mandatory and the bottom 8 are optional.

Even though we will not be importing “Department Groups” in this example as we have not set them up ahead of time, we still need the empty Department Group column in the text file. Likewise since we are going to be identifying Header Accounts the last field in the map we will have to add a column for all the fields before we get to the Header Column.

E.g. the massaged import text file now look like this with all the field names added to row 1 and columns lined up in order going from left to right. (pdf image here).

Note: we also added information to the file:

The account type codes [Red Arrow] a mandatory field that must have a identifiable value

Account codes/types are: CA = Current Assets, FA = Fixed Assets/Capital Assets, CL = Current Liabilities, TL = Term Liabilities, SH= Equity accounts, IN = Income, SA = Sales (Note: they function the same), CS = Cost of Sales/Cost of Goods Sold, EX equal Expenses.

Department Groups has been left blank

We have entered a tax code for each account [Green Arrow] as the tax code assigned to the accounts is the default used in MW. The default tax codes that MW created in your country file vary by localization, country etc. If you are not sure what they are enter the * code at this time. the asterisk code is a system zero tax code. It is normally used for system accounts (bank accounts, AR, AP etc.) where sales tax does not apply. You can alwways changethe tax code assigned to an account after you have imported them.

For the P&L field/column [Blue Arrow] we have added the PL account that MW created in our company file. This account gets assigned to the Income/Sales, Cost of Sales and Expense accounts as shown below. In other words your Income Statement/P&L accounts.

The other fields/columns have been left blank until we get to the colour field [Purple Arrow]. Here, we are assigning the colour Blue to the accounts we have identified as Header accounts in the original export file from our legacy system.

In the Comments field [Orange arrow] we have pasted the original account type descriptions from the legacy export as a future reference.

Lastly we have marked the accounts that were used as Headers with the number “1″ which will tell MW to check these accounts as Heading accounts [Black Arrow].

Import the text file.

The file is now ready to import, drag it to the open Accounts list and the Accounts List Import Map will open.

Click Continue and the

Click Import and the accounts are entered with the new numbered accounts at the top, with the Headers in Blue.

and the original Minimal system accounts that colour coded red at the bottom.

Merging the System Accounts to the New Accounts

It would be great if that was all there was to it, but unfortunately MW requires system accounts in order to function.

e.g. Bank Accounts are system accounts and MW requires at least one to function. Even though a Bank account is a Current Asset, the account type when setting one up is “Bank Account”. Open the Bank1 system Account not Account type [Orange Arrow].

The Bank Accounts we brought in from the import, we brought in as CA, current assets are bank accounts only in name. So we need to rename the system Bank Accounts that MW created to match the names of the ones we imported.

Then noting the account numbers used, delete the existing Current Asset Bank accounts we imported and recode the system accounts so that they are numbered the replace them as shown here.

The credit cards that were imported were brought in as Current Assets. Credit Cards are similar to banks in MW as you can create payments selecting them as the payment method, but they are in reality Current Liabilities. To mnain the account codes which is the point of this exercise we will create new Credit Card accounts in MW but use the existing numbering system from the import.

Likewise the Accounts Receivable & Accounts Payable system accounts that MW created will be renumbered to replace the Accounts Receivable and Accounts Payable Accounts that were imported.

The TAXREC system account will be renumbered to replace the tax collected account was imports, as will the TAXPAID account and the TAXHOLD account which is the Sales Tax Payable account.

Lastly we will rename and renumber the PL system account that MW created to the Retained Earnings description and code that we imported e.g. acc. 3500.

The sales, Cost of Goods & Expense accounts that we assigned the PL account to in the import will automatically change to newly account 3500 Retained Earmnings, e.g. account 5760 Rent is now assigned the 3500 retained Earning account.

Bottom line we have imported a fairly complex chart of accounts and then merged by recoding the Minimal system accounts that MoneyWorks requires and ended up with a chart that is identical to what the customer was using for ten years.

e.g. merged accounts in red.

However as stated at the beginning of this post, it is usually easier and quicker to modify one of the Account templates that MoneyWorks offers when creating your company file.

For more information about MoneyWorks Support in Canada, in the USA, or in the UK contact www.moneyworkssupport.wordpress.com

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s