OZmium Sports Betting and Horse Racing Forums

OZmium Sports Betting and Horse Racing Forums (http://forums.ozmium.com.au/index.php)

- General Topics (http://forums.ozmium.com.au/forumdisplay.php?f=59)

- - Excel Development Course (http://forums.ozmium.com.au/showthread.php?t=23333)

Been a week since i posted here and just thought i would add a couple files to show the speed difference the extra code i added makes from the standard web query.

There are 2 files here that are the same on the outside but the macros on the After sheet have been updated the the method i use now for imports.

The sheets are just designed to down load unitab data and select the runner in barrier 1 with results if available, give then a test to see the difference.

Press "Start" put in date then you just need to select a venue and put in the first race number and the last race number then hit run.

Sorry on last post you don't need to add the last race it will know already just need to ad first race.

Shaun, I'll post the same Race List Workbook, that way it's part of these lessons
If most of the process can be formula to get the columns right, then good.

Some F4 races webs in on separate rows, so that had to be sorted to.

Shaun,
Not sure where the error is for the URL reference formula in Sheet 3-Range G1, tried to type in the formula with direct references from the cells.
The web query is in Module1.

The purpose is, so when a race is finished or done with looking at, the referenced race list row can be deleted and on with the next race on the list.

Got it to work OK with tats.com, but this one seems a bit stubborn
if can you check it out, much appreciated.

Thanks,

@Shaun

Excellent excellent work with this. Been a brilliant help. I'm essentially just importing tote odds direct into my punting worksheet, which allows easy comparison, kelly bet calcs etc etc - no more manual data entry.

I've basically got to BASICS PART 2 of your 'course', just needed some help with PART 3. Working fine just want to make it quicker.

Am importing from ozbet (unfortunately name/odds table doest work for some reason so need to grab whole screen), got meeting ID, date in cells A1 and A2 of sheet 'ID'. Have created a list of individual macros for each race of the day, then another macro to run them all depending on how many races in the meeting, so I update all tote prices for meeting at once.

I've shown below the macro I've got for importing Race 1. What I want to do is incorporate the Public Functions (from your 5th Feb post) to speed things up, cos it does take a while.

Are you able to perhaps point out what I need to do to this code to enable the Public Functions to work. My VBA skills aren't quite at that level yet to follow your example.

Code:


 
Sub Race_1_tote()
' Race_1_tote() 
Macro
	With Sheets("tote").QueryTables.Add(Connection:= _
		"URL;https://www.ozbet.com.au/UISecure/ToteUI/RaceTote.aspx?fixtureId=" & _
		Sheets("ID").Range("a1").Value & _
		"&fixtureDate=" & _
		Sheets("ID").Range("a2").Value & _
		"&contestNumber=" & _
		"1", 
Destination:=Range("tote!$A$10"))		
.Name = "RaceTote.aspx?fixtureId=" & _
		Sheets("ID").Range("a1").Value & _
		"&fixtureDate=" & _
		Sheets("ID").Range("a2").Value & _
		"&contestNumber=" & _
		"1"
		.FieldNames = True
		.RowNumbers = False
		.FillAdjacentFormulas = False
		.PreserveFormatting = True
		.RefreshOnFileOpen = False
		.BackgroundQuery = True
		.RefreshStyle = xlInsertDeleteCells
		.SavePassword = False
		.SaveData = True
		.AdjustColumnWidth = False
		.RefreshPeriod = 0
		.WebSelectionType = xlEntirePage
		.WebFormatting = xlWebFormattingNone
		.WebPreFormattedTextToColumns = True
		.WebConsecutiveDelimitersAsOne = True
		.WebSingleBlockTextImport = False
		.WebDisableDateRecognition = False
		.WebDisableRedirections = False
		.Refresh BackgroundQuery:=False
	End With
End Sub

Thanks in advance!!

Try this out, if you can't get it working upload the sheet so i can take a look.

I also included the 2 functions so remove them if you have them already.

Code:


Public Function ExecuteWebRequest(url As String) As String
    Dim oXHTTP As Object
    Set oXHTTP = CreateObject("MSXML2.XMLHTTP")
    oXHTTP.Open "GET", url, False
    oXHTTP.send
    ExecuteWebRequest = oXHTTP.responseText
    Set oXHTTP = Nothing
End Function

Public Function outputtext(text As String)
Dim MyFile As String, fnum As String
        MyFile = ThisWorkbook.Path & "\temp.txt"
        fnum = FreeFile()
        Open MyFile For Output As fnum
        Print #fnum, text
        Close #fnum
End Function


Sub Race_1_tote()
' Race_1_tote()
    Sheets("tote").Select
    Range("A10:Z500").Select
    Selection.ClearContents
    formhtml = ExecuteWebRequest("URL;https://www.ozbet.com.au/UISecure/ToteUI/RaceTote.aspx?fixtureId=" & _
    Sheets("ID").Range("a1").Value & "&fixtureDate=" & Sheets("ID").Range("a2").Value & "&contestNumber=" & "1")
    outputtext (formhtml)
Set temp_qt = ThisWorkbook.Sheets("tote").QueryTables.Add(Connection:= _
    "URL;" & ThisWorkbook.Path & "\temp.txt" _
    , Destination:=ThisWorkbook.Sheets("tote").Range("$A$10"))
    With temp_qt
        .Name = False
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
ActiveSheet.QueryTables.Item(1).Delete
    Set temp_qt = Nothing
        Kill ThisWorkbook.Path & "\temp.txt"
    If ThisWorkbook.Connections.Count > 0 Then ThisWorkbook.Connections.Item(ThisWorkbook.Connect  ions.Count).Delete
End Sub

Hi Shaun

Thanks for the very prompt reply. Couldn't get it to work, so have posted it for you to tinker with as suggested.

As you will see I import all races onto one tab, then I can apply vlookup to bring tote odds into my form worksheet.

On the ID tab, cell A3 is obviously the number of races for that given meeting.

Thanks again, you're a champ.

Run the test_tote macro, it is located on the second module, if you need multiple macros that is fine as long as they are on same module you only need is one set of functions.

Beauty Shaun!

Just gave it a go, works like a charm. Thanks a bunch. Speed is phenomenal.

Only really stumbled on this forum lately, will try and give a bit back in the future.

Big thumbs up from me.

Np, that's why i made this post so people could take what i know and use it themselves and learn, if you get stuck on anything let me know,chances are i may have used it before.