Solved

Bank Statement Reconciliations

Posted on 2002-07-12
7
1,089 Views
Last Modified: 2013-11-27
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
Comment
Question by:baggsh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 7149012
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
 

Expert Comment

by:kd0ec
ID: 7149386
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
 

Author Comment

by:baggsh
ID: 7156841
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:baggsh
ID: 7156853
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
 

Expert Comment

by:kd0ec
ID: 7157172
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7281359

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
 

Accepted Solution

by:
ComTech earned 0 total points
ID: 7314818
This question will be placed in PAQ and points refunded.

Best regards,
ComTech
Community Support
Administrator @ EE
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question