how to automatically record markets in a quick list

Discuss anything related to using the program (eg. triggered betting tactics)

Moderator: 2020vision

Re: how to automatically record markets in a quick list

Postby cjones198 » Thu Oct 01, 2020 3:48 pm

Thanks Captain

i thought i did, the formula for iCol, when i inserted it at the end of the loop berfore theNext

it just made things plot in every other cell.

i will keep experimenting and reading up on stuf.
cjones198
 
Posts: 102
Joined: Wed Aug 05, 2020 10:02 pm

Re: how to automatically record markets in a quick list

Postby cjones198 » Thu Oct 01, 2020 3:53 pm

one more question sorry if you dont mind?

why on this set of code does change occur when anything changes in the worksheet? rather than just what happens when the price changes?
cjones198
 
Posts: 102
Joined: Wed Aug 05, 2020 10:02 pm

Re: how to automatically record markets in a quick list

Postby Captain Sensible » Thu Oct 01, 2020 5:30 pm

Worksheet_Change registers every change on the worksheet so if any cell is changed it will fire off the code. That code had nothing to stop routines from firing when prices changed etc or even when only certain cells changed.

Here's the code tweaked so you can link it to BA and use live data. Just remember if you want to play around with it manually you have to comment out the line If Target.Columns.Count <> 16 Then Exit Sub

Code: Select all
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Dim i As Integer, end_of_loop As Integer
Dim direction As String: direction = ""
Static old_price As Currency, volume As Currency, MyMarket As Variant


If MyMarket <> Worksheets("Sheet1").Cells(1, 1).Value Then
'let's set the market variables

old_price = Worksheets("Sheet1").Cells(5, 15).Value
volume = Worksheets("Sheet1").Cells(5, 16).Value
MyMarket = Worksheets("Sheet1").Cells(1, 1).Value
Worksheets("Sheet2").Rows("10:11").ClearContents
End If

Dim iCol As Integer
iCol = Worksheets("sheet2").Cells("10", Columns.Count).End(xlToLeft).Column + 1

i = getTicks(old_price, Sheets("Sheet1").Cells(5, 15).Value)
If i >= 0 Then direction = "up"
i = Abs(i)
end_of_loop = WorksheetFunction.floor(i, 2)


If i >= 2 And Worksheets("Sheet1").Cells(2, 5).Value = "Not In Play" Then


For i = 2 To i Step 2

    If direction = "up" Then
        Worksheets("Sheet2").Cells(10, iCol).Value = plusTicks(old_price, 2)
    Else
        Worksheets("Sheet2").Cells(10, iCol).Value = minusTicks(old_price, 2)
    End If

    If i = end_of_loop Then
    Worksheets("Sheet2").Cells(11, iCol).Value = Sheets("Sheet1").Cells(5, 16).Value - volume
    Else
    Worksheets("Sheet2").Cells(11, iCol).Value = 0
    End If

    iCol = iCol + 1

Next i

old_price = Sheets("Sheet1").Cells(5, 15).Value
volume = Sheets("Sheet1").Cells(5, 16).Value

End If



Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: how to automatically record markets in a quick list

Postby cjones198 » Thu Oct 01, 2020 6:42 pm

Thanks Captain!!!

i take it this is the code that stops it firing everytime anything changes?

If MyMarket <> Worksheets("Sheet1").Cells(1, 1).Value Then
'let's set the market variables

old_price = Worksheets("Sheet1").Cells(5, 15).Value
volume = Worksheets("Sheet1").Cells(5, 16).Value
MyMarket = Worksheets("Sheet1").Cells(1, 1).Value
Worksheets("Sheet2").Rows("10:11").ClearContents
End If
cjones198
 
Posts: 102
Joined: Wed Aug 05, 2020 10:02 pm

Re: how to automatically record markets in a quick list

Postby Captain Sensible » Thu Oct 01, 2020 7:08 pm

Worksheet_Change fires off your code everytime something changes on the sheet

If Target.Columns.Count <> 16 Then Exit Sub

exits the routine early if less than 16 columns ( a price refresh from BA) have changed so the rest of the code won't run.

If 16 columns have updated then it passes that part of code and the rest runs, variables get set etc. What happens is then all dependant on whether your IF statements are true or false. But basically the code will now run line by line

Code: Select all
If MyMarket <> Worksheets("Sheet1").Cells(1, 1).Value Then
'let's set the market variables

old_price = Worksheets("Sheet1").Cells(5, 15).Value
volume = Worksheets("Sheet1").Cells(5, 16).Value
MyMarket = Worksheets("Sheet1").Cells(1, 1).Value
Worksheets("Sheet2").Rows("10:11").ClearContents
End If


If it's a new market that code runs and sets up your starting values.


Code: Select all
If i >= 2 And Worksheets("Sheet1").Cells(2, 5).Value = "Not In Play" Then

............

End If


That code sticks your volume/ prices etc into sheet 2 if i (your amount of ticks) is greater or equal to 2 and the market is Not In Play. You can easily add more conditions if you want .
User avatar
Captain Sensible
 
Posts: 2883
Joined: Sat Nov 19, 2005 2:29 pm

Re: how to automatically record markets in a quick list

Postby cjones198 » Thu Oct 01, 2020 7:09 pm

got it!! thanks Captain, youve been a massive help
cjones198
 
Posts: 102
Joined: Wed Aug 05, 2020 10:02 pm

Previous

Return to Discussion

Who is online

Users browsing this forum: No registered users and 35 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