Reporting GnuCash in OpenOffice.org

Does this work with OpenOffice 3.0? I could not get 3.0 to recognize the macro and noted that the paths have changed.
 

First time I hear of somebody who tried it with Ooo 3.0. I have not tested it yet and frankly will not do it until OpenOffice 3.0 is distributed with ubuntu. Meanwhile I would be glad if other users post information about problems and solutions.
Knut
 

Tried it in ubuntu 8.10 [Wubi] with openoffice 2.4. Appears to work - copied over profile from Windows.
 

I have also tried it in OpenOffice 3.0, with same problem as anonymous, the macro was not recognised. I put the macro in /home/bob/.ooo3/user/Scripts/python. I had to create the python part of that path.
 

Thank you, Bob, for the information. But did it work, after you had created the complete path?
 

My reading of Bob's comment is that it did not work - even with the creation of the phython directory in the path.

I note that Ubuntu has 3.0. Goggle "Ubuntu 8.10 OpenOffice" and the 3rd or 4th item is http://news.softpedia.com/news/How-To-Install-OpenOffice-org-3-0-in-Ubuntu-8-10-96449.shtml.
 

It works with OpenOffice 3.0 if you put the macro in the main macro directory (i.e. not per user). On Windows, this is C:\Program Files\OpenOffice.org 3\Basis\share\Scripts\python.

It does not, however, work with OpenOffice 3 on Mac OS X 10.5.x (known python issue). I was unable to get it to work on OOo 2.4 on OS X. It is kind of odd - I can see the macro in the Run dialog, but when I select it, nothing shows in the right pane. The "Hello World" python script runs OK in 2.4, so it may be a syntax issue of some sort (this is speculation on my part).
 

There is an upcoming ubuntu version update (8.10) and the default version of Ooo is still 2.4.1. As an ubuntu user I will leave it there, in six months Ooo 3 will be included and its teething problems will be over. See Ubuntu explains OpenOffice.org 3.0 decision
 

DES is correct as to placement of macro under 3.0 in Windows XP. Path works. When I attempt to fill, I get this error which I did not get under Ubuntu 2.4. - it just worked.

#This file was generated by OpenOfficeMakro: SetGnuCashFilePaths
GnuCashDatafile: C:\Documents and Settings\xx\My Documents\Profile\xx
Registered Database in Openoffice.org: bdm

Errorlogging Tue Oct 28 04:57:42 2008
Error! SAXParseException unknown:1:0: not well-formed (invalid token)
current routine = init_split
last input startElement: not available
last input endElement: not available
last input insert_statement: not available
 

Tried in Openoffice 2.4.1 portable for Windows - same result. Can set path, but fill gives same error.
 

Anonymous, see "Limitations and Preconditions" on page 1 of this doku (introduction/overview). You must use unzipped GnuCash input file. This has nothing to do with 3.0 or not. Well, at
least the workaround for the macro's location is confirmed.
Knut
 

Knut:

You are correct. As soon as I unchecked the compressed box in general preferences - it worked in OpenOffice 3.0 under windows xp.

Thanks
 

ok, there is an issue tracker in Ooo where the bug is described.
I had the same difficulty when Ooo 2.4 was new and they fixed it pretty soon. Meanwhile Linux and Windows users could try Des' workaround as long as issue #93994 (Python user's script are not recognized) is not resolved (Mac users could look into issue #93084 to see if they can make something of it).
See issue tracker and search for "93994" in search field "issues numbered". When this issue has state "RESOLVED" and resolution "FIXED" it should be possible again to put the macro into the user-dependant script directory if you update to the newest version of Ooo3.
 

I'm having a problem with the macro. I use Ooo 3.0 on a Gentoo box. The macro runs well, no errors what so ever, but no data is imported at all to the database. Usually it takes only a few seconds and the operation of "importing" data is over.

What could be the reason?
 

Hello Heikki,
greetings to Linus Torvalds homecountry! Could you send me an email (see link imprint/contact) and we discuss it off the blog? Please send your file GnuCashFilePaths.txt as attachment.

Knut
 

Hi Knut,

This looks really interesting. From a business perspective GnuCash sucks in Invoice formatting. A business likes to have output that handles its Logo well and puts address fields where they can be used in a DL sized "window face" envelope. This is really difficult to set up under GC, but since an Invoice is just a report, then maybe there is some hope that your macro could save the day. Can te invoice fields be imported via your macro, into OOoWriter?

If not, then some design work in Calc to produce a suitable Invoice template may be necessary.

Andrew
 

Hi Andrew,
simple and plain: No. Business Objects are not exported by the macro - I think it is suited for Private Finance only.
Kind regards
Knut
 

This macro looks like just what I need to produce custom reports in OOO from gnucash files, but I cannot get it to work.
I am getting a different error message. Can you please advise.

Using Windows XP
with GnuCash 2.2.8
with OpenOffice 3.0.0
with the macro placed in C:\Program Files\OpenOffice.org 3\Basis\share\Scripts\python

I set the directories of my gnucash file and database (both in C:\gnucash\)
Then I try and load the data.
I get the following error message () substituted for <> on unknown

Error! SAXParseException (unknown):1:0 not well-formed (invalid token)
For Further information look into C:\Documents and Settings\Peter\Application Data\OpenOffice.org\3\user\config\GnuCashFilePaths.txt

Loking at GnuCashFilePaths.txt I find

#This file was generated by OpenOfficeMakro: SetGnuCashFilePaths
GnuCashDatafile: C:\GNUcash\gn_Amy_folder\gn_Amy
Registered Database in Openoffice.org: gn_Amy

Errorlogging Wed Dec 31 19:25:21 2008
Error! SAXParseException (unknown):1:0: not well-formed (invalid token)
current routine = init_split
last input startElement: not available
last input endElement: not available
last input insert_statement: not available

Can you help me please?
 

Peter, please read the above comments of October 28, 2008. Same errormessage, probably same cause.
Knut
 

Hi Knut,
I was on my way to dive into the scheme reporting API of gnucash when I found your work and tried it. I am on a Gentoo box, with ooo3, and couldn't get it to work. No error, simply no data but the tables and views are created.

I spent some times on your code and because I couldn't understand the cause, I hacked your macro a bit. It seems that the xml sax handler is only triggered on its character() method and never on startElement()/endElement(). Thus no data and no error. When I use this handler from command line, it works perfectly well, so my weak mind tends to suppose that there is a bug in the parser that comes with OOo3. From that point, I have reached my limits in term of python and ooo macro (in both, I am a beginner). There could be some hope there (another XML parser) : http://www.oooforum.org/forum/viewtopic.phtml?t=4907 but only if one could translate this scripting to python.

All that said to let you know that I am ok to deal with this more deeply with you, I am ready to share my experience on this and would be very please to have your advice on what's going on.

And by the way, thank you for your work.
 

Hubert, I am indeed interested as heikki (see above, also Gentoo!) has got exactly the same problem. Up to now I have only come just about as far as you in your diagnosis. I have not been working on this because of family obligations during the holidays. Also i must confess that i am quite clueless and have not had a good idea how to tackle this problem. Only information I can add for now is that it cannot be a general OOo3 problem, other linux-distributions with OOo3 do not have this particular problem. Please contact me more directly by email (see imprint/contact on my page). Regards, Knut
 

Also (regarding the "Gentoo-cases"): There is an important distinction between the python runtime that is shipped with OOo and the normal one of the Linux-distribution. The python-macros in the case of heikki (and probably Hubert) run on the "OOo-python", i.e. the modules are loaded from e.g. /usr/lib/openoffice/basis3.0/program/python-core-2.3.4/lib/xml/sax/handler.py, as can be found out with the help of the inspect-command of the python language.
There is even a guide "Replacing the python runtime with your system's python installation" at http://udk.openoffice.org/python/python-bridge.html#replacing. Looks a little outdated but if some enterprising soul would like to try...
 

Thanks Knut, I have noted it. For the moment I am trying to build OOo because I was using the binary package of it on Gentoo. I know this is subversive as regard to the motto of Gentoo but for such app, it is a huge spare of time ;-) I will keep you inform of how well your macro run on this brand new OOo. If it doesn't and 2009 let me some more time before the big start, I will have a look at the differences between OOo-python sax handler and the one on my system.
 

This is a great tool !!!
Are there also plans to support the business functions of GNUCash (in a way invoices can be exported to Ooo) ?

Thanks
 

No, there aren't. Reasons: My own bookkeeping is strictly private and so I lack knowledge and motivation to extend the export of business applications. Second: A project to make possible the storage of GnuCash's output optionally into a database instead of an xml-file seems far advanced to me, see GnuCash's user-list/Phil Longstaff. He once mentioned version 2.4 as possible productive target. This will be a much better and complete solution, so until then my macro is hopefully useful to a lot of people but I will not put much work into it anymore. But thanks for your compliment.
 

Hi, using Ooo for the reporting is a great solution, but unfortunately it doesn't work for me. I always get an SQL exception importing the transactions.

Error! com.sun.star.sdbc.SQLException Value too long in statement [INSERT INTO TRN(ID, NUM, DESCRIPTION, DATE_YM, DATE_D) VALUES(?, ?, ?, ?, ?)]
For further information look into file .openoffice.org2/user/config/GnuCashFilePaths.txt

I really have some long transactions lines, but that should not cause troubles to the import. Either use a longer field or shorten to long texts.

Klaus
 

Hi Klaus,
yes, you are right I could do that, but right now I am unwilling to publish a new release, it seems to be a very rare problem anyhow. You could try an individual solution and edit the program file "gnuc2ooo.py", presumably somewhere under a .openoffice subdirectory of your /home directory if you installed as "My Macro" or under "/usr/lib/openoffice/share/Scripts/python" if you installed as Shared Macro. Just change the length of the "description"-field in statement "create_trn = ..." from 512 to 1024 or whatever.
If this becomes a longer discussion I would prefer if you would send me an email (see imprint/contact resp. Impressum).
Regards
Knut
 

First of all, thank you for a much-needed macro!

I use OpenOffice.org 3.0.1 on Windows XP. I tried to add the "memo" field from the splits, but failed and need some help to fix it. Am I missing something?

* In the create_split statement I inserted "MEMO VARCHAR(512)," (without the quotes) as the third field.

* In the insert_split statement I inserted "MEMO, " as the third field. Also added one more "?, " in the VALUES list.

* In the split0 statement I inserted "'memo', ".

* Further below in the script (around line 480) I inserted the statement:
Stmt_ins_split.setString(3, value_dict['memo'])
and made made corresponding number changes for the statements underneath (5->6, 4->5, 3->4)

When filling the database (fillGnuCashDB) I get the error message:
Errorlogging Fri Feb 20 09:15:23 2009
Error! com.sun.star.sdbc.SQLException Column count does not match in statement [INSERT INTO SPLIT(\n ID, TRN_ID, MEMO, VALUE, QUANTITY, ACCOUNT)\n VALUES (?, ?, ?, ?, ?, ?)]
current routine = insert_statement
last input startElement: split:account
last input endElement: trn:split
last input insert_statement: split {'account': u'e9c9a2667f244d4f073f37a2cb6495e1', 'memo': '', 'value': 5252.0, 'trn_id': u'1bdf94a86b3fa9988d3cfa04ef78c45f', 'id': u'84c7d5ae7409e48e80c0e2c9d75ad572', 'quantity': 5252.0}


I have tried to mimic the handling of the "description" field of a transaction, but obviously I have missed something. To me the column count do seem match. :-/

Kind regards
Magnus
 

Well, Magnus, here is your helping hand - I don't have the time to program and test this in an orderly manner myself as I have to prepare some discussions with the German Internal Revenue Service (sigh!):
I think, even though your changes look good, the new definition of the split-table with 6 colummns is not effective. Please look up the split-table in the .odb database: Usually only the contents gets deleted not the table structure. Try deleting the .odb database and running SetGnuCashFilePaths again.
Please report back here when you are successful as this feature is of concern to others also. On the other hand, if we have a more extended dialogue please resort to emailing me directly, as usual.
If you need the memo-field in ALL_TRANSACTIONS you will still have to adapt the view-definition in the program. But first things first.
Kind regards
Knut
 

Thank you very much! Deleting the tables in the .odb file did the trick (I tried that first because I have some queries and reports defined in the same file). I was not aware of that only the contents of the tables were replaced when refilling the tables. :-)

Kind regards
Magnus
 

Thank you for this helpfull macro. The macro works fine under OO 3.01 in XP. But i like linux and Suse 11.1. The macro SetGnuCashFilePaths brings the errormessage:
Error! TypeError _uno_import() got an unexpectes keyword argument 'fromlist' For further information look into file /home.../GnuCashFilePaths.txt."

and this is the text from this file:
#This file was generated by OpenOfficeMakro: SetGnuCashFilePaths
GnuCashDatafile: ected keyword argument 'fromlist'
current routine = getGCFilename
last input startElement: not available
last input endElement: not available
last input insert_statement: not available

Can you help me?
Thank you
Christian
 

Christian: this is a new problem for me and will require some effort. Please contact me directly by email (see imprint/contact resp.Impressum/Kontakt).
Regards
Knut
 

Christian's problem is a known bug that opensuse's developpers will fix any time soon I hope. In the meantime anybody in need for a temporary fix can send me an email.
Knut
 

Can I create a summary of current (or history) balances of mutual funds & stocks?
 

I am afraid it will not be what you want. As I myself do not possess any stocks or mutual funds, I did not have this on my mind while programming and I doubt that changes in price are taken care of correctly. To explore this you should create the evaluation like demonstrated in my "Example 1" and then change the ACCOUNT-TYPE from "EXPENSE" to "STOCK". Sorry, but this is all I can do for you.
Kind regards
Knut
 

This is a great extension! Worked as advertised. Thanks!
 

Hello. :) I'm getting this as an error when I run the set file paths macro:

Error! TypeError encode() argument 1 must be string, not None

and in your GnuCashFilePaths.txt I get this:

#This file was generated by OpenOfficeMakro: SetGnuCashFilePaths
GnuCashDatafile: e string, not None
current routine = getGCFilename
last input startElement: not available
last input endElement: not available
last input insert_statement: not available

I tried to put the correct path in this file but that also produced an error (no idea what you named the output filename variable).

Thanks in advance for any assistance you can provide.
 

Hi,
well, at first sight it sure looks as if you hadn't provided one of the file-names. But please let's discuss this off the blog.
Could you send me an email (see link imprint/contact)?
Knut
 

Actually I did enter a file name in the first dialog. This is also open office 3.1.0 I'm using, maybe that's the problem? Finally, your profile is not publicly available so I can't seem to find your email address.
 

3.1.0 - That is interesting indeed!
As for my email-adress:
Go to the main page of this blog.
Search for "imprint/contact".
Click on it to follow the link it represents.
I don't want to repeat this email-adress unnecessarily in public as this draws spam which I already get plenty on this address.
Regards
Knut
 

Greetings from England, and Vielen Dank!

Following your instructions, I have been able to create separate spreadsheets for ALL_TRANSACTIONS, and the current year, based on a query. However, when I try to drill down by double-clicking a total, OpenOffice crashes, 'due to an unexpected error'. It looks like a problem with OOO, but I wonder if anyone knows where to start looking for a solution?

I'm running OOO 3.0.1 on Ubuntu 9.04.
 

Hi Krivine,
I noticed that - have got the same system. But I was too lazy to file a bug report. First I hoped it would straighten itself out in further releases and now I think the whole macro will be outdated soon anyway when GnuCash 2.4 is released (see my Top page - "Important notice" resp. www.gnucash.org). So, I didn't feel like putting more work into it. But of course you are free to file a bug report yourself... I don't think it has to do with the macro (and I don't know if it isn't fixed in Ooo 3.1 already).
Regards
Knut
 

Hi Knut

He he, nothing wrong with being lazy! I can live with it as it is.

Regards
Krivine
 

Seems to have worked nicely using NeoOffice 3.0.2 for Mac.