VBNet Sample (Yes really)

[SimonN]

So, for the final part of your post, I start my program by getting a list of Events (these are particular football matches). For each, I get the MarketCatalogue data that I'm interested in. All of this is stored in a (one of many) football match object. After that, when I want to get odds, I use a list of MarketIds (a string) from the MarketCatalogue in my listMarketBook call, allowing me to get several markets at once. I don't know whether I can get multiple markets from different events in a single call.

yes sounds like I am doing it pretty much the same way there.

The de/serialisation relates to translating a string of JSON data to an object or vice-versa.

For example, when a listMarketBook call is made, it returns a JSON string:

Public odds As New ClassListMarketBookResult
Dim jss As New JavaScriptSerializer() 'JSON (de)serialiser 

' inrec is the JSON string returned from Betfair:
odds = jss.Deserialize(Of ClassListMarketBookResult)(inrec)

ClassListMarketBookResult looks like this:

Public Class ClassListMarketBookResult
    Public jsonrpc As String
    Public result() As ClassMarketBook
End Class

So when the JSON string is deserialised, I get an array of ClassMarketBooks. So, if you specify several markets in your API call, you get them all in the result() array.

I'm sure there are other equally valid ways to do this.

Haha looks like I did not miss an opportunity to "do something simply when you can do it complicatedly" lol!

Not sure whether or not all this JSON seriliazation stuff was about at the time or what it is but the VBA samples in the betfair developers section don't use it. Instead they use their own parsing routines to loop through the JSON return string and strip out the non alpha numeric characters (such as ], }, ", etc) and insert the vital data into collections.

Searching for a worked example of JSON Serialization using Excel VBA, I cam across this simple worked example ( http://ramblings.mcpher.com/Home/exc...son-conversion ) - so hopefully I can get my head round it to integrate it into my routines and replace all the manual parsing clarting on that I have - which works fine like but seems unnecessary and redundant. Hopefully it isnt too much even for a someone new to this JSON business like me!

Thanks again for this Jabe!

Seems like I've got the basic programmatic algorithm correct navigating BF data structures with my JSON requests in the right places, just could do with optimizing the serilization process.

By the way seems like your program is doing the same as mine fetching multiple football markets for multiple events.

Simon

[jabe]

I'm glad to hear it's all working for you! My program is very complicated (and I used to program for a living, but in a different language). I have yet to add code to deal with the loss of the internet connection, among other things.

[SimonN]

(and I used to program for a living,

Ditto!

[jabe]

Ditto!

Never had to do all this async nonsense in Cobol though...

Still, those bits are working.

[SimonN]

Betfair search field - what operation is it using?!

Going mad here.

If you use the Betting API Demo tool ( https://developer.betfair.com/exchan...ting-api-demo/ ) to search Events with "Man Utdv" no records are returned.

But when you enter the same string (Man Utdv) into the Betfair search field it returns Man Utd v Arsenal | 28 Feb 2016, 14:05 (and other fixtures).

Why the difference? What data structure is the BF Search field using or what kind of query is overcoming the misspell to return the results? How can I programmatically realise the same results for misspelled events?

Driving me mad lol!

Cheers

Never had to do all this async nonsense in Cobol though...

Still, those bits are working.

[SimonN]

Same as this see

"Man United v Arsenal" returns nothing searching Events but returns today's game top of the list when you use the same string on Betfair Search field, like so:

"Man Utd v Arsenal | 28 Feb 2016, 14:05"
"Man Utd v Watford | 2 Mar 2016, 20:00"
"Liverpool v Man Utd | 10 Mar 2016, 18:00"
etc

Even just "man united" by itself returns nothing from Events but on BF Search returns:

Man Utd v Arsenal | 28 Feb 2016, 14:05
Man Utd v Watford | 2 Mar 2016, 20:00
Liverpool v Man Utd | 10 Mar 2016, 18:00
Man Utd v West Ham | 12 Mar 2016, 00:00
West Brom v Man Utd | 6 Mar 2016, 16:00
Middlesbrough U21 v Man Utd U21 | 29 Feb 2016, 19:00

Or "Crystal Palace" on Events returns "East Bengal FC U19 v Crystal Palace FC U19 | 29 Feb 2016, 11:00"
but on BF search returns Sunderland v C Palace | 1 Mar 2016, 19:45

And "PSG" searching Events using BETTING API DEMO TOOL returns "Atletico Madrid v PSV"!!
But on BF Search returns:
Lyon v Paris St-G | 28 Feb 2016, 20:00
Chelsea v Paris St-G | 9 Mar 2016, 19:45

How are they doing it?! They have to have some kind of extra look up, surely, that they are using, clearly not just straight Events anyway?!


To confirm my JSON request string, which I confirm is working (just not returning results like BF Search) is:

GetListEventsRequestString = "{""filter"":{""eventTypeIds"":[""" & EventTypeId & """],""textQuery"":""" & searchString & """}}"

where searchString contains the team name or fixture as exemplified above.

Please help me to programmatically replicate the BF Search function.

[betdynamics]

Most likely you can't - at least not without a lot more data than is available to you via the API.

[SimonN]

Most likely you can't - at least not without a lot more data than is available to you via the API.

As feared, BetDynamics! Which just leaves web scraping to automate the BF Search but that is just sooo slow!

Tried it using IE and MS HTML Object library but it's painfully slow.. wonder if there's a quicker way but I doubt it with it having to open up a browser session for every request..and there's several needed to process

Thanks Mate

[jabe]

I think I'm missing the point here. Partly I don't understand what/why you're trying to do.

If you're making an API call, you want it actioning 100% immediately with no delays at all, and that means it takes your search string exactly as it comes. If there is a search function on Betfair itself, it needs to accommodate a variety of search strings, whether it's Man U, Man Utd, Man United, etc, to help users to get where they want.

I don't understand your "Man Utdv" string either. Is the lack of a space between "Utd" and "v" a typo or deliberate? Again, I can see why the Betfair site has some built-in leeway, but not why you'd want to put that in your program.

You know that "Man Utd" is how the club is named in Betfair data. If you want to search for matches involving specific teams, why not store all team names, perhaps with countries, and give your user the option to choose that way? Or add code with alternative names so your program caters for variation?

As I say, I don't know why you're trying to match the Betfair website search function, but I can see why the textQuery doesn't work the way you want it to.

I'm happy to listen and help if you wish to add more.

[SimonN]

Hello Jabe,

It's to compare team names the way bookmakers have spelled them and try to replicate the Betfair search function in the very good results the BF Search function returns on misspelled names.

I started off with some kind of partly dynamic dictionary like you're suggesting but of course better to get it direct from source if you can.

Anyway I've sort of made an adjustment so that it searches for individual words within fixture names which seems to be returning most, all, the records that include the correct one.

There's also an edit function to correct misspellings like the "Man Utdv" which I gave as an example. It wasn't that "Man Utdv" for example was a likely spelling error but just another illustration to demonstrate that the Events request is something quite different from the BF search function in the way it handles the query as BetDynamics says. That said you can never predict how bookies spell teams and it might surprise you how bizzare the misspellings can be at times. That's not so bad if it's a well known club but if it's an obscure foreign team then some of them team names look misspelled even when not misspelled lol.

Anyway thanks again to you both for replies. As I say I have divided the search string up so that it returns more records which seems to catch the right one for the user to select.

It's not ideal but it will have to do. Webscraping I doubt would be fast enough. I was just disappointed and surprised to see that the BF search function isnt exposed to the API.

[jabe]

Hi Simon

I see. Yes, I can understand that. I've long intended to create a database of football results and I've seen how different sites have different names for teams, so I intended to have an index containing multiple names for particular teams.

I've been comparing scores and results on FlashScores with the ones my program thinks it's derived from Betfair data, and some foreign team names are very different. On top of that, I can't find a way to scrape FlashScores.

I wonder if using the country, competition ID, and competition name on Betfair would help. Potentially you could assign each team an ID and then link a bookmaker with a particular version of the name. All very well until they use a different version of the name, of course.

Would it be too much to get all fixtures each day and store all the team names? If you store names by country and competition, you could also check how many you have for each competition and whether you have them all.

I don't know how often you're thinking of getting the data, but I'd expect Betfair to be consistent and keep the same names for the long term.

[SimonN]

Hello Jabe,

This is the 4th way I have attempted to realise a BF names corrector function:

1. Maintain some kind of "teams dictionary" with a known list of wrong names indexed with a betfair corresponding correct name list - as you suggest. Works but is fiddly inaccurate and relies on maintenance of up to date team names (the BF teams list I found online is extensive but a bit out of date). Labour intensive.

2. Web scrape the BF Search function using MS HTML Object library - correctly mimics BF Search - with exact identical results - but too slow.

3. Started scraping BF Search function using Selenium to see if could be faster than MS HTML Object Library but stopped after getting 4. below working. Suspect it would be faster than 2. but still nothing like API call.

4. Using BF API as discussed. This is now working well after tinkering with it so that - rather than searching using the complete fixture, each word in the event fixture is used as a search parameter individually.

For example I have "Inter Milan v Juventus" and I know that the correct BF name is "Inter v Juventus".Searching using:

"Inter Milan v Juventus" returns 0 records
"Inter Milan" returns 0 records
"Milan" returns 3 records incl AC Milan but not the correct "Inter v Juventus"
"Inter" returns 4 records including the correct "Inter v Juventus"
"Juventus" returns 3 records also including the correct fixture "Inter v Juventus"

So, splitting the fixture string up and using each part of the fixture as search parameter works well returning a short enough list of results to enable the operator to quickly choose the correct fixture (I also return fixture date and time too).

Doing this also results in a greater likelihood that Events search finds correct teams even with misspelled names but for the cases when it is not clever enough to recognise misspelled names I have an edit function to enable the operator to manually update the fixture to something more closely resembling correct team names which can then be used to repeat the Events search with a greater likelihood of finding the correct BF fixture.

So it's been - as you maybe can see - quite an endeavour - for what should have been a fairly small job! Initial testing shows it to be working beautifully now though - very powerful.

How was the scraping Jabe? What scraping technologies did you try?

Cheers!

Hi Simon

I see. Yes, I can understand that. I've long intended to create a database of football results and I've seen how different sites have different names for teams, so I intended to have an index containing multiple names for particular teams.

I've been comparing scores and results on FlashScores with the ones my program thinks it's derived from Betfair data, and some foreign team names are very different. On top of that, I can't find a way to scrape FlashScores.

I wonder if using the country, competition ID, and competition name on Betfair would help. Potentially you could assign each team an ID and then link a bookmaker with a particular version of the name. All very well until they use a different version of the name, of course.

Would it be too much to get all fixtures each day and store all the team names? If you store names by country and competition, you could also check how many you have for each competition and whether you have them all.

I don't know how often you're thinking of getting the data, but I'd expect Betfair to be consistent and keep the same names for the long term.

[jabe]

Hi Simon

I'm glad you've found a method that works. I do wonder whether just getting all match Events for the relevant days and then searching that data might be more flexible. I remember writing some code (but I can't think where or why) a few years back that narrowed down a search as I typed, so after every character added to the search string, it'd show results containing what I'd typed so far. It was remarkably fast, like typing into the Google home page. It would soon narrow down the search, even in a list of a few thousand football matches.

I gave up trying to scrape FlashScores some time ago. I couldn't view the page source and I couldn't work out how the data was being fed to the page. Perhaps another time. I tried HTMLAgilityPack a few weeks ago on another site, but I must have had my turnip head on, because it didn't do as I wanted, and the documentation didn't help. I'll try again when I've more time.

[SimonN]

Pleased to say it is working like a dream so far.

Yes that does sound like a good solution though that I would definitely have been open to - narrowing down as you type sounds boss and I will certainly keep in mind your idea going forward.

Thank you for your insights and support.

[jabe]

Glad to be of help, if I have been! Good luck with the rest of your plans.