how to automatically record markets in a quick list

[Captain Sensible]

No problem.

Later on you might want to start moving those little routines to sub routines in modules and switching to public variables so you can start adding error trapping and how to deal with closed markets or shutting down when you reach the last market etc.

[cjones198]

Sorry to bother XCaptain, ive noticed its recording the price when ever anything changes on the sheet i only want it to change when the price does only

[Captain Sensible]

worksheet_change fires the VBA code on each refresh because something like the time to the off will have changed. If you only want to log data when a cell value changes there are a few ways to do it but because you sheet is running other things like switching markets etc the easiest way is to simply log the cell value into a Public/static variable so it's always retained. Then you simply only run the code when that value has changed, much the same as the myMarket routine runs


If old_price <> F5 then
run your logging code
old_price=F5
end if

That way that snippet will only ever run when the price in F5 has changed

[cjones198]

Thanks

also its started skipping again lol. when it goes in play it skips to the 3rd market. the basic code is

Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant
Static Price As Variant
Static iCol As Variant
If Target.Columns.Count <> 16 Then Exit Sub

Application.EnableEvents = False ' stopping events here mean we don't need to repeat it elsewhere
Application.Calculation = xlCalculationManual '

If Worksheets("Market").Range("A1").Value <> MyMarket Then
MyMarket = Worksheets("Market").Range("A1").Value
switchMarket = False

' In here we'd set up all the new variables to setup a new market and it would only run once
Call startstats
Price = Sheets("Market").Cells(4, 15).Value 'store price
iCol = 4 'store col number
Sheets("Selection").Rows(10).Clear 'clear recorded row
Sheets("Selection").Rows(11).Clear 'clear recorded row

End If


'Here would be the main section of the code where we would call sub routines that possibly run on each refresh i.e Call Record routines

[b][b](When i entered in here my tick increment number, it resulted in the price keep on getting loggied into (iCol) when ever anything changed!)
in a nut shell on sheet labelled Selection i enter tick increments i wish to log, 2,3 4 etc. and i have a cell which then x that by the tick increments depending on current price so for example if the horse is price is 3.50 i have a tick increment of 0.05, if its price is say 6.4 then the increment is 0.2 etc etc.) [/b][/b]



If Price <> Sheets("Market").Cells(5, 15).Value Then
Sheets("Selection").Cells(4, 5).Value = Sheets("Market").Cells(5, 15).Value
Sheets("Selection").Cells(5, 5).Value = Sheets("Market").Cells(5, 16).Value

Sheets("Selection").Cells(10, iCol).Value = Sheets("Market").Cells(5, 15).Value
Sheets("Selection").Cells(11, iCol).Value = Sheets("Market").Cells(5, 16).Value
Price = Sheets("Market").Cells(5, 15).Value
iCol = iCol + 1
End If

If Worksheets("Market").Range("E2").Value = "In Play" And switchMarket = False Then

Worksheets("Market").Range("Q2").Value = -1
switchMarket = True

End If

Application.EnableEvents = True ' turn events back on
Application.Calculation = xlCalculationAutomatic
End Sub

[cjones198]

tick increment code,

If Sheets("Selection").Cells(6, 4).Value >= "0.5" Then 'if 2 or more ticks selected

If Sheets("Selection").Cells(4, 4).Value >= 1.01 And Sheets("Selection").Cells(4, 4).Value <= 2 Then
Sheets("Selection").Cells(1, 10).Value = "0.01"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 2.02 And Sheets("Selection").Cells(4, 4).Value <= 3 Then
Sheets("Selection").Cells(1, 10).Value = "0.02"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 3.05 And Sheets("Selection").Cells(4, 4).Value <= 4 Then
Sheets("Selection").Cells(1, 10).Value = "0.05"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 4.1 And Sheets("Selection").Cells(4, 4).Value <= 6 Then
Sheets("Selection").Cells(1, 10).Value = "0.1"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 6.2 And Sheets("Selection").Cells(4, 4).Value <= 10 Then
Sheets("Selection").Cells(1, 10).Value = "0.2"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 10.5 And Sheets("Selection").Cells(4, 4).Value <= 20 Then
Sheets("Selection").Cells(1, 10).Value = "0.5"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 21 And Sheets("Selection").Cells(4, 4).Value <= 30 Then
Sheets("Selection").Cells(1, 10).Value = "1"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 32 And Sheets("Selection").Cells(4, 4).Value <= 50 Then
Sheets("Selection").Cells(1, 10).Value = "2"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 55 And Sheets("Selection").Cells(4, 4).Value <= 100 Then
Sheets("Selection").Cells(1, 10).Value = "5"
ElseIf Sheets("Selection").Cells(4, 4).Value >= 110 And Sheets("Selection").Cells(4, 4).Value <= 1000 Then
Sheets("Selection").Cells(1, 10).Value = "10"
End If 'work out the tick increments

[Captain Sensible]

Can't see anywhere your entering tick increments in the code you posted or why that should affect the line beow as it's not changing it.

Code: Select all

If Price <> Sheets("Market").Cells(5, 15).Value Then

Here's a load of functions to deal with ticks you can simply copy them to a module and they'll come in handy later down the line for you.

Code: Select all

Function getValidOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1 To 2
oddsInc = 0.01
Case 2 To 3
oddsInc = 0.02
Case 3 To 4
oddsInc = 0.05
Case 4 To 6
oddsInc = 0.1
Case 6 To 10
oddsInc = 0.2
Case 10 To 20
oddsInc = 0.5
Case 20 To 30
oddsInc = 1
Case 30 To 50
oddsInc = 2
Case 50 To 100
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
If Math.Round(odds + oddsInc, 2) <= 1000 Then
getValidOdds = Round(odds / oddsInc, 0) * oddsInc
Else
getValidOdds = 1000
End If

If getValidOdds = 1 Then getValidOdds = 1.01

End Function



Function getPrevOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1.01 To 2
oddsInc = 0.01
Case 2.02 To 3
oddsInc = 0.02
Case 3.05 To 4
oddsInc = 0.05
Case 4.1 To 6
oddsInc = 0.1
Case 6.2 To 10
oddsInc = 0.2
Case 10.5 To 20
oddsInc = 0.5
Case 21 To 30
oddsInc = 1
Case 32 To 50
oddsInc = 2
Case 55 To 100
oddsInc = 5
Case 110 To 1000
oddsInc = 10
End Select
If Math.Round(odds - oddsInc, 2) >= 1.01 Then
getPrevOdds = Math.Round(odds - oddsInc, 2)
Else
getPrevOdds = 1.01
End If
End Function

Function getNextOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.98
oddsInc = 0.02
Case 3 To 3.95
oddsInc = 0.05
Case 4 To 5.9
oddsInc = 0.1
Case 6 To 9.8
oddsInc = 0.2
Case 10 To 19.5
oddsInc = 0.5
Case 20 To 29
oddsInc = 1
Case 30 To 48
oddsInc = 2
Case 50 To 95
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
If Math.Round(odds + oddsInc, 2) <= 1000 Then
getNextOdds = Math.Round(odds + oddsInc, 2)
Else
getNextOdds = 1000
End If
End Function

Function plusTicks(odds As Currency, ticks As Byte) As Currency
Dim i As Byte
For i = 1 To ticks
odds = getNextOdds(odds)
Next
plusTicks = odds
End Function

Function minusTicks(odds As Currency, ticks As Byte) As Currency
Dim i As Byte
For i = 1 To ticks
odds = getPrevOdds(odds)
Next
minusTicks = odds
End Function




Function getOddsStepUp(ByVal odds As Currency) As Currency

Dim oddsInc As Currency
Select Case odds
Case 1 To 1.99
oddsInc = 0.01
Case 2 To 2.98
oddsInc = 0.02
Case 3 To 3.95
oddsInc = 0.05
Case 4 To 5.9
oddsInc = 0.1
Case 6 To 9.8
oddsInc = 0.2
Case 10 To 19.5
oddsInc = 0.5
Case 20 To 29
oddsInc = 1
Case 30 To 48
oddsInc = 2
Case 50 To 95
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
getOddsStepUp = oddsInc
End Function

Function getOddsStepDown(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1.01 To 2
oddsInc = 0.01
Case 2.02 To 3
oddsInc = 0.02
Case 3.05 To 4
oddsInc = 0.05
Case 4.1 To 6
oddsInc = 0.1
Case 6.2 To 10
oddsInc = 0.2
Case 10.5 To 20
oddsInc = 0.5
Case 21 To 30
oddsInc = 1
Case 32 To 50
oddsInc = 2
Case 55 To 100
oddsInc = 5
Case 110 To 1000
oddsInc = 10
End Select

getOddsStepDown = oddsInc
End Function



Function getTicks(odds1 As Currency, odds2 As Currency) As Single
odds1 = getValidOdds(odds1)
odds2 = getValidOdds(odds2)
Dim i As Double
Dim tickCount As Single
Dim thisStep As Double
Dim thisodds As Double

Select Case odds2
Case Is < 1.01, Is > 1000
GoTo xit
End Select

Select Case odds1
Case Is < 1.01, Is > 1000
GoTo xit

Case Is < odds2
    tickCount = 0
    i = odds1
    Do While i <> odds2
    thisStep = getOddsStepUp(i)
    i = i + thisStep
    tickCount = tickCount + 1
    Loop
 getTicks = tickCount
 
 Case Is > odds2
    tickCount = 0
    i = odds1
    Do While i <> odds2
    thisStep = getOddsStepDown(i)
    i = i - thisStep
    tickCount = tickCount + 1
    Loop
getTicks = tickCount - (tickCount * 2)

Case Is = odds2
getTicks = 0
End Select

xit:
End Function

You can then simply use them to get the amount of ticks between two prices and fire your copy code if needed like so

Code: Select all

If Abs(getTicks(old_price,F5)>2 then ' using Absolute to get negative or positive changes
run copy code
old_price=F5
end if


[cjones198]

Thanks Captain

I take it this a far more efficient way than mine lol, I'm very new to vba so just hashing my way through it

[cjones198]

Lets take this example,
how is working?

would i just call it within worksheet change everytime price changes?

Function getValidOdds(ByVal odds As Currency) As Currency
Dim oddsInc As Currency
Select Case odds
Case 1 To 2
oddsInc = 0.01
Case 2 To 3
oddsInc = 0.02
Case 3 To 4
oddsInc = 0.05
Case 4 To 6
oddsInc = 0.1
Case 6 To 10
oddsInc = 0.2
Case 10 To 20
oddsInc = 0.5
Case 20 To 30
oddsInc = 1
Case 30 To 50
oddsInc = 2
Case 50 To 100
oddsInc = 5
Case 100 To 1000
oddsInc = 10
End Select
If Math.Round(odds + oddsInc, 2) <= 1000 Then
getValidOdds = Round(odds / oddsInc, 0) * oddsInc
Else
getValidOdds = 1000
End If

If getValidOdds = 1 Then getValidOdds = 1.01

End Function

[Captain Sensible]

getValidOdds() would just take whatever number we put into it and return us the valid odds for betfair to process. Say we tried to send a bet with odds of 3.6666 because of some calculation we can use getValidOdds to give us a correct increment either on an excel sheet cell or via VBA

VBA functions will work on the spreadsheet or in VBA

So in cell A1 if we entered
=getValidOdds(3.6666)
A1 would show 3.65

in VBA we'd just say

price = getValidOdds(3.6666)

and price would now hold 3.65. Obviously your 3.6666 can come from a calculation = getValidOdds(A1+2*A3)

[cjones198]

Thanks Captain
I'm sure I will use them in the future!
I tried get my own recording stuff going today but just ended up getting in a right old mess ha ha.
Thanks for the help!!

[Captain Sensible]

No problem. The tick routines just make life easier as you can get the ticks between prices , add ticks or take them away etc.

Most of us record logfiles of races so if you have problems with recording data they'll be loads of code snippets on forum or just ask as it's rarely much more complicated than saying

Range("A1").Value = Range("b1").value

and decding if you want the record to be ascending or descending

[cjones198]

Thanks Captain

I just need to log price changes based on however many tick increments I choose, so for example it's generally at least 2.

So say if I open up a race and it starts at 2.50
Then when it hits 2.54 it logs, then 2.58 etc etc.

Amongst that code you sent the best one would be the one that rounds a number up to nearest real tick increment,

[Captain Sensible]

All you'd need to use is the getTicks function and use the code is posted before, obviously the 2 could also be a variable if you want it to change

Code: Select all

If Abs(getTicks(old_price,new_price) >=2 then
run copy code
old_price=new_price
end if

Basically getticks will find the number of ticks between two prices, we use ABS(olute) because that gets the absoulute number only so it could be -2 or 2. If your prices weren't coming from the exchange so they might not be valid tick increments you'd simply wrap them inside getValidOdds.

[cjones198]

is there anything wrong with this? its still skipping 2 races

Private Sub Worksheet_Change(ByVal Target As Range)
Static MyMarket As Variant
Static Price As Variant
Static iCol As Variant
If Target.Columns.Count <> 16 Then Exit Sub

Application.EnableEvents = False ' stopping events here mean we don't need to repeat it elsewhere
Application.Calculation = xlCalculationManual '

If Worksheets("Market").Range("A1").Value <> MyMarket Then
MyMarket = Worksheets("Market").Range("A1").Value
switchMarket = False

' In here we'd set up all the new variables to setup a new market and it would only run once
Call startstats
Price = Sheets("Market").Cells(4, 15).Value 'store price
iCol = 4 'store col number
Sheets("Selection").Rows(10).Clear 'clear recorded row
Sheets("Selection").Rows(11).Clear 'clear recorded row

End If


'Here would be the main section of the code where we would call sub routines that possibly run on each refresh i.e Call Record routines


If Price <> Sheets("Market").Cells(5, 15).Value Then
Sheets("Selection").Cells(4, 5).Value = Sheets("Market").Cells(5, 15).Value
Sheets("Selection").Cells(5, 5).Value = Sheets("Market").Cells(5, 16).Value

Sheets("Selection").Cells(10, iCol).Value = Sheets("Market").Cells(5, 15).Value
Sheets("Selection").Cells(11, iCol).Value = Sheets("Market").Cells(5, 16).Value
Price = Sheets("Market").Cells(5, 15).Value
iCol = iCol + 1
End If

If Worksheets("Market").Range("E2").Value = "In Play" And switchMarket = False Then

Worksheets("Market").Range("Q2").Value = -1
switchMarket = True

End If

Application.EnableEvents = True ' turn events back on
Application.Calculation = xlCalculationAutomatic
End Sub

[Captain Sensible]

Have you declared switchMarket as a public variable anywhere? It doesn't appear anywhere in the code you've posted so that means it will always be set to false each time the worksheet change runs.