How do I record all the odds

[MiniBlueDragon]

To be honest I think even if I have to pay more commission to use the BF assistant instead of BD one it'd be worth it so I'm going to keep developing this workbook anyway. Do you know of a way to count the number of races in the quick pick list Cap? That'd let me create a loop for x number of iterations.

[Captain Sensible]

At the end of the day anything automated that makes money is worth it regardless of what site it uses, use both if it's profitable.

Cell J3 contains details about the amount of races left so it's easier just to use that value, it shows L for the last market and F for the first, mine just stuck -5 at the end of the routine if J3 contained L so it's resume from the start and -8 (to remove a market) if the status was closed

[Captain Sensible]

There's also an option to export the quicklist to a separate sheet when logging to excel, it'd be easy enough to count those if the J3 cell didn't suit your needs

[xiaonvren]

Do you know of a way to count the number of races in the quick pick list Cap? That'd let me create a loop for x number of iterations.

[Joe68]

I use that option for exporting the Quick Pick List mentioned by Capt. S - it works well.

Just write some code to count the races there. Should be straightforward.

[MiniBlueDragon]

Yep that's what I did in the end:

Code: Select all

'Count number of races
numberRaces = Worksheets("Gruss_QuickPickList").Range(Worksheets("Gruss_QuickPickList").Range("A1"), Worksheets("Gruss_QuickPickList").Range("A65535").End(xlUp)).Count - 1

The issue was that I use Betting Assistant for BetDaq and logging to a sheet wasn't an option. I've not subscribed and moved over to BetFair; even if I pay 2% more commission it's not a bank-breaker and I can now automate better.

My current boggle is trying to figure out why my vba works when I step through it with F8 but when I run it via button click it just keeps grabbing and pasting the current race's odds in succession:

Stepped:

Code: Select all

If In Doubt   1.4
Askamore Darsi   6.2
Lord Brendy   9.8
Volo Mio   26
Shackled N Drawn   1.92
Middle East Pearl   2.6
One Moment   10
Knockamany Bends   3.25
Lazy Sioux   3.6
Satellite Express   3.75
Cookie Ring   8.6
Bold Max   23

Run:

Code: Select all

If In Doubt   1.11
Askamore Darsi   10
Lord Brendy   210
Volo Mio   85
If In Doubt   1.07
Askamore Darsi   10
Lord Brendy   160
Volo Mio   75
Shackled N Drawn   1.9
Middle East Pearl   2.64
One Moment   10
Knockamany Bends   3.3
Lazy Sioux   3.65
Satellite Express   3.7
Cookie Ring   8.6
Bold Max   23
Junius Brutus   4.3
My Teescomponents   4.9
Chitu   4.6
Along Came Theo   8.2
Bear Island Flint   8.8
Master Moon   10
The Peaky Blinder   46
Junius Brutus   4.3
My Teescomponents   4.9
Chitu   4.6
Along Came Theo   8.2
Bear Island Flint   8.8

Kinda feels like the code is skipping ahead of the race selection so it's repeating the copy/paste before the race is actually showing in Excel.

[Captain Sensible]

Without seeing the code it's hard to second guess why it's not working as you expect. Are you using the Betdaq or Betfair versions? Also do you have events/calculations disabled to stop the code from looping within itself, also it's worth noting that BA does two writes to excel, one for prices one for market data that's why you'll see

Code: Select all

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

in lots of peoples code so it only executes any worksheet_change routines when there's a price refresh.

[MiniBlueDragon]

Yeah the target columns 16 I use for any type of code that I need to run when the market changes but with this I assumed that as I need to run it through a single loop I'd be better off with a simple button_press:

Code: Select all

Sub Button2_Click()
    ' Define all variables
    Dim runningFlag As Variant
    Dim numberRaces As Variant
    Dim numberRunners As Variant
    Dim lastCell As Variant
    Dim pasteStartRow As Variant
    Dim pasteEndRow As Variant
    Dim i As Variant
   
    ' Switch off calculations to speed up routines
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
   
    ' Set running flag
    runningFlag = 1
   
    ' Clear Existing Odds
    Sheets("Odds").Range("A:B").Clear
   
    ' Set first paste row to 1
    pasteStartRow = 1
   
    'Select first race in Quick Pick List
    Sheets("Gruss").Range("Q2").Value = "-5"
   
    ' Wait for sheet to refresh
    Application.Wait (Now + TimeValue("00:00:03"))
   
    'Count number of races
    numberRaces = Worksheets("Gruss_QuickPickList").Range(Worksheets("Gruss_QuickPickList").Range("A1"), Worksheets("Gruss_QuickPickList").Range("A65535").End(xlUp)).Count - 1
   
    ' Start looping through races
    For i = 1 To numberRaces
        ' Count the number of runners
        Worksheets("Gruss").Range("F5").Select
        lastCell = ActiveCell.End(xlDown).Row
        numberRunners = lastCell - 4
       
        ' Set pasteEndRow
        pasteEndRow = pasteStartRow + (numberRunners - 1)

        ' Copy runner names to Odds sheet
        Worksheets("Gruss").Range("A5:A" & lastCell).Copy Destination:=Worksheets("Odds").Range("A" & pasteStartRow & ":A" & pasteEndRow)
       
        ' Copy runner odds to Odds sheet
        Worksheets("Gruss").Range("F5:F" & lastCell).Copy Destination:=Worksheets("Odds").Range("B" & pasteStartRow & ":B" & pasteEndRow)
       
        ' Set row to next blank row
        pasteStartRow = pasteStartRow + numberRunners
       
        ' Select next race from Quick Pick list
        Sheets("Gruss").Range("Q2").Value = "-1"
       
        ' Wait for sheet to refresh
        'Application.Wait (Now + TimeValue("00:00:05"))
       
        Dim time1, time2

        time1 = Now
        time2 = Now + TimeValue("0:00:03")
        Do Until time1 >= time2
            DoEvents
            time1 = Now()
        Loop
    Next i
   
    ' Reset variables
    pasteStartRow = Null
    pasteEndRow = Null
    lastCell = Null
    numberRunners = Null
    numberRaces = Null
    i = Null
    runningFlag = 0

    ' Switch calculations back on
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

I've added two types of "pause" in the code to try and let BA catch up before executing the next loop but even that's not helping. Definitely seems to be that the code's looping quicker than the market change time though I think?

[Captain Sensible]

I'd have thought gruss would wait for the VBA routine to finish before moving onto the next refresh so maybe the -1 to move markets isn't getting picked up that's why I prefer to have a worksheet_change event kicking everything as a separate loop then using the F or L in J3 to resume looping.

[MiniBlueDragon]

Stepping through the code the -1 is definitely being picked up because it skips to the worksheet_change event for the refresh and then resumes the code from there.

I think because I only need to run it one time it's definitely best being kicked off with a button. That would also allow me to clear all the existing data and count the number of races for the day.

I reckon you might be right with the for loop being best suited to the worksheet_change so I'll have a think about the best way to code it.

[Captain Sensible]

You could still have a button, just have the button set a flag say a "Y" in AA1 and have the worksheet change routine only fire if AA1 contains "Y" as soon as it hits the last market J3 shows "L" have it reset the flag to "N" and return to the start by sticking -5 in Q2. Can still have the routine as a sub routine just use and IF to fire it.

If Range("AA1").Value = "Y" Then Call routine

I have plenty of buttons calling up macros on my sheets to either suspend betting,reset markets, clear data etc to override things when I need to

[MiniBlueDragon]

Genius idea. I had a runningFlag set to 1 at the beginning and 0 at the end but couldn't see how I could read that variable from another method. Just using the actual sheet makes far more sense. Cheers (again!) Cap

[Captain Sensible]

Was more down to my lack of VBA than a genius idea to be honest

I used to send variables to the sheet originally just so I could see they were actually doing what I wanted them to, plus I didn't know how to declare global variables at the time.

If you want the variable to be accessable by any routine on the sheet you just need to declare it as Public outside the sub routines

i.e.

Code: Select all

Public runningFlag As Variant

Sub Button2_Click()
    ' Define all variables
   .........................

End Sub

[MiniBlueDragon]

Simples when you know how!

I think I've figured out my boggle. It has something to do with the timing of the market swapping in Q2 as now it's all inside the worksheet_change it cycles through fine. What it does do though is:

- If I don't set a "-5" at the end of the routine I end up with the first race duplicated twice.
- If I do set it the entire column of odds is overwritten with all the odds from the first race.

Taking a night off coding this evening so maybe some relax time will help with a eureka moment.

[Captain Sensible]

Yep I'd had similar problems with moving between markets can be hit and miss but more likely down to not knowing exactly when and what gets written to excel by BA. I think things like additional columns don't get sent on first refresh so if you're also exporting things like BSP that may cause duplicates. These days use flags to ensure data only gets written once rather than duplicate and use the MyMarket routine Gary stuck up years ago to reset flags on market change. I've given up with trying to figure out why it sometimes works or doesn't and just hard code it to only move once market has switched and data written these days

Code: Select all

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
End If


Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub