Quicken to Excel

tb6612

New Member
Joined
Dec 12, 2005
Messages
3
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Dec 12, 2005
Messages
3
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

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Dec 12, 2005
Messages
3
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

Legend
Joined
May 15, 2003
Messages
29,536
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,523
Messages
5,572,639
Members
412,478
Latest member
MakeItWorkVBA
Top