Warning: Use of undefined constant style - assumed 'style' (this will throw an Error in a future version of PHP) in /home1/tayoswdg/public_html/sdnuggets/wp-content/themes/ThrillingTheme0/functions.php on line 522
835 views

How I Built a Bank Transactions Monitor (BTM) ™Excel-VB Driven App as a Gift for a Client in 2006 [Another Example of Real Life Problem Solving with Excel-VB]

The app show in the screenshots below is called the Bank Transactions Monitor (BTM) ™. I built it in November 2006, for the Head of Accounts in a Medium Sized Lagos based hotel client.

btm3

Above: The Login Screen

The hotel’s MD had hired me, on the recommendation of the company’s audit consultant, to build a Hotel Records Management System (HRMS) ™ which worked by linking different workbook modules (Accounts, Restaurant, Bar, and Reception/Rooms) via the internal computer network server, to a report generation interface.

 

That was the first and only client that ever showed interest in having me build an Excel-VB app in which multiple users could interact with the app at the same time.

The design of the interconnected Excel-VB software system made it possible to allow users work from different terminals in their respective departments/sections, independent of the other.

 

I learnt to “think” about using MS Excel in this manner from studying the work of Pierre LeClerc, a guru Excel-VB developer whose work I’d followed closely for years.

 

But the HRMS™ is NOT why I’m writing this post.

 

I only mention it preparatory to saying that I built the BTM™ as a complimentary gift to the accounts department.

 

You see, during the 2 month period I’d worked on site in the hotel, to build the HRMS™, I’d had many useful conversations with the accountant, who often told me challenges he faced in getting his work done, to meet the demanding expectations of his boss.

 

Being a hotel operation, one of the key areas of interest and attention for the boss was the handling of incoming and outgoing cash.

Every time he came into the hotel, the first thing he wanted the accountant to show him was the record of sales in all departments accurately summed up for the previous day.

 

In addition, he would be looking at deposits of cash made into the bank for each day, in comparison to cash payments received at the payment points. And he always wanted to know what the balances in each of the company’s bank accounts were each morning.

 

On the expense front, every payment voucher would be attached to the summary of expenses for the day, for review.

 

My work with the CEOs and accountants of organizations like this taught me the value of ensuring your cash passes through your bank account as a reliable means of keeping track of your business’ cash flow at any point in time.

 

Apart from the fact that doing so enables you easily track your money for reconciliation/audit trail purpose, it also lets your banker get a fair idea of how you are doing, in terms of the turnover you ultimately records.

 

It was in a bid to make life easier for himself, especially with regard to monitoring the bank account balances, so as to have a ready answer for his boss, back then, that the accountant complained to me about the workload he was struggling with to update his paper records, to reflect the actual in the bank.

 

So, I built him the BTM™, and the last time I checked he had used it for 5 years.

 

It goes without saying that new tools we all have ready access to, today, enable any account owner readily access details of his/her bank transactions and balances, at any time, using even our phones. So, it is possible that this kind of app may be obsolete in relevance.

 

However, I argue that it all depends on the perspective one adopts.

 

For instance, I believe this app or one similar to it can be used as a teaching/learning tool. And under certain circumstances, it can even still be useful for some individuals or organizations.

 

Whatever be the case, my purpose for sharing this story about the BTM™ along with the screenshots below is to provide yet another example of how I have been able to solve real life client problems using MS Excel-VB.

Screenshots of different user interfaces in the BTM™ [Note the custom automated data entry form that allows the user post over 5,000 entries without needing to interact directly with the spreadsheet]

btm8

 

btm5

 

btm4

 

btm6

btm7

 

btm1


Warning: count(): Parameter must be an array or an object that implements Countable in /home1/tayoswdg/public_html/sdnuggets/wp-includes/class-wp-comment-query.php on line 399

Leave a Comment Here's Your Chance to Be Heard!

You must be logged in to post a comment.