Using Excel VB Sample Code Sheet

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

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

[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

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

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]

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.

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

[jabe]

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

but that failed altogether.

It's only data in strings, and the Betfair server has standard responses, so don't worry about unwanted data.

You might find a look at the JSON tutorial on the w3c website worthwhile. It'd save you trying things like the one above.

[judgement]

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.

is the traded volume the same as totalMatched? i am getting a figure for totalMatched returned.

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/

i've started using it a little but didn't know this is the visualiser. i've seen a lot of references to the visualiser but didn't realise this is it!

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)

that seems a good way to find out the code to use. i just gave it a quick try but couldn't immediately see the code of the request but will have a better look later.

[judgement]

It's only data in strings, and the Betfair server has standard responses, so don't worry about unwanted data.

ok thanks at some stage i will get on to extracting relevant bits from returns.

You might find a look at the JSON tutorial on the w3c website worthwhile. It'd save you trying things like the one above.

so much to learn and so little time!

[betdynamics]

is the traded volume the same as totalMatched? i am getting a figure for totalMatched returned.

No - my mistake. tradedVolume is not the same at totalMatched.

[jabe]

ok thanks at some stage i will get on to extracting relevant bits from returns.

so much to learn and so little time!

There is a lot to learn. No short cuts, alas. I used to be a programmer for a living and my current APING program is probably the most complicated one I've ever written. Good luck with your!

[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

listCurrentOrders added in the same way..

added to Example module:

Request = MakeJsonRpcRequestString(listCurrentOrdersMethod, GetlistCurrentOrdersRequestString())
Dim listCurrentOrdersResponse As String: listCurrentOrdersResponse = SendRequest(GetJsonRpcUrl(), GetAppKey(), GetSession(), Request)
Sheet1.Cells(OutputRow + 1, OutputColumn).value = listCurrentOrdersResponse

declared in the Util module:

Public Const listCurrentOrdersMethod As String = "listCurrentOrders"

added in Util module:

Function GetlistCurrentOrdersRequestString() As String
GetlistCurrentOrdersRequestString = "{""marketIds"":[""" & MarketId & """],""fromRecord"":0,""recordCount"":0}"
End Function

lifted from

JSON request for listCurrentOrders:

{"jsonrpc":"2.0","method":"SportsAPING/v1.0/listCurrentOrders","params":{"marketIds":["1.116451712"],"fromRecord":0,"recordCount":0},"id":1}

thanks betdynamics

£2 unmatched bet placed on the relevant match.
response to listCurrentOrders call:

{"jsonrpc":"2.0","result":{"currentOrders":[{"betId":"77337570265","marketId":"1.127508662","s electionId":55190,"handicap":0.0,"priceSize":{"pri ce":2.26,"size":2.0},"bspLiability":0.0,"side":"BA CK","status":"EXECUTABLE","persistenceType":"LAPSE ","orderType":"LIMIT","placedDate":"2016-10-19T18:42:42.000Z","averagePriceMatched":0.0,"sizeM atched":0.0,"sizeRemaining":2.0,"sizeLapsed":0.0," sizeCancelled":0.0,"sizeVoided":0.0,"regulatorCode ":"GIBRALTAR REGULATOR"}],"moreAvailable":false},"id":1}

[judgement]

response to listCurrentOrders call:

{"jsonrpc":"2.0","result":{"currentOrders":[{"betId":"77337570265","marketId":"1.127508662","s electionId":55190,"handicap":0.0,"priceSize":{"pri ce":2.26,"size":2.0},"bspLiability":0.0,"side":"BA CK","status":"EXECUTABLE","persistenceType":"LAPSE ","orderType":"LIMIT","placedDate":"2016-10-19T18:42:42.000Z","averagePriceMatched":0.0,"sizeM atched":0.0,"sizeRemaining":2.0,"sizeLapsed":0.0," sizeCancelled":0.0,"sizeVoided":0.0,"regulatorCode ":"GIBRALTAR REGULATOR"}],"moreAvailable":false},"id":1}

the calls to and responses from Betfair i have done so far have been for 1 football match each. is it possible to get information from Betfair for more than 1 match at a time eg all Premier league matches, all GB matches or even all football matches or do you have to make separate calls for each match?

[jabe]

It is possible to do calls to get data for several games. The data is hierarchical in nature, so you might start at the Event level (perhaps specifying the Event type for football along with a date). 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.

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.

For the listEvents call, countryCode can be specified to reduce the number of Events returned. The marketFilter mentions competitionCode, but competitionCode isn't in the Event class, and I don't know if you could specify it in a listEvent call. I'd guess not. The MarketCatalogue does include competitionCode, so perhaps you could narrow things down later with that.

You might want to make a few calls, during when your program starts, such as listCountries, list Competitions, etc. Be aware that the countryCode may not always exist or be valid, so you might need to fill in gaps.

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

If you do intend to make calls for multiple matches of markets, you do need to be aware that there are weighting limits for certain calls, as listed in this link:
http://docs.developer.betfair.com/do...Request+Limits

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.