How to save odds in Excel

[vanbuuts]

Thanks for your assistance, I shall give it a go and let you know how it goes.

[vanbuuts]

Hi CS, the problme when adding this code is that the macro's don't run / or don't get called.

My full code is below. These trigger specific macro's based up situations occurring in play

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

 'Game goes in play
         
If Range("E2").Value = "In Play" And Range("F33").Value = "" Then
Me.Activate
             Game_Starts
                          End If

End Sub

       Private Sub Worksheet_Calculate()

'Fix pre match odds

If Range("G33").Value = 1 Then
Me.Activate
   Odds2

       End If
   
    'Fix pre match Formulas

If Range("G33").Value = 2 Then
Me.Activate
     Formulas2
       End If
       
       'After Game Changes

If Range("G33").Value = 3 Then
Me.Activate
     Reset
       End If

' Play suspends

If Range("F39").Value = 11 Then
Me.Activate
  SuspendLay
   
    End If

' Play suspends

If Range("F39").Value = 1 Then
Me.Activate
  Suspend
   
    End If

' Goal before30

If Range("F34").Value = 3 Then
Me.Activate
Early
    End If

'Run initial lay bet
If Range("F35").Value = 1 Then
Me.Activate
Initial
Freeze
    End If

    ' Lay odds matched

If Range("K9").Value = 1 Then
Me.Activate
  Lay
  End If
   

' Under dog scores first

If Range("F36").Value = 1 Then
Me.Activate
Dog
    End If
   
    ' Dog odds matched

If Range("K11").Value = 1 Then
Me.Activate
  Dog2
  End If
 
'No goal at 70 mins

If Range("F37").Value = "1" Then
Me.Activate
No_Goal
    End If

' Favourite scores first

If Range("F38").Value = 1 Then
Me.Activate
Fav
    End If
   
     ' Fav odds matched

If Range("K10").Value = 1 Then
Me.Activate
  Fav2
  End If
 
 
 ' Reset Game

If Range("N21").Value = 1 Then
Me.Activate
  Reset
  End If
 
  ' Reset Game

If Range("Q19").Value = 1 Then
Me.Activate
  Reset
  End If
   
        End Sub


[Captain Sensible]

I can't see why turning off events by including Application.EnableEvents = False should stop your sheet from running but I've no idea what all the macros do. The loo[ing is due to the sheet changing and continually triggering your Worksheet_Change. Do those writes to the sheet then set off other macros later in your code? If so have you considered storing those values as variables in memory rather than sending them to the sheet as a 1 in cell N1 etc

[Captain Sensible]

I'm not too clued up with VBA and haven't seen code like

Me.Activate
Game_Starts

I'm assuming that's some way of running the macro, I usually just use

Call Game_Starts

If i wanted to runno a macro, dunno if the activate is causing the sheet to continually fire as don't know what it does tbh

[vanbuuts]

Hi CS, the Me.Activate makes sure the code is run on the correct sheet (I sometimes have multiple markets open on different sheets).

The turning off the events means that the trigger to set the next macro off isn't fired (as you suggest) I don't know anything about storing the values as variables in the memory so I've not considered this option (as I don;t know what it is)

[Captain Sensible]

Storing variables in memory isn't hard, your Cell G33 is simply storing a variable, you'd just think of a name for the variable then Declare In Memory (Dim) i.e. just set aside a portion of the PC's memory to store the variable in, so adding

Dim Data_in_G33 As Long

Would set aside memory for a variable Data_in_G33 that stored your numbers and could be called by name IF Data_in_G33 >1 Then etc etc but it'd be best for you to sort out the running order of your macros so they don't keep triggering another event and looping rather than complicating things further for now.

I'm not sure why turning off events should stop macros from running, if one macro is dependant on another then you'd be better off combining them, if say writing 1 to N1 to the sheet triggered something even if it didn't run on that run thru it should6 still fire on the next refresh as N1 would contain 1, so I can't see why events off would bring you to a shutdown

[vanbuuts]

Thanks again for the reply, what seems to have worked (at least for now) is adding the enable.events code to each separate variable as below

Code: Select all

  Private Sub Worksheet_Calculate()

'Fix pre match odds
     
If Range("G33").Value = 1 Then
Me.Activate
Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
   Odds2
Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic

       End If
   
    'Fix pre match Formulas
       

If Range("G33").Value = 2 Then
Me.Activate
Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
     Formulas2
     Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic

       End If
       
       'After Game Changes
       

If Range("G33").Value = 3 Then
Me.Activate
Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
     Reset
     Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic

       End If

' Play suspends

If Range("F39").Value = 11 Then
Me.Activate
Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
  SuspendLay
    Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic

    End If

' Play suspends

If Range("F39").Value = 1 Then
Me.Activate
Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
  Suspend
    Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic

    End If

' Goal before30

If Range("F34").Value = 3 Then
Me.Activate
Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
Early
Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic

    End If

'Run initial lay bet
If Range("F35").Value = 1 Then
Me.Activate
Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
Initial
Freeze
Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic

    End If

    ' Lay odds matched

If Range("K9").Value = 1 Then
Me.Activate
Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
  Lay
  Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic

  End If
   

' Under dog scores first

If Range("F36").Value = 1 Then
Me.Activate
Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
Dog
Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic

    End If
   
    ' Dog odds matched

If Range("K11").Value = 1 Then
Me.Activate
Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
  Dog2
  Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic

  End If
 
'No goal at 70 mins

If Range("F37").Value = "1" Then
Me.Activate
Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
No_Goal
Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic

    End If

' Favourite scores first

If Range("F38").Value = 1 Then
Me.Activate
Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
Fav
Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic

    End If
   
     ' Fav odds matched

If Range("K10").Value = 1 Then
Me.Activate
Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
  Fav2
  Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic

  End If
 
 
 ' Reset Game

If Range("N21").Value = 1 Then
Me.Activate
Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
  Reset
  Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic

  End If
 
  ' Reset Game

If Range("Q19").Value = 1 Then
Me.Activate
Application.EnableEvents = False
        Application.Calculation = xlCalculationManual
  Reset
  Application.EnableEvents = True
        Application.Calculation = xlCalculationAutomatic

  End If
   
        End Sub


[rourkem]

Hi. I have tried this but I still not working. Am I missing something by any chance?

[Captain Sensible]

Hi. I have tried this but I still not working. Am I missing something by any chance?

You don't say what you've tried, the example vanbuuts posted doesn't include his macros. Have you got macro's enabled?

Try making a new sheet, post in this code and save as a macro enabled spreadsheet then just point it at some greyhound/racing markets. It should just move on when the market suspends and copy the runner name, pnl and last odds to sheet 2. It was just an example for vanbuuts showing bits of vba to move around, use variables and save things but he seemed to have sorted his problems. Should hopefully work OK

Code: Select all

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

If [A1].Value = MyMarket Then



   
If Range("E2").Value <> "In Play" And Range("F2").Value = "" Then
    Range("AA5:AA50").Value = Range("F5:F50").Value
End If


If Application.WorksheetFunction.IsText(Range("D2")) And Range("AB1").Value <> "Copied" And Range("F2").Value <> "" Then
On Error GoTo Switch_Market
Range("AC1").Value = Range("AC1").Value + 1

    If Range("AC1").Value > 2 Then

    Range("AB1").Value = "Copied"
    Dim LastDataCell As Long
    Dim LastMarketCell As Long
    LastDataCell = Sheets("Sheet2").Range("A3000").End(xlUp).Offset(1, 0).Row - 1
    LastMarketCell = Sheets("Sheet1").Range("A60").End(xlUp).Offset(1, 0).Row - 1
    Worksheets("Sheet1").Range("A1:A" & LastMarketCell).Copy Destination:=Worksheets("Sheet2").Range("A" & LastDataCell) 'runner name
    Worksheets("Sheet1").Range("X1:X" & LastMarketCell).Copy Destination:=Worksheets("Sheet2").Range("B" & LastDataCell) 'profit/loss
    Worksheets("Sheet1").Range("AA1:AA" & LastMarketCell).Copy Destination:=Worksheets("Sheet2").Range("C" & LastDataCell) 'last odds
    Worksheets("Sheet2").Range("D" & LastDataCell) = WorksheetFunction.Max(Worksheets("Sheet1").Range("X5:X60"))
    Worksheets("Sheet2").Range("D" & LastDataCell).NumberFormat = "£#,##0.00"
    GoTo Switch_Market
    End If
   
   
End If
   


 
   
    Else
   
    MyMarket = [A1].Value
    Range("AB1").Value = ""
    Range("Q2").Value = 2
    Range("AC1").Value = 0
End If





Xit:
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Exit Sub
   
Switch_Market:
    Range("Q2").Value = -1
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
   
End Sub

[vanbuuts]

Hi CS, yep the enable events and calculation mode have helped my sheet work now (but as previously mentioned I had to add them to each individual macro call routine).

One thing I have noticed, if one of my bet triggers is called, it often triggers the bet before the odds market has "settled". So say for instance a goal is scored in a football game, the odds on the draw market maybe 1.10 to back and 300.0 to lay. As my bet odds are based around the "current" back odds + 2 ticks this means my bet tries to place at 1.12. Is there a way to delay the bet placement for 30 seconds until the market has re-stabilized? I tried the

Code: Select all

Application.Wait Now + TimeValue("00:00:30")

variable, but the screen didn't refresh during this time, it simply delayed the macro placing the trigger by 30 seconds without updating the odds

[Captain Sensible]

You could always send a timestamp to a cell and include as another criteria it in your betting trigger IF statement, i.e. wherever you had

Application.Wait Now + TimeValue("00:00:30")

just send a timestamp to say Y5

Range("Y5").Value = Now

Then use that timestamp to fire the next macro something like

If Now >= Range("AC1").Value + TimeValue("00:00:30") And Range("AC1").Value <> "" Then Range("AD1").Value = "Fire Macro"


The Range("AC1").Value <> "" is just there to stop it whilst nothing is in AC1 just add whatever criteria you need but it's basically to show you can timestamp cells to use rather than Application.Wait which will just freeze the whole sheet

[vanbuuts]

Nice idea, I will have a look at that

[vanbuuts]

In response to the delaying of bet placement, I have found a good coded solution (should anyone else be interested).

Placing the following code before your macro code will delay the placement by 5 seconds - but allow the odds to continually refresh (unlike the application.wait code). Obviously the 5 can be changed as required

Code: Select all

Dim StartTime As Single
 
    StartTime = Timer
     
      Do While Timer - StartTime <= 5
        DoEvents
      Loop