When entering large volume of journals such as opening balance imports it can be useful to create the transactions in a spreadsheet and upload them to WorkBook. This article shows you how to prepare the import specification and the format in which to upload.
...
Go to the finance & administration module > general ledger sub-menu > select journalsGo
Select an existing journal entry or create a new one from List tab
Then going to the journal entry tab > click the import data button
...
CREATING A NEW IMPORT SPECIFICATION
From List tab, selecting or creating a new journal entry
Go to the journal entry tab > click the import data button
In the journal entry data import pop-up> click on the Settings tab with the cog icon
Then click the create import specification button
...
Specification - give the specification a name
Exclude lines - if you have a title row (which is advised) enter 1
File type - this should be ‘excel WorkBook (.xlsx)' unless you’re using a csv file
Field separator - leave it blank if you’re using selecting xlsx format in the File type or add a comma for if you’re selecting csv format.
...
Back in the execute tab, you can now start matching the field data
...
There are a number of different options:
FIELD | DESCRIPTIONRECOMMENDED? | REQUIRED | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Booking date | This is the date when the transaction will be posted at | Yes | Voucher number | Used when entering creditor invoices | Voucher date | Used when entering creditor invoices | Account entry type | You can specify in WorkBook. | Yes | |||||||
Voucher number | Indicate how you want to assign the voucher number for the relevant transaction. There are 4 options:
 | Yes | ||||||||||||||
Voucher date | This is the original date of the transaction. | Yes | ||||||||||||||
Account entry type | Specify which type of account the entry comes under whether it’s:
It can be set default directly in the import specification which will apply to all lines, or it can be provided in the excel/csv file. | Yes | ||||||||||||||
Account number | This is corresponding to the option you provide in Account entry type above.
| Yes | ||||||||||||||
Currency code | This needs to match the code in your WorkBook system | Currency rate | 100.00 if the currency is your native currency or the currency rate for foreign currency | Currency amount | Use this if it’s a foreign currencySpecify the currency of the transaction. E.g. AUD, USD, SGD, etc. | Yes | ||||||||||
Currency rate | This field allows specifying the exchange rate between the transactional currency and the company currency (base currency). This is only required if your transaction is in foreign currency.
| No | ||||||||||||||
Currency amount | The amount of the transaction in foreign currency (bearing in mind whether it’s - or +). This is only required if your transaction is in foreign currency. | No | ||||||||||||||
Amount | The amount of the transaction (bearing in mind whether it’s - or +). This always be the amount based on company currency (base currency). E.g. AUD
| Yes | ||||||||||||||
Offset account entry type | If Only required if you’re off-setting the account on the same line, enter the type here | Offset account | Finance type - . Specify the offset account type:
It can be set default directly in the import specification which will apply to all lines, or it can be provided in the excel/csv file. | No | ||||||||||||
Offset account | This is corresponding to the option you provide in Offset Account entry type above.
| No | ||||||||||||||
Activity GST | If required, this needs to match your Activity GST setup. This usually be used in Europe where it is required specifying the deduction on certain tax. | No | ||||||||||||||
AR/AP GST | Depending on the type of transaction you are importing (e.g. sales invoice, supplier invoice), this allows you to specify the VAT/GST code that is applied to the transaction. If required, this needs to match your AR/AP GST setup
| No | ||||||||||||||
GST amount | Enter your GST/VAT amount. This always be the amount based on company currency (base currency). E.g. AUD. | No | ||||||||||||||
Comment | The description of your transaction | YesNo | ||||||||||||||
Invoice numberUsed when entering invoices | Enter the original number of your transaction. E.g. Receipt number, original invoice number, reference number, etc. | |||||||||||||||
Currency GST amount | Enter your currency GST amount if required | Due date | Used when entering invoices | Creditor no. | Your creditor number | Payment identification number | If required | Bank reg. no. | If required | Bank account no. | If required | Swift no. | If required | IBAN no. | If requiredGST amount in foreign currency in corresponding to the currency selected above. This is only required if your transaction is in foreign currency. | No |
Due date | Specifying the due date of the transaction if you are importing invoices | No | ||||||||||||||
Transaction type | Invoice | No |
Some fields such as dates and amounts will need the format specified, these will be highlighted by the orange triangle
...
Once you’ve prepared your spreadsheet:
From List tab, selecting or creating a new journal entry
Go to the journal entry tab > click the import data button
Specification - select the correct journal specification
File type - this should be ‘excel WorkBook (.xlsx)' unless you’re using a csv file
Source - choose your file
Your transactions will appear in the box
...
Finance will appear in the Offset Account Type (unless you’ve specified it in your journal in order to offset the account on the same line), this won’t effect the journal and if you try to delete it, you may delete the whole transaction line.
...
FIELD | TIP |
|---|---|
Date | In your excel spreadsheet, format your date to text and make sure it’s in the same format as the import specification (dd/mm/yyyy or mm/dd/yy) |
Voucher no. | You can create your own voucher no. number but make sure that it’s uniqueit has not been used in the system before. |
Finance account types | Depending on the type of import, you may need to use numbers to identify your finance account types rather than words: 1 D = Debtor |
Account no. | Make sure that you only enter the account number as the import will fill in the rest of the account name |
Currency | Check your currency codes in settings > global system settings > currency codes and rates. The code needs to match exactly. |
GST/VAT | Check your GST/VAT codes in settings > finance settings > GST codes > AR/AP Codes. The code needs to match exactly. |
...