reporting, again aka python for people (was: History Quotes)

[Christopher Lam]

Really just wanted to resurrect this thread to express my profuse thanks to
Sebastien for creating piecash. You have rekindled my love for Gnucash.
I had input data for several years into it yet found it exceedingly hard to
extract regular reports. The gnucash feature to 'save reports' only
appeared 1-2 years ago, and was a boon. Then came piecash.

Over 1-2 days, being *very* rusty in Python (I last made a .py around 10
years ago), I was able to concoct the following. My CoA has numerous
accounts, and I've placed all of my tax-deductible expenses under the
format Expenses:Business:*". I know this is functionally the 'Transaction
Register' report, but easier to report!

----start----
import datetime
from piecash import open_book
b = open_book('accounts-sql.gnucash')
date1 = datetime.date(2014, 4, 5)
date2 = datetime.date(2015, 4, 4)
total = 0
accountprefix = "Expenses:Business:"
accountname = "EXPENSE"
accountsfiltered = [i for i in b.accounts if accountprefix in i.fullname]
print()
print("--------------------------------------------------")
print("{} REPORT FOR PERIOD {:%d-%m-%Y} - {:%d-%m-%Y}".format(accountname,
date1, date2))
print("--------------------------------------------------")
for acc in accountsfiltered:
    trlist = [i for i in b.splits if i.account == acc]
    sel = []
    subtotal = 0
    for i in trlist:
        if (date1 <= i.transaction.post_date.date()) and
(i.transaction.post_date.date() <= date2):
            othersplit = [j for j in i.transaction.splits if not j == i]
            sel.append((i,othersplit))
    if sel:
        print(acc.fullname)
        subtotal = 0
        for i,othersplit in sel:
            otheraccount = othersplit[0].account.fullname if
len(othersplit) == 1 else "--Split--"
            print(" {:%d-%m-%Y} | {:.<60.60} | {:.<25.25} | {:8.2f}
".format(i.transaction.post_date, i.transaction.description, otheraccount,
i.value))
            subtotal += i.value
        print(" Sub-total: ${0:.2f}".format(subtotal))
        print()
    total += subtotal
print("===========")
print("Grand Total: ${0:.2f}".format(total))
--------stop--------------

...which produces the useful report as follows for the accountant:

-----start-----

--------------------------------------------------
EXPENSE REPORT FOR PERIOD 05-04-2014 - 04-04-2015
--------------------------------------------------
Expenses:Business:Insurance
 29-08-2014 | AXA: Landlord Insurance ....................................
| Assets:Current:HSBC Curre |    69.96
 29-09-2014 | AXA: Landlord Insurance ....................................
| Assets:Current:HSBC Curre |   164.56
 Sub-total: $234.52

Expenses:Business:Accounting
 08-12-2014 | SL ACCOUNTS.................................................
| Assets:Current:FD 1stAcc. |   360.00
 Sub-total: $360.00

Expenses:Business:Mortgage Interest
 25-04-2014 | DEBIT INTEREST..............................................
| Liabilities:HSBC Mortgage |   150.21
 01-05-2014 | Skipton BS Interest.........................................
| Assets:Current:HSBC Curre |   321.75
 04-06-2014 | Skipton BS Interest.........................................
| Assets:Current:HSBC Curre |   321.75
 03-07-2014 | Skipton BS Interest.........................................
| Assets:Current:HSBC Curre |   321.75
 05-08-2014 | Skipton BS Interest.........................................
| Assets:Current:HSBC Curre |   321.75
 03-09-2014 | Skipton BS Interest.........................................
| Assets:Current:HSBC Curre |   321.75
 03-10-2014 | Skipton BS Interest.........................................
| Assets:Current:HSBC Curre |   321.75
 05-11-2014 | Skipton BS Interest.........................................
| Assets:Current:HSBC Curre |   321.75
 03-12-2014 | Skipton BS Interest.........................................
| Assets:Current:HSBC Curre |   321.75
 06-01-2015 | Skipton BS Interest.........................................
| Assets:Current:HSBC Curre |   321.75
 04-02-2015 | Skipton BS Interest.........................................
| Assets:Current:HSBC Curre |   298.00
 04-03-2015 | Skipton BS Interest.........................................
| Assets:Current:HSBC Curre |   298.00
 Sub-total: $3641.96

===========
Grand Total: $4236.48
-----

On 22 November 2015 at 04:59, Sébastien de Menten <sdementen at gmail.com>
wrote:

> On Fri, Nov 20, 2015 at 10:58 PM, Wm... <tcnw81 at tarrcity.demon.co.uk>
> wrote:
>
> > Wed, 18 Nov 2015 06:45:11 <CAB2pxDtda8MKvy98dGboq=FQ67PeTXZahWHDCtQ=
> > CmxGHAMZfg at mail.gmail.com>
> > Sébastien de Menten <sdementen at gmail.com> wrote...
> >
> > piecash 0.10 has been just released with the ability to get all
> >>
> > transactions/splits and prices into neat pandas DataFrames.
> >
> > I will try it this weekend with interest.
> >
> > nice, keep me informed !
>
>
> > You can see an example of a usage of these dataframes to do basic
> reporting
> > here :
> >
> https://nbviewer.jupyter.org/github/sdementen/piecash/blob/master/example
> > s/ipython/piecash_dataframes.ipynb
> >
> > That is looking wonderful, Sebastien, I am *very* impressed.  You've
> > obviously put a lot of work in to only use the imports you have.
> >
> > Thanks Wm. The API is rather readable and simple to use. It can be a good
> basis to generate reports (with pandas a.o.) or to generate documents (with
> jinja2 and latex, or with reportlab a.o.).
>
>
> > One Q while it is on my mind, why plotly rather than matplotlib ? <-- not
> > a crit, a query, may become obvious when I try piecash latest myself
> >
> > Just because it has been open sourced last week and I wanted to try it
> :-)
> But indeed matplotlib is probably a more mature solution for charts.
> Anyway, there is no link between piecash and plotly (feel free to use
> whatever you prefer), piecash just enables to export the splits and the
> prices to pandas DataFrame. The chart were there to show visually the
> results.
>
>
> > I put the piecash google groups in CC so that further discussions
> specific
> > to piecash can be held there instead of on the gnucash ML.
> >
> > I'm not sure they should really be separated, it is just the writing that
> > is argued about, everything else is good for all, surely? :)
>
>
>
> ok, I am fine with any preference from the ML
> _______________________________________________
> 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.
>