Update: This is four years old. A new post on same topic may be found here.
I wrote in the previous post that I would write about how to import Indian Banks Transaction files in GnuCash accounting software.
ICICI Bank offers download of Bank Transactions file in MS Money Format (.qif) and GnuCash accepts importing this file format but when you try to import it may fail and if it succeeds the import wizard will ask many questions. Open Office has a macro CALC2QIF which imports .qif file. When you import the file you will see that the Category column has entries like TCI, TBI, TIP etc and while GnuCash can import the transactions into the Bank Account it does not understand where to post the corresponding debit/credit entry into the other account. Let us consider one example.
Suppose you have received payment from your Customer there are two entries to be made. One in the Bank Account and the other in Accounts Receivable but the file downloaded from ICICI Bank shows TBI on the Category. You need to change TBI to Accounts Receivables. Similarly you should write the names of other accounts like Cash etc in the Category column. If you can't decide the account name you can write Imbalance against such entries in the Category and GnuCash will show them as imbalance till you change them.
After you change the entries in Category column you should select the cells and click on Save to QIF, clear the default fields and select them again in the order they are displaced i.e. Date, Amount, Check Number, Payee, Memo, Category and Sub-Category, select the destination for QIF output file and click on Convert.
Now you can import this file easily in GnuCash.
I wrote in the previous post that I would write about how to import Indian Banks Transaction files in GnuCash accounting software.
ICICI Bank offers download of Bank Transactions file in MS Money Format (.qif) and GnuCash accepts importing this file format but when you try to import it may fail and if it succeeds the import wizard will ask many questions. Open Office has a macro CALC2QIF which imports .qif file. When you import the file you will see that the Category column has entries like TCI, TBI, TIP etc and while GnuCash can import the transactions into the Bank Account it does not understand where to post the corresponding debit/credit entry into the other account. Let us consider one example.
Suppose you have received payment from your Customer there are two entries to be made. One in the Bank Account and the other in Accounts Receivable but the file downloaded from ICICI Bank shows TBI on the Category. You need to change TBI to Accounts Receivables. Similarly you should write the names of other accounts like Cash etc in the Category column. If you can't decide the account name you can write Imbalance against such entries in the Category and GnuCash will show them as imbalance till you change them.
After you change the entries in Category column you should select the cells and click on Save to QIF, clear the default fields and select them again in the order they are displaced i.e. Date, Amount, Check Number, Payee, Memo, Category and Sub-Category, select the destination for QIF output file and click on Convert.
Now you can import this file easily in GnuCash.
6 comments:
I am having trouble importing the ICICI Bank statement which I extracted in MS-Money format (.qif) file. Even MS-Money software doesn't know how to open it. Did you face that problem.
I am using ICICI Bank Corporate Banking System
@ Suresh Sambandam
This blog post is about how to import the ICICI Bank statement into GnuCash. The file could not be imported into GnuCash also and I imported it into Open Office using CALC2QIF. If you don't use Open Office you can import in MS Office using XL2QIF. After importing in MS Office you can edit it and save it as .qif and try importing in MS-Money. Since I don't use MS-Money I can't troubleshoot your problem.
Thanks. I am just in the process of trying Gnucash and need the qif support. I am going to try your method. Hope it works.
Did everything as told. I get an error "Invalid date format". I have double-cheked the date entries and the format. They are both DD/MM/YY and all entries are fine, yet I get this error. Any tips?
Ok. Sorry for repeated comments here, but thought I must share how I solved it finally. I was bent upon using Gnucash, mostly because I needed a good programme that is free/os. That error I mentioned earlier just would not resolve. Finally, I used the CALC2QIF that you suggested to make a csv file from the ICICI QIF file, edited the category (marked all Imbalance for now), made the date into dd/mm/yyyy format (it was yy format) and then reconverted to QIF using this free online link which was very good. It allows you to map the fields from teh csv to QIF very nicely. Here it is: http://csvconverter.gginternational.net
Anyway, thanks for this tip! If not for this, I would have given up on Gnucash because I had no idea why it would not import QIF from ICICI.
Good to know you Mr Prashanth. After 5:30 PM I don't check my mail and could not respond but I am happy to know that you solved the problem.
Post a Comment