Solved

Excel Delay

Posted on 2011-09-12
38
383 Views
Last Modified: 2012-05-12
Hello Experts,

A very clever expert assisted with the development of the attached spreadsheet.

I'm now putting it through its paces. Basically the spreadsheet receives data and pushes the old data a row below the current row.

The problem I'm having is that there is a serious delay of a second each time a new value is introduced. Everytime, the spreadsheet receives a new value it pauses and a spinning round circle appears - its as if its trying to do a calculation. However, there is no calculation involved. The spreadsheet simply recieves data.

I was wondering if there was something in the formula that may be contributing to the delay? If an expert could take a look at it with a fresh pair of eyes may be you'll see something the original Expert missed?

The only thing that I can think of that may contribute to the delay is the conditonal formatting, but not sure.
EE-Thetapev11update.xlsm
0
Comment
Question by:cpatte7372
  • 22
  • 16
38 Comments
 

Author Comment

by:cpatte7372
ID: 36525006
Hi Experts,

Don't if anyone has been looking at this issue, however I think the delay is due to conditional formatting, what do you guys think?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36526619
Hahaha.  You're probably right.  I created a few "homemade" functions to substitute for your subscription to the xlqintra functions, then looked at the time to calculate.  Even at 800-odd rows, its not very fast.  Apparently, you setup initial conditional formats, then every time it inserts a new row, those formats are propogated.  Over time, I would say DEFINATELY, the conditional formatting would begin to wear on your system.

You can prove this out by deleting all your history and start with a fresh file.  Is it faster, then as rows are added, does it run more slowly?

Also, the actual process of inserting rows (as opposed to shifting the dataset down a "notch" may be adding extra time, as well.

Let me know if it runs faster with less rows.  

If the answer is YES, then read on:

A question:  Is the conditional formatting set in row 3 going to be the same for that row (as it moves down), or, once its been set, would remain static (same font colors) in the "history" - e.g., rows 4+.   If it remains static, one possible solution would be to change the conditional formatting for the "history", replacing it with the actual font colors that were set by the conditional formats.  There's really only a need for that formatting in the first row, correct?  After that, it wouldn't change?

Let me know.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36526637
Just to be clear - take a look at row 5 in the spreadsheet you posted.  Would D5 always be red font, and the rest be black font, regardless of future events?  E.g., it was set when it was added, then stays the same from that point forward?

Also, do you have Excel 2007 or Excel 2010 (would impact the type of solution I would provided, based on your response, as Excel 2010 has some easier mechanisms for replacing conditional results with actual font changes).

Again - is it faster if you delete the "history" down to a few rows?  AND, once the conditions are set when the row is created, do those conditions change, ever again?

If the answer is YES, NO, then this solution path should be valid.

I'm looking forward to your response.

Dave
0
 

Author Comment

by:cpatte7372
ID: 36526646
Hey hey, its Dave,

It does run faster with less rows.

I removed the conditional formatting and it runs fast - but I really need some form of conditional formatting.

Yes, there is only a need to formatting in the first row - but as you can see it moves the formats with the row as it moves down.

Also, I think shifting the dataset down a notch may really improve things as well.


Cheers Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36526665
Version of Excel?

I can do this in a few.  Will be done tonite.  Will be looking at the data shift, but primarily at setting the conditional to non-conditional but visible font settings for all but the first row.  That way, as the data shifts down, there's only conditional formatting rules on the first row.

Dave
0
 

Author Comment

by:cpatte7372
ID: 36526691
2007

-C-
0
 

Author Comment

by:cpatte7372
ID: 36526693
I'll be here mate...
0
 

Author Comment

by:cpatte7372
ID: 36526917
Hi Dave,

I guess its a little more difficult than you thought, eh?

Anyway, I'm very confident you'll produce something worthy..

Speak sooo mate.
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 36527104
Not so difficult - funny story coming...  First, had to get home, deal with kids, cook something, eat while I thought about the problem, and... for this particular application, seems like a very, very simple solution.

GIVEN:  There's only 1 set of conditionals and they are on ROW 3
CONDITIONAL:  IF BIDSIZE > ASKSIZE, then BIDSIZE SHOULD BE BOLD RED, and ASKSIZE should be normal
CONDITIONAL:  IF ASKSIZE > BIDSIZE, then ASKSIZE SHOULD BE BOLD GREEN, and BIDSIZE should be normal

GIVEN:  One can do this with a conditional, but since we're dealing with a calculation event, anyway, we can make the font changes on the fly, with each calculate (rather than pull out wizardry to convert the conditional to normal font color at each calculate).

As a result, the solution is very simple, I believe.

Here's the code modification:

 
Option Explicit

Private Sub Worksheet_Calculate()
Dim rg As Range, rg2 As Range, targ As Range
Static oldVal(13) As Double
Dim i As Long, j As Long, nCols As Long

    Set targ = Range("Q3:V3")  'Watch these cells for changes
    nCols = targ.Columns.Count
    
    Call setConditionals 'just to ensure its initialized in the first row
    
    Set rg = Range("B3:G3")     'Put changed values here, pushing old values down
    Set rg2 = rg.Offset(0, -1).Resize(1, rg.Columns.Count + 2)
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    For j = 1 To nCols      'Watch columns Q through AC on row 3 for changes
        If oldVal(j) <> targ.Cells(1, j) Then
                'Shift data down
            rg2.Insert Shift:=xlShiftDown
            rg2.Copy rg2.Offset(-1, 0)
            'rg.Offset(-1, 0).Resize(1, nCols).Formula = targ.Value              'Copy over just data in Q3:V3
            rg.Offset(-1, 0).Formula = targ.Resize(1, rg.Columns.Count).Value   'Copy over data in Q3:AC3
            For i = 1 To nCols      'Capture values of data for next run
                oldVal(i) = targ.Cells(1, i).Value
            Next i
            
            Call setConditionals 'to update the new row 3, as its now the new row
            
            Exit For
        End If
    Next j
        
    Application.EnableEvents = True

End Sub

Sub setConditionals()
Dim bidSize As Range, askSize As Range

    'conditional Testing and sets font colors (or other flashy stuff) based on bid and ask size
    
    Set bidSize = Range("D3")
    Set askSize = Range("E3")
    
    If bidSize.Value > askSize.Value Then
        bidSize.Font.Color = vbRed
        bidSize.Font.Bold = True
        askSize.Font.Color = vbNull
        askSize.Font.Bold = False
    Else
        bidSize.Font.Color = vbNull
        bidSize.Font.Bold = False
        askSize.Font.Color = vbGreen
        askSize.Font.Bold = True
    End If
End Sub

Sub ResetAllConditionals()
Dim rng As Range
Dim bidSize As Range
Dim askSize As Range
'simple function to reset "history" so the fonts are colored at the beginning...


    'conditional Testing and sets font colors (or other flashy stuff) based on bid and ask size
    
    'clear all conditional formatting on the sheet
    
    Cells.FormatConditions.Delete
    
    'now reset them based on the following logic - no "live" conditional formats, needed
    For Each rng In Range("D3", Range("D" & Rows.Count).End(xlUp))
        Set bidSize = rng
        Set askSize = rng.Offset(0, 1)
        
    
        If bidSize.Value > askSize.Value Then
            bidSize.Font.Color = vbRed
            bidSize.Font.Bold = True
            askSize.Font.Color = vbNull
            askSize.Font.Bold = False
        Else
            bidSize.Font.Color = vbNull
            bidSize.Font.Bold = False
            askSize.Font.Color = vbGreen
            askSize.Font.Bold = True
        End If
    Next rng
End Sub

Open in new window


See attached.  Please let me know how it runs.

PS - if you just move the code over (in the IntraDay codepage), then run the macro ResetAllConditionals() which will clear conditional formatting on the ENTIRE SHEET (caution!) and then set the formats of Red/Green based on the rules, discussed, above.

From that point on, worksheet_Calculate would drive the formatting of the BidSize/AskSize as new rows are potentially created in the dataset.

If you look closely at the setConditionals() subroutine, you'll see where you can make color changes to the font (I just used vbRed and vbGreen) or even change the fill of the cell, rather than font, etc., so at a later time if you want different "blinkers", you can make the modification here (as opposed to the old conditional formatting approach requiring you to change each format...

See attached.

Enjoy!

Dave  
EE-Thetapev11update-r1.xlsm
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36527635
To fully test this out, I created dummy functions for the subscription functions you have, re: xlqIntraBid, etc., which generated random numbers for the values.  As a result, I could see the spreadsheet work by hitting F9 repeatedly.  It runs very fast, and rewriting the shift down logic had no merit - I tried it and could not differentiate any timing differences.

FOR FUN ONLY, here's my test app, where you can just hit F9 to see how it works with repeated calculations.  DON"T USE THIS VERSION as I've substituted my functions for the xlqIntraBid functions....

See attached - again DEMO ONLY.  My prior post is the one you want for "production".

Dave
DEMO-ONLY---FOR-FUN-r1.xlsm
0
 

Author Comment

by:cpatte7372
ID: 36528315
Hi Dave,

I have just turned on PC. Wow! I've only glanced over what you've done so far. I'm very excited to test this out live when the markets open.

It looks impressive, can't wait to see how it works.

Cheers mate
0
 

Author Comment

by:cpatte7372
ID: 36529306
OK, Dave,

Everything is set up. Markets open in 7 mins. Will let it run for a couple of hours to test its speeds.

Will let you know it goes.

Fingers crossed.

Cheers mate.
0
 

Author Comment

by:cpatte7372
ID: 36529901
Dave,

Just over an hour now, and the spreadsheet is still superfast.....Lets see for how long..


Cheers
0
 

Author Comment

by:cpatte7372
ID: 36529940
To be honest, I don't how its keeping up with the live data feed... there is virtually nothing in it. Amazing!
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36530397
Nice!
0
 

Author Comment

by:cpatte7372
ID: 36533397
Mate,

I hammered this spreadsheet today and it took it in its stride. Its a beautful thing....

Cheers
0
 

Author Closing Comment

by:cpatte7372
ID: 36533402
EE, this guy is worth his weight in Platinum.

The man is a wonder!

Cheers mate.
0
 

Author Comment

by:cpatte7372
ID: 36536439
Hi Dave,

Its still looking good.

Anyway, can you please show me how to stop or delete after say 1500 rows?

I think there is something like Rows(1500).delete. But I'm not sure.

Cheers
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36537284
If you want to just keep a running 1500 row history, without retaining anything past that, do the following:

            Range("A1500:A" & Rows.Count).EntireRow.Clear

Delete could be much more intensive.  This is also inefficient.  So, a better, more optimal way, is to clear what has data:

            Range("A1500", range("A" & Rows.Count).End(xlUp)).EntireRow.Clear

These work just fine (after your for/next loop in the worksheet_calculate subroutine) if you have data beyond 1500, and from that point forward.  That's because the End(xlUp) part will shorten your stack to where you are at that given time.

However, for new or "old" sheet, the following works for all cases

            Dim lastRow As Long  'put this up in your declarations

            'put the rest of this after your loop in the worksheet_calculate subroutine - or call it when you want as a separate module, to pare down your data, as needed.

            lastRow = Range("A" & Rows.Count).End(xlUp).Row
            lastRow = IIf(lastRow < 1200, 1200, lastRow)
           
            Range("A1500", Range("A" & lastRow)).EntireRow.Clear


Put that after your for/next loop in the Worksheet_Calculate subroutine.

Dave
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 41

Expert Comment

by:dlmille
ID: 36537287
That one line should read:

   lastRow = IIf(lastRow < 1500, 1500, lastRow)
0
 

Author Comment

by:cpatte7372
ID: 36537356
Dave,

Would I put "Range("A1500:A" & Rows.Count).EntireRow.Clear as shown below?"

For j = 1 To nCols      'Watch columns Q through AC on row 3 for changes
        If oldVal(j) <> targ.Cells(1, j) Then
                'Shift data down
            rg2.Insert Shift:=xlShiftDown
            rg2.Copy rg2.Offset(-1, 0)
            'rg.Offset(-1, 0).Resize(1, nCols).Formula = targ.Value              'Copy over just data in Q3:V3
            rg.Offset(-1, 0).Formula = targ.Resize(1, rg.Columns.Count).Value   'Copy over data in Q3:AC3
            For i = 1 To nCols      'Capture values of data for next run
                oldVal(i) = targ.Cells(1, i).Value
            Next i
           
            Call setConditionals 'to update the new row 3, as its now the new row
            Range("A1500:A" & Rows.Count).EntireRow.Clear
            Exit For
        End If
    Next j
       
    Application.EnableEvents = True

End Sub
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36537408
Yes, but as I posted, if you have < 1500 rows, then it will cut you off where you are...

Dave
0
 

Author Comment

by:cpatte7372
ID: 36537628
Hi Dave,

After the euphoria of getting this to work with such lightening speed, I decided to closely inspect the numbers. Unfortunately, the spreadsheet is picking up random numbers from somewhere and missing numbers. I attached a snapshot of datafeed I receive from my stockbroker alongside the spreadsheet. Now, as you know, each time any one of the values from Q3 to V3 changes it cause the existing row to move down a notch, which is exactly what happens with my stockbrokers sheet you see on the right.

As I'm sure you've gathered, I'm getting the data from my stock broker on the right which is being fed into the spreadsheet.

So far so good.

Now, comparing the spreadsheet my stockbroker you will see there are numbers that appear on the spreadsheet but was never fed from my stockbroker. For example, the bid size 29, & 9, never appeared from my broker,there are a few others aswell. But worse still is that its actually missing a whole of bid sizes, for example, 13, 2 (between 5 and 4) and a whole bunch of recurring numbers - _"_ simply means repeat. I original thought that Excel didn't recognise _"_ but the stockbroker doesn't actually send _"_ its just used in their application.

I don't think we're beaten by this but I wonder if this is fixable?

I know we've closed this ticket, but any help you can provide will greatly appreciated mate.

Cheers
0
 

Author Comment

by:cpatte7372
ID: 36537641
Oops! Attachment
TapeReader.jpg
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36537838
You are using the NON-DEMO version, correct?  Recall all my disclaimers on the "Play Test"...
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36537861
Look at your formulas Q3:V3 - those, I did not touch (Except in the DEMO version, where I wrote dummy functions to test for speed).  

If they aren't reflecting what you see from the Broker, then that's a problem that is independent of this question, agreed?  However, I'm with you till this is resolved, independent of ticket "status" :)

Cheers,

Dave
0
 

Author Comment

by:cpatte7372
ID: 36538024
Dave mate,

Its not the spreadsheet. The problem is with my broker. I have been giving them a tongue lashing.... I've told them I've lost money as a result of wrong bid figures - which is actually true.

Thanks for your continued support.

Was I correct on where to place "Range("A1500:A" & Rows.Count).EntireRow.Clear as shown below?"
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36538207
I was holding my breath, but seemed right.

Yes, you've put it in the right place.  That line will cut off at 1500 lines as long as you have > 1500 lines as your starting point.  Otherwise, it will cut off wherever you are at the time you implement (see alternative code provided which works in all cases).

The entire module is below for the "all cases" version.

Dave


Option Explicit

Private Sub Worksheet_Calculate()
Dim rg As Range, rg2 As Range, targ As Range
Static oldVal(13) As Double
Dim i As Long, j As Long, nCols As Long
dim lastRow as long

    Set targ = Range("Q3:V3")  'Watch these cells for changes
    nCols = targ.Columns.Count
    
    Call setConditionals 'just to ensure its initialized in the first row
    
    Set rg = Range("B3:G3")     'Put changed values here, pushing old values down
    Set rg2 = rg.Offset(0, -1).Resize(1, rg.Columns.Count + 2)
    
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    For j = 1 To nCols      'Watch columns Q through AC on row 3 for changes
        If oldVal(j) <> targ.Cells(1, j) Then
                'Shift data down
            rg2.Insert Shift:=xlShiftDown
            rg2.Copy rg2.Offset(-1, 0)
            'rg.Offset(-1, 0).Resize(1, nCols).Formula = targ.Value              'Copy over just data in Q3:V3
            rg.Offset(-1, 0).Formula = targ.Resize(1, rg.Columns.Count).Value   'Copy over data in Q3:AC3
            For i = 1 To nCols      'Capture values of data for next run
                oldVal(i) = targ.Cells(1, i).Value
            Next i
            
            Call setConditionals 'to update the new row 3, as its now the new row
            
            Exit For
        End If
    Next j


    'put the rest of this after your loop in the worksheet_calculate subroutine - or call it when you want as a separate module, to pare down your data, as needed.

     lastRow = Range("A" & Rows.Count).End(xlUp).Row
     lastRow = IIf(lastRow < 1200, 1200, lastRow)
            
     Range("A1500", Range("A" & lastRow)).EntireRow.Clear
        
    Application.EnableEvents = True

End Sub

Sub setConditionals()
Dim bidSize As Range, askSize As Range

    'conditional Testing and sets font colors (or other flashy stuff) based on bid and ask size
    
    Set bidSize = Range("D3")
    Set askSize = Range("E3")
    
    If bidSize.Value > askSize.Value Then
        bidSize.Font.Color = vbRed
        bidSize.Font.Bold = True
        askSize.Font.Color = vbNull
        askSize.Font.Bold = False
    Else
        bidSize.Font.Color = vbNull
        bidSize.Font.Bold = False
        askSize.Font.Color = vbGreen
        askSize.Font.Bold = True
    End If
End Sub

Sub ResetAllConditionals()
Dim rng As Range
Dim bidSize As Range
Dim askSize As Range
'simple function to reset "history" so the fonts are colored at the beginning...


    'conditional Testing and sets font colors (or other flashy stuff) based on bid and ask size
    
    'clear all conditional formatting on the sheet
    
    Cells.FormatConditions.Delete
    
    'now reset them based on the following logic - no "live" conditional formats, needed
    For Each rng In Range("D3", Range("D" & Rows.Count).End(xlUp))
        Set bidSize = rng
        Set askSize = rng.Offset(0, 1)
        
    
        If bidSize.Value > askSize.Value Then
            bidSize.Font.Color = vbRed
            bidSize.Font.Bold = True
            askSize.Font.Color = vbNull
            askSize.Font.Bold = False
        Else
            bidSize.Font.Color = vbNull
            bidSize.Font.Bold = False
            askSize.Font.Color = vbGreen
            askSize.Font.Bold = True
        End If
    Next rng
End Sub

Open in new window

0
 

Author Comment

by:cpatte7372
ID: 36539597
Thanks Dave ...
0
 

Author Comment

by:cpatte7372
ID: 36548205
Good morning Dave,

I have a request to add to this spreadsheet and I was wondering if I should submit a new request so that when you answer it (its really easy:-) you get the points. What do you think?

Basically, where you've fixed the spreadsheet so that the conditional formats don't get pushed down a notch I have added time in cell A3 with =NOW(), however I don't need that being pushed down.

I've looked at the spreadsheet to see if I can do it, but I'm sure I'll make a hash of it. So if you could take a look when you get a chance that would be great mate..

Cheers
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36551677
Sure, go for it!

:)

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36551714
where did you put the =NOW()?
0
 

Author Comment

by:cpatte7372
ID: 36559112
Hello mate,

Just come back from holiday in Scotland and a girfriend banned all access to computers and Internet, hence why its taken so long to reply.

Anyway, I hope you're well. I put the =NOW() in cell A3. As I mentioned, the formula need not get pushed down a notch. I failed in makin the adjustment.

Cheers

Carlton
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36561339
Easily done.  You need to change this line:

    Set rg2 = rg.Offset(0, -1).Resize(1, rg.Columns.Count + 2)

With this:

    Set rg2 = rg.Resize(1, rg.Columns.Count + 1)

See attached.

Dave


EE-Thetapev11update-r2.xlsm
0
 

Author Comment

by:cpatte7372
ID: 36572937
Once again Dave  you've come through.

Brilliant. Cheers mate...
0
 

Author Comment

by:cpatte7372
ID: 36596055
Hello Dave,

Just thought I would let you know that I'm going to be submitting a new request based almost entirely on this request. The only real difference is that instead of many rows shifting down and cells referencing other cells it all takes place in one cell.

Anyway, I will explain it in more detail in the request, I just thought if you were available you might assist as you're familiar with the situation

Cheers mate...
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36596061
ok - just refer to this question - see ask a related question right here where you type, just above the text box...

Dave
0
 

Author Comment

by:cpatte7372
ID: 36596288
Hi Dave,

I had already submitted the question before I read your comments.....

sorry about that
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
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…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now