How to - Import accounts into Visual Cash Focus
In the Universal import option, the Specifications button gives the structure of the import file for the various options: Accounts, Budgets, etc.
Data can be imported from an Excel spreadsheet. Also, data can be exported to an Excel spreadsheet. In addition to Excel spreadsheets, import / export is also supported for the common file types: eg text file, spreadsheet, database, etc. This import / export routine is mainly used when you wish to import data from an external source into the software.
Tip: The easiest way to see the required structure of an import file is to open an existing model and export the data.
This is an example of how to import accounts using: Method 1: Specify the account details on each line. (A lookup file is not used).
Right click on the link to download this example file: vcf_import_accounts.xls
The "blue bit"
If you open this file in Excel, notice that towards the bottom is a blue line called the "bottom bit".
In your own spreadsheet, you will put your own accounts (as per the accounts shown above the blue line), and then copy into your spreadsheet the rows from the blue bit to the end.
The purpose of the blue bit is to give your accounts a link to the existing folders and accounts already in the software. The link is done by correctly filling in column F for your accounts to match the accounts in the blue bit.
This is explained in the software via the Specifications button.
Link accounts to the blue bit
The under_acc is a real account number, e.g. 2-1500.
The bottom bit is your link to the standard accounts in VCF.
The bottom bit is your link to the standard accounts in VCF.
So:
If you want to change the account number for Expense - operating accounts, you do that in the bottom bit. Here you would change EXP-OE to say 4-444. If you do that, you need to change the under_acc from EXP-OE to 4-444 for all the under_acc. If you don't change EXP-OE, then you can leave under_acc to EXP-OE.
The major folders in VCF are given account numbers. This allows you to put an account under it. When you put an account under a major folder, you must use its account number.
The ** designator:
In an import file, we need a way to designate that a row contains a major folder (Note: a major folder is not under anything). That’s where we use the ** designator.
Take a look at my example. Look for the blue bit at the bottom, which I tend to add to most spreadsheets when I’m importing accounts.
The purpose of this blue bit is to allow me to assign account numbers to the major folders. For example, for BANK I’ve assigned an account number of BANK. You could have used 12345 or any number. The **BANK tells me that this is the major Bank folder, so it knows where to put this account number.
The purpose of this blue bit is to allow me to assign account numbers to the major folders. For example, for BANK I’ve assigned an account number of BANK. You could have used 12345 or any number. The **BANK tells me that this is the major Bank folder, so it knows where to put this account number.
Then in the spreadsheet the under_acc column, I can use the account number for the bank folder (BANK in my case, or 12345).
You only need to add the blue bit to the import file if you want to assign account numbers to the major folders. If they already have account numbers you are happy with, then you don’t need the blue bit. I usually add the blue bit anyway to remind me of its purpose. It can be at the top or bottom – I find the bottom more convenient.
If the import file is a spreadsheet, your import file will look something like this:
Note: Export the accounts from one of your models and you will see something similar to that shown below.
|
|
A |
B |
C |
D |
E |
F |
G |
H |
|
1 |
Revenue |
S |
Bread |
# 130 |
FALSE |
# 99 |
Administration |
N |
|
2 |
Revenue |
S |
Fruit & Vegetables |
# 119 |
FALSE |
# 33 |
Administration |
N |
Column A:
The first figure in the import file (Revenue) is the type of account. This field is for convenience and is not imported. Anything can be entered here, or it can be left blank.
Column B:
The second figure is the code for the Type of account. This is imported if it contains one of the designated letters below. S is used for Sales or revenue. Valid selections are:
Selection Used for:
S Revenue
Z Direct costs
X Expenses
O Other income
C Cash at bank
r Accounts receivable
N Inventory
6 Other current assets
F Fixed assets
8 Intangible assets
V Investments
B Bank overdraft
p Accounts payable
7 Other current liabilities
G Long term loans
H Leases / hire purchase
E Equity
W Quantity item
Column C:
The third figure is the account description (account). Examples: Bread, Fruit & Vegetables. This is used to create new accounts. Note: On page 4 choose the Account description checkbox. If checked (the default) and you use a new account description for an existing account number, the account description in the import file will update the account description already in the model.
Column D:
The fourth figure is the account number. Use an existing account number to refresh the account details, or a new number to import a new account. The account number must be unique. If two rows in the import file contain the same account number, neither will be imported. You will be alerted if this happens.
Column E:
The fifth figure describes whether this line is a folder (is_folder). Can be TRUE or FALSE. Export any existing model to see how this field is filled. Note: On page 4 choose the Is a folder checkbox. If checked (the default) and you import an account that already exists, the account folder status in the import file will update the account folder status already in the model.
Column F:
The sixth figure is the account number that this account is under (under_acc). This number must exist in the model, or be included as a new account number in the import file. See notes below for mopre information on this entry.
Column G:
The seventh figure is the profit centre that this account is under (under_pc). Example: Administration. The profit centres in the import file can be empty, or entered. You can use the profit centre descriptions that already exist in the model (use exactly the same name, case insensitive). Note: On page 4 choose the Add new profit centres checkbox. If checked (the default) and you use a new profit centre description, it will automatically be added to the model.
Column H:
The eighth figure is the kind of account (kind). Most accounts are "normal", so usually use an N. If this field contains an N, is left empty or contains an * (asterisk), it will be taken as a normal account. If you want to use something other than Normal, valid entries for kind of account are shown in the Specifications button.
Note regarding Taxation expense
There can be only one account for Taxation expense. The import file can be used to change the account number of the Taxation expense, but should not be used to add a new taxation expense account. (It will not import extra Taxation expense accounts). The easiest way is to delete your taxation expense account from the import file, and use the existing line in the blue bit if you want to enter an account number for the taxation expense.
Tip: To find out what is needed to import a certain kind of account, try this:
Start a new model in Visual Cash Focus. Enter the account required. Then export the accounts. Look at the Excel file created and you will see what the fields are for that kind of account.