8 Replies Latest reply: Dec 3, 2015 12:49 PM by Grant Perkins RSS

    newbie here; need to import brokerage statements

    Patrick DeLangis

      Hi all,

       

      I've used Monarch about 10 times over the past 5 years to create databases from static documents for me to analyze in Excel or Access. I now have about 500 brokerage account statements that I need to database all transactions occurring within the account as well as the account summary so that I can verify all the dollar amounts from each transaction were OCRd correctly.

       

      Does anyone already have a template for importing brokerage account statements that I can start with so I don't have to start from scratch?

       

      (Being that this matter is in litigation, I don't have any other options to obtain the data - like directly from the brokerage house)

       

      Thanks,

      Patrick

        • Re: newbie here; need to import brokerage statements
          Olly Bond

          Hello Patrick, and welcome

           

          No need to worry - if you've an urgent model-building challenge we can turn it round in 24 hours. We've worked with PDF files for a litigation case a little while ago, for a US firm, although these weren't OCR'd. I presume you have Monarch Classic v13?

           

          Best wishes,

           

          Olly

            • Re: newbie here; need to import brokerage statements
              Patrick DeLangis

              We're currently on v12 classic, but plan to upgrade as part of our SMS to v13, assuming we can. Was considering purchasing Monarch Complete.

               

              Do people share their templates? Or is that not something that is done? I imagine that bank statement and brokerage statement templates would be something that could be a little more universal than other reports.

               

              Part of getting an existing template from someone is so I can learn from them as well.

               

              Thanks,

              Patrick

                • Re: newbie here; need to import brokerage statements
                  Grant Perkins

                  Hi Patrick,

                   

                  I think people probably share models within their company but externally is somewhat rarer as far as I know.

                   

                  Indeed there may be less common formatting (and purpose for the results!) than you might think.  Plus in most cases a basic extraction model only takes a few minutes to create. Anything more challenging may not work too well as a share anyway!

                   

                  That is especially true for PDF files which may look (more or less) identical to the eye but can be very different in the electronic files under the hood. There are many PDF writer programs out there  - they are not all the same in terms of the results they produce.

                   

                  OCR activity can make them especially exciting to deal with.   What did you have in mind for your checking needs?

                   

                   

                   

                  Grant

                    • Re: newbie here; need to import brokerage statements
                      Patrick DeLangis

                      Thank you Grant.

                       

                      I don't understand your question about what I have in mind for my checking needs. Do you mean checking that the data OCR is accurate?

                       

                      Patrick

                        • Re: newbie here; need to import brokerage statements
                          Grant Perkins

                          Basically yes.

                           

                          As I read it you seem to be extracting information from OCR generated documents.

                           

                          Assuming you can achjeve some consistency from the process because you are starting out from a consistent source the question them becomes what have you got (presumably from the process)  that allows you to link back and check accuracy?

                           

                          Obviously one can check for Alpha characters in a Numeric field, number of characters and so on but things can still be quite difficult to pin down. So I was wondering what cross referencing you had available and how you might want to deploy it.

                           

                           

                          Grant

                            • Re: newbie here; need to import brokerage statements
                              Patrick DeLangis

                              Grant - yes I do have a plan for checking the data. I learned the term GIGO early on in my career - Garbage In Garbage Out. If I can't verify the data is accurate, all my analysis is wasted. The statements have recap on the first page summarizing the starting balance, transactions and then closing balance - I plan to import that data for each month's statement. The first thing I do after getting the data in excel is run a quick pivot table for each month where I get the summary data and compare it to the sum of the "detail" imported by Monarch. If the data doesn't match, then one or the other is off and I go clean up that data.

                               

                              Thanks for bringing this up since I am not well experienced in using Monarch.

                                • Re: newbie here; need to import brokerage statements
                                  Grant Perkins

                                  Hi Patrick.

                                   

                                  Good plan.

                                   

                                  It is usually not too difficult to find ways to verify numeric fields against totals so your strategy seems sound (of course)  - it's attempts at text verification (or at least assessment) that can be more challenging, letters being letters.

                                   

                                  For example of you need to use a particular Alpha character string as a linking reference code it may need validating for likely accuracy or art lest for the risk of any inaccuracy causing problems. A non-successful would be its own quality check. But a successful though incorrect link could be more problematic and, perhaps, more difficult to detect.

                                   

                                  Dates may also need some care for similar reasons.

                                   

                                  Obviously for this sort of work you should use whatever tool(s) you are most comfortable with. However it ought to be possible to make the checks within Monarch if you wish to consider that if and when the time comes.

                                   

                                   

                                  Grant

                        • Re: newbie here; need to import brokerage statements
                          Patrick DeLangis

                          And, BTW - it is not a rush - we haven't been officially retained on the project yet.