Excel worksheet seems to always want to update

[Ngolem]

Hi

I have a problem that has bugged me for a while.

I created a company stock analysis workbook that has sort of grown like a cancer over the years. It has worked well for about 5 years but in the last month or so it has suddenly slowed to a crawl in one section.

The speadsheet basically contains transaction information, current status and analysis section. It is the Analysis section that was creating the problem so I decided to split it off into a separate workbook.

I severed all links with the main workbook (or at least I think I did. but there is still the same slow problem.

The analysis workbook consists of 4 worksheets.

Worksheet #1 : just a display of buy/sell recommendations for about 40 stocks linked to Worksheet #4 (no problems here)

Worksheet #2 : Here I have a WebQuery to import financial price history of a given stock. With a macro I sort and select the data I want and Paste this data into Worksheet#3.(no problems here)

Worksheet #3 : Sections of this worksheet are assigned to 40 different stocks. Here a price history of about 1 year of data for each stock is maintained and Technical parameters such as moving averages, bollinger bands, RSI and other parameters are calculated. There is no real problems in these calculations. The data from this page is graphed and summarized on Worksheet #4 through data links.

Worksheet #4 is where the problems happen.

This page is divided into sections for each stock (40 of them). In each section I have a Chart of 6 months of data for the given stock showing the last closing price, moving averages and Bollinger bands. Another Chart shows the RSI results, Another chart shows Bollinger Band Width and another chart shows Accumulation/Distribution. These charts update properly from Worksheet #3 with no problem.

In addition to the these charts there are summary analyses for slopes, parameters and ultimately an algorithm results in a Buy/Neutral/Sell recommendation THIS IS THE PROBELM.

If I set the focus on a chart there is no problem. I can move around the worksheet with no difficulty. But if I place the cursor anywhere on the page other than on a chart there seems to be some kind of updating going on that locks up the application. It does not matter if I put the cursor on an empty cell or one with a formula the same lock-up occurs.

This has crept into my workbook in the last month and I am at a loss as to how to troubleshoot this. I have no links to any other workbook and I eliminated all macros to no avail.

I am sorry for all this dialog but any help is appreciated.



Jim Broadbent

 

[Ngolem]

I found the error????

I am taking sums for moving averages from columns of data.

Column A

12
10
13
14
5

If I make a formula like this Sum(A2:A4) which would evaluate to 37 ,I get this sum but also an error saying that there are adjacent numbers not included in the sum!!!

It isn't an error....I want it that way!!!

Turning off background error checking solved my problem. Everything updates smoothly now.

Wish I could selectively kill that single error check so I could get other potentially useful error catches but I have hundreds of such errors :-( since I am calculating many many moving averages.

Stupid Excel!!!


Jim Broadbent

 

[xlbo]

Tools>Options>Error Checking

untick "Inconsistent Formula in region"

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Ngolem]

Hi Geoff

That helps a bit but still there is a delay since it wants to re-run the errorcheck every time I touch another cell.

I suppose it is rechecking all the data that supports the 160 charts I created, even though there has been no change to those supporting worksheets

Very annoying unless you have another trick up your sleeve.

I want automatic recalculation enabled as when I add new data on the worksheets elsewhere I want the other worksheets to be recalculated...but this constant error checking when I am simply moving through the one worksheet is annoying.

Jim Broadbent

 

[xlbo]

Would suggest unticking each option in background error checking till you find the ones that are causing you issues and just leave them out. Unfortunately I can;t really help more than that as the error checking will be very specific to your layout and formulae...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Ngolem]

It is me again on the same spreadsheet or rather a modified spreadsheet from what I had described earlier.

I split off the graphs and update of those graphs from the main body of the spreadsheet. I scoured out all the links between the two workbooks (named fields, macros and links )

I have no problem with the main workbook...it has only one chart on it and updates in a second...no matter what I do..cut/paste or copy/paste.

I still have a lot of trouble with the speed of the Workbook of charts. I removed all error checking which was a problem before.

This is my problem (BTW it is Excel 2003)

1. The workbook consists of 3 worksheets...one with a query to grab financial data from the internet. I then run a macro which selects the data I want, rearrange the columns, sort the selected data and store it in clipboard. No problem here at all...the process takes less than a second and never slows down.

2. I then paste this data on the next worksheet under the column for that relates to that data.No problem here at all...the process takes less than a second and never slows down.

3. Now I copy then paste one row of several columns which contain several formulas that are used to process the raw data (high/low/close/volume) into moving averages, RSI, and other technical indicators. There is problem here The time to complete this copy/paste can take 30seconds or more and seems to be independent of the the number of rows that are processed.

4. If I add 4 rows of data at the bottom...I must re-adjust the rest of the data for that stock so that the last row of data is positioned properly for the charts on the next page.

This copy/paste takes another 30 secs. I don't cut/paste since there is It interfere with further calculations on the chart worksheet.

5. Now I have to eliminate the left over 4 rows at the bottom taking 30 secs again

If I copy/paste a single cell on this sheet...it takes 30 seconds to complete...it drives me nuts!!!

If I copy/paste(value) though it takes place immediately with no problem????

It seems that when you copy a formula and paste it...every formula in the workbook is reevaluated, whether it is related to the pasted formula or not????

Is there a setting to prevent this from happening.



Jim Broadbent

 

[xlbo]

Hmmmmm - you would need to have a lot of dynamic formulae for this to happen - is that the case? I think so from what you have posted before.

there are several different calculation states within excel that you may want to consider trying

You can calc:
The whole workbook from fresh
The whole workbook based on what has changed
The worksheet
An individual range

This is what VBA help says on the subject:

Calculates all open workbooks, a specific worksheet in a workbook, or a specified range of cells on a worksheet, as shown in the following table.

To calculate Follow this example
All open workbooks Application.Calculate (or just Calculate)
A specific worksheet Worksheets(1).Calculate
A specified range Worksheets(1).Rows(2).Calculate

expression.Calculate
expression Optional for Application, required for Worksheet and Range. An expression that returns an object in the Applies To list.

Example
This example calculates the formulas in columns A, B, and C in the used range on Sheet1.

Worksheets("Sheet1").UsedRange.Columns("A:C").Calculate

This would mean that your spreadsheet would need to be in manual calc mode for the most part but you could set up an event driven routine (maybe the worksheet CHANGE event or the worksheet DEACTIVATE event) to ensure that calculations were updated

I think the issue may be in the number of formulae you have - this may be causing excel to have to recalc its whole formula stack each time you make a change (I have seen this happen quite a lot when I use dynamic named ranges especially in conjunction with complex criteria based aggregations). You mention an algorithm that you use - is this standard worksheet formulae or a custom function you have written in VBA ? If the latter, does it have "Application.Voltile" anywhere in the code as this could cause the issue you are seeing as well....

This website is very good for explaining how excel handles its calculations - hope it is of some use to you:

http://www.decisionmodels.com/optspeedb.htm

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Ngolem]

I think I may have to redesign the workbook

Currently I have 3 worksheets

1. website query for financial data and processing
2. Sheet for tabulation processed data for all financial stocks (20 of them)
3. Sheet containing all of the charts of the stocks and summary calculations.

I think I will try to have one worksheet per stock containing the elements of worksheet 2 and 3 specific for that stock.

This should limit the number of recalculations of data that is unchanged.

My formula calculations don't employ VBA I only use VBA for manipulation of the original web query data....hmmmm I wonder if I should use a macro to do all of the calculations instead of using individual cell formulas???? Maybe that would be the trick to speeding things up as well.

Thanks you have given me some things to think about

Jim Broadbent

 

[xlbo]

In general, worksheet formula calcs are pretty optimised compared to using VBA. It may be however that you can use VBA to enter the formulae at certain points and then paste as values when done - I guess this depends on how much and how often the calcs are updated....

Happy thinking !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Gavona]

Just in case you want a bit more to think about...

This site provides a lot of insight into efficient spreadsheet design:

http://www.decisionmodels.com/

And, I also came across this one that looks good too:

http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm

Gavin

 

[Ngolem]

Well I am learning stuff.

I have redesigned the spreadsheet and have a worksheet for each stock with raw data and calculation summary/evaluation.
(I am 1/3 through the redesign)

I eliminated construction of charts on the data with the exception of one. In their place I paste images that I copy from a website. Works ok but not as updated as often as I like.

It seems that as long as I am not updating other worksheets from a one worksheet there is little problem. There are still some small logistical points that must be addressed but those are solvable and are less irritating than waiting 1 min or so for each update

Thanks for all your comments....they helped


Jim Broadbent