Programmable Banking Community: Banking On Spreadsheets

14 December 2021, by Nick Benson

At the November OfferZen Investec meet-up with the Programmable Banking Community, Malan Joubert and the team shared their mission to help non-technical people better understand programmable banking. Their solution?

To use a language that any finance manager can understand: spreadsheets.

The team – Pieter Heyns, Loftie Ellis, Shannagh Hare and Aretha Cooper – talk us through their proof of concept: a simple Google Sheets tool that will manage your bank account balance.

Transcript of the demo

Malan 0:04

I can’t remember exactly how it happened. But Pieter – if you guys don’t know, he is the CFO of Luno. He’s got a finance background and knows a lot about kind of finances and crypto and stuff. And sort of in Pieter’s world, everything happens in spreadsheets, basically. I imagine, even wars are kind of managed over spreadsheets because companies certainly are. Everything sort of happens in spreadsheets.

That is a confirmation.

And we’re like, okay, just trying to sort of connect to spreadsheets might open up some interesting applications.

And the one problem I faced is my spreadsheet of choice, or the one that I only really use is Pivot Tables, we’ll get to that. That was definitely the hardest part.

We had a three-hour frigging Pivot Table debugging in this thing. It was so rough. But I generally like Google Sheets. And they’ve got a Google Apps Script, which is like a JavaScript-based macro language if you will. I don’t know if you guys know, I actually just saw now, I’m wearing a joke shirt.

If you don’t know, this is definitely referring to JavaScript. And I was a key contributor to the shirt because I just find JavaScript insanely confusing. So, it was not a joke for me. Okay, so given massive confusion on JavaScript, we, fortunately, managed to convince Loftie to join us.

He actually knows what he’s doing in JavaScript. Like 80% of the time, he can tell you what this means in a given context, which is basically 80% more than me. Okay, we all set off. And the main problem statement was making it understandable to non-technical people. I think so I’m pretty sure. I hope I’m not wrong.

The Problem

Malan 02:31

Sort of the main idea was to make programmable banking understandable, basically.

Also, Devina, who heads up the project, has some senior person at Investec every week, asking her what on earth is the point of this thing, and they all tend to be finance people.

The Solution

They understand spreadsheets, not really API. So, she was, very excited. We were like, hey, well, we’ll make it into a spreadsheet. And the main idea was basically to kind of explore that really quickly.

It’s a bit of a stretch to say they all use Google Sheets. The start-up people tend to use Google Sheets, and the other guys use Excel, but a quick, cursory internet search, verifies that Microsoft has abandoned VBA macros. You can now actually get a JavaScript macro framework for Excel.

I thought, ‘Yes, this will transfer’. So, we were off to the races. And what we did, which worked surprisingly well, is we set out sort of half a day, I think we started about noon.

Is that right, Aretha?

And we basically worked on it till about 6pm. We basically said, okay, cool, what can we do in that time? And the really cool thing we found with spreadsheets is you can prototype crazy fast. You’ll see in the board on the bottom right? That’s me and Pieter.

When we kind of had an idea, we’d mock it up in the spreadsheet, put it on the board, write the interaction diagram, do another round. And that worked surprisingly well. From prototyping, what have you, I can really recommend spreadsheets, not so much on the UI, but great UX, great prototyping.

Basically, our kind of R&D loop or development loop was to quickly mock up the screen and paste it on a Miro board. Try to write the back-end JavaScript for it, and then regroup an hour later and do the next thing.

And that worked surprisingly well.

We moved surprisingly fast to a large degree, thanks to Loftie. He might have been faster if he wasn’t fixing my bugs, but we got there in the end. So that’s, like the main idea that we wanted to do .

How It Works

Malan 5:06

The way we thought about it is, having your main account – what I call a main account, the account your card is tied to – to always have a reasonable amount of money in, but not too much.

Because a) that’s scary and b) bad interest. And, on the other hand, not too little, so your card doesn’t work at a shop. It was kind of the idea, instead of having to transfer that around. My original idea was to just set a target amount, and then it balances. And it took about five minutes for Pieter to explain to me why that does not solve the problem.

Especially in a business context, he’s like, the way business works is – and I should probably know this – it’s payroll at the end of the month, and you need a lot of money in the account from that day, and then at the start, you don’t. We quickly realised that, okay, basically the amount you need changes over time. It took us a few spreadsheet mock-up iterations to get to something that I think actually solved it quite elegantly. I definitely can’t take credit for the answer, but I do like it.

I’ll show that to you guys. So that’s the basic idea. We realised, okay, the idea of the right amount of money in an account changes over a month.

And then Aretha chipped in and explained, that’s kind of true for being just a normal person as well, which made a lot of sense when we thought about it. You’ve got a debit at the start of a month, maybe your debit orders. So, the amount of money you need in your account changes over a month. And generally, when we did a poll, we like realised, oh, we all actually kind of understand what that amount should be. Okay, so that was the plan: fast iterations.

What happened? I’ll just share the link afterwards if you want to get a copy of the code.

The reason I’m starting with this is to say,** this is the kind of code your mom warned you about when you’re growing up**, or she should have warned you about.

So, have a look at it; it was built in half a day to prove a concept. It does sort of work; I have been running it for a few weeks now. And my account balance is staying in the line, I haven’t yet lost money, and it can only transfer internally.

But just be wary of this. If you’re going to use it, just have a quick scan through the JavaScript before you go nuts. It is certainly not … this is just the POC level; it is not hardened.

Malan 07:28

Okay. So basically, what the code does is there’s a bunch of sheets. And we create a sheet for every account in your profile. So, this is an example of Shannagh’s test account. And you basically pull all of the card transactions in one view. So, it’s one thing, as you get just like an account pool, and you can see running balance and a bunch of things. Then behind that, there is an app script.

I’ll show you guys just now sort of how that links in. And that’s kind of interacting with both the spreadsheet and with Investec at the backend. And then next to the account view is “Treasury rules”. So that is where you set the optimal balance.

Can you guys see that graph?

Sort of on the middle row, kind of straight in the middle, that’s basically saying this is the lower and the upper limit of balance to keep in that account at any given time. You literally just set it in the spreadsheet, like 200 and 300 Rand at the start of the month, and then it drops to 100 and 200.

And for each day, you can set a minimum and a maximum. And you just do that, and then you just connect it up to a Treasury account. That’s like the parent account. You just literally enter the Treasury account number in C 14.

What it then does is, if there is too much money in this account, it moves the excess money to the Treasury account, and if there’s too little, it pulls money from it. It sort of just auto-stabilises on that kind of line that you provide. Does that make sense? Cool. Okay, so super simple. And kind of how it works is. By far, the hardest part of this was spreadsheeting. Oh sorry. Yes?

Community Questions

Rijnhardt Kotze 9:22

Okay. With the minimum balance, does it take out of Treasury into your account if you’ve reached the minimum?

Malan 9:27

Yes. It does bidirectional, so either excess or minimum. And what it does, is it resets. So anywhere in the zone, it’s happy, but when it pulls money from the Treasury back in, it always shifts you to the middle. It kind of resets you to the middle of your safe zone and then lets you kind of walk in that safe zone until you go out and then it locks you back to the middle. Does that make sense?

Cool. Okay, so just sort of how it works – if you’re looking at the file.

The Google Sheet file actually contains two things, one of which is hidden. The one sort of obvious explicit thing is the spreadsheet, which you can see, and you can do things to it. But it actually also contains an embedded JavaScript thing that you can only access if you go through the Editor, which I’ll show you just now. But just note, these things are tied together in the same file; the one part is just hidden. That embedded JavaScript has a link to the sheets.

There’s a Google library to manipulate sheet functions, and then it does outbound calls to Investec. So, as I said, the security model on this thing is amazing. You literally enter your API credentials in a spreadsheet, it pulls it from there and uses that to auth.

[Laughs] You guys are all developers. So, note this, this is not something you want to share around. Yes, yes. Wayne Summers, good one. Wayne knows exactly what we did. He told me we can do it as long as you don’t ever share it with anyone.

Okay,so that’s how it works. Just to explain some of the bits if you’re poking at it, that you might find interesting. I’m just also realising that presentations are really bad for explaining code. PowerPoint is not good for code. And the one example of how you interact with the spreadsheet is the Get API settings that pulls it from the sheet. Google’s object there is a spreadsheet app.

And that lets you kind of link to the current spreadsheet. That’s just one aspect of it. Then there’s a bunch of very strange normal spreadsheet or normal sort of spreadsheet-type logic and functions.

A lot of that has to do with the fact that this thing auto-generates sheets for you. It connects to the API, gets a list of all of your accounts, for each account, it generates a bunch of sheets and then has to cross-reference those. It pulls all of your transactions in one, references that in a View sheet, references that in the Treasury sheet, but these sheets don’t exist yet.

This took us a while to realise that we need references between sheets that don’t yet exist. If you see things like “is number now”, “indirect”, blah, blah, blah, That is a massive hack to make references between sheets that don’t exist yet.

When I proposed this, Pieter first laughed at me a lot, then cried a little bit, and then finally relented and admitted that it was the only sane solution.

But if you are wondering, it works. Yeah, sorry. It’s amazing how well it works. Yeah, and we definitely couldn’t think of an alternative because you literally need references between sheets that don’t exist. And then copy that template over and reset the reference. If anyone’s got a better plan?

There are some pretty funky things in there. But, yeah, it’s all on the same sheet. I think more fun. If you guys want to laugh, let’s see if we can actually get this going. Okay, so you should all be seeing this. I’ll share a link later. And you can set this up like literally, you type in a client ID and a secret in there, and you’re off to the races. These images are hacked. They’re actually buttons. You can actually have an image and assign a script to it. Very, very cool. And that actually runs whatever JavaScript function you’ve got in the embedded code.

For example, suppose I click “Update transactions”. In that case, you’ll see it starts running a thing that invokes that specific function and the embedded JavaScript in some kind of the equivalent of like an AWS Lambda. They’ve their own little execution engine. And then it’s done.

Malan 14:26

So, let me just jump to an account view. Nick Benson’s got a test account for the community we’re using. We’re not actually just taking Nick’s personal money. Don’t worry, we’re not that mean.

So, this is the account view. Basically, you’ve got all of your transaction histories, and you can set in the code, how far back it pulls. You’ll see where that works.

The indirect stuff is happening over here, and the raw data. All of these sheets are being created off an account list. So we’ve got this account list here that gets pulled. And then, you can tick-box which you want to enable for spreadsheet banking. And what that then does, it takes the template sheet and generates instances of the view, the Treasury rules for each specified account, and also a kind of a hidden one, where the sheet just dumps the raw data. It got pretty dodgy when we had the script dumping raw data into the View sheet.

We realised splitting them out created much more sanity. If you’re wondering what the third sheet is for: sanity of spitting out data. That’s the high level. What we should see … So, I’m going to the Treasury rules for that account, just to give you guys a kind of a demo. Here we are. I just ran it earlier. I put my balance at 350, right in the middle of my point. But just for demo purposes, I’ll change this to 300, only on that day, then I’m way over. And in terms of the question earlier, if you now run the Treasury rules, it should put it at 250. Smack bang in the middle of 200 and 300. Now just because it’s a live demo, and it’s the kind of thing one should do, I’ll show you how to actually set the rules.

Malan 16:26

What I’m going to do is I’m going to jump into the Editor. So, the extensions app script takes me into the embedded code. You can’t actually access this file anywhere. You can also have loose script files. That confused us for a while, but an embedded one is not accessible. You see all of your code here.

What I’m just going to do is I’m just going to set the Treasury function to run, just to give you an idea of how that would work. “Do Treasury” is a very well-named function. I can set a timer. I’m going to go every 12 hours. I click Save. I think “do Treasury” sits in here.

So, this is another way to basically do cron-type things. And now that function gets called every 12 hours. Every 12 hours, it’s going to auto-balance my account. And if I change my spreadsheet rules, it will auto-balance to that rule. I will go back into the Editor just to show you guys around.

So, you can have different files. And you can see all the code afterwards if you want to play around. If I run that, you get a little console. It’s transferring 100 Rand. No errors. That all looks good.

That actually worked. It takes Investec a second to catch up. I call it a mainframe system – I think only half in jest; I don’t know if there’s an actual mainframe somewhere – but it takes about a minute to update balances. But that’s gone through in “Order balance”. And if we refresh transactions, we should see it’s 100 Rand less. Thanks, Wayne. Cool. That’s the end of the demo.

I’ll share the code afterwards. But are there any questions?

Malan 18:39

Just for fun and because you stayed to the end and everything, there’s your very own dodgy spreadsheet.

I think the permissions should work. If you want to make a copy, and if you want to live dangerously, you can just stick your credentials in there, and it should work.

If you want to be smart, maybe just read through that JavaScript first before sticking your bank access into a random spreadsheet that some guy on the internet gave you. [Laughter]

Because it’s really not going to sound good when you explain to your partner what happened with their money. Cool. Any questions?

Does it all make sense? Cool.

If anyone’s got some ideas, please** do shout. Some of the ideas today; I thought you could probably retrofit and use that code as a hacky blueprint. Definitely Brad’s idea. I think I would just straight-up run on that. And so cool, thanks very much, peeps. I really appreciate it.

Nick 19:51

Cool. Thank you, guys. Thank you, and well done to Malan, Loftie, Peter, Aretha and Shannagh. Great job building that out. Thank you for sharing that journey with us. That was cool.

Malan 20:03

Thanks very much. It was super fun. It was a really fun project. I can highly recommend asking any of that team to do something.

OfferZen_Investec_PB_How-we-built-spreadsheet-banking-in-a-day_blog-inner-article-image--1-

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: