Moderator: 2020vision
by cjones198 » Thu Oct 01, 2020 10:54 am
by Captain Sensible » Thu Oct 01, 2020 11:16 am
Dim icol As Integer
icol = Worksheets("sheet1").Cells("1", Columns.Count).End(xlToLeft).Column + 1
by Captain Sensible » Thu Oct 01, 2020 11:23 am
cjones198 wrote:Sorted that! silly error hadnt put application.enable events in!
by cjones198 » Thu Oct 01, 2020 1:32 pm
by Captain Sensible » Thu Oct 01, 2020 1:51 pm
by cjones198 » Thu Oct 01, 2020 1:57 pm
by Captain Sensible » Thu Oct 01, 2020 2:00 pm
If Target.Columns.Count <> 16 Then Exit Sub
Price = Sheets("Sheet1").Cells(5, 15).Value
If Price <> Sheets("Sheet1").Cells(5, 15).Value Then
by cjones198 » Thu Oct 01, 2020 2:01 pm
by cjones198 » Thu Oct 01, 2020 2:06 pm
by cjones198 » Thu Oct 01, 2020 2:06 pm
by 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
If Price <> Sheets("Sheet1").Cells(5, 15).Value Then
.....
If Price <> Sheets("Sheet1").Cells(5, 15).Value Then
Price = Sheets("Sheet1").Cells(5, 15).Value
......
by Captain Sensible » Thu Oct 01, 2020 2:28 pm
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
by cjones198 » Thu Oct 01, 2020 2:36 pm
by cjones198 » Thu Oct 01, 2020 3:23 pm
by 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
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
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.