[GNC] update on piecash, unofficial python bindings for GnuCash

[Sébastien de Menten]

hello everyone,

I bring you some news regarding *piecash *(https://piecash.readthedocs.io/),
the pure python binding for GnuCash that is easy to install and use, well
documented and run seamlessly even on windows.

piecash has been updated to support 4.2 GnuCash books (in one of their SQL
flavors: sqlite, postgres or mysql).

I have also updated a page with all GnuCash related github projects, sorted
by programming language that could be interesting for you => see
https://piecash.readthedocs.io/en/master/doc/github_links.html

I just discovered on https://wiki.gnucash.org/wiki/Python_Bindings that
piecash is referenced (thank you Henrik). Could you update the comment now
that piecash supports 4.2 ? and also adapt the link to the documentation of
piecash (currently it refers to https://pythonhosted.org/piecash/ instead
of  https://piecash.readthedocs.io). Thank you again Henrik!

Regards,

Sébastien
___
gnucash-user mailing list
gnucash-user@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Re: [GNC] GC 3.2 Import Price File CSV

[Sébastien de Menten]

Would it be possible you have a blank as first character in your security
editor (i.e. " ABF.L")? The screenshot you sent give me this impression...
But it may be just a visual artifact.

On Sat, Aug 18, 2018, 08:54 Megagrumpy  wrote:

> Thanks for the response, Deva. I have tried every combination of date
> format,
> and security ID with no luck. Unfortunately my screenshots were taken from
> different times so I agree - if I had tried to use those particular
> settings
> I would have had an error.  I have followed your instructions to the letter
> and still I get the message "Commodity From could not be understood"! In
> this case I have used the MDY date format. my input file reads:
> ABF.L,08/12/2018,25,GBP
>
> My commodity in the security editor is defined as ABF.L - see Scrrebshot:
> 
>
>
> I still get the same error and it is only that the security ABF.L cannot be
> recognised - see Screenshot:
>
> <
> http://gnucash.1415818.n4.nabble.com/file/t377722/ABF_error_screenshot.jpg>
>
>
> As you can see the date and all the other fields are OK and parse
> successfully. Only the Stock ID is rejected.
>
>
>
>
>
> --
> Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
> ___
> gnucash-user mailing list
> gnucash-user@gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -
> 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@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Re: [GNC] pros/cons of storage formats

[Sébastien de Menten]

Is the SQLite storage format immune to the issue regarding "very long time
to save the XML format" some users are experiencing?

On Thu, May 17, 2018, 10:46 David T. via gnucash-user <
gnucash-user@gnucash.org> wrote:

> And, as I noted in January, the sql format does not get compressed, so the
> files are much larger.
>
>
>
>   On Thu, May 17, 2018 at 10:37, Adrien Monteleone<
> adrien.montele...@lusfiber.net> wrote:   Keith,
>
> The SQL backends are having a few issues at the moment. It would be a good
> idea to search the list here and also to look over the bug reports on
> Bugzilla.
>
> I originally used MySQL when I was running an Ubuntu box as my daily
> machine, but changed to XML when I moved to MacOS, and then switched to
> sqlite3 about two years ago, but had to change back to XML since the 3.x
> series release due to several bugs related to the business features. (if
> you don’t use those, you might not have any problems)
>
> If all you want is the ability to run outside queries for custom reporting
> or data integration, then sqlite3 is probably your safest bet. MySQL and
> Postgres are much more involved to setup and maintain. Essentially, if
> you’re not a database admin, you probably don’t need those two or will not
> find any advantage to the maintenance learning curve and overhead, stick
> with sqlite3 or XML.
>
> As for searching the list archives, use the following syntax before your
> search query terms:
>
> site:lists.gnucash.org
>
> However, I see that DuckDuckGo does not return any results with this
> method.
>
> Google does, as well as StartPage. (the latter I find to be more privacy
> conscious than DDG and usually provides me more relevant results.) I
> haven’t tested any other search engines with that syntax.
>
> Regards,
> Adrien
>
>
> > On May 16, 2018, at 10:32 PM, Keith Keller <
> kkel...@wombat.san-francisco.ca.us> wrote:
> >
> > On Thu, May 17, 2018 at 03:21:13AM +, David T. wrote:
> >> I'm not sure how you looked,  but this topic had been discussed for
> many years on the list. A recent one was
> https://lists.gnucash.org/pipermail/gnucash-user/2018-January/074315.html.
> Another was
> https://lists.gnucash.org/pipermail/gnucash-user/2013-September/050410.html
> .
> >
> > Thanks David!  That January 2018 thread was helpful.  I'm not sure why
> > DDG didn't turn it up.  :(
> >
> > My impression is that the format really doesn't make an appreciable
> > difference to performance, and that a SQL format might be more flexible
> > for reporting but otherwise the formats are basically interchangeable.
> > Does that sound like a reasonable summary?
> >
> > I may simply stick with XML for now since it's easy and lazy.
> >
> > --keith
> >
> >
> > --
> > kkel...@wombat.san-francisco.ca.us
> >
> > ___
> > gnucash-user mailing list
> > gnucash-user@gnucash.org
> > To update your subscription preferences or to unsubscribe:
> > https://lists.gnucash.org/mailman/listinfo/gnucash-user
> > If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> > -
> > 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@gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -
> 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@gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -
> 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@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Re: [GNC] Automated Reporting

[Sébastien de Menten]

just the SQL backends (sqlite3, postgres and mysql)

On Tue, Apr 24, 2018 at 6:56 AM, Justin Phelps <jus...@onitato.com> wrote:

> On Mon, Apr 23, 2018 at 9:15 PM Sébastien de Menten <sdemen...@gmail.com>
> wrote:
>
>> With some python skills, you could use piecash (
>> http://piecash.readthedocs.io/en/master/) to build you own reports and
>> export them in the best format for your needs.
>> We are working to support gnucash 3.0, the current version support
>> gnucash 2.6.
>>
>> On Mon, Apr 23, 2018, 23:26 Justin Phelps <jus...@onitato.com> wrote:
>>
>>> I'm hoping there's a command line method for generating reports from a
>>> gnucash file. I want to script a series of daily, weekly, and monthly
>>> reports that are automatically emailed to certain people. I can achieve
>>> the
>>> scheduling and emailing from my server, but having some way to get this
>>> data out from gnucash without clicking through the interface would be
>>> really useful.
>>>
>>> How would you approach this challenge?
>>>
>> ___
>>> gnucash-user mailing list
>>> gnucash-user@gnucash.org
>>> To update your subscription preferences or to unsubscribe:
>>> https://lists.gnucash.org/mailman/listinfo/gnucash-user
>>> If you are using Nabble or Gmane, please see
>>> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
>>> -
>>> Please remember to CC this list on all your replies.
>>> You can do this by using Reply-To-List or Reply-All.
>>>
>>
> I certainly know more Python than Scheme at this point, so the piecash
> tools seems promising. Does it support the XML style file, or just the SQL
> based solutions?
>
___
gnucash-user mailing list
gnucash-user@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Re: [GNC] Automated Reporting

[Sébastien de Menten]

With some python skills, you could use piecash (
http://piecash.readthedocs.io/en/master/) to build you own reports and
export them in the best format for your needs.
We are working to support gnucash 3.0, the current version support gnucash
2.6.

On Mon, Apr 23, 2018, 23:26 Justin Phelps  wrote:

> I'm hoping there's a command line method for generating reports from a
> gnucash file. I want to script a series of daily, weekly, and monthly
> reports that are automatically emailed to certain people. I can achieve the
> scheduling and emailing from my server, but having some way to get this
> data out from gnucash without clicking through the interface would be
> really useful.
>
> How would you approach this challenge?
> ___
> gnucash-user mailing list
> gnucash-user@gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -
> 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@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Re: [GNC] Accounts columns currency

[Sébastien de Menten]

The default currency story has always puzzled me a bit in gnucash (a.o.
when working on piecash) but with your last emails John, now it makes sense
to me!

There is a "Book currency" (the ROOT account currency) that is used to
display the summary information at the bottom of the gnucash main window.
It is set once at book creation (the description of this choice in the 2nd
screen of the wizard is misleading as it says "choose the currency used for
new accounts", probably a documentation bug) and cannot be changed
afterwards via the GUI.
And there is the "default currency for new accounts", which is used when
creating new accounts in a book and can be changed via the gnucash
preferences.

Is that correct ? According to my tests (gnucash 2.6.21), yes but happy to
get an extra confirmation.


On Sun, Apr 15, 2018, 00:18 John Ralls  wrote:

>
>
> > On Apr 14, 2018, at 3:10 PM, John Ralls  wrote:
> >
> >
> >
> >> On Apr 14, 2018, at 1:13 PM, Alen Siljak  wrote:
> >>
> >>
> >>> I think that the currency offered on the accounts page is the currency
> of the root account. That’s set when you create a new book with File>New
> and its default is the default currency in Preferences. Once it’s set for a
> book it can’t be changed in the GUI.
> >>
> >>
> >> Thanks for the info, John. I was hoping it was that easy. But... Here
> are a few interesting details:
> >>
> >> - I wanted to convert my XML book to SQLite and modify the root-account
> currency. However, I see no other book format options besides xml. GnuCash
> was installed from the default OpenSuse repo.
> >> - Checked one of the SQLite backup files and the root account is
> already in EUR commodity.
> >> - The summary line at the bottom of the Accounts window is showing all
> the amounts in Euros, respecting the default currency setting, it seems.
> >>
> >> But the list of columns that can be added to the Accounts window still
> only contains the locale currency (GBP).
> >> Changing the system default language also had no effect on the currency
> displayed here.
> >> I was going to check the config directory but I see no
> ~/.config/gnucash.
> >
> > Having only the XML backend indicates that you haven't installed either
> libdbi or the dbd driver packages.
> >
> > Language and locale are separate. Currency, numeric separators, time
> formats, etc. are locale settings. If you launch GnuCash from the command
> line you can set the locale for it with e.g. LANG=de_DE gnucash. If you use
> a desktop icon it's easier to set it in /usr/share/etc/gnucash/environment.
> It's about halfway down and says # LANG = nl_BE. Remove the # and set it to
> your locale of choice. If you want the same language just uncomment the
> following line. If you want a different language, uncomment the following
> line and replace {LANG} with a comma-separated list of two-letter language
> codes in order of preference, e.g. fr,de,en.
> >
> > Is this 2.6.something or 3.0? For 2.6.something the config directory is
> ~/.gnucash.
> >
> > Are you sure you selected the Choose radio button in
> Preferences>Accounts>Default Currency?
>
> Oh, and I just noticed the tooltip for that radio button says "Use this
> currency as the default for new accounts", so I'm likely mistaken and it
> does less than I thought.
>
> Regards,
> John Ralls
>
> ___
> gnucash-user mailing list
> gnucash-user@gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -
> 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@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Re: [GNC] future of custom reports in 3.x

[Sébastien de Menten]

You can also look at
http://piecash.readthedocs.io/en/latest/object_model.html
Piecash is in fact a python ORM (sqlalchemy) on top of the gnucash dql
tables.

On Tue, Apr 10, 2018, 18:14 Matthew Pounsett  wrote:

> On 10 April 2018 at 11:45, Amish  wrote:
>
> > May be fastest and easiest way would be to first save sample data (say
> > only 1 transaction) to say PostgreSQL database and then use pg_dump to
> dump
> > it to text file.
> >
> > That's an approach, for sure.  However...  there are 24 tables just in my
> personal accounts sqllite db, but 21 gnc-*-sql.cpp files in the SQL
> back-end code, and not all of those are created.  That's an odd mismatch
> that needs to be accounted for.  Also, in the database there are no foreign
> keys defined, which means it's going to be difficult to confirm all of the
> expected relations.  That combination of facts makes for a really difficult
> job reverse-engineering the schema by trial and error.   A pointer to the
> documentation that the developers are using when they do their work would
> make the whole thing a lot easier.
> ___
> gnucash-user mailing list
> gnucash-user@gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -
> 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@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Re: [GNC] future of custom reports in 3.x

[Sébastien de Menten]

Re running reports within gnucash, I have toyed with the idea to be able to
call from gnucash a local web server to run a report and return the HTML to
gnucash (or even other mimetype like an excel, csv or word file). It needed
the Web Client module for guile (
https://www.gnu.org/software/guile/manual/html_node/Web-Client.html) which
is only avail as of guile 2.0.

With gnucash 3.0 that uses guile 2.2, I will work again on this and I am
more than open to extend the discussion to have a simple protocol/standard
to call a web server (the web server implements a simple Web API to get the
options and another one to get the result when passing both the gnucash URI
and the value of the options). My web server was running locally and was
implemented (obviously) with python and piecash.

Anyone interested ?

sebastien


On Apr 9, 2018 07:14, "Saša Janiška"  wrote:

John Ralls  writes:

> While we would like to replace the reporting system with something
> more flexible and user friendly we’re a very long way from being in a
> position to even start on it.

OK - that's nice to know. ;)


> In the meantime there are four alternatives for custom reports:

> 4. With an XML file use your favorite language's XML bindings with
> XPATH or XQUERY to retrieve the data and format your report.

I'd rather stay away from fiddling with XML stuff.


> 3. Learn Python and use either the python bindings or PieCash (also
> requires the SQL backend) to query the database and write custom
> reports.

I'll take a closer look at this option.


> 2. Learn SQL and use a SQL backend to extract the data you want. The
> results are generally amenable to import into a spreadsheet for
> further processing; you could also install the appropriate ODBC module
> for your SQL engine of choice and connect to it with Libre/OpenOffice,
> Microsoft Access, or some similar tool with a custom report writer or
> your favorite programming language’s SQL interface (e.g. DBAPI for
> Python).

This one seems to be close to 2nd option...


> 1. Learn Scheme and qof-query to make custom reports for use inside
> GnuCash.

This seems to be the most lucrative option enabling one to take
advantage of GC's infrastructure, but just wonder if replacing qof-query
with SQL query query would still allow using it along with Scheme since
"make reports for use inside GnuCash" certainly has its adavantages?


Sincerely,
Gour

--
You have a right to perform your prescribed duty, but you
are not entitled to the fruits of action. Never consider
yourself the cause of the results of your activities,
and never be attached to not doing your duty.


___
gnucash-user mailing list
gnucash-user@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/
Mailing_Lists for more information.
-
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@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see 
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.

Re: GNUcash setup

[Sébastien de Menten]

Adrien,

This thread
https://lists.gnucash.org/pipermail/gnucash-user/2016-September/066879.html
talks about a similar topic. I have posted some "experimental hacks" on it
to have a book with multiple root accounts. But far from guaranteed to work
properly in gnucash.

Sébastien

On Mar 3, 2018 02:02, "Adrien Monteleone" 
wrote:

> Thanks Dave,
>
> To clarify, what I was suggesting was this:
>
> Assets
> Assets:Personal
> Assets:Entity1
> Assets:Entity2
>
> with appropriate sub-accounts under each.
>
> In line with this you’d also have:
>
> Expenses
> Expenses:Personal
> Expenses:Entity1
> Expenses:Entity2
>
> with appropriate sub-accounts under each, and so on for Revenue and then
> Liabilities and Equity as needed.
>
> Each of those ’second levels’ should probably be marked as placeholders to
> avoid a mess.
>
> The other alternative would be this:
>
> Assets
> Assets:Current Assets:Checking
> Assets:Current Assets:Checking:Personal
> Assets:Current Assets:Checking:Entity1
> Assets:Current Assets:Checking:Entity2
>
> That is, if such actual real accounts exist for each entity. The same
> would go for liabilities and Equity would most likely ALL be under
> Personal, so no need for separate accounts.
>
> For expenses, this would be the alternate format:
>
> Expenses
> Expenses:Utilities
> Expenses:Utilities:Electricity
> Expenses:Utilities:Electricity:Personal
> Expenses:Utilities:Electricity:Entity1
> Expenses:Utilities:Electricity:Entity2
>
> with the individual subs being their respective percentages allowed to be
> deducted on the tax return. (or more likely, for that particular expense
> type, just leave everything personal and calculate the percentage at tax
> time off the main expense, which is personal)
>
> Some expenses might only be for each of the business entities or one of
> the other, so not necessarily all expense categories would have entity
> sub-accounts.
>
> In the second set of examples, the level just above the entities should
> probably be marked a place-holder.
>
> Otherwise, yes, you’d have to keep separate books for each and figure out
> how to handle ‘paying yourself’ according to your local tax laws. You’d
> also have to figure out your itemized deductions a bit differently, but
> separate books would also give you separate ‘loss or gain from business
> activity’ numbers.
>
> I’ve seen prior discussions showing both these methods with respect to
> rental property tracking. All properties are part of the same legal entity
> and so should all be on the same statements and return, but the owner
> wanted to track their profitability individually.
>
> Personally, I keep everything in one book and don’t separate entities at
> all because I don’t have individual concerns I need to analyze. But I do
> track separate “revenue streams” for various work.
>
>
> Regards,
> Adrien
>
> > On Mar 2, 2018, at 6:26 PM, DaveC49  wrote:
> >
> > Ken, Adrien
> > Just a quick note re "separate account trees for each business".
> >
> > I have experimnted to try to do this within the one set of books (i.e.
> file)
> > in Gnucash and have never found a way of doing it.
> >
> > My problem has always been creating an entity level top account as there
> is
> > no top level account of type entity in the Gnucash structure which can
> have
> > the other major account types (i.e. Asset, Liability, Equity, Income,
> > Expense) as the type of its sub accounts.
> >
> > The account type of a top level account is restricted to be one of {Bank,
> > Cash, Asset, Credit Card, Liabilty, Stock, Mutual Fund, Income, Expense,
> > Equity, A/Receivable, A/Payable,or Trading}. Having made a choice of the
> top
> > level account (e.g. Asset),the choice of account type for a subaccount is
> > restricted to types in the Asset and Liability classes , i.e.{Bank, Cash,
> > Asset, Credit Card, Liability, Stock, Mutual Fund, A/Receivable,
> A/Payable}.
> > If the top level account is of type Liability, the choices are
> restricted to
> > the same list. If the top level account is an Equity account type, its
> sub
> > accounts can only be of type Equity. Expense and Income class top level
> > accounts can only have a type for the subaccounts of {Income, Expense}.
> This
> > precludes setting up multiple entity structures with the entities as
> > separate top level accounts with their own sub trees.
> >
> > I think this is by design and that the expected strategy is to use
> separate
> > files for separate entity.
> >
> > I have adopted the second strategy you mentioned of having "entity"
> > subaccounts under each of the major top level classes {Asset,Libility,
> > Equity, Income, Expenses} to separate my wife's, mine and a few minor
> > business ventures we operate jointly or separately. It works OK, but
> > increases the likelihood of putting a transaction in the wrong account
> and
> > the balance sheets and income statements are not ideal but easily
> rearranged
> > once 

Re: File Format : XML v MySQL / postgres / sqllite

[Sébastien de Menten]

It is indeed a python package so typically you write a script. For
instance, create the file test_piecash.py with the content

# start of the file
import piecash
book = piecash.open_book("/the/path/to/your/gnucash-book.gncash")

for transaction in book.transactions:
print(transaction.post_date)
for split in transaction.splits:
print("  ", split.account.name, split.value)
print()
# end of the file

and then run it with "python test_piecash.py" and you will see all your
transactions printed (do not forget to change "
/the/path/to/your/gnucash-book.gncash" with the path to your real gnucash
file save in the SQLite format).

You can see other examples of use in the piecash doc (like on
http://piecash.readthedocs.io/en/latest/tutorial/index_existing.html#access-to-objects
)

I was answering to this thread as the topic was about the file format and
how to access the file outside gnucash ... but indeed, it requires some
programming skills (but python is one of the most accessible language out
there).

If you have further questions or remarks that are piecash specific, better
to post on https://groups.google.com/forum/#!forum/piecash

kr

sebastien

On Sun, Jan 28, 2018 at 12:58 PM, David T. <sunfis...@yahoo.com> wrote:

> For what it’s worth, I was able to execute “sudo pip install piecash”
> successfully, but there is a gap for me at this point: what next? Is there
> a python shell? Am I supposed to write a script file (and once written,
> would I use some python command to run it? Which command?)? What, exactly,
> did I install?
>
> Clearly, piecash isn’t for the average end user…
>
> David
>
>
> On Jan 28, 2018, at 4:31 PM, Sébastien de Menten <sdemen...@gmail.com>
> wrote:
>
> Editing a gnucash book via piecash is not the approved way of doing it
> (advice from the gnucash core team that recommends to go through the
> gnucash engine) but a very nice way ;-)
>
> However, for reading gnucash books, it is an alternative as they are many
> to choose (to read from XML, to read from SQL, ...)
>
> On Jan 28, 2018 12:03, "D" <sunfis...@yahoo.com> wrote:
>
>> Sébastien,
>>
>> I know that users are advised not to edit their Gnucash data directly; is
>> piecash an approved application to edit Gnucash data directly?
>>
>> David
>>
>>
>> On January 28, 2018, at 3:41 PM, Sébastien de Menten <sdemen...@gmail.com>
>> wrote:
>>
>>
>> David,
>>
>> This is one of the key reason for piecash to exist as it does not need
>> any compilation (it is a pure python package) and works even without having
>> gnucash installed.
>> It doesn't use gnucash libraries as it reads (and writes) directly to the
>> gnucash book through SQL.
>>
>>
>> I do not have a Mac so I can't test the installation directly but it
>> should be just a matter of installing the "piecash" package available on
>> PyPI (the python package index).
>>
>> Sebastien
>>
>> On Jan 28, 2018 08:00, "David T." <sunfis...@yahoo.com> wrote:
>>
>>> Sebastien,
>>>
>>> The problem isn’t with Python (I am told that Macs already have Python
>>> on them). The problem as I understand it (John will no doubt correct me
>>> when my statements prove ill-informed or flat out wrong) has to do with the
>>> fact that GnuCash requires a specific version of Python, which can only be
>>> guaranteed by compiling GnuCash with the current version of Python. Short
>>> of shipping GnuCash with a complete Python installation, the only way
>>> around this is for individual users to compile locally. Which, as I noted,
>>> is a painful process for me. But thanks for the information.
>>>
>>> David
>>>
>>>
>>> On Jan 28, 2018, at 11:55 AM, Sébastien de Menten <sdemen...@gmail.com>
>>> wrote:
>>>
>>> Hello David,
>>>
>>> Regarding the use of python on windows and Mac, Anaconda has made a huge
>>> difference in the last years providing all packages in an easy precompiled
>>> version.
>>> You can have a look at https://conda.io/docs/user-
>>> guide/install/macos.html for the installation (which is simple and does
>>> not require any compiler).
>>>
>>>
>>> On Jan 28, 2018 05:42, "D" <sunfis...@yahoo.com> wrote:
>>>
>>>> Ah, yes that was my incorrect reference. Sorry for the crossed
>>>> reference.
>>>>
>>>> I will note that my confusion stems in part from the fact that, as a
>>>> Mac user, I have never used any

Re: File Format : XML v MySQL / postgres / sqllite

[Sébastien de Menten]

David,

This is one of the key reason for piecash to exist as it does not need any
compilation (it is a pure python package) and works even without having
gnucash installed.
It doesn't use gnucash libraries as it reads (and writes) directly to the
gnucash book through SQL.


I do not have a Mac so I can't test the installation directly but it should
be just a matter of installing the "piecash" package available on PyPI (the
python package index).

Sebastien

On Jan 28, 2018 08:00, "David T." <sunfis...@yahoo.com> wrote:

> Sebastien,
>
> The problem isn’t with Python (I am told that Macs already have Python on
> them). The problem as I understand it (John will no doubt correct me when
> my statements prove ill-informed or flat out wrong) has to do with the fact
> that GnuCash requires a specific version of Python, which can only be
> guaranteed by compiling GnuCash with the current version of Python. Short
> of shipping GnuCash with a complete Python installation, the only way
> around this is for individual users to compile locally. Which, as I noted,
> is a painful process for me. But thanks for the information.
>
> David
>
>
> On Jan 28, 2018, at 11:55 AM, Sébastien de Menten <sdemen...@gmail.com>
> wrote:
>
> Hello David,
>
> Regarding the use of python on windows and Mac, Anaconda has made a huge
> difference in the last years providing all packages in an easy precompiled
> version.
> You can have a look at https://conda.io/docs/user-guide/install/macos.html
> for the installation (which is simple and does not require any compiler).
>
>
> On Jan 28, 2018 05:42, "D" <sunfis...@yahoo.com> wrote:
>
>> Ah, yes that was my incorrect reference. Sorry for the crossed reference.
>>
>> I will note that my confusion stems in part from the fact that, as a Mac
>> user, I have never used anything to do with python and Gnucash, on account
>> of the high barrier to install the proper toolsets (i.e., compiling
>> yourself). My previous attempts at this process always ended in frustration
>> and failure. As a consequence, when I see anything that mentions python and
>> Gnucash, my eyes glaze over.
>>
>> In the past, there hasn't been much to recommend beating my head against
>> that wall, but perhaps the time has come to see what happens at this time,
>> since there eh to be increasing numbers of ways to use python with Gnucash
>> data.
>>
>> David
>>
>> On January 28, 2018, at 12:55 AM, John Ralls <jra...@ceridwen.us> wrote:
>>
>> I suspect David was thinking of CuteCash, Christian Stimming’s
>> experimental Qt GUI.
>>
>> Regards,
>> John Ralls
>>
>> > On Jan 27, 2018, at 10:44 AM, Sébastien de Menten <sdemen...@gmail.com>
>> wrote:
>> >
>> > Hello David,
>> >
>> > I saw you were mentioning piecash in your message. If you talk about
>> > http://piecash.readthedocs.io/en/latest/, it is not a proof of concept
>> for
>> > a frontend but an alternative to the gnucash python bindings to work
>> with
>> > gnucash books stored in any of the 3 SQL backends (SQLite, postgres and
>> > MySQL) and is alive and maintained (last release on the 20th December
>> 2017).
>> >
>> > The following article https://arachnoid.com/gnucash_utilities/ shows
>> some
>> > interesting use cases for it.
>> >
>> > With gnucash 3.0, piecash will also allow you to write your own reports
>> > using si python and jinja2 (work in progress).
>> >
>> > For people interested to contribute, https://github.com/sdementen/p
>> iecash.
>> >
>> >
>> > On Jan 27, 2018 18:27, "D via gnucash-user" <gnucash-user@gnucash.org>
>> > wrote:
>> >
>> > Mike,
>> >
>> > There is nothing new as far as I know about the file formats. You are
>> right
>> > that the primary benefit to using sql is being able to generate data
>> sets
>> > from sql that can used in reports. Processing a data file is no quicker
>> in
>> > sql, since it all gets loaded in at start up. Sql, moreover, adds a
>> > database manager role to the mix; many users are not prepared to learn
>> how
>> > to manage a database platform to manage their financial accounts.
>> >
>> > I stick with the XML because it also can store the data compressed; when
>> > dealing with the main data file and all its copies, the difference
>> between
>> > 3mb and 13mb adds up quickly.
>> >
>> > Piecash, as I understand it, was a proof-of-concept 

Re: File Format : XML v MySQL / postgres / sqllite

[Sébastien de Menten]

Hello David,

Regarding the use of python on windows and Mac, Anaconda has made a huge
difference in the last years providing all packages in an easy precompiled
version.
You can have a look at https://conda.io/docs/user-guide/install/macos.html
for the installation (which is simple and does not require any compiler).


On Jan 28, 2018 05:42, "D" <sunfis...@yahoo.com> wrote:

> Ah, yes that was my incorrect reference. Sorry for the crossed reference.
>
> I will note that my confusion stems in part from the fact that, as a Mac
> user, I have never used anything to do with python and Gnucash, on account
> of the high barrier to install the proper toolsets (i.e., compiling
> yourself). My previous attempts at this process always ended in frustration
> and failure. As a consequence, when I see anything that mentions python and
> Gnucash, my eyes glaze over.
>
> In the past, there hasn't been much to recommend beating my head against
> that wall, but perhaps the time has come to see what happens at this time,
> since there eh to be increasing numbers of ways to use python with Gnucash
> data.
>
> David
>
> On January 28, 2018, at 12:55 AM, John Ralls <jra...@ceridwen.us> wrote:
>
> I suspect David was thinking of CuteCash, Christian Stimming’s
> experimental Qt GUI.
>
> Regards,
> John Ralls
>
> > On Jan 27, 2018, at 10:44 AM, Sébastien de Menten <sdemen...@gmail.com>
> wrote:
> >
> > Hello David,
> >
> > I saw you were mentioning piecash in your message. If you talk about
> > http://piecash.readthedocs.io/en/latest/, it is not a proof of concept
> for
> > a frontend but an alternative to the gnucash python bindings to work with
> > gnucash books stored in any of the 3 SQL backends (SQLite, postgres and
> > MySQL) and is alive and maintained (last release on the 20th December
> 2017).
> >
> > The following article https://arachnoid.com/gnucash_utilities/ shows
> some
> > interesting use cases for it.
> >
> > With gnucash 3.0, piecash will also allow you to write your own reports
> > using si python and jinja2 (work in progress).
> >
> > For people interested to contribute, https://github.com/sdementen/
> piecash.
> >
> >
> > On Jan 27, 2018 18:27, "D via gnucash-user" <gnucash-user@gnucash.org>
> > wrote:
> >
> > Mike,
> >
> > There is nothing new as far as I know about the file formats. You are
> right
> > that the primary benefit to using sql is being able to generate data sets
> > from sql that can used in reports. Processing a data file is no quicker
> in
> > sql, since it all gets loaded in at start up. Sql, moreover, adds a
> > database manager role to the mix; many users are not prepared to learn
> how
> > to manage a database platform to manage their financial accounts.
> >
> > I stick with the XML because it also can store the data compressed; when
> > dealing with the main data file and all its copies, the difference
> between
> > 3mb and 13mb adds up quickly.
> >
> > Piecash, as I understand it, was a proof-of-concept experiment to see
> > whether a python front end could be designed. It has not been maintained
> > for some years now, and I recall recent discussion about changing its
> > status with the project.
> >
> > As far as I know, there has been no change in recommendation to stay with
> > xml.
> >
> > David
> >
> > On January 27, 2018, at 12:13 PM, Adrien Monteleone <
> > adrien.montele...@gmail.com> wrote:
> >
> > I switched from XML to SQLite about a year ago. I perceive a performance
> > improvement on loading, but that’s about it. (and I generally never shut
> > the app down anyway)
> >
> > I haven’t attempted any custom reporting yet, but I plan to.
> >
> > I could be mistaken, but I think PyCash is designed to parse the XML file
> > so you can still get the reporting you want.
> >
> > Regards,
> > Adrien
> >
> >> On Jan 27, 2018, at 12:55 AM, Mike Stillingfleet <
> > mikestillingfl...@fastmail.co.uk> wrote:
> >>
> >> Dear All,
> >>
> >> XML is the default when creating a new file.
> >>
> >> I have seen various quite dated questions about the decision process of
> > selecting which db to use.
> >>
> >> I think that the main advantage of using MySQL would be the ability to
> > generate custom reports.
> >>
> >> Are there any other advantages and are there any differences in the
> > interface between the various db's. Does the forum still recommend XML.
> &

Re: File Format : XML v MySQL / postgres / sqllite

[Sébastien de Menten]

Hello David,

I saw you were mentioning piecash in your message. If you talk about
http://piecash.readthedocs.io/en/latest/, it is not a proof of concept for
a frontend but an alternative to the gnucash python bindings to work with
gnucash books stored in any of the 3 SQL backends (SQLite, postgres and
MySQL) and is alive and maintained (last release on the 20th December 2017).

The following article https://arachnoid.com/gnucash_utilities/ shows some
interesting use cases for it.

With gnucash 3.0, piecash will also allow you to write your own reports
using si python and jinja2 (work in progress).

For people interested to contribute, https://github.com/sdementen/piecash.


On Jan 27, 2018 18:27, "D via gnucash-user" 
wrote:

Mike,

There is nothing new as far as I know about the file formats. You are right
that the primary benefit to using sql is being able to generate data sets
from sql that can used in reports. Processing a data file is no quicker in
sql, since it all gets loaded in at start up. Sql, moreover, adds a
database manager role to the mix; many users are not prepared to learn how
to manage a database platform to manage their financial accounts.

I stick with the XML because it also can store the data compressed; when
dealing with the main data file and all its copies, the difference between
3mb and 13mb adds up quickly.

Piecash, as I understand it, was a proof-of-concept experiment to see
whether a python front end could be designed. It has not been maintained
for some years now, and I recall recent discussion about changing its
status with the project.

As far as I know, there has been no change in recommendation to stay with
xml.

David

On January 27, 2018, at 12:13 PM, Adrien Monteleone <
adrien.montele...@gmail.com> wrote:

I switched from XML to SQLite about a year ago. I perceive a performance
improvement on loading, but that’s about it. (and I generally never shut
the app down anyway)

I haven’t attempted any custom reporting yet, but I plan to.

I could be mistaken, but I think PyCash is designed to parse the XML file
so you can still get the reporting you want.

Regards,
Adrien

> On Jan 27, 2018, at 12:55 AM, Mike Stillingfleet <
mikestillingfl...@fastmail.co.uk> wrote:
>
> Dear All,
>
> XML is the default when creating a new file.
>
> I have seen various quite dated questions about the decision process of
selecting which db to use.
>
> I think that the main advantage of using MySQL would be the ability to
generate custom reports.
>
> Are there any other advantages and are there any differences in the
interface between the various db's. Does the forum still recommend XML.
>
> Regards
>
> --
>  Mike Stillingfleet
>  mikestillingfl...@fastmail.co.uk
> ___
> gnucash-user mailing list
> gnucash-user@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@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@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@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.

Re: customizing reports

[Sébastien de Menten]

Hello Richard

If you have some programming experience, I can provide you some help to
achieve what you want through piecash (for the python programming language).

Sebastien

On Jan 10, 2018 18:32, "Adrien Monteleone" 
wrote:

> I’ve seen such a report your describe in other software, but only for one
> customer at a time and only as an indicator of “y/n” as to if a regular
> monthly payment was made, not the actual amount. I don’t think such a
> report currently exists that displays the info the way you prefer. You’d
> have to write your own report. (daunting indeed)
>
> There are options to writing a report from scratch, but they aren’t much
> better with respect to learning curve.
>
> If you are using the MySQL backend, you could query the database directly
> and use the results of the query in a report.
>
> I think there is also something called PyCash that will allow you to
> access some data from outside GnuCash using Python which you could then use
> to generate the report you want.
>
> Otherwise, the reports that are available include the Customer Report &
> Receivable Aging report.
>
> The Customer Report will show each invoice and payment (done through the
> business features) for any specified period. But this is one customer at a
> time. Think of it as a Statement of Account.
>
> The Receivable Aging report will show all customers, but shows you which
> ones are late (30 days, 60 days, etc.) Again, this pulls data through the
> use of the business features only. (you’ll need to be issuing invoices and
> processing payments)
>
> If you aren’t up for writing a report either directly for GnuCash or
> outside of it using SQL queries or Python, you can always track what you
> want in a spreadsheet separately.
>
> There is one other option that will at least display if an invoice is paid
> or not so you can see if any customers missed a payment. You can do an
> invoice search to show all invoices which will give you a list of all of
> your invoices with a checkbox column that shows if they are paid or not.
> However, this list will show each invoice per line (instead of each
> customer as you requested) and the list is not exportable, best I can tell.
> To show all invoices change the search criteria to “matches regex” and use
> a “.” as the search term.
>
> Regards,
> Adrien
>
> > On Jan 5, 2018, at 5:54 PM, richard.cour...@verizon.net wrote:
> >
> > I’m trying to escape Quicken and return to double entry accounting  with
> gnu cash.
> >
> > But creating/customizing reports has me stumped!
> >
> >
> >
> > Let's say I have 20 customers each paying monthly for a service.
> >
> > I'd like to track the payments over a 12 month period for all 20
> customers.
> >
> > What kind of report can I create with gnu cash, so each customer will be
> a row and in each column, that customer's payment will be seen in the month
> it was paid?
> >
> >
> >
> > An application might be collecting rents from 20 tenants.
> >
> >
> > ---
> > This email has been checked for viruses by Avast antivirus software.
> > https://www.avast.com/antivirus
> > ___
> > gnucash-user mailing list
> > gnucash-user@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@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@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.

Re: export general ledger

[Sébastien de Menten]

To export a gnucash book to a ledger-cli output, you may be interested by
the piecash python module and the related script
https://github.com/sdementen/piecash/blob/master/scripts/piecash_ledger.py


On Mon, Dec 18, 2017 at 11:32 AM, Jeff Abrahamson  wrote:

> On 18/12/17 03:26, Wm via gnucash-user wrote:
> > On 17/12/2017 19:31, Jeff Abrahamson wrote:
> >> I'd like to export the general ledger to a csv file.  When I use File ->
> >> Export, however, I'm offered a choice of exporting income, expenses,
> >> assets, or liabilities.  But I'd like to have all transactions in the
> >> csv file.
> >>
> >> I think I see how to do this using python (the example script
> >> account_analysis.py in the examples is instructive).  But this seems so
> >> basic I suspect I'm missing something.
> >>
> >> Many thanks for any pointers.
> > May be easier to do a transaction report or similar and copy and paste
> > that into a spreadsheet.
> >
> > Alternatively there are a number of scripts that can take gnc files of
> > some flavour and produce ledger-cli / beancount compatible files which
> > are generally mores useful than csv files for accounting data anyway.
>
> Ah, ledger-cli and beancount are neat, thanks for the pointer.
>
> This sort of export is something I want to be able to do frequently.
> I'll have a look around for scripts to automate it.
>
> --
>
> Jeff Abrahamson
> +33 6 24 40 01 57
> +44 7920 594 255
>
> http://p27.eu/jeff/
>
> ___
> gnucash-user mailing list
> gnucash-user@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@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.

Re: Gnucash 2.7 on Windows

[Sébastien de Menten]

I filled a bug in bugzilla. Could it be possible to fill issues in github
instead as the bugzilla interface is rather complex ?

On Sat, Nov 18, 2017 at 4:38 AM, John Ralls <jra...@ceridwen.us> wrote:

>
>
> On Nov 17, 2017, at 5:05 PM, David Carlson <david.carlson@gmail.com>
> wrote:
>
> I assume that you know that 2.7 is is a pre-release version, I would call
> it an alpha or beta version, not for production use.
>
> That said, I thought that the developers had updated it so that it did not
> crash instantly in Windows.  Be sure that you have the most recent build.
> It may be a bit hard to find.  I personally am not testing it myself.
>
> David C
>
> On Fri, Nov 17, 2017 at 5:55 PM, Sébastien de Menten <sdemen...@gmail.com>
> wrote:
>
> Had anyone got some success in running the gnucash 2.7 binaries on Windows?
> My version just crashes at startup time without any meaningful logs.
> ___
> gnucash-user mailing list
> gnucash-user@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.
>
>
> The latest shouldn’t be hard to find at all, the links are in the
> announcement that leads http://www.gnucash.org/news.phtml. Since we’ll be
> releasing 2.7.2 next weekend it would be good to know if there’s still a
> problem. If 2.7.1 crashes please file a bug report as soon as you can with
> as much detail as possible—a stack trace would be ideal.
>
> Regards,
> John Ralls
>
>
>
___
gnucash-user mailing list
gnucash-user@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 2.7 on Windows

[Sébastien de Menten]

Had anyone got some success in running the gnucash 2.7 binaries on Windows?
My version just crashes at startup time without any meaningful logs.
___
gnucash-user mailing list
gnucash-user@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.

Github projects related to gnucash

[Sébastien de Menten]

Hello,

Just to let you know, I have updated the list of github projects with
gnucash in their description:
http://piecash.readthedocs.io/en/latest/doc/github_links.html

Python is the front runner language in terms of # projects followed at some
distance by perl and Java.

Kind regards,

Sébastien
___
gnucash-user mailing list
gnucash-user@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.