Spreadsheet replacement for budgetting purposes

A few years ago I posted a similar topic on the old forums, see Spreadsheet replacement for budget ? • KDE Community Forums

Basically I use an OpenOffice spreadsheet (with numerous sheets within) for budgetting purposes. I asked if KMM can do that type of processing. “ipwizard” replied …

KMyMoney budgeting feature will probably not help here as the detail is not as fine grained as you want it. I would go and create scheduled transaction for all those income and expense transactions. Then you simply increase the preview period in the ledger (see KMyMoney manual for the exact description) and should see the information you want at a glance.

and “aperali” replied …

Or you could use crystal reports with a connection to your kmm mysql db

So, as this actually 5 years ago, and as I have still use/need of the spreadsheet as a secondary means of obtaining budgeting, cash flows, etc, are there now any easier methods , so that I can replace the OOffice spreadsheet ?

I’m very familiar with db connections, code, various programming, so can do that. As I use KMM daily and want to ensure data is not corrupted in any form by any sort of add-on’s, what is the best approach, with that in mind ?

  • Develop a KMM plugin ?
  • Export the KMM data via a ‘save as’ to XML/SQL, then code to produce reports ?
  • Investigate the use of Crystal reports ?
  • Add all the extra scheduled trans and see if that fits ?
  • Other ??
1 Like

Not a full response, but some thoughts and ideas.
First, a .kmy file is just a gzipped .xml file, so there is no need to explicitly save as xml (unless your main storage is sql.) I have a few scripts written in Perl with LibXML from CPAN, so that approach is certainly possible.
If you want to access your sql database directly, that would also work. To ensure you don’t corrupt your data, you can use read-only access, or just be sure your code (stand-alone or plugin) does not write anything to the data.
What I would suggest is to start by writing your tool in pseudo-code - just to force you to think through possible variations and to be sure you really know what data you need for the reports you want. Once that is done, you can better decide how easy/hard it will be to implement in each of the possible approaches.
It does appear that Crystal Reports is not available for Linux, but you can easily search for possible alternatives.
[edit because I saved too soon…]

1 Like

First, a .kmy file is just a gzipped .xml file, so there is no need to explicitly save as xml (unless your main storage is sql.) I have a few scripts written in Perl with LibXML from CPAN, so that approach is certainly possible.[/quote]

Yes, I have previously unzipped a .KMY file and see it is XML; only 10 Mb uncompressed. I did take a quick look at the Perl library, however came across some Python code, only 9 lines and it was able to open the XML and dump the contents to o/p.

If you want to access your sql database directly, that would also work. To ensure you don’t corrupt your data, you can use read-only access, or just be sure your code (stand-alone or plugin) does not write anything to the data.

I will probably stay with XML, so that any additonal data can be stored sperately from the KMY data, and yes, definitely only open the KMY file in read mode. May even just uncompress the latest backup and open that in read mode. There are probably resources within KMyMoney about the XML format and where values are, or just as easy to search and use those values.

What I would suggest is to start by writing your tool in pseudo-code - just to force you to think through possible variations and to be sure you really know what data you need for the reports you want. Once that is done, you can better decide how easy/hard it will be to implement in each of the possible approaches.

Thanks, yes the pseudo-code is a good idea. Having used the spreadsheet approach for quite a few years works well, but it constantly needs rows shifted to cater for periods. To do all that with some code will eventually save time.

It does appear that Crystal Reports is not available for Linux, but you can easily search for possible alternatives.

I did look at Crystal and it seemed quite expensive. I like the output of a spreadsheet but the report doesn’t really have to be a spreadsheet, just a GUI with rows and columns.

Thanks for your help @ostroffjh

I just opened the KMyMoney XML file, hoping that I can easily obtain some account balances. However to my dismay, there is nothing in the <ACCOUNT … data to indicate a balance. Seems I will have to parse through the whole file to get all the account balances by totalling up the <TRANSACTION … records.

Oh well. :slight_smile:

That is correct. Balances are always computed on the fly and not stored. Storing them might save some calculation time, but would lead to the possibility of inconsistent data if the stored sum ever managed to not match the actual sum of transactions.

1 Like

Thanks @ostroffjh . Do you know of any KMyMoney plugins, scripts, etc, either python or Perl that are written to calculate the balances please ? I’m just not wanting to reinvent the wheel.

The Python script at GitHub - JuanMiste/ENBD-to-QIF-converter-for-KMyMoney: Script that converts Emirates NBD bank statements (the XML based Excel file (1997-2004)) to QIF format ready for import by KMyMoney, including a simple recurring transaction classification function does do XML file processing, so that may be a start.

Helpful documentation on the structure of the XML file - Structure of a KMyMoney XML File – Altynbek Isabekov

Uhm I just hope you use Libreoffice by now :smiley:

Primarily I use KMyMoney for all my finances. I only use LibreOffice/Calc for the small budgetting purposes.

I have not written any plugin myself, and don’t know of anything in a
scripting language. An actual plugin for KMM would be written in C++,
using Qt and KDE constructs. My guess is that a good way to start is
by looking at the Plugins section of the Configure KMyMoney dialog,
pick one that you think might have at least some of the pieces you
need, and look at the code. For a script going directly against the
data (whether SQL or XML) you just need to iterate through the
transactions for the given account/category, and keep a running total
of the amount of each transaction. You might want to create a test
dataset with a relatively small amount of data, so tracing through any
routine takes a reasonable effort. Also, the script getsplitpart.pl in
the contrib directory of the source shows one way of dealing with an
XML file, without using the XML structure at all.

1 Like

Thanks @ostroffjh for your help. I’ll take a look at that Perl script.