Record Price at specific time - Help

Find a developer for your Excel triggered betting needs and advertise your development service here.

Moderator: 2020vision

Record Price at specific time - Help

Postby Raiden » Fri Nov 11, 2011 1:13 pm

Hey lads,

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

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
Raiden
 
Posts: 8
Joined: Fri Nov 11, 2011 12:56 pm

Postby Captain Sensible » Fri Nov 11, 2011 3:41 pm

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
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Postby Raiden » Fri Nov 11, 2011 3:58 pm

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.
Raiden
 
Posts: 8
Joined: Fri Nov 11, 2011 12:56 pm

Postby Captain Sensible » Fri Nov 11, 2011 4:12 pm

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
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Postby Raiden » Fri Nov 11, 2011 4:19 pm

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
...
Raiden
 
Posts: 8
Joined: Fri Nov 11, 2011 12:56 pm

Postby Captain Sensible » Fri Nov 11, 2011 4:39 pm

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
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Postby Raiden » Fri Nov 11, 2011 4:54 pm

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.
Raiden
 
Posts: 8
Joined: Fri Nov 11, 2011 12:56 pm

Postby Captain Sensible » Fri Nov 11, 2011 5:04 pm

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
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Postby Raiden » Fri Nov 11, 2011 5:17 pm

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.
:D
Raiden
 
Posts: 8
Joined: Fri Nov 11, 2011 12:56 pm

Re: Record Price at specific time - Help

Postby howks1030 » Thu Feb 05, 2015 11:55 pm

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

Raiden wrote: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
howks1030
 
Posts: 1
Joined: Thu Feb 05, 2015 11:30 pm


Return to Find an Excel developer

Who is online

Users browsing this forum: No registered users and 10 guests

Sports betting software from Gruss Software


The strength of Gruss Software is that it’s been designed by one of you, a frustrated sports punter, and then developed by listening to dozens of like-minded enthusiasts.

Gruss is owned and run by brothers Gary and Mark Russell. Gary discovered Betfair in 2004 and soon realised that using bespoke software to place bets was much more efficient than merely placing them through the website.

Gary built his own software and then enhanced its features after trialling it through other Betfair users and reacting to their improvement ideas, something that still happens today.

He started making a small monthly charge so he could work on it full-time and then recruited Mark to help develop the products and Gruss Software was born.

We think it’s the best of its kind and so do a lot of our customers. But you can never stand still in this game and we’ll continue to improve the software if any more great ideas emerge.

cron