PDA

View Full Version : download unitab horse data into excel spreadsheet - problem


gsdanger
5th July 2010, 09:22 PM
Hi All, and specifically Kennedy 22384

I have been following with interest your conversations with regards to using an excel spreadsheet for obtaining racing data from the unitab site. I have the same situation. I was given 2 spreadsheets from a member of this forum also – (I think his name was Peter – don’t know his alias). I have the same spreadsheet that has been outlined, however that spreadsheet actually pertains to gathering historical data for form analysis and trend following. The spreadsheet I have attached (Tomorrow racing today) actually allows you to input the day’s racing date and the venue code (example: MR for Melbourne Races etc). It downloads all the detailed form for the venue you have nominated.

I have been using this spreadsheet, in concert with a spreadsheet I have created (2nd attachment) to analyse each horses current form and rate, sort and price them according to the rating figures derived from this analysis. I can rate a race in 1 minute.

Unfortunately, about 12 months ago, I upgraded to Microsoft Office 7 suite, and at the same time upgraded my PC. My PCs operating system is now Windows 7.

I thought this transition to office 7 and windows 7 was the reason for my spreadsheets not functioning. Maybe not.

Could you possibly have a look, in the first instance the first attached spreadsheet (tomorrow racing today) and see if you can get it to download the detailed form from the unitab site. Currently, the spreadsheet works in a limited way. It downloads the abbreviated form available but not the detailed form.(the spreadsheet should also be populated to the right of column ‘Z’ also). Obviously the ‘Get external Date’ functions are working, however it seems that there is some sort of limiting factor (maybe with the unitab site) that is precluding the detailed form to be downloaded in it’s entirety.

Normally, when this spreadsheet obtains all the detailed form, I minimise it and open my other spreadsheet (KwikPick Form Analysis). All I have to do then is input all the day and race data on the main page (including the race date), then go to the race I want to rate (hyperlinked to the relevant worksheet). Here I only need to input the following in the ‘red’ area: YYYY/MM/DD/XRXX for example: 2010/07/MR05 and press the ‘Get Data’ Button. All the form is then downloaded into this spreadsheet and assessed. I then press the ‘sort and Price the Selections’ button. The horses are sorted in highest rated order and priced accordingly. Too easy!

This aspect has an error in it also (I think it’s a syntax error. I maybe wrong). Can you see if you can fix this also. The macro that pulls the data from the ‘tomorrow racing today’ spreadsheet into each race sheet is called ‘Get Data’, attached to the button with the same name.
I will be very thankful if you can assist me in getting these spreadsheets functioning correctly.

Kind Regards.

gsdanger

Crackone
6th July 2010, 03:28 PM
It may be that Unitab changed its forms page you can't access it with out an account.

gsdanger
6th July 2010, 06:52 PM
Hi Crackone,

thanks for your response. I have had an account with unitab for 5 years and it is still active! No money in it, as I only used it to access the detailed form guides.

Thanks for your response, however, I still need assistance, especially with the first spreadsheet (tomorrow racing today) as I will probably be able to fix the 2nd one myself (I created this one)....

Kind Regards,

gsdanger.

Crackone
7th July 2010, 02:27 PM
For all my excel problems I go to mrexcel.com

gsdanger
7th July 2010, 05:33 PM
Once again, thanks for your assistance Crackone.

I remember who supplied the first spreadsheet to me all those years ago. it was: OZDOC

OZDOC, if you read this thread please respond with some options.

Kennedy 22384, are you able to assist me with my problem? I really hope so, as I'm going down the punting gurgler without my ratings.

Kind Regards, and please assist, if you guys, or anyone else can.

gsdanger...

gsdanger
1st August 2010, 06:51 PM
download unitab horse data into excel spreadsheet - problem

--------------------------------------------------------------------------------

Good Evening OZDOC,

I have been having data download problems with the spreadsheet (Tomorrow racing today - 1st attachemnt). I received this spreadshet from you 5 or 6 years ago and have been using this spreadsheet, in concert with a spreadsheet I have created (2nd attachment) to analyse each horses current form and rate, sort and price them according to the rating figures derived from this analysis. I can rate a race in 1 minute. Works well.

Unfortunately, about 12 months ago, I upgraded to Microsoft Office 7 suite, and at the same time upgraded my PC. My PCs operating system is now Windows 7.

I thought this transition to office 7 and windows 7 was the reason for my spreadsheets not functioning. Maybe not. Might be that the unitab site might have been upgraded, or re-configured.

Obviously, you will be familiar with the workings of the first spreadsheet (originally yours), so could you possibly have a look, the attached spreadsheet (tomorrow racing today) and see if you can get it to download the detailed form from the unitab site. Currently, the spreadsheet works in a limited way. It downloads the abbreviated form available but not the detailed form.(the spreadsheet should also be populated to the right of column ‘Z’ also). Obviously the ‘Get external Date’ functions are working, however it seems that there is some sort of limiting factor (maybe with the unitab site) that is precluding the detailed form to be downloaded in it’s entirety.

Normally, when this spreadsheet obtains all the detailed form, I minimise it and open my other spreadsheet (KwikPick Form Analysis). All I have to do then is input all the day and race data on the main page (including the race date), then go to the race I want to rate (hyperlinked to the relevant worksheet). Here I only need to input the following in the ‘red’ area: YYYY/MM/DD/XRXX for example: 2010/07/MR05 and press the ‘Get Data’ Button. All the form is then downloaded into this spreadsheet and assessed. I then press the ‘sort and Price the Selections’ button. The horses are sorted in highest rated order and priced accordingly.

I will be very greatful if you can assist me in getting this spreadsheet functioning correctly. Without it, I'm struggling to find an alternative web site to download appropriate data from, and am slowly dying on the punting vine. Please Help.....

Kind Regards.

gsdanger
Attached Files Tomorrow Racing Today.xls (1.05 MB, 41 views)

kennedy22384
10th November 2010, 09:53 PM
i am so sorry i havent seen this message but lemme take a look at the files and see what can be done

kennedy22384
10th November 2010, 10:14 PM
The issue here is two things,

one, if you are not a member of unitab, you only get the last 3 tab starts which is not accurate and two, unitab has redone the website which has made it kinda unfriendly for web data grab.

I will try to find an alternative website to go and use but im not sure if i can pull it off

Shaun
11th November 2010, 01:28 AM
Try the ACT tab site site might be better.

kennedy22384
11th November 2010, 01:30 PM
Ok, i think i fixed it up. I had to alter a few bits, for one the form doesnt come from unitab but the fields do.

All you need to do is open it up and go to macros and select RunAll and press run.

It will go through every horse race in australia on the day and spew out the sheets one by one.

Move all the result sheets (unitab codes) to another workbook after you are finished. Do not run it with all the sheets still in the workbook.

I built this on office for mac 2011. Im not sure if it works with windows so please test :)

Shaun
11th November 2010, 03:11 PM
I see you are using the same form as i do for my ratings when i use them, i manually import each race as needed but have always wanted to automate the task so i might be able to steal some of your code if that's ok to get mine running.

kennedy22384
11th November 2010, 03:11 PM
No prob just so long as you share it with everyone

kennedy22384
12th November 2010, 08:45 AM
Updated, fixed a couple of issues and the date will change automatically for unitab. :)

the instructions still the same as above

Shaun
12th November 2010, 09:00 AM
Mine crashed after running getrace3 said the internet web address does not exist

kennedy22384
12th November 2010, 09:01 AM
you said runall right, you cannot just run the getraces3 macro, you have to run the runall macro

kennedy22384
12th November 2010, 09:19 AM
Update, fixed some bugs that arose today. This will go on until they are all fixed

Shaun
12th November 2010, 09:21 AM
That is correct, i will try it again i think it has something to do with the link.

kennedy22384
12th November 2010, 09:25 AM
Another update on the way, just testing it and added code to delete the queries after each race. You may have noticed that the file size is increasing, that is due to the queries being held. i added code to take care of that. Will upload in about 20 mins or maybe less

Shaun
12th November 2010, 09:33 AM
I uploaded the sheet as it is after i get the error, if you have a look on Racelists you will see under the date column it has replaced the date, i use win7 with office 2010

kennedy22384
12th November 2010, 09:37 AM
It shouldnt have cause if you select a cell, it should say '2010-11-12
The single quotation mark makes sure it puts it in as the text and not the date

kennedy22384
12th November 2010, 09:46 AM
Updated, still increasing file size, maybe to do with the fact it is xls. If you can save it as a xlsm file

kennedy22384
12th November 2010, 09:51 AM
the issue with the racelist is to do with lines 100-101 in the code. Cause im on a mac, you may need to play with it and trial and error

Shaun
12th November 2010, 10:31 AM
Sub GetRaces2()
Sheets("RaceList").Select
Sheets("RaceList").Range("A2:AB500").Select
Selection.ClearContents
Sheets("Races2").Select
Sheets("Races2").Cells.Select
Selection.ClearContents
With Sheets("Races2").QueryTables.Add(Connection:= _
"URL;http://formguide.cyberhorse.com.au/index.php/Form/view-form.html" _
, Destination:=Sheets("Races2").Range("$A$1"))
.Name = False
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.Refresh BackgroundQuery:=False
End With
A = 2
B = 2
strquote = Chr$(34)
TrackName = vbNullString
RaceNumber = 0
Do While Sheets("Races2").Cells(A, 1) <> vbNullString Or Sheets("Races2").Cells(A + 1, 1) <> vbNullString
If IsNumeric(Sheets("Races2").Cells(A, 1)) = False Then
TrackName = Sheets("Races2").Cells(A, 1)
If TrackName = "Port Macquarie" Then TrackName = "Pt Macquarie"
A = A + 2
End If
If IsNumeric(Sheets("Races2").Cells(A, 1)) = True Then
RaceNumber = Sheets("Races2").Cells(A, 1)
Sheets("RaceList").Cells(B, 1) = TrackName
Sheets("RaceList").Cells(B, 2) = RaceNumber
Sheets("RaceList").Cells(B, 3) = "=GetAddress(Races2!E" & A & ")"
Sheets("RaceList").Cells(B, 4) = "=SUBSTITUTE(C" & B & "," & strquote & "&" & strquote & "," & strquote & "&&" & strquote & ")"
Sheets("RaceList").Cells(B, 4) = "http://" & "formguide.cyberhorse.com.au/index.php/Form/form.html?" & Right(Sheets("RaceList").Cells(B, 4), Len(Sheets("RaceList").Cells(B, 4)) - 80)
Sheets("RaceList").Cells(B, 5) = "'" & Mid(Sheets("RaceList").Cells(B, 4), 70, 10)
A = A + 1
B = B + 1
End If
Loop
RaceCount = B - 1
End Sub



My vba is limited but i can see where the problem is, in the above code if all we need is the address can't we just use what is outputted in column C

kennedy22384
12th November 2010, 10:35 AM
No, for some reason the address that comes out is incorrect. i have to put in an extra 2 lines of code for column D. the first one adds an extra ampersand as excel removes the ampersand from any webpages. The second line of code sets up the correct webpage. the incorrect address does provide the information to convert it to the correct address.

Shaun
12th November 2010, 12:44 PM
I will have a play with it when i get time, if i get it working i will create a ratings sheet that anyone can add there own figures to each form factor to make there own ratings.

Shaun
15th November 2010, 10:06 PM
I am having an issue, i am using part of your code but it keeps dropping race 1 from each venue when it creates the racelist, bit of help needed.



'Global RaceCodes(1 To 20) As String
'Global RaceNumbers(1 To 12) As Integer
Global RaceCount As Integer
Global RaceNo As Integer
Global RaceCode As String
Sub RunAll()
Application.Calculation = xlCalculationAutomatic
GetRaces2
For RaceNo = 2 To 2 'RaceCount
Next RaceNo
End Sub
Sub GetRaces2()
Sheets("RaceList").Select
Sheets("RaceList").Range("A2:AB500").Select
Selection.ClearContents
Sheets("Races2").Select
Sheets("Races2").Cells.Select
Selection.ClearContents
With Sheets("Races2").QueryTables.Add(Connection:= _
"URL;http://formguide.cyberhorse.com.au/index.php/Form/view-form.html" _
, Destination:=Sheets("Races2").Range("$A$1"))
.Name = False
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.Refresh BackgroundQuery:=False
End With
A = 2
B = 2
strquote = Chr$(34)
TrackName = vbNullString
RaceNumber = 0
Do While Sheets("Races2").Cells(A, 1) <> vbNullString Or Sheets("Races2").Cells(A + 1, 1) <> vbNullString
If IsNumeric(Sheets("Races2").Cells(A, 1)) = False Then
TrackName = Sheets("Races2").Cells(A, 1)
If TrackName = "Port Macquarie" Then TrackName = "Pt Macquarie"
A = A + 2
End If
If IsNumeric(Sheets("Races2").Cells(A, 1)) = True Then
RaceNumber = Sheets("Races2").Cells(A, 1)
Sheets("RaceList").Cells(B, 1) = TrackName
Sheets("RaceList").Cells(B, 2) = RaceNumber
Sheets("RaceList").Cells(B, 3) = "=GetAddress(Races2!E" & A & ")"
Sheets("RaceList").Cells(B, 4) = "=SUBSTITUTE(C" & B & "," & strquote & "&" & strquote & "," & strquote & "&&" & strquote & ")"
Sheets("RaceList").Cells(B, 4) = "http://" & "formguide.cyberhorse.com.au/index.php/Form/form.html?" & Right(Sheets("RaceList").Cells(B, 4), Len(Sheets("RaceList").Cells(B, 4)) - 80)
Sheets("RaceList").Cells(B, 5) = "'" & Mid(Sheets("RaceList").Cells(B, 4), 70, 10)
A = A + 1
B = B + 1
End If
Loop
RaceCount = B - 1
End Sub

Function GetAddress(HyperlinkCell As Range)
GetAddress = Replace _
(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function

Shaun
21st November 2010, 01:56 PM
Using code provided by kennedy22384 i have put together a sheet that will download each race and output ratings, the value of each form figure can be changed on the "Tables" sheet just change the figures in red to suit your own ratings, if you wish to exclude a form criteria from your ratings just place an "X" in the box above that list to exclude.

You will need to remove all the days races that i have tested it on so delete all sheets after the "Data" sheet, there are still plenty of bugs and it crashes after a while and takes about 30 seconds a race to update so i still have work to do, or maybe others can solve the issues.

To start the sheet go to the "Data" sheet and hit "Start" If it does crash just hit Debug then close the VBA window and press "Re-Start" you may lose the info for that race but it should continue on.

I hope to have bugs fixed in a few days.

kennedy22384
21st November 2010, 07:51 PM
Cool Shaun, I have just been data mining for greyhound's names for the last 5 days or so, i will test when im done with that cause then i got to get the form for each dog and let me say, this will take a while.

Shaun
21st November 2010, 08:23 PM
I found a few bugs and am adding more ideas so that is just a rough draft

Shaun
21st November 2010, 11:47 PM
Fixed a few bugs and added the option to import single race one at a time.
I also added a delete button that will delete all the extra sheets that are created when the days racing is done.

Open the sheet go to data and press Start this imports the race lists, then you have the option to use Automatic to import all races or Manual for a single race.

For single race option just click on the first cell of the race you want on the left hand side then press Manual

Shaun
22nd November 2010, 03:39 AM
small update

Shaun
22nd November 2010, 01:34 PM
Fixed Barrier number

Shaun
22nd November 2010, 11:21 PM
Another update, fixed the crash between races and at the end, added a Hide/Umhide button on the data sheet that hides the formulas, all you need to do is come up with a good ratings method to add to this sheet and you will have a winner.

the imports are still very slow so if someone who knows VBA can have a look at that to see why.

Shaun
23rd November 2010, 08:03 PM
I have updated the sheet but will store it offsite for easier updates the link is in my sig.

I have added some ratings figures and a new pricing system so will do some testing myself.

Thanks for the help in getting this done as it will make my own ratings much easier to use.

moeee
24th November 2010, 07:19 AM
Hey Kennedy91210

Is GRRR still about?
I ain't seen it around for ages.

Shaun
24th November 2010, 05:07 PM
New file uploaded, i think i have solved the issue with venue names so it should see all venues.

If the race has resulted it will display results, when you first run the sheet it will ask for a date, press ok for todays races or change the date to back test your ratings.

On the main page you can adjust the race times by putting in your time difference from eastern states times, example 3 for correct Perth times 1 for Brisbane times and so on.

Still trying to sort out the speed and had an issue with it freezing today so will look at that.

Shaun
24th November 2010, 05:37 PM
Since i have made a few changes on the next update i will add instructions, will also add ability to delete races from RaceLists for those venues you are not interested in rating.

moeee
24th November 2010, 05:39 PM
Is there any Chance of giving away your Program in an older version of Excel?
My 2003 won't recognise it.
Or is there something in the program what Older Excel cannot possibly do?

Shaun
24th November 2010, 07:23 PM
Give this version a try, should work ok, i will add an extra link in my info later for this version and try to remember to update it.

http://www.box.net/shared/zb6lsevs2v

moeee
24th November 2010, 07:32 PM
Thank You.

I'm not a horse person, but the way My Greyhounds is going, or not going, I'm thinking a change be best before a Lack of BANK decides the change will be no punting at all.

Shaun
24th November 2010, 09:02 PM
If you could find the form i could make this work with the greys, at least i think i could.

Shaun
27th November 2010, 11:11 AM
Ok new version up now that fixes the issue with the meetings page ,should be fixed let me know.

I will have another version up later that will record those results on a different sheet.

aussielongboat
28th November 2010, 10:33 AM
shaun, i get this message whne i open the file:
" excel found unrreadbale content in formratings...
do you want to recover the contents of this workbook.?
if you trust the source of this workbook , click yes."

Any comments.
thank you
aussie

Shaun
28th November 2010, 11:47 AM
Not sure what that is but does not seam to do any harm, will work on it a bit later meed to sort out a few bugs

Shaun
29th November 2010, 01:29 AM
I have made an update to fix a few problems and have added a results page.

moeee
29th November 2010, 07:13 AM
Shaun
I don't know whether I will use your program ever or not.
But I would like to say it is very noble of you to share.
May Allah bless you and your family and rain rewards on you that you justly deserve.

aussielongboat
29th November 2010, 07:55 AM
I have made an update to fix a few problems and have added a results page.
thanks shaun,
are you having any problems opening cyberhorse ?
this is my message this morning at 8.50AM.

"Run time error '1004'
unable to open
http://formguide.cyberhorse.com.au/index.php/form/view-form.html?
Cannot locate the internet server or proxy server.


i then typed in
http://formguide.cyberhorse.com.au/index.php/form/view-form.html (http://formguide.cyberhorse.com.au/index.php/form/view-form.html)

to my browser and it would not open the page ???


am i missing something ?

thanks and cheers
aussie

Shaun
29th November 2010, 10:43 AM
I see the problem yet again another abbreviation in the name Port is displayed as PT, go to the "Meetings" sheet and change PT to PORT and that should fix it for now

Shaun
29th November 2010, 11:32 AM
New vweaion fix this issue.

aussielongboat
29th November 2010, 11:49 AM
New vweaion fix this issue.
thanks shaun,
what about cyberhorse ???
can you get that web page/site to open

i cant get that to open manually or automatically - do i have to be a subscriber or something,
cheers and thanks again

aussie

Shaun
29th November 2010, 12:04 PM
Tell me if the form imports with the new version as i had no issues

Shaun
29th November 2010, 04:48 PM
New update, i have improved the price element and fixed a couple miscalculations, i found with the prices it would change the order of rating, so now it calculates it different to maintain your order.

aussielongboat
29th November 2010, 05:05 PM
Tell me if the form imports with the new version as i had no issues
same message
"cannot locate the internet or proxy server etc"

for cyberhorse.

i have tried their site manually and it will not load...?
is anyone else having this problem?

thanks

aussie

Shaun
29th November 2010, 06:44 PM
No problem with the site, try clearing your browser cache.

I did find another bug in the unitab ratings so would suggest not to use that form value for now.

aussielongboat
29th November 2010, 06:52 PM
No problem with the site, try clearing your browser cache.

I did find another bug in the unitab ratings so would suggest not to use that form value for now.thanks shaun
- yes i found out my IP address had been blocked by cyberhorse.
how weird

cheers

aussie

Shaun
29th November 2010, 07:12 PM
I wonder if the amount we access the site has anything to do with it, well i guess time will tell.

Dale
30th November 2010, 08:00 PM
Hi Shaun,

Impressive work,i think you may be able to help me with something,could you please email me at go the halatau at hot mail dot com and ill write you back.

Cheers

Shaun
1st December 2010, 01:25 AM
I have made an update to solve the issue of the way some races are displayed, also changed the layout a bit so read the instructions for changes, i will fully test tomorrow and let you know if i find bugs.

gsdanger
30th January 2012, 09:24 PM
Another update, fixed the crash between races and at the end, added a Hide/Umhide button on the data sheet that hides the formulas, all you need to do is come up with a good ratings method to add to this sheet and you will have a winner.

the imports are still very slow so if someone who knows VBA can have a look at that to see why.

Good Day Shaun, (and Kennedy 22384)

I have been absent from this forum for quite a few years. One of my posts was for assistance with downloading race data from the Unitab website, for data analysis and ratings production. Kennedy 22384 responded to my enquiry (22Nov2010). Unfortunately, I have only just read his response now (30Jan2012 !!). I appreciate Kennedy 22384 s efforts in trying to fix my initial problem. I downloaded his attached spreadsheet and have run it. unfortunately it has an error or 2 in the running of the macros.

I followed the thread down and have been interested in your Form Ratings 2010.xls spreadsheet. Am I able to obtain a copy of this spreadsheet, as it looks very good. I'm not sure how many updates you have completed, as the attached copies of the spreadsheet went off the forum after you posted the above referenced thread. I would like to experiment with some rating methods I have. If they seem profitable, I will post a copy onto this forum for all to use.

Please advise. Also, if Kennedy 22384 reads this thread, Thank you for attempting to assist me in my original problem...It was soooo long ago!!.

Kind Regards...

Shaun
30th January 2012, 10:08 PM
From the Unitab site i can only get data from the main page as i don't have an account.

I can get data from Cyberhorse and i have made many updates to fix issues and stuff.

What version of excel do you have?

gsdanger
31st January 2012, 04:40 AM
From the Unitab site i can only get data from the main page as i don't have an account.

I can get data from Cyberhorse and i have made many updates to fix issues and stuff.

What version of excel do you have?

Good Morning Shaun,
I am using excel 2007 version.
As stated in my previous response, can I have a copy of your Form Ratings 2010.xls spreadsheet please (latest version).

Kind regards...

gsdanger...

gsdanger
31st January 2012, 04:54 AM
From the Unitab site i can only get data from the main page as i don't have an account.

I can get data from Cyberhorse and i have made many updates to fix issues and stuff.

What version of excel do you have?

Good Morning Shaun, again,

Further to my last reply (excel version 2007),
I only used unitab for the complete form download. I don't bet with them. I bet with the NSW TAB. So I don't care where the form downloads come from, as long as the spreadsheet works. So if your Form Ratings 2010 spreadsheet extracts form from other souces than Unitab, that's fine. I just want to look at experimenting with your spreadsheet (very good) with some rating and pricing ideas I have.

Hope you can supply me with a copy of your latest version of your spreadsheet.

Kind Regards..

gsdanger....

gsdanger
2nd February 2012, 05:13 AM
I have updated the sheet but will store it offsite for easier updates the link is in my sig.

I have added some ratings figures and a new pricing system so will do some testing myself.

Thanks for the help in getting this done as it will make my own ratings much easier to use.

Good Morning Shaun,

I noticed in your quote above, you mention that the link to this spreadsheet is "the link is in my sig".
Where can I access the link please? What does "the link is in my sig" mean?, and where can I access this?

Please respond. Kind Regards... gsdanger...

Shaun
2nd February 2012, 08:31 AM
Here you go, this is my latest sheet it has been reworked from the sheet you had and imports data in a different way.

The sheet also contains the newest ratings idea i was using so feel free to use it or rework it for your own.

gsdanger
2nd February 2012, 06:35 PM
Here you go, this is my latest sheet it has been reworked from the sheet you had and imports data in a different way.

The sheet also contains the newest ratings idea i was using so feel free to use it or rework it for your own.

Good Day Shaun,

Thank you very much for this spreadsheet. I have downloaded it and ran it.
Unfortunately, reading the instructions, in particular items 3 and 5, there is a reference to a drop down box? The left column under "Race Venues" is blank. I cannot locate any drop down box on the Racelist sheet... Am I missing something? If so, please indicate where I can locate this drop down box please.

Kind Regards..

gsdanger....

Shaun
2nd February 2012, 06:59 PM
If you have a list of races in the box the drop down box is on the left.

When you first open the sheet go to RaceList sheet and you should see 4 venues already in the boxes, click an empty one and see if you get the list of races.

This is an old race day but if you see the list than you know that part works.

gsdanger
2nd February 2012, 07:26 PM
If you have a list of races in the box the drop down box is on the left.

When you first open the sheet go to RaceList sheet and you should see 4 venues already in the boxes, click an empty one and see if you get the list of races.

This is an old race day but if you see the list than you know that part works.

Hi Shaun,

Thanks for your time on this...
I have re-installed the spreadsheet just now and have run it.
I have gone to the Racelist sheet.There are 2 columns under Race Venues.
The right hand one has all the downloaded race venues in it Wanganui, Rockhampton, Hawkesbury, Geraldton and Ballarat. Obviously todays races.
the left hand column is completely blank. I have clicked all the left hand cells. All I get is an indication that the cell is the active cell. No drop down box ! I'm sure the rest of the process won't function without selecting the race venue I want. Any thoughts. Please assist...

Kind Regards...gsdanger

Shaun
2nd February 2012, 08:19 PM
Still the Data Validation issue, no problem try this version, same instruction except a bit different to get the races in the box on the left.

gsdanger
2nd February 2012, 08:44 PM
Still the Data Validation issue, no problem try this version, same instruction except a bit different to get the races in the box on the left.

Hi shaun,

Yep... That version worked..Thanks heaps for this..As you know, I have been fiddling around with spreadsheets and ratings for years (over 10). I had it all worked out with the Unitab downloads and my spreadsheet rating sheet. Unfortunately, unitab decided to alter their detailed formguide options. It rendered my download options from them useless.
I have since been looking at re-creating a spreadsheet again, similar to my original Kwikpick.xls spreadsheet. Your effors have been magnificent....
You have saved me countless hourse of frustration with trial and errors with VBA , functions and formulas. A thousand thank yous to you (and a few beers).
I will make a copy of your spreadsheet and use the copy as experimenting with the rating values, and other things. If i manage to create the Eldorado...I will post it here for all to share in....Here's hoping, although your ratings seem to be close to the mark..You never know, another pair of eyes and a bit more grey matter can help us all.
Once again, thank you for this spreadsheet. I will keep you advised of my exploits and results.

Kind Regards......gsdanger

Shaun
2nd February 2012, 09:26 PM
Glad it worked, just about everything i know about excel is used in that sheet from the downloads to the formulas that extract the info.

It has always been a work in progress and some parts of the sheet are still incomplete but as a working ratings it does work.

A lot of info in there has been provided by members of this forum and others, particularly the pricing side of it that i have struggled to get as accurate as possible.

The pricing was provided by a member from this forum the name escapes me at this time but i am sure they know who they are and is contained on other threads.

The original download idea was first posted by Kennedy can't recall his exact user name, he also has some downloads on this site that are a bit more refined but i adapted them to suit my needs and knowledge of excel.

A lot of info was gained from Mr Excel Forums that place has a wealth of information and help, specific racing macros and formulas were obtained from the Gruss forum.

these are just a few places i gained the knowledge to create the program, Google search filled in the rest.

If you are looking at a new source of data to download take a look at centrebet.com they have a great form guide that i was going to use if cyberhorse ever failed on me.

gsdanger
4th February 2012, 09:01 AM
Good Day Shaun,
yes, this trying to sort out the form and coming up with workable ratings and prices is always going to be a 'work in progress'situation.

The availability of the criteria values (Tables) sheet is great. Lets the user experiment with different combinations of values.
I guess there is no right or wrong way to rate horses. It all comes down to the users's ideas on which criteria is the most important..
I created my base rating from the average prizemoney of each horse. I know the API is basically this, However, I assigned a base rating to each horse based on the average prizemoney base rating gained from a scale I set-up, from 45 (the worst - catered for low class horses with an average prizemoney of less than 1K, to 60 (the best - catered for the champs of our sport - the Kingston Towns, Octagonals, and Lonhros). the rest of the horses were allocated a rating between these figures (naturally all done authomatically with excel). Then the Plus and Minus of the other form aspects were applied, giving me a final rating for each horse in the race. This was then priced (rating differences converted into the decimal differences and the price calculated from this).
I used these figures for race to race betting at the track, or exotics at home. All worked very well until I had the problem with Unitab re-configuring their website a few years ago. However, with this spreadsheet, I can resume building what I perceive to be a good rating method. As stated before, if I happen to have some long term on-going success, I will share it with all on this forum.

Shaun, with regards to the results sheet, does this update with the results automatically?
Also, the listings of the rated races on this sheet, can they have the race times inserted somewhere and then sorted by race times, to give you a progressive list of events by time, rather than by meeting. Maybe this could be an option, rather than the current meeting by meeting listings. Just a thought. Anyway, once again, thanks for your efforts (and all the other members who have contributed). I'm sure this spreadsheet will mature into an essential tool for rating horses in a quick and efficient manner.

Have good day Shaun.. Talk to you soon.

gsdanger