Using Excel VB Sample Code Sheet

[vexxejr]

Hi all,

I am fairly new to the Betfair API. I have just downloaded the sample code sheet to see what the results look like when implementing the API with excel.

I have got myself an App Key and Session ID which worked using the test screen on the Betfair developers site. However i am having trouble in learning how to use the sample sheet, it has the rows which i have listed below and i have tried entering an ID from a normal betfair URL and various other things but none of that works.

I wondered if somebody could let me know what to type and how to instruct the sample code to retrieve betfair prices?

Raw Response: listEventTypes
Raw Response: listMarketCatalogue
Raw Response: listMarketBook
Raw Response: placeOrders

Event Type ID
Market ID
Selection ID
Prices
Result
Instruction Result

Thanks in advance

[jabe]

I haven't used the Excel VBA spreadsheet example. For some reason it wouldn't open in my copy of Excel 2010. My coding has been in VB.NET.

Have you looked at the documentation? That has more examples in it.

I'd suggest you familiarise yourself with these, if you haven't already:

1. JSON. I believe there's a tutorial on the w3c site
2. Check out curl. You may not need to use it for VBA/API calls, but it'll give you an idea what calls look like and what other bits and pieces need calling.
3. The relevant stuff in the documentation. Sometimes looking at the examples for other coding languages can help.

To get odds, you call ListMarketBook. Beforehand, you will have chosen an event (listEvents) and obtained details of one or more markets for that Event using listMarketCatalogue.

There's an awful lot to learn about.

[judgement]

I am fairly new to the Betfair API.

me too

I wondered if somebody could let me know what to type and how to instruct the sample code to retrieve betfair prices?

i couldn't find any documentation and was asking the same questions.

having spent a some time trying to understand how it works as far as i understand it with my limited VBA knowledge is that you don't enter anything just click either Go button and it returns 7 in B11 which is the Event Type ID for horse racing, enters the Market ID of the next horse race in B12, the Selection ID of the first horse in that race in B13 and the prices for that horse in B14, B15 and B16. i think it then tries to place a 1p bet on that horse using the back price in B14 but the bet fails to be placed because the stake is too low.

i think the idea is not to use this spreadsheet as it is but to take the relevant bits of code from it for our own app.

now the hard work begins

[jabe]

Lots of documentation: http://docs.developer.betfair.com/do...lu3yomq5qye0ni

The odds are in the response to the listMarketBook call.

[judgement]

i meant documentation for the SampleCode.xlsm spreadsheet apart from the comments in the VBA. us newbies need spoon feeding!

anyway here is my next challenge..

when i change 'Horse Racing' on line 121 of the Util module to 'Soccer' (my area of interest) and click the Go button the macro stops at line 129 in the Util module:

GetMarketIdFromMarketCatalogue = Response.Item(1).Item("marketId")

with a Run-time error 5 - Invalid procedure call or argument.

and the 'Raw Response: listMarketCatalogue' (cell B7) is not completed with any football data.

[gus]

This is a total guess, since I know nothing about Excel and less than nothing about VB6, but I'd try changing 'Soccer' to 'Football' and see what happens.

At least it shouldn't take too much effort!

[betdynamics]

The use of "Soccer" is correct.

The issue is caused by the fact that you are trying to retrieve the WIN market - but there is no WIN market in football matches.

Try retrieving the MATCH_ODDS market instead in the GetListMarketCatalogueRequestString function.

[judgement]

try changing 'Soccer' to 'Football' and see what happens.

thanks anyway

Try retrieving the MATCH_ODDS market instead in the GetListMarketCatalogueRequestString function.

brilliant that worked it finds Ross Co U20 v Kilmarnock U20 now

[judgement]

i am going to step through the VBA code that runs when a Go button is clicked to try and understand the code.

the first code is simply DeleteLogFile which deletes a file called log.txt if it exists from the same folder that the spreadsheet is saved in. this file is created each time the app runs.

tonight's homework was easy but tomorrow's looks much harder!

[judgement]

the next 8 lines of code in the Example module gets a list of Event Types and displays them in cell B6 but lets see how the code does this.

lets start with just the first 2 of these lines.

Dim Request

firstly a variable called Request is declared.

the next line of code looks as if it is the code that gets the Event Types from Betfair:

Request = MakeJsonRpcRequestString(ListEventTypesMethod, GetListEventTypesRequestString())

i'm new to functions but this seems to be a function which is function in itself that has 2 inputs the first of which is a text string and the second is a function.

MakeJsonRpcRequestString is a function that has 2 string inputs called Method and RequestString that outputs a string:

MakeJsonRpcRequestString = "{""jsonrpc"": ""2.0"", ""method"": ""SportsAPING/v1.0/" & Method & """, ""params"": " & RequestString & ", ""id"": 1}"

however where are the values of Method and RequestString obtained from? Betfair?

ListEventTypesMethod is a string called listEventTypes.

GetListEventTypesRequestString is a function with 1 input called {""filter"":{}}

GetListEventTypesRequestString = "{""filter"":{}}"

putting this all together i'm getting something like:

{""jsonrpc"": ""2.0"", ""method"": ""SportsAPING/v1.0/" & Method & """, ""params"": " & RequestString & ", ""id"": 1}(listEventTypes, {""filter"":{}})

when you step through the code the yellow highlight goes to Function GetListEventTypesRequestString first and then Function MakeJsonRpcRequestString which seems to tie in with this.

am i even close?

[jabe]

Yes, that's the general idea.

The definition of the MakeJsonRpcRequestString function includes names for two parameters that will be passed to it when it is called. These two parameter names are used in the code internal to the function, but when you call the function, you just make sure the call has two appropriately defined parameter values, either in the form of a variable or an actual value.

As an example, suppose I want to multiply two numbers. I can create a function that looks like this (this is generalised code, so may not be VBA):

Function Multiply(firstNumber, secondNumber)
Return firstNumber * secondNumber
End function

And to call it you might use

dim seven = 7
Answer = Multiply(seven,9)

So the function finds that firstNumber = the value of seven, which is 7, and secondNumber = 9 and, we hope, it all returns 63, which appears in Answer when the function completes.

So the values Method and RequestString are decided when your program calls the function. They are set somewhere in the program. The function is a general one, so you tell it which of several API calls you want to do and what the parameters are to get the data you want.

The call you mention is this

Request = MakeJsonRpcRequestString(ListEventTypesMethod, GetListEventTypesRequestString())

You've already seen that
ListEventTypesMethod = "listEventTypes"
and
GetListEventTypesRequestString()
(which is, as you thought, a function call itself)
= "{""filter"":{}}"
which is basically blank parameters for the request to get EventTypes.

Now then, when you pass these values to the function, the function looks at what arrives in its brackets and finds that

Method = "listEventTypes"
and
RequestString = "{""filter"":{}}"

So it takes those values and puts them in this string

"{""jsonrpc"": ""2.0"", ""method"": ""SportsAPING/v1.0/" & Method & """, ""params"": " & RequestString & ", ""id"": 1}"

and gets

"{""jsonrpc"": ""2.0"", ""method"": ""SportsAPING/v1.0/" & "listEventTypes"& """, ""params"": " & "{""filter"":{}}"& ", ""id"": 1}"

and because the & just fastens strings together, it ends up something like this

"{""jsonrpc"": ""2.0"", ""method"": ""SportsAPING/v1.0/listEventTypes"", ""params"": {""filter"":{}}, ""id"": 1}"

and that's now just a string, and the double quotes are only there because of VBA limitations, so ultimately the string passed to Betfair is this

"{"jsonrpc": "2.0", "method": "SportsAPING/v1.0/listEventTypes", "params": {"filter":{}}, "id": 1}"

which is JSON that Betfair can respond to.


The strings sent back and forth between your spreadsheet and Betfair are in JSON format. There is a tutorial for this on the w3c site (you can Google this - the site is supposed to set the standards for the internet and has all manner of tutorials on it).

[judgement]

thank you for your explanation and example of a function as it helped me to understand that Method and RequestString are just descriptive names and that ListEventTypesMethod and GetListEventTypesRequestString are the values of those parameters. and i can see from your explanation how those values are entered in the string that is passed to Betfair.

i guess it is helpful to understand the JSON format in order to be able to construct requests specific to our own requirements so i guess i'll have to add it to the list of things to learn!

[jabe]

The JSON isn't too bad - most data has an identifier that tells you what it is, plus a colon, and then its value, then a comma to separate it from the next one, like this

"horseName": "OverWeightDonkey",

Square brackets denote an array or collection of values:

"dayNames": ["Monday","Tuesday","etc"],

and curly brackets indicate related data items (for some reason, in my browser curly and square brackets sometimes look the same on this site and aren't visually as different as they should be):

"myCarDetails": {"engines": 1, "doors": 4, "topSpeed": 18},


Some of the data typing may not be terribly strict at the Betfair end. It appears to accept numbers within quotes as numbers. One word of caution - marketIds look very much like numbers in that their format contains a decimal point (eg: 1.2345678). It's best to treat these as strings and put quote marks round them. There may have been situations where a marketId with a zero at the end lost it somewhere along the line, resulting in a mismatch.

The chief failures with JSON sent to Betfair are unbalanced brackets and missing out required information.

[judgement]

thanks i may be able to construct my own first JSON to be sent to Betfair using that but before that i want to continue understanding the code in the sample spreadsheet and i'm struggling with the next line:

Dim EventTypeResult: Set EventTypeResult = ParseJsonRpcResponseToCollection(ListEventTypesRes ponse)


as i understand it EventTypeResult is an object variable that equals the output of the function ParseJsonRpcResponseToCollection and the input of this function is called ListEventTypesResponse which is the same name (but not the same thing?) as the variable obtained by the previous code which is the response from Betfair.


Function ParseJsonRpcResponseToCollection(ByVal Response As String) As Object

Dim Lib As New jsonlib
Set ParseJsonRpcResponseToCollection = Lib.parse(Response).Item("result")
Exit Function

End Function


Response is the name of the input but does Response = ListEventTypesResponse?

i don't know what jsonlib.parse is, is it a JSON command?

it seems this is working on the list of Event Types obtained from Betfair (Response?) in particular the Item called result which if i look in cell B6 is all the Event types so this seems to be the code that gets the relevant bits from the string returned from Betfair which the next line of code will get the Soccer EventTypeId from.

[jabe]

Starting with jsonlib.parse - it looks like jsonlib is an object designed to make it easy to deal with data in JSON string format. The .parse denotes a method which divides the JSON string into a collection (or array, or whatever). So, in the case of the statement you mentioned, it splits the string ListEventTypesResponse into elements to be held within the EventTypeResult object. You'll then be able to access the individual elements. The part that says .Item("Result") appears to be telling it how to split the Response string.

I'd guess that the jsonlib has some function which will do the reverse of its parse method, perhaps taking an object and making it into a JSON string. There should be some documentation associated with the jsonlib.

On the other topic, it looks like ListEventTypesResponse is a JSON string returned by a call to the Betfair API asking for EventTypes, from a function in your program.

In this instance, the ListEventTypesResponse string is passed to the function ParseJsonRpcResponseToCollection, as you thought, and, yes, that function calls the data it receives Response.

As a minor aside - and feel free to ignore this for now - there are two ways of passing data to a function. One physically puts the data somewhere specifically for the function to deal with (ByVal), and the other says "the data is over here:" (ByRef). There's more to it than that, but perhaps best to leave it till you're more familiar with VBA.

Looking at the definition of jsonlib, it appears that jsonlib is a library of JSON routines for making life easier. In the function it's just called Lib, but you can call it whatever you like.

Libraries of functions, such as jsonlib, can be added to a program to add in the ability to do some task(s) not covered by standard commands in the coding language you're using. You could write your own routines for dealing with JSON, but if someone's done it already, there's not much point.