Using Excel VB Sample Code Sheet

[jabe]

Just to add to Grantay's last comment; for many years there were two basic ways to run code.

1. Interpreted
In this case an interpreter program reads a file of code and turns it into executable code line by line and executes it as it goes along. It may be that the lines in a code are read and interpreted many times while the code is running. This is not efficient.

2. Compiled
A compiler program reads a file of code essentially converting each part of it once into machine language that the operating system can understand. It's a one-off operation, unless the original code is changed. The results of the conversion are generally written as an executable file. The executable file can be run repeatedly. Compiled programs run much faster.

There are intermediate types nowadays, part way between interpreted and compiled.

[Grantay.]

very helpful thanks does this mean GetMarketIdFromMarketCatalogue can be used in another function for example, is that what an object is?

GetMarketIdFromMarketCatalogue is a function:

Function GetMarketIdFromMarketCatalogue(ByVal Response As Object) As String
    GetMarketIdFromMarketCatalogue = Response.Item(1).Item("marketId")
End Function

As Jabe noted in VBA the function name holds the return value. So you use the Set statement to get the value. In this case you are setting the value to the MarketId value that is returned from the listMarketCatelogue call.

The function can be used any where you need it, but you need to understand the structure of the BF response so you know exactly what to pull out - that is where VBA is sadly lacking

what does compile a spreadsheet mean?

What I meant was compiling the VBA code behind the spredsheet. You need to enable the Developer tab to easily access the VBA code:

1) Click the File tab;
2) Click the Options at the left to enter into Excel Option window;
3) Click the Customize Ribbon at the left;
4) At the right, select the Main Tabs from Customize The Ribbon drop down box;
5) Check the Developer item;

Click on the developer tab and you can then go straight to the code, to the debug then the press compile

Grantay

[judgement]

thanks both i'm learning a lot hopefully it will help others too.

And so, in the function that returns the EventType for Soccer, when GetEventTypeIdFromEventTypes is set to 0, it isn't to use it as the variable counted during the loop (that is Index in your case), it's setting the value of 0 in case by the end of the loop it hasn't found a case where

EventType.Item("name") = "Soccer"

thanks i'd never have worked that out.

Something you may find useful - my first ever computer program was written in, I think, 1977. For as long as I can remember, I've written little test programs

thanks i've started saving snippets of code i'm likely to use from time to time.

Your best source of help is stack overflow, which is a bit easier to follow than the MS documentation.

i've found stack overflow useful several times, like the way it's organised.

Secondly you are going to find it very difficult to follow your own code unless you break your code into distinct lines.

Instead of

Specify the variables you are defining, then set the values

this code was copied from the sample spreadsheet but point taken.

To summarise, you use the Set keyword when assigning values to Object types:

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

other types such as String, Integer etc are assigned directly:

EventTypeId = "7" (for horse racing or "Soccer")

very helpful thanks does this mean GetMarketIdFromMarketCatalogue can be used in another function for example, is that what an object is?

I use VBA as my main tool for database intensive usage of Betfair bets and so on, most of the time in VBA I use a VB.net process for all the Betfair grunt work via a vb.net com addin that is called from Access ,then pass the data into MsAccess/VBA for processing.

VB.net seems popular but i know nothing about it so i will just try and get something working with what i know something about which is Excel and VBA.

I have never been able to get the ServiceClientNG-SampleSpreadsheet.xlsm to compile

what does compile a spreadsheet mean?

[Grantay.]

VBA Notation

my first question which is a bit of an aside is why the word 'Set' was used in the previous line but not in this line i.e. why is it not:

Dim EventTypeId: Set EventTypeId =

anyway it calls this function:

First thing to understand when working in VBA is that it is similar but definitely not equivalent to vb.net.

Your best source of help is stack overflow, which is a bit easier to follow than the MS documentation.

Secondly you are going to find it very difficult to follow your own code unless you break your code into distinct lines.

Instead of

Dim EventTypeId: EventTypeId = GetEventTypeIdFromEventTypes(EventTypeResult)
Sheet1.Cells(OutputRow + 6, OutputColumn).value = EventTypeId

Specify the variables you are defining, then set the values thus:

Dim EventTypeId
EventTypeId = GetEventTypeIdFromEventTypes(EventTypeResult)
Sheet1.Cells(OutputRow + 6, OutputColumn).value = EventTypeId

One reason you are getting confused is the way VBA allows you to NOT specify a variable type. So for EventTypeId you have not specified it to be of Type String, which it is as you can see from the full Function declaration:

GetEventTypeIdFromEventTypes(ByVal EventTypes As Object) As String

So you are passing in a parameter of type 'Object' and the return value is of type "String".

So the correct format would be

Dim EventTypeId as String - If you then tried to assign something to that variable you would get the correct VBA error which is "Object required".

To summarise, you use the Set keyword when assigning values to Object types:

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

other types such as String, Integer etc are assigned directly:

EventTypeId = "7" (for horse racing or "Soccer")

I use VBA as my main tool for database intensive usage of Betfair bets and so on, most of the time in VBA I use a VB.net process for all the Betfair grunt work via a vb.net com addin that is called from Access ,then pass the data into MsAccess/VBA for processing.

One important point to note is that VBA does not easily expose much of the data that betfair provides - using very clumsy structures like parsed json strings you can see the actual data but it is very much simpler using list(of T) structure in .net, particularly if you create your own classes to hold (parse) the data into and out of. So for racing you create a Meeting Class, Race Class and Runner Class and write code to push and pull the json into and out of those structures.

Sorry, I cannot help you with running the spreadsheet - I am no expert in Excel, and I have never been able to get the ServiceClientNG-SampleSpreadsheet.xlsm to compile and run so I just wrote my own VBA in access to use my vb.net com add-in as noted above.

Hope that clarifies it a bit

[jabe]

I'm not 100% certain about your question about Set. In Visual Basic.NET, some variables (data items, fields) in a class can be called properties. In other languages I've looked at, the only way to deal with those properties from outside of the object created is via standard .Set and .Get methods. Having a method for setting or getting a property allows the possibility of the object doing some processing of the property.

It's possible that the property might need calculating or checking before being Set or passed via a Get.

I've Googled a little and it does appear to be the case that the Set should be used with class properties, so I'm not sure why it appears to be missing on this line. Visual Basic.NET does allow for access to class variables (if appropriately defined) without using Get and Set. It may depend on the definition of the class.

That isn't the greatest answer ever, I have to admit, but I hope it goes some way to answering your question.


There's only one thing I can see in your assessment of the function that finds the EventType for Soccer that I should comment on, otherwise it's spot on.


You remember earlier in the thread I gave you an example of a function, like this

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

and, at the time, I gave an example of calling it, like this

Answer = Multiply(seven,9)

Well, VBA acts a little differently when it comes to sending back the value calculated by the function.

As far as VBA is concerned, the function name itself becomes a variable. So instead of using

RETURN solution

VBA says

FUNCTIONNAME = solution.

In the Multiply function, it would change from

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

to

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

And so, in the function that returns the EventType for Soccer, when GetEventTypeIdFromEventTypes is set to 0, it isn't to use it as the variable counted during the loop (that is Index in your case), it's setting the value of 0 in case by the end of the loop it hasn't found a case where

EventType.Item("name") = "Soccer"

because if it can't find the Soccer value, it still needs to return a value.

But when it does find the value for Soccer, it does this

GetEventTypeIdFromEventTypes = EventType.Item("id")

i.e; it sets the function name to the value for Soccer.

So it sets the value to 0 and if it doesn't find what it wants, that's the value it'll pass back. If it does find the Soccer value, that's what you'll get.


Something you may find useful - my first ever computer program was written in, I think, 1977. For as long as I can remember, I've written little test programs in which I can try things out, rather than doing trials in the current big project. This has the additional benefit of allowing me to go back and look at the test programs (as long as the names reflects their purpose), and maybe tweak them, especially if they're on a topic I haven't used for a while. I wrote with COBOL professionally for many years and always had to look up details of the SEARCH statement because I used it so rarely. I always thought (and still do) that the code for it was an utter mess.

Anyway, hope that's been some help.

[judgement]

i've spotted the class module called jsonlib now and having googled what a class module is learnt that the 'New' in 'Dim Lib As New jsonlib' creates a new instance of that class.

and, yes, that function calls the data it receives Response.

this was helpful as i thought i should be looking for an input that was called Response elsewhere.

i think i can move onto the next couple of lines:

Dim EventTypeId: EventTypeId = GetEventTypeIdFromEventTypes(EventTypeResult)
Sheet1.Cells(OutputRow + 6, OutputColumn).value = EventTypeId

my first question which is a bit of an aside is why the word 'Set' was used in the previous line but not in this line i.e. why is it not:

Dim EventTypeId: Set EventTypeId =

anyway it calls this function:

Function GetEventTypeIdFromEventTypes(ByVal EventTypes As Object) As String
GetEventTypeIdFromEventTypes = "0"

Dim Index As Integer
For Index = 1 To EventTypes.Count Step 1
Dim EventType: Set EventType = EventTypes.Item(Index).Item("eventType")
If EventType.Item("name") = "Soccer" Then
GetEventTypeIdFromEventTypes = EventType.Item("id")
Exit For
End If
Next
End Function

when EventTypeId is defined the input of the function GetEventTypeIdFromEventTypes is EventTypeResult which was obtained from the previous code and if my understanding is correct this function calls this data that it receives EventTypes.

GetEventTypeIdFromEventTypes is firstly set to zero and then loops through incrementing by 1 each time making EventType equal the eventType field associated with that item until it reaches an Event Type number where the name item associated with it is Soccer (i changed Horse racing to Soccer) and then assigns the id number of that Event Type to the value of the GetEventTypeIdFromEventTypes function and outputs it in cell B11.

[jabe]

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.

That's pretty much the case, but I should mention this:

"EventTypeResult is an object variable that equals the output of the function ParseJsonRpcResponseToCollection"

This sort of statement is an extremely common one in computer code. It's best not thought of as something being equal to something else. Rather, it's an assignment statement. If we have something like this:

A = B

it's not saying "A is equal to B", it's saying "put the value(s) of B into A". Or "make A equal to B" or "set A equal to B" or "assign the value of B to A". That sort of phrase.

There are different versions in other languages; some use := rather than = and COBOL uses the (now) slightly confusing MOVE statement.

It's just a minor thing, and you probably got it already.

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

[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]

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]

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]

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]

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?

[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]

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