[GNC] My experience importing 19 years of Quicken data into GnuCash

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

[GNC] My experience importing 19 years of Quicken data into GnuCash

no_more_quicken
I've been diligently tracking my personal finances since early 2000. I
started with MS Money, then reluctantly switched to Quicken when Money was
discontinued. I LOVED MS Money, but I've never really been happy with
Quicken.  It's hard to pinpoint exaclty why, but some combination of the
clunky UI, subtle bugs in the reporting, upgrades with no new features, etc.
made me lose confidence in the program and made me want to switch. Over the
past 19 years, I've accumulated a large variety of accounts & transactions,
including trades of stocks/commodities/futures, hedge fund investments,
cryptocurrency trades, corporate pension accounts, restricted stock
distributions, IRA accounts, 401k accounts, 529 accounts, home purchases &
sells, auto loans, tax adjustments, cash accounts, etc.

I tried MANY alternatives, including the obvious online ones, but none of
the options supported all of my needs. None of the online solutions could
handle my manual cash accounts, nor maintain the full history from accounts
that have long been closed.  And none of the desktop programs supported all
of the different account/transaction types.

I recently stumbled upon GnuCash, and decided to give it an earnest chance,
partly because the double-entry backend + open source program gave me
confidence that my data would remain safe, exportable & future-proof once it
was converted.  I wrangled with the process for about a week, and finally
got all of my data in, but it was a bumpy road.  Here are some notes from
the process; I am hoping it will help others like me and/or the developers
looking to improve GnuCash.  This was all using GnuCash 3.4 (Build ID: 3.4+
(2018-12-30)) on a Surface Book 2 running Windows 10:

1.  At first, I exported everyting from Quicken into a giant 400k-line QIF
file, and tried to import that into GnuCash.  This took FOREVER (as in, 5+
hours), and eventually failed with an unhelpful message ("import failed", or
something similar). From the log file, I was able to see what appeared to be
the last successfully imported transaction, but no clue as to why the next
transaction didn't import.  Undeterred, I proceeded to export each account
individually from Quicken into ~50 separate QIF files, and imported them one
by one.  I'll note that each import took a VERY long time, which may or may
not be related to  the HiDPI bug
<http://gnucash.1415818.n4.nabble.com/GNC-Saving-in-GnuCash-painfully-slow-what-can-I-do-td4701583.html>
, whose existence and workaround I only discovered after finishing the
import process.  During this arduous process, I experienced failures similar
to the giant import, and each time I had to manaully edit the QIF file to
remove offending transactions to make it work. In the end, there were 5
transactions that had to be removed, and they were all transfer transactions
which were duplicates of transactions which had been imported by an earlier
QIF file.

2.  The HiDPI bug mentioned above
(http://gnucash.1415818.n4.nabble.com/GNC-Saving-in-GnuCash-painfully-slow-what-can-I-do-td4701583.html)
nearly caused me to abandon GnuCash altogether, as saving to XML was taking
several minutes.  Before discovering the workaround, I tried using both the
mysql and sqlite3 backends, but not having a way to undo mistakes was a
dealbreaker.  (However, having the ability to export to SQL is amazing.)
I'll also note that even after changing the HiPDPI scaling to be performed
by "System," the save to XML still takes ~25 seconds for me, which is still
quite slow.  I understand the root cause may be from a third-party library,
but I think somehow addressing this out-of-the-box would make a HUGE
difference to new users.

3.  Having gotten the data into GnuCash, next came the process of cleaning
things up.  Here, not having the ability to multi-select / bulk edit
transactions proved to be quite a handicap.  I found numerous situations in
which I wanted to re-categorize a bunch of stuff, clean up the way my loan
payments had been recorded in the past, etc.  I understand that the best way
would be to get it exactly right at the time of entry/import, but inevitably
people will make mistakes and want to correct them in bulk.  The "delete
account and move" workaround helped a bit, but still proved limited for what
I wanted to do.

4. The online banking setup is quite confusing, mostly because of the UI.
Since most users (myself included) think in terms of their online
username/login, I think simply having a one-step Quicken-style wizard which
hides the UUID details could go a LONG way toward making things less
daunting. Once I figured out the terminology/UI, I had a relatively smooth
experience with some accounts (e.g., American Express), but for Chase I
ended up having to sniff my old Quicken network traffic to grab the UUID to
make it work, and for E*Trade I can get the account list but not download
any transactions.

5. I still haven't quite figured out the reporting framework; my goal is to
generate an "expenses over the last X months grouped by month" report (i.e.,
just like the "Expense Over Time" built-in chart, but in a table format with
all the numbers for every account).  I found  this thread
<http://gnucash.1415818.n4.nabble.com/Monthly-Income-Expense-Reports-td1561239.html>  
from a decade ago but if anyone has any updated tips that would be
appreciated.

Now that I have my data imported and mostly cleaned up, I am hoping I won't
ever have to go back to Quicken.  A big thank you to all the GnuCash
developers for creating and maintaining a great program built on a solid
infrastructure.






--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] My experience importing 19 years of Quicken data into GnuCash

Adrien Monteleone-2

> On Apr 2, 2019, at 11:59 AM, no_more_quicken <[hidden email]> wrote:
>
>
> 5. I still haven't quite figured out the reporting framework; my goal is to
> generate an "expenses over the last X months grouped by month" report (i.e.,
> just like the "Expense Over Time" built-in chart, but in a table format with
> all the numbers for every account).  I found  this thread
> <http://gnucash.1415818.n4.nabble.com/Monthly-Income-Expense-Reports-td1561239.html>  
> from a decade ago but if anyone has any updated tips that would be
> appreciated.

While a Transaction Report might get you the data, I tend to like the Budget Report for its layout.

If you only want to see actual expenses, simply uncheck the “Show Budget” in the Display tab.

You can get each month/period in its own column, optionally have a total column, and everything is listed by account just like a P&L.

I think you have to setup a budget first to define the reporting period to get it to work. I haven’t tried it without one created.

Regards,
Adrien
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] My experience importing 19 years of Quicken data into GnuCash

Cricket Onebit
In reply to this post by no_more_quicken
Thanks for this.

I have a similar project in the near future, and was wondering the best
order for some things. It looks like the major re-categorization should be
done before leaving Quicken.

Good to know there's a workaround for the HiDPI bug.

Is there a bug report / feature request for bulk actions? I'd vote for it
(if I get to the "pay it forward by being active in GnuCash Community" part
of the project).

Can you explain more about not being able to undo things with the SQL
backend vs XML (or point to more details)? I'm undecided about which
backend. Fully-functional and easy to install and maintain is more
important to me than a few seconds while using, unless those seconds add up
quickly. I want to do my accounting, not trouble-shoot the installation.

Thanks,

Cricket / Sandy

--
+++

Not as a ladder from earth to Heaven, not as a witness to any creed,
But simple service simply given to his own kind in their common need.
-- Rudyard Kipling

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] My experience importing 19 years of Quicken data into GnuCash

no_more_quicken
Cricket Onebit wrote
> Can you explain more about not being able to undo things with the SQL
> backend vs XML (or point to more details)? I'm undecided about which
> backend. Fully-functional and easy to install and maintain is more
> important to me than a few seconds while using, unless those seconds add
> up
> quickly. I want to do my accounting, not trouble-shoot the installation.

With the XML backend, any modifications are kept in memory until you
explicitly save the changes.  (You can also setup GnuCash to auto-save at
set intervals.) So if you make a major/destructive goof while editing
transactions, you can always just not save and get back to your last saved
state.  With a SQL backend, modifications are committed immediately to the
database, so there is no way to go back.  That said, converting from one
format to another is trivially easy and doable at any time, so it's not
necessarily a big decision you have to make a priori.




--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] My experience importing 19 years of Quicken data into GnuCash

no_more_quicken
In reply to this post by Adrien Monteleone-2
Adrien Monteleone-2 wrote
>> On Apr 2, 2019, at 11:59 AM, no_more_quicken &lt;

> nospam@

> &gt; wrote:
>>
>>
>> 5. I still haven't quite figured out the reporting framework; my goal is
>> to
>> generate an "expenses over the last X months grouped by month" report
>> (i.e.,
>> just like the "Expense Over Time" built-in chart, but in a table format
>> with
>> all the numbers for every account).  I found  this thread
>> &lt;http://gnucash.1415818.n4.nabble.com/Monthly-Income-Expense-Reports-td1561239.html&gt; 
>> from a decade ago but if anyone has any updated tips that would be
>> appreciated.
>
> While a Transaction Report might get you the data, I tend to like the
> Budget Report for its layout.
>
> If you only want to see actual expenses, simply uncheck the “Show Budget”
> in the Display tab.
>
> You can get each month/period in its own column, optionally have a total
> column, and everything is listed by account just like a P&L.
>
> I think you have to setup a budget first to define the reporting period to
> get it to work. I haven’t tried it without one created.
>
> Regards,
> Adrien

Thanks, Adrien.  This approach is working for me -- I just had to create a
new budget and was able to create the report!




--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] My experience importing 19 years of Quicken data into GnuCash

Cricket Onebit
In reply to this post by no_more_quicken
That makes sense.

I know with some versions of SQL, you can roll-back. I suspect that each of
the three versions GNUCash supports does it differently, sigh. If I do use
SQL, I'll treat it like my novel. Dated daily backups, and add good title
and change log when I make major changes. Not that I ever regret major
changes.

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Tue, 2 Apr 2019 at 15:23, no_more_quicken <[hidden email]> wrote:

> Cricket Onebit wrote
> > Can you explain more about not being able to undo things with the SQL
> > backend vs XML (or point to more details)? I'm undecided about which
> > backend. Fully-functional and easy to install and maintain is more
> > important to me than a few seconds while using, unless those seconds add
> > up
> > quickly. I want to do my accounting, not trouble-shoot the installation.
>
> With the XML backend, any modifications are kept in memory until you
> explicitly save the changes.  (You can also setup GnuCash to auto-save at
> set intervals.) So if you make a major/destructive goof while editing
> transactions, you can always just not save and get back to your last saved
> state.  With a SQL backend, modifications are committed immediately to the
> database, so there is no way to go back.  That said, converting from one
> format to another is trivially easy and doable at any time, so it's not
> necessarily a big decision you have to make a priori.
>
>
>
>
> --
> Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> 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.
>


--
+++

Not as a ladder from earth to Heaven, not as a witness to any creed,
But simple service simply given to his own kind in their common need.
-- Rudyard Kipling
_______________________________________________
gnucash-user mailing list
[hidden email]
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.
GWB
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] My experience importing 19 years of Quicken data into GnuCash

GWB
Haven't tested this with the SQLite GnuCash backend, but it is
sometimes possible with PostgreSQL to "roll back" to an earlier state
before the last transactions, or for that matter, any arbitrary number
of transactions before the last one.  I haven't used the PostgreSQL
backend with GnuCash for some time now, since the SQLite format works
extremely well.  But having multiple formats (xml files and sql
databases) was one reason I moved from various other finance programs
(which shall not be named).  SQL Ledger is a financial server-client
model that allows this.

Unless you are willing to spend some time learning various sql query
and dbase commands and functions, I would stick with SQLite and make
snapshots (if your file system allows it) or frequent backups.  And
there is still nothing wrong with the .xml file format that I can see.
I would not suggest the developers try to incorporate some kind of
multiple "undo" within the GnuCash menu (there is already a "replay"
feature, I think, with the .xml file), unless they have lots of spare
time (also not likely!).  But maybe if someone has already had some
success with "rolling back" the SQLite gnucash file to an earlier
state (using command line sql functions and commands?) they could put
it on the wiki.

Keeping changes in memory until commit ("save") works great on most
machines (i.e., the xml file format).  I worry about core dumps and
crashes on linux (less so FreeBSD) so I like various types of sql
backends, but my guess is that most Windows and Mac users (the
majority of gnucash users?) are only rarely affected by crashes.

Gordon

On Sat, Apr 6, 2019 at 1:20 PM Cricket Onebit
<[hidden email]> wrote:

>
> That makes sense.
>
> I know with some versions of SQL, you can roll-back. I suspect that each of
> the three versions GNUCash supports does it differently, sigh. If I do use
> SQL, I'll treat it like my novel. Dated daily backups, and add good title
> and change log when I make major changes. Not that I ever regret major
> changes.
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
> Virus-free.
> www.avast.com
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
> On Tue, 2 Apr 2019 at 15:23, no_more_quicken <[hidden email]> wrote:
>
> > Cricket Onebit wrote
> > > Can you explain more about not being able to undo things with the SQL
> > > backend vs XML (or point to more details)? I'm undecided about which
> > > backend. Fully-functional and easy to install and maintain is more
> > > important to me than a few seconds while using, unless those seconds add
> > > up
> > > quickly. I want to do my accounting, not trouble-shoot the installation.
> >
> > With the XML backend, any modifications are kept in memory until you
> > explicitly save the changes.  (You can also setup GnuCash to auto-save at
> > set intervals.) So if you make a major/destructive goof while editing
> > transactions, you can always just not save and get back to your last saved
> > state.  With a SQL backend, modifications are committed immediately to the
> > database, so there is no way to go back.  That said, converting from one
> > format to another is trivially easy and doable at any time, so it's not
> > necessarily a big decision you have to make a priori.
> >
> >
> >
> >
> > --
> > Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
> > _______________________________________________
> > gnucash-user mailing list
> > [hidden email]
> > 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.
> >
>
>
> --
> +++
>
> Not as a ladder from earth to Heaven, not as a witness to any creed,
> But simple service simply given to his own kind in their common need.
> -- Rudyard Kipling
> _______________________________________________
> gnucash-user mailing list
> [hidden email]
> 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
[hidden email]
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.
Reply | Threaded
Open this post in threaded view
|

Re: [GNC] My experience importing 19 years of Quicken data into GnuCash

cicko
GWB wrote
> Unless you are willing to spend some time learning various sql query
> and dbase commands and functions, I would stick with SQLite and make
> snapshots (if your file system allows it) or frequent backups.  

I'm also doing this manually with the SQLite backend. I wrote a small Python
script that compacts the database, compresses it using LZMA algorithm, and
creates a copy in the backup folder. There I keep ~50 recent files, manually
copying the last one every month to a long-term backup directory.

While the backup scheme implemented for the XML storage is nice and
convenient, I don't think it would be necessary to try to accommodate all
the different backends in that regard. I can only thank the developers for
making it so simple as to having one file with all the data when using
SQLite backend. That way I can implement any backup scheme I like. On Linux
there are some nice automatic backup options available but I stopped using
that after writing the above script, which I now use on all the platforms
where I use GnuCash.



--
Sent from: http://gnucash.1415818.n4.nabble.com/GnuCash-User-f1415819.html
_______________________________________________
gnucash-user mailing list
[hidden email]
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.