Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Bank Transactions and transposing (97)

    HI Everyone!

    I would like to know how I can accomplish the following task in the simplest manner. I have been using Quicken for several years now but I would like to try using Excel for my bookkeepping. I can download my transactions in a number of formats, but I'd like to know how to best import them into Excel and transpose the information into the appropriate columns on another worksheet. Attached is a sample file. One worksheet contains the data just as it is imported. The other worksheet contains the formatting that I'd like to get the data into.

    Each row in the raw data file is one piece of information. The item is preceded by a character which defines what the piece of information is. For example: T12.95 (T=transaction amount), PSOME STORE (P=Payee), MDIRECT DEPOSIT (M=memo) and so on. Each complete transaction is made up of either 3 or 4 rows, all in column "A". Each block of data (one transactoin) is separated by a row containing a ^ symbol. See attached file.

    Any help would be most appreciated.

    Drew
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Bank Transactions and transposing (97)

    The VBA code below should do what you want. It assumes that you want the data added to the end of what is on the "What I Want" sheet, and that the only transaction codes are D,T,P,N, and M, and that P and N are handled the same. It also assumes that the first row in the "bank" sheet will contain "^".

    <pre>Sub transpose_finances()
    Dim I As Long, lNextRow As Long
    Dim strWk As String
    Dim oSource As Range, oDest As Range
    Set oSource = Worksheets("bank").Range("A1")
    Set oDest = Worksheets("What I Want").Range("A1")
    I = 0
    lNextRow = Worksheets("What I Want").UsedRange.Row + Worksheets("What I Want").UsedRange.Rows.Count - 2
    Do While oSource.Offset(I, 0).Value <> ""
    strWk = oSource.Offset(I, 0).Value
    If strWk = "^" Then
    lNextRow = lNextRow + 1
    Else
    Select Case Left(strWk, 1)
    Case "D"
    oDest.Offset(lNextRow, 0).Value = Right(strWk, Len(strWk) - 1)
    Case "T"
    oDest.Offset(lNextRow, 1).Value = Right(strWk, Len(strWk) - 1)
    Case "P", "N"
    oDest.Offset(lNextRow, 2).Value = Right(strWk, Len(strWk) - 1)
    Case "M"
    oDest.Offset(lNextRow, 3).Value = Right(strWk, Len(strWk) - 1)
    Case Else
    MsgBox "Unrecognized transaction in row: " & I + 1
    End Select
    End If
    I = I + 1
    Loop
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Bank Transactions and transposing (97)

    Legare!!

    That was awesome!! I so appreciate your prompt response. I immediately took your code, pasted it into a module and tested it with a full-years transactions. It worked great. The only thing I subsequently changed is the worksheet names since the default name in the imported worksheet is different. At least I see how it was done and now I can make some tweaks as I need them.

    THANKS AGAIN!!

    Drew

Posting Permissions

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