Import Skrooge export?

[Matthias Beyer]

Hi,

maybe someone here had the same problem: I'm using Skrooge[0] right now and I
want to convert my data to beancount files.

I tried exporting CSV and writing a Ruby script to convert the data, but I
failed (three times by now). I have 6k lines worth of CSV output, about 5 years
of historical data I'd like to keep.

Has someone ever imported Skrooge data into beancount before? I asked on IRC,
though as there are only a handful of people I post this question here on the
ML.

[0]: https://skrooge.org/

--
Mit freundlichen Grüßen,
Kind regards,
Matthias Beyer

Proudly sent with mutt.
Happily signed with gnupg.

[Martin Blais]

Hi Matthias,

Which problems are you encountering?

Can you be very specific?

What's the data format you're exporting from?

Can you share a sample? (Best to change numbers, account names and other PII)

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/20160602161613.GN26461%40yuu.
For more options, visit https://groups.google.com/d/optout.

[Martin Blais]

I just installed skrooge and tried an export.

It looks like single-entry accounting in a CSV file.

Should be straightforward to convert.

Let me know what doesn't work and I'm sure I could help.

[Matthias Beyer]

On 04-06-2016 16:01:38, Martin Blais wrote:
> I just installed skrooge and tried an export.
> It looks like single-entry accounting in a CSV file.
> Should be straightforward to convert.
> Let me know what doesn't work and I'm sure I could help.
>

Wow, thanks for the support and sorry I didn't answer in time, I'm currently
suffering from a cold.

So here's some sample data, including the CSV header (see attachement).

Note: as said, I track my money really precisely, so, as you can see from the
sample data, I track every drink I have and so on.

The first four lines are the "open" lines. "Konto4" is my Cash in this case
here.

I tried to script the import several times, grouping by date and account, but I
failed most of the time because of several points.
One I can think of right now is: When doing a transaction from one account to
another, this is done with two transactions in skrooge (you can type the things
one time, but it will create two transactions): You can see this in line 5 and 6
(with comments "Moved"). You can also easily see that lines 7-9 do not balance
properly. I don't know why this is.

Other problems were non-balancing transactions.

I tried to convert like this:

1. Group the transactions from skrooge by
1. Date
2. Account
2. For each transaction
1. generate the account name ( {Income, Expenses}:<category, transformed to colon-syntax of beancount>)
2. Make accounts unique
3. open the accounts
4. generate the transaction from {Income, Expenses}:<category> to <account>

I ended up with the problems listed above.

[Martin Blais]

On Sun, Jun 5, 2016 at 10:55 AM, Matthias Beyer <ma...@beyermatthias.de> wrote:

On 04-06-2016 16:01:38, Martin Blais wrote:
> I just installed skrooge and tried an export.
> It looks like single-entry accounting in a CSV file.
> Should be straightforward to convert.
> Let me know what doesn't work and I'm sure I could help.
>

Wow, thanks for the support and sorry I didn't answer in time, I'm currently
suffering from a cold.

So here's some sample data, including the CSV header (see attachement).

Note: as said, I track my money really precisely, so, as you can see from the
sample data, I track every drink I have and so on.

The first four lines are the "open" lines. "Konto4" is my Cash in this case
here.

I tried to script the import several times, grouping by date and account, but I
failed most of the time because of several points.

Here are some observations on the data you attached:

- It looks to me like the "id" field may be a unique key for each line (posting) and like the "idtransaction" field refers to those. If this is true, this is how you should group your postings: by unique value of "idtransaction".

- If this is right, the field you provided is a subset of the entire thing. If not, then it's probably a single-entry system and you have to figure out how to group the postings some other way. 

Q. Does the "number" field ever have a non-zero value?

Q. Do you have any idea what the "idgroup" field may be for?

One I can think of right now is: When doing a transaction from one account to
another, this is done with two transactions in skrooge (you can type the things
one time, but it will create two transactions): You can see this in line 5 and 6
(with comments "Moved"). You can also easily see that lines 7-9 do not balance
properly. I don't know why this is.

Hmm. It isn't clear indeed.

The best way to debug this IMO would be to manually create a sample input file and see how the export translates those.

Is there another export format?

 

 

Other problems were non-balancing transactions.

I tried to convert like this:

1. Group the transactions from skrooge by
    1. Date
    2. Account
2. For each transaction
    1. generate the account name ( {Income, Expenses}:<category, transformed to colon-syntax of beancount>)
    2. Make accounts unique
    3. open the accounts
    4. generate the transaction from {Income, Expenses}:<category> to <account>

I ended up with the problems listed above.

--
Mit freundlichen Grüßen,
Kind regards,
Matthias Beyer

Proudly sent with mutt.
Happily signed with gnupg.

--
You received this message because you are subscribed to the Google Groups "Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email to beancount+...@googlegroups.com.
To post to this group, send email to bean...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/20160605145512.GA12724%40lucy.

[Martin Blais]

Looks like many other formats are available, I'd try a few to find the less lossy one. Likely CSV is not great (not hierarchical):

Usage: skroogeconvert [Qt-options] [KDE-options] [options] 

A conversion tool for financial files (KMyMoney, GnuCash, Skrooge, ...)

Generic options:

  --help                    Show help about options

  --help-qt                 Show Qt specific options

  --help-kde                Show KDE specific options

  --help-all                Show all options

  --author                  Show author information

  -v, --version             Show version information

  --license                 Show license information

  --                        End of options

Options:

  --in <file>               Input file

  --out <file>              Output file

  --param <name>            Name of a parameter

  --value <value>           Value of a parameter

Input supported formats:

*.afb120 *.cfo *.csv *.uncompressed *.gnucash *.gnc *.gsb *.xhb *.kmy *.mt940 *.mny *.mmb *.ofx *.qfx *.qif *.skg *.sqlite|All supported formats

*.afb120 *.cfo|AFB120 file (cfomb)

*.csv|CSV file

*.uncompressed *.gnucash *.gnc|GnuCash document

*.gsb|Grisbi file

*.xhb|Homebank document

*.kmy|KMyMoney document

*.mt940|MT940 file

*.mny|Microsoft Money document

*.mmb|Money Manager Ex document

*.ofx *.qfx|OFX file

*.qif|QIF file

*.skg|Skrooge document

*.sqlite|Sqlite document

Output supported formats:

*.csv *.json *.kmy *.qif *.skg *.sqlite *.xml|All supported formats

*.csv|CSV file

*.json|JSON file

*.kmy|KMyMoney document

*.qif|QIF file

*.skg|Skrooge document

*.sqlite|Sqlite document

*.xml|XML file

[Martin Blais]

Tried a bunch of them.

Wow. This thing is a beast. It creates a lot of unwanted stuff by default.

The SQLite export is what I wanted most, but it fails.

The XML and JSON exports show a lot of SQL commands.

Frankly, the QIF export is the most sane of the available formats.

If I were you, I'd convert from that.

QIF is simple and well documented:

https://en.wikipedia.org/wiki/Quicken_Interchange_Format

[Matthias Beyer]

On 05-06-2016 23:59:20, Martin Blais wrote:
> Tried a bunch of them.
> Wow. This thing is a beast. It creates a lot of unwanted stuff by default.

Yep, quite a monster (another reason I want to switch).

> The SQLite export is what I wanted most, but it fails.

I have an sqlite export of my data, though I cannot share it because of privacy
reasons, I guess you can understand that.

> Frankly, the QIF export is the most sane of the available formats.
> If I were you, I'd convert from that.
> QIF is simple and well documented:
> https://en.wikipedia.org/wiki/Quicken_Interchange_Format

I'll try this one, yes.
Thanks for your help so far, I might get back to that problem next weekend, I'll
post into this mailthread if I have questions! Thanks!

[Martin Blais]

On Mon, Jun 6, 2016 at 11:57 AM, Matthias Beyer <ma...@beyermatthias.de> wrote:

On 05-06-2016 23:59:20, Martin Blais wrote:
> Tried a bunch of them.
> Wow. This thing is a beast. It creates a lot of unwanted stuff by default.

Yep, quite a monster (another reason I want to switch).

> The SQLite export is what I wanted most, but it fails.

I have an sqlite export of my data, though I cannot share it because of privacy
reasons, I guess you can understand that.

Yes of course. What I meant is that I wanted for the export to SQLite of my small test data to work, because it's probably the best way to learn about their schema.

> Frankly, the QIF export is the most sane of the available formats.
> If I were you, I'd convert from that.
> QIF is simple and well documented:
> https://en.wikipedia.org/wiki/Quicken_Interchange_Format

I'll try this one, yes.
Thanks for your help so far, I might get back to that problem next weekend, I'll
post into this mailthread if I have questions! Thanks!

Another thing that would be useful would be for you to create a brand new file with a single transaction of each unique "shape" that you have in your actual input file.

[Matthias Beyer]

Hi list,

sorry to re-start this conversation, but I still fail to import my
skrooge export.

I'm willing to share a huge data dump (lets say ~1000 lines CSV of my
~6,5k lines) of my personal finance data if someone can help me
develop a Script (I use ruby as I know it best) to convert the data to
valid beancount data.

With my approaches I get everything from 15kLOC to 109kLOC error
output from bean-check and messed up data as well - clearly too much
to fix up by hand.

If someone is willing to help me I'd share more information how my
data is structured. As skrooge is a beast:

On 07-06-2016 23:03:58, Martin Blais wrote:
> > On 05-06-2016 23:59:20, Martin Blais wrote:
> > > Wow. This thing is a beast. It creates a lot of unwanted stuff
> > > by default.

converting to valid beancount input is a non-trivial job. (just as a
warning)

I hope someone can help.