Using Excel VB Sample Code Sheet

[betdynamics]

Trying to get the traded volume is not a good idea as you are using the delayed AppKey. The delayed AppKey does not return any traded volume.

You may already be aware of this, but I am not certain so I'll post this in the hope that I am not teaching grandma to suck eggs!

All of the different parameters that you can use in the listMarketCatalogue call are documented in the API documentation. The return objects are also documented, so it should be possible to determine roughly what data each parameter returns.

The listMarketCatalogue call is documented at http://docs.developer.betfair.com/do...arketCatalogue

I would also recommend the use of the API Visualiser. This allows you to enter different parameters and see what data is returned, all from within your browser. You can access the API Visualiser at https://developer.betfair.com/exchan...ting-api-demo/

If you use the Visualiser from within Chrome, then you can hit the F12 key and you will be able to see the request that was sent to Betfair and the response received (these would be in JSON format)

[judgement]

listMarketBook (and its sister call listMarketCatalogue) are really designed for obtaining information about a specific Betfair market.

listMarketCatalogue returns the more "fixed" elements of the market, whilst listMarketBook returns the more "dynamic" elements of the market.

If you are specifically interested in obtaining the current status or results of bets that you have placed, then you could look at the listCurrentOrders and listClearedOrders calls instead.

thanks this is helpful.

[judgement]

i modified my GetTradedVolumeRequestString() Function so that it only included some of the parts (params?) previously used eg

"{""marketIds"":[""" & MarketId & """]}"

"{""marketIds"":[""" & MarketId & """],""priceProjection"":{""priceData"":[""EX_BEST_OFFERS"",""EX_TRADED""],""exBestOffersOverrides"":{""bestPricesDepth"":1} }}"

"{""marketIds"":[""" & MarketId & """],""priceProjection"":{""priceData"":[""EX_BEST_OFFERS"",""EX_TRADED""]}}"

"{""marketIds"":[""" & MarketId & """],""exBestOffersOverrides"":{""bestPricesDepth"":1} }"

these calls return a subset of the previous output however they all include quite a few different bits of data. is it possible to specify which bits of data you want returned for example would it be possible to only return one bit of data eg "totalMatched":471.94? i thought it might be something like:

"{""marketIds"":[""" & MarketId & """totalMatched""]}"

but that failed altogether.

just that if every call returns a lot of data you don't need it seems inefficient.

[judgement]

got my first listMarketBook call to Betfair incorporated in the Sample spreadsheet.

added these 3 lines in the Example module:

Request = MakeJsonRpcRequestString(ListTradedVolumeMethod, GetTradedVolumeRequestString())
Dim ListTradedVolumeResponse As String: ListTradedVolumeResponse = SendRequest(GetJsonRpcUrl(), GetAppKey(), GetSession(), Request)
Sheet1.Cells(OutputRow + 1, OutputColumn).value = ListTradedVolumeResponse

declared this variable in the Util module for the ListTradedVolumeMethod input:

Public Const ListTradedVolumeMethod As String = "listMarketBook"

added this function in the Util module for the GetTradedVolumeRequestString() input:

Function GetTradedVolumeRequestString() As String
GetTradedVolumeRequestString = "{""marketIds"":[""" & MarketId & """],""priceProjection"":{""priceData"":[""EX_BEST_OFFERS"",""EX_TRADED""],""exBestOffersOverrides"":{""bestPricesDepth"":1} }}"
End Function

which was lifted from this post:

Hi, I use this string successfully:

strRequest = "{""jsonrpc"": ""2.0"", ""method"": ""SportsAPING/v1.0/listMarketBook"", ""params"": {""marketIds"":[""" & marketId & """],""priceProjection"":{""priceData"":[""EX_BEST_OFFERS"",""EX_TRADED""],""exBestOffersOverrides"":{""bestPricesDepth"":1}}}, ""id"": 1}"

Will post details of the associated market book class & additional code shortly.

With regards
Mike

for now to test it i looked up an example of a Market ID to get listMarketBook info for and declared a variable for it :

Public Const MarketId As String = "1.127395031"

the output in cell B6 of the Sample spreadsheet is:

{"jsonrpc":"2.0","result":[{"marketId":"1.127395031","isMarketDataDelayed":tr ue,"status":"OPEN","betDelay":0,"bspReconciled":fa lse,"complete":true,"inplay":false,"numberOfWinner s":1,"numberOfRunners":3,"numberOfActiveRunners":3 ,"lastMatchTime":"2016-10-12T13:47:29.118Z","totalMatched":105.54,"totalAvai lable":2678.93,"crossMatching":true,"runnersVoidab le":false,"version":1458891941,"runners":[{"selectionId":2145939,"handicap":0.0,"status":"AC TIVE","lastPriceTraded":4.9,"totalMatched":0.0,"ex ":{"availableToBack":[{"price":4.5,"size":11.58}],"availableToLay":[{"price":4.9,"size":30.85}],"tradedVolume":[]}},{"selectionId":5114005,"handicap":0.0,"status": "ACTIVE","lastPriceTraded":1.79,"totalMatched":0.0 ,"ex":{"availableToBack":[{"price":1.76,"size":41.7}],"availableToLay":[{"price":1.85,"size":16.17}],"tradedVolume":[]}},{"selectionId":58805,"handicap":0.0,"status":"A CTIVE","totalMatched":0.0,"ex":{"availableToBack":[{"price":4.0,"size":52.53}],"availableToLay":[{"price":4.7,"size":50.3}],"tradedVolume":[]}}]}],"id":1}

my next task is to understand this output and how it relates to the input paramenters and find out if other listMarketBook parameters are available and what they output.

[judgement]

Just a word of encouragement - i started from scratch 2 years ago and now have 1000s of lines of code, functions, procedures that all run autonomously 24 hours a day placing bets etc etc
It's a steep learning curve but all very doable with the resources and a "little" VBA experience
Good luck!

thanks that is very encouraging and gives more belief i can do something like that myself if i keep chipping away at the problems. if i can get a handle on the calls to Betfair i will be able to write some code to play around with the data in Excel to at least to get something basic working to start with. it'll be worth it if i can sit back and watch the money coming in without having to do anything

[betdynamics]

listMarketBook (and its sister call listMarketCatalogue) are really designed for obtaining information about a specific Betfair market.

listMarketCatalogue returns the more "fixed" elements of the market, whilst listMarketBook returns the more "dynamic" elements of the market.

If you are specifically interested in obtaining the current status or results of bets that you have placed, then you could look at the listCurrentOrders and listClearedOrders calls instead.

[schmoopie]

VBA and Betfair

Just a word of encouragement - i started from scratch 2 years ago and now have 1000s of lines of code, functions, procedures that all run autonomously 24 hours a day placing bets etc etc
It's a steep learning curve but all very doable with the resources and a "little" VBA experience
Good luck!

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

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

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]

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]

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]

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 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?

[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