Reporting on changes to personal net assets

Aaron Whitehouse lists at whitehouse.org.nz
Sat Dec 10 18:40:22 EST 2011


Hello all,

I am hoping for your help in replacing a spreadsheet with GnuCash
(custom) reports.

First of all, thank you for creating such a great piece of software! I
just made a donation to show my support.

I studied as an accountant and know my way around spreadsheets pretty
well. I have therefore made a spreadsheet for our personal finances that
I fill in each fortnight (after my wife and I get paid) to show how much
"progress" we have made since the previous fortnight. An example of that
spreadsheet (with fake data) is here:
http://www.whitehouse.org.nz/Example_finance_comparison.ods

I have been using GnuCash to report on income and expenses for years,
and have entered all our financial information into the program (much
more than we put into the spreadsheet). What I am hoping to do is move
all of the functionality that I currently have in my spreadsheet into
GnuCash, so that I can use that for everything (this would have the
added advantage that it would be much more accurate, as GnuCash has
information on our superannuation funds etc, which I don't snapshot each
fortnight in the spreadsheet). In essence, I expect that the solution
will be to create custom reports that show the information that I have
in the spreadsheet and its graphs. I hope to do this mainly because I
expect it will help other GnuCash users.

The specific functionality that I have not found in GnuCash is set out
below. I am posting here in case the functionality does exist (and I
just haven't found it), or someone on the list has already written a
custom report that would save me time:

1) Line graph showing fortnightly progress (see the "Graph" sheet in the
spreadsheet). Mike E has already addressed this (very promptly!) by
creating a new "Net Assets Line Graph" in this bug:
https://bugzilla.gnome.org/show_bug.cgi?id=664862
By changing the report options to fortnightly and setting the time
frame, this report is very close to my spreadsheet's.

2) A table showing the "progress" (change in Net Assets from the
previous fortnight (see "Sheet 1", row 7)). This is the reason that I do
the snapshot each fortnight, so is very important to me. If I choose to
have a table showing in the graph mentioned in 1 above, it will show the
Net Assets every week, but it does not show the difference between each
fortnight's Net Assets. Ideally this would have the option to be in
reverse chronological order, as I am most interested in the most recent
result.

3) Total progress (see "Sheet 1", row 8). I would like to show the total
change in Net Assets from the starting point of the report.

4) Average progress (see "Sheet 1", row 9). I would like to show the
average fortnightly change in Net Assets over the period of the report.
Weekly and average progress are shown in the graph "Fortnight progress".

5) Extraordinaries (see "Sheet 1", row 11). I see this a more difficult
problem for incorporating in GnuCash. In essence, we use this if we have
a "blow out" one way or another--say we get in a big bonus or buy
something really expensive. This distorts the numbers so dramatically
that I manually add/subtract them to cancel out their effect and see how
we would have done "but-for" the extraordinaries. The best method that I
can think of to implement this idea in GnuCash reports is some form of
"tagging" system. Then there could be some option in the report options
to include/exclude any transactions tagged with a certain tag (there
could be a list of tags in the options, just like there is currently a
list of accounts). I see that this could be more generally useful. As
one example, we had a lot of expenses related to our wedding, which were
also travel, food or accommodation costs. It would be quite neat to be
able to have a pie graph showing all wedding expenses by category. There
is an "Adjusted" Net Assets after Extraordinaries row and a 10-week
average of the adjusted progress in row 13. The Adjusted fortnightly,
and 10-week average, progress are shown in the graph on sheet "Adjusted
fortnight".

6) Average weekly interest. We own our own house with a mortgage. I
therefore calculate the interest cost per week as a conceptual
equivalent to weekly rental (ie are we paying less in interest than we
could rent a place for). It would be great if GnuCash had a report
showing and graphing a category of expense over time. A slight
complexity of this is that our mortgage interest is payable monthly (in
various separate transactions), but we are interested in the weekly amount.

7) This isn't in the spreadsheet, but as an aside it would be great if
GnuCash graphs were more interactive. I used MS Money years ago and, for
example, I believe that you could click a pie graph of expenses and it
would take you to that category. It would be neat if, on the line graph
of the Net Assets over time, I could hover the cursor near the line and
it would move a highlighted point along the line and show, say in a
label above that highlighted point, the y-value at that x-value.

If I do have to create custom reports from scratch for these things, is
the "new" way to do these:
http://wiki.gnucash.org/wiki/Custom_Reports_Using_Eguile
? I am using GnuCash 2.4.2. I am not a programmer, but hopefully can
muddle my way through making a custom report.

Finally, the online banking numbers that I use for the fortnightly
snapshot are often different from the sum of shown transactions. This is
normally because the balance for credit cards is updated before the
detail of the transaction comes through, or because accrued interest is
included in a mortgage account balance but only debited from our
mortgage at the end of the month. I assume that the best way to ensure
that the accounts are "up-to-date" on my snapshot date would be to do a
reconciliation of the account to that balance on that day and leave the
Imbalance there until I had the real data?

Thanks again for all of your hard work and I hope that others find this
(overly-long) post helpful. I look forward to your thoughts.

Regards,

Aaron


More information about the gnucash-user mailing list