Bank Statement Analysis - Seek Guidance

[mgforce]

Hello Fellow Users,

Every year, at end of the Financial Year, I download bank statement across bank accounts and I analyse the big flows (outflow & inflow) and classify them in separate column.

Example something like below from 2021-22 stmt wherein I added 2 column "Remarks" and "Toggle"



Today, in manual way of working, Remarks are entered by reading Column Particulars and finding words. Like if there is individual name (ABC) and bank name (XYZ) and it is a withdrawal, then Remark becomes "Funds Out - ABC XYZ Bank".

Toggle is simple if Withdrawal or Deposit. I select one of them and start doing the Remarks entry.

Toggle also helps in making a pivot and seeing only Withdrawals / Deposits

Now as a user trying to maximize benefits, I have bank accounts at many banks. So I end up with analysis of atleast 4-5 bank statements with 100-150 transactions each. I do for a few family members.

I was thinking to use PowerBI feature called "Column from Examples" thinking that it has some AI/ML so that once trained, can do like 80%... but that is not the case - tried and it doesn't work.

Do you guys, have any guidance of some smart way of doing this? Not sure if some feature say in Google Sheets with simple ML plugin available?

Tagging hks789 ChurningSidd vigneshn92 vigneshelan kej vishnum26 mastfun in case they have some tips - they seems good with coding/tech tools/scripts

​

Thanks in advance to anyone who has useful guidance here on analysing it faster and with less manual review

 

[firesnake.nik]

I have been using small app for windows app https://moneymanagerex.org/. Portable tool so can be stored on usb and used anywhere. During lockdown got enough spare time to download all old statements for 20yrs of citibank and hdfc to import them. for icici and boi and corpbank used ocr option in camera and scanned image to convert them in csv to import.

This app is very simple creates report for monthly expenses and yearly analysis. android app is not updated to latest version of database so it would not support opening the file. If you only use windows system give it a try.

App is much smaller in size other than apps like gnucash (https://www.gnucash.org/) and HomeBank ( http://homebank.free.fr/ )

 

[mgforce]

I have been using small app for windows app https://moneymanagerex.org/. Portable tool so can be stored on usb and used anywhere. During lockdown got enough spare time to download all old statements for 20yrs of citibank and hdfc to import them. for icici and boi and corpbank used ocr option in camera and scanned image to convert them in csv to import.

This app is very simple creates report for monthly expenses and yearly analysis. android app is not updated to latest version of database so it would not support opening the file. If you only use windows system give it a try.

App is much smaller in size other than apps like gnucash (https://www.gnucash.org/) and HomeBank ( http://homebank.free.fr/ )

Thanks @firesnake.nik , I will explore this app.

By the way, ICICI Bank does give statement in csv/excel format

 

[hks789]

There is a commercial statement parsing tool called Perfios.
Unfortunately they are stopping their b2c support from next month.
Maybe you can use it for these last weeks to generate all the reports you need.

 

[kishorvr]

There is a commercial statement parsing tool called Perfios.
Unfortunately they are stopping their b2c support from next month.
Maybe you can use it for these last weeks to generate all the reports you need.

I loved Perfios. I had a platinum account. I anyway created an excel sheet many years ago. I have bank and credit card transactions on it since 2008 till today.

 

[mgforce]

There is a commercial statement parsing tool called Perfios.
Unfortunately they are stopping their b2c support from next month.
Maybe you can use it for these last weeks to generate all the reports you need.

Yes, had used them long back, as free user.

I loved Perfios. I had a platinum account. I anyway created an excel sheet many years ago. I have bank and credit card transactions on it since 2008 till today.

Wow, quite organized I must say.

How did you parse all card transaction details?

 

[kishorvr]

Yes, had used them long back, as free user.

Wow, quite organized I must say.

How did you parse all card transaction details?

I entered everything manually. I was shocked by the amount that went for eating out since 2008. I could have been richer by investing it or just parking it in fd.

 

[simplewarrior]

Hello Fellow Users,

Every year, at end of the Financial Year, I download bank statement across bank accounts and I analyse the big flows (outflow & inflow) and classify them in separate column.

Example something like below from 2021-22 stmt wherein I added 2 column "Remarks" and "Toggle"

View attachment 21209

Today, in manual way of working, Remarks are entered by reading Column Particulars and finding words. Like if there is individual name (ABC) and bank name (XYZ) and it is a withdrawal, then Remark becomes "Funds Out - ABC XYZ Bank".

Toggle is simple if Withdrawal or Deposit. I select one of them and start doing the Remarks entry.

Toggle also helps in making a pivot and seeing only Withdrawals / Deposits

Now as a user trying to maximize benefits, I have bank accounts at many banks. So I end up with analysis of atleast 4-5 bank statements with 100-150 transactions each. I do for a few family members.

I was thinking to use PowerBI feature called "Column from Examples" thinking that it has some AI/ML so that once trained, can do like 80%... but that is not the case - tried and it doesn't work.

Do you guys, have any guidance of some smart way of doing this? Not sure if some feature say in Google Sheets with simple ML plugin available?

Tagging hks789 ChurningSidd vigneshn92 vigneshelan kej vishnum26 mastfun in case they have some tips - they seems good with coding/tech tools/scripts

​

Thanks in advance to anyone who has useful guidance here on analysing it faster and with less manual review

Try setting criteria in power query. Create a conditional column>then specify conditions like if this text then this will output in the column. You can have multiple criteria.

 

[mgforce]

I entered everything manually. I was shocked by the amount that went for eating out since 2008. I could have been richer by investing it or just parking it in fd.

Wow. Kudos for this much discipline.

won't comment on the savings & investing bit 🙂

 

[mgforce]

Try setting criteria in power query. Create a conditional column>then specify conditions like if this text then this will output in the column. You can have multiple criteria.

Hmm, let me check, not aware of this.

But then manually creating conditions will be too much of task. I was thinking something which picks from earlier manual mappings.

 

[Pankhuri]

Any way you can share the template?

I loved Perfios. I had a platinum account. I anyway created an excel sheet many years ago. I have bank and credit card transactions on it since 2008 till today.