# Importing Quicken Tab Delimited .*TXT File and plotting

I am using Mathematica 7 to try and plot my quicken data file. I have exported my quicken file as a .*TXT and have made zero changes to it. I want to do a DateListPlot on the file. The date is in column 1 and the amount is in column 9. So I want ALL data in those columns lined up by date. Now my paycheck I keep track of the taxes I pay so an entry in the file will have those amounts listed but no date in the associate column, so I'm guessing I may need to do some clean up there (many thousands of rows...)

I have the following code:

quickentest =
DateListPlot[
Take[Import[
"C:\\location of file\\quickentest.txt", {"Data",
All, {2, 10}}], "DateList" -> "Month", "", "Day", "", "Year"]]


And I get the following output error:

Take::seqs: Sequence specification (+n, -n, {+n}, {-n}, {m, n}, or {m, n, s}) expected at position 2 in Take[{{Date,Amount},{,},{BALANCE 2/18/2006,0.},{2/19/2006,168.77},{2/20/2006,-32.54},{2/21/2006,-20.},{2/23/2006,-103.03},{2/23/2006,-10.},{2/24/2006,-21.75},{2/24/2006,1044.},<<4248>>},DateList->Month,,Day,,Year]. >>
DateListPlot::ntdt: The first argument to DateListPlot should be a list of pairs of dates and real values, a list of real values, or a list of several such lists. >>


I also get some output which for the most part looks pretty good except for the header row which looks like this:

DateListPlot[
Take[{{"Date", "Amount"}, {"", ""}, {"BALANCE 2/18/2006",
0.}, {"2/19/2006",
168.77}, {"2/20/2006", -32.54}, {"2/21/2006", -20.}, \
{"2/23/2006", -103.03}, {"2/23/2006", -10.}, {"2/24/2006", -21.75}, \
{"2/24/2006",
1044.}, {"", -127.18}, {"", -15.13}, {"", -64.73}, {"", -37.87}, \
{"", -1.2}, {"2/24/2006", 68.28}, {"2/25/2006", -32.51}, {"2/25/2006",
0.}, {"2/27/2006", -40.}, <<4220>>, {"12/10/2012", -29.43}, \
{"12/10/2012", -35.55}, {"12/10/2012", -30.}, {"12/12/2012", -18.4}, \
{"12/13/2012", -50.}, {"12/14/2012",
381.27}, {"12/14/2012", -6.29}, {"12/14/2012", -120.}, \
{"12/15/2012", -7.73}, {"12/15/2012", -18.85}, {"12/17/2012", \
-35.02}, {"12/17/2012", -58.73}, {"12/17/2012", -21.72}, \
{"12/17/2012", -15.}, {"12/18/2012", -152.06}, {"12/18/2012", -37.}, \
{"12/28/2012", -959.}, {"1/1/2013", -355.}, {"2/19/2006 - 1/1/2013", \
-1007.55}}, "DateList" -> "" \[Ellipsis] "h", <<2>>, "", "Year"]


I tried to exclude the header by using the suggested code below 2;; to span out what I want but get errors I listed in the comment section. When exporting from Quicken 2013 Deluxe my only option is a .*TXT file from inside the checkbook registry.

Thank you

-
Best to export from Quicken as XML. –  Mike Honeychurch Dec 17 '12 at 23:25
Also "quicken_test" is not an appropriate variable name. Underscores in Mathematica are reserved for pattern matching –  Mike Honeychurch Dec 17 '12 at 23:27
thanks guys, I changed it to just quickentest.xml and will fool around with it like that –  MCP_infiltrator Dec 19 '12 at 23:17

Problem #1

The point I was making above is that you need to exclude items that are not {date, value}. You hadn't provided data -- now you have provided data. In your example the first 3 rows are not something that DateListPlot can interpret. So instead of excluding the first row it follows that you need to exclude the first 3 rows.

So therefore use 4;; rather than 2;;.

Problem #2

Incorrect Mathematica syntax:

DateListPlot[Take[Import[...], "DateList" -> "Month", "", "Day", "", "Year"]]


which means you have

Take[importedData, "DateList" -> "Month", "", "Day", "", "Year"]


This is wrong on several counts. The first is that it is not the proper syntax for Take. "DateList" is not an option for Take. Please read the documentation. The second is that functions that would accept "DateList" as an option would require the argument to be a list.

"DateList" -> {"Month", "", "Day", "", "Year"}


But if you use Span then Take is redundant.

So the way to make life easier is first try this:

Import["C:\\Location_of_said_file\\quicken.txt", {"Data", All, {1, 9}}]


If that returns two columns of data with {date, value} starting at row then then try this

Import["C:\\Location_of_said_file\\quicken.txt", {"Data", All, {1, 9}}][[4;;]]


If that removes your first 3 rows then plot the data in DateListPlot.

-
When using the above I get the following errors: Import::elemstx: 2;;All is not a valid format or element specification. Expecting a string, list, or integer. >> Take::normal: Nonatomic expression expected at position 1 in Take[\$Failed,DateList->Month,,Day,,Year]. >> DateListPlot::ntdt: The first argument to DateListPlot should be a list of pairs of dates and real values, a list of real values, or a list of several such lists. >> –  MCP_infiltrator Dec 19 '12 at 23:34
Thank you for the correction in syntax, I understand that the 2;; was trying to get rid of the headers I did not want, so I got what you were trying to say above. Now that I get no errors I will read the documentation and try to figure out how to plot the import. I tried using `quicken["Elements"] to see what kind of elements I had inside the variable but was just given the list back out, so it looks like I'll need to read to figure out how to format it as well in order to get it to plot out. –  MCP_infiltrator Dec 21 '12 at 1:01