Monthly Income/Expense Reports
David G. Hamblen
dhamblen at roadrunner.com
Mon Apr 4 08:05:44 EDT 2011
Attached are 4 text (SQL) files that generate an accounts tree table and
a SQL view that I find useful in displaying a balance sheet and Income
Statement using OpenOffice Calc. There's a few hard-coded bits that may
be specific to my situation (6 levels in my Chart of Accounts is one).
I'm sure there must also be some mysql'isms in there as well; so sqlite3
or postgresl probably won't work (but the original posting by Mark
Sluser was in Postgresl).
As I look at what I've done, one of the first things was to access the
gnucash SQL database (mysql backend) using OpenOffice calc, where I know
how to select, display, and graph the data. I'm not sure how I got it
registered (and since my computer is now configured to do this, I don't
recall how to reconstruct it), but I think it goes something like this:
In OOCalc, do File/New/Database, and select "Connect to an existing
database". There are some odbc drivers that may be needed.
On 04/01/2011 07:24 PM, Martin Cunningham wrote:
> Please do when you get a chance, David - i would be very interested. I
> found the thread you mentioned here, incidentally:
> https://lists.gnucash.org/pipermail/gnucash-user/2009-July/030590.html
> Thank you. Great to know there are like-minded users out there.
>
> Martin Cunningham
> T: (415) 448-6854
>
>
> On Fri, Apr 1, 2011 at 3:34 PM, David G. Hamblen
> <dhamblen at roadrunner.com <mailto:dhamblen at roadrunner.com>> wrote:
>
> I've ported the SQL code posted by markluser back on Jul 20 2009
> (you should be able to find the thread in the gnucash-user
> archives). His code was for Postgresql, I'm using mysql. At this
> point I get a nice Income Statement (aka P&L) by year (or
> month/day/microsecond) as well as Balance Sheets by year (or
> whatever increment I want). The SQL creates a "view", which can
> be opened using an OpenOffice Calc Data Pilot (pivot table).
>
> I've also expanded a portfolio query which I found in the mailing
> list (the reference escapes me at the moment). It produces a
> Portfolio table within Gnucash which can be plotted, again using
> OpenOffice calc pivot tables. I would be happy to share this
> stuff (given a little time for documenting).
>
>
>
> On 04/01/2011 05:42 PM, Martin Cunningham wrote:
>
> Hi -
>
> I contributed to this thread about a year ago hoping to
> generate interest in
> starting a library of SELECT queries; when executed against
> the GnuCash
> database, they could serve as ODBC data sources for Excel
> pivot tables or to
> generate reports in other tools like Access, Crystal Reports,
> etc. There
> doesn't seem to be much interest, or am I looking in the wrong
> place, or not
> explaining things properly? Im using MySQL as my back end for
> GnuCash,
> incidentally.
>
> To date, I have been able to produce a functioning query that
> I'll post
> later today that returns every journal entry in the database
> including date,
> description, debit account, credit account and amount (can't
> recall how it
> handles multiline splits). One key thing that's missing from
> my query: the
> FULL name of the account being debited (or credited), e.g.,
> "Assets:Current
> Assets:...:My Checking Account" instead of just "My Checking
> Account", which
> is what i have today. Or assign "Assets" to a column called
> "AcctLev1",
> "Current Assets" to "AcctLev2", etc.
>
> Has anyone else tried tackling this problem?
>
> Martin Cunningham
> T: (415) 448-6854 <tel:%28415%29%20448-6854>
>
>
> On Sun, Jul 18, 2010 at 3:53 AM, Geert Janssens
> <janssens-geert at telenet.be
> <mailto:janssens-geert at telenet.be>>wrote:
>
> On Saturday 17 July 2010, Martin Cunningham wrote:
>
> Ah - got it. Thank you!
>
> Is anyone posting SQL statements they have used to
> generate reports?
>
> seems
>
> a repository of those would be a useful thing.
>
> I think it's a little early for this. The sql backend is
> currently only
> available for the unstable development series. Most users
> will wait for the
> stable 2.4 release before updating their software, so most
> users haven't
> played with the sql backend yet.
>
> But if you like you are very welcome to add your results
> to the GnuCash
> wiki.
> I recently started a "Using GnuCash" section [1], which
> would be a suitable
> place to add useful SQL statements.
>
> Geert
>
> [1] http://wiki.gnucash.org/wiki/Using_GnuCash
>
> Martin Cunningham
>
>
> On Sat, Jul 17, 2010 at 3:34 AM, Geert Janssens
>
> <janssens-geert at telenet.be
> <mailto:janssens-geert at telenet.be>>wrote:
>
> On Friday 16 July 2010, Martin Cunningham wrote:
>
> Well I found an answer to one of my questions:
> the schema is posted
> here<http://wiki.gnucash.org/wiki/SQL>.
> I have downloaded/installed a copy of MySQL
> and the 'Workbench' GUI.
> Now what - can someone tell me how to connect
> to the GnuCash data
> source?
>
> Martin Cunningham
>
> Did you save your gnucash data into a mysql
> database already ?
>
> You can do so by opening your existing data file
> and use "Save as"
> to save it in mysql.
>
> From there on, you should be able to simply run
> your mysql queries in
>
> any
>
> mysql aware tool (like MySQL workbench).
>
> Geert
>
>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org <mailto: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.
>
>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user at gnucash.org <mailto: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.
>
>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: AccountsTreeScript
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20110404/99685ba2/attachment.cc>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: AccountsTreeBlank-sql
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20110404/99685ba2/attachment-0001.cc>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: AccountsTree-sql
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20110404/99685ba2/attachment-0002.cc>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: acctView-2-sql
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20110404/99685ba2/attachment-0003.cc>
More information about the gnucash-user
mailing list