Investec’s Programmable Banking API allows Investec business banking customers to integrate their bank accounts with their own systems, so they can sense transactions and respond to them automatically.
This demo shows how Imraan Parker has built a process to sync transactions automatically from his Investec account into Sage One. It makes the previous manual process much easier by retrieving transactions to compare and removing transactions that have already synced. It also compares any manually added transactions in Sage One to prevent duplicates.
The tech he worked with included FastAPI, Sage API, Python, Heroku, Python Investec OpenAPI Wrapper.
Click here to download the presentation slide deck.
You can find the repo and installation instructions here.
Transcript of the demo
Imraan [00:02]
Thanks, Ben for that. I was saying I am pretty excited to do this project and get something that will be kind of a real-world scenario where we can use the Investec OpenAPI. I'm going to take you guys through a little bit of what I've done, and then closer to the end, I'll give you guys a demo of how everything works together.
Imraan [00:22]
Okay, so just a little bit about me. I am Imraan Parker, also known as Rabobi. I don't know if you guys know what Rabobi is, but it is the Xhosa name for Spiderman. They call me Spiderman at the office, and I don't know why – maybe because I spin a wave of deceit over the manager's eyes but other than that, I don't know.
Imraan [00:43]
The problem that I'm trying to solve here is syncing your Investec transaction to Sage One. That should happen automatically and should happen in the background.
Imraan [00:56]
The way that they currently do it is they manually import it, and then they export it from Investec as either an OFX file or a CSV file and then re-import it into Sage, and there's a multitude of issues with that, like duplication and all sorts of stuff that could occur.
Imraan [01:16]
They use another method inside Sage One, and that is you can give your credentials to a third party and they log into Investec and get the transactions through. But again, there are security implications with that, and it doesn't work that well, or at times it does not work at all.
Imraan [01:33]
Often it switches and doesn't work for days. And you have no visibility, whether it's working or not so I looked at what we can do about that.
Imraan [01:42]
The stack I'm using is Python 3, and I'm using a Fast API, which is a Python module, which gives you OpenAPI type functionality and things like that and obviously, I'm deploying to Heroku.
Imraan [01:59]
I'm also using Vince's Python Investec OpenAPI wrapper, which he built a few weeks ago and because it's Python, it allows me to talk to the Investec OpenAPI, and the only thing I had to do was to talk to Sage and write a wrapper for Sage.
Imraan [02:17]
In a nutshell, this is how it works – after I build the wrapper for Sage, it retrieves the transactions from both Investec and Sage to compare. Now obviously, there's a date range and you can decide the date range which you want to import. Now it has both sets of transactions, it removes transactions that are already synced.
Imraan [02:40]
The nice thing about Sage is I can store the transaction hash inside Sage, and I can see if my transaction hashes are the same and therefore skip that transaction. Then I also compare manually added transactions in Sage One because you can go into Sage One and add a transaction manually.
Imraan [02:56]
I don't want to duplicate that transaction when re-importing it for a particular date. I check that using the description, the date, and the amount to see if there's one that's already done and then whatever's left over, I import into Sage One. It works like that.
Imraan [03:13]
You can run this thing five times in a day and it won't re-import already imported transactions and things like that so you can run it all the time. I'll show you how I deployed it to Heroku where I have a Cron schedule that runs every hour that syncs the stuff and goes through it.
Imraan [03:30]
Okay, so before I go to the demo, let's talk a little bit about what I learnt and the challenges that I had. Understanding the Sage One API took a bit of work as it has over 200 services that you can call and they expose every single feature inside Sage One via the API, which I think is cool because you can build your interface for Sage One, replace the interface and tell people okay, come use my nice, responsive interface which works, and at times is better than Sage’s one as their interface is clunky.
Imraan [04:03]
One of the biggest things that I found was getting a Sandbox account and Sage API access took some time. It's not just as simple as asking for one, emailing them, then they give you an API key, and off you go. There is a questionnaire that you must fill in, fill in some forms and you must sign your life away and give them your firstborn child or something like that. So yeah, it's quite a process so understand that.
Imraan [04:29]
Also building a Python wrapper for the Sage One API is a big task. There are over 200 services, so if you want to extract it and make the wrapper Pythonic, it's a lot of work to expose and to wrap 200 services. I'm thinking about a different way of doing that.
Imraan [04:46]
What I did learn which was quite nice, and I gained some knowledge on the OData, how it relates to OpenAPI, the Open Data standard, and how you can interact with RESTful web services. As an example, how you filter and do pagination and stuff like that. That was quite an interesting learning that I did not know before. I'll take you guys through some of that when I show you the coding and some code snippets.
Imraan [05:11]
Okay, so let's have a look at the live demo, shall we and hopefully the demo gods are with me.
Imraan [05:28]
So, this is Sage accounting, I'm logged in, this is my transaction list, and I'm connected to my bank account. These are my transactions in my bank account, and I've got zero transactions. So, I've got two companies underneath my Sage, and this is so, just so that I can have two companies.
Imraan [05:52]
One is Imraan Parker Incorporated, and one is John Doe. This is the sandbox environment and under Imraan Incorporated, I created my private cheque account, which then allows me to input the transaction scene. There are a few things to do there and I'm going to take you directly to the app now as well to show you how the app works, as I've got it deployed here on Heroku.
Imraan [06:17]
It's got a nice little swagger interface that will open the API interface, whatever you want to call it, to have a look at it and I have exposed certain stuff, for example, if I want to get the Sage companies that I have, I can pick it up and try it out, I hit execute and it goes in the background and gets the company so you can see there is Imraan Parker Incorporated and John Doe Incorporated, and then I can take, let's say, for example, Imraan Parker Incorporated to get me the bank account for that company.
Imraan [06:48]
I put in the company ID, and I execute, and it will bring back my private cheque account, which was that one, that's just a fake account number which I put in. That allows me to do that and then expose where you can sync transactions. Let's say I want to do that, and let's try it out, so in the sync transactions for 2020-08 just for the 2nd as an example then I can execute it.
Imraan [07:20]
This is now talking to both Investec first and then Sage, it imported, three transactions that came through. If I go back here, and I hit refresh (F5), you should see my three transactions, but sometimes this interface is a bit clunky but there are my three transactions that you can see came through automatically that were imported directly from Investec.
Imraan [07:46]
To prove to you that it did, I will go here to do the same thing so you can see this is the OpenAPI that I'm talking to. There's my Google, Afrihost and my Topeka Spur one which is the same as the ones - my Google, Afrihost, and my Topeka Spur. If I were to run this now again, it would now go back and compare, and it would say it is zero imported because obviously, they've already done so.
Imraan [08:18]
So if I, for example, deleted the Afrihost and I went back here, and I execute it again, it will give back that one that was missing, and if I refresh, it's back again. What I'm going to show you now is if I had to do it manually, so if I delete all of these, and if I import my bank transactions manually, and I have them for the 2nd which I have in an OFX file so, now it imported those very same transactions, but these are now manual. These were brought in from outside the system, and if I execute them again, it still says zero because even though they were manually done, I can still compare them and it will still say zero when imported.
Imraan [08:53]
So, it's those very same three transactions, but these are now manual. All right, so these were brought from outside of the system, and if I execute it again, it should still say zero because even though they were manually done, I can compare them, and it will still say zero were important.
Imraan [09:15]
Okay, to prove to you that it did work, if I delete all these again, and re-import, it should say three - just to prove that I'm not lying guys. Right, you believe me. All right, excellent. I can then change the date, and I can make this the 12th and then this will execute up until the 12th, and it will import the 10 extra transactions that came through, and if I hit refresh here, we should see those 10 extra transactions. Okay, so there you go. So, there are those 10 extra transactions that came through.
Imraan [09:51]
The stuff I must default, for example, is VAT as I don't know if that was paid for the amount or not, so I defaulted to no VAT. I also don't know which expense you want to put it under, if this expense was rent or was it repairs and maintenance. These are the things that I want to do in the future to be able to allocate where something should go and then automatically do that.
Imraan [10:19]
Now with the guys from OfferZen showing off their stuff last week, where they do select the category, I want to be able to map it to something in here so, when I do import it, it automatically selects it here correctly for them. That's something I want to do at a later stage.
Imraan [10:41]
The code is on GitHub, and it's quite simple to use. I've got an overview of how it works, what the requirements are, how to install it, how to deploy to Heroku and how to set up your configuration. When I imported the transactions, I didn't need to say which bank account or which company it is. The reason for that is that I just wanted some security around so that you can just do it for anyone, but the settings can set it. How I do that is two ways you can either do it via the config file, or you can do it via environment variables. On Heroku, I set up all my environment variables, and all the config files, for all my information like this so that you don't see it and then you can execute stuff, and it will just work.
Imraan [11:28]
My client secret and things like that have been set up like that so you can do the same thing and then it will just work with it magically. Then the other thing that I did in Heroku, is I set up a Heroku Scheduler, it's an add on that you can have, and I wrote a little file called Sync.py, and in the code base, there's a little file called Sync.py. All this does is it will import the last three days' worth of transactions, it calls imports and imports the last 3 days of transactions, and I make it run every hour. I could potentially make it run every 10 minutes if I wanted it to. You just must be careful if you're running on the free dinos, as your application dies after 30 minutes, and then the sync won't happen. That's why you have to run it every 10 minutes if you want it to continue working because if you run it on the hourly basis, you have to make sure your app is up and running in some other way. So that's how it works. Anything else I need to show you? Nope, I think that's about that. So, let's get back to the presentation.
Imraan [12:57]
So, getting involved, you can Slack me, email me, and as I showed you before the repo, the installation instructions are all there. If you want to learn about Fast API, the nice thing about Fast API is that it gives me that entire interface that you saw today. And, if you want to learn more about the Sage API, it's over here.
Imraan [13:22]
Next steps. What do I want to do next? I want to implement Oauth for the open AI interface that's the interface that you saw there so that I could just start executing stuff, but maybe I should have some auth so that you first have to obviously auth yourself before you can use that interface.
Imraan [13:39]
Extending the Python wrapper for Sage One as well that's something I'm looking at doing and then integrating with OfferZen's finance application categories for the transactions. That's something I'm looking forward to doing. The other thing I want to do, and I've spoken to Ben and the Investec guys, is how we can host this as a service. You don't have to take the code and deploy it yourself to Heroku and get it all sorted out and then set up your environment variables. We can somehow get a three-legged Oauth to authorise me to talk to Investec and to talk to Sage, and then be able to sync the transactions and host this as a service rather than every person having to deploy it themselves because that's the barrier to entry now.
Imraan [14:27]
Okay, any questions?
Wayne [14:34]
Yes, I've got one. Imraan well done. I'm so impressed. It's amazing what you pulled off here, and I think that just goes to prove to us how we can think about things differently and get stuff to work. So, I'm very impressed.
Wayne [14:48]
I have a question because many people tell me, this won't work because the number of fields in the API doesn't relate a comp map to the SAGE interface necessarily. Did you find that there was enough information coming out of the API or did you have to jippo lots of stuff?
Imraan [15:04]
The only problem was coming out of the Investec API. There were issues because the Investec API gave me three dates, the transaction date, a posted date, and action date. I wasn't sure which date to use, that Sage would use. I realised it was the posted date because I tested the import, so that's one of the things that got me a little bit.
Imraan [15:39]
The other thing was that our transactions in Investec don't have an ID, so it wasn't easy for me to set something on Sage to say you've got this ID and I had to do the hashing, but that was fine. Other than that, everything else was in the API, and it worked, and it works quite well.
Wayne [15:54]
Okay. Cool. Super impressed.
Ben [16:03]
Yeah, man, thanks so much. Awesome demo as well. You've just approached this project with so much professionalism, which is great and it has been awesome to work with you so far on it. I mean, you've been doing all the work, but it's been awesome to watch your work.
Ben [16:20]
Have you worked with Sage before?
Imraan [16:23]
Not with Sage One. No, I've worked with other accounting systems, and that's why I knew how to work myself around. When I registered on Sage One, I knew more or less where to go and look for stuff and they all kind of work in the same way - accounting systems are accounting systems, they have different nuances, and that helped me a little bit having that knowledge from other accounting systems to get on board with Sage One. For somebody new to Sage One, it might be a bit of a learning curve.
Ben [16:52]
Then for those categories, have you considered using any of the other kinds of categorisation tools that have been built so far, some machine learning tools.
Imraan [17:02]
Yeah, I thought about that, but I think for every customer it might be customised to you. For example, when I looked at what you guys did on the OfferZen demo, those categories are completely different from the categorisation for all your personal expenses.
Imraan [17:32]
It would be more of integration at that kind of level where the companies want to sync their transactions. This is how I map Sage to my categories, so this is the Sage category, and this is my category, but that mapping exercise would be a once-off, and everything will just work after that.
Ben [17:52]
So, on Sage, can you not make your categories?
Imraan [17:56]
You can make your categories, but you still need to say this is my Sage categories, you need the ID of the Sage category and then link it to my category.
Ben [18:06]
Okay, cool.
Ben [18:09]
Now, what was the best part about working on this project for you, or the best, parts of it,
Imraan [18:14]
In the beginning, getting the whole thing to work as I wasn't sure that we would be able to sync everything properly because I wasn't sure that the data was all there but we found out the data is there and it works quite well. Learning about Sage and OData was quite interesting, and obviously, I learned a little bit more about Heroku as some stuff I had not done before as we don't use Heroku much in my environment.
Ben [18:14]
Cool. And what was it like working with the OfferZen team or working with a business.
Imraan [18:52]
That was great. It was great. You, guys, we're very responsive. Monique was the most excited person more excited than all of us combined when she saw it working. So, it was quite nice to be able to talk to somebody who's going to use this in the real world and make a difference.
Ben [19:10]
Yeah. And what are your hopes for this contribution or your contribution to the community? What are you hoping it turns out to become - Your idealistic answer and your realistic one?
Imraan [19:24]
My idealistic and realistic answer, I think they can both be the same in a sense, I think this can grow into something where we can build tools that work with Sage One, and Investec together. As the OpenAPI specification opens up, you can start doing transactions and all those kinds of things and this can be a real tool to use to build a whole new application around being able to talk between Sage and Investec. You can do a lot of things around that.
Imraan [19:56]
I think that would be great, especially when things like virtual cards come on board, so you build an app for that but then you automatically sync the transactions to Sage when you're done, and you categorise it, and it's all done, and the finance team don't have to do much.
Ben [20:10]
Cool. Do you have any questions for the audience or anything that you'd like to poll people on?
Imraan [20:19]
You guys saw that there is over, 200 services that Sage API has, so I'm trying to build a wrapper for that, but I don't want to implement 200 services, but I still want to expose them in a Pythonic way so if you guys have ideas on how to do that holler and get in contact with me.
Ben [20:50]
I'm wondering if anyone in the audience has dealt with accounting systems – are there any other integrations that we should be thinking about or putting up as challenges? We are looking for more challenges to fund, but we want to try and find challenges to fund that would have a high chance of getting used without too much searching.
Audience member [21:13]
Ben, I'm not sure if this is perfect for the SA market but YNab is a good system. It's more for personal use than business, I think, and I might have the integration for it, but I think that's a very useful one to have.
Audience member [21:42]
The other thing I'm wondering is how we can find the Investec clients who are using Sage because that would be an interesting avenue to approach those clients and be hey, we've got an integration do you want to use it? Wayne, can you throw some ideas out there on that?
Wayne [22:04]
The interesting thing for me about what Imraan built is, if you have a multi-sided platform like you have in this model, what you potentially have is somebody that can integrate into payrolls upwards like Sage, and you could extend that, obviously to [] Zero and Sage, and let's do XYZ, then keep on expanding on that side.
Wayne [22:23]
And at the bottom side, you can potentially as the banking system in South Africa opens up, plug in all the banks so you could end up with a piece of software which is quite powerful, which can facilitate bank to payroll interoperability across multiple, payrolls upwards and banks downwards.
Wayne [22:39]
Now, that doesn't help me as Investec much, but this is a powerful way to think about technology and how we can unlock potential with these open systems. So, I'm excited at what was done so quickly and the point that it wasn't a six months project to get anything done is amazing.
Ben [22:58]
Wayne have you guys considered building a Sage Integration or organizing a Sage integration from your side at all.
Ben [23:05]
Never, I'm allowed to say, but if I say something wrong, it wasn't me okay. We have been trying to build one, but it's very hard to build something like this in a bank because they don't see it as an ecosystem play or an open thing. I see it as Investec talks to Sage it's a bespoke integration. The interesting thing about this challenge is to say, how do we get everybody to work better together to leverage the power of this more.
Ben [23:35]
This was a bit of an experiment to see if it would work, to be quite honest, I'm very pleased that it worked out well. Cool.
Ben [23:50]
All right, Adrian, I think we're going to Well, you're up next, but I think we'll be looking at Sarah and QuickBooks as the next ones.
Ben [23:57]
Wayne, I guess one more question I had you were saying earlier about creating that system or that thing, which integrates between accounting systems and banks is agnostic? Does that exist elsewhere in another part of the world?
Wayne [24:14]
There are many people playing in the open banking space, especially overseas. Financial service organisations will struggle to interface into multiple third parties because each one has a cost to it. If I have to say, Investec interface to Sage, then Zero, then blah, blah, blah, blah, it becomes a very expensive exercise.
Wayne [24:34]
You do see the emergence of these companies that sit like aggregators, between large financial services and third parties and help them with that integration work to make it easier. This feels very much like it could be something in that space but I'm not aware of anybody else doing this in Africa, and internationally I don't know.
Ben[24:57]
Okay, cool.
Ben [24:57]
All right, thanks, everyone. I don't know if anyone else has questions, you can save it for the end as well. We'll have a chance to ask check questions in general. But again, thanks, everyone.
Find out how programmable banking can work for your business!
If you’re interested to find out more about how you can use programmable banking at your business, please pop me an email.