Quicken to Excel

[tb6612]

I have built my own money management tool using Excel. I would like to take Quicken download data and output this data in four columns (type of transaction, date, description, and amount). My credit card company allows download in csv format, but my bank only alllows Quicken and Money downloads. I have no need for header info that comes with the download.

I have managed to extract transaction data to get it into the 4-col format. However, I have to dump onto one worksheet, manually add rows where transaction take up less than 7 rows. Then, I copy this data onto another sheet that extracts, format, and dump data into the four columns. Whew, it make me tired trying to explain it. Is anyone aware of VBA code that can simplify this process.

Terry

 

[Smitty]

Welcome to the Board!

Can you post an example of the download data and the result?

Take a look to the bottom of the page for the link to Colo's HTML Maker, which will let you do that.

Smitty

 

[tb6612]

Below is typical transaction from Quicken download:

(TRNTYPE)CHECK
(DTPOSTED)20051109120000
(TRANAMT)-12.00
(FITID)7451474040200511090000002234
(CHECKNUM)000050002
(NAME)CHECK
(/STMTTRN)
(STMTTRN)

IMPORTANT NOTE:
In actual download "<" are replaced with "(" and ">" replaced with ")"
When "<" and ">" are used without quotes they and text enclosed does not appear.

 

[Smitty]

See how this is for a start:

<font face=tahoma><SPAN style="color:#00007F">Sub</SPAN> Quicken()
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    
        <SPAN style="color:#007F00">'   Data-->Text to Columns on Data column</SPAN>
        Sheets("Data").Range([A1], [A65536].End(xlUp)).TextToColumns _
            Destination:=Range("A1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
            :="=", FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1))
        Columns("A:A").EntireColumn.Delete
        Columns("A:B").EntireColumn.AutoFit
        
        <SPAN style="color:#007F00">'   Convert Data</SPAN>
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Sheets("Data").Range([A1], [A65536].End(xlUp))
            <SPAN style="color:#00007F">Set</SPAN> LastRow = Sheets("Register").Range("A65536").End(xlUp)
            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> c.Text
                <SPAN style="color:#00007F">Case</SPAN> "TRNTYPE"
                    LastRow.Offset(1) = "VISA"
                <SPAN style="color:#00007F">Case</SPAN> "DTPOSTED"
                    LastRow.Offset(, 1) = _
                        Mid(c.Offset(, 1), 5, 2) & "/" & Mid(c.Offset(, 1), 7, 2) & "/" & Left(c.Offset(, 1), 4)
                <SPAN style="color:#00007F">Case</SPAN> "TRNAMT"
                    LastRow.Offset(, 2) = c.Offset(, 1) * -1
                <SPAN style="color:#00007F">Case</SPAN> "NAME"
                    LastRow.Offset(, 3) = c.Offset(, 1)
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> c
                    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

It assumes that your download is on a sheet called "Data" and your are transferring the data to a cheet called "Register". (Try the code in a new workbook as macros are difficult to undo).

Smitty

EDIT: of course this does kind of beg the question why not just spring for Quicken?

 

[tb6612]

Smitty,
I ran the macro as you said, but no data was copied to Register worksheet.

Does the macro take in consideration that "=" are "<" and ">", and "*" are "/" in actual data? Thanks.

Terry

 

[Smitty]

Does the macro take in consideration that "=" are "<" and ">", and "*" are "/" in actual data?

Yes. The Data-->Text to Columns strips the "="'s.

It did work for me based on your data sample. PM me your e-mail address send I'll send you the wb I tested.

Smitty