Importing Odds (that refresh) into Excel

[CBedo]

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

Very nice explanation. Thanks.

[Warren Henry]

THANKS for sharing that technique. Much appreciated.

[DanG]

These are the posts that make wading through the occasional internet nonsense worthwhile. Very generous use of your time CB, thank you.

[CBedo]

Originally Posted by DanG

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.

[DanG]

Originally Posted by CBedo

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.

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]

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]

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]

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]

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]

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


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]

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]

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]

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]

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.