# 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
###### Who is Participating?

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")

bidSize.Font.Color = vbRed
bidSize.Font.Bold = True
Else
bidSize.Font.Color = vbNull
bidSize.Font.Bold = False
End If
End Sub

Sub ResetAllConditionals()
Dim rng As Range
Dim bidSize 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

bidSize.Font.Color = vbRed
bidSize.Font.Bold = True
Else
bidSize.Font.Color = vbNull
bidSize.Font.Bold = False
End If
Next rng
End Sub
``````

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

Author 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

Commented:
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.

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

Commented:
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 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

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

-C-
0

Author Commented:
I'll be here mate...
0

Author 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

Commented:
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 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

Author 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

Author Commented:
Dave,

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

Cheers
0

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

Commented:
Nice!
0

Author Commented:
Mate,

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

Cheers
0

Author Commented:
EE, this guy is worth his weight in Platinum.

The man is a wonder!

Cheers mate.
0

Author 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

Commented:
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

Commented:

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

Author 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

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

Dave
0

Author 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

Author Commented:
Oops! Attachment
0

Commented:
You are using the NON-DEMO version, correct?  Recall all my disclaimers on the "Play Test"...
0

Commented:
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 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.

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

Commented:
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")

bidSize.Font.Color = vbRed
bidSize.Font.Bold = True
Else
bidSize.Font.Color = vbNull
bidSize.Font.Bold = False
End If
End Sub

Sub ResetAllConditionals()
Dim rng As Range
Dim bidSize 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

bidSize.Font.Color = vbRed
bidSize.Font.Bold = True
Else
bidSize.Font.Color = vbNull
bidSize.Font.Bold = False
End If
Next rng
End Sub
``````
0

Author Commented:
Thanks Dave ...
0

Author 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

Commented:
Sure, go for it!

:)

Dave
0

Commented:
where did you put the =NOW()?
0

Author 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

Commented:
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 Commented:
Once again Dave  you've come through.

Brilliant. Cheers mate...
0

Author 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

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

Dave
0

Author Commented:
Hi Dave,