Why? Because I want to budget so that I can spend more where I can and cut down where I have to.
Simple, right?
Before we start, let’s go through the lay of the land. I have one bank account (HDFC) & two credit cards (Amazon ICICI & OneCard) from where I spend.
I invest through Zerodha, but tracking investments is a whole new ballgame and I don’t want to get into that.
Now about spends. Pretty simple setup, whenever salary hits my DBS account (which I’m trying to retire), I transfer it to HDFC and run all transactions out of the HDFC. Depending on offers, I spend money on the credit cards if it makes sense & usually pay the bills in under 20-25 days.
About the transaction mediums
- UPI for day to day: 80% of my bank statement is UPI spends (I’ve crunched numbers btw). Range from >5 to < 5000 INR usually.
- NEFT & RTGS: for cross account transfer
- ACH (Automated clearing house transactions): For all the auto deducting subscriptions, mutual fund transfers etc.. volume wise these are less transactions but amount wise they are significant.
First try: Account aggregator
Account aggregator in principal is awesome, one singular place where all account information is available.
But that’s the catch, it is available. But not to you. It is available to companies to profile and sell their business.
I have nothing against it by the way, making money is important. I would have loved if some company offers a paid API to pull transaction data so that I can setup everyday sync with the budgeting platform of choice.
This is not a groundbreaking idea, Plaid is a company which does it in the US, a lot of other apps like YNAB use for syncing transactions. But no company in India provides this as a service. And interestingly no company is solving for budgeting yet in India. Why not?
Since I don’t have access to my own data neither through API nor through CSV exports in the Account Aggregator ecosystem, on to the next try.
Second try: Bank statement parsing
Every bank worth its salt gives a CSV export, Right?
Not really (for example Jupiter money does not), but some banks do. For example HDFC does!
You can download the last five years bank statement in multiple formats ranging from CSV to excel to MS Money..?
Anyways, we need to download “delimited”, which is “delimited” by a comma. Nice. But on downloading you see a txt file…? How did that happen?
Well because HDFC only said it’ll share a delimited file, it did not say its a csv file. Right? so the text file is actually a csv file. You’ll just have to rename the extension.
On open, the first line is empty.. that’s not a deal breaker. But then you look at the timestamp, which is impossible because there is no time stamp in the export file. Only a date. So technically there is no ordering of transactions.
I don’t understand the rationality of these technical decisions for some reason. I’m sure the server is reading off a database, I’m sure it is storing the timestamp for reconciliation exercises. Why not share it? Is it because non tech people get scared by looking at timestamps? I don’t honestly know, anyways moving on
Now that the CSV piece is sorted, I quickly wrote a python script which parses and transforms the data into a simpler CSV. And then added another step to pipe it into Actual budget. My budgeting apps of choice.
I wanted to develop a pipeline where I can forward the bank statement to a telegram app & it parses the files and stores it in the budgeting app of choice. Because the whole notion of doing this every week on a desktop seems hectic to me.
So I open the HDFC mobile app, and go to the statement section.
On clicking “Request for a statement”, I realize that HDFC mobile does not have the option to generate a “delimited” statement. Only excel & PDF. WHY?
Alright.
I’ll write another parser for excel then, since its the only thing available for both mobile and desktop. Not a big deal, since the core logic is the same.
Side quest: Extracting data from narrations
See, Banks don’t give you what you want. They give you whatever they feel like. In a statement, it would be nice If I had columns as such
- Timestamp
- Payee
- Credit/Debit amount
- Mode
- Payee identifier (could be bank account : IFSC, UPI handle or none for something like ACH)
- Reference ID (Something along the lines of UTR)
- Narration (for description/notes)
All of which is nicely laid out in their database I’m sure.
Payee context is important because If I have 50 transactions of swiggy, I need some way of saying that all of them are from me, the individual. To swiggy, the company without doing sub-string match kung-fu. I cannot really budget if I don’t know whom I’m paying to through my month.
But no, life can’t be that simple in Banking.
Banks just share
- Date
- Narration
- Amount
- Ref Number (UTR ID)
and that’s it! Now its my job to do sub-string match kung-fu and extract data!
What the **ck! (I was saying “what the heck” there by the way, messed up mind you got)
There is a way though, at least for HDFC narrations all UPI narrations start with UPI and delimited by – have the payee name, IFSC and narration. Similarly for NEFT and RTGS.
Sidenote, if you think the Ref number will be unique for all transactions. That’s not true. Some reversed transactions can have the same UTR (Unique transaction reference). One more thing to mention, if you have a mutual fund auto deduction or auto sweep setup for FD, then the reference ID can either be 0 (on CSV) or 00000000000 (on excel). They could have just let it blank, why have a variable length string of zeros?
Anyways. Moving on..
After dealing with all this, I wrote a parser in python through which works pretty well. So now I have payee information along with the narration. This later on becomes very useful since I can say that everything from Swiggy is food in my budgeting app.
But then, 80% of my transactions are UPI, right?
And all of them are small size payments done to local vendors. Normal people who don’t have a commercial entity… How do I know that I paid 250 to an auto guy for travel when 20 days back if the narration says “sidappa@ybl”?
Another side quest: Adding category context to UPI transactions
One solution is to type out some context in the UPI transaction notes section. say for example, enter “travel” when you are paying for auto and “groceries” when you are buying some vegetables on the street.
But the overhead of typing is pretty irritating.
So I thought I will make a react native app in which you can scan a QR code, put the amount, click on a category icon and click next. Meanwhile this app will generate a brand new UPI link with all this context and open it in your preferred UPI app of choice, with the description and amount pre-filled.
I’ve added one extra click to your user experience, but you are permanently putting context about transactions into your bank statements. That context is golden for budgeting.
I’ve actually built this out in about 2-3 hours, I’ll make it open source very soon. Generated an APK, checked it on a friends phone. It works! But it does not work all the time. Only merchant payments seem to work..
So basically, I scanned a QR code through the app. It made a new app link (something along the lines of paytm://pay...) and this opens paytm. The description & amount is as expected and right, but then after entering the PIN, the transaction fails stating “Risk reasons”.
On further research, I found out there is a parameter called signature. And it needs to be generated via authorized sources. normal links will not work.
Apparently, this is a known issue. And as of now without integrating with phonepe, cashfree or the likes, there is no way to go about opening UPI apps from my app. 🙁
I cannot even type the context manually since these QRs sometimes come with default transaction note, which cannot be over-ridden.
My current setup
Every weekend I block 15 minutes to
- download the “delimited” bank statement from HDFC website, rename it to csv
- use my script to extract payees where-ever possible, and generate a clean version of CSV with columns of my choice
- send it through a data importer so that they get populated in my budgeting app of choice
With that, every month end I know how much I spent where. Also every once in a while I look at how much I’ve allocated for myself this month and see if I’m exceeding that. If yes, I cut down.
So the setup works, but it sucks. There is no seamlessness at all. It’s riddled with manual operations. I don’t like it, but we don’t have Plaid or something similar in India.
My ideal setup is a platform that allows me to do all this in an app, right from setting budget per category per month. Allowing UPI transactions with tagging, rules to classify transactions & reporting.
Until then, this is our best bet.
Conclusion
Thanks for coming along the journey, if you liked this post feel free to share it with your friends too. If you feel like there is a better way to go about it, message on telegram!
That’s about it for this time, hopefully there will be volume two which is not so long and much more cleaner 😉