Skrooge, Revisited, Part 2: Importing Other File Formats

by David Pardue (kalwisti)

In my previous article, I offered some tips for using Skrooge more efficiently. After getting started with the program by manually entering two months' worth of my most recent transactions, I became more adventurous and tried importing all of my checkbook data -- from June 2019 to the present -- into Skrooge. (I do my bookkeeping redundantly with GnuCash, HomeBank as well as a LibreOffice Calc checkbook template).

One of Skrooge's strengths is its impressive import / export capabilities. A list of supported file formats is available at https://skrooge.org/imports.

Since both .xhb and .gnc (.gnucash) formats are supported, I imported my HomeBank and GnuCash data files -- approximately 1600 transactions. As another part of the evaluation, I imported two small test files created with Grisbi (.gsb) and Money Manager EX (.mmb). Each of those files contains approximately 75 transactions.

Later, I experimented with downloading recent transactions from my online banking website in QFX and CSV format, then importing them into Skrooge. I wanted to learn if bank downloads would streamline data entry.

I will summarize my results below, in case you are considering trying this yourself. I hope it will save you time, keystrokes and fruitless experimentation.

To import a file into Skrooge:

• From the File menu > choose New or,
• Click on the "New" icon in the toolbar or,
• Type <Ctrl + N>

• Then, from the File menu > select Import > Import...

Importing a HomeBank .xhb File

Note: I created a simple, standardized table to display the results of each import at a glance. This will make it easier to compare Skrooge's handling of different file types.

The data elements which were successfully imported include the Date (and Date format), Payees, Categories (except for the categories involved in Split transactions), Amount and the Comments.

Check numbers transferred but for some unknown reason, they appear at the tail end of the Comment field rather than in the Number field. I consider this to be a minor annoyance.

Another problem was that the Mode field only showed "Check" or "Other" (which lumps together Debit Card purchases and Deposits); a moderate amount of data cleanup would be needed to fix this.

The major issue was that Split transactions were not preserved. Approximately 10% of my transactions have split categories, so I was reluctant to edit that many entries manually.

The screenshots below show the results of the .xhb file import (including the non-preserved split transaction for $89.75):

In the original HomeBank file, the $89.75 payment above was split between three categories: Bills:On-line/Internet Service [69.99]; Postal [15.50]; Hobbies/Leisure:Online Subscriptions [4.26].

Importing a HomeBank .qif File

As a variation, I exported my full HomeBank file in .qif format, then imported the QIF file into Skrooge. This approach yielded much better results than the native .xhb file import.

There are two problem areas: the Mode data element is missing, which is not a deal breaker, in my opinion. Check numbers are in the correct field, so it is clearly apparent which transactions were paid by check. Debit card transactions -- the majority of our purchases -- appear with a negative dollar amount. (We occasionally withdraw cash from the bank, but in those instances, I enter the payee as "cash withdrawal").

The screenshots below show the results of the HomeBank .qif file import (including a preserved Split transaction for $110.74):

Another glitch was an incorrect initial balance after my file import. I was alarmed that my checking account was overdrawn by seven thousand (!) dollars. However, closer scrutiny revealed that in the Accounts section of Skrooge > Initial balance was $0.00. After changing Skrooge's Initial balance to match the Start balance of my HomeBank file, the discrepancy was corrected. To find your initial account balance in HomeBank, go to Manage > Accounts. Next, under the General tab, look for Start balance.

Importing a GnuCash File

The GnuCash file import (.gnucash) was the best of the lot; all checkbook data was imported cleanly and accurately. If you have been keeping your books with GnuCash, this is the preferred route to take.

Data elements which were successfully imported include the Date (and Date format), Payees, Mode (all variations were preserved), Comments, Number, Amount and Categories. I was impressed that all of my numerous Split transactions were accurately transferred.

The screenshots below illustrate the results of the GnuCash data import:

In the screenshot above, you can see that the Split transaction was successfully preserved from my GnuCash source file. In Skrooge's Operations tab, the highlighted triple-arrow icon in the entry line indicates that it is a Split transaction.

Importing a Grisbi .gsb File

The version of Grisbi in the PCLinuxOS repositories was recently updated to 2.0.5. (Thank you, Texstar!). While creating a test file with 75 transactions, I found that Grisbi is user-friendly and has a fairly intuitive interface, reminiscent of Quicken.

The Grisbi data file was imported with excellent results. Based on my tests, this ties for second place (with HomeBank's QIF file).

Data elements which were successfully imported include the Date (and Date format), Payees, Amount, Mode, Comments and Number field. Split transactions were accurately transferred.

One possible caveat involving Categories is that Grisbi's are a bit Francocentric; you might wish to edit them, in order to have them better mesh with Skrooge's default categories. For example,

• In Grisbi, the category Housing [French 'Logement'] includes the subcategories Electricity, Gas, Phone, Water.


• In Skrooge, those subcategories can either be placed under the parent category Bills, or the parent category Utilities.


• For educational expenses, Grisbi has the category Studies, with the subcategories Books, Lessons, School Fees.


• Skrooge has the default category Education, which includes the subcategories Books, Fees, Tuition.

The screenshots below illustrate how the Grisbi data was imported (including a correctly preserved Split transaction):

Importing a Money Manager EX .mmb File

I created a test file with the current version of Money Manager EX (1.5.12) in Windows 10. It contained 75 transactions (identical data to the Grisbi test file).

This was my first experience with MMEX; it has some nice features, yet is not intimidating to set up or use. The program also has a very active discussion forum. However, the .mmb file import was flawed in Skrooge. There were numerous errors in Category assignments which would require a lot of time and effort to fix.

First Import Attempt

My first MMEX import attempt failed because a currency unit -- the Azerbaijani manat -- was not found in the Skrooge database:

Second Import Attempt

I opened MMEX and deleted all the foreign currencies. (There are approximately 80 and they must be deleted individually, so it took a while). I retained USD ($) as the sole currency. Note: MMEX continues to function well without those extra currencies.

The single-currency .mmb file imported successfully. On the surface, the results looked promising but this was deceptive. The data elements Date (and Date format), Payees, Amount, Comments and Number were successfully imported. Split transactions were preserved correctly.

A somewhat minor problem was that the Mode field was empty. (This did not surprise me because in MMEX, transaction types can only be classified as a Withdrawal or a Deposit. There is no way to indicate that an entry is a Direct Debit or a Check payment [although there is a Number field for recording check numbers]).

I noticed that some Categories were missing. Upon closer examination, I uncovered a major problem: 57% of transactions had incorrectly assigned categories after being imported into Skrooge. (I should mention that the categories are correct within MMEX).

The screenshots below show the most egregious mistakes:

• (4) transactions for car fuel were categorized as Other Income (although they were correctly logged as debit amounts).


• (12) grocery purchases were classified as Insurance.


• (2) water bill payments were categorized as Healthcare.

I don't know whether this glitch -- a showstopper, in my opinion -- is due to altered associations within MMEX's SQLite internal database, or whether Skrooge's .mmb import rules have a bug.

Importing (Downloaded) Bank Files

Another area I explored was importing files of transactions downloaded from my bank, to determine if it might save data entry time. My bank currently offers free exports/downloads in Quicken (QFX) format and as CSV (Comma-Separated Values) files. So I tried both options.

While experimenting, I kept this basic tenet in mind: Automation's goal is to save time and reduce the chance of miskeyed data. However, if you waste more time trying to automate something than it takes to just do it, then you should assess whether you are doing the right thing.

I was reminded of a tidbit of "programmer humor" from Reddit: "Never spend 6 minutes doing something by hand when you can spend 6 hours failing to automate it."

Importing a .qfx File

The Quicken file format is proprietary; it is owned and maintained by Intuit. QFX (Quicken Financial Exchange) format is the proposed replacement for the older QIF (Quicken Interchange Format). QFX is more robust and easier to work with than QIF, so Skrooge's developers recommend using it if possible.

QIF format has the drawback of not specifying the currency used; it also lacks a defined date format.

Data elements which were successfully imported include the Mode, (Check) Number and Amount. Although the Date appears, it is the date of bank clearance rather than the actual check date, or date of the direct debit transaction.

As expected, Categories are missing; banks rarely include categorization in exported data files. Comments are present but not particularly helpful. They lack the descriptive details I supply with manually entered transactions.

A more serious issue is that Payees do not always appear. For example, in the screenshot below, there is no Payee given for check number 7100 (to my life insurance company) or 7102 (the local public utility company). I concluded that it would be as time-consuming to clean up the QFX import as it is to manually enter transactions.

The screenshots below illustrate how the transactions were imported:

Importing a .csv File

Unfortunately, CSV (Comma-Separated Values) lacks a strictly defined format; banks can format their .csv files as they wish, which creates a great deal of inconsistency.

If the rows and columns in the CSV file are not arranged "just so," the Payees, Amounts etc. will not map to the required field. This is what happened with my first import attempt, using the bank's default (unedited) .csv file, even though Skrooge allows for flexible processing of these files.

To Skrooge's credit, the program generated a specific error message when the import failed: "Columns 'Date' and 'Amount' not found. Set import parameters in Settings. Settings > Configure Skrooge > Import/Export > CSV > Edit regular expressions..."

This gave me a clue as to the problem. It is possible to manually define how the CSV file is processed but I could not figure out how to map the columns to enable a successful import.

Skrooge's import rules require that a column named Amount be present, so my file's separate Withdrawals and Deposits produced a garbage import -- even after deselecting the Automatic search feature and mapping the column positions manually:

As illustrated in the screenshots below, the only data fields that imported were Date and Category. The Amount field (presumably auto-generated by Skrooge) is $0.00 because it does not exist in the original CSV file. The Withdrawals and Deposits columns in the .csv were ignored:

Nevertheless, I discovered that with pre-import editing (as detailed in the subsection below), the bank's .csv file could be successfully imported into Skrooge.

Pre-Import Editing Procedure

The bank's default exported CSV file was very "lossy" compared to QFX and contains six columns of data:

Date | Description | Withdrawals | Deposits | Category (auto-assigned by the bank) | Balance

The Description combines various data elements, such as Mode, Payee and (Check) Number. I could not devise a strategy for how to easily separate the individual elements in this field.

This procedure merges the Withdrawals and Deposits into a single column (which I later rename Amount) and deletes the superfluous Balance column:

1. Open the .csv file in LibreOffice Calc.

2. In the Deposits column, 'Find and Replace' the dollar sign [$] with a plus sign [+].

3. In the Withdrawals column, 'Find and Replace' the dollar sign [$] with a minus sign [-].

4. Merge the Withdrawals and the Deposits columns.

5. Follow the "merge columns" procedure as outlined on the Ask LibreOffice website.

6. Delete the Withdrawals column.

7. Change the name of the Deposits column to "Amount."

8. Finally, delete the Balance column.

The modified .csv file looks like this prior to the Skrooge import:

The results of the CSV import were disappointing, considering how much effort I made to prepare the file. Data elements which were successfully imported include the Amount, Date and Categories. However, the Date is the date of bank clearance, and the Categories were assigned by the bank's software, so they require editing to sync with Skrooge's categories.

These data elements were missing: (Check) Number, Mode, Payee and Comments. All these fields would have to be entered manually.

Since the CSV needs even more cleanup than the QFX file, I decided that in my usage case, manually inputting checkbook data is quicker and cleaner.

The screenshots below illustrate how the modified CSV file was imported:

Conclusion

If you are already a GnuCash user who is interested in trying Skrooge but retaining all your historical data, the .gnucash (.gnc) file format was the clear winner. Everything imported cleanly and accurately.

Based on my experience, the runner-up was a tie between HomeBank's exported .qif format and Grisbi (.gsb). Their files do not appear to need much work to successfully migrate your checkbook data. HomeBank's .qif import just needed the Starting Balance adjusted to correct the discrepancy and/or would need the Mode field added manually (if you regard it as essential). Grisbi's import might need its categories tweaked (depending on how much consistency you want in your recordkeeping).

Third place went to HomeBank's .xhb format. If you do not have a lot of Split transactions to clean up / adjust, this might be a viable option for you.

The most problematic imported test file was Money Manager EX's .mmb format. I believe the high percentage of incorrectly assigned categories would make the required data cleanup unfeasible.

If you wish to eliminate the need for manually entering transactions via a bank download, QFX is the preferred and more robust method. In my case, however, I found it simpler to continue with manual data entry.

In my testing, by far the most labor-intensive method is importing CSV files. Although the process is cumbersome with my bank's current .csv format, I will add the cliché that "Your mileage may vary." If your bank's CSV files have a more granular format than mine, tinkering with them might yield better results.