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 10:54 am

How docile get that iCol + 1 after every loop I tried iCol = iCol + 1 but it didnt work, I used a countif function and set iCol + that cell.
Because each loop could give more than 1 cells worth of information I take it that I have to use a countif function of some sort?
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 11:16 am

Everytime you run your worksheet_change you set iCol = 4.

So you either need to store that as a one off event or just get the first epty cell in the row with something like

Code: Select all
Dim icol As Integer
icol = Worksheets("sheet1").Cells("1", Columns.Count).End(xlToLeft).Column + 1
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 Captain Sensible » Thu Oct 01, 2020 11:23 am

cjones198 wrote:Sorted that! silly error hadnt put application.enable events in!


it's worth getting into the habit of turning off EnableEvents , screenupdating and xcalculation within any worksheet_event code.
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 1:32 pm

thanks Captain ive cobbled this together and nothing happens at all?
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Static i As Integer, end_of_loop As Integer
Static direction As String: direction = ""
Static Price As Currency, volume As Currency
Static iCol As Integer
Static MyMarket 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 '


iCol = Worksheets("sheet1").Cells("4", Columns.Count).End(xlToLeft).Column + 1
Price = Sheets("Sheet1").Cells(5, 15).Value

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


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


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



For i = 2 To i Step 2

If direction = "up" Then
Worksheets("Sheet2").Cells(10, iCol + i / 2).Value = " " & plusTicks(Price, 2)
Else
Worksheets("Sheet2").Cells(10, iCol + i / 2).Value = " " & minusTicks(Price, 2)
End If

If i = end_of_loop Then
Worksheets("Sheet2").Cells(11, iCol + i / 2).Value = volume
Else
Worksheets("Sheet2").Cells(11, iCol + i / 2).Value = 0
End If

Next i
End If
Application.EnableEvents = True ' turn events back on
Application.Calculation = xlCalculationAutomatic

End Sub
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 1:51 pm

I don't know what you mean by nothing happens at all, is your sheet attached to BA and no data being received by the sheet?

I see the iCol method is messed up but that wouldn't mean nthing at all happens.
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 1:57 pm

yeah its not linked, im just manually changing cell(5,15)
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 2:00 pm

In your code you have a line

Code: Select all
If Target.Columns.Count <> 16 Then Exit Sub


Unless you're changing 16 columns manually it will simply exit the routine, also the most obvious errors in your code are the lines

Code: Select all

Price = Sheets("Sheet1").Cells(5, 15).Value

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


If you set the variable price using a cell it is never going to be different to that cell value later on in the code.
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 2:01 pm

ive linked it to Grus BA and data is received but nothing calculates and logs
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 2:06 pm

in my sheet that works
price is Price = Sheets("Market").Cells(5, 15).Value 'store price

and code is
If getTicks(Price, Sheets("Market").Cells(5, 15).Value) > 1 And Sheets("Selection").Cells(11, iCol - 1).Value = "START" Then 'if the price changes from last price record and move on to next col
Sheets("Selection").Cells(10, iCol).Value = Sheets("Market").Cells(5, 15).Value

and it works
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 2:06 pm

its weird to me lol
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 2:25 pm

cjones198 wrote:ive linked it to Grus BA and data is received but nothing calculates and logs


Like I said you're setting your variable before you compare it so it'll never get past the line

Code: Select all
If Price <> Sheets("Sheet1").Cells(5, 15).Value Then
.....


If you want to store the value you need to set it after the If , not before. You're basically saying x = 2, if x is not equal to 2 do something.


Code: Select all
If Price <> Sheets("Sheet1").Cells(5, 15).Value Then
Price = Sheets("Sheet1").Cells(5, 15).Value
......
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 Captain Sensible » Thu Oct 01, 2020 2:28 pm

Also your rutine for iCol should be incremented like so, it shouldn't be a static variable either, to be honest most of them shouldn't be static as you don't want to retain most of their values for the next time the code runs.

Code: Select all
Dim iCol As Integer
iCol = Worksheets("sheet1").Cells("10", Columns.Count).End(xlToLeft).Column + 1


For i = 2 To i Step 2

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

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

    iCol = iCol + 1

Next i
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 2:36 pm

Thanks Captain i can see that now! cheers! thanks for all the help
The trouble is I've managed to write up codes and just bodged them and bodged them until they work.

Obviously i have an okish idea of how things work but, obviously need to improve a lot, just purely to make life easier.
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:23 pm

Im stumped now lol!! after its looped a price change i want it it to do the next price log in the next empty column in row 10, but nothing works lol,

i tried putting iCol = iCol +1 where you suggested but it just puts a cell gap between the price logs

so id want
loop 1 = price 2.50 - 2.54

cell a 10 = 2.54

loop2 2.54 - 2.32
cell b10 2.58
cell c10 2.32
etc etc

i even tried countif cells >0 for row 10 and linking iCol to that e.g iCol = 1 + countif cell value

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Static i As Integer, end_of_loop As Integer
Static direction As String: direction = ""
Static Price As Currency, volume As Currency
Dim iCol As Integer
Static MyMarket As Variant
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
'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("Sheet1").Range("A1").Value <> MyMarket Then
MyMarket = Worksheets("Sheet1").Range("A1").Value


iCol = Worksheets("sheet2").Cells("10", Columns.Count).End(xlToLeft).Column + 1
Price = Sheets("Sheet1").Cells(5, 15).Value

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


End If

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



For i = 2 To i Step 2

If direction = "up" Then
Worksheets("Sheet2").Cells(10, iCol + i / 2).Value = " " & plusTicks(Price, 2)
Else
Worksheets("Sheet2").Cells(10, iCol + i / 2).Value = " " & minusTicks(Price, 2)
End If

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

ws.Range("A1") = Application.WorksheetFunction.CountIf(ws.Range("A10:ZZ10"), ">" & ws.Range("A2"))

Next i


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

End Sub
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 3:37 pm

cjones198 wrote:Im stumped now lol!! after its looped a price change i want it it to do the next price log in the next empty column in row 10, but nothing works lol,

i tried putting iCol = iCol +1 where you suggested but it just puts a cell gap between the price logs

so id want
loop 1 = price 2.50 - 2.54

cell a 10 = 2.54

loop2 2.54 - 2.32
cell b10 2.58
cell c10 2.32
etc etc

i even tried countif cells >0 for row 10 and linking iCol to that e.g iCol = 1 + countif cell value

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Static i As Integer, end_of_loop As Integer
Static direction As String: direction = ""
Static Price As Currency, volume As Currency
Dim iCol As Integer
Static MyMarket As Variant
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")
'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("Sheet1").Range("A1").Value <> MyMarket Then
MyMarket = Worksheets("Sheet1").Range("A1").Value


iCol = Worksheets("sheet2").Cells("10", Columns.Count).End(xlToLeft).Column + 1
Price = Sheets("Sheet1").Cells(5, 15).Value

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


End If

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



For i = 2 To i Step 2

If direction = "up" Then
Worksheets("Sheet2").Cells(10, iCol + i / 2).Value = " " & plusTicks(Price, 2)
Else
Worksheets("Sheet2").Cells(10, iCol + i / 2).Value = " " & minusTicks(Price, 2)
End If

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

ws.Range("A1") = Application.WorksheetFunction.CountIf(ws.Range("A10:ZZ10"), ">" & ws.Range("A2"))

Next i


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

End Sub



There's nothing in that code to increment iCol i.e. iCol = iCol + 1

You probably need to spend a bit of time understanding how each part works rather than trying to bodge code together. I guess coding is like any language if you learn bad habits early on they're hard to break. What you're trying to achieve isn't very hard especially as the hardest part the tick routines have already been written by Gary. Just try things out on some kind of test sheet before going live as you're just overcomplicating things with countifs etc

Here's the code you posted earlier tweaked to increment iCol

Code: Select all
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim i As Integer, end_of_loop As Integer
Dim direction As String: direction = ""
Dim old_price As Currency, new_price As Currency, volume As Currency
Dim iCol As Integer


iCol = Worksheets("sheet1").Cells("10", Columns.Count).End(xlToLeft).Column + 1
old_price = Sheets("Sheet1").Cells(1, 1).Value
new_price = Sheets("Sheet1").Cells(2, 1).Value
volume = Sheets("Sheet1").Cells(3, 1).Value

i = getTicks(old_price, new_price)
If i >= 0 Then direction = "up"
i = Abs(i)
end_of_loop = WorksheetFunction.floor(i, 2)



For i = 2 To i Step 2

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

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

    iCol = iCol + 1

Next i

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

PreviousNext

Return to Discussion

Who is online

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