"You can't get there from here" - balancing exported split transactions.

skeleton567

Have you ever noticed how many of the topics in this community begin with:

"Thank you for contacting the Quicken Community, though I do apologize that you are experiencing this issue with...".

And then if you keep following the discussions do you notice how many times it is obvious there is no real solution to the problems we are facing? 

Recently when working with my 78 years of historical data that over the past I have entered and accumulated within the Quicken application, I came across yet another issue, this time with the QIF export not handling split transactions correctly. 

I have created a process which reads my Quicken annual archive files and exports each to a QIF file which then is converted to .CSV format and accumulated into a relational database.  First I will add that the QIF file concept is probably the worst concept for handling data I have ever seen in a decades-long IT career.   Due to the horrible design of the QIF export my historical data is represented by some 1,300,000 individual records.  

When querying and analyzing my historical data I noticed that within the exported data for my tracked liability accounts for SOME of the real estate loans that are related to my banking accounts by split transactions, it is not possible to reconstruct the split transaction from the split detail since some data is missing 

Ironically, during this reconstruction process involving history for around ten liabiity accounts I discover there are some 75 split records floating around in the data which do not identify an Account Type and the Account Name, so they are 'orphaned' when in fact they are the 'missing' parts of the splits.  Within the Quicken file itself these seem to somehow be handled, since the liability accounts do recondile to the bank accounts.  

So far I am able to identify that the problems are isolated to two liability accounts which were originated since I began to use QW2014.  My current Quicken file contains all of the historical data related to these liability accounts.

My reserach is indicating that the problem is missing data in the exported QIF files for parts of the split transaction that contain Account Type and Account Name.  When these records are assembled into a single record in the .CSV format these two data elements contain NULL values indicating the incoming file contained no data to be put into the records.   It is possible, however, to relate the 'orphaned' records back to the original splits visually by the transaction date and an assigned 'identity' sequence number which has allowed me to identify the issue.

Never in my IT career did I find a problem that could not be fixed.  But sometimes I just had to admit I screwed something up and would have to go back and redesign the solution, even though it was uncomfortable to do so.