Have an Excel spreadsheet that summarizes various key metrics for the business. The source data for these metrics comes from various sources including other spreadsheets, and web/ERP systems.

A suggestion was made to completely automate the whole data retrieval/population into the spreadsheet.

For this type of tool (only a few users) I prefer a copy-paste approach so that the users are actually looking at the data and making sure it is correct.

What reasons can I give that a full automation is not a good idea?

  • linking Excel spreadsheets requires that the source spreadsheet doesn't change
  • Excel can export CSV files from the ERP system but this would require a macro -> can the users maintain the macro
  • ?


Forget to mention that this process is a stop-gap until a data warehouse is built later this year. Following the suggestion of the accepted answerer it appears that the full automated answer would take weeks more effort then the few hours per month that the copy-paste method would take.

  • 1
    How error-prone is the copy-paste method? How many things need to be copied and pasted? How easy is it for someone to get it wrong? How easy is it to train someone new to do it? – FrustratedWithFormsDesigner Mar 24 '11 at 13:48
  • @Frustrated - the copy-paste method is generally less error prone in scenario's where the data source changes frequently. The user would be usually reading a step-by-step guide and would have to conciously ask themselves if they have the right tool and data source (ie March 2011 data). – John M Mar 24 '11 at 13:51
  • @John M: Is it possible to make choosing a datasource easy? Such as from a drop-down list or user-entered parameters for the connection string? – FrustratedWithFormsDesigner Mar 24 '11 at 13:54
  • @Frustrated - I'm not allowed to touch the ERP database sources directly - everything is extracted from a CSV export from those systems. – John M Mar 24 '11 at 15:42
  • @John M: But could this automated tool be designed in such a way that choosing which datasource to use could be made easy to avoid user error? You said one large source of errors is when the data source changes. Making the automated tool handle that could remove or mitigate one source of errors. – FrustratedWithFormsDesigner Mar 24 '11 at 15:54

You should make a list of all the anticipated risks and costs (in time, resources, cash) on one side, and the perceived benefits (time savings etc.) on the other.

If the sum total of the first list is noticeably larger than that of the second, you have a convincing argument towards management.

My addition to your list of concerns would be that it may not be worth investing a lot of effort into automating something, which can at best save a limited amount of time for a couple of users (and at worst, take even more of their time, having to deal with integration failures due to source format changes / outages, etc.). Also, as you mentioned, relying too blindly on automated tools may introduce invalid data into the system, with possibly severe consequences. To avoid this, the automation scripts need to be thoroughly tested to ensure they handle errors gracefully and never output invalid data - which is extra cost again.


Doing it manually is great to start out so users can find out what they need, how to do it, what problems to look out for, etc., but at some point you have to ask yourself the question WTF did they hire a programmer for? A programmer should have an aversion to watching users copy an paste data and doing things manually over and over again and definitely not encourage it. Maybe they don't mind, but it bothers the hell out of me.

It takes too long to train people to do these manual processes (Don't forget to insert extra rows and copy the formulas- this is nuts.) and they they are prone to make mistakes. Program it so the data are gathered correctly - every time (Within reason because things change, sources outside your control change.).

Time to get data out of Excel and into a database. Create an application that would build the necessary file (I like to use an Excel template to help with the formating.) and populate all the data from all the sources. Eliminate as many Excel data sources as you can. If the data in there are a result of Excel computations, then have a way to send that to the database so other apps can gather it.

  • > normally I would agree with you but I forget to mention this process is a stop-gap until a proper data warehouse is built later this year. I have partially automated pieces of the pie but the total time required for full automation far exceeds the manual copy-paste. – John M Mar 24 '11 at 15:40
  • > the database route was my original thought - but there are far too many calculations and conditonal formats needed. The users generally understand Excel but MS Access is a mystery to them. – John M Mar 24 '11 at 15:40
  • @John M: I forget to mention this process is a stop-gap until a proper data warehouse is built later this year. - Well if it's a stop-gap measure then you've answered your own question and you don't need our help. – Jim G. Mar 24 '11 at 17:37
  • I think a stop gap solution will give you better insight when you build the datamart. Think of it as a prototype. – JeffO Mar 25 '11 at 1:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.