Results 1 to 8 of 8

Thread: How to pull out cards with balances?

  1. #1
    Seeker Emanuel Levy's Avatar
    Join Date
    Jan 2013
    Location
    Toms River, New Jersey, United States
    Posts
    19
    Articles
    0

    How to pull out cards with balances?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.
    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Seeker Emanuel Levy's Avatar
    Join Date
    Jan 2013
    Location
    Toms River, New Jersey, United States
    Posts
    19
    Articles
    0
    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.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    Seeker Emanuel Levy's Avatar
    Join Date
    Jan 2013
    Location
    Toms River, New Jersey, United States
    Posts
    19
    Articles
    0
    Quote Originally Posted by Ken Puls View Post
    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 I will try this tonight and i will answer your question tonight

  6. #6
    Seeker Emanuel Levy's Avatar
    Join Date
    Jan 2013
    Location
    Toms River, New Jersey, United States
    Posts
    19
    Articles
    0
    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 used Goal to pay to Minimum Payment
    Platinum 31.60% 29.00% $28
    Costco 1.26% 5.00% $35.00
    Zync 0.00% 0.00% $33.08
    Cash Back 0.15% 0.00% $8.00
    Freedom 34.15% 29.00% <blank because no data>
    Slate 31.47% 28.00% <blank because no data>
    Discover IT 3.61% 0.00% <blank because no data>
    Gas Card 1.44% 0.00% $9.39
    CashRewards 92.95% 90.00% $298.00
    Visa 28.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

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,284
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Seeker Emanuel Levy's Avatar
    Join Date
    Jan 2013
    Location
    Toms River, New Jersey, United States
    Posts
    19
    Articles
    0
    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •