PDA

View Full Version : Importing Odds (that refresh) into Excel


CBedo
09-24-2009, 01:04 AM
I have gotten quite a few questions about scraping odds data to use to make betting decisions. Although there are much more elegant methods, I'm gong to show everyone how to pull the brisnet supertote odds information into an excel spreadsheet that will refresh automatically, and you can use for whatever your heart desires. I hope this is pretty straight forward, but if you can't get it to work, post questions in this thread, and I'll try to help (or someone might even know a better way that they will share).

I'm using Excel 2007 for this demonstration, but if I remember correctly, you do the same thing with similar steps in older versions. I'm not sure about OpenOffice or GoogleDocs, but it wouldn't surprise me if they have this feature.

Ok, let's fire up Excel 2007, open a new workbook, and let's goto work!

Steps to Import Odds into a spreadsheet

1)In the data tab, on the left, there is a “Get External Data” block. Choose “From Web.”

2)Choose the data tab; then on the left side, there is a “Get External Data” block. Choose “From Web.”

3)Excel will pull up an IE instance, type in www.brisnet.com (http://www.brisnet.com/) in the address space, just as you would in your browser.

4)Navigate to Supertote, choose a track (or tracks) and start Supertote. You will probably have to enter your username and password, just as when you use BRIS normally.

5)You will see small arrows in the corners of each section of the website. When you hover the mouse over the arrow, it will show you what section of the site you will be importing.

6)Choose, the arrow that highlights the summary odds for up to five tracks (the top section), and click the import button. You could just as easily use the pool information or the exotic probables. If a section you want doesn’t seem to have a arrow icon, click the “Hide Icons” button on the top right (yellow arrow icon with a red x in the bottom) twice, once to hide all icons, and once to bring them back. This should refresh the sections.

7)If you are importing odds, before you click the import button, click options in the top right and disable date recognition (otherwise your 7/2 & 4/5 type odds won’t show up correctly).

8)Click Import.

9)Choose a cell that will be the top left of the odds you are trying to import.

10)Before you Click OK, click properties, and check the Refresh Every X Minutes options and change the number to 1 (or however often you want it to update). Also, play with the other formatting such as the auto adjusting of column widths and the how to handle the section when the data changes (new race). Click OK when done to get back to the import data window.

11)Click OK, and your data should be imported into your spreadsheet.

That’s it. You now have an updating odds board in a spreadsheet that you can then use to do whatever odds or value calculations you would love to do. If you have pool or exotic data on a specific track, you can edit the web query and change the track codes if you need to and it will update your sheet.

I recommend using one sheet for the raw data import, and then another spreadsheet that accesses the data. That way you don’t have to worry about messing up the query, or dealing with the formatiting.

I hope this helps, and now that you all have access to the odds, I expect you to only bet overlays!

headhawg
09-24-2009, 10:36 AM
Very nice explanation. Thanks.

Warren Henry
09-24-2009, 11:05 AM
THANKS for sharing that technique. Much appreciated. :ThmbUp: :ThmbUp:

DanG
09-24-2009, 11:57 AM
These are the posts that make wading through the occasional internet nonsense worthwhile. Very generous use of your time CB, thank you.

CBedo
09-24-2009, 01:42 PM
These are the posts that make wading through the occasional internet nonsense worthwhile. Very generous use of your time CB, thank you.I thought you were saying you had to wade through my nonsense to find the occasional nugget--which would probably be fairly accurate! ;)

I'm not an Excel guru, and I know almost zero VB, so maybe someone out there can help me extend this technique a bit. What would be really great would be to have a cell that had the track code in it, and then have Excel insert the track code into the web query. I did this before when scraping stock quotes, but I have no clue how I did it. :bang:

DanG
09-24-2009, 02:25 PM
I thought you were saying you had to wade through my nonsense to find the occasional nugget--which would probably be fairly accurate! ;)

I'm not an Excel guru, and I know almost zero VB, so maybe someone out there can help me extend this technique a bit. What would be really great would be to have a cell that had the track code in it, and then have Excel insert the track code into the web query. I did this before when scraping stock quotes, but I have no clue how I did it. :bang:
No; the nonsense slingers know who they are and Lord knows I’ve done my share.

I know what you mean concerning some VB skills. I also take the sledgehammer approach with Access & Excel. Just too busy using it to go back and learn the full blown application is my current excuse.

CBedo
09-24-2009, 02:45 PM
Alrighty then. After my last post about not knowing how to make a query dynamic, I decided to not be lazy and actually figure it out! It's amazing what you can accomplish when you decide to actually try!

I figured it out, and will post it as soon as I get a minute to write it up (it's pretty simple actually).

So be on the lookout for it!

CBedo
09-24-2009, 03:42 PM
Steps to be able to change track codes in your spreadsheet

1) Follow the exact same process as in the above directions up to and including step 7 (for this example, I’m going to choose the pool data for a track).

2) In the upper right corner of the window, just to the left of the options button, there is a button to save your query. Click it, and save it. You should get a text file with a .iqy file extension. Be sure to take notice of the location of the file.

3) For the moment, cancel your query. It’s time to get dynamic!

4) Open the saved query with notepad My query files are in the C:\Users\Bedo\AppData\Roaming\Microsoft\Queries directory (I told you to make a note of where it was!).

5) The third line of the file should contain the web address of the query. Somewhere in that line you should see “pools.cgi?track=” and then the track code of the data you are pulling into Excel.

6) Change the three letter track code to [“keyword”, “Enter Track Code:”].

7) The address should now look something like: http://www.brisnet.com/cgi-bin/totedb/pools.cgi?track=["keyword", "Enter Track Code:"]&type=0

8) Save your work and exit notepad.

9) Now head back to Excel, and somewhere in your sheet enter a track code (for example, enter BEL into A1).

10) In the data tab, instead of choosing “From Web” in the “Get External Data” section, choose “Existing Connections.”

11) Choose your recently created web query which should be listed under “Connection files on this computer,” and click open.

12) Make sure to click properties and change the refresh time and other formatting just as we did in step 10 in the first set of instructions.

13) After changing the properties, click OK, and another small window should open up that says “Enter Track Code:” Select the cell where you entered the track code in step 9.

14) Make sure to check both the “Use this value/reference for future refreshes” and “Refresh automatically when cell value changes.” Click OK and let’s see if our work paid off

15) You should now see the odds and pool data for track you selected. You should be able to change the track, and the sheet should change the data to show you that’s track’s information (there could be a slight delay in calculation depending on the speed of your machine. Also, if you have your sheets set to manual calculation, you might have to recalculate (F9) to get it change tracks.

Hope this works for everyone, and helps you all make some profitable bets!

headhawg
09-24-2009, 04:51 PM
I echo DanG. Great stuff again. Thanks CB.

Yowie! The Handicapping Software section has really been full of lots of useful things lately. :)

formula_2002
09-27-2009, 08:02 AM
CB, thanks for the post. You have encouraged me to revisit my "BETITANDFORGETIT" automated program.
Using a form of VB and dbV, I was able to log onto Brisbet and even make a bet.
The problem was getting the betting pool dollars in a reliable fashion.

Ray2000
09-27-2009, 05:43 PM
CBedo,

Thanks for posting that info. Your generosity has prodded me into attaching this Excel Spreadsheet. (No macros, virus scanned negative). Once the data connections are established to the web source by your description, the data will refresh and the exacta overlays will be highlighted in rows 30-41 on Sheet "Exactas". It uses the Harville method* which tries to exploit the exacta pool based on win probabilities. It only handles 12 or less field size, (I play harness:))

I use TwinSpires, Brisnet's "Sister Company" and the import and refresh data works for me as you described but you must navigate to "Wager Now" ---> Select your track---> Start---->Exotics---> Exacta to select the starting table, (Hide-show icons options is necessary) and then do the same for the Win/Place/Show Pools table. After establishing and saving the data source queries, you can alter it to show a different track using Edit Queries, or using your .iqy text edit method. (set folder options to show hidden files to get to the query, if you're running Vista be sure to access the "AppData" folder NOT "Application Data" shortcut)

I do have one question for you. I cannot come back the next day and re-use the query because I get Session Expired errors. I need to delete the connections and re-establish them. Is there any way around this other than using a macro to delete and regenerate new connects?

Also..
The Harville method has been studied alot and probably (pun intended) found inferior to others, but is the easiest to calculate.

*
http://libra.acadiau.ca/library/ASAC/v26/02/26_02_p032.pdf (http://libra.acadiau.ca/library/ASAC/v26/02/26_02_p032.pdf)


BTW... a bit off topic but the author of the referenced paper asks a very intriguing question.

If the exacta pool is efficient can it be used to exploit the win pool.? In other words, since barn money is commonly hidden by staying away from the win pool and put into Exacta wheels maybe we should look for overlays in the other direction.:)

Finally, I make no claims as to the accuracy of the formulas or any suggestion to use this spreadsheet to make bets. I use it to sniff out Exacta pool "strangeness" when I have time to watch the tote-board.

Jeff P
09-27-2009, 08:47 PM
I do have one question for you. I cannot come back the next day and re-use the query because I get Session Expired errors. I need to delete the connections and re-establish them. Is there any way around this other than using a macro to delete and regenerate new connects?There is but IMHO opinion it's more trouble than the alternative...

When you log in (userid and password) their site puts a new session cookie on your machine good for X-number of hours... something like 8 to 10 hrs if memory serves...

IMHO, the easiest best way to handle a session expired condition is to write an assertion that calls a sessionExpired function. The sessionExpired function contains a simple html parse routine that tests for a session expired condition and returns either True or False back to the caller...

If I were using Excel (which I'm not) I'd do this using VBA code... but I suppose a macro could be made to work too...

The hml that delivers the session expired message is different than the html from a normal page.. easy enough to find the words "session expired" embedded in it and then react accordingly...

When the sessionExpired function returns True just establish a new connection and get a new session cookie... retest the connection... And of course if it returns False continue on as if nothing had happened.

Inserted into the normal program flow of an ASP page, in VBScript it looks something like this:

'Assertion: Test The Connection before continuing on -
If SessionExpired(strPageHTML) Then

'The Session has expired - get a new session cookie
If getNewConnection(strPageURL) Then

'new connection established - continue on...

Else
'session still expired - reload the page
Response.Redirect(strPageURL)

End If

End If

Clean... neat... seamless.


-jp

.

Jeff P
09-27-2009, 08:49 PM
If the exacta pool is efficient can it be used to exploit the win pool.? I say yes... as can pool information from locations other than the host track.

-jp

.

Ray2000
09-28-2009, 10:14 AM
Thanks for the reply, Jeff good info

If I ever get around to using ASP Web scripts, it would be a good check.
Right now I'm doing a project with VB6 for TrackMaster's Development Challenge Contest,
so I'll stay in an area where I'm more comfortable.

I just went thru the pages at JCapper... WOW ...Quite a lifetime work of coding...

Ever apply it to Harness Racing?

CBedo
09-28-2009, 11:31 AM
I do have one question for you. I cannot come back the next day and re-use the query because I get Session Expired errors. I need to delete the connections and re-establish them. Is there any way around this other than using a macro to delete and regenerate new connects? Jeff's version is the "right" way to do it. The brute force ugly method is to save your query and open it in a sheet with no other calculations, and have all your other calculations in a different sheet that will read the query. Then you don't have to rebuild the whole thing each time, just re-log in and import the query. Ugly, but effective.

Just as Ray has so kindly left his sheet, hopefully others will use and extend the examples I started so that the method will get better for all of us.

Jeff P
09-28-2009, 11:32 AM
Ray,

No. But funny you should ask.

I've recently been in talks with one of the more prominent Canadian providers for harness data over a joint project.

Things are very preliminary at this point. Far too early to say whether or not such a project even gets off the ground.

It IS intersting to note that harness data is sourced in a very simiar manner as thoroughbred data: time, position, beaten lengths, driver, trainer, owner, class level, etc.

But to my knowledge - the collective body of work being done modeling harness data is nothing compared to what's being done (and under current development <G>) with thoroughbred data.


-jp

.

Ray2000
09-25-2010, 06:43 AM
I recently updated the Spreadsheet that is attached to Reply #11 in this thread, which I use for importing Odds into Excel. The new version can be found in the Harness forum at

http://www.paceadvantage.com/forum/showthread.php?t=75328 reply#5


Thought I'd mentioned it here. This one contains a VBA macro to refresh both Win and Exacta pools for any track at Twinspires. It compares current Win Odds with Win Odds by Exacta pool amounts, strange win/place or win/show betting and Harville minimum acceptable Exacta prices

alsoran
10-03-2010, 01:31 PM
Great work. A problem that I have is that the BRIS odds are texts and I can use value() to convert most of these to numbers, but fractional odds (5/2 or 9/5 etc) convert to integers in the 40000 range. Does any one know how to get 5/2 in text to convert to 2.5?

Jeff P
10-03-2010, 05:29 PM
Great work. A problem that I have is that the BRIS odds are texts and I can use value() to convert most of these to numbers, but fractional odds (5/2 or 9/5 etc) convert to integers in the 40000 range. Does any one know how to get 5/2 in text to convert to 2.5?
Let's say for the sake of argument that you are using VBA code to parse a web page that shows current odds. Let's further say that you've got a good parse routine that has located the section of that page where current odds are displayed and that the name of the variable in your code currently holding the string (text) value of the current odds is named strCurrentOdds.

Given the above, I'd use a series of If Then statements to convert string (text) values to numeric odds to be stored in a numeric variable.

Example in VBA Code:
'Declare variable to hold numeric odds
Dim sngNumericOdds As Single

'Remove excess spaces
strCurrentOdds = Trim(strCurrentOdds)

'Assign Numeric Odds
If strCurrentOdds = "1/9" Then sngNumericOdds = 0.1
If strCurrentOdds = "1/5" Then sngNumericOdds = 0.2
If strCurrentOdds = "1/4" Then sngNumericOdds = 0.25
If strCurrentOdds = "3/10" Then sngNumericOdds = 0.3
If strCurrentOdds = "4/10" Then sngNumericOdds = 0.4
If strCurrentOdds = "2/5" Then sngNumericOdds = 0.4
If strCurrentOdds = "5/10" Then sngNumericOdds = 0.5
If strCurrentOdds = "1/2" Then sngNumericOdds = 0.5
If strCurrentOdds = "3/5" Then sngNumericOdds = 0.6
If strCurrentOdds = "4/5" Then sngNumericOdds = 0.8
If strCurrentOdds = "1/1" Then sngNumericOdds = 1
If strCurrentOdds = "6/5" Then sngNumericOdds = 1.2
If strCurrentOdds = "7/5" Then sngNumericOdds = 1.4
If strCurrentOdds = "3/2" Then sngNumericOdds = 1.5
If strCurrentOdds = "8/5" Then sngNumericOdds = 1.6
If strCurrentOdds = "9/5" Then sngNumericOdds = 1.8
If strCurrentOdds = "2" Then sngNumericOdds = 2
If strCurrentOdds = "2/1" Then sngNumericOdds = 2
If strCurrentOdds = "5/2" Then sngNumericOdds = 2.5

Your code would have to handle every possible case the page you are parsing uses to display odds as string (text) values.

Alternatively (and even better) your VBA routine could be made to parse pool totals and the amount wagered on each entrant instead of current odds in string form. From there, your routine could apply the takeout and calculate decimal odds to the nearest 0.10 or 0.05 depending on the racing jurisdiction you are parsing/playing.


-jp

.

Bill F
10-03-2010, 07:58 PM
Great work. A problem that I have is that the BRIS odds are texts and I can use value() to convert most of these to numbers, but fractional odds (5/2 or 9/5 etc) convert to integers in the 40000 range. Does any one know how to get 5/2 in text to convert to 2.5?

Jeff's VBA method is probably the way to go, but the way I handle it is with two columns using "Substitute" formulas.

Assuming the Bris odds for the #1 horse are in cell C2, then enter into cell D2 (or whatever cell you choose):
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(C2,"1/9","0.1"),"1/5",".2"),"2/5",".4"),"1/2",".5"),"3/5",".6"),"4/5",".8"),"6/5","1.2")

Then in the next column enter:
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"7/5","1.4"),"3/2","1.5"),"8/5","1.6"),"9/5","1.8"),"5/2","2.5"),"7/2","3.5"),"9/2","4.5"))

Then copy those two formulas down thru row 17.

Works for me, hope it helps.

raybo
10-03-2010, 09:32 PM
Great work. A problem that I have is that the BRIS odds are texts and I can use value() to convert most of these to numbers, but fractional odds (5/2 or 9/5 etc) convert to integers in the 40000 range. Does any one know how to get 5/2 in text to convert to 2.5?

If you know the cells for the odds, and, you're referencing those cells in another area of the worksheet, for calculation purposes, why not just use a formula to change the text to a number?

First, assume that cell A1 contains win odds (in text format, and, assume that the text 5/2 is in there), then assume that cell F1 is where you want those text odds changed to a number. Right click cell F1, select "Format cells", then select "Number", then change the "Decimal places" to 1. Now select cell F1, and enter the following formula:

=IF(AND(LEN(A1)=3,MID(A1,2,1)="/"),MID(A1,1,1)/MID(A1,3,1),A1)

Now, to test it, format cell A1 as "Text", then type in that cell: 5/2

Cell F1 should now show 2.5. Change the odds in cell A1 to whatever you wish and see if cell F1 is showing the correct odds, in number format.

The formula assumes that there will never be more than 3 characters in cell A1. The only fractional odds I've ever seen on a tote board are 1/9, 1/5, 2/5, etc., 6/5, 7/5, 3/2, 8/5, 9/5, 5/2, 7/2, 9/2, etc..

I've never seen 10 as a denominator, which would mean you would have to modify the formula, by first testing "IF(Len(A1)=4," and then change the Mid stuff to reflect the extra character.

This stuff is just off the top of my head, haven't really thought too much about it, so, be sure to test it thoroughly to make sure it does what you want.

CBedo
10-04-2010, 02:08 AM
If you know the cells for the odds, and, you're referencing those cells in another area of the worksheet, for calculation purposes, why not just use a formula to change the text to a number?

First, assume that cell A1 contains win odds (in text format, and, assume that the text 5/2 is in there), then assume that cell F1 is where you want those text odds changed to a number. Right click cell F1, select "Format cells", then select "Number", then change the "Decimal places" to 1. Now select cell F1, and enter the following formula:

=IF(AND(LEN(A1)=3,MID(A1,2,1)="/"),MID(A1,1,1)/MID(A1,3,1),A1)

Now, to test it, format cell A1 as "Text", then type in that cell: 5/2

Cell F1 should now show 2.5. Change the odds in cell A1 to whatever you wish and see if cell F1 is showing the correct odds, in number format.

The formula assumes that there will never be more than 3 characters in cell A1. The only fractional odds I've ever seen on a tote board are 1/9, 1/5, 2/5, etc., 6/5, 7/5, 3/2, 8/5, 9/5, 5/2, 7/2, 9/2, etc..

I've never seen 10 as a denominator, which would mean you would have to modify the formula, by first testing "IF(Len(A1)=4," and then change the Mid stuff to reflect the extra character.

This stuff is just off the top of my head, haven't really thought too much about it, so, be sure to test it thoroughly to make sure it does what you want.If you are assuming that all the fractional odds are of length 3, then you can simplify (tiny amount) the function by using left(A1,1) & right(A1,1) instead of mid().

If you want to assume a more general case where you don't know the length, then you could use the find("/",A1) to determine its position and then use that value in your mid() function to parse the numerator & denominator.

Also if assuming the general case, then instead of using a combination of len() and mid() to determine if a cell contains fractional odds, you could do something like not(iserror(find("/",A1))) which will return TRUE if fractional odds exist.

raybo
10-04-2010, 09:57 AM
If you are assuming that all the fractional odds are of length 3, then you can simplify (tiny amount) the function by using left(A1,1) & right(A1,1) instead of mid().

If you want to assume a more general case where you don't know the length, then you could use the find("/",A1) to determine its position and then use that value in your mid() function to parse the numerator & denominator.

Also if assuming the general case, then instead of using a combination of len() and mid() to determine if a cell contains fractional odds, you could do something like not(iserror(find("/",A1))) which will return TRUE if fractional odds exist.

Thanks,

As I said, I hadn't thought too much about the formula, I figured there could be a more simplified formula but "off the top of my head", the formula I posted was what I came up with, and it appears to work, as far as I tested it.

Ray2000
10-04-2010, 04:01 PM
Thanks, alsoran

The various replies, all offer workable solutions...

the one I chose using a spreadsheet was a vlookup function for range "Oddstotext" (it's at "exactas":AY45:AZ85) in that Harville SpreadSheet

Similarly, for your need, you could use vlookup for a table, call it ..TexttoOdds

1/9 0.111
1/5 0.2
2/5 0.4
1/2 0.5
3/5 0.6
4/5 0.8
1/1 1
even 1
6/5 1.2
7/5 1.4
3/2 1.5
8/5 1.6
9/5 1.8
2/1 2
5/2 2.5
3/1 3
7/2 3.5
4/1 4
9/2 4.5

and so forth
5/1 5
6/1 6
7/1 7
8/1 8
9/1 9
10/1 10.....

For VB code ("Morning_Line" is a text string) I use


If InStr(Morning_Line, "/") Then
Morning_Line_Value = Val(Left(Morning_Line, InStr(Morning_Line, "/"))) / Val(Right(Morning_Line, 1))
ElseIf Morning_Line = "Even" Then
Morning_Line_Value = 1
Else
Morning_Line_Value = 0
End If


BTW
TwinSpires new beta interface (An obvious copy of Youbet) cannot be used with that spreadsheet for importing odds. I hope they keep the classic wagering option.

Does anyone have an import method for Youbet's app?

CBedo
10-04-2010, 04:10 PM
Actually, the easiest way to deal with it is not use the odds field. Just pull in the pool data and get the actual odds.

As far as using a lookup for odds, just know that the calculation speed and memory use are a bit more taxing on your machine (probably not a big deal usually).

TheGhostOfOscarB
10-04-2010, 11:18 PM
Another almost unreadable treatise on the workings of the exacta in horse racing.

http://www.hss.caltech.edu/~snowberg/papers/Snowberg-Wolfers%20Risk%20Love%20or%20Decision%20Weights-NBER.pdf

I warn you that all the analysis is based only on the WINNING combination. They had no access to the exacta matrix for the races.

PS I concocted what is now known as the Harville formula way back in the 70's (long before Harville's paper) when exactas first came to New York. I still actually have the now aged sheet of paper where it was transcribed. Not E=MC^2, but wth.

PPS. As wonderful as these convoluted works are, they all seem to miss the obvious.