Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1130
  • Last Modified:

Bank Statement Reconciliations

My bank provides the ability to download a Bank Satement from its internet site for the purpose of reconciliationand balancing the Bank Statement. When this link is clicked a text file is downloaded and when the download is complete the file associates itself with Excel and the appropriate icon appears when the file is viewed in the Windows explorer. I understand there is a commonly used concept which in prinnicple allows an automatic reconcilaition with the checks written in the accounting program on the local computer. I use an Access 2000 database which includes a Table called transactions. In the Transactions Table I have fields for check Number, Amount, a presented Y/N field and other fields. I am hoping to have an automated procedure which I imagine will include a form on which commands are placed to compare the similar fields in the Excel file and the Transactions table and place an "Y" in the "Presented" field in the Transactions Table wher the amount and check number appears in both. I wonder if anyone can give me some suggestions as to how all this should be setup and if possible any examples of how to do this.
Many thanks
 
0
baggsh
Asked:
baggsh
1 Solution
 
TextReportCommented:
I would but Microsoft Money or Quicken as these support this feature and are designed to do this work for you, failing that it is a lot of programming to get the same4 functionality.

Cheers, Andrew
0
 
kd0ecCommented:
I wrote a simple GL program that has a check reconciliation feature in it. I have a "cleared" (Boolean) field and a cleardate (Date/Time) in my Trans table. I have a ckrec form that uses a query to populate it. The query's criteria is absence of a "check Mark" in the trans file.

If you use a simular method, you could import the excel file into a temp table indexed on the check number and look up these numbers from the ckrec form or the query itself. Once found, you could set the "cleared" field in you trans file and display the ones that haven't cleared.

You didn't mention whether the deposits are included in the excel file. If so, and the depostits are numbered, perhaps you could do the same with them.

kd0ec
0
 
baggshAuthor Commented:
kd0ec, how does one make a user friendly from to achieve all this...i.e. what sort of code would one use to import the excell file into a temporary table, then compare the two tables and set the cleared ones....can you give me more of an overview please...IN australia the deposits are not numbered unfortunately. Only an amount to compare which is a difficult issue and I can't see anyway around a manual comparison because the amount on the Bank Statement includes the sum of many different amounts in the Tarnsactions table. The nature of my work is that many people pay me funds during one day and at the end of the day I bank them as one total (payin) on the Bank...I'm not so worried about the deposits though and would appreciate some more help on the debits(checks) (which incidently in Australia we normally spell as cheques!!! In Australia we known as a wierd mob!
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
baggshAuthor Commented:
kd0ec, how does one make a user friendly form to achieve all this...i.e. what sort of code would one use to import the excell file into a temporary table, then compare the two tables and set the cleared ones and then delete the temporary table....can you give me more of an overview please...In australia the deposits are not separately numbered unfortunately. Only an total amount to compare  because the amount on the Bank Statement includes the sum of many different amounts in the Access Transactions table. The nature of my work is that many people pay me funds during one day and at the end of the day I bank them as one total (payin) on the Bank...I'm not so worried about the deposits though and would appreciate some more help on the debits(checks) (which incidently in Australia we normally spell as cheques!!! In Australia we known as a wierd mob!
0
 
kd0ecCommented:
This is a code snippet that I use to import a comma delimited text file into a table.

DoCmd.TransferText acImportDelim, _
                "Outages Export Specification", _
                "RawOutages", NewFile, False, ""

The "Outages Export Specification" was created by using File|Get External Data|Import then selecting the type of file (in this case txt) in your case xls. This will bring up a wizard which will allow you to define the fields and later, name the table in which the data goes. Do this first (before you automate the process) then what I do is make a macro to import the data, then compile the macro to get the "wizard's" code. You can experiment with the layout of your Excel spreadsheet. For example, you might need to copy the valid data to another "sheet" in Excel to avoid headers and so forth. Worst case you can design a macro in Excel that will export a comma delimited file limiting the export to only data in a range. Then import the comma delimited file into access.

kd0ec
0
 
nico5038Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
ComTechCommented:
This question will be placed in PAQ and points refunded.

Best regards,
ComTech
Community Support
Administrator @ EE
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now