How to automatically import your bank statement see manual on overview of screens – Download of Manual
Security #626
Prerequisities:
- Finish your bank reconciliation in back office. Balance and print a copy of your Ledger Bank Reconciliation Statement for file – make sure this balances before using Spider Bank Reconciliation.
- You can not change from back office reconciliation to spider reconciliation half way through.
- Suggest you do bank statement daily – so that it will match your transactions better and save time
- IMPORTANT : if you have one transaction showing on your bank statement you should be processing your receipts payments the same way e.g.
- Receipts – DD – will show individual transactions; EF – a total, EL a total, CA (Cash) & CH (Cheques) will show as one amount on your Bank statement for that batch.
- Payments – BP – will show as individual transactions (usually used for when your bank takes money directly from your bank account); EL – a total ; CH – will show an individual transactions
- Also using Electronic Bank Rec you will have to be careful when entering your receipts that you receipt each line that is in your bank statement so you can be matched off:
Example of this incorrect:
Current Banks tested:
BANK | Country | [Select Format]
to use: |
Type of file to get from
your banking software: |
ADF | Australia | QIF | QIF |
ASB | New Zealand | ASB | csv (do daily statements) |
ANZ | Australia | QIF | QIF (4 digit year) |
ANZ | New Zealand | CSV | CSV (do daily statements) |
BNZ | New Zealand | BNZ (use QIF format) | GIF (used QIF) |
BSA | Australia | QIF | QIF |
CBA Biz
CBA |
Australia | Commonwealth Bank of Australia | csv (do daily statements) |
RAB (Regional Aust. Bank) | Australia | QIF | QIF |
WESTPAC
WESTPAC Business Live only (QIF) |
Australia | QIF
QIF (is the best format to use) |
Step 1 Print your Bank Statement from your Bank
Print your bank statements off from your Bank as you would normally do and enter your transactions from your Bank Statement into PCSchool as usual.
Step 2 Importing your Bank Statement
To import your Bank Statement, you need to be able to get a transactions file from your Bank. As many banks have different formats, we may have to adjust the program to read in your statement. If not showing in the list please email the options you have available to help@pcschool.net.
Creating/Import New Statement
- [Code] select your Entity
- [Account] select your Bank Account
- [Create/Import New Statement]
This screen will pop up : (note: various banks will use different ways of importing the data – some require you to enter the closing balance, some will import the opening and closing balances)
- Select the [Period] e.g. if in October use your period set up for October most cases this will be 10
- [Select Format] – select from the drop down your format you wish to use (see above for your banks format which is acceptable to use)
- [Date] select the last date of the transactions you are bringing in e.g. 30/09/2018
- [Opening Balance] check this is correct
- [Cr] or [Db] select the correct Debit or Credit status
- [Browse & Import] select where you have saved your bank statement (suggest you create a file for each month so you can pull in the correct bank statement entries) this will then pull in your bank statement entries
- [SAVE]
With some banks it will pop up with this screen to check your balance or asking you to enter your opening balance as per your bank statements:
Select [OK]
Next screen will show you that it has imported correct and the statement number.
Step 3 Auto matching your transactions
Select the sheet you have created
- You will see the [Pending Statement Records] – which shows 23 records
- [Opening Balance] check to make sure this is correct
- Select icon for [Presentation]
Overview of Screen:
- Shows the item imported from the Bank Statement
- Show the items that have been entered into PCSchool
- [Exact] show the items that the system thinks as Exact matches to your transactions
- [Possible] possible matches
- [No Matches] any transactions that it cannot match
- [Presented]
[Refresh] button at bottom right will refresh the presented or unpresented transactions – do this at any time to update your screen.
Step 4 [Exact] matches
[Exact matches] tab
Check your transactions and make sure you are presenting the correct amount and Name and date – if correct press the [Present this] icon
[Finalise all auto matched] will automatically match all these entries
To Unpresent if an error
Select the [Refresh] button the [UnPresent this] button will appear:
Now select the entry you wish to [Un-Present this]
[Refresh]
And present the correct entry
Step 5 [Possible Matches] Select the Possible matches tab
Note: If you go back into PCSchool and add more transactions to Debtors, Creditors etc you will need to F5 to refresh Spider – so it will pick up these new transactions
Select the [tick] item that you wish to match
Work thru all the [possible] matches
[No Matches] – select the tab
- In [No Matches] there is still 9.09 to be matched.
- Select [Unpresented] items and look or search for this amount e.g. there is 9.09
Highlight the $9.09 on the left and the $9.09 on the right
- [Present this] icon appears so you can now present this – e.g. of one more than one amount
The amount will show in the [Present this] so you can check that it balances. Once correct select [Present this]
Step 6
Create a General Ledger transaction for items with no transactions e.g. Bank fees, interest etc – which go directly to the ledger
This shows there are still 2 transactions that require to be presented but have not been receipted etc within PCSchool
Select the (Unpresented Consolidation] icon
- Shows the Batch that the system will create – select the [Create Batch] icon
- Shows the entries still to be reconciled
Select the Icon to push the information over
- [Account Info] Select from drop down or type in your General Ledger code
- [Project] select from drop down
- [GST] change if required
- [Amount] should populate from the entry
- [Whom] type in, if required
- [Description] type in, if required
- [SAVE]
In back office it has created the following entry:
Path: Finance > Gen Ledger > Batch Transactions > Batch Receipts
Note this is a finalised batch.
Once you have matched all entries:
- You will see there are NO [Pending Statement Records]
- Check your [Opening Balance] and
- [Closing Balance]
- Once correct [Print] Statement
Step 7 Printing Reports
- Print the Bank Reconciliation report from the
[Print ] icon
- From back office print the following reports:
- Print Unpresented Report
- Print Ledger Balance report – making sure this balances with your Bank Statement closing balance (if not investigate further before proceeding)
- File together with a copy of your bank statement for Auditor
Comments
0 comments
Article is closed for comments.