Expense tracker - treating the database

[carics]

Hello,


I have been trying the 7 Personal Expense Trackers from last week's post and I realized that I like some of them a lot but I know I will not use them.

In the past I have designed my own Tracker for this purpose and it was great (for me). Nevertheless the updates only lasted 4 months, and then it started dying.


The reason is simple: I do not have the time to check my everyday's account movements and check them into a spreadsheet. The result is that I only visually check my balance and the latest ins/outs of money to see if I'm +/- good.


But lately my bank online's site started including a downloadable spreadsheet for the movements in a certain period and I started wondering if I could work with that to automatically update an expense tracker.


The problem here is that the spreadsheet condensates most info in just one column:

A = date operation

B = date value

C = concept -> purchase/withdrawal/tax/salary, etc + card number/invoice number/authorization number, etc + company name/period of payment/description, etc

D = value

E = balance


As you may see the column C concentrates most info in text. If only I could separate this info into two different columns it would be easier to automate the update of the tracker. Example:


F = movement type (withdrawal/service payment/purchase/transfer, etc.)

G = all the rest


My first thought was to go for a LEFT function but the movement type has a lot of different descriptions, all with different character lengths.


Here are some examples:

. CASH WITHDRAWAL 2010-07-06, CARD NUMBER: 5555555555555555, CHARGE TAX: 0,00

. PURCHASE MADE 2010-07-05, CARD NUMBER: 5555555555555555 IN "SHOP NAME"

. PAY CHECK FROM "EMPLOYER NAME"

. IRS PAY: "TAX DESCRIPTION"

. TRANSFER FROM: "CODE AND SENDER DESCRIPTION" 1 INVOICE:5555555555

. RECEIPT "SERVICE PROVIDER NAME" "AUTHORIZATION NUMBER" "DESCRIPTION"


So to sum up I would just need to get the movement type into a separate cell.


My second thought was to list the first 4 or 5 characters of every possible movement type in another sheet in column A and in column B manually enter the character length of the total description (IRS P / 7). Then link a LEFT function with a VLOOKUP to set the length.


Is there a better way to do it?


Thanks

 

[Hui]

You have a few options here:


What you have proposed is ok, it is easily expanded as you come across more codes.


Alternatives would be to write a User Defined Function(UDF) which can take the code and breakout 2 or 3 pieces of information

eg: if you had an extry in A10: ". CASH WITHDRAWAL 2010-07-06, CARD NUMBER: 5555555555555555, CHARGE TAX: 0,00"

you could write a UDF to return the Debit/Credit Type, Transaction Description and Amount to 3 seperate cells


Have you looked at options for exporting the data in a diferent format, Although you say the Bank has a Spreadsheet Format, does it have a CSV, Text file or other format where it mainrtains the data fields better?

 

[carics]

I have asked for another format but the bank only has spreadsheet or pfd.


I have to study UDFs; do you know a good site for beginners?


Anyways, I am starting to work on the solution I explained before and it seems to work propperly.


Thanks a lot for your help.