3 Replies Latest reply: May 15, 2014 9:58 AM by Steve_P _ RSS

    quicken

    Steve_P _

      Hi All - I'm guessing this has probably been discussed before, but I couldn't find anything in the knowledge base. 

       

      I am downloading a file with a detail of transactions from my credit union, in what they are calling a quicken format.  Okay, so I admit, I am too cheap to just purchase quicken (it's legal for me to just use monarch to grab the data and drop it into a spreadsheet, right?!).

       

      Here is what the data looks like:

      [font="courier"] 

      !Type:Bank            

      D11/08/2005                                   

      T-20.00                                       

      PSAMSCLUB #6333 BANGOR ME                     

      MWithdrawal Debit Card                        

      ^                                             

      D11/04/2005                                   

      T-20.00                                       

      PFAR REACHING MIN                             

      MWithdrawal ACH                               

      ^                                             

      D11/03/2005                                   

      T-204.00                                      

      N02119                                        

      PUnknown Payee                                

      MWithdrawal by Draft

      ^

      D10/31/2005                                   

      T-10.57                                       

      PPOS SUBWAY #587 570 STILLWATER AVE BANGOR    

      MWithdrawal Point of Sale #061273             

      ^                                             

      D10/28/2005                                   

      T-27.40                                       

      PPOS SHAW'S MARKET 48 353 MAIN ST BANGOR ME   

      MWithdrawal Point of Sale #634300             

      ^                                              /font[/quote]So, it seems pretty easy.  Each line has an identifier (date, transaction amount, party, etc.), and there is a ^ character after each transaction. 

       

      I am using the ^ as my detail line, and setting each of the other lines above it as an append template.  Am I going in the right direction? 

       

      The problem I am having is this:  not every line has the "N" field (check number).  I set up a template for this field, but it is 'appending' this check number to all subsequent fields, until it comes across a transaction with a new check number. 

      "That's what it's supposed to do" you are saying...  smile.gif[/img] 

      Is there a way to work around it filling in subsequent fields?  Maybe I am doing this wrong.

       

      Any help would be greatly appreciated.  Thanks!

        • quicken
          Nigel Winton

          Steve

          I would start by trapping on the D before the date and using the Advanced Tab, End Field on None of the above. This will trap the entire block and stop it on the ^ character. Then comes the fun.

          Using fullfield as the name of the big block you should be able to use these formula to parse it out.

           

          ctod(substr(fullfield,2,instr("T-",fullfield)-2)) will give you the date.

           

          substr(fullfield,instr("T-",fullfield),100) will give you a field I have named secondstring. This is the part of the main one from the T- onwards.

           

          val(substr(,3,instr(" ",[secondstring])-1)) will give you the amount.

           

          trim(substr(,instr(" ",[secondstring]),100)) will give you the remainder of the block whic is he description. You could go on and parse this further if you wish.

           

          See if this works on your data.

           

          Nigel

          • quicken
            Steve_P _

            Cool - I will give this a try.  Thanks for your help, Nigel! 

            • quicken
              Steve_P _

              Nigel - I was able to use a mix of your solution plus my original model to grab all of the information I needed.  Thanks so much for sharing your ideas!