What started off as a quest to evaluate FOSS money management apps ended up revealing an issue with PayPal that plagues even highly funded proprietary money management programs running on that other operating system.
This is both a review and a complaint, which often seems to go hand in hand in the tech world.
I’ve been looking for a financial management app recently. Since I closed a bricks and mortar store back in 2012 — after eight years it became yet another victim of the 2007 recession — I’ve been letting my business and personal bank accounts, along with my PayPal account, sort themselves out separately. Business has improved a bit recently, and the time has come to once again put all of my accounting eggs in the same basket, so to speak.
My plan was supposed to be easy, but you know what they say about well laid plans.
I first installed about five free and open source money management apps on a test machine for a look, weeding out the ones that are too simple to handle both personal banking and the requirements of small businesses. I also took a pass on those that were much too unintuitive to be easily learned. As well, I didn’t bother to check out GnuCash, which I’d used at the old store and even before, because I now want something that requires a little less manual input.
Although the two apps are dissimilar in many ways, below the surface there’s not a dime’s difference between the two and using one is much like using the other. Both offer pretty much the same abilities to keep track of both personal and lightweight home business finances, with documentation that’s not too bad once you get around the fact that their users’ guides assume you’ve already read the developers’ minds — a common problem with open source documentation. Once you get around that, however, the documentation becomes fairly complete and useful. As always, the forums help.
After taking enough time to figure my way around in each program, I configured three bank accounts in each, to include my personal and business accounts, as well as PayPal. Always being a bit paranoid, I left the account numbers in each blank, and instead of identifying each account by the name its institution, I used the headings “Personal,” “Business” and “Online.” I decided to use the beginning of the year as the start date, and duly entered the start of the year balance in each account as the starting balance.
Then it was off to my bank’s website to download my transactions for the year.
The bank offered several formats for downloading and I chose CSV, as I wrongly figured that would be the easiest option. The trouble was that HomeBank didn’t like the CSV files offered and said they were an “unknown or invalid file type,” even though the app is supposed to support the format. This caused me to switch horses in mid gallop and download QFX files instead. Then I crossed my fingers, held my tongue just right and one-by-one imported from both bank accounts into both applications.
That went well, with the ending balance of both accounts in both programs being precisely right, which is pretty much a requirement for a financial program. I was feeling confident. One more download and import, and I could go about the business of finishing configuring whichever program I was going to keep.
Christine’s rule number twelve: Never get confident when halfway through a never done before task on a computer. Reference Murphy’s law for details.
It was time for PayPal, which offers something short of a ton of download options. As QFX wasn’t one of them, I chose QIF and downloaded from the beginning of the year to the present. I imported into HomeBank first, because that was the application that I was beginning to favor. The importation seemed to go smoothly and I was ready to celebrate, until I looked at the ending balance, which was a little less than $400 instead of the more than $1,500 that was actually there.
Hmm… It was time to try KMyMoney to see if it did any better. It didn’t. It returned the exact same result.
A quick comparison of the imported transactions with the transactions listed on PayPal revealed the problem if not the cause. Numerous transactions were completely missing from the QIF file I was importing into the money apps.
The cause was easily found with a single Google, as this has been a problem plaguing business owners trying to sync PayPal accounts with accounting applications for years. It seems that whenever a customer is refunded, PayPal marks that transaction as “incomplete” and fails to include it in the QIF download. This would be a problem even if it just involved total refunds where the refund cancels out the payment, but PayPal also fails to include any part of transactions that include only partial refunds as well. In other words, if a customer places a $100 order through an online store and is issued a $10 refund for a not in stock item, then both the order and the refund are not included in the QIF file, which puts the imported balance sheet $90 out of whack with reality.
Is this any way to run a financial institution that tries to act like a bank?
PayPal’s CSV files, on the other hand, do include every transaction, including transactions that receive a partial or complete refund. So I dutifully downloaded the CSV file and went to import it into HomeBank. Just as it did with the CSV files from the bank, the app refused to recognize it as a known file type.
KMyMoney, on the other hand, was ready to import the file, but when it came to telling the program what column was what, it crashed every time. I quickly came to the conclusion that this was likely caused by the fact that PayPal includes 20 or so columns of unnecessary information in the CSV download, so I opened the file in LibreOffice Calc and deleted all but the columns that would be needed by KMyMoney.
After that, the app let me go through the process of identifying Column One as the date, Column Two as the Payee and so forth without crashing, so I was hopeful that the import would work this time. Guess again. During the import process error messages flew left and right, and when the import was complete, KMyMoney was showing that I had a whopping $20,000 plus in the account, which I assure you isn’t anywhere near the case. I haven’t had that kind of money in any account at any time in my life.
As I said, I’m not the only one. Forums for both KMyMoney and HomeBank, as well as the forums for some proprietary money apps and PayPal’s forums, are filled with business owners tying to deal with the issues of PayPal’s QIF downloads being incomplete and its CSV downloads being impossible to import.
The way I see it, there are two ways for this issue to be resolved. The one that makes most sense is for PayPal to fix it by offering QIF, or better yet QFX, downloads that are complete with no transactions left out — as it should have done long ago.
Since it looks as if that’s not going to happen, maybe KMyMoney, HomeBank or another financial app will offer the import choice “CSV preconfigured for PayPal.” That would guarantee a large user base, as even the proprietary financial management programs experience this issue.
Meanwhile, since HomeBank doesn’t seem to recognize any CSV file as valid, I’m sticking with KMyMoney. And it looks like I’ll be spending time during the next week manually adding the transactions that disappear when importing as a QIF.
For the amount of money I have, it hardly seems worth it.