I've loaded a BankCategories.xltx template to add the accounts field to csv bank statements. If the statement description contains a string in the lookup table it adds the account. The Output sheet probably needs to go through csv2qif.

Click the Down-Arrow to Download, don't save from Google Docs.

Any comments welcome.

That link just gives me a spinning circle, and your description is pretty cryptic, so I really don't understand what you are trying to do. If you would provide a more descriptive and detailed explanation, you might have a better chance of getting useful comments.

Following specific advice of the issues from another user I updated the original post to explain how to download the document.

I downloaded it, and I'm still not clear on what you are trying to do. First question is whether you really mean accounts, or do you mean categories? Also, where is the list of transactions coming from in the first place? Is it just that you are trying to add a column/field to the input transaction to specify a category? Separately, KMM can import CSV files as well as QIF (or OFX) so that conversion shouldn't be necessary unless you are going to use some feature of QIF you can't control with a csv import.

TLDR - Type a few categories into AccountKeywords column B and the words as (continuous characters including any spaces) you use to find them in your bank statement into column A. Paste your bank statement into Bank Statement sheet and drag the formula down for columns F and G. As you update AccountKeyword the Bank statement is updated. Save the csv file when you are finished and import it to the cashbook.

Thanks @ostroffjh that's good feedback. The terminology can be a bit confusing because different financial packages use different terms and accounts is used to refer to different things. In this context accounts = categories.

The good things about bank data are the transaction dates, numbers (if used) and amounts. A lot more data is required in a cashbook which normally requires decoding the description to determine the payee and account, and the class and memo if they are used. This decoding can be slow and error-prone so a preprocessor can make the process more accurate and efficient.

This generalised spreadsheet template is an empty preprocessor used to enrich the bank statement before it is imported.

The sheets:
1. Readme
2. BankStatement - bank transactions to add data to
3. AccountKeywords - master list of keywords to search for and the account (category) they represent
4. Accounts - a sheet of categories purely for reference at this stage but could be a lookup.

This is a template so it has two dummy records in the AccountKeywords sheet to mark the start and end of the range because the lookup only uses records in the range. You pick out enough sequential characters from the bank statement description which identifies the record to add data to. eg Description "458264 Pay to acc 451825 on 251220" you might use Pay and assign it to Salary. A list of 20 will likely auto-assign half your statement.

Bank statement has your bank records.
* Columns B, F and G can only be renamed but others can be inserted or deleted.
* Paste bank records in starting at row three and drag the formula down in columns F and G.
* Delete Row 2
* Export data as csv an import to cashbook

With a bit of modification it could add multiple attributes to each record (eg payee).

BankStatement:

Code: Select all

Date   Number   Description   Amount   Balance
05/03/06   101   GG25j1546 Groceries    wtf 15:57 061124   -45.21   413.05
06/03/06      Transfer to J&J Doe Savings Acc 5765-8397 589654259587   100.00   513.05
14/03/06      Direct Credit Salary from Employers R Us   670.00   1,183.05
28/03/06      Mmvoin515b  Internet Company bg??   -20.00   1,163.05
28/03/06   102   Light Company Big City Branch   9g8k863   -78.00   1,085.05
28/03/06   103   Phone Company Name    Autodebit 595642583   -45.00   1,040.05
28/03/06   104   Internet Transfer         April Rent 5 Short Road   -350.00   690.05


AccountKeywords:

Code: Select all

Keyword             Account
Groceries           Groceries
Savings Acc         [Savings]
Salary              Pay
Internet Company    Broadband
Light Company       Electricity
Phone Company       Phone
Rent                Rent


Note: Internet transfer for Rent is not Broadband.