excel model that outgrew itself

[canuck86]

Hello

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

Rob

[rpeare]

I thought Excel was limited to 65,000 rows. If that's still the case you won't be able to do this in excel at all.

Can you give an example of the type of operation you're trying to perform. Show an example of your original data, an example of your assumption and what you want the output to be. If it's all based on logic and static rules you should be able to program it either via queries or VB code.