Do I need Visual Basic for this?

  • Thread starter Thread starter unix-freak
  • Start date Start date
U

unix-freak

I want to track my credit card charges that are out of grace period. April
28 was my first charge after a 0.00 balance.
If someone can point me in the right direction to do this, I would
appreciate it.

This is basically what I'm trying to do:

if (today -30) minus (April 28) < 0
then exit
if (today -30) - (April 28) > 0
then get all rows from (April 28) to (today)
filter all code "J" transactions (these are my transactions...my wife
has her own code)
add all payments
filter all code "J" charges from (April 28) to (today -30)
add all charges
subtract all charges from payments
export this value to bar chart on another page
 
No, you don't need visual basic.

I'll assume the date of your transaction is in column A, the
transaction amount is in column B, the transaction code is in column
C, and your list starts at the top of the column (row 1).

Add a column D which has the following formula in cell D1:
=IF(AND(A1>DATE(2004,4,28),A1<(TODAY()-30)),B1,0)
Then copy that function down the row as far as you want.

This will populate column D with the amount of your credit card
transaction, but only if that transaction occured since 4/28 AND more
than 30 days ago. Otherwise, it will populate D1 with a 0.

Now, pick a cell which will be the sum of all YOUR charges (and not
your wife's). For example, use cell E1. Enter the following formula:
=SUMIF(C:C,"J",D:D)

This will generate a sum of all the charges which match your date
filtering logic in column D, and further filter based on whether there
is also a "J" in colunm C on the corresponding row.

It should now be pretty straight-forward to have your cell which
appears in the graph be the sum of all payments minus cell E1.

I hope that helps. If not, send me your spreadsheet and I'll take a
look at it.

Trevor
(e-mail address removed)
 
That works great! Thanks. I had to do the same thing to filter the payments
/ and sum them / and then some minor math and I've got a nice bar chart from
it all.

Thanks Trevor!
 
I'm glad it helped. Thanks for the feedback!
Click to expand...

My spreadsheet has become very powerful. I had some very complex
conditional formulas in it before, and now with your help has opened
up my thinking - so to speak. Last night I setup the capablity to
balance my checkbook, which I believe is nothing more that "starting
balance - ending balance + outstanding deposits - outstanding checks".
That should equal 0.00. I have to put a "c" in cells for transactions
that have cleared.

All of this with allow me to get away from Microsoft Money. It's
worked out fine, it's just that I don't want to have to enter
transactions into both that and excel. Next is to have accounts with
amortization figured into running balances.
 
Back
Top