Excel Delay

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
cpatte7372Asked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
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
 
cpatte7372Author Commented:
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
 
dlmilleCommented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
dlmilleCommented:
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
 
cpatte7372Author Commented:
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
 
dlmilleCommented:
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
 
cpatte7372Author Commented:
2007

-C-
0
 
cpatte7372Author Commented:
I'll be here mate...
0
 
cpatte7372Author Commented:
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
 
dlmilleCommented:
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
 
cpatte7372Author Commented:
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
 
cpatte7372Author Commented:
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
 
cpatte7372Author Commented:
Dave,

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


Cheers
0
 
cpatte7372Author Commented:
To be honest, I don't how its keeping up with the live data feed... there is virtually nothing in it. Amazing!
0
 
dlmilleCommented:
Nice!
0
 
cpatte7372Author Commented:
Mate,

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

Cheers
0
 
cpatte7372Author Commented:
EE, this guy is worth his weight in Platinum.

The man is a wonder!

Cheers mate.
0
 
cpatte7372Author Commented:
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
 
dlmilleCommented:
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
 
dlmilleCommented:
That one line should read:

   lastRow = IIf(lastRow < 1500, 1500, lastRow)
0
 
cpatte7372Author Commented:
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
 
dlmilleCommented:
Yes, but as I posted, if you have < 1500 rows, then it will cut you off where you are...

Dave
0
 
cpatte7372Author Commented:
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
 
cpatte7372Author Commented:
Oops! Attachment
TapeReader.jpg
0
 
dlmilleCommented:
You are using the NON-DEMO version, correct?  Recall all my disclaimers on the "Play Test"...
0
 
dlmilleCommented:
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
 
cpatte7372Author Commented:
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
 
dlmilleCommented:
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
 
cpatte7372Author Commented:
Thanks Dave ...
0
 
cpatte7372Author Commented:
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
 
dlmilleCommented:
Sure, go for it!

:)

Dave
0
 
dlmilleCommented:
where did you put the =NOW()?
0
 
cpatte7372Author Commented:
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
 
dlmilleCommented:
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
 
cpatte7372Author Commented:
Once again Dave  you've come through.

Brilliant. Cheers mate...
0
 
cpatte7372Author Commented:
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
 
dlmilleCommented:
ok - just refer to this question - see ask a related question right here where you type, just above the text box...

Dave
0
 
cpatte7372Author Commented:
Hi Dave,

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

sorry about that
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.