The Programmable Banking community got together to demo a few of their Transfer API solutions in the Q4 2021 Investec Programmable Banking hackathon. Here Russell Knight presents his user-friendly BankBot solution, which automates transfers to and from your accounts, balancing them to your needs and habits, all using Excel. It even has a social conscience!
Summary of demo
- Russel Knights walks us through his BankBot solution using the Investec Transfer API
- The solution performs three main functions of utility for the user;
- Manual instruction for transferring funds between accounts
- Auto transfer of funds based on set parameters to optimise your interest
- Based on predefined types of transactions, i.e. buying takeaways, it determines the amount of SinTax. It does an automated transfer to a savings account, then to a charity of your choice.
Transcript of the demo
Russell Knight 0:00
All right, I'll kick-off. Thanks very much for the opportunity to present. So, mine is a sort of complex, simple solution. It's simple in that it's been implemented on a spreadsheet, because most people have got access to Excel, with Visual Basic for applications as the basis for the coding in the background. The purpose of this, I call this BankBot, because it's an automation tool of mine for my personal finances.
How It Works
And there's three major things that it does from a transfer perspective in terms of utilisation of the Transfer API. The first is that it can just do a manual type of transfer, where I just instruct it on the amount that I want transferred, which is fairly simple and uses the API to put that transfer through. The second is what I call Auto Transfer, which is similar to what Frikkie has just discussed. I've got a sort of threshold amount, but I've also actually got a sort of a band that you can set.
So, when it looks like it's going to drop below the threshold amount. And the reason I say "looks like" is, it also considers the pending transactions that are coming in.
By virtue of taking the amount that is available, you know what your credit limit is, and you're thus able to work out what the pending transactions are. And then what it does is it transfers either in or out of the account according to what is required. I'll get onto the automation a little bit later.
The automation is a little bit clumsy. It works, but it's not as good as what Frikkie just showed, I reckon.
The other thing that it does other than transferring in and out automatically in order to optimise interest across your accounts, is I've developed a thing called SinTax.
So very quickly, the idea behind SinTax is when you do a naughty spend. Naughty spend is buying takeaways, going to restaurants, perhaps even putting in fuel – when you put in fuel, you're damaging the environment. You should really be donating X percent of that amount to some charity. And the idea behind it is I've repurposed one of my savings accounts as a SinTax account. And I'll show it to you in a moment.
And then what happens is each time that transactions are ingested, it runs through a calculation and determines the amount of SinTax and does an automated transfer. And the idea is that at some point during the year, I'll take whatever's in the SinTax account, and I'll distribute it to the charities that I support. So, this over here: the modules of the solution are housed in different worksheets within the workbook.
This is the Parameters worksheet. You'll see I've got 30 parameters over here. I obviously don't have time to go through them. I want to maintain my account at 5,000, the lower limit. So, if after the pending transactions it’s going to drop below 5,000, it must restore those; then I've got switches for enabling into and out of transfers over here, which I've got set on with a "yes". And I've got "Is your SinTax activated?", and I've said yes, so it will calculate SinTax. Okay, so those parameters have been maintained. The next thing we'll do is we'll check our portfolio. I've got quite a bit over here; I've got my entire financial portfolio, including accounts with other organisations, my investments, etc.. I've just blanked them all out with white text so that you guys can't see them. And these are the two accounts that we're concerned with.
The first is the main account, which is currently sitting at that balance over there. And there is the pending amount that is about to come through.
So, you can see if you take the 2 800 off of the 7 800, you'll end up with 5 000. So BankBot has currently got it set at 5 000. I've currently accumulated R84 in the SinTax donation sort-of fund or ring-fenced fund. Take note of that figure for a little bit later. Okay, in the background as well, what the solution does is it also associates supporting documentation. If you look in my BankBot folder, I've got these over here which are basically just images or scanned PDFs of the expenditure that I've done. As my wife goes and spends my money, she hands me the slips, and I take pictures of them, and I just drop them in that folder. It's a cloud folder. Okay. And then what I'm going to do now is I'm just going to transfer out of the main account to force BankBot into saying, "Hey, you know, you've used up some money". I've got account numbers in here, which I've also blanked out in white text, so that they don't come up on the recording. And what I'll do is I will initiate a transfer. If you take a look over here, you'll see my current token has expired. Okay.
And what's going to happen now is when it executes the transfer, it takes a little while to go and get a new token. And then you'll see over there that amount has now been transferred.
You'll also see I've got rich text messaging, so it says I've transferred 575. And it uses friendly account names, even though I've got the account IDs in there, it actually translates them into the friendly name so that I don't expose that data on this messaging app. Okay. Now it gives you a confirmation code, etc. And it gives you fairly rich text, to tell you what it is that it's done. Then, now that we've done that transfer, and we know that we're in a state where BankBot is going to need to take action. I'm just quickly going to show you the other spreadsheet. If you take a look at transactions. Now I've blanked all of this out in white text, but I've got a year's worth of transactions below here. It always feeds in at the top, so you can see there's no transaction sitting up at the top here in black text. And the temporary housing solution is this API worksheet, which is where the data gets brought in, before it gets ingested into my system. And what I'm going to do now is I'm going to show you, I'm going to basically ingest a whole lot of transactions that are pending that haven't yet come into the system. And I'm going to show you the steps that the system will go through now in order to basically follow through with the automatic transfers and SinTax calculation, etc, that need to take place. Okay. All right. So, let's go now to my portfolio.
I'm going to click on this button. And what it's going to do is it's going to execute the same process that I have that takes place on a daily basis. It'll take a little while to process. You'll start seeing messages popping up. It went and it found 64 transactions. And what it does is, if you take a look over here, it tells me what my current balances are. It then says, "Ooh, hang on, if I take 7,200 minus 2,800, we're going to have a problem here. You've dropped below your R5,000 limit". It then does a rounding factor. It rounds to the nearest 100. And I've got a parameter...
Hagashen Naidu 7:14
One second, I'm not sure if you're expecting us to see something that's happening. But...
Russell Knight 7:19
Oh, goodness. Are you're not seeing anything at the moment?
Hagashen Naidu 7:22
It's just on the portfolio summary that's the only screen we are seeing at the moment. Is that correct?
Russell Knight 7:27
Sorry. Let me transfer this then. Thanks very much for letting me know. Sorry. Let me actually share my screen. Are you able to see telegram messaging now? My apologies for that. Sorry, guys. Thanks for letting me know. Okay.
This is the first message that came through. This is where I transferred the 575 earlier. This is it telling you how many transactions have ingested into the system. This is telling you what the balance is that it's extracted. And it then says, "with that balance, you're going to drop below your 5,000 parameter that I've set", and it does a R600 transfer. It rounds up to either R100 or R1,000. You set that in the parameters, and it tells you what your new balance is. It then ingests the transactions, everything with a tick here. It was able to do what's called an Auto Classification. I'll show you classification in a moment.
Okay. So, it classifies those expenses for me for reporting later on. It then performed the calculation for the SinTax and came up with this value, and it performed an automated transfer and put it away for me. And then it associated the files, those PDF files that I spoke about earlier; it shows you which ones are associated, and which ones couldn't be associated.
Okay, I need to speed up.
It does a quick budget assessment and reports; I'm not going to go into detail on that. Okay, I'm going back to the spreadsheet. Can you see the spreadsheet? Okay. All right. What happens now is if you go and take a look at the transfers, you can see that there was a SinTax transfer, and there was a R600 transfer to restore the balance to the account so that it's about 5,000 or 5,000. And how it calculated that SinTax is if I go to the transactions over here, and I make these invisible. I'm just trying not to show my personal finances here. Okay. These all the transactions that came in.
And effectively what happens is, over here, if you double-click on one of these, the associated document will actually come up. So over there, there'll be a fuel receipt. And if we take a look at the sheet; if you look at these ones that are red, they don't have receipts, and it's just alerting me to the fact that I will need to get them later on.
Very quickly, if I take a look at the portfolio, and I'll say, let me refresh these balances. You'll see that the balance increases by that R7.63. And then you've got 7Â 880, which is what the balance has been restored to make sure that after the 2,800 comes off, it will still be above the 5,000.
Sorry, I've run out of time. And that's basically it.
Just to wrap up very quickly. There's an automation portion to this, where I've got a script that is basically executed by Windows Task Scheduler. And that can do it as often as I want to. I only currently do it on a daily basis.
When you go into transactions, you've got the standard Excel functionality where you can effectively filter. You've got full control of your information as a layman. You can say, "I want to see everything that I've spent on restaurants", for instance. Sorry, where am I clicking?
I say, "I want to see everything of restaurants". And what it will do is, it will confine that to that. It's very easy to see; I can always click on to check my supporting documentation, etc. That's basically it. Any questions? I've got some more reporting in the background, but I've run out of time.
Community Questions
Nick Benson 11:48
It's very cool, mate. I think if anyone wants to see more reporting, they can chat with you in the Slack group and stuff.
Hagashen Naidu 11:57
Yeah, super well done. Lots of features; it covers a hell of a lot of bases. The fact that it uses Excel is also like a massive plus, right.
It makes it feel like anybody can come and grab this and use it, which is a huge plus as well, for sort of everybody and not needing to necessarily know a ton of coding or to know how all these things work in the background. So, ja, really impressed.
I don't have anything to add in terms of what you could have done more. There were just so many features in that. It's very cool. Well done.
Russell Knight 12:42
Thank you.
Hagashen Naidu 12:44
Apologies, but I have to leave now. Everybody else, well done. There were some really awesome submissions. And we had lots of fun going through them and evaluating them. Thank you everybody for your submissions and taking the time and making the effort to do something for the community and for the hackathon. Enjoy the rest of the evening. All the best to the winner. Enjoy your prizes. Thanks a lot. Cheers, everyone.
Nick Benson 13:13
Thanks. Cheers. Anyone else have any more questions? Anything to ask Russell?
Peter Thompson 13:23
Russell, I was wondering maybe about your transaction classification? How are you doing that? Are you only looking at point of sale? And like using the merchant category codes or? Or label?.
Russell Knight 13:39
I neglected to actually show that portion of it, just because I was running out of time. But I've got a ... and I'll show it now very quickly if I can. Let me just do a quick screen share again. I've got a classification spreadsheet over here. And effectively in this, I basically take a key word from the descriptor field. For instance, I know that Zapper is where I would normally spend restaurants. It runs through this entire list that you see over here, with each transaction very quickly, and basically says "is this keyword in the descriptor field?". If it's in, then classify it as an expense and entertainment and restaurants?
And "No, I don't need supporting documentation" or "Yes, I do" and apply SinTax to it. This is like an area of master data that a user would maintain beforehand according to their preferences and it can be easily added to, so you just add on to the bottom. It dynamically goes and looks how big the spreadsheet is. And basically, the user’s got full control over that.
Peter Thompson 14:59
Thanks!
Nick Benson 15:00
Guys, any more questions you can ask in the chat or in Slack.
Rijnhardt Kotze 15:06
One question. If you spend with the card on the after hook, there's actually a merchant categorisation that comes through. I don't know if that is something that you could potentially bring into your master sheet, because that could help with your automatic categorisation.
And have you had plans to bring this to Google Sheets, perhaps? Because I don't run on Windows.
Russell Knight 15:34
[Laughter] I was expecting the backlash, and I take it on the chin. And yeah, I actually am very interested to see what happens in Google Sheets. And that's something I'm going to experiment with. I've not done anything on Google Sheets with it. But your idea's a fantastic one. I hadn't even considered that. And I think I will explore that. I've chewed up a lot of time, Nick.
Rijnhardt Kotze 15:59
Overall, it was phenomenal.
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 and we will get back to you.
If you want to see more from what the community has been up to, you can:
- Browse the community’s open-source projects
- Read the dev docs
- See more demos
- Read other programmable banking related blog posts
- Get access to the community
