Excel Silks Description

[jc0r]

Hi

I am trying to find away to get the description of the silks (shown when you hover of the silk in BA), not the actual silks themselves, into Excel.

When i right click on a silk in Chrome and Inspect Element, under title, you can see the description of the silk. I was wondering if it would be possible to have some VBA get this information for each horse based on the URL provided when you log Silk URL?

Thanks in advance

[Captain Sensible]

Shouldn't be too hard all you need is available thru excel, I have VBA that runs once when a market runs and that actually uses data from the webpage does a few calcs and lines it back on the excel sheet.

Obviously a lot depends on you level of VBA skills, mine are still limited and I still rely on some of my old php scripts to do the backend regex stuff.

The excel sheet display the market ID in cell N3 or N2, it needs to be prefixed with a 1 for UK markets and 2 for AUS markets so 120987150 in N2 would need to be sent to the web as 1.120987150 because it's a UK market. I've no idea why Gruss omits the country digit as the data from the API is sent with it so they must strip it out for some unknown reason, either it's a pain.

You'd then need your VBA to grab the data off the webpage using either of these URL's where $marketId is your 1.120987150 etc

Code: Select all

UK server

http://uk.site.sports.betfair.com/betting/InitialMarketDataAction.do?mi=$marketId

Aus Server

http://au.site.sports.betfair.com/betting/InitialMarketDataAction.do?mi=$marketId

That'd get you the raw webpage and VBA regex can easily get you the racing silk data and align it in some column against the runner using the name to match, the runner data is laid out below so easily scraped.

Code: Select all

p.m_ER(89425462,28474,120991377,'Triple Eight',0,false,5.0,36.0,5.1,9.0,5.2,19.0,5.4,6.0,5.5,52.0,5.6,4.0,oM,9,0,"http://content-cache.betfair.com/feeds_images/Horses/SilkColours/c20151001ban/00056522.jpg","Light blue and black check, light blue sleeves, black armlets, light blue cap.","Adam Nicol","Philip Kirby","7 / 10-1","6778-12","11","3 ","")



[jc0r]

Hi Capt

Thanks for your response. I have the following code that will do a simple web query for me and any given page. As you can see, i have edited the page to display a BF horse race (17:50 Beverley) however it just returns a blank cell. This is also echoed when i put that web address directly into my browser, a blank page is returned. Any ideas?

Code: Select all

Sub WebQuery()
Dim url As String
url = "URL;http://uk.site.sports.betfair.com/betting/InitialMarketDataAction.do?mi=1.120991421"
With Worksheets("Sheet1").QueryTables.Add(Connection:=url, Destination:=Worksheets("Sheet1").Range("A1"))
  .Name = "Web Query"
  .RowNumbers = False
  .FillAdjacentFormulas = False
  .PreserveFormatting = True
  .RefreshOnFileOpen = False
  .BackgroundQuery = False
  .RefreshStyle = xlOverwriteCells
  .SavePassword = False
  .SaveData = True
  .AdjustColumnWidth = True
  .RefreshPeriod = 0
  .WebSelectionType = xlEntirePage
  .WebFormatting = xlWebFormattingNone
  .WebPreFormattedTextToColumns = True
  .WebConsecutiveDelimitersAsOne = True
  .WebSingleBlockTextImport = False
  .WebDisableDateRecognition = False
  .WebDisableRedirections = False
  .Refresh BackgroundQuery:=False
End With
End Sub


[Captain Sensible]

Try it without the exchange indicator in the id i.e 120991421 instead of 1.120991421 . Probably due to the way the variable is being sent, I think the Betfair site can parse both, my php code uses the server id just so I can send to the correct url for UK or AUS

[Captain Sensible]

Don't know if your VBA will return anythig as that's looking for tables but in a webpage you'll see the data returned is within the javascript tags as that's what the betfair site uses to update the data on the page

http://uk.site.sports.betfair.com/betti ... =120991421


Just right click the page and view source

[jc0r]

Yeah i have tried playing around with the URL and that was something i have tested. Without the exchange indicator, it just displays Market Data View on the page in Google Chrome

I can't seem to get anything displaying when i try to use the site http://uk.site.sports.betfair.com/ The only way i can get to markets by ID is using the default (i assume updated web address) https://www.betfair.com/exchange/plus/# ... d=27554376 however, Excels web query doesnt like this and returns nothing. I think that might be due to Excel using an older version of IE

[Captain Sensible]

Yeah i have tried playing around with the URL and that was something i have tested. Without the exchange indicator, it just displays Market Data View on the page in Google Chrome

That's because the data is hidden inside the javascript tags and won't display on a html screen, if you right click on the page you'll see all the hidden data returned by the URL, from there you'd just use some regex to put it in a format to use in excel

The webpage http://uk.site.sports.betfair.com/betti ... =120991421 will show

Code: Select all

Market Data View

but right clicking to view the page source will show

Code: Select all

<html><head>




      <script type="text/javascript">
         document.domain = "betfair.com";


         var exchangeCount = 2;

      </script>

<script language="Javascript">
var p = parent.parent.main;
p.interface_compareVersions("V20.0.23.0");
p.betexUIController.entryPt = "";
try {
   top.interfaces_getSubNavCmsFrame().showCHPageLinks('true');
}
catch (x){
}
p.interface_toggleCompetition(false);
p.interface_controlMyMarketsDisplay('false');
var thisMarketId = 120991421;
var oM = p.m_M(120991421,'Bev 1st Oct - 17:50 5f Hcap','<table cellborder=\"0\" width=\"100%\"><tr><td><img src=\"http://content-cache.betfair.com/images/en_GB/homepage/RUKlogo.gif\" border=\"0\"></td><td width=\"5\">&nbsp;</td><td class=\"marketInfo_GreyText\">This meeting is on RACING UK</td></tr></table><br><a href=\"http://form.timeform.betfair.com/\" target=\"_blank\"><img src=\" http://content-cache.betfair.com/images/en_GB/mr_fr.gif\" title=”Form/ Results” border=\"0\"></a>\n<br><br><b>MARKET INFORMATION</b><br><br>This market information applies to Exchange Singles bets only. Please see our <a href=http://content.betfair.com/aboutus/content.asp?sWhichKey=Rules%20and%20Regulations#undefined.do style=color:0163ad; text-decoration: underline; target=_blank>Rules & Regs</a> for further information, multiples and all other sections.<br><br>Who will win this race? Betfair Non-Runner Rule applies. This market will turn IN PLAY at the off with unmatched bets (with the exception of bets for which the \"keep\" option has been selected) cancelled once the Betfair SP reconciliation process has been completed. Betting will be suspended at the end of the race. Should there be a very close finish, a photo finish declared, a Stewards Enquiry or an Objection called the market may be re-opened with unmatched bets (again with the exception of bets for which the \"keep\" option has been selected) cancelled. The market will then be suspended when this result is announced. This market will initially be settled on a First Past the Post basis. However we will re-settle all bets should the official result at the time of the \"weigh-in\" announcement differ from any initial settlement. BETS ARE PLACED ON A NAMED HORSE. Dead Heat rules apply.<br><br>Customers should be aware that:<b><ol><li>transmissions described as \"live\" by some broadcasters may actually be delayed;</li><li>the extent of any such delay may vary, depending on the set-up through which they are receiving pictures or data;</b> and </li><li>information (such as jockey silks, saddlecloth numbers etc) is provided \"as is\" and is for guidance only. Betfair does not guarantee the accuracy of this information and use of it to place bets is entirely at your own risk.</li></ol><br><br/>UK wallet','GBP&nbsp;6,852',7,1443721800000,'17:50','Odds',false,false,1,27554376,1,0,'Standard',null,0.0,5.0,true,60.0,null,null,null,'2015-10-01 17:50 Europe/London (GMT+1.00)',null,true);
p.m_ER(89421570,7378276,120991421,'Lazy Sioux',0,false,5.0,178.0,5.1,37.0,5.2,83.0,5.3,6.0,5.5,14.0,5.6,17.0,oM,2,2,"http://content-cache.betfair.com/feeds_images/Horses/SilkColours/c20151001bev/00046308.jpg","Royal blue, light green stars on sleeves, royal blue cap, light green star.","Gemma Tutty","Karen Tutty","4 / 9-6","139474","8","5 ","")
p.m_ER(89421550,10122625,120991421,'Anieres Boy',1,false,5.5,11.0,5.6,4.0,5.7,2.0,6.0,2.0,6.2,4.0,6.4,18.0,oM,7,4,"http://content-cache.betfair.com/feeds_images/Horses/SilkColours/c20151001bev/00071369.jpg","White, red cross belts, red and white striped sleeves, hooped cap.","Graham Gibbons","Michael Easterby","3 / 9-2","6357","17","0 ","")
p.m_ER(89421562,6275605,120991421,'Perfect Words',2,false,10.5,6.0,11.0,12.0,11.5,10.0,12.5,2.0,13.0,6.0,13.5,4.0,oM,3,5,"http://content-cache.betfair.com/feeds_images/Horses/SilkColours/c20151001bev/00063060.jpg","Dark green, light green stripe, yellow cap.","James Sullivan","Marjorie Fife","5 / 9-5","775566","19","0 ","cheekpieces")
p.m_ER(89421566,1550158,120991421,'Oldjoesaid',3,false,9.6,25.0,9.8,4.0,10.0,3.0,11.0,3.0,11.5,21.0,12.0,20.0,oM,1,12,"http://content-cache.betfair.com/feeds_images/Horses/SilkColours/c20151001bev/00077645.jpg","Royal blue and yellow check, royal blue and white check sleeves, yellow cap.","Paul Mulrennan","Paul Midgley","11 / 9-7","845740","8","0 ","")
p.m_ER(89421558,6309088,120991421,'Adiator',4,false,9.6,26.0,9.8,7.0,10.0,6.0,10.5,49.0,11.0,14.0,11.5,7.0,oM,5,8,"http://content-cache.betfair.com/feeds_images/Horses/SilkColours/c20151001bev/00001958.jpg","Royal blue, yellow sash, royal blue and white hooped sleeves, royal blue and yellow hooped cap.","Robert Winston","Neville Bycroft","7 / 9-4","900697","8","0 ","cheekpieces")
p.m_ER(89421553,6290196,120991421,'Fortinbrass',5,false,10.0,55.0,10.5,19.0,11.0,4.0,11.5,2.0,12.0,87.0,12.5,7.0,oM,4,9,"http://content-cache.betfair.com/feeds_images/Horses/SilkColours/c20151001bev/00042286.jpg","Light blue, white diamond, diamonds on sleeves.","Shane Gray","John Balding","5 / 9-4","5-50066","70","0 ","")
p.m_ER(89421584,7407448,120991421,'Tinsill',6,false,13.0,5.0,13.5,26.0,14.0,6.0,15.0,17.0,16.0,2.0,16.5,6.0,oM,10,1,"http://content-cache.betfair.com/feeds_images/Horses/SilkColours/c20151001bev/00063264.jpg","Light blue, dark blue disc and armlets.","Andrew Mullen","Nigel Tinkler","4 / 8-10","505890","33","0 ","cheekpieces")
p.m_ER(89421577,8797730,120991421,'Zebelini',7,false,12.0,4.0,13.0,11.0,13.5,7.0,15.0,31.0,16.0,9.0,16.5,9.0,oM,6,6,"http://content-cache.betfair.com/feeds_images/Horses/SilkColours/c20151001bev/00834200.jpg","Royal blue, red stars, red and royal blue striped sleeves, royal blue and red striped cap.","Jacob Butterfield","Ollie Pears","3 / 9-3","4-65036","57","3 ","hood")
p.m_ER(89421574,8692942,120991421,'George Bailey',8,false,16.0,13.0,17.0,6.0,17.5,3.0,19.0,2.0,21.0,2.0,24.0,3.0,oM,8,7,"http://content-cache.betfair.com/feeds_images/Horses/SilkColours/c20151001bev/00074272.jpg","Light blue, black seams, light blue sleeves, black stars and cap.","Tom Eaves","Suzzanne France","3 / 8-12","571056","34","0 ","")
p.m_ER(89421588,8826167,120991421,'Kinloch Pride',9,false,23.0,2.0,24.0,4.0,25.0,4.0,27.0,8.0,28.0,8.0,36.0,4.0,oM,9,11,"http://content-cache.betfair.com/feeds_images/Horses/SilkColours/c20151001bev/00031000.jpg","Yellow, large emerald green spots and star on cap.","Joe Fanning","Noel Wilson","3 / 8-11","057634","10","0 ","cheekpieces")
p.m_ER(89421592,822914,120991421,'Grenade',10,false,25.0,3.0,26.0,3.0,29.0,2.0,38.0,6.0,40.0,18.0,42.0,8.0,oM,11,3,"http://content-cache.betfair.com/feeds_images/Horses/SilkColours/c20151001bev/00834113.jpg","Black, orange hoop and armlets, quartered cap.","Duran Fentiman","Patrick Holmes","3 / 8-8","505640","35","0 ","hood")
p.m_ER(89421595,7334237,120991421,'Beautys Forte',11,false,32.0,3.0,34.0,2.0,38.0,4.0,40.0,4.0,42.0,9.0,46.0,9.0,oM,12,10,"http://content-cache.betfair.com/feeds_images/Horses/SilkColours/c20151001bev/00041068.jpg","White, red spots on sleeves, red cap.","Neil Farley","Declan Carroll","4 / 8-7","260588","10","0 ","visor")
var oCPLs = new p.m_CMLC();
p.interface_setCouponParentLinks(oCPLs);
p.interface_setMarket(oM);
var oPLAs = p.m_PLAs();
oPLAs.add(p.m_PLA(7378276,null,null,null));
oPLAs.add(p.m_PLA(10122625,null,null,null));
oPLAs.add(p.m_PLA(6275605,null,null,null));
oPLAs.add(p.m_PLA(1550158,null,null,null));
oPLAs.add(p.m_PLA(6309088,null,null,null));
oPLAs.add(p.m_PLA(6290196,null,null,null));
oPLAs.add(p.m_PLA(7407448,null,null,null));
oPLAs.add(p.m_PLA(8797730,null,null,null));
oPLAs.add(p.m_PLA(8692942,null,null,null));
oPLAs.add(p.m_PLA(8826167,null,null,null));
oPLAs.add(p.m_PLA(822914,null,null,null));
oPLAs.add(p.m_PLA(7334237,null,null,null));
p.interface_setPAndLAnnotations(oPLAs);
p.interfaces_setMarketPAndLForSetteledBets(0.0);
p.interface_setMediaControls(true,'http://radio.betfair.com/', true,'http://livevideo.betfair.com/', thisMarketId);
p.interface_setFormLink(false,'null', 'Form/Results');

   p.interface_setUserLoginStatus(true);

   p.interface_setUserReplicated(true);
p.interface_controlMarketViewRealEstate('resize');
p.interface_setMiniGames();

</script>
</head>
<body>Market Data View

</body></html>

[jc0r]

Ahh ok i get where you're going with this now

Right, just need to learn what regex is and how to extract the information into Excel when i provide the URL i suppose?

Thanks again

[Captain Sensible]

Regex is just regular expressions that allows you to parse the html, I'm afraid all my knowledge is well out of date and php based rather than VBA otherwise I'd try and help a bit more.

All computing languages have regex functions able to parse the data but I think you probably need to look at the more modern DOM functions these days rather than regex as that seems to be the way things are going especially with things being more standardised across the web.

If you do get it sorted I'd be interested to see the coding as I've been meaning to port my php stuff to vba for ages but never get the time or inclination as it does what's needed even if it's not pretty.

[jc0r]

I don't mean to take anything away from your efforts so far as they're much appreciated but I posed the same question to Gary who has just got back to me saying he will try to add the description as a separate column in Excel. If this can be done, I feel it would be far more effective than any vba script I could come up with. I'll wait to hear from this development before pursuing any further. Thanks

[Captain Sensible]

No problem it should be a simple thing for Gary to add as the data's already available as another field when the first API market call is done, much easier if he includes it as an additional field than messing around with vba.