The aim of this addin is to match and reconcile Accounting Database and bank POS statements.
We have a single accounting DB and many bank statements with different formats
Once we download all the csv, xls or xlsx files to a folder, the add in will
-scan the folder,
-import the data from different file formats to a single file (create multiple sheets)
-create an autoincrement index for every sheet
-create a comparable column for every sheet
-create a Match column for every sheet
-create a result sheet
The excel macro will work like this:
The add-in is assigned to a button. When the addin opens it asks for:
-DB file (I can use the already open csv sheet) and profile assigned to it.
-Bank statements (up to 8) and profile assigned to it (all the files in the same folder with DB file are scanned for "filename match" below)
- - filename match to profile: filename (starts,contains,ends) has .... string then set profile...
- - Add index? (adds an autoincrement index Row number to the right end of the table where the data finishes ). index is used to specify a specific row with sheet name
- - Add Comparable column?
- - - Concatenate following [Column nr & applied function] ( functions: LEFT (N) , RIGHT (N), Multiply by (N), Convert date to YYYYMMDD number, Convert date to YYYYMMDDHHMMSS number )
- - - Add comparable column to Nth column
Add-in Result sheet Algorithm
-Get all the rows on DB
-Create 1 column for every bank and 1 column for error reporting
-If the Comparable column matches with one bank put 1 to that column. If two banks match put 1 to each column (copy that to the errors sheet) write the index of the matching value to the relative rows (in Match column) in DB and bank statement, also mark the DB row and the Bank row yellow.
-If the data is not present in any bank statement then put a 1 to the error column, mark DB row red, insert index value "Error" to result column on DB sheet.
-If the data is present only in bank statement add it (as a new row) to the Result Sheet put a 9 to the bank's column and another 9 to the error column
Resolver For the error results:
time sensitivity shall be asked in minutes. if the comparable column has time it should be removed. Then the process shall be reRun for these error rows. This time if there is a match within the same time sensitivity these transactions shall be matched too.
Point and click matcher:
on the left DB, on the right Bank. Transactions shall be selected with single click. Click from left and click from right. These are matched.
All changes shown on the result sheet.
23 pekerja bebas membida secara purata $676 untuk pekerjaan ini
Hello, Instead of naive VBA scripting, I can develop a professional .Net VSTO Excel Add-in which shall do the required tasks. Please discuss if you are interested. Thank you, Usman
HI, in the past I worked with office addins and I can build the excel addin that you need. Share me a sample of files if you can. Check my portfolio.
Hi, I've made something like yours. But i'ts not about accounting. It was about excel security system. I think I can make an add in like you want. I hope we'll discuss further. Thanks
I am experienced Excel Add-in developer. I have developed many reconciliation Excel solutions and other financial solutions with Excel, Access and VBA.