Bypassing scratched horses

[IainMack]

I don't need to explain that I am a 'newbie' because that will become obvious once you see the question!

I downloaded the Excel/VB 'SampleCode.xlsm' and added some code to list the name and odds for all the runners instead of just the first. That new code only works when by chance the next race has no runners scratched. When there is a scratched runner there is an error when the program attempts to insert the odds into a cell.

I have tried using AvailableToBack.Count but when I refer to that it causes a failure anyway. I just swap one fatal error for another. It may be that AvailableToBack doesn't exist for scratched runners?

I know there has to be a method for bypassing runners that are scratched, but I haven't been able to find any documentation/forum entry etc that covers this aspect.

I realise that it is quite possibly the most basic question ever posted, but I'd appreciate any assistance or pointer to the right documentation.

[Nick JD]

Runners that have been scratched will return a status of 'REMOVED'.

I'm unfamiliar with the VB/excel approach, but reasonably sure you'll need to add a condition that allows for or ignores removed horses.

https://api.developer.betfair.com/se...nitions-Runner

Familiarise yourself with the marketBook (especially the runners in the market) - these questions will answer themselves.

Also, learn about the impact a scratched horse has on the prices of the rest of the horses still running ... and on the price (it'll change) you have on a position in the market. This stuff catches almost all (me too) who are familiar with programming, and not bookmaking.

[IainMack]

Thanks for the quick response.

I now can't get the status 'get' to work. I've looked for some information about the format but I can't find it. Lots about the data returned, nothing about the code.

In the absence of anything else I copied 'GetAvailableToBack' and changed what I thought might have been the right bits. Here's the code that fails, any insights would be appreciated.

Function GetStatusForSelection(ByVal SelectionId As String, ByVal Response As Object) As Collection
Dim Runners As Object: Set Runners = Response.Item(1).Item("runners")

Dim Index As Integer
For Index = 1 To Runners.Count Step 1
Dim RunnerStatus: RunnerStatus = Runners.Item(Index).Item("status")
Dim Id: Id = Runners.Item(Index).Item("selectionId")
If Id = SelectionId Then
' the next statement is the error
Set GetStatusForSelection = Runners.Item(Index).Item("ex").Item("status")
Exit For
End If
Next

Set Runners = Nothing
End Function

Yes, I realise the error is "right there" but I can't see it. Any assistance is appreciated. I do know that the selection Id contains a valid value.

[betdynamics]

An idea of the error that you are getting might be useful.

[IainMack]

Sorry, of course.

Run time error 424. Object required.

[IainMack]

OR:
If it's easier, I've just added some code to the GetAvailableToBack function. It looks for the field 'RunnerSTatus' and at least for the first runner it returns "ACTIVE" (Refer code for cell A10). How do I pass that status back along with the existing ATB info?

Function GetAvailableToBackForSelection(ByVal SelectionId As String, ByVal Response As Object) As Collection
Dim Runners As Object: Set Runners = Response.Item(1).Item("runners")
Dim Index As Integer
For Index = 1 To Runners.Count Step 1
Dim RunnerStatus: RunnerStatus = Runners.Item(Index).Item("status")
Range("A10") = RunnerStatus
Dim Id: Id = Runners.Item(Index).Item("selectionId")
If Id = SelectionId Then 'And RunnerStatus = "ACTIVE" Then 'iain addition
Set GetAvailableToBackForSelection = Runners.Item(Index).Item("ex").Item("availableToBa ck")

Exit For

End If
Next

Set Runners = Nothing
End Function

[Grantay.]

VBA handling of complex objects

The one tip I can give you here is that VBA does not handle situations where an object contains complex objects as children. You can get it to work but not easily through VBA.

One of the biggest difficulties is that the error reporting will not give you any clear direction: object required, data mismatching etc.

I find the best way to go - if you need Access like I do, or Excel is to write com add-ins for either in VB.net of C# net then using that add in directly - you get two benefits - you can use the great VS ide and test to make sure it is all working then drop it into the office app and use all the functionality you need from there - the best of both worlds.

grantay

[IainMack]

I appreciate the suggestion, but if I knew how to code in C# (Or any of C/Java/Python etc) I'd already be looking at that for a solution.

Sadly my technology is limited to Excel/VB or Visual Basic.

[betdynamics]

Go back to your original function (forget adjusting the GetAvailableToBack function)

Now looking at your original function, see the two lines:

Dim RunnerStatus: RunnerStatus = Runners.Item(Index).Item("status")

Set GetStatusForSelection = Runners.Item(Index).Item("ex").Item("status")

You are looking in two totally separate places for the information.

Try changing the second line to:

Set GetStatusForSelection = Runners.Item(Index).Item("status")

[IainMack]

Yes, you're right, it is two different places, and evidence I don't always clean up old code as well as I should.

The 'extra' (or 'first' in your comment) line of code works, or seems to. When I set a BreakPoint and check, RunnerStatus is "ACTIVE". That same code (the version you suggest) fails in the second line. I have also tried "Set GetStatusForSelection = RunnerStatus" since RunnerStatus seems to be correct, and that code fails on the same 'Set' line of code.

I just now thought that perhaps the failure is in the first part of the line, the 'GetStatusForSelection'. I am aware that if a variable name is repeated that strange errors can occur. I changed that name (to GetSelectionStatus) and that line of code still failed.Yes, I changed every occurrence in the project.

This might imply that there is 'something' in the code from where this function is called. Here is that code:
Set RunnerStatus = GetSelectionStatus(IGMSelection, MarketBook)

And for comparison, here is a line of code that does work:
Set AvailableToBack = GetAvailableToBackForSelection(IGMSelection, MarketBook).

Note that 'AvailableToBack' and 'RunnerStatus' have been previously defined. Both are defined before the loop through the runners, so they are only 'defined' once.

I believe that if you're not getting answers that make you happy then: Ask a better question. So my better question is this: Do any of you have a skeleton program in VB that works on the new API, and does a simple run through all active runners for a race, and do you want to earn a few quid? I almost don't care of it is VB or C#, I can learn... I hope.

Unless somebody can see why the VBA/SampleCode based program fails I can only move on.

[betdynamics]

Check your Private Messages (top right hand corner under the Quick Links button, when you are logged in to the forum)

[betdynamics]

Here's some code for a routine that runs through all of the selections and outputs the selection id and runner status to the spreadsheet:

Function GetRunnerStatus(ByVal Response As Object) As Collection
    Dim Runners As Object: Set Runners = Response.Item(1).Item("runners")
    
    Dim Index As Integer
    For Index = 1 To Runners.Count Step 1
        Dim SelectionID: SelectionID = Runners.Item(Index).Item("selectionId")
        Dim RunnerStatus: RunnerStatus = Runners.Item(Index).Item("status")
        Sheet1.Cells(24 + Index, 1).value = SelectionID
        Sheet1.Cells(24 + Index, 2).value = RunnerStatus
    Next
    
    Set Runners = Nothing
End Function

You call it via:

    GetRunnerStatus (MarketBook)

which I have placed just after the call to OutputAvailableToBack.

So...

    Dim AvailableToBack As Object: Set AvailableToBack = GetAvailableToBackForSelection(SelectionID, MarketBook)
    OutputAvailableToBack AvailableToBack, OutputRow + 9, OutputColumn - 1
    GetRunnerStatus (MarketBook)

[IainMack]

Thank you, everyone who contributed. I added code inside the GetAvailableToBack function to insert the status into a specific cell. I deleted the whole 'get runner status' code, and used the cell value instead. That seems to work. Yes, simple and effective. Thanks again for the pointer.

I also had an offer for a bespoke program, if I provide the requirements (Basically: what information do I want listed?)

Between the two it should be plain sailing from here.

Again: thanks for sharing your knowledge and for your patience.