1 2 Previous Next 15 Replies Latest reply on Jun 14, 2014 7:16 PM by TKnTexas

    Quickbooks/Excel Financials Import

    PhilSlaughter

      Title

      Quickbooks/Excel Financials Import

      Post

           I have spent a few days looking for an answer to this but I may need help forming the question.  We currently have about 200 companies set up in separate Quickbook files.  Each of the companies has it’s own GL, Bank accounts, revenues, etc.  What I am looking for is a way to consolidate the companies in order to see a “total” cash balance or “total” income for all the companies combined.  Really I would like to roll up all the companies into a consolidated set of Financials.  According to my CFO doing this through QB would be almost insurmountable.  

            

           My initial idea was to have a set of financials generated for each company into an excel spreadsheet that I could then import into FM.  So, each months Balance Sheet and P&L for each company would represent one record in a Balance Sheet table and a P&L table.  I figured I could create a custom import map (script) for each company to sort out discrepancies in GL Account names.  What I initially ran into was trying to import a spreadsheet that had the headers or GL account names in a column with the data in the next column instead of the row headers and row data.  Then once I started looking into a way to import this data I began questioning whether or not that was the best way to tackle this problem.

            

           I figure I’m not the first person to try this but I think I’m phrasing my search terms incorrectly.

            

            

           Phil

        • 1. Re: Quickbooks/Excel Financials Import
          philmodjunk

               I have heard of a product called "quick books connector" that is supposed facilitate the exchange of data between QB and FMP, but know next to nothing about it? Have you heard of it? I'd web search that and resarch it first as it might greatly simplify the process.

               But if you have to export to excel and then import into FileMaker to make this work, that should be possible to do. And I can definitely understand how putting what looks like a field name in one column and the field's value in the next would complicate the process, but that does not make the job insurmountable.

          • 2. Re: Quickbooks/Excel Financials Import
            PhilSlaughter

                 I'll research the Quickbooks connector and let you know.

                 Thanks.

            • 3. Re: Quickbooks/Excel Financials Import
              PhilSlaughter

                   Unless I overlooked something, it seams that most of the QB connection tools are custom scripts created for you by a third party.  This is probably not a bad solution but is not what I really had in mind. I was hoping for an ODBC type product.  I would like to explore the importing of the excel files.  How would I start learning how to parse report type data?

                   I wouldn't want to import summary data or section headers but I would want account numbers and titles as field names.  In a perfect world, I would loose the account titles and just use the GL account numbers but I figure I could probably do that manually since it will only have to be done once.
              • 4. Re: Quickbooks/Excel Financials Import
                philmodjunk

                     I suggest that, as an experiment, you drag and drop this excel file onto the FileMaker application icon. This will create a new FileMaker Database with a table into which the data in the spread sheet has been imported. You can examine that table in that file to see how this data was imported into that table.

                     The exact details of what you need to do here are not clear to me as I have no idea what you want to do with this data once you have imported it.

                     Do you already have a database designed into which you want to import this data?

                     What tasks do you want to accomplish with the imported data?

                     Do you have a data model (system of fields, records, tables and relationships) designed to accomplish those tasks--even if only a "paper design"?

                     There's just way too many ways you can go with this data until we narrow this down a lot by looking at what you want to do with this data.

                • 5. Re: Quickbooks/Excel Financials Import
                  SteveMartino

                       Phil has a very good idea as a way to start.  I just tried it with my QB, just for giggles.  It puts all the records on a Table layout. It's doable but a little slow, about 30 seconds per spreadsheet, without FM performing any calculations.  Here's some possible pointers:

                       1.  Does every company's P&L look the same in Excel as far as where they land on the spreadsheet columns?  It's not mandatory, but it would help.

                       Some obvious stuff:

                       1.  After you try an import, you'll have to change the field names and create some more tables

                       2.  Then add calculation fields (or summary fields) to do the math. 

                       After that, to create side by side comparisons, you probably would need to build some advanced reports.  But I just don't see how you can compare 200 some companies easily.  Exactly how do you want to compare?  Like 2 or 3 at a time, or do you want to rank them by income, net profit, etc.

                  • 6. Re: Quickbooks/Excel Financials Import
                    philmodjunk

                         I am not actually suggesting that you do this for more than one or two excel files nor as a way to start designing your database. This is just an easy way to do a "quick check" to see how the data in your spreadsheet maps into fields in a FileMaker table. It can help you see what field is going to get the text "Ordinary Income-Expense" as it spans more than one column in your spreadsheet and you can refer to this when figuring out how to get this data into FileMaker.

                    • 7. Re: Quickbooks/Excel Financials Import
                      PhilSlaughter

                           I dumped the above spread sheet into FM and it came out looking pretty much the same as the report.  The problem is FM treats every row on the report as a new record.  I'm looking to treat only the data column as a new record. One new record for each excel file import.  So every month I import each companies P&L and have one record that relates to another table that contains basic information on that company.  I.E.: ownership, company type, each owners percentage of ownership, if that company owns other companies, etc.  So, Phil, to answer your question; with the above relationship I can allocate income and expenses out to individuals even if the individual's income flows through another company.  I do have a database set up (Phil, you have also helped greatly with that, thanks).  I am looking to use the distinct GL accounts as fields for a new table.  So, I want my table to have a field(column) for each GL Account, the Date of the excel report, and a companyID field that is a join to the "Company" table.  One goal is to be able to take the Data and construct a P&L for a "Owner".  Right now I would have to gather up all the P&L's for the companies that person owns, then match the GL Accounts to each other and then, add up all the GL Accounts.

                           I attached how FM is importing the records.  What I want to be able to do is set up the 5020-Interest as a Field and have the 55,955.14 be a value in a record in that field.  Then when I import the next company's "Interest" it may be in GL account with the label "5021-Interest Income". I would like to set up a custom import map for that company so that the value in 5021-Interest Income imports to 5020-Interest as a new record value.  Then once I have an import map for each company I can import and Sum "5020-Interest" and report my Interest income for all the companies or owners or C-Corps or S-corps, etc.
                            
                            
                      • 8. Re: Quickbooks/Excel Financials Import
                        PhilSlaughter

                             My Relationship Graph

                        • 9. Re: Quickbooks/Excel Financials Import
                          PhilSlaughter

                               This is how I would like the PLTable structured:

                                

                          • 10. Re: Quickbooks/Excel Financials Import
                            SteveMartino

                                 Maybe when you create an Excel SS from QB you dump it into the same Excel File. Then take one sheet (call it 'Import' for example) with formulas (VLOOKUP, HLOOKUP just to name a few) to put the data on this 'Import' sheet the way you show in your last post.  Then importing this sheet should make the process a lot simpler.

                                 @PhilModJunk's last post.  My comment was based on the fact I didn't know you could just drag a file onto FM like that.

                            • 11. Re: Quickbooks/Excel Financials Import
                              philmodjunk

                                   You can also import data into table 1, and then use a script to transfer the data into the fields and records of your final table as a way of taking a column of data in the spreadsheet and populating the different fields of a single record.

                              • 12. Re: Quickbooks/Excel Financials Import
                                SteveMartino

                                     The only potential issue, when you just import the P/L from QB to a blank table in FM, for those relatively 2 dozen fields, you end up with over 60,000 records. I don't know why.  I do a couple of imports from Excel to FM regularly, and having one sheet in Excel that pre-arranges the data made it easier for me.

                                • 13. Re: Quickbooks/Excel Financials Import
                                  PhilSlaughter

                                       @Steve I also didn't know you could drag and drop like that.  Pretty cool.

                                       After a day of meetings I think we figured out we are going to have to do a Copy/Paste/Transform in Excel (thanks @Steve) before we import into FM.  It will probably add a couple of minutes to the import process per report but on the bright side we will be able to construct a homogenous set of GL accounts.  Then we will go back to our QB files and clean things up.  At that point it should be a fairly easy process to get the information into FM faster.

                                       I'm still not 100% on what I need to do in FM to organize the data but I am comfortable getting it in there.  

                                       Thank you both.

                                  • 14. Re: Quickbooks/Excel Financials Import
                                    SteveMartino

                                         When you get the Excel File the way you want it, post back and someone will be able to help you more.  Just a note, on the sheet you will import,  it may be easier to put All the headings from the P/L across the top, regardless of whether they appear in each P/L per company, with the amount below it.  Then when you import to FM, you can use the  'use first row as Field Names' option to match to corresponding field names in FM.  After you set that up once, and do it manually, you can auto import with a script and never have to go thru the import set up process again.

                                    1 2 Previous Next