4 Replies Latest reply: May 15, 2014 9:58 AM by Grant Perkins RSS

    newbie can't use preceding string for statement file

    toddchakos _

      Hello--

       

      I'm evaluating Monarch v7 for my company to see if it can pull data out of a bank statement file.  One problem I'm having is that the statement isn't consistent from page to page.  For example:

       

            S71 SHARE A/C - BRONZE CHECKING                         

            -


                                                                                       

      -


      SUMMARY----


          

                                                                      

             Previous Balance as of 05/01/03.......        2578.21  

                                                                      

             Total of 14 Checks for ...............        1998.00 -

             Total of 25 Other Debits for..........        2492.32 -

             Total of 2 Other Credits for .........        3940.48 +

                                                                      

             Ending Balance as of 05/31/03.........        2028.37  

             Average Balance ......................        1494.32  

             -


          

       

      And then a later page might look like this:

       

      S71 SHARE A/C - BRONZE CHECKING                        

      -


                                

      (Joint with JANE DOE)                                                                               

      -


      SUMMARY----


         

                                                               

      Previous Balance as of 05/01/03.......          50.00 

                                                               

      Total of 1 Other Debits for...........           5.00 -

                                                               

      Ending Balance as of 05/31/03.........          45.00 

      Average Balance ......................          49.83 

      -


         

       

      It seems like I should pull this whole block in a single templat, but I'm not seeing how I could get the number of checks since the number is before the word "checks" and it isn't always there anyway.

       

      Thanks in advance.  And I'd like to mention that the interface to Monarch is exceptionally user friendly and intuitive.

      --Todd

        • newbie can't use preceding string for statement file
          Steve Caiels

          Hi Todd,

           

          You can (usually!!) only use a single template if all your sections are the same layout.  So imagine you are cutting holes in paper and laying it over the top of the report.  If that same piece of paper will work with every block of data, then you can use a single template.

           

          So for this report, select a single transaction line as your detail.  For example

           

          Total of 14 Checks for ............... 1998.00 –

           

          Assuming all these lines will always start with Total and it always appears in the same columns, then that should be a perfect trap.

           

          Highlight two fields.  One from the number of transactions to the end of the …. (called description for example), and one covering the amount including the training minus sign and any extra spaces for bigger numbers (as your amount field appears to be left justified)

          [font="courier"]Total of 14 Checks for ............... 1998.00 –

                   ^^^ description field ^^^^^^^ ^ amnt ^^^^^[/font][/quote]Two calculated fields can be used to extract the quantity and transaction type.

           

          For quantity use Val()

          For transaction type use Extract(," ","for")

           

          To get the Previous balance create a separate append template.  To get the account type (S71 Share A/C etc . .) create another separate append.  Select a three line sample if you want the joint info.

           

          Finally, create another template to extract the Ending Balance and Average balance.  It is very important to change the template type to a footer as it is below the detail it relates to.  If you don’t do this, the balances will be attributed to the share account below them. 

           

          I’ve just had a thought . (unusual in itself)  Is it JUST the cheques (sorry checks) you want?  If so, forget all about the detail template and calculated fields.  Select a single line sample using one of the check lines.  Use the word check as a trap, and highlight the qty and amount fields.  This should work fine for the sample, but wont work if you have fewer or more digits in the quantity column.  To get around this, tick the floating trap.  You may need to fiddle around to make sure all your larger quantities are covered.

           

          Cheers

          Steve

          • newbie can't use preceding string for statement file
            Grant Perkins

            Here's a slight variation on Steve's idea ( and I assume that the report output is not exactly as shown in your post. Most notably the line of dots which may need some careful checking with some solutions.)

             

            Try this if you want to assess an alternative solution.

             

            Detail line the same as Steve's suggestion but trap on the word "for". Make the trap line the one where "for" occurs as far right as any likely line will display it. Mark the trap as a floating trap.

             

            Make 2 fields. One to be everything from the LEFT of the word "for" and the other to be everything from the RIGHT of the word. (Your choice as to whether or not to include the spaces either side of "for" but they may have an effect if you do, see later.

             

            Create an append for the header info relating to the account type. Your sample would allow this to be a 3 line selection with details on the 1st line and possible extra information on line 3. If there is no line 3 info for a record i field defined there would simply come up blank. However if there might be other 'info' on that line not always required a slightly more complex solution, probably using a 'Start field after preceding string' solution might be required. (Let us know if that is a problem to look at).

             

            Taking the filed to the LEFT of the trap word you can split it into the number of transactions and the transaction type as follows.

             

            Number of transactions. Make the field NUMERIC.

             

            VAL(Lsplit(,4," ",3))

             

            Transaction Type. Make the field CHARACTER

             

            Lsplit(,4," ",4)

             

            Note in both of the above examples I am using the SPACE  to split the line up but only into 4 sections.  (Note also the SPACE in the ," ", part of the formula. Extra spaces somewhere can lead to unexpected results and are difficult to spot sometimes)

             

            The transaction amounts are a little trickier if we assume that the dots appear in the original. Unequal lengths and number of characters will come into play. The Cut and Paste sample I created exhibited this problem where some lines had a space after the word "for" and some didn't. Probably consistent on the original report - but maybe not, as I learned some years ago!!

             

            Anyway the point was that a 'normal' Lsplit function gave poor results so I looked for something else.

             

            VAL(Extract(,"..... ",))

             

            seems to work OK. The field again needs to be defined as numeric with an appropriate number of decimal places. Again note the SPACE at the end of the row of dots which I use to identify the END of the row of dots rather than the beginning of some point in the middle. Your original report MAY NOT always appear in such a consistent form of course.

             

            An alternative approach would be

             

            VAL(Strip(,".",))/100

             

            This would take all of the dots (periods) out of the data leaving just the number and the sign. As the decimal point would also disappear the division by 100 and formatting to 2 decimal places restores the correct value.

             

            You may need to consider using the TRIM or LTRIM functions if you end up with odd leading or trailing spaces which create problems in any of the calculated fields.

             

            Nice to be able to use a couple of the new V7 functions 'in anger'!

             

            Steve's suggestions for the footer area look complete but if anything unusual crops up please let us know,     smile.gif[/img]    

             

            Good luck.

             

            Grant

             

             

                Originally posted by toddchakos:

            Hello--

             

            I'm evaluating Monarch v7 for my company to see if it can pull data out of a bank statement file.  One problem I'm having is that the statement isn't consistent from page to page.  For example:

             

                  S71 SHARE A/C - BRONZE CHECKING                         

                  -


                                                                                             

            -


            SUMMARY----


                

                                                                            

                   Previous Balance as of 05/01/03.......        2578.21  

                                                                            

                   Total of 14 Checks for ...............        1998.00 -

                   Total of 25 Other Debits for..........        2492.32 -

                   Total of 2 Other Credits for .........        3940.48 +

                                                                            

                   Ending Balance as of 05/31/03.........        2028.37  

                   Average Balance ......................        1494.32  

                   -


                

             

            And then a later page might look like this:

             

            S71 SHARE A/C - BRONZE CHECKING                        

            -


                                      

            (Joint with JANE DOE)                                                                               

            -


            SUMMARY----


               

                                                                     

            Previous Balance as of 05/01/03.......          50.00 

                                                                     

            Total of 1 Other Debits for...........           5.00 -

                                                                     

            Ending Balance as of 05/31/03.........          45.00 

            Average Balance ......................          49.83 

            -


               

             

            It seems like I should pull this whole block in a single templat, but I'm not seeing how I could get the number of checks since the number is before the word "checks" and it isn't always there anyway.

             

            Thanks in advance.  And I'd like to mention that the interface to Monarch is exceptionally user friendly and intuitive.

            --Todd /b[/quote]

             

            [size="1"][ July 15, 2003, 02:28 PM: Message edited by: Grant Perkins ][/size]

            • newbie can't use preceding string for statement file
              toddchakos _

              Thank you both for the feedback--it is much appreciated!  Talk about a lesson in "thinking outside the box".

              • newbie can't use preceding string for statement file
                Grant Perkins

                Originally posted by toddchakos:

                Thank you both for the feedback--it is much appreciated!  Talk about a lesson in "thinking outside the box". /b[/quote]Box?

                 

                What box?

                 

                     

                 

                Grant