One of wisest tips on business I ever came across was contained in the quote below:
“Because its purpose is to create a customer, the business enterprise has two – and only these two – basic functions: Marketing and innovation. Marketing and innovation produce results, all the rest are ‘costs’.” – Peter Drucker, described as the Grandfather of Modern Marketing (and in my opinion Management)
In this article I offer a FREE copy of a new Excel-VD driven app that I created, to solve a data handling and reporting challenge I had, in reconciling certain financial transactions details with 2 different clients, using an MS Excel bank statement downloaded from my bank’s website.
The thought occured to me that others who use online banking platforms similar to mine, especially in the Nigerian market, are likely to find this app useful.
NB: This PI Squared newsletter will be published weekly, on Mondays, in place of the Speaking/Web Marketing IDEAS newsletter, starting from today – 15th February 2016. I’m reinventing my Monday newsletter content and theme, to accommodate my vision of serving the growing audience of serious minded individuals and organizations reaching out to me, with information, education. news and research findings designed to help them do what they do better.
************
PII 045: Learn Spreadsheet Programming to Auto-generate Reports from Your E-Bank Statement [FREE App You Can Use]
One of wisest tips on business I ever came across was contained in the quote below:
“Because its purpose is to create a customer, the business enterprise has two – and only these two – basic functions: Marketing and innovation. Marketing and innovation produce results, all the rest are ‘costs’.” – Peter Drucker, described as the Grandfather of Modern Marketing (and in my opinion Management)
In this article I offer a FREE copy of a new Excel-VB driven app that I created, to solve a data handling and reporting challenge I had, in reconciling certain financial transactions details with 2 different clients, using an MS Excel bank statement downloaded from my bank’s website.
The thought occurred to me that others who use online banking platforms similar to mine, especially in the Nigerian market, are likely to find this app useful.
So I resolved to use it for my annual year end mix of innovation and marketing to hit the ground running in 2017, with regard to my Excel Visual Basic Solutions Development.
Below: The 5 minute demonstration video I recorded for this app. Click here to watch it in my Youtube Channel.
Below: Screenshot of the single sheet workbook file downloaded from my GTBank online banking platform.
Below: This was modified from what was originally in the downloaded Excel worksheet bank statement downloaded via a query I did in my accounts interface on the bank’s website.
Like I mentioned in yesterday’s preview, I was trying to make some sense of the transactions in my bank statement which I generated from my online banking interface.
I actually wanted a copy of the statement because I needed to review certain entries based on some repeat clients I had transactions with via online banking.
A need arose to trace some particular transactions for two such clients, and there was a bit of confusion and delays in finding the information because each of them had at some point done transfers to me through a 3rd party.
But the 3rd parties had not taken the pains to enter descriptions mentioning who they were making payments on behalf of.
As a result, there were some payments that I could not trace for each client since their names were not linked to them.
To get started, what you need to do is simply click the IMPORT BANK STATEMENT button. In the case of this copy of the app, I’ve configured it to check for and import ONLY the workbook downloaded from the online banking interface.
This works well and is a safe strategy because typically, the online banking interface query will generate a statement that is saved using your account number as a unique filename identifier.
That means I only have to change the filename it refers to (with path modifications as needed), for this app to work with your own MS Excel based statement downloaded from THIS bank’s website.
If you use any other bank, modifications will simply be made to accommodate whatever differences exist in the data table layout/format, as well as the corresponding reports to be generated.
TIP: For those of you who take up the offer I’m making, you’ll get this app FREE to use for life, with 100% FREE support and upgrades, if you purchase ONE year membership of my Excel-VB club for N5k instead of N13k on or before Midnight of Saturday 31st December 2016.
Back to using the app…
Clicking the IMPORT BANK STATEMENT button loads a prompt informing you the app will first of all delete all existing data in the table, before it commences importation of data from the downloaded workbook.
I put in that procedure in anticipation of instances when you may be importing a new body of downloaded data that’s smaller than the one you just finished working with (For instance you download a 2013 to 2016 statement, after viewing a 2016 statement like I did).
The app imports the statement into the table and leaves the bank statement single sheet web page style workbook open in a separate window, which you can easily switch to, and compare data from.
Notice that even the worksheet tab for the database worksheet bears the same label as the filename.
Now, one of the things I wanted to be able to do, was to find information relating to a series of transactions with some specific individuals, for reconciliation purposes.
To make this easy, I got the app to generate a list of names from the “FROM” and “TO” columns in the data table – eliminating duplicates.
So, you simply click on a name from the drop list, that you want to query and review transactions for, and the app would filter the table’s contents, displaying only those matching your query, and hiding the others.
You then have the option to further query the table using month and year parameters, via drop menus provided.
So if someone tells me there was a transaction in 2014 March for a certain amount, I can use the above menu driven query items to easily locate relevant references to him/her.
Worst case scenario – if the information s/he supplies does not turn up matching records, I can simply do a blanket search with his/her name from the menu, and ALL records linked to it will appear, from which we can then review and decide the way forward.
At any point during your query based review, you have the option to click the PREVIEW button to see the pre-print view of the filtered table contents.
If you have a PDF creator installed, like I do, you will be able to generate PDF versions of your final table version – if you do not wish to print on paper. This PDF option may come in handy should you wish to send the scenario generated to a 3rd party without having to share the entire bank statement’s contents with him/her.
Another thing to note is that the totals for DEBIT and CREDIT columns are updated dynamically using functions that recalculate to reflect the displayed contents.
This is quite useful because it saves you from worrying about recalculating the totals for the scenario you generated. Watch the way the totals change as the various menu items are clicked in the video demonstration.
When you’re done with querying the table, clicking the SHOW ALL button will return the table to its default display state, with all data visible.
Now, clicking the MENU button loads the floating Main Navigation Menu button.
On the Main Menu’s left 4-tabbed panel, the 2 middle tabs allow you to access 2 key summary dynamic reports generating interfaces.
The Single Year Scenario: Allows you to generate a table of summaries with corresponding charts in bar and line formats. I created this interface to review trends on a month by month basis for a chosen year. Again, a PDF version of each scenario generated can be created and emailed to a 3rd party without needing to share the entire body of data.
Tip: Notice the auto-generated headers inserted into the print-ready page by the app, which personalizes the report with the owner’s name and other details
The Single Month Scenario: Here you want to drill down through the data for a particular month of a particular year, on a daily basis. My purpose here was to see if I could establish any useful trends and relationships between the periods reviewed, towards making useful decisions or deductions.
What I’ve done is to put in dummy data, to protect privacy of myself and those I transacted with. So what you see in the video and screenshots are NOT real values.
Tip: Now one of the things I had to do in this table was to cater for differences in numbers of days for some of the months, like February (Which has 28 or 29 days depending the year), and other months like November, September, June and April, which have 30 days, unlike the rest with 31 days.
The table’s TOTALS dynamically hide and display the last few rows for EACH month, as the month name changes, so that when February is clicked, no data from March appears in the table, regardless of the number of days it has, in that year.
This is a major time end effort saving format.
***The AUTO FIT button is used when a cell has generated entries are displayed as hash (####) signs due to inadequate cell width. Clicking in any cell in the column and clicking the AUTO FIT button will activate the auto size function, resizing the cell to allow proper display of the contents.
It goes without saying that what I have done with this app was simply basic level data handling for report generation. Nothing complex in terms of real financial analysis.
And that’s because my immediate need for basic information drove this app’s creation. But that’s why I want to bring others on board, especially financial experts and other serious business users, so they can learn Excel-VB coding as they use the app. I can help (and/or coach them) to do a LOT more.
Over the past decade, serving as an Excel-VB Solutions Developer, I have worked with many CEOs and financial consultants in various markets/industries, and I know they all have various tasks they use MS Excel to manually carry out on their business financial data – especially those found in their bank statements.
Indeed virtually any income earning adult in today’s society makes use of a bank account and is therefore likely to understand the potential benefits of being able to relate to his/her financial data using MS Excel, in the manner I have demonstrated with this app.
My vision is to empower interested business and other users of MS Excel, with Excel-VB coding competence to enable them do what I have done here, in terms of solving a data handling, analysis and report generation problem, to meet their peculiar individual needs.
Typically, any self-employed professional, marketing experts, HR Executives, and financial experts in paid or self-employment, and indeed any business owner or working adult with a need to use MS Excel, will find learning a little MS Excel-VB automation quite a handy skill to possess.
But if you cannot spare the time, maybe someone you know, or who works for you can learn it and then help you with it.
That is why I offer YOU this app, with FREE lifetime support to use it for your own purposes, if you sign up for ONE year membership of my Excel-VB Automation club.
All you need do is pay the promo signup fee of N5k, and I will send you the app, with details of your membership benefits, including the 50% discount you get on ALL my products and services as a member.
This offer remains valid until Midnight of Saturday 31st December 2016, when the fee will revert to N13k, the Standard membership fee.
NB: Current club members interested in getting a FREE copy of this app can do so by paying the N5k fee, instead of the standard N13k fee, to renew their membership for another year.
1. Click here to learn more about this app – watch demo videos etc
2.Click here to watch a 4 part video in which I demonstrate how to use this app to formulate rations using real life data sent to me by an Algerian PhD student.
Click here to contact me about purchasing this product.
Click here to download a detailed PDF user guide and watch 15 screen shot user guide tutorials of the Monthly Poultry Farm Manager that I now offer Farm CEOs.
Click here to contact me about purchasing this product.
Specialist and Multipreneur (i.e. a highly versatile/multi-skilled entrepreneur), with a bias for delivering Best Practice solutions to Farm Businesses and others.
Since 2002, he has earned multiple streams of income providing individuals and organizations with personal development training and coaching, custom MS Excel-VB solutions, web marketing systems/web hosting, freelance writing services, and best practice extension support services (for farm business owners).
He has delivered talks/papers to audiences in various groups and organizations, including the Centre for Management Development, University of Lagos, Christ Baptist Church, Volunteer Corps, Tantalisers Fast Foods and others.
In May 2012 he was the Guest Speaker at the Centre for Entrepreneurship Development’s Annual Semester Entrepreneurial Lecture at Yaba College of Technology in Lagos.
On 1st April 2013, Tayo (who reads, write and speaks the French language) relocated to Cotonou, Benin Republic to begin slowly traveling across the West African region.
His key purpose is to deliver talks, seminars and workshops on his key areas of focus and interest to interested audiences (Email tayo at tksola dot com for details).
In a previous life, before leaving to become self-employed, Tayo served for seven years (October 1994 to December 2001) as a high performing manager in Guinness Nigeria. He rose from Shift Brewer to Training & Technical Development Manager, and later acted in senior roles as Production Manager and Technical Manager.
In addition to constantly challenging the status quo and influencing positive work changes, he built a reputation for using self-taught spreadsheet programming skills (starting with Lotus 1-2-3, and later moving to Excel Visual Basic) – in his spare time – to develop Automated Spreadsheet Applications to computerize manual report generation processes in the departments he worked. Over four(4) of his applications were adopted for brewery level reporting.
Tayo holds a B.Sc degree in Agricultural Extension Services from the University of Ibadan, Nigeria, having graduated top of his class – with Second Class Upper Division honors – in 1992. He is an Associate Member of the UK Institute & Guild of Brewing, a 1997 National Finalist of the Nigerian Institute of Management’s(NIM) Young Managers’ competition, a Certified Psychometric Test Administrator for Psytech UK, innovator of Spontaneous Coaching for Self-Development™ (SCfS-D™), and Founder of the Self-Development Academy (SDAc).
When he’s not amazing clients with his superhuman skills (wink), Tayo works as the creative force behind his Daily Self-Development Nuggets blog – on which he also publishes The Farm CEO Weekly Newspaper (sent via email to paid subscribers) and his Weekly Performance Improvement IDEAS newsletter.
Visit Tayo Solagbade Dot Com, to download over over 10 performance improvement resources to boost your personal and work related productivity.
====
[IMPORTANT NOTE:====
On 4th May 2014, Tayo’s 9 year old domain (Spontaneousdevelopment dot com), which hosted his website, was taken over by Aplus.net.
Within a few days however, Tayo used his advanced self-taught web development skills to build a SUPERIOR “reincarnation” of it the website http://www.tayosolagbade.com.
But updates are still ongoing to URLs bearing the old domain name in most of the over 1,000 web pages, and blog posts
he’s published.
If you experience any difficulties finding a page or document, email Tayo at tksola dot com.
