Bank statements in Excel

Dear @lubos,

Many banks don’t support qif, iff etc etc. I used to import my statements through an excel file, with a lot of formulas, and Batch Create. But now that you have separated Receipts and Payments this has become a real mess. How about adding the possibility to import a bank statements doing a copy and paste from an excel with the four columns that are needed inside qif, (date, reference, description and amount). A sort of Batch Create from bank statement.

2 Likes

You do not need separate receipts and payments. You import the bnk statement as you did before. Then click on the bank tab and categorize the categorized receipts and payments, assign receipt or payment rules where appropriate to facilitate next imports.

As far as I know, you cannot Batch Create Receipts and Payments with one single script. They have also different fileds’ headers. And you canno import bank statements in excel. What I am asking to have a more structured procedure to import statements. The actual one is very lacunous. You should be able to open the file, map the columns (like when you import contacts in outlook from excel), select the lines you want to import…

I don’t understand why you want to use Batch Create when you can just import the statement. And if you can convert your statements in Excel to include date, reference, description, and amount, you can already save the result (in Excel) as a .csv file. Why add more steps and complexity?

3 Likes

I assume because bank rules do not provide the granularity @Davide needs so he is doing the equivalent of bank rules in excel then just importing the results.

Correct. Not only… keep in mind that CSV is not a real standard. Even in Excel, based on the localization, 90% of times you will get a useless file.

In my country, electronic bank statements are downloaded in pdf format. Which I convert to excel (.CSV) file and import into Manager. As long as the bank rules ( now separated as receipt & payment rules) are in place, all transactions should be allocated accurately.
However, the only time I would need the bank Statements to be imported in a batch ( or batches) would be when you have to enter several years or months at one time, I guess.
Again there would be no other way to carefully allocate those transactions than by the way of importing month in, month out( as per bank statement opening and closing balances)

That should not be a concern. There is no time or size limit on the importation of bank statements.

I have a similar scenario, being able to batch create receipts and payment from one TSV file where negative amounts are payments and positive amounts are receipts would be of great help!

The question is why you would use batch create rather than just import a bank statement?

There are many scenarios where you don’t receive the bank statement in CSV. One example is PDF bank statements. It is easy enough to copy from PDF to Excel in this scenario. Now that you have the data in Excel, I prefer to use batch create because it is a simple copy and paste, in stead of Save As CSV, then navigate back to manage, select the CSV and import. It reduces the steps in the process. When you have multiple bank accounts, you can create a spreadsheet with separate tabs for each bank account and simply copy and paste into Batch Create and maintain all your banking transactions for one business in a single Excel file in stead of multiple CSV files.

And even if I receive the file in CSV, I often open the CSV in Excel to review before import. Then copy and paste to Batch Create makes sense as well.

I can download a csv file from my bank. I import these transactions in an excelfile which converts the transactions to the qif fformat. Then these data are imported into notepad and this file is written as a qif file. After that I can import this file into Manager. Manager imports the transaction like a standard MT940 file.

What I am suggesting is something similar to what you have in Outlook when you import contacts from an excel file, ie you open the file, you map the corresponding fields, you have a preview of the results and you accept the import.

Exactly.

My bank statement downloads as a csv file which I can edit and it imports perfectly.

Lucky you. I have to interface with at least 10 different banks and only one of the gives me a format compatible with Manager.

I have to re-arrange in Excel so that it meets the import criteria.

The issue is not only to reorganize the fields in Excel, the real issue is that CSV is not a real standard. Based on the localisation the separatar can be a comma, a point, a tab etc etc, the fields can be delimited with " or '.

I am fortunate that all my banks have multiple formats, OFX works best for me.
In my real job I use CSV and TAB delimited files all the time. I have found Notepad++ allows me to reconstruct the CSV to meet the import standard for the software.
It may be of use to you.

1 Like