VBA help needed

[grantx]

Gentlemen,

I need to modify the following code to enable update on a tick-by-tick basis.

I think the line in bold (set at 1-second intervals) is what needs to changed. Any advice appreciated.

Thank you.

Grant.


Public RunWhen As Double
Public Const cRunWhat = "MyFunc"

Sub StartTimer()
RunWhen = Now + TimeValue("00:00:01")
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub

Sub MyFunc()

Dim row As Single

row = ActiveSheet.Range("A65536").End(xlUp).row + 1
Cells(row, 1).Value = Time()
Cells(row, 2).Value = [A1].Value
Cells(row, 3).Value = [B1].Value
Cells(row, 4).Value = [C1].Value
Cells(row, 5).Value = [D1].Value
Cells(row, 6).Value = [E1].Value
Cells(row, 7).Value = [F1].Value

StartTimer
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub

Sub ClearArea()
[A4:H60000].Clear
End Sub

 

[The General]

Grant,

A couple of things:

1. What is stopping it from working is the fact that you are setting the scheduling to 'True' - this doesn't work as it (Now + Timevalue) is not a time which can ever repeat in this format. The same is true of the 'Stop' routine, you can not cancel a schedule for something which cannot be scheduled.

To get it working as is then, take TRUE out of the initial timesetting, delete the line in the 'STOP' oproc entirely. Replace this line with setting a module-level boolean variable. Test for this variable before re-affirming the application.ontime method....

2. This timing methodology is prone to errors and is patchy at best.
A better solution is to download the CCRP Timer control (GOOG it) which is free and fires a timer event where you can handle the capture of your data values. This control is a true timer as opposed to the above 'dirty-workaround' and when you set the interval to a number of milliseconds, that is truly what it is.

IMHO, only use ontime method for initial timed firing of the CCRP timer object if required....

Hope this helps...

 

[swandro]

I would avoid the ontime function. A better method would be to use the built-in event called Worksheet_Change. Click on the worksheet and then select the relevant drop-down boxes in the VBA window.

This will be entered every time any cell on this worksheet changes. So you then need to test for a change in the specific cell that you want to monitor.

The parameter going into this routine is called Target and it is a range. So you can say... If target.row = blah blah and target.column = blah blah else exit.

This is a very quick answer. Hope it makes sense. If not, let me know and I will explain in a bit more detail.

 

[The General]

Swandro,

All well and good, but this is a quick way to fill 65k rows in the spreadsheet...

One would therefore imagine that was the foundation of the requirement to have a timed 'capture' ??

 

[swandro]

General

I read Grant's requirement differently. He does not want a timed capture - he wants a tick-by-tick capture. Tick updates can presumably arrive at any time. So with my approach, the Change event would only be triggered when a tick update occurs.

That's how I saw it anyway.

 

[grantx]

Come on gentlemen, let’s not fall out.

“setting a module-level boolean variable...If target.row = blah blah and target.column = blah blah else exit.”

My knowledge of VBA is less than patchy, so please bear that in mind.

The code works but only for “intervals”. General, your solution for an interval of milliseconds may just be an extension of this but tick intervals are indeterminate, ie random. If a trade hasn’t occurred for say, three seconds but the interval is 1 second, you’ll get three identical values, which is obviously incorrect.

Swandro, re Worksheet_Change. Can this differentiate between successive trades at identical prices?

If I remember correctly, average max for tick data (bund, for example) is around 80,000 – 100,000 rows.


Grant.

 

[swandro]

Grant

After testing that the cell that contains the new value is the one that has changed, as I have already outlined, you could then test the new value against the last value which you would store in a static variable. Because it is static, it will not be lost when you exit from the Change routine.

So it would run something like this:

If variable = value in cell then exit

variable= value in cell

Then do your code for copying the new value into a new row.

Another thing I would do is to have a bit of code to stop this routine trying to run itself when it is already busy. This could happen if two tick changes occur almost simultaneously.

To do this:

Dim IAmBusy as boolean

If IAmBusy = true then exit

IAmBusy = true

all the other code

IAmBusy = false

Hope this makes sense

 

[The General]

Fair point re the tick updates. I would, however, point out the following:

1. As the number of possible worksheet change causing items (asset prices) increases, you will, at some point, need to consider saving to a file iso pasting into the worksheet.

2. From memory, if the cell updates are provided by DDE updates, these do not fire the worksheet change event - one would have to insert a calculated field based on one of the DDE fields (which would potentially miss every update unless the same principle is applied to all the DDE fields (such a calculated field could be {cellvalue]=[DDE field] * 1.
If the updates are by way of Excel's RTD functionality, then this is not a problem.

Just a few things to consider...

 

[new_trader]

Come on gentlemen, let’s not fall out.

“setting a module-level boolean variable...If target.row = blah blah and target.column = blah blah else exit.”

My knowledge of VBA is less than patchy, so please bear that in mind.

The code works but only for “intervals”. General, your solution for an interval of milliseconds may just be an extension of this but tick intervals are indeterminate, ie random. If a trade hasn’t occurred for say, three seconds but the interval is 1 second, you’ll get three identical values, which is obviously incorrect.

Swandro, re Worksheet_Change. Can this differentiate between successive trades at identical prices?
If I remember correctly, average max for tick data (bund, for example) is around 80,000 – 100,000 rows.


Grant.

Grant,

If your aim is to capture tickdata, then there is a simple and elegant solution to ensure that you capture each tick and that it is unique.

As swandro suggested, you need to get rid of the timer function and use:

Private Sub Worksheet_Calculate()

However, above this function you need to declare a variable as an integer. This will be used to check a change in the Total Volume of the contract you are trading. I assume you have a DDE link to this? If not, add it. This is the best way of ensuring each tick is unique even if it trades at the same price for a number of consecutive trades.

eg/

Dim oldvolume As Long (when initially declared it will either be 'null' or 0)

Private Sub Worksheet_Calculate()

If oldvolume <> Range("F2").Value Then [Where F2 is Total Volume]

oldvolume = Range("F2").Value [Updates 'oldvolume' for the trade just taken]

[ Rest of code]

End if

End Sub

 

[grantx]

NT, Swandro, Genral,

"A simple and elegant solution" for a simple and elegant person.

How does this look?

Grant.

Public RunWhen As Double
Public Const cRunWhat = "MyFunc"

Dim oldvolume As Long

Private Sub Worksheet_Calculate()

If oldvolume <> Range("G1").Value Then
oldvolume = Range("G1").Value

Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub

Sub MyFunc()

Dim row As Single

row = ActiveSheet.Range("A65536").End(xlUp).row + 1
Cells(row, 1).Value = Time()
Cells(row, 2).Value = [A1].Value
Cells(row, 3).Value = [B1].Value
Cells(row, 4).Value = [C1].Value
Cells(row, 5).Value = [D1].Value
Cells(row, 6).Value = [E1].Value
Cells(row, 7).Value = [F1].Value
Cells(row, 8).Value = [G1].Value

StartTimer
End Subn 'I need some way to stop and start - can this be kept or will it conflict?,

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
End Sub

‘Will remove this – not needed’
‘Sub ClearArea()
[A4:H60000].Clear
End Sub'

End if

End Sub

 

[swandro]

Grant

I think you might be mixing two ideas here. If you use the Calculate event, which you are expecting will be triggered whenever a price gets updated, you can abandon the ontime thingy altogether. Just put the MyCalc code under the test for volume (i.e. just the bit that actually stores the current values).

Also - if volume has not changed, you need to exit the routine - you don't need to do anything until it has changed.

And - minor point I know - if you define row as a single variable, it will blow up after 16000 lines. If that is OK fine, but if not, make it a double. Go on - live a little!

Hope this helps

 

[The General]

Grant,

As per my previous, not sure if DDE link would fire a calculate event but if it does then fine...

Also, your 'turning off' requirement should be a simple boolean value which is trapped within worksheet calculate event.

 

[new_trader]

Grant,

As per my previous, not sure if DDE link would fire a calculate event but if it does then fine...

Also, your 'turning off' requirement should be a simple boolean value which is trapped within worksheet calculate event.

It does. 100%.

 

[A Dashing Blade]

It does. 100%.

I'd have said it doesn't

Certainly a Bloomberg DDE doesn't nor does a eSignal one, furthermore, given the technology behind DDE is over 15 years old (basically DDE puts data into the spreadsheet with no regard as to what else excel is doing) I can't quit see how it can (as events only came in with Excel 97 iirc).

However you do have the Worbook.Linksources property together with the Workbook.SetLinkOnData ecent which enables you to capture updates.

 

[new_trader]

I'd have said it doesn't

Certainly a Bloomberg DDE doesn't nor does a eSignal one, furthermore, given the technology behind DDE is over 15 years old (basically DDE puts data into the spreadsheet with no regard as to what else excel is doing) I can't quit see how it can (as events only came in with Excel 97 iirc).

However you do have the Worbook.Linksources property together with the Workbook.SetLinkOnData ecent which enables you to capture updates.

Strange. We are both speaking from experience but getting contradicting results.

Pass...

 

[timm]

Strange. We are both speaking from experience but getting contradicting results.

Pass...

I'm with you. When DDE value changes, the worksheet re-calculates. When the worksheet re-calculates you get a calculate event. What would be the point of DDE in excel if the sheet didn't re-calculate?

 

[A Dashing Blade]

Ahhh, ok.

My point is that the change in the DDE cell doesn't fire of the worksheet calculation event itself, although for sure, changes in dependent cells should.

 

[new_trader]

Ahhh, ok.

My point is that the change in the DDE cell doesn't fire of the worksheet calculation event itself, although for sure, changes in dependent cells should.

I am a little bit puzzled for 2 reasons:
1) I used the Calculate event in the way I have described and it functions perfectly.
2) If there are dependant cells then this would imply that the DDE link does trigger the ‘Calculate’ event. If the DDE cell is A1 and B1 contains the formula A1 x 5, what would make Excel calculate the value of B1? Surely B1 would never change because, as you say, there is nothing to trigger the calculate event.

 

[timm]

May be it's changed in later versions but in Excel 2002, I get a calculate event for every change regardless of any dependent cells.

 

[new_trader]

May be it's changed in later versions but in Excel 2002, I get a calculate event for every change regardless of any dependent cells.

I am saying the same thing and I am using Excel 2007. It makes sense because the DDE linked Cell is dependent on an external source for it's value so I can't see why it should be any different.