How to pull out cards with balances?

E

Emanuel Levy

New member
Joined
Jan 14, 2013
Messages
19
Reaction score
0
Points
0
Location
Toms River, New Jersey, United States
I figured out how to count how many accounts I have open with balances. What I want to do next is start working on a snowball calculator. Before I can do that though I need to pull out to a separate tab the following

Issuing Bank, Card Name. Balance Limit Utilization, Minimum Payment and Amount to goal.

All this information is on the Credit Cards Data tab

My ultimate goal right now is to have the following snowball methods

1) Snowball by Utilization Goal
2) Snowball by balance smallest to largest
3) Snowball by balance largest to smallest
4) Snowball by interest rate lowest to highest
5) Snowball by interest rate highest to lowest

I figured I can break that out by a sort possibly by a radio button to choose a pre-defined sort on the Payment calculation sheet then put the results back to the credit card data sheet. I would use the values on the bank balances tab minus the Fixed Monthly payment tab and what is left will go to the credit card payments

I hope this makes sense as I am tired and my brain is a bit foggy right now.
 

Attachments

  • My_debt_plan_v05d.xlsm
    32.9 KB · Views: 24
Sorry Emanuel, I'm not quite following this. My brain is also a bit foggy at the moment too though.

I see the Snowball column, but the Payment Calculation Sheet is blank.

Can you be a bit more detailed with the logic you're trying to impart here?
 
Ok I had sleep and can type coherently now.

I *think* the best way to do what I want is to have a vba macro or script "pull"from the credit cards data page the following

Card Name, Util %, % to pay to goal, and minimum payment only for cards with balances

Then after sorting the accounts on this tab based on the sort method I choose have it subtract the total of minimum payments from the total liquid value value.

Then taking the amount left after the minimum payment apply that to the card with balances based on the sort order until each goal is reached and money is left or the payment money for the month runs out.
 
Hi Emanuel,

I just cobbled this together to start the extraction of the info. Tell me if this is along the right direction...

After that, I'd need a bit of clarification on the next step... what sort method are you after?

Code: 
Sub Extract()
    Dim ary(3) As Variant
    Dim cl As Range
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    
    Set wsSource = Worksheets("Credit Cards Data")
    Set wsTarget = Worksheets("Payment Calculation Sheet")
    
    With wsSource
        For Each cl In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
            'Check if card balance is > 0
            With cl
                If .Offset(0, 4).Value > 0 Then
                    ary(0) = .Offset(0, 2)
                    ary(1) = .Offset(0, 6)
                    ary(2) = .Offset(0, 7)
                    ary(3) = .Offset(0, 9)
                    With wsTarget
                        .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 4) = ary()
                    End With
                End If
            End With
        Next cl
    End With
End Sub
 
Ken Puls said:
Hi Emanuel,

I just cobbled this together to start the extraction of the info. Tell me if this is along the right direction...

After that, I'd need a bit of clarification on the next step... what sort method are you after?

Code: 
Sub Extract()
    Dim ary(3) As Variant
    Dim cl As Range
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    
    Set wsSource = Worksheets("Credit Cards Data")
    Set wsTarget = Worksheets("Payment Calculation Sheet")
    
    With wsSource
        For Each cl In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
            'Check if card balance is > 0
            With cl
                If .Offset(0, 4).Value > 0 Then
                    ary(0) = .Offset(0, 2)
                    ary(1) = .Offset(0, 6)
                    ary(2) = .Offset(0, 7)
                    ary(3) = .Offset(0, 9)
                    With wsTarget
                        .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 4) = ary()
                    End With
                End If
            End With
        Next cl
    End With
End Sub
Click to expand...

Ken I will try this tonight and i will answer your question tonight
 
Ok when I run it it pulls data but now what I'm looking for entirely just some minor issues.

1) I need the issuing bank along with the card name for it to make sense to me not just the card name
2) The values are not showing in the right format. They are just numeric and not percentage or currency

After manually putting in a header and formatting the cells so they look like

CARD Percent usedGoal to pay toMinimum Payment
Platinum31.60%29.00%$28
Costco1.26%5.00%$35.00
Zync0.00%0.00%$33.08
Cash Back0.15%0.00%$8.00
Freedom34.15%29.00%<blank because no data>
Slate31.47%28.00%<blank because no data>
Discover IT3.61%0.00%<blank because no data>
Gas Card1.44%0.00%$9.39
CashRewards92.95%90.00%$298.00
Visa28.50%28.00%$61.00

I want to be able to sort the card by any of the following

Balances both High to Low and Low to High
Interest rate both lowest to highest and highest to lowest.
Utilization percentage both lowest to highest and highest to lowest.

I then want to make a money waterfall and take the money I have left after the minimum payment and apply it to each card until it hits the goal then take anything left and apply it to the next card and so one until either all the cards are at goal or the money is at zero
 
Hi Emanuel,

I've modified the macro to add the formatting, and sort by the Minimum payment first:

Code: 
Sub Extract()
    Dim ary(4) As Variant
    Dim cl As Range
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lCol As Long
    
    Set wsSource = Worksheets("Credit Cards Data")
    Set wsTarget = Worksheets("Payment Calculation Sheet")
    
    With wsTarget
        'Clear out the old data
        .Cells.ClearContents
        
        'Create headers
        .Range("A1:E1") = Array("Issuing Bank", "Card", "Percent Used", "Goal to pay to", "Minimum Payment")
        
        'Format columns
        .Columns("C:D").NumberFormat = "0.00%"
        .Columns("E:E").Style = "Currency"
        .Columns("A:E").EntireColumn.AutoFit
    End With
    
    With wsSource
        'Fill table
        For Each cl In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
            'Check if card balance is > 0
            With cl
                If .Offset(0, 4).Value > 0 Then
                    ary(0) = .Offset(0, 0)
                    ary(1) = .Offset(0, 2)
                    ary(2) = .Offset(0, 6)
                    ary(3) = .Offset(0, 7)
                    ary(4) = .Offset(0, 9)
                    With wsTarget
                        .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Resize(1, 5) = ary()
                    End With
                End If
            End With
        Next cl
    End With
    
    With wsTarget
        .Sort.SortFields.Clear
        .Sort.SortFields.Add _
            Key:=Range("E2:E" & .Range("E" & .Rows.Count).End(xlUp).Row), _
            SortOn:=xlSortOnValues, _
            Order:=xlDescending, _
            DataOption:=xlSortNormal
        With .Sort
            .SetRange Range("A1:E" & wsTarget.Range("E" & wsTarget.Rows.Count).End(xlUp).Row)
            .Header = xlYes
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
End Sub

From here, I'm not quite sure on how to proceed. Since we can only sort by one thing to start with, it would seem to make sense to me that any additional sorts would be manually triggered. As far as the money to apply, where is that, and how do you see it being applied to the schedule, exactly? Are you thinking of a formula that shows ever decreasing amounts available or... Will that money be provided at runtime, is it alreayd somewhere in your file?
 
That code works perfectly.

The money to apply is the total liquid value on the Bank Balances tab in cell F1

I was thinking of having a drop down box with the different sort options and whatever one is chosen is the sort that's applied.


After giving this some thought I think the amount that can be applied to the bills should be the liquid amount after subtracting the fixed monthly bills which is already it's own tab.

The total left in fixed bills is found in cell M4 on the Fixed Monthly Payments Tab the only problems with that tab are

1) It's adding future payments example the car and insurance payments are not due this month but it's adding them in
2) I have no idea how to make it reset that the payment is due when the month changes again without manually removing the value in Amt Paid This Month

See the Red Text on Payment Calculation sheet for my thoughts about the "waterfall"

I've uploaded a new spreadsheet to this post
 

Attachments

  • My_debt_plan_v05f.xlsm
    37.1 KB · Views: 12
You must log in or register to reply here.
Back
Top