cpatte7372
asked on
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
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
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
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
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
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
ASKER
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
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
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
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
ASKER
2007
-C-
-C-
ASKER
I'll be here mate...
ASKER
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.
I guess its a little more difficult than you thought, eh?
Anyway, I'm very confident you'll produce something worthy..
Speak sooo mate.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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
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
ASKER
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.
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.
ASKER
Dave,
Just over an hour now, and the spreadsheet is still superfast.....Lets see for how long..
Cheers
Just over an hour now, and the spreadsheet is still superfast.....Lets see for how long..
Cheers
ASKER
To be honest, I don't how its keeping up with the live data feed... there is virtually nothing in it. Amazing!
Nice!
ASKER
Mate,
I hammered this spreadsheet today and it took it in its stride. Its a beautful thing....
Cheers
I hammered this spreadsheet today and it took it in its stride. Its a beautful thing....
Cheers
ASKER
EE, this guy is worth his weight in Platinum.
The man is a wonder!
Cheers mate.
The man is a wonder!
Cheers mate.
ASKER
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
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
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.Clea r
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)).Ent ireRow.Cle ar
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
Range("A1500:A" & Rows.Count).EntireRow.Clea
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)).Ent
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
That one line should read:
lastRow = IIf(lastRow < 1500, 1500, lastRow)
lastRow = IIf(lastRow < 1500, 1500, lastRow)
ASKER
Dave,
Would I put "Range("A1500:A" & Rows.Count).EntireRow.Clea r 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.Clea r
Exit For
End If
Next j
Application.EnableEvents = True
End Sub
Would I put "Range("A1500:A" & Rows.Count).EntireRow.Clea
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.Clea
Exit For
End If
Next j
Application.EnableEvents = True
End Sub
Yes, but as I posted, if you have < 1500 rows, then it will cut you off where you are...
Dave
Dave
ASKER
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
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
ASKER
Oops! Attachment
TapeReader.jpg
TapeReader.jpg
You are using the NON-DEMO version, correct? Recall all my disclaimers on the "Play Test"...
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
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
ASKER
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.Clea r as shown below?"
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.Clea
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
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
ASKER
Thanks Dave ...
ASKER
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
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
Sure, go for it!
:)
Dave
:)
Dave
where did you put the =NOW()?
ASKER
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
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
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
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
ASKER
Once again Dave you've come through.
Brilliant. Cheers mate...
Brilliant. Cheers mate...
ASKER
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...
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...
ok - just refer to this question - see ask a related question right here where you type, just above the text box...
Dave
Dave
ASKER
Hi Dave,
I had already submitted the question before I read your comments.....
sorry about that
I had already submitted the question before I read your comments.....
sorry about that
ASKER
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?