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

Sun Jul 31 08:50:08 EDT 2016

Hello Christopher,

Nice script. You can simplify a bit the following
trlist = [i for i in b.splits if i.account == acc]
with
trlist = acc.splits

hello Wm,
Did you make some progress on building reports with piecash ?

seb

On Sat, Jul 30, 2016 at 5:29 PM, Christopher Lam <christopher.lck at gmail.com>
wrote:

> 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.
>>
>
>