Using Excel VB Sample Code Sheet

14-04-2016, 07:59 AM

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?

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.

Comment

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.

Comment

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):

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.

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

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).

Comment

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!

Comment

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.

Comment

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:

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.

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.

Comment

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.