If you use Capital One Spark, and signed up for that Capital One Spark account online, you are facing a serious problem if you also use QuickBooks.
It turns out Capital One Spark DOES NOT CONNECT to QuickBooks via their QBO file. Yes, Capital One Spark creates a QBO file. But that QBO file cannot be read into QuickBooks. This is a documented, known issue. However, you often have to escalate within the Capital One Spark support system before you reach someone who will admit this problem. Not only that, but they want you to read to them verbally your ENTIRE FULL SOCIAL SECURITY NUMBER before they’ll even tell you this non-account-specific information. How is that even remotely secure?
The solution is to go to a physical branch, open up a new account, and then move all your money into that new account. Really. That’s the only solution. They simply are not enabled to allow QBO access from the online-created account.
What I decided, rather than create an entirely new account (either at Capital One Spark or elsewhere), is to write a script which takes in a QBO file and outputs the more generic IIF file. An IIF file always is accepted. There aren’t the same requirements as exist on a QBO file. So once you make an IIF file, you are all set.
An IIF file is simply a text file which is tab delimited. You have a header file and then the data rows. Here’s a sample IIF file template to show what it looks like.
!TRNS TRNSID TRNSTYPE DATE ACCNT NAME AMOUNT MEMO !SPL SPLID TRNSTYPE DATE ACCNT AMOUNT MEMO !ENDTRNS TRNS CHECK 03/11/2020 BANK-CapitalOneSpark Check -14.75 USPS PO Debit Card Purchase~ SPL CHECK 03/11/2020 Administrative 14.75 USPS PO Debit Card Purchase~ ENDTRNS TRNS DEPOSIT 03/02/2020 BANK-CapitalOneSpark Amazon Author Income 7.29 AMAZON.COM DEPOSIT 03/02/2020 Author Income -7.29 AMAZON.COM ENDTRNS
The IIF file really is that simple. You have money going out and money going in. The file knows where things are by their tab position. You don’t need other lines at all. Just that one set of !-begun header information and then the data lines.
So to read in the QBO, you in essence parse the incoming text file to get the data, rearrange it a bit, and then export it in this format.
If you’re interested in seeing my actual code, let me know.
It is just staggering that this is the solution that Capital One Spark forces its clients to take, though. Capital One Spark is a BUSINESS BANK ACCOUNT and it doesn’t work with QuickBooks? Surely this is an issue that should be handled.
Ask with any questions!