Monthly Income/Expense Reports

[Martin Cunningham]

I've discovered that by first exporting a GnuCash transaction report to HTML
and then opening the HTML file in Excel, you have a ready-made data source
from which you can create a pivot table with the transaction data grouped
both by account and by period, with very few steps involved. Two
enhancements i made to the data are (1) grouping the dates by month; and (2)
parsing multilevel account names into columns labeled "Account1",
"Account2", etc using the ":" delimiter. This gives you a report that is
very close to the monthly/quarterly/yearly income/expense report that MS
Money and Quicken provide.

Ideally, i would link the pivot table directly to the data source, rather
than having to do the export/import operation described above. I saw an
article here
<http://blog.edseek.com/archives/2005/08/18/gnucash-export-to-gnumeric-and-csv/>that
describes a process for transforming the GnuCash XML file into XLS but I
only understand about half of the steps so I'm going to pass. I'm fairly
handy with databases, though. Is the schema for the relational database
back-end of GnuCash 2.3 available?

If a database connection to Excel proved to be feasible, then I would ask
how to deal with those multi-level account names - I have up to six levels
of accounts, ranging from "Assets:Cash" to "Income:Investment
Income:Dividends:[Broker Name]:[Account Number]:[Security]". How are these
parent-child relationships implemented in the database, and how would one go
about expressing them (in SQL) in a report?

Martin Cunningham


On Fri, Feb 19, 2010 at 5:54 PM, Yawar Amin <yawar.amin at gmail.com> wrote:
> On 2/19/10 6:01 PM, RaPquest said:
>> Thank you 'trythis'  for the testing ... the options do allow for the
dates
>> to be set but the result is always one total value (a total for the dates
>> chosen) instead of individual values - I'm looking for columns of monthly
>> values.  One way would be to create 12 reports - one per month (setting
the
>> options appropriately)
>> but I'd really like one report. I have no problem using a spreadsheet (eg
>> OO) - but how to input?
>>
>
> See below to get a report that shows a table of monthly values. Once you
> have that, you can export it from GnuCash (File > Export > Export
> Report, and save as whatever.html) and then copy and paste the HTML
> table into OO.o Calc as per
>
http://www.oooforum.org/forum/viewtopic.phtml?p=106709&sid=624bafaf0195171b35098ec6d8fa0ccb#106709
>
>> Thank you also Derek ... unfortunately, none of the graphical reports
>> display individual values.
>>
>
> In the Income & Expense Barchart, you can check `Show Table' in the
> Display tab of the Options dialog box. This will show monthly (or
> whatever period you select) income and expense values, along with the
> net profit for that period.
>
> HTH,
>
> Yawar
>
>
>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
>