Import Bank Statement Reference Import Failure

Noted: Import Bank Statement using csv is accepted by manager, I had no problems after learning about comma delimitation which already solved.

After updated For Manager version 18.8.70, the reference which included in reference column in csv did not import/shown in edit transaction after checked the updated with bank rules and edit or view. it really does not import the reference column. weird enough no error thrown to stop the process to be imported. @lubos please test it out.

Since auto-numbering can be enabled on receipts & payments I can no longer let bank statement import to populate Reference field.

Imagine your bank using reference number such as 111111111, then the next receipt you create (if auto-numbering enabled) would produce automatic reference 111111112.

So if your CSV file has reference, it should be within description column now.

Therefore CSV import understands following columns only:

  • Date
  • Payee
  • Description
  • Amount

I was ready not to use auto numbering feature in favour of bank import where I can populate (auto fill sequence) within excel. Now I stumped. where I can’t reverse what I did. I can do faster with by import rather than go through one by one with manager’s form.

There is a reason why I suggested parallel reference, one generated by manager another is by user. Now I’m the one receiving short end :disappointed_relieved:

Try the latest version (18.9.2) where I’ve added batch operations under Receipts & Payments tab.

You can do batch update now to remove all your bank references from receipts & payments (and copy them into another field).

This will leave your reference column clean so you can enable auto-numbering.

Would this work for you?

Will come back to you @lubos.

I’m working on editing the transaction via batch update in payment & receipt tab. While At the same time I peek into batch create. compare both has the same column except ‘key’ column. Lubos For Batch create I have to define the accounts and amount one by one via form? Can Bank import able to add custom field column similar to do batch create to be used with bank rule. or do you plan to unified with cash since payment & receipt merged. something like cash and bank rule? It will speed up initial phase to populate existing data to be keyed in to Manager via spreadsheet program.

I take it that lubos intended to recommend the automatic-numbering reference to be used by default. and have it user own reference to be keyed in custom field instead.

The idea is that once batch create improves to be better than CSV import, CSV import will be removed.

As for custom reference numbers, you can either have custom field for that or make it part of description. I’d just make it part of description. Why would this information need to be separate?

no please dont. in many parts of the world bank statements are given only in excel and pdf formats. it is easy to convert excel to csv.

The idea is that you can copy & paste from Excel to Manager without going through CSV step.

1 Like

oh that should be good

Well I separate it when I used excel’s autofill cell features it will made the last numeric numbers sequential just by pulling it down to other cells. from 1,2,3,4 instead copy cells 1,1,1,1. when I want to batch update.

I rather have parallel reference one for manager since it mixed with cash accounts it became useful as integrity check.

Another defined myself to associate with external document. for reference point by line items. Noted bank transfer (Maybank) does not have any its own reference number by line items shown in bank statement. which made checking between manager and bank statement difficult by auditors. If i used auto numbering than there will be non sequential numbers in reference within bank account tab. @raj had made the point that there is the need to distinguish transaction for cash and bank. my solution would be add own reference to know which is which.

I appreaciate that you have made it easy by allowing the bank account and cash account custom field to be display in transactions respectively, that made the identification easy. except by line items to associate with external documents.

Description may described by my client for their understanding base on actual documents. I would not want mixed it up with mine so I’ve use custom field more often.

For example, the client of mine is purely marketing person, she is the director, certain period she will transfer the money to her own personal designated bank accounts for certain usage is like piggy bank, where you save money to buy phone, or other piggy bank for tuition fee. end of financial period then she will transfer balances (if yet to be used) back to company bank accounts. she called it profit first.

what do we accountant called it? Loan to director.


Lubos the problem is that in our case, the accountant wants the bank transanctions every month as the appeared in the bank. Our bank has 2 options for exporting: a) pdf and b) csv files, without reference.
So what I did it so far, was to add in the csv file a custom alphanumeric code in order to achieve the line sequence adn import the csv file to Manager. Without the reference option and without the automatic reference I have to addby hand reference numbers to the manager which is rather stupid and time consuming proccess. So can you please advise what can I do for my case?

Actually thinking this through again, I think there is no harm in allowing CSV to import Reference column so that is brought back in the latest version (18.9.9). After all, whatever will replace CSV import will support importing Reference column anyway.

@nathan.kor I think your issue can be solved by setting up automatic reference numbers under Form Defaults and then when importing bank transactions, Manager would automatically generate reference numbers for each imported transaction. That way you can avoid setting up reference numbers in your CSV file.

thanks for getting back Reference to csv import!

I have considered the solution that you mention about the automatic generation upon importing the bank file. The only problem is that you have to be very careful because if lets say in a real scenario you want to add 1 week data from the bank to have a real view of the balance and then you have some cash transanctions in the middle and finally at the end of the month import the next 3 weeks of the bank transanctions, then the automatic reference will be mixed up meaning bank and cash transanctions will have sequential numbering…
This is not a very strange case, it can happen, I do this all the time!

Thanks again for the help!

@lubos early on the batch update indicate the update is success. when I checked again some are update as suppose to while there still lots of record didn’t update. When I re-do again, this time doesn’t work, it doesn’t point me to bulk update screen.

Is there any particular condition I should wary of like case sensitive or date format?

Update: using batch delete works fine, then batch create, manager only import the second line after headings column.

Update 2: Now I know Why! I accidently left ‘date’ headings format as date instead ‘general’ just like the rest of headings.

Update 3: Interesting, since the batch create and batch update doesn’t specify which account to be posted to, the account field is empty, weird enough no suspense account indicate in summary. lubos i think you need to add account column field to pe posted or at least design similar to import bank statement where you have uncategorize transaction. The best way to do it with least error is based on account code.

Update 4: When set automatic reference number to True in batch update spreadsheet format and bulk update. the reference field won’t be updated unless manually update one by one in edit screen. time consuming for ‘automatic-numbering’ in case of batch update scenario. The workaround is manually autofill in sequence via spreadsheet program. which is no biggie.