Record Price at specific time - Help

[Raiden]

Hey lads,

first of all, hello to everyone, it's my first post here

I need a little help regarding VBA-Code:
I want to record the current back prices of all runners at a specific time e.g. 10 seconds before off.

I tried following code for first runner, but I cannot get it work:

Private Sub Startpreis()
If Tabelle1.Cells(1, 32) = 10 Then
Tabelle1.Cells(5, 34) = Tabelle1.Cells(5, 6)
Else
End If
End Sub

// cell 1,32 is my timer counting down to the start of the event
cell 5,34 is where the price shall be recorded
cell 5,6 is the current back price of first runner



I also tried this if function, but problem is, that price gets resetted to zero again. I don't know if I can avoid that resetting:
=if(AF1=10;F6;)

I only want the price recorded once, every time a market reaches e.g. 10 secs. This is because I need the starting back prices of the runners.

Does somebody know how to do this approriate?

Thanks and regards
Raiden

[Captain Sensible]

A couple of points, what is kicking off your sub routine Startpreis() ?

Maybe you should be sticking your code within a

Private Sub Worksheet_Calculate() or

Private Sub Worksheet_Change(ByVal Target As Range)

routine so it'll fire the routine on each update of the worksheet.

Secondly how fast is your refresh rate ? Sometimes it's easy to miss a refresh depending how quick you're refreshing might be best to set your parameters to cover a range depending on the refresh rate i.e.

If Tabelle1.Cells(1, 32) >= 10 AND Tabelle1.Cells(1, 32) <= 12 Then

[Raiden]

Hey, thanks for answering.

Unfortunately I'm new to sub routines. I have only created macros with functions so far.
I have tried out your tips, but the still remains blank, when 12 seconds are reached.

Private Sub Worksheet_Calculate()

If Tabelle1.Cells(1, 32) >= 10 And Tabelle1.Cells(1, 32) <= 12 Then

Tabelle1.Cells(5, 34) = Tabelle1.Cells(5, 6)
Else
End If

End Sub


I set refresh rate of 0.2 to try it out.

[Captain Sensible]

It could be due to the way you're calculating the time in cell (1,32) , remember excel treats time as a decimal value.

What do you have in cell 1,32 gruss exports the time to the off in cell D2 by default

[Raiden]

Hey,
I'm using this function, I found in the forum:

=IF(LEFT(D2)<>"-";(HOUR(D2)*3600)+(MINUTE(D2)*60)+SECOND(D2);-((HOUR(SUBSTITUTE(D2;"-";""))*3600)+(MINUTE(SUBSTITUTE(D2;"-";""))*60)+SECOND(SUBSTITUTE(D2;"-";""))))

It gives back normal Integer values:
60
59
58
...

[Captain Sensible]

Try something like this, in cell (1, 32) put in 00:00:10 as that's the 10 seconds you want to set your code to kick off at


Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

If Range("D2").value >= Range("AF1").Value Then
Tabelle1.Cells(5, 34) = Tabelle1.Cells(5, 6)
Else
End If

Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub


The odds should continually update whilst D2 is greater than 10 then stop so you'd get the closest odds to 10 seconds pre off.

Remember few races go off on time so if you want the closest odds to the off so should maybe start looking to be logging prices whilst the indicators are showing Not In Play and not suspended

[Raiden]

Thank you for the help.
I put in your new code, and the 00:00:10 and formated the cell to time format.
But still, the cell stays blank.

I was easily able to get it to show the odds with this function:
Function Startpreis(x As Integer) //I set x as the countdown cell AF1 in the spreadsheet then
If x <= 12 And x >= 10 Then
Startpreis = Tabelle1.Cells(5, 6)
Else

End If
End Function

This works fine, but it resets the price to zero when it counts down further.

[Captain Sensible]

Just try this for now just to make sure the routine is firing , it should simply put the odds into cell(5,34) on each refresh


Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


Tabelle1.Cells(5, 34) = Tabelle1.Cells(5, 6)


Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

[Raiden]

Ok, I started from zero, and now it works.

This code works perfectly:

Code: Select all

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

If Range("D2").Value >= Range("AF1").Value Then
Tabelle1.Cells(5, 34) = Tabelle1.Cells(5, 6)
Else
End If

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub


It records the price until the time I set it to record and then stops without resetting to zero. Nice.
Thank you very very much.

[howks1030]

Hi, I'm completely new to VB, so need a bit of help. I use Gary's "Place bets on a selection of horses" to place bets, but want to base the back or lay on the price at a given time. I can work out the calcs to place the bet once I have recorded the odds at the specific time, but have no idea how to use the code below to record the odds for each selection. Some races will have more than one selection

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

If Range("D2").Value >= Range("AF1").Value Then
Tabelle1.Cells(5, 34) = Tabelle1.Cells(5, 6)
Else
End If

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub