Solved

Data manipulation in Excel

Posted on 2011-03-01
7
447 Views
Last Modified: 2012-08-13
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

More Background:
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.

My Status:
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.

0
Comment
Question by:Opie1313
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 35011581
Opie1313,

>So, specifically, if you were in my shoes, what approach would you take to my project?

I would establish an Excel database with all the data in it that I will need to assemble the required reports/summaries. I would then use SUMPRODUCT() to extract what was needed to create the reports/summaries. That would make it highly flexible and easy to maintain.

Patrick
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35011622
I would go for this


- 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

At the same time I will try and use stuff like

1) Application.Screenupdating = False
2) Avoid use of .Select and .Activate
3) Minimal use of variables etc...

Sid
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35011784
I would make every effort to:

1.  avoid like the plague using VBA. The reason for that is that VBA is ALWAYS slower than native Excel functions.

2. Avoid using the filter function. Instead use SUMPRODUCT() as it extracts only what is needed and sums it on the fly.

3. Avoid using Pivot Tables as they are clunky and inflexible. They have their place but not for what you want to do.

Patrick
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 45

Accepted Solution

by:
patrickab earned 500 total points
ID: 35011872
I would also ensure that:

1. There were no hyperlinks to anything

2. All the data and all the reports were in one file. That would maximimse speed and simplify formulae enormously.

3. Avoid using volatile Excel functions - they are:

Volatile functions in Excel: RAND(), NOW(), TODAY(), OFFSET(), CELL(), INDIRECT(), INFO().

Despite claims, these are not volatile: INDEX(), ROWS(), COLUMNS(), AREAS().


Patrick
0
 

Author Closing Comment

by:Opie1313
ID: 35012216
Had some nice comments to help with my solution.
0
 

Author Comment

by:Opie1313
ID: 35012222
Thanks Patrick for the help.  I will stay away from those volatile functions that you mentioned.
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35012237
Opie1313 - Thanks for the grade - Patrick
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question