OZmium Sports Betting and Horse Racing Forums

Page 2 of 2

Show 40 posts from this thread on one page

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)

Shaun

20th February 2012 02:40 PM

2 Attachment(s)

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.

Shaun

20th February 2012 02:47 PM

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

Puntz

22nd February 2012 10:16 PM

1 Attachment(s)

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.

Puntz

27th February 2012 09:20 PM

1 Attachment(s)

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,

darkshines

10th October 2012 05:08 PM

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

Shaun

10th October 2012 09:28 PM

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

darkshines

11th October 2012 11:04 AM

1 Attachment(s)

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.

Shaun

11th October 2012 05:26 PM

1 Attachment(s)

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.

darkshines

11th October 2012 06:01 PM

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.

Shaun

11th October 2012 06:04 PM

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.

Martinw

14th December 2012 09:10 AM

I am trying to update my webquery to MSXML2.XMLHTTP using the code in this posting

I have tried to modify it as below:-

Dim WA As String

Sub queryhtml(wks As Worksheet)

WA = wks.Range("AF1").Value
Formhtml = ExecuteWebRequest(WA)

It will not run the Formhtml function; just skips it and exits the sub.

Any help would be greatly appreciated as I am completely out of my depth here.

Ta,
Martin

Shaun

14th December 2012 01:53 PM

If you post the whole code for that macro i might be able to see where the issue is.

Post it with code tags thanks.

Martinw

14th December 2012 03:51 PM

Hi

Sub queryhtml(wks As Worksheet)
Dim WA As String
WA = wks.Range("AF1").Value

Formhtml = ExecuteWebRequest(WA) ' runs the first function.

outputtext (Formhtml) 'gets the contents held in the file created by the second function

Set Temp_qt = wks.QueryTables.Add(Connection:=WA, Destination:=wks.Range("BA1")) ' creates the link from the tx doc to the sheet.

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

' wks.QueryTables.Item(1).Delete

' Set Temp_qt = Nothing
Kill ThisWorkbook.Path & "\temp.txt" 'deletes the temp txt file.
' If ThisWorkbook.Connections.Count > 0 _
' Then ThisWorkbook.Connections.Item(ThisWorkbook.Connect ions.Count).Delete 'deletes any left over connections.

End Sub

Martinw

14th December 2012 03:59 PM

Whoops forgot the wordy bits

wks. is because I run the same code on multiple sheets - 1 for each race up to 70 a day. So instead of writing Range("AF1").Value I write wks.Range("AF1").Value and that tells the computer to work on the current sheet. I use OnTime to do the timing. Each minute it checks each sheet to see if it needs to be updated and if so runs the code.

The first step is download the data and as Tatts xml is not updating properly I am using web query. But the old TABonline page will not stay up forever so want to modify yr code so I can get it off the Tatts page. And then maybe NSW

Ta,
Martin

Shaun

14th December 2012 10:27 PM

I am not the best at reading code to find issues but have you tried running in debug mode?

Open your code page and position the cursor at the start then select debug then every time you press F8 it will run one line of code and you will see where at goes and you can even look at your sheet to see the outcome.

If it is crashing on the formhtml at usually means the web address returned an erro so just for a test copy the actual web address from the web page you after and past it between the bracket and between "" these.

If when you run it again in debug it does what it should then the issue is in your web address.

As far as using Taboline site you should have no issues except i find it slower than the old site, takes about 5 to 10 seconds to import as the old site takes about 3 seconds or less.

I have no idea with the new NSW/Vic sites as i can't convert the new code to what i know.

Martinw

14th December 2012 11:49 PM

Just had another try and it gets to oXHTTP.send in the Public Function ExecuteWebRequest and then exits - does not go onto ExecuteWebRequest = oXHTTP.responseText

Martinw

17th December 2012 05:24 PM

Got it working but found tatts to be very slow. Tried there xml feed again and still getting 30% error rate. So will stick with what I have for now and try to get into NSW's xml. This exercise has given me a few hints about that

norisk

18th December 2012 06:07 AM

I was of the belief that the XML feeds from NSW & Vic sites will no longer be available once the old sites are shut down, which as it happens is today.

Old sites are gone & none of my Vic code is working so I guess that's that...

Shaun

18th December 2012 10:28 AM

Yes, unless you use there new service, but importunately even they don't know how to use it.

Martinw

18th December 2012 02:35 PM

Re Shawns's post
Well that's encouraging!
I tried reading the Live Odds book again last night. Rather vague to a person of my limited skill. But then then whole xml thing seems to be like that. Reading all the various pages there is lots of data but even the pro's seem to struggle to make it work.
I am hoping that the Tatt's page, http://www.tabonline.com.au, stays up until they get their xml back on track. I really miss their xml feed it was so sweet and simple even I could use it. Still I have recorded every race today so far of tabonline and will get the balance and it is running fine.

Ta,
Martin

All times are GMT +10. The time now is 08:55 PM.

Page 2 of 2

Show 40 posts from this thread on one page