At July’s Programmable Banking meetup, participants in the low-code/no-code Hackathon walked the community through their submissions. Here Russell Knight teaches the accountants a thing or two with his no-code ProSSBanking tool, which uses MS Excel’s full functionality to make sense of bank transactions.
Transcript of the demo
Russell Knight 0:03
Can you see my screen? Yes. Okay. All right, thanks very much for the opportunity to present. My solution is a no-code solution. That's very important to understand. There's no coding whatsoever in here. I've tried to leverage the full functionality of MS Excel. The problem I'm solving for is, basically, you've got a lot of financial information, it’s spread around all over the place; I'm trying to consolidate it all in one place. I'm trying to imagine someone waking up, getting a cup of coffee and coming and sitting in front of this spreadsheet in the morning. The first sheet that I'm showing you, is basically Parameters; you set this up once. Everywhere you look in my spreadsheet and you see this red text with a yellow background, that's where you would have the person or the user actually inputting data. This just sets up a whole lot of conditional formatting – which we'll get to in a moment – which basically shows, accentuates, and also sort of removes emphasis, or de-emphasises, information that doesn't really need your attention. I've got four major components to this solution. The first is this visual augmentation of the banking information; then stock portfolio valuation; foreign currency valuation; as well as some news-feed elements. Okay. And then sort of a little bonus feature I've got over here, which is a sort of a quote that appears on most of the pages. If you push F9, and you recalculate, it just goes and draws a new one. So that's not an API, I've extracted a whole lot of data out of an API, dumped it into a hidden worksheet on this workbook, and it just draws randomly as you cycle through the pages. Okay. This conditional formatting, or this colour that I have set up, is currently fixed. But if we began to augment this with code, we could get the user to select the colours they want. But right now, they can only select the values that they want to accentuate. In this case, I've said that when you get a Takealot transaction, and I'll show it to you just now, I want it accentuated. This is the Budget portion of it. This again, the user would come and set up their categories once-off, okay. I've done this very successfully. I've been trialling this sort of categorisation mechanism for about seven months or so, and it's worked quite successfully. You set up your budget for each of these categories. And then, what it does, is it pulls that budget for whichever period it is. So, over a here, you choose which month you're working with, and it will pull the budget, and it will go and it will pull the actual value using this sort of advanced structure formula; getting the pivot-table data, which I would have extracted from the data table that is connected to the Investec API. That obviously gives you your variance, gives you percentage of each of those, so that you've got quite a bit of information to work with over there. And it'll do that automatically. As soon as you refresh the pivot table. As the data is ingested, you click on ‘refresh the pivot table’ and this will automatically update.
Russell 03:40
Okay, so moving on to the next one. This is the Classification. So over here, what ends up happening is that you give it key search terms to look for in your descriptor fields. Like I say, I've been using this for about seven months, and I get about a 95% accuracy rate without having to intervene at all. So, kudos to AI. Yes. Sounds great. Maybe mine is a very simplified sort of expenditure that I do, and because of that, I can categorise it very easily. But, like I say, I get a very high success rate and the ones that it doesn't categorise, I want to pay attention to. I don't want some AI to mis-categorise it for me. This has worked very well. There's three levels of categorisation. You can […] can drill down to quite a detailed level. And what happens over here, is these fields are using data validation, which is also an innate function of Excel. It basically draws that information from what you've already set up here in the budget. [The] pages spool through to each other. Once you've set them up, the data is there for reuse. Over here, what we're doing is we're now analysing; so, the user doesn't input any data over here. This is basically the information that was ingested by the API, that is now having the conditional formatting, which again is a natural feature of Excel. It is basically taking that conditional formatting instruction. And you'll see there's a whole lot of grey text over here. What it's saying to me is, this over here has been de-emphasised; you do not need to pay attention to this. So, I've got a bank bot that does all my transfers on a daily basis. I know I can trust those transfers; [they don’t] need my attention. You start seeing that this has been digitally augmented for me to draw my attention to the things that really matter. So, that is a medium-level expense. I've just told it, show me all the entertainment, so I can control my entertainment expense. And then, if you come down over here, you'll see I told it, Caltex. So, anything that comes up with Caltex it must accentuate this way. That's a very high expense.Here is one of importance.So, you can see over here, so if there were a fraudulent transaction, for instance, that had come through; we can see it over here. Because it stands out with this red, and then credits will come through that are above a certain value, but you'll see over here, that credit interest of three rand […] you don't want to pay any attention to that.Here’s the Takealot that I referred to earlier on. Basically, you can see in one quick look. Remember, each day, […] as a person, you'll probably only have this page to look at, and very swiftly, you can see what needs your attention. Particularly if you have an uncategorised transaction. It takes that data as well, and it pivots it. This is the standard pivot table that you can collapse and expand. And I don't know if everybody knows, and this is another Excel feature, is if you want to take a look at what that figure is comprised of, you double-click on it, and it will […] go and draw the actual transactions for you, open up its own sheet, and then you can see the actual transactions that made that up. That’s not something I've programmed or anything like that. That's a natural feature of pivot tables. It’s doing this … you can see by period, by month, basically. And then by expense category. This is just something to demonstrate filtering mechanisms. This is a formula that uses filter and sort and large and all sorts of advanced dynamic formulae in Excel**.** And it gives you your Top Five, in this case, because you have a number that are the same value, it brings them in as well. This over here is the Heartbeat of the account. What it does is it tracks your account balance, and I call it a Heartbeat because obviously at the end of each month, money comes in and then flows out very quickly. So, [it] just tracks your daily balance. And then, this is frequency, [it’s the] same sort of concept, but looking at the volume of transactions that run through. This over here is Forex. So, what happens is, this is a query. […] If you see over here, it says ‘running a background query’; what's happening is it's busy querying a website, and you'll see that there was a bit of a flash, and then the numbers changed. This, again, is a feature of Excel. And behind this, there is a query that gets run. And I'll just show it to you very quickly. I know I'm running out of time over here. But basically, it obtains it from websites over there, and they'll perform some transformation steps on it.
Russell 8:43
So as this updates, you can have your Portfolio in over here. And it will value them out according to the exchange. And I've got all the exchange rates at the bottom here. And that's the main exchange rates that it does. These are News feeds. So again, these can be refreshed. They work according to an XML map. I'm not going to go into too much detail with that, but it gives you a link, you click on the link and that news will open up. I've got three examples in there. They were very easy to set up. One is MyBroadband, BBC, and then BusinessTech. Then this is Share Price. So, what ends up happening over here is that in the background, I've got the JSE, basically all the shares; it goes to the JSE website; it picks up this table, again via an Excel query. And, basically, when you refresh it, it draws all those values in for you. It does some transformation. This one is a little bit more complex. But again, it's all natural, there's no code. And you'll see over here that it does a bit more transformation in this. So those are the transformation steps. For instance, if I show you over here … That's what it looks like before the next transformation step; when you do that, you see it split those two into two different columns. And then what you do with this, is you just […] pick a share over here. Say we've got AGL, and you put your […] share code in there, and it gives you that immediately, and you say, you've got 200 shares, and then it costs it out for you. And then there's your share portfolio value. And that's basically it. You've got quite a few […] elements of your financial picture that are put before you. And then you could also consolidate them all into one sheet if you wanted to, obviously. […] The idea behind it is sort of one source of truth and one place to go to. And, like I say, we managed to do this with zero, zero code. Okay, that's it, I'm finished.
Judges Comments
Nick Benson 10:56
Nice. Very cool. Pieter, as the accountant, what do you think of this?
Pieter Heyns 11:05
Thanks a lot, also, for this submission. Russell, I think, you know I really liked this app in the sense that it almost seemed like a complete tool that you can use. And I think it's a very relevant and very useful tool – the ability to do these kinds of analysis on your transactions; to do the classifications. Even the fact that you do have some sort of automation there, or some training where you can automatically categorise … or when you train it, and it automatically categorises information going forward. I really, really liked your submission. I thought overall as an app, it was very usable. The implementation was done really well. And it's almost something that you can basically [ship]. The feedback that I would add on it, in terms of, […] what I was missing from it, is … Oh, just before going to that, it's just also so cool […]. I've been using Excel for a long time, but you're using things here that I didn't know existed. I didn’t know [it was] possible to use data structures in the way you just [demonstrated]. There’s just so much, I think, you know, sort of accountants can learn from software developers and software engineers, that can basically enhance the tools that we use and in a low-code, no-code sort of way. These tools are just becoming really powerful, which is what makes it so much more exciting. But I think the thing that I missed was almost like, I think you tried to implement that in a pivot table and the analysis. I think what I wassort of missing was an income statement, like a proper … this is my income, this is my expenses, this is what I made … And then a balance sheet, like you have this area we can put all your assets. But you would ideally want are those two reports as an output. Those are the things that I […] from an accountant perspective, missed, and especially if I think that this can potentially have business application. It can be useful for small business, maybe a two- or three-man small business that maybe wants to track their transactions, but they don't necessarily want to have an accounting package. I think that, you know, just by adding those two things, you can basically almost deliver a product like that […] is very useful for the small business application and for a lot of users. I think there was sort of just in terms of the sheet. And I know like, you know, these are submissions that still need polishing. But I found it really hard to sort of figure out what the sheet was for. I think more of a template guide to actually guide the user through, you know, what does the sheet do? What are the actions, basically splitting a model in between inputs and outputs? And giving clear instructions as to this input section, this is what you do to set it up. I just struggled a bit with that. I couldn't figure out the context of each sheet in terms of me wanting to set it up, because I did download the sheet and I tried to get my transactions in there. And I think it'd also be easier if you actually integrated it directly with the API and transactions with spreadsheets. But overall, yeah, I think […] this work that Investec and this community is doing is really, really exciting for me, just as an accountant and being able to do this. I have my own little budget that I've set up in a similar type of way that summarises my transactions, I use the Treasury bot every day to balance my account. I'm […] actually also using these tools. So yeah, I'm really excited about the work that Investec is doing. And this is a great example of how this could be practically done in a way that makes sense and that people would definitely use.
Nick 14:54
Cool, thanks, Devina. I know that we are running a bit over time. Any quick remarks for Russell?
Devina Maharaj 15:04
No, I think Pieter covered a lot of a lot of it. Russell, this, for me, just blew me away. I think just the level of detail, the thought and deliberate kind of … you know, answering the questions that the majority of our clients have, I think for me was super-impressive […]. It was exceptionally thorough. And for my first 10 years at Investec, I was a private banker to CAs. So, I can honestly tell you that your spreadsheet would; it would just be so amazing to all of them, […] what it's delivering, the questions it's answering, from the transactional capability to the Forex view to the Portfolio view; I think was exceptionally exciting. And what was also exciting for me, when Pieter touched on it, was around the fact that you highlighted many things that I didn't even know about Excel. For me, it was just really great to learn. I really thoroughly enjoyed it. And the accounting side of me just loved it completely. And I think, yeah, I also loved your quote. So [the quotes] completely distracted me in your demo because I kept on looking to see what it was. That was really nice as well. Thank you. It was really awesome.
Russell 16:31
Just for interest, I am a chartered accountant.
Devina 16:37
Surprise, surprise. [Laughter]
Get involved in the Programmable Banking Community.
If you have questions or 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 about what the community has been up to, you can:
- Join the Programmable Banking community
- Browse the community’s open-source projects
- Read the dev docs
- See more demos
- Read other programmable banking-related blog posts
