Russell Knight has leveraged Investec’s Programmable Banking API and Spreadsheet Banking to create a one-stop shop Excel workbook that puts a plethora of useful financial information at your fingertips – without you needing to write a line of code. Check out his demo here.
One personal finance sheet to rule them all
As the world generates exponentially more data, one of the key challenges for organisations across all fields is how to make sense of it all. Large companies are turning to dedicated service providers just to sift their data and present it in comprehensible, actionable formats.
This is the challenge that Russell Knight, senior IT business analyst and Programmable Banking community member, set out to tackle on a personal-finance level.
“The problem I'm solving for is that people typically have a lot of financial information split and spread all over the place,” explains Knight. “I'm trying to consolidate it all in one place. I imagine someone waking up, getting a cup of coffee and sitting in front of this spreadsheet in the morning to take stock of their finances.”
How it works
There are four major components to Knight’s solution. “The first is visual augmentation of the banking information, then stock portfolio valuations, foreign currency valuations, and some news feed elements.”
Knight’s solution begins with a parameters tab, which you set up once. This establishes conditional formatting that accentuates important information and de-emphasises less important information.
The next portion is budgeting, where you set up your categories and your budget for each one. “The solution then pulls the budget for whichever period you’re dealing with, along with the actual value from the pivot table connected to the Investec API,” explains Knight. “This shows your actual and percentage variance too, so there’s quite a lot of information to work with. As the data is ingested into the pivot table via the API, you simply update the spreadsheet, by clicking ‘Refresh’.”
The classification tab is where transactions are categorised on three levels. Knight says he’s used this classification mechanism for more than six months with 95% accuracy. “Crucially, the transactions that are uncategorised are the ones I want to pay attention to, rather than miscategorising them.” The classification fields use data validation, an innate Excel function, to draw the list of entries from the budget page.
The analysis tab displays all your financial information according to the conditional formatting rules established in the parameters tab. Unimportant transactions are greyed out and medium-level expenses appear in blue, while uncategorised ones are bright red to draw your attention. “Here you can see in one, quick look what requires your attention,” says Knight.
All the information you could need
On top of all that, Knight’s solution includes a pivot table with expandable items grouped by period and category. The really handy thing about this, says Knight, is that it utilises an innate Excel feature to enable you to see what actual transactions comprise your financial figures by double-clicking on them.
TopX is a simple table that shows as many of your top expenses as you like, giving you an instant view of what’s eating into your finances. That’s followed by HeartBeat, which is a visual representation of your account balance over time, and Frequency, which shows your total transactions per day over a period.
Then there’s a foreign exchange tab, which tracks updated rates for all currencies, with the major ones at the top, and updates your portfolio of various currencies accordingly. The newsfeed tab works according to an XML map, tracking stories relevant to your interests. Finally, there’s a share price map, which picks up values from a JSE tab via an Excel Power Query, drawing in updated values when you refresh the page. “You can pick a share, enter your share code to populate the price, then enter your number of shares held,” explains Knight. “The sheet totals up your portfolio value for easy reference.”
A single source of truth
Knight says his goal was to establish a no-code spreadsheet solution to deliver a single source of financial truth to users, and in that goal, he’s succeeded comprehensively.
“I really like this app in the sense that it’s a relevant and complete tool that enables you to do analysis on your transaction,” says Pieter Heyns, startup advisor at FireID.
“This demonstrates how much accountants can learn from software engineers to enhance the tools that they use, in a low- or no-code way. I see potential uses for this among small businesses that want to track their finances but don’t necessarily want an accounting package, for example.”
Get involved in the Programmable Banking Community
If you have questions or just want to say “hi” to the Programmable Banking Community core team, you can pop us a mail at community-investec@offerzen.com, and we will get back to you.
If you want to see more about what the community has been up to, you can:
- Join the Programmable Banking community
- Browse our Programmable Banking community wiki
- Read the dev docs
- See more demos
- Read other programmable banking-related blog posts
