Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013

    Unanswered: excel model that outgrew itself


    My name is Rob. I’m trying to build a massive financial model that has 1 million rows, originally in Excel. Essentially, it should take assumptions, process the assumptions on the basis of the data in another worksheet, and then spit out outputs which I can proceed to slice and dice and then display to users in various ways. Outputs should change simply by changing a cell with an assumption in it.

    However, I’ve come to realize that this isn’t possible in Excel. While I have some basic knowledge of Access, it’s not to the point where I’m super-comfortable writing in query form what I would have done in Excel (with nested IFs, VLOOKUPs, SUMIFs etc.). With that said, the fact that my spreadsheet was 330 MB, and counting—at about 20% completion—suggested that Excel wouldn’t be able to handle what I’m hoping to do.

    So that leaves me asking what ideas you guys might have on how to tackle this problem. I’ve written macros in VBA before, but quite rudimentarily. With that said, my current thinking is that I’ll have user-friendly assumptions in Excel, port these as variables into VBA, use VBA to query an Access database (running nested IFs on the data), and then finally port the results from the Access query back into a large Excel table (maybe thousands of rows, but not a million) which I can then slice and dice in Excel.

    Is there an easier way (i.e., way with less of a learning curve) to do this? Or is this my best option? I’m on a tight timeline and am most uncomfortable with respect to writing the query—everything else I think I have a reasonable handle of (or can conceivably get one).

    Thanks for your help


  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    With such a volume of data, I would switch to a free version of SQL Server, provided that the number of clients (i.e. instances of the application connected to the database) remains low (5 if my memory serves me right, but MS regularly changes the rules). With more clients, you'll need to buy a licence.

    Both Access and Excel are able to be connected to such a server.

    You'll probably need to learn the SQL language, although not becoming an expert in it.
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    Access should be able to handle 1 million rows and MORE.
    If this is a single user db then I doubt you will get that much difference using Access or SQL server (or any server product for that matter).
    if its going to grow significantly or become multi user or have the data stored on a remote server then going to a server product will make sense

    if you are going to be doing massive amounts of number crunching then stuff the data into DB
    forget the idea of using Excel to STORE the date.
    bear in mind that if you want to you can extract data from a database into Excel as required. if you are happy in the Excel world then it may make sense to stay there for analysis

    however there is a world of difference between the spreadsheet world and the database of the biggest issues facing people moving from spreadsheets to databases is the concept that you only process the data you need. looking at temporal / series data can be a pain (there is no idea of comparing rows 10 with row 20, row 11 with row 21, it can be done but its not built in

    databases use 'set theory' to process groups of data. a group can be 1 row or as many others as required
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2004
    outside the rim
    So, along the lines of what healdem is saying, if your "processing" involves taking only a set of data and working with it, you could leave the processing in Excel, and create a "table" in Excel that draws from an outside data source.

    This "draw" from an outside datasource can use parameters that come from your spreadsheet in certain cells (a date range, for example) - this is a well documented process on the web and requires no VBA at all. It utilizes MS Query, which comes with Excel.

    The model would be: put all the "raw data" in a DB application (several examples already given). In Excel, link to the data, and in doing so, Excel will create a "table" (which you can give a name to - the names make it a lot easier to work with the data in formulas). You can even link to the same data in different ways if you want to - creating "tables" in Excel representing different views of the data. An example is if you are looking at a monthly report and a weekly report, you could have the same data drawn down into 2 tables, since the breakpoints of the months and weeks will be different.

    You can then do your "processing" in Excel using the structures you already have in place. This is a very common model in which Excel is used to create "dashboard views" of data from a large source.
    have fun!

    Todd S.
    click to email

    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET

    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  5. #5
    Join Date
    Apr 2013
    Thanks everybody--very helpful. It looks like Excel will have to play adjunct to Access.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts