Excel Development Course

[Shaun]

I get a lot of people asking for help with excel on here and a lot of the time it is the same things asked.

I have decided to run a crash course in excel for those that are interested and will be concentrating on things needed for racing in general.

Some of the topics i will cover are.

*The most common formulas i use.
*Dealing with web queries
*A simple data base

I will be using Excel 2010 as my platform but a lot of what i show can easily be added to earlier version of excel.

I am not a professional when it comes to teaching and my excel is limited so i am hoping what i have to offer will provide those in need a little more help.

I am not going to create different threads for each topic it will all be in the one for easier reading and searching.

If you are interested in learning let me know the types of things you would like to learn so i know what i need to cover.

[norisk]

Hi Shaun, count me in, my main area of interest would be to do with Web Queries & also interested in any advice you have to offer about setting up a database.

[Shaun]

Not a lot of reply's but am sure that is where most people could benefit from excel.

The type of data base i can do will be like a phone directory type where you would put in say a horses name then record specific info such as the price you backed it at or any info then when you need to go back and check that horse at a later date you just select it from your list and the info will be displayed.

I will start on web queries and will post some examples and info to get started a bit later tonight.

[Puntz]

Yep,
Thanks

My particular interest would be these types of pages
http://tatts.com/pagedata/racing/2012/1/31/VR1.xml
and how to code to extract precisely what is required and where to place it in a worksheet

If you don't mind, hope this worksheet I got from another post here I been working on.
It has a list of TODO's, hope these can be basic lessons.

Thanks

[Shaun]

Sites like that are good if the have xml page to gather info from, but if they don't then web queries will do the same job if not better.

[Shaun]

THE BASICS

The best way to learn web queries is to just jump right in and try them out.

First thing is to start the macro recorder, this is the best way to go when you are first learning macros as it will record anything you do in excel and record the format needed for visual basic the language the macro is writen in.

Create a new excel file then open it up, look at the very botton left next to where it says "Ready" you will see a button this starts the macro recorder.

Put in a name for the macro and then hit ok, go to the tab labeled Data at the top of excel then find "From Web".

In the address bar paste this address in http://tatts.com/racing/2012/2/1/MR/7 then press "Options" on the right, take note of the options on this page, we don't need to change anything here now so just click "Ok" then press "Go" on the next page.

After the page loads you will see yellow arrows these indicate the different tables on the page, for the first test we will use the whole page, this is the arrow in the upper left corner, select it and it will change to green,once selected press import you then need to select the location you want the import to start at, in this case =$A$1 should be shown, then press the properties button on the next page go to the botton and check mark "Overwrite existing cells with new data, clear unused cells" then press "Ok" then press "ok" again.

Once the page has been imported then press the record macro button again this will stop recording.

Right click Sheet1 and select "View Code" then double click "Modules" then double click "Module1" and your macro will be shown on the screen, start by clearing the green text as it is not needed as this is just comment text.

You should now see this code.


Sub Test()

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://tatts.com/racing/2012/2/1/MR/7", Destination:=Range("$A$1"))
.Name = "7"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.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


Now lets look at what we have, the first part is telling us where it is connected and the destination of where to place the info
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://tatts.com/racing/2012/2/1/MR/7", Destination:=Range("$A$1"))

The rest of the information is what you would find on the options tab i showed you earlier and the properties tab, if you create another web query on another sheet and compare what is on those pages you can see where they are on here.

Most of this information i don't change.

Some pages you can get a specific table others you can't so it is trial and error some times.

Go to sheet2 and Follow the steps from starting the macro recorder and put this url in http://www.tab.com.au/Racing/Information/Results/DisplayDetailedView.aspx?State=1&MeetingCode=M&RacingCode=R&FromDate=2012-02-01T00:00:00&DisplayType=FinalDividendView&RaceNumber=01

When you get to the webpage i want you to scroll down until you see the arrow just above the race prices, when you highlight it a box will go around the prices, this is what we want so click it.

Then continue on with the rest of the steps until you stop the macro recorder.

Now see we just have the prices, now go in to view code and look at module2 you should have this.


Sub Macro2()

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.tab.com.au/Racing/Information/Results/DisplayDetailedView.aspx?State=1&MeetingCode=M&RacingCode=R&FromDate=2012-02-01T00:00:00&DisplayType=FinalDividendView&RaceNumber=01" _
, Destination:=Range("$A$1"))
.Name = "00&DisplayType=FinalDividendView&RaceNumber=01"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """BetGrid_DGTableOne2"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub


Scroll down to .WebSelectionType = xlSpecifiedTables see how this is different from the first one.
If you look you will also see a new line has been added .WebTables = """BetGrid_DGTableOne2""" this is the name of the table we selected, different websites use different names for this, some sites use numbers.

If you want to try different races for these websites you need to changed these parts in red.

http://tatts.com/racing/2012/2/1/MR/7

http://www.tab.com.au/Racing/Information/Results/DisplayDetailedView.aspx?State=1&MeetingCode=M&RacingCode=R&FromDate=2012-02-01T00:00:00&DisplayType=FinalDividendView&RaceNumber=01

The NSW/VIC tab urls change before and after the race so you can just use the url that is shown on the page you want to import.

To rerun the macros again for a new page just go to "view" tab at the top of excel then "Macros" and double click the macro you want to run.

That's it for now, try this on a few different sites you are trying to get data from and see what you can import.

[Puntz]

Today's race list in time order.
self explanatory, but there are 3 choices, simply copy and paste to workbook if you have downloaded it from this thread.

[AngryPixie]

Nice idea Shaun. I'm lurking with interest.

[Shaun]

THE BASICS PART 2

Now you have the basic idea of importing webpages let’s make it easier to change races or pages.

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://tatts.com/racing/2012/2/1/MR/7", Destination:=Range("$A$1"))

This is the part of the code that tell the macro what url you want and where to place it, as you know some parts of a url change to load different pages so what we need to do is link that part of the url with a cell on the sheet so when you put in a different race number for example it will change the macro without you going in to the macro itself.

As you can see there are ” at the start and finish of the url, these are important because when you add a cell location you will cut the url in half and these need to be added to any part of the url to make it work.

When we add anything to the url we use the & sign this tells us the combine “this part&the next part” to make one part, I will show you an example of adding a cell reference to change date, race venue and number.

Add a new sheet to excel; I will assume this is sheet4 for this example, right click and go to “view code” you can add “Sub Test4()” macro to an existing module.

I am going to use the old Qtab site as they have not added as much junk to it as the new one.

For proper formatting you should restrict VBA code to a reasonable length across the page, if code becomes too long and you want to go to the next line put this symbol in first_ then hit enter this tells the code to continue on the next line.

The coloured text corresponds to the code needed in the macro.
Here is the original link www.tabonline.com.au/2012/02/02/NR01.html

The red is the standard address that does not change.
The green is the date the code formats the date the correct way for the site.
The blue is the venue.
The orange is the race number.
The yellowgreen is the last part of the url.
On the new in cell A1 you put the date, in cell B1 you put the venue in cell C1 you put the race number in single digit, then add this code to D1

=IF(C1<10,0&C1,C1)

The code adds a zero to races below 10


Sub Test4()

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.tabonline.com.au" & Format(Sheets("Sheet4").Range("A1").Value, "/yyyy/mm/dd/") & _
Sheets("sheet4").Range("B1").Value & Sheets("sheet4").Range("D1").Value & ".html", Destination:=Range("$A$3"))

.Name = False
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.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


If you can get this to work see if you can get the NSW/Vic page to load in the same way.

[moeee]

THE BASICS
Create a new excel file then open it up, look at the very botton left next to where it says "Ready" you will see a button this starts the macro recorder.


I suppose in Excel version 2003 , there aint no button down there?

[Shaun]

If you look under tools then macro you will find the recorder there for 2003.

One thing i forgot to mention with excel 2007 onward is when you go to save the file the first time you need to change the file type to "Excel Macro Enabled Workbook"

Once that is done you can save it each time you close it.

[Puntz]

Done BASIC 1

Did not do THE BASICS PART 2 cos I already do that with cell refs.

good show,

Thanks

[Puntz]

The tatts url to get a racelist is
in cell A1
2012/02/04/raceday

With Selection.QueryTable
.Connection = "URL;http://tatts.com/racing/" & Range("A1") & ".html"

Then there is a series of macro to get it to go to columns

For some reason it won't work when I try . & Range("A1") & ".xml"

[Robot]

Shaun.Thanks for your basics training.Can manage all up to modules Where do I find them.If double click modules in the drop down it goes to module 2.What am I doing wrong.Have got the page for MR1 up but all I get is sheet1(code) Would like to continue to learn more as discussed before Maybe a slow learner.

Tom the ROBOT

[Shaun]

No macros on that sheet, also you don't need it in a zip file you can just upload the file, but i am sure the Zip file reduces the size if you are using an older excel.

For those that wish to use a new version of excel you can get a trial copy for free that will last you 6 months, at the end of 6 months you have 3 choices.

1) Continue to use the program and get a popup when opening saying it is unregistered and not be able to update but all functionality is the same.
2) Format your hard drive and re install the program and have it again for 6 months.
3) Purchase the program.

I use option 2 but if i am lazy option 1 is sometimes in effect, has been for the last 2 years.

[Shaun]

This is what you should see when you open the VBA editor, you may have 2 or more modules depending on how many macros you created.

[Shaun]

Someone needs to teach me how to post images that you can see but are not to large to upload.

[Neurokahuna]

HI Puntz,

I've been caught out in this same issue before and the Unitab XML feeds can be a little misleading in this respect.

It may relate to the format of the date you are using in cell A1. I notice you have it in YYYY/MM/DD format, but I've found that when it is a single digit day and single digit month (i.e. 2012/01/04), Unitab's xml feed can't interpret it. It needs to be changed to 2012/1/4

To reformat cell A1, right-click on it, select Format Cells, then Numbers tab, then Custom from the Formattingh Options.

In the 'Type:' field, enter 'YYYY/M/D' and it should now display the date as 2012/1/4, instead of 2012/01/04.

Don't worry, it will still interpret 2012/10/10 correctly.

Hope this helps.

Neurokahuna

[Puntz]

Thanks Neurokahuna

I'll work on your suggestions later and do some tests.

Have uploaded the current date method.
The point is eliminating as much typing as possible so no typo errors happen.
I hope the right format will happen on this same worksheet.

By all means correct it if so to make it work like it ought to,
show and tell is appreciated.

Puntz

[Puntz]

Shaun Someone needs to teach me how to post images that you can see but are not to large to upload.

Open MS Paint
Print Screen and Paste to Paint
Save <filename>
Close

Then upload here, usually works.

[Shaun]

Yeah i tried that but file was to big so i cropped it, guess i made it to small.

Trial version Office 2010 i can explain at a later date how to extend for 6 months free use.

http://technet.microsoft.com/en-us/evalcenter/ee390818

650MB download so depending on your connection speed my take some time.

[norisk]

Thanks for your posts guys, been swamped but hope to have a look at it all next week.

[norisk]

Anyone got an idea what is wrong with the following code? Cell A1 contains the date, E1 the 1st letter of the race code eg "M', D1 the race number in 2 digit format eg '01', - as nothing happens when I run it.



Sub Victab_odds()



With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.tab.com.au/Racing/Betting/StandardBets/PlaceStandardBet.aspx?State=1&MeetingCode=" & ActiveSheet.Range("E1").Value & "RacingCode=R&FromDate=" & Format(ActiveSheet.Range("A1").Value, "/yyyy/mm/dd/") & "T00:00:00&DisplayType=FinalDividendView&RaceNumber=" & ActiveSheet.Range("D1").Value, Destination:=Range("$A$3"))



.Name = "PlaceStandardBet.aspx?State=1&MeetingCode=" & ActiveSheet.Range("E1").Value & "RacingCode=R&FromDate=" & Format(ActiveSheet.Range("A1").Value, "/yyyy/mm/dd/") & "T00:00:00&DisplayType=FinalDividendView&RaceNumber=" & ActiveSheet.Range("D1").Value




.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """BetGrid_DGTableOne2"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False


End With
End Sub

[Shaun]

I had a look at your code and found a few errors but after i fixed them i still couldn't get it to work.

Sometimes this happens and because i don't know heaps about macros i came up with another way of doing this.

What i do is create the url i need on the sheet and add the variables there, the good thing about doing this is you can see the results and check it with the original to find problems.

So here is what i have

Adjust the sheet to match where you want the info.

A1 =Today() this will put today's date or you could just put a date.
C1 you put the race number in single digit form
D1 =IF(C1<10,0&C1,C1)
E1 you put the letter of the meeting such as M
F1 ="http://www.tab.com.au/Racing/Betting/StandardBets/PlaceStandardBet.aspx?State=1&MeetingCode="&E1&"T&RacingCode=R&FromDate="&TEXT(A1,"yyyy-mm-dd")&"T00:00:00&BetType=Win&RaceNumber="&D1


here is the code you need to put in the VBA sheet


Sub Victab_odds()
Sheets("sheet1").Range("A3:AZ100").Select
Selection.ClearContents
Sheets("sheet1").Range("A1").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & Sheets("Sheet1").Range("F1"), Destination:=Sheets("Sheet1").Range("$A$3"))
.Name = False
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """BetGrid_DGTableOne"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False


End With
End Sub

[Shaun]

To remove the actual hyperlink right click the cell and select " Remove Hyperlinks"

If you don't want to see the url just reformat the text in that cell to the colour whte or what ever the background colour is.

[norisk]

Thanks Shaun, I made a few adjustments mainly so I can use it on any worksheet & also adjusted the url to -


="http://www.tab.com.au/Racing/Betting/StandardBets/PlaceStandardBet.aspx?State=1&MeetingCode="&E1&"&RacingCode=R&FromDate="&TEXT(A1,"yyyy-mm-dd")&"T00%3a00%3a00&RaceNumber="&D1

Sub Victab_odds()

With activesheet.Range("A3:AZ100").Select

Selection.ClearContents
activesheet.Range("A1").Select

With activesheet.QueryTables.Add(Connection:= _
"URL;" & activesheet.Range("F1"), Destination:=activesheet.Range("$A$3"))
.Name = False
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """BetGrid_DGTableOne"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False

End With
End With

End Sub

[Shaun]

THE BASICS PART 3


Now we have a couple of ways to get info in to the sheet we need to make it more efficient, this will also speed up the time it takes to import.

We need to add a couple new macros to the sheet, these macro should be added to the top of the sheet before the inport macros or any other macros you have added.

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


The first code uses the MSXML2.XMLHTTP procedure to import the info, this is what I understand it does but I may be wrong.

The second code downloads your info to a temp file.

You need to make changes to your web query to use these 2 codes, if we use the above code to import the Vic tab odds these are the changes we need to make.

Sheet1 has your variable info
Sheet2 has your imports


Sub Race()
Application.ScreenUpdating = False ‘ this stops the screen updating while it runs macro
Sheets("Sheet2").Select ‘ selects the sheet you want to import to
Range("A1:Z100").Select ‘ selects the range
Selection.ClearContents ’ clears the range
formhtml = ExecuteWebRequest(ThisWorkbook.Sheets("Sheet1").Range("F1").Value) ‘; this is the sheet and range where you have created the url as explained
outputtext (formhtml)
Set temp_qt = ThisWorkbook.Sheets("sheet2").QueryTables.Add(Connection:= _
"URL;" & ThisWorkbook.Path & "\temp.txt" _
, Destination:=ThisWorkbook.Sheets("sheet2").Range("$A$1"))
With temp_qt
.Name = False
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """BetGrid_DGTableOne"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
‘this section will delete the temp file and remove all connections from the workbook
ActiveSheet.QueryTables.Item(1).Delete
Set temp_qt = Nothing
Kill ThisWorkbook.Path & "\temp.txt"
If ThisWorkbook.Connections.Count > 0 Then ThisWorkbook.Connections.Item(ThisWorkbook.Connections.Count).Delete
Sheets("Sheet1").Activate
Application.ScreenUpdating = True ‘ reactivates screen updating
End Sub


I would be a good time to add a button to sheet1 that runs the macro, if you want this all on the one sheet that's fine just make sure you adjust the the first section that clears the sheet to start at say A3 and also adjust the Destination to A3 and also change sheet2 to sheet1

[Shaun]

norisk - that's fine, once you have the idea and it works you can make a lot of different changes to suit the site you want to use it on and the info you need.

I have posted many sheets on here with different things i have added but with this basic steps most people should be able to get web query's up and running.

[norisk]

Good stuff Shaun, the speed difference is impressive even with such a small query.

[Shaun]

The one main issue with doing this way is you lose the ability to refresh unless you run the macro again, I have not had a lot to do with refreshing a macro as I have not needed it so far but here is an option for auto refresh.

Add this code to the bottom of the module.

Sub RepeatMacro()
Dim lastRunTime
Do
lastRunTime = Now
Range("A2") = "Last run: " & Format(lastRunTime, "hh:nn:ss")
Call Race
DoEvents
Application.Wait lastRunTime + TimeValue("00:00:01")' change the time to adjust the refresh rate
Loop
End Sub


Then you need to create 2 buttons to click so do this, select excel sheet where you have the race options , go to the top panel look for “Developer” if developer is not shown go to “File”/“Options”/”Customize Ribbon” and make sure it is checked on the right hand panel.
Select “Developer” then “Insert” then select “Command Button” in ActiveX Controls. Paste the button on the page then double click it then paste this between the top and botton line.

Call RepeatMacro

Repeat instructions to create another button and double click it and paste this in.

MsgBox "Stopped."
End

You can then rename the buttons “StartButton” and “StopButton” you then need to activate them by pressing design mode to turn it off.
You can adjust the refresh time in “RepeatMacro” just press the buttons to start and stop refresh, you need to stop refresh before changing the race when you hit start it will switch races.

[Puntz]

Shaun, I am partially lost at this point,(THE BASICS PART 3) but impressed at the speed of the imports to say the least using the XML thing.

The way I do my "refresh" is race by race on the race start time order list.
By "refresh" I mean a Loop in starting at the top using the
Do until
-----------------------
So it's ( your code )

Sub LoadRaceField()

Application.Calculation = xlAutomatic
Application.ScreenUpdating = False
Sheets("Sheet1").Select

Do Until _
Sheets("Sheet1").Range("J2").Value = "PAYING" Or _
Sheets("Sheet1").Range("J2").Value = "INTERIM" Or _
Sheets("Sheet1").Range("J2").Value = "CLOSED" Or _
Sheets("Sheet1").Range("J2").Value = "ABANDONED" Or _
Sheets("Sheet1").Range("J2").Value = 5

Dim xmldoc As MSXML2.DOMDocument

and the rest of your code at this point.
----------------
FOR TESTING,
I have used a counter method, so when it reaches "5", it simply gets the next race on the list. This will be deleted later on.
Sheets("Sheet1").Range("J2").Value = Sheets("Sheet1").Range("J2").Value + 1

Loop

Application.Calculation = xlAutomatic
Sheets("Sheet1").Range("J2").Value = "0"
DELETE_RACE
LoadRaceField
'and so on
End Sub


How is the code written to get RaceDisplayStatus to appear in Sheet 1 Cell J2 please?
And the results if possible for that race.
Thanks.


Puntz

[Shaun]

So what you want it to do on the unitab site is select a race then keep refreshing then when the race is over you want it to move to the next race?

[Shaun]

That is for the new site correct.

[Puntz]

Yes, that is for the tatts site cos that is the one I am working on now, but later I'll move on to the other sites.

For now,

the Do Until requires the status, whatever it is, if it's,
a "Time", "PAYING","INTERIM","CLOSED", "ABANDONED" "PHOTO" "PROTEST" etc etc
and/or whatever else the scenario may be.
Eventually it will get to "PAYING", which will be the official RESULTS.

The actual RESULTS payout figures may be best go to Sheet2.

During some testings I don't wait around for PAYING, and choose INTERIM then move on to the next race.

The time limit is important to.

If lets say a race has a advertised start time of 3:00pm and it's Looping, there has to be a limit or it will Loop forever, so therefore I implement a Variable factor of let's say 20 minutes after advertised race start time LIMIT.

So if at 3:20pm there is no status or they are still negotiating a PROTEST, then 20 minutes past 3:00pm ( Variable), Next Race...,

Puntz

[Shaun]

What do you use to get the race times in order and where do you get them?

I was having a look at your last sheet the screen updating is doing twice the work, have it set like this


Application.ScreenUpdating = False

Rest of macro here

Application.ScreenUpdating = True

What that does is stops the screen updating so it just looks like the info appears and you don't see the macro working, if you do the same for the clean up macro will look smother.all the

[Puntz]

Ok, I did not include the entire macro, only the section.

-----------------------------
What do you use to get the race times in order and where do you get them?
It's in another workbook
using the old html code
With Selection.QueryTable
.Connection = "URL;http://tatts.com/racing/" & Range("A1") & ".html"

Cell A1 is: 2012/02/06/raceday
This brings up the entire race card
----------------------------------

I was having a look at your last sheet the screen updating is doing twice the work, have it set like this


Application.ScreenUpdating = False

Rest of macro here

Application.ScreenUpdating = True

What that does is stops the screen updating so it just looks like the info appears and you don't see the macro working, if you do the same for the clean up macro will look smother.all the

Yes, I am still tweaking as I go along.

I'll post the new day's race list tomorrow at around just past 9.AM.

I need the RESULTS though or the project/workbook cannot be completed, and won't be able to get past BASIC 3.

Thanks

Puntz

[Puntz]

Today's Race list
There are 3 sheets
1: with only horse and trots
2: horse-trots-greys
3: all races on card
the all races on card sheet, the overseas racetimes may show past midnight so effectively it is not in true time order.

The code I use to delete a race row goes something like:
add a sheet and call it: DAYSRACELIST
USE THE ONE POSTED HERE AS A BACK-UP, COPY AND PASTE FROM BACKUP TO DAYSRACELIST, THEN YOU NEVER LOSE THE ORIGINAL.

Sub DELETE_RACE()
Application.Calculation = xlAutomatic
Application.ScreenUpdating = False

Sheets("DAYSRACELIST").Select

Sheets("DAYSRACELIST").Rows("3:3").Delete Shift:=xlUp

Sheets("DAYSRACELIST").Range("C1:I1").Value = _
Sheets("DAYSRACELIST").Range("C3:I3").Value
'----------------------------------------------------
Sheets("DAYSRACELIST").Range("A1").FormulaR1C1 = _
"=RC[5]&""/""&RC[6]&""/""&RC[7]&""/""&RC[2]&""/""&RC[3]"

Sheets("DAYSRACELIST").Range("A1").Value = _
Sheets("DAYSRACELIST").Range("A1").Value

'-----------------------------------------------------
ActiveWorkbook.Save

Application.ScreenUpdating = True
End Sub


Puntz

[Puntz]

Shaun,

On the Workbook you have here where it gets the prices using xml how is it coded to gather other information ?
For example
Length,Distance etc etc

This xml makes no sense, it's not like the code when Record Macro is written where it understandable-linear-->

xml looks like "2-way speak"

Set runnerNumber = runner.Attributes.getNamedItem("RunnerNo")
If Not runnerNumber Is Nothing Then
Sheet1.Cells(i + 5, 1) = runnerNumber.Text


Ya know?

It's not like,
"put race length in Cell B1"
Give this some thought, since when does a punter need to learn to program a PC to win a race when the punter already knows more than the programmer about racing and betting ?


But if your answer is something like, "well what goes round comes round, it is the age of the programmers"
I say ****, Pitsburgh Phil never used a computer!

http://en.wikipedia.org/wiki/George_E._Smith_(gambler)

Methods

Smith took notice of every detail in horse racing. He kept detailed notes of which horses were good runners during muddy conditions and always inspected horses at the end of a race to look for subtle signs of lameness or impediments (i.e. loose girth straps) that may have negatively impacted a horse's speed.[1] As he became more famous and seemingly successful, bookmakers would often refuse to place his bets outright for fear of losing money when he won. As a result, Smith conducted most of his betting through "beards", or men that he would commission to place bets for him. Smith tried to keep the identity of his beards a secret and never revealed their identities, even to his other commissioners. His movements were continuously followed by agents for the bookmakers and by the Pinkerton detectives employed by The Jockey Club.

[Shaun]

xml is different from what i have been doing in this short course, xml is the website designers way of providing all the information that a person needs to put that in excel.

The VBA that i have posted is excels way of gathering information from the website that is why not all information is able to be separated in to individual bits before download.

This is why some times you can download individual tables and other times you have to get the whole page and take what you need.

I am not good with xml i can only understand some from there example so i am not the best person to ask about this.

As for your comments about computers helping punters win, well to be honest i think it is the punter helping the computer win.

Computers are no smarter than what information they are given, they can calculate things and display other things in a layout that is easier to understand.

The speed they can accomplish these things can be very fast.

[Puntz]

comment are just my way of breaking monotony, I have a soft spot for programmers, I surely do.
I went to TAFE actually to enter a 6 year IT course and failed the intro.,
( I did not tell them my "secret" that I wanted to make my own horse race program) they said I need to go back to school and learn Trigonometry. I said OK.
So I went back the TAB and looked for a horse with a similar sounding name.
When I asked the woman at the counter after placing a bet,
"Do you know Trigonometry?"
She pointed to her wedding ring!

Anyway,

This is why some times you can download individual tables and other times you have to get the whole page and take what you need.

Yes, that's what I been doing using another web query with html at the end,
and yeah, like you say, "take what you need"


Thanks

[Shaun]

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]

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

[Puntz]

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]

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]

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



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]

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.


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.Connections.Count).Delete
End Sub

[darkshines]

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]

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]

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]

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]

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]

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]

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.Connections.Count).Delete 'deletes any left over connections.

End Sub

[Martinw]

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]

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]

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]

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]

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]

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

[Martinw]

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