Using Excel VB Sample Code Sheet

[judgement]

many thanks for the reply i thought it would be possible and this gives me a good idea of how to go about it thanks so i'll try and give it a go.

[judgement]

think i managed to do what i wanted more by luck than judgement.

the code below is the GetListMarketCatalogueRequestString function of the sample spreadsheet edited to include competitionId 31 which is the Premier league and to find 30 results which returns listMarketCatalogue info for the next 30 Premier league matches.

"{""filter"":{""eventTypeIds"":[""" & EventTypeId & """],""competitionIds"":[""" & 31 & """],""marketCountries"":[""GB""],""marketTypeCodes"":[""MATCH_ODDS""]},""marketStartTime"":{""from"":""" & dateNow & """},""sort"":""FIRST_TO_START"",""maxResults"":""30"",""marketProjection"":[""RUNNER_DESCRIPTION""]}"

and the code below is the GetListMarketBookRequestString function edited to find listMarketBook info for 2 of the matches returned by the listMarketCatalogue call:

"{""marketIds"":[""" & 1.127850691 & """,""" & 1.127850355 & """],""priceProjection"":{""priceData"":[""EX_BEST_OFFERS""]}}"

[judgement]

After that you'd get the marketCatalogues for each game or all the games you're interested in. You'd specify all the EventIds for the games of interest, or do them one at a time. Then you'd need the relevant marketBooks.

The MarketCatalogue does include competitionCode, so perhaps you could narrow things down later with that.

When it comes to listMarketBook, you can pass a string of several marketIds to it.

It might be possible to make listMarketCatalogue calls specifically for Premier League games without troubling the weighting limits. I guess that would depend on how far in advance such games are added to Betfair.

just as you said

[judgement]

If you're interested in just Premier League games, you could potentially drill down the navigation file to find the relevant EventIds. I haven't made this work yet. The amount of data returned is around 3-6MB - not quick to download, as you might guess. My attempts so far to convert the returned string into objects have all failed. Fortunately I don't currently need it.

out of interest what is the navigation file? i haven't come across that yet. my calls returned text strings so nowhere this amount of data or the weighting limits i'd guess.

[jabe]

The navigation file contains the data with all the headings that appear in the left column when you use the Exchange site.

It starts a list of sports (etc), and allows you to select sub-categorisations. The navigation file (or menu.json as it's actually called) can be downloaded by an API call.

I eventually got my code (which is VB and you can have it if you want) to return a string correctly, but my conversion from the string to objects hasn't worked. Initially the returned string was too big for the JavaScriptSerializer, but fixing that didn't help, nor did my attempts to use the NewtonSoftJson converter. My classes might be inadequate. You're welcome to the code I used. At some point I intend to return to it and post my findings, along with some working code, but it's not my priority.

The menu/navigation file is described here:
http://docs.developer.betfair.com/do...r+Applications

The endpoint is this:
https://api.betfair.com/exchange/bet...tion/menu.json

[judgement]

thanks for the explanation and offer it sounds as if this maybe useful if betting on lots of sports and events but as i am still taking baby steps i don't think i need it now but i'll know where to go to thanks!

my thoughts are now turning to extracting data from strings that are returned. i was thinking i would have to search for specific strings of characters in the string and use VBA to chop the relevant bits out or something like that but when i was searching around to see if i could find anything i could use i found this:

http://www.mrexcel.com/forum/excel-q...lications.html

which looks as if it does what is needed. is this the way to go?

what is NewtonSoftJson? what does that do? sorry so many questions

[jabe]

NewtonSoftJson and the JavaScriptSerializer both allow a JSON string to be converted to an object (or back) to save us all the problems of writing code to parse the JSON strings returned by Betfair.

(Warning: totally inadequate definition follows!) An object is a data item. It can contain data items. It can contain methods to do things, often with data. Some of the .NET languages are not as strict about objects as other languages. The contained data items might be properties (this is where .NET gets a bit lax about things).

Objects are defined using classes.

This is an examples of a class used in the API:

[CODE]Public Class ClassCountryCodeResult
    Public countryCode As String    ' ISO-2 code for the country
    Public marketCount As Integer   ' No of related markets 
End Class

[/CODE]

As you can see, it has two data items - countryCode and marketCount. The .NET languages allow you to directly read the countryCode and marketCount when they are defined as Public. (I say that; VB certainly does. C# might be more strict).

If I do a listCountryCode API call, I'll get lots of CountryCodes back, so the object for receiving all this data might look like this:

Public Class ClassListCountryResult
    Public jsonrpc As String
    Public result() As ClassCountryCodeResult
End Class

As you can see, it contains an array called result().

When I do a listCountryCode, I'll get a string back. It looks like this, but there's much more; I've put line breaks after the commas (etc) to aid readability:

{"jsonrpc":"2.0",
"result":[
{"countryCode":"GB","marketCount":2874},
{"countryCode":"PL","marketCount":138},
{"countryCode":"UA","marketCount":251},
{"countryCode":"CH","marketCount":821},
{"countryCode":"IE","marketCount":28},
{"countryCode":"SE","marketCount":370}
],
"id":1}

This is a relatively simple example. I have a value for "jsonrpc" and half a dozen ClassCountryCodeResult objects in an array.

Now then, the way NewtonSoftJson works is that in a SINGLE statement it can take all the data from the string and put it into a object defined as ClassListCountryResult.

Rather like this:

If I call the string above inString, then

Dim countries = Newtonsoft.Json.JsonConvert.DeserializeObject(Of ClassListCountryResult)(inString)

This parses the string for you and puts all the data into the countries so you can directly access data items such as

countries.result(0).countryCode
countries.result(0).marketCount

and even if you want to

countries.jsonrpc

though I don't know why you'd want to.

Etc.

You might wonder what happened to the "id" parameter from the JSON string. There was nowhere for it to go, so it got ignored. Easy.

You might need to be more specific and say
Dim countries as ClassListCountryResult (etc)
but VB.NET does a good job assuming you want it that way via the class mentioned in the DeserializeObject statement.


Going from an object to a string is serializing and looks like this:

Dim aString = Newtonsoft.Json.JsonConvert.SerializeObject(anObject)

The NewtonSoftJson is a third-party add-in. in Visual Studio it's installed via menu items Project / Manage NuGet Packages. I don't know how Excel would do it.

So, basically you can convert between strings and objects without writing masses of code to do it.

If you can't find out how to get the NewtonSoft stuff in Excel, you could investigate the JavaScriptSerializer, which does much the same (but with slightly different code). If neither work out, let me know.