Using Excel VB Sample Code Sheet

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

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

[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

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

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

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

[judgement]

you can probably tell i still haven't been understanding when to define a variable using Set. i found this stackoverflow discussion helpful:

http://stackoverflow.com/questions/3...ally-do-in-vba

in particular this definition:

Set is used for setting object references, as opposed to assigning a value.

and example:

Dim WS As Worksheet
Set WS = ActiveWorkbook.Worksheets("Sheet1")
WS.Name = "Amit"

i think i've got that.

now applying this to the code in the sample spreadsheet:

Dim EventTypeResult
Set EventTypeResult = ParseJsonRpcResponseToCollection(ListEventTypesRes ponse)

firstly i tried removing Set and running the code and got:

Run-time error '450'
Wrong number of arguments or invalid property assignment

this proves that Set is required here and i'm guessing the error is invalid property assignment however when i tried stepped through the code to try and find out why references need setting it went through loads of jsonlib code that is way past me so i've given up on that but if i was to have a wild guess it would be that it is referencing part of the string of text in ListEventTypesResponse not a cell reference.

i've got a better idea now of why Set is used which will do for now as i'm going off at a tangent so i'll get back to looking at the next code and hopefully it will be downhill from here await more questions

[judgement]

i couldn't let it go but i think i understand now why Set is used here..

Set EventTypeResult = ParseJsonRpcResponseToCollection(ListEventTypesRes ponse)

you explained it earlier

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.

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.

ParseJsonRpcResponseToCollection seems to convert the ListEventTypesResponse string to an array object and this web site..

http://www.cpearson.com/excel/Passin...ningArrays.htm

says..

The variable that receives the array result ..must have the same data type as the returned array.

therefore EventTypeResult has to be an object if the array is an object and therefore needs to be Set/referenced.

when you say "the data is over here:" (ByRef) , where is here if that is not a daft question?

[jabe]

I'll start with a little on objects. I don't know what you've read about them, but you can define them however you wish. You'll have seen in the Betfair documentation what bits and pieces comprise each objects that can be returned by Betfair. I'm a little surprised that in the VBA lines such as

Dim EventTypeResult

don't say anything about what type of data EventTypeResult is going to be. From the documentation, EventType contains just id and name. EventTypeResult also contains the marketCount for each Event Type.

As you saw, a call to list EventTypes may get lots returned, and so you have an array, or somesuch, returned.

On ByRef and ByVal; sometimes it won't matter, but sometimes you might want a function to change your value. Many times you won't. If you want your original value to change, you have to use ByRef, which tells the function whereabouts in program memory that piece of data is. Then it can act directly on that data. Otherwise ByVal is the one to choose, as that only passes the value. I thnk that covers it.

[judgement]

i thought it might be whereabouts in program memory the data is.

one thing i have read about Excel objects is that if you press F2 in the VB browser it displays a list of all possible objects so presumably memory is one of these.

the rest of the code in the sample spreadsheet is largely made of blocks of code similar to the chunk of code we've looked at so i'm happy to move on. i changed the 0.01 stake in the GetPlaceOrdersRequestString function to 2.00 and successfully placed a £2.00 bet on Chelsea to beat Liverpool tonight which is the next GB football match that the sample spreadsheet currently finds.

what i want to try and do next is modify the sample spreadsheet to do something else but i am not sure what as i want it to be achievable but useful. for example since i have backed Chelsea at 2.3 i would like to get it now to watch for Chelsea's lay odds to come down to 2.28 and place a lay bet of the same amount. maybe that is too advanced for me yet i am away on holiday next week so will think about it then.

[jabe]

It's a long time since I've written anything in VBA (my brother does it all the time - including a prog to solve all Sudokus). I hadn't used objects the last time.

The precise location of data in memory is something most programming languages keep from programmers. Those that write in C can find out because it uses "pointers", which are, IIRC, memory addresses.

When I used to be involved with COBOL programs, a failed program would produce a memory dump which we had to trawl through working out which bit of data was which. It was tedious in the extreme, and the company I worked at bought a package called AbendAid which was a godsend and listed the contents of every variable when the program failed.

Something you might find useful with VBA in Excel is that if you wish to save values for the next run it's so easy because you can just put the values in cells and read them the next time.

Often with learning new programming languages it can open up a whole new set of possibilities. The languages I started with couldn't do graphics, couldn't talk to the internet, couldn't make sounds, couldn't time things, couldn't respond to key presses, etc, etc. Happy holidaying, and take pen and paper!

[judgement]

Something you might find useful with VBA in Excel is that if you wish to save values for the next run it's so easy because you can just put the values in cells and read them the next time.

that's my plan

it' time to carry on with the next phase of this project which is to start making changes to the sample spreadsheet. i could start with a blank spreadsheet and take bits from the sample spreadsheet but that wouldn't be as easy. in time the sample spreadsheet may evolve into something that does not resemble the original file much at all. i'm giving myself a year to produce a working football betting app doing little by little because i've got plenty of time and no pressure.

i haven't got some wonderful money making strategy up my sleeve and haven't got much of an idea what the end product will look like or do.

i've started with a simple modification. i assume that the Event Type ID for Soccer is always going to be 1 so instead of finding the Event Type ID for Soccer every time the macro is run i have commented out the first couple of blocks of code that does this and added

Public Const EventTypeId = 1

in the Util module where the other constants are declared and the macro still seems to work ok so no unforeseen implications i'm aware of yet!

hmm what to do next??

[jabe]

If you make sure all the different calls work as they should, it'll be easier to adapt when you do come up with a money spinning idea.

[judgement]

i'm going to dive straight into the deep end. sooner or later i'll want to pull off Betfair information about bets i i've placed and it seems listMarketBook is used to do this.

http://docs.developer.betfair.com/do...listMarketBook

next i looked for some code using listMarketBook (why reinvent the wheel!) that i can adapt and found this which looks relevant:

http://forum.bdp.betfair.com/showthr...listMarketBook

the good thing is that it is VB and it will be a good opportunity to learn about classes. obviously i'll have to make it relevant to football.

i've got no idea what this code does and whether it will be useful but if it isn't i can try something else but even if that is the case i'm sure it won't be an entirely wasted excercise.

i'm setting myself a target of 2 weeks to understand it, add it to the sample spreadsheet, adapt it and successfully retrieve details of bets i have placed. it'll be fun i'm guessing everyone goes through this sort of learning curve to some degree.