Jump to content
House Price Crash Forum

Archived

This topic is now archived and is closed to further replies.

Pick It Down

Updating Huge Spreadsheets - Macros Or Formulas?

Recommended Posts

I've just started a new job and finding it hard to work out what is going on with spreadsheets I've inherited. I've always used formulas rather than macros in spreadsheets to make error detection easier but these new ones I'm using are based around macros picking up data from different locations. I don't want to keep using spreadsheets where I can't explain what they are doing as errors might slip through unspotted, but also don't want to waste days re-working huge spreadsheets..

What's the HPC experts' view on this? :)

Share this post


Link to post
Share on other sites

What's the HPC experts' view on this? :)

I work with complex spread sheets and my advice would be to bite the bullet and spend time re writing the current programmes to your understanding and spec.

In doing so you will learn how the things 'actually' work, will probably be able to make some improvements, and if later in the day you need to 'tailor' it you will have an intiment knowledge of what can and can't be done.

There are mant other reasons, so just get on with it and don't try and muddle through with someone elses work.

Regards

Share this post


Link to post
Share on other sites

I've just started a new job and finding it hard to work out what is going on with spreadsheets I've inherited. I've always used formulas rather than macros in spreadsheets to make error detection easier but these new ones I'm using are based around macros picking up data from different locations. I don't want to keep using spreadsheets where I can't explain what they are doing as errors might slip through unspotted, but also don't want to waste days re-working huge spreadsheets..

What's the HPC experts' view on this? :)

Start again. Unless there is a very good reason not to.

Share this post


Link to post
Share on other sites

I work with complex spread sheets and my advice would be to bite the bullet and spend time re writing the current programmes to your understanding and spec.

In doing so you will learn how the things 'actually' work, will probably be able to make some improvements, and if later in the day you need to 'tailor' it you will have an intiment knowledge of what can and can't be done.

There are mant other reasons, so just get on with it and don't try and muddle through with someone elses work.

Regards

Thanks. Naturally that would also be my approach but I have to worry if my team would want a whole new approach. Having only worked at the one company i'm not too sure what the standard approach is elsewhere. For a while the company (well, IT) actually frowned upon people writing macros as they didn't want reliance on individuals, which is fair enough but recently people have been given much more leeway and VB seems to be something they actively look for when recruiting..

Share this post


Link to post
Share on other sites

Sounds like your new job is in a bank or a hedge fund.

They love huge spreadsheets that Excel can't handle, cobbled together with macros and often unreliable. Good luck!

Share this post


Link to post
Share on other sites

Sounds like your new job is in a bank or a hedge fund.

They love huge spreadsheets that Excel can't handle, cobbled together with macros and often unreliable. Good luck!

There are things you cant do without programming, and macros are just very basic programming.

Personally I do all my programming in VB and use Excel as the presentation layer only. The people where I work now call them "Magic Spreadsheets" where Excel opens on its own and gets populated with all the data they wanted with no work from them.

Share this post


Link to post
Share on other sites

I detest macros. Fragile as anything. Loved by trainers because they are good visually. "Look, I can paint it red." I have grudgingly allowed some of my team to use them in limited applications but nothing integral to core systems.

Rewrite it with formulae, look-ups, and pivot references.

Build in plenty of error checks, if it is hugely complicated also link these through to a control page so you can see at a glance whether anything is out of line.

Re-writing it will also force you to understand everything that it is doing and you will become the expert.

Share this post


Link to post
Share on other sites

Used to work with monster spreadsheets at the Audit Commission ages ago, they should really have been Access databases IMHO but I've noticed with several employers now that they tend to use spreadsheets when they really want a DB.

I had to use macros for those because formulas were just too slow and the macros ran a lot faster.

This was a while ago now though and computer horsepower has gone through the roof since then so it might not be an issue these days.

Share this post


Link to post
Share on other sites

Used to work with monster spreadsheets at the Audit Commission ages ago, they should really have been Access databases IMHO but I've noticed with several employers now that they tend to use spreadsheets when they really want a DB.

I had to use macros for those because formulas were just too slow and the macros ran a lot faster.

This was a while ago now though and computer horsepower has gone through the roof since then so it might not be an issue these days.

Share this post


Link to post
Share on other sites

Whenever people properly audit Excel spreadsheets they generally find errors in about 15% which is kind of scary particularly for financial data.

You should have checksums for *everything* e.g. If you add up all the sales for the year and add up all the sales for each month, then as a checksum, add up the monthly sales and make sure they equal the Year (or Year to date) total. A lot of people fail to do this because they think "well that will obviously match so why bother"

Very often you start off with a correct spreadsheet and some idiot does a copy and paste replacing a formula with a fixed value. The other big issue is replacing formula with a macro but it contains a circular reference - in a formula this will give you an error, in a macro it can easily go unnoticed.

Share this post


Link to post
Share on other sites

IMHO you need to understand it, spend the time finding out what it does. Could you add a sheet which checks/tests the validity of the data that is being imported, you could change the macros to output some exra debug/test info to this separate sheet, by changing the macros you will start to understand them more....

Share this post


Link to post
Share on other sites

IMHO you need to understand it, spend the time finding out what it does. Could you add a sheet which checks/tests the validity of the data that is being imported, you could change the macros to output some exra debug/test info to this separate sheet, by changing the macros you will start to understand them more....

Possibly. I wonder if doing so will take as much time as re-working the buggers though.

There's been some good suggestions on this thread thanks for all the ideas, even if I stick with the macros I will certainly add in some checks on them.

Its been very good to hear how HPC experts use spreadsheets, thanks.

Share this post


Link to post
Share on other sites

 

My advice is to check out the new Excel Services stuff from MS and start coding some XLLs to perform  complex re-useable functions and then keep those locked down and don't allow tinkering.  Macros are too easy to fiddle with and become sheet specific. This way you be able to use the standard Excel functions and your own ones.

The other good thing is that Excel services is cluster aware, so provided you have a good candidate sheet to run in parallel, you can speed things up massively.

Share this post


Link to post
Share on other sites

Thanks. Naturally that would also be my approach but I have to worry if my team would want a whole new approach. Having only worked at the one company i'm not too sure what the standard approach is elsewhere. For a while the company (well, IT) actually frowned upon people writing macros as they didn't want reliance on individuals, which is fair enough but recently people have been given much more leeway and VB seems to be something they actively look for when recruiting..

Have you looked at the code ?

Alt F12. Then step through the macro line by line. Easiest way to actually see what is going on. F6 IIRC.

If the person writing it has any clue they will explain it as they go along. Every section of code will be preceded by:

' Copy data from sheet x and populate sheet Y blah blah blah blah blah.

If it is missing this ? Good luck.

Share this post


Link to post
Share on other sites

Used to work with monster spreadsheets at the Audit Commission ages ago, they should really have been Access databases IMHO but I've noticed with several employers now that they tend to use spreadsheets when they really want a DB.

Or they use Access when they really need something much better. I can't stand that either!

Share this post


Link to post
Share on other sites

I've just started a new job and finding it hard to work out what is going on with spreadsheets I've inherited. I've always used formulas rather than macros in spreadsheets to make error detection easier but these new ones I'm using are based around macros picking up data from different locations. I don't want to keep using spreadsheets where I can't explain what they are doing as errors might slip through unspotted, but also don't want to waste days re-working huge spreadsheets..

What's the HPC experts' view on this? :)

Without knowing anything about what your spreadsheets are meant to do, my first thought would be to ask whether a spreadsheet is the right solution tool in the first place.

If a spreadsheet is appropriate, sometimes the best solution is a combination of macros and formulae, or even formulae generated by macros. (ML's suggestion is even better, but this may not be within your skill set or something that the company will be happy with.) Inter-spreadsheet links, in my opinion, should be avoided at all costs, as I have as yet to see a project where they did not cause serious errors and heartache so I would always transfer/update data by macro if it was feasible.

I currently run the modelling for a project with about 250 very large but structurally similar spreadsheets which are continuously updated. This was previously done purely with spreadsheets and formulae. It was a disaster. Instead I wrote code to generate the spreadsheets and their attendant formulae and shift data around. People can look at the spreadhseets and follow the logic as the formulae are still there, but the human element which invariably leads to incorrect formulae etc. has been removed as any changes are done via code. It was a big step for the company to take, as the code took some time to develop (12 man weeks), but in the first year it has already saved 40 weeks of QA time and data accuracy has shot through the roof. At least 2 more years to go on the project, so lots more savings to come. (The spreadsheets are also less than 1/10th the size.) There is obviously strict control over the code - the resulting spreadsheets are code/macro free. The generating code version controlled, and is kept well away from the people who use and change the values in the spreadsheets, and is is very well documented, so that when I get hit by a bus, someone else can follow what is going on.

This approach, of course, may not be suitable for all projects and intitially there was a lot of resistance until the benefits became apparent to all. In the end, every project is different and without understanding what the problem is, it is very hard to give reasonable advice.

Share this post


Link to post
Share on other sites

Its amazing where spreadsheets are used. I went a conference 4 years back where nationwide described there banking system, which appeared to partly involve lots of spreadsheets and some databases. They had improved it by controlling the spreadsheet with some orchestration software. I wouldn't be surprised if lots of banks still use spreadsheets to calculate important things...

Share this post


Link to post
Share on other sites

Without knowing anything about what your spreadsheets are meant to do, my first thought would be to ask whether a spreadsheet is the right solution tool in the first place.

If a spreadsheet is appropriate, sometimes the best solution is a combination of macros and formulae, or even formulae generated by macros. (ML's suggestion is even better, but this may not be within your skill set or something that the company will be happy with.) Inter-spreadsheet links, in my opinion, should be avoided at all costs, as I have as yet to see a project where they did not cause serious errors and heartache so I would always transfer/update data by macro if it was feasible.

I currently run the modelling for a project with about 250 very large but structurally similar spreadsheets which are continuously updated. This was previously done purely with spreadsheets and formulae. It was a disaster. Instead I wrote code to generate the spreadsheets and their attendant formulae and shift data around. People can look at the spreadhseets and follow the logic as the formulae are still there, but the human element which invariably leads to incorrect formulae etc. has been removed as any changes are done via code. It was a big step for the company to take, as the code took some time to develop (12 man weeks), but in the first year it has already saved 40 weeks of QA time and data accuracy has shot through the roof. At least 2 more years to go on the project, so lots more savings to come. (The spreadsheets are also less than 1/10th the size.) There is obviously strict control over the code - the resulting spreadsheets are code/macro free. The generating code version controlled, and is kept well away from the people who use and change the values in the spreadsheets, and is is very well documented, so that when I get hit by a bus, someone else can follow what is going on.

This approach, of course, may not be suitable for all projects and intitially there was a lot of resistance until the benefits became apparent to all. In the end, every project is different and without understanding what the problem is, it is very hard to give reasonable advice.

That sounds much more of a system that I am working with! We're lucky at our place if they handle the clock change days :rolleyes:

Do you think it is a good idea to ake yourself indispensable though?

Share this post


Link to post
Share on other sites

Its amazing where spreadsheets are used. I went a conference 4 years back where nationwide described there banking system, which appeared to partly involve lots of spreadsheets and some databases. They had improved it by controlling the spreadsheet with some orchestration software. I wouldn't be surprised if lots of banks still use spreadsheets to calculate important things...

You don't know the half of it..:ph34r:

Share this post


Link to post
Share on other sites

You don't know the half of it..:ph34r:

From my dealings with banks I am astounded that the auditors are ever sufficiently confident in the numbers to sign them off. Shambles.

My main issue with macros (other than the maintenance issues) is the lack of backward traceability. I want to be able to see a headline number and track it back all the way to the original record. Whether this is a piece of off-the-shelf software or a complex spreadsheet with an ODBC link to such software. Macros tend to pick up and dump data so you are then having to reconcile an extract back to a source without being able to follow an individual link back.

I've bene trying to think of where I have used macros and all I can come up with are major items lists where I'm not trying to produce something that reconciles, just to pull out big items. So copy / paste special from a table into a hidden spreadsheet, sort, then copy the top ten records and drop them into the front sheet. Works fine for that but equally it's not part of a proper system, just pulling out some highlights.

Share this post


Link to post
Share on other sites

That sounds much more of a system that I am working with! We're lucky at our place if they handle the clock change days :rolleyes:

Do you think it is a good idea to ake yourself indispensable though?

It got implemented because we had a manager who could stare everyone else down. People had been trying for a few years previously to move the work to a DB solution, but had been thwarted by the user base. We implemented it in secret and presented the beta version a fait accompli and dealt with the complaints then. Actually, what we have done is implement something that is structured so that it will all fit nicely into a DB if that route is ever chosen.

(1) I'm not indispensable because the code is well documented. There is a huge Javadocs style API documentation, together with lots of internal code comments. Together with operator and user manuals this makes me pretty dispensible. I don't mind, because I get bored doing the same job for more than a year or two.

(2) Because of (1) they like me and keep me doing work for them.

Making yourself indispensable by not documenting code seems to have been a good way to keep a job in the past. There was (note the "was") this one fellow at the company I'm consulting for who had ignored management's requests for years to get his house in order, but they never got rid of him because too much rode on his code working. It didn't save him this time with extreme cuts in staff being implemented.

Share this post


Link to post
Share on other sites

  • Recently Browsing   0 members

    No registered users viewing this page.

  • 153 Brexit, House prices and Summer 2020

    1. 1. Including the effects Brexit, where do you think average UK house prices will be relative to now in June 2020?


      • down 5% +
      • down 2.5%
      • Even
      • up 2.5%
      • up 5%



×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.