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

[Christopher Lam]

+1 for 10x speedup.
Any further discussion will take place in the piecash Google group.

On 31 July 2016 at 20:50, Sébastien de Menten <sdementen at gmail.com> wrote:

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