Data manipulation in Excel
Posted on 2011-03-01
I would like an expert's opinion on something. So, this does not have a specific answer.
Here is my goal:
I would like to take structured data from an excel sheet and create a financial statement with it. I would like the user to be able to select a few criteria using a combobox control and then push a button and up pops the financial report.
Here is what I am considering:
- I know one popular path to what I am doing is to use pivottables which uses the efficient pivotcache. I could programmatically pull data from the pivottable and feed it into the financial statement.
Positives: This is absolutely what most people would recommend and I would have a lot of example code to use. Also, this may be the fastest since it uses an internal data store native to excel called pivotcaches.
Negatives: I have used pivottables before with vba and found it to be clumsy. It was difficult to trap errors when I used it. I also had to debug and view the pivot tables and found it added an extra layer of programming. I know this is probably the best method to make the most efficient use of memory, but is this really the most stable method?
- I could also convert the structured data into xml and pull data from the xml table created directly into my report.
Positives: the data verification in excel for xml is strong
Negatives: There is not too much example code in xml.
- I could simply pull the structured data into a sheet and use the standard "collection" in vba to persist the data in active memory. I could filter the data programmatically and then dump it into a report format.
Positives: I have greater control in this method
Negatives: This punishes system resources the most
The structured data will have around 5,000 rows with 200 unique columns.
The resulting financial statement will consolidate this into around 30 rows and 12 columns.
The user will be a intermediate user who has no desire to manipulate the data with pivottables or otherwise. S/he will want to simply press a button and have the financial statement appear. However, wait times such as 3 or 4 minutes are acceptable.
I am between a power user and an expert. I can use vba, classes, and interfaces.
So, specifically, if you were in my shoes, what approach would you take to my project?
Thanks in advance for any comments.