VBA help needed

This is a discussion on VBA help needed within the Technical Analysis forums, part of the Methods category; Gentlemen, I need to modify the following code to enable update on a tick-by-tick basis. I think the line in ...

Reply
 
LinkBack Thread Tools Search this Thread
Old Oct 17, 2007, 4:41pm   #1
Joined Jun 2006
VBA help needed

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
grantx is offline   Reply With Quote
Old Oct 18, 2007, 12:23am   #2
 
The General's Avatar
Joined Oct 2003
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...
__________________
Cheers,

The General.
The General is offline   Reply With Quote
Old Oct 18, 2007, 12:24am   #3
Joined Aug 2003
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.
swandro is offline   Reply With Quote
Old Oct 18, 2007, 12:44am   #4
 
The General's Avatar
Joined Oct 2003
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' ??
__________________
Cheers,

The General.
The General is offline   Reply With Quote
Old Oct 18, 2007, 12:57am   #5
Joined Aug 2003
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.
swandro is offline   Reply With Quote
Old Oct 18, 2007, 1:27am   #6
Joined Jun 2006
grantx started this thread 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.
grantx is offline   Reply With Quote
Old Oct 18, 2007, 1:41am   #7
Joined Aug 2003
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
swandro is offline   Reply With Quote
Old Oct 18, 2007, 1:48am   #8
 
The General's Avatar
Joined Oct 2003
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...
__________________
Cheers,

The General.
The General is offline   Reply With Quote
Old Oct 18, 2007, 10:01am   #9
 
new_trader's Avatar
Joined Jan 2006
Quote:
Originally Posted by grantx View Post
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
__________________
"It always pays a man to be right at the right time."
-Jesse Livermore


CLICK - My Trading Journal | SEARCH: soundmoneyproject.org |
new_trader is offline   Reply With Quote
Old Oct 18, 2007, 6:32pm   #10
Joined Jun 2006
grantx started this thread 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
grantx is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel VBA Developer job??? gurm Home Trader 7 Nov 30, 2005 10:29pm
help needed spredbetterstop Techies Corner 5 Mar 5, 2004 11:07pm
any techies able to help Excel Vba V. III Techies Corner 3 Sep 19, 2003 10:51am
help needed? techcherry Techies Corner 5 Feb 1, 2003 3:05pm
Help needed please options Techies Corner 6 Jan 23, 2003 4:01pm

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)