Excel Formula Help

[Sparky12]

Hi

I use MS Excel to analyse form data which I currently manually type into my spreadhseet using Racing and Sports form guide. To try and save on the manual labour I have been trying to "copy and paste" data from various websites to save manual data entry. One of the problems I am coming up against is that certain data (eg: last four starts) gets pasted into a single cell in Excel "3x45" or "1121" etc. Same problem arises with a horses distance record eg: 21 starts for 1 1st and 3 nds and 0 thirds means that 21-1-3-0 appears as a (text string?) in a single cell in Excel.

I need to break this data up into separate cells to run my calculations eg: last four starts would have 1 1 2 1 in four separate cells - but am unclear whether Excel has the funtionality to allow me achieve this?

Has anyone any experience or advice they can provide?

I'd also greatly appreciate any advice from the forum on god sources of electronic form data (Southcoast?) - manual labour is not for me!

Thanks everyone

[Shaun]

This should provide all the formulas you need, depemds where you get your form from i get all of mine from TVF free form with last 3 starts this is all added with copy and past, the fields are copied from the webpage and pasted in, the form is copied from the page then pasted to a text document then i import this.

I then use the ratings listed on the page to automaticly rate the runners.

[Sparky12]

Shaun

Many thanks for providing this info, very much appreciated. Looks exactly what I'm after - stand by for a flurry of posts from me with limitless clarification questions re the formulas/macros!

Thanks again and will let you know how I go. Will also have a look at the VFG data.

Cheers

[Shaun]

Np it is easier for me to explain answers when you have the sheet to go by, so fire away when ready.

[Chinbok]

Hi Shaun,

I'm interested in your rating method. Do you log every race, every day and do you know what your strike rate is long term?

It seems that a horse gets a track and distance score for winning and/or placing. However, a horse with 100% win strike rate doesn't get the place component of the score. The figures; 5 starts for 1 win and 1 place scores better than 3 starts for 3 wins.

Are you copying and pasting manually? This can be automated using the Web Query under Import External Data. If you use the macro recorder and do a Web Query you can see the code that you need.

Cheers

[Chrome Prince]

That's an excellent spreadsheet Shaun, well done.
I know how much time it takes to get all that right.

[Shaun]

Funny you say that CP about getting it right, i found an error in it the other day before posting it here not sure how long it has been there.

As for the win and place ratings i am only rating as long as they have had 1 win or place if they have had 10 for 10 they still get the same, this sheet i have used for many ratings methods it is more of a template rather than a current method i am using but it helps to have things in place to explain it.

Looking at what you said about the distance and track options it might be better to use a percentage of starts of these conditions as the ratings method, i might make the change and see how i go.

When i have used different methods i keep records i am a bit of a lazy punter, i know the method listed here had a 30+% with the top selection and about 70+% with the place but as we all know it is price that counts and i have always used the top 4 selection and backing those above $4 but you can make your own changes.

Yes this is done with copy paste so i can see if i need to make adjustments for horses with less than 3 starts it only takes me about 2 minutes to process a race and i find this is ok as i only look at open and above races most of the time.

This will also export to a text file with the date as the file name so takes about 30 minutes to do all my selection and print on a sheet.

[Sparky12]

Shaun


Once again mate, many thanks for sharing the excel file - I've spent a few late nights this week with Excel manual in hand getting to grips with things and your formulas will save me so much time its not funny.

One quick question for you. How do you import your data? I've done a copy and paste from VFG website and much of the data (except the race field info at the top) pastes into Excel as one column. I notice from your file (eg: rows 156 to 162) that you have evrything separated into columns which enables you to pick up info from cells such as D159 etc.

How do you achieve this? Is it using the text to columns function in Excel or do you import the data in some way that it automatically presents data in the way you want it?

Cheers

Mark

[Shaun]

Yes i thought that might be a problem for you, it is a bit of a DIY approach but i will explain what i do.

I find the race i want to rate then i copy the race list from this point as shown in "Starters.doc" this is copied directly to the excel sheet after pressing field so it aligns correctly.

I then copy the form as shown in "race.txt" but this is pasted to a text document first then i use the "import data" feature in excel 2003
selecting "Delimited" i then check mark all the delimiters and on the next screen i just select finish and on the last screen i press ok.

As long as you have pressed the "form" button on the sheet before you start the import the data will go to where it needs to be.

The only problem you will have is where a runner has only two starts you will need to insert some data in the text document before you save it, because i only use the last two runs for most of my rating i just copy one of the last two runs and insert it in the correct place this will make it all work out in the end.


PS. i had to cut the form short to reduce the file size for upload)

[Sparky12]

Many thanks Shaun. Prior to seeing your post I went with the ultra simple option and cut and paste the data directly into Excel from the VFG website. I then ran the "text to columns" utility which treated the data as delimited text and broke the data into colums in an almost identical manner to your own columns http://www.propun.com.au/racing_forums/images/smilies/smile.gif . I've rated a few races now (using my own system which looks quite similar to yours) and it seems to work OK but I'll keep checking in case it comes unstuck. I've recorded a macro to run the formatting of the imported data and make a few other changes and I can be done in less than 2 mins. Again, this is all thanks to your formulas!

Cheers

[Shaun]

Cool glad you got it working, i am no excel wiz most of what i know i learned from testing and asking questions on http://www.mrexcel.com/board2/index.php this is a fantastic site most times i can ask a question and have and answer within an hour or two.

[Shaun]

Hi again,

I found a problem with the import feature i have been using and thought i would post the answer here.

With Microsoft Office 2003 when importing the text file using Data and then import external data you need to change the "File Origin" in step 1 from "Unicode (UTF7)" to "Unicode (UTF8)" and in step 3 select "text" under "Column data Format" so that the excel doc will accept the "+" that indicates a win if not it will leave it out and stuff up the rating as i have found.

In my ratings this is not a problem unless the winner has won by more than half a length because it will get a penalty and can reduce it's overall score

[travelrock]

IMO it's better to do your form with pen and paper. That way you dont get bogged down in spreadsheets and you are focusing more on the horses then the formulas.

Print out your system and then work from that with pen and paper.

Scan the form to identify the top 5 chances then work out your ratings.

You can do it pretty quick after a bit of practice and in the process you learn more about the horses.

[Neurokahuna]

Hi all,

I just wanted to give something back to the Propun forums, albeit a token gesture. I have gleaned so much knowledge and information off here in the last 12 months, I figured I should offer my little contribution, a web query import/form splitter for Excel 2000-2007.

OK, what does it do? Well, I always wondered how screen scraping works. Then I discovered Web Queries.....they allow you get the form and DFS for free (this one is for UniTAB) rather than having to copy and paste it into the spreadsheet manually. Once your used to it, the process takes only a few seconds to load a whole race's information in including the most recent prices.

I've haven't investigated the other TABs but I imagine that with a little reformatting of the cells you can do it easily enough.

The real beauty is that as once you load a race, you can simply right click on the race and click 'Refresh Data!'...and it does....all the prices as they change right up until they jump.

I can tell you how to build the formula to work out what percent is on what horse as you refresh the race details, but that info is already on here...you just need to convert it to a formula....I have and it helps a lot with dutch betting.

Warning for UniTAB punters....the UniTAB site changes format once a race jump time gets within 5 minutes.....as a rule, remember to re-import the race information once the '5 mins to go' message pops up on the web page, or do what I do, and only load races running in the next five minutes.....If it does go pear-shaped, simply shut the spreadsheet down (WITHOUT SAVING) and re-open it.

Give it a crack and if you need any help, e-mail me on suntzu4@hotmail.com

Good luck,

Neurokahuna

[Sparky12]

I'll be the first to say many thanks for this Neuro. I struggled getting web queries to work the way I wanted previously so will have a play and see how I get on. However my biggest "thank you" is reserved for the LEN() formula in your spreadsheet. I couldn't work out how to get round the problem of horses with less than four starts and now I've got the solution! Next time please don't wait 12 months to post the solution!


Cheers mate

[Crackone]

Thanks Neurokahuna will have a look.

[Zlotti]

Shaun

Me thinks you changed your email address... hmm... was that to get away from questions?

I would appreciate a few minuyes of your time when you are free.

Cheers

Zlotti

[kennedy22384]

Hi All,

I have attached a spreadsheet for all of you to try out. The system is called Method 260 which was given to me for free from the nice people at racerate.

Instructions:

Open spreadsheet to the Main tab
Press Alt-F8 to bring up the macros window
Select Method260 and press run
Enter the date in YYYY/MM/DD format ie. Today will be 2009/05/19.
Watch the program work.

It will do each race in chronological order and create a new tab for each race. It is not 100% in that after all races are put in it will create a blank tab for each meeting. If someone can fix that, happy days.

The next day if you want to start again, simply delete every tab created leaving the Main, Horse and Table tabs in place. To do this simply select the first tab after the Table tab then while holding shift down click on the last tab. Then right click and select delete. Then save the sheet and start again.

If anyone has got a good system that could be improved by automation, post it up here and I will see if I can make it better.

Cheers.

[Shaun]

I have seen this sheet before, but never the attached macro

[kennedy22384]

Yeah, all I did was automate it as best as I could. Just run it before the start of the race day and watch it in action.

The only thing is the prizemoney change from the last race I cant get automatically.

[Shaun]

Are you only able to get information from Unitab site, i know a few people that would like it from a couple of others.

[kennedy22384]

Yeah, Unitab is the one stable website I can get the information from. I cant seem to get it elsewhere. tabcorp has made life difficult for everyone else.

[Shaun]

How about some of the bookie sites, i can list a couple maybe you could see if it is possible to get some info, i know the main thing that is needed is the opening prices.

International All Sports
Betchoice

[Brendon]

Sparky12,

post the link and I will have a go.

[kennedy22384]

I can get opening prices from IAS bet but the page does not provide me with enough data to do the ratings method. I can set something up which will simply grab the next race from each meeting and present it in their own tab. Check back with me in a few hours. :)

[Brendon]

I can get opening prices from IAS bet but the page does not provide me with enough data to do the ratings method. I can set something up which will simply grab the next race from each meeting and present it in their own tab. Check back with me in a few hours. :)'K.

When you say "opening prices" you mean prices from a particular time, say 10.30 am on the day, right?

BTW, tried your excel program. Superb!

[Pauls123]

Hi Kennedy

What am I doing wrong here. I cant see anywhere to insert the date. Or maybe mine didnt open up correctly,...hmmm?

Paul

[Shaun]

You mayneed to activate macros,


after you have saved the excel file open it then click on "Tools" then "Options" go to the tab "Security" then look for "Macro Security" set it to "Medium"

When you are back on the sheet hit
Press Alt-F8 to bring up the macros window
Select Method260 and press run
Enter the date in YYYY/MM/DD format ie. Today will be 2009/05/19

[Pauls123]

Help..! I have done all that and it works, but how do I get the fields up again..?
They seem to have all vanished somewhere.

Paul

[Shaun]

Do you see the Tabs at the bottom of the excel doc, each one is a race.

[wise one]

I have a xcel spreedsheet that I got a few years back from someone in this forum ( my apoligies for not remembering who ) This works on the Unitab site, but because they have change the layout of the second page it does not collect the data from there. ( you need to have a untab acct to get the detailed form history that this picks up ) So if someone out there can fix this and return it to me I would be very grateful.

This will not only collect data for todays races but for last month and the the 2 previous years.

In the options tab at the top click on "enable content"
Click in the purple and yellow boxes selecting "day, month , year , track "
Leave on race 01
If you want just 1 day click on the "get data day " and watch the numbers in race number move.
If you want raw data without scratching removed click on the "data" at the bottom and you can copy and paste
If you want the scratching removed click on the "blue" tab
If you want a whole month click on "get data month" tab it will however remove scratching automatically once it has collected the data
Once again is some can correct this so it collects the data of the last 3 race results it would be muchly appreciated

Wise One

[kennedy22384]

'K.

When you say "opening prices" you mean prices from a particular time, say 10.30 am on the day, right?

BTW, tried your excel program. Superb!

The opening prices on IAS only appear after the previous race is run.

[kennedy22384]

I have a xcel spreedsheet that I got a few years back from someone in this forum ( my apoligies for not remembering who ) This works on the Unitab site, but because they have change the layout of the second page it does not collect the data from there. ( you need to have a untab acct to get the detailed form history that this picks up ) So if someone out there can fix this and return it to me I would be very grateful.

This will not only collect data for todays races but for last month and the the 2 previous years.

In the options tab at the top click on "enable content"
Click in the purple and yellow boxes selecting "day, month , year , track "
Leave on race 01
If you want just 1 day click on the "get data day " and watch the numbers in race number move.
If you want raw data without scratching removed click on the "data" at the bottom and you can copy and paste
If you want the scratching removed click on the "blue" tab
If you want a whole month click on "get data month" tab it will however remove scratching automatically once it has collected the data
Once again is some can correct this so it collects the data of the last 3 race results it would be muchly appreciated

Wise One

Spreadsheet looks very interesting. Let me take a look at it.

It is simply a data mining spreadsheet to put together form and data so users can test their systems. I am a bit bust atm with work but when I get the time, i will be able to come up with something pretty good

[wise one]

The person that supplied me with this was OZDOC

I haven't been around for awhile so I'm not too sure if he/she still posts here or maybe use a different nic

Wise One

[kennedy22384]

Ok, I worked today on making an improvement on OZDOC's spreadsheet.
I decided that one meeting for a month was just not enough.
I think every race meeting going back to the beginning of 2008 should suffice.
I have come up with a way to extract each horse's last 3 starts data from UNItab. It doesnt show 4th or worse just unplaced, but i have been able to put in days since last start, barrier, weight etc.

I am just testing it out now and I will release this when I am happy. All I would like now is some systems to test to see if they are good or not.

[wise one]

you can email me I can can send you some old data

owllieathotmaildotcom

wise one

[kennedy22384]

Nah, thats cool, all I need right now is some systems people want tested out.

I have been able to sort out dead heats which ozdoc did not

[wise one]

could you send it too me me so as I can test it out

Thanks

Wise One

[kennedy22384]

I havent included code to remove the scratchings. Just filter all the scratchings and delete it when it is finished.

[kennedy22384]

Found some issues with it. Still needs work

[kennedy22384]

Here is a working version (i hope!) try it out, better to turn it on before bed time cause this will take a while.

[moeee]

OH MY GOD

Kennedy91210!!!

Ain't seen or heard of you for ages

Good to see you back :)

You always been helpful BIG Time to many.

I been running that Greyhound Spreadsheet you gave away about 3 years ago and have been having varying degrees of success with it.

[kennedy22384]

OH MY GOD

Kennedy91210!!!

Ain't seen or heard of you for ages

Good to see you back :)

You always been helpful BIG Time to many.

I been running that Greyhound Spreadsheet you gave away about 3 years ago and have been having varying degrees of success with it.

home.alphalink.com.au/~ackd/raker/raker.htm

Thats my new program which grabs ratings for all races. 30 Day free trial. Try it out. Been finding good results with odds on rated dogs and top 2 which are only ones under $10 been producing good quinellas.

Just please remember not to bet on the tab's and bet with the online bookies

[Shaun]

Kennedy91210

Are you able to help me import just one table from
betchoice.com/racing/?eventid=271668

All i am interested in is the table under the heading "flucs" now this table is normally only updated about 10 minutes before the race and each race has it's own id the only way i can get the id is to do a properties on the page this gives me the event id.

I have been trying to setup a web query directly from excel but this has to be done for each race and i have to import lots of unneeded data, also when the race is finished they add results to this page and it is stuffing up my formulas, any help would be grateful.

[kennedy22384]

Kennedy91210

Are you able to help me import just one table from
betchoice.com/racing/?eventid=271668

All i am interested in is the table under the heading "flucs" now this table is normally only updated about 10 minutes before the race and each race has it's own id the only way i can get the id is to do a properties on the page this gives me the event id.

I have been trying to setup a web query directly from excel but this has to be done for each race and i have to import lots of unneeded data, also when the race is finished they add results to this page and it is stuffing up my formulas, any help would be grateful.


Ill see what I can do.

BTW, its 22384, not 91210 lol. Hate that show lol

[kennedy22384]

Hi,

Just to let you know I have come across another issue but I can explain it on here.

When you open up the spreadsheet, go to the Dates tab and remove the following 2 rows with these dates:

21/03/08
25/12/08

The program does not like non race days :). Just do this then run it to your hearts content

[moeee]

I had a go at the Greyhound Program Kennedy.

But there ain't no instructions.
How do I select what race I wish to have handicapped?
When I ran it last night, it automatically did Cannington Race 7 and 8
Maybe I wanted to do else where.

Can't see any user input.
The only control seems to be is "get ratings"

Why did you choose qtab in preference to grv databases?

[kennedy22384]

I had a go at the Greyhound Program Kennedy.

But there ain't no instructions.
How do I select what race I wish to have handicapped?
When I ran it last night, it automatically did Cannington Race 7 and 8
Maybe I wanted to do else where.

Can't see any user input.
The only control seems to be is "get ratings"

Why did you choose qtab in preference to grv databases?

When you press get ratings, you get every race yet to run which is at least 10m from jump. Cannington R7 and R8 was all that was left. You dont select a race to have handicapped, the program does all the races Unitab covers as per above what I just said. While the program is running any races done you can select the race from the list of races and the odds for each runner will appear (Unless the ratings say they are really bad then they wont). Alternatively you can go open Export.txt which is in the same folder as the program. It updates after every qualifying race.

I only use Qtab to get races and race fields, the form comes from GRV.

I followed the number 1 rule of programming K.I.S.S. (Keep it simple stupid).

Happy Raking :)

[Shaun]

Need some help with this formula

=IF(BF7="","",IF(ISNUMBER(FIND(",",U7)),LEFT(U7,FIND(",",U7)-1),U7))

it checks to see if the cell is a number then it will remove eveything after "," problem is sometimes there is no bet fluctuation so there is just one set of numbers in the cell and it can't find "," so it returns nothing.

I need it to return the number in cell U7 if it can't find ","

[kennedy22384]

Need some help with this formula

=IF(BF7="","",IF(ISNUMBER(FIND(",",U7)),LEFT(U7,FIND(",",U7)-1),U7))

it checks to see if the cell is a number then it will remove eveything after "," problem is sometimes there is no bet fluctuation so there is just one set of numbers in the cell and it can't find "," so it returns nothing.

I need it to return the number in cell U7 if it can't find ","

try adding IF(ISNUMBER(U7),U7,

[Ziemba]

Hi

I need to break this data up into separate cells to run my calculations eg: last four starts would have 1 1 2 1 in four separate cells - but am unclear whether Excel has the funtionality to allow me achieve this?

Has anyone any experience or advice they can provide?

Thanks everyone

Assume the number 1221 is in cell A1

In cell B1, express the number 1221 as text. =TEXT(A1,"#")

In cell C1, select the first character of the text string "1221". =MID(B1, 1, 1)

In cell D1, select the second character of the text string "1221". =MID(B1, 2, 1)

In cell E1, select the third character of the text string "1221". =MID(B1, 3, 1)

In cell F1, select the fourth character of the text string "1221". =MID(B1, 4, 1)

Hth. Let me know if you need a clearer explanation.

Best

Chris

[moeee]

Ziemba

You have done well and hopefully you will continue helping others.

BUT

When you do spend your time reading a post, always remember to pay attention to the date when it was posted.
I have seen and fallen into the trap of replying to VERY OLD Posts as well.

Welcome to the Forum Ziemba.
Hopefully more members can share your enthusiasm in our quest for knowledge.

[Ziemba]

Aha!

[angrymob]

hi guys
i have uploaded a worksheet and was wondering if any one can help me get it running again a i have it running in something elsebut was trying to change it to
excel have it in another format if any one can help my addy is angrymobs99at hotmail

[kennedy22384]

type in simtools.xla in google and download it to the folder name which is in the formulas and then open it up. It should fix it, it did for me!

[angrymob]

how do you run the simtools ken have it in the proper folder but nothing happens could u walk me through it if possible
thanks ron

[moeee]

I'm probably wrong, but you may have to be in Excel and click on TOOLS,then on ADD-INS.
Then place a tick in the Box of your Sims Program.

[Shaun]

If i am working on another sheet and the macro starts i get an error because i am not on the active sheet that the web query wants to download to i added a sheet select code but all that does is change to the correct sheet, how can i get it to work no matter what sheet i am working on?



Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [A1].Value = MyMarket Then
GoTo Xit
Else
MyMarket = [A1].Value
If Len(Range("$AZ$54").Value) = 0 Then GoTo Xit
Sheets("Data-Config").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;https://www.betchoice.com/racing/betting.asp?eventid=" & Range("$AZ$54").Value, _
Destination:=Range("$A$50"))
.Name = "betting"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = """HorseTable"""
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
If Len(Range("$BB$54").Value) = 0 Then GoTo Xit
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.way2bet.com.au/form-guide/race/" & Range("$BB$54").Value, _
Destination:=Range("$AP$55"))
.Name = "Form"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=True
End With
Xit:
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End If
End Sub

[angrymob]

hi shaun or moee have u managed to have a look at my spreadsheet
kennedy managed to get it working but still cant keeps not recognising my simtools if u can have look will be really grateful
ron
also have a ms access horse racing database if anyone is interested it needs work too lol

if anyone wants to contact me im angrymobs99 t hotmail.com
ron

[Shaun]

If i get some time will check out see if i can work out what problem is, i got my code sorted out so now down to testing

[moeee]

hi shaun or moee have u managed to have a look at my spreadsheet
kennedy managed to get it working but still cant keeps not recognising my simtools if u can have look will be really grateful
ron
also have a ms access horse racing database if anyone is interested it needs work too lol

if anyone wants to contact me im angrymobs99 t hotmail.com
ron

I tried your program, but it won't work at all for me because it is looking for details of a Cranbourne Race that I don't have.

And I don't know what is "Mcorrels" and "corrand"

Best wait for kennedy to roll up.
He's redhot on this sort of stuff :)

[angrymob]

thanks moeee same here he had it running but maybe if he changes a few things im hopeless but like to try these things can send you it running in different format if you have email address

[woof43]

Hi Angry,

I had a look at your sheet, if you have Simtools loaded as an Add-in you can do the following to get it working.

In Cell C27 insert Norminv(rand(),C11,C12) then copy that formula across to J27.

Now to get your sheet to work highlight from Cell B27:J2027
then go to the header or menu where you have simtools then click Simulation table depending on the speed of your computer it will only take a few seconds and you should have a new set of probs and rankings etc at M23 :J25

[ian1506]

Hi wise one.

I was having a look at your spreadsheet from post #31 and it looks great. Can you tell me if there is any way to identify maiden races or if there is a macro etc to remove them.

I presume that the maidens are shown with zero's for each horse in the form line. Is that correct?

Any help gratefully accepted.

[kennedy22384]

Giving this thread a bump

Here is an updated version of the data grabber. It will get the unitab data and last 3 starts data for every horse for every race from 1/1/2008 to now. You can add dates by going to the dates tab but to run it, go to the Home tab and press the button.

Best to run this before you go to bed, it will take a while and in case it does crash or excel freezes up, it does an auto save after 500 races so you dont lose it all.

Enjoy

PS: the DATA tab will have all the data and you can filter the results to your hearts content. If you wanna finish it up early, simply press esc a few times until excel brings up a message box and click End.

[Crackone]

Hi kennedy22384 nice work, would it be possible to include the Uni tab ratings on the Data page?

Cheers

[kennedy22384]

Column Y mate, under the heading "R".

Cheers

[Crackone]

Column Y mate, under the heading "R".

CheersCouldn't see it for looking

[Dennis G]

I have a xcel spreedsheet that I got a few years back from someone in this forum ( my apoligies for not remembering who ) This works on the Unitab site, but because they have change the layout of the second page it does not collect the data from there. ( you need to have a untab acct to get the detailed form history that this picks up ) So if someone out there can fix this and return it to me I would be very grateful.

This will not only collect data for todays races but for last month and the the 2 previous years.

In the options tab at the top click on "enable content"
Click in the purple and yellow boxes selecting "day, month , year , track "
Leave on race 01
If you want just 1 day click on the "get data day " and watch the numbers in race number move.
If you want raw data without scratching removed click on the "data" at the bottom and you can copy and paste
If you want the scratching removed click on the "blue" tab
If you want a whole month click on "get data month" tab it will however remove scratching automatically once it has collected the data
Once again is some can correct this so it collects the data of the last 3 race results it would be muchly appreciated

Wise OneJust a heads up.....

I had a look at this one just the other day and noticed a couple of anomalies in its output. I discovered that the places (ie 1st, 2nd, 3rd) in some races were mispresented. Most of these anomalies seemed to occur in Bris/Syd/Melb races 7/8/9. Upon investigation I found that the races affected were those that UniTAB had declared a 'Jackpot' on; eg Quadrellas, Quinellas etc.

It seems that the code was reading the extra data line placed above the results box in these races and, hence, did not apply a first position, but did assign 2nd to the winner and 3rd to the runner up.

Anyone who has used this SS for UniTAB downloads should be aware that your data may be not exactly correct...

Den

[michael gorman]

I am still having trouble with downloading information from internet to excel for my horseracing information. You said about last three races from TVF. Can you please help me.

[michael gorman]

Hi Shaun , im having trouble finding the last 3 starts in TVF .
This should provide all the formulas you need, depemds where you get your form from i get all of mine from TVF free form with last 3 starts this is all added with copy and past, the fields are copied from the webpage and pasted in, the form is copied from the page then pasted to a text document then i import this.

I then use the ratings listed on the page to automaticly rate the runners.

[Shaun]

You can copy paste the full race information in to a text document then import that to excel, once you have done that you can use formulas to extract the information you need.

At one point i uploaded a sheet to this forum that containd most of the formulas you need, it was an old ratings sheet i was using.

[michael gorman]

Hi Shaun , do you include the weights in your formulas, if so how do you use weights in an effective way.



You can copy paste the full race information in to a text document then import that to excel, once you have done that you can use formulas to extract the information you need.

At one point i uploaded a sheet to this forum that containd most of the formulas you need, it was an old ratings sheet i was using.

[Shaun]

I use to include weights but when they increased the minimum weight i found them less effective.

[aussielongboat]

hi all, i cant get this or the unitab grabber to work. with the form ratings cyberhorse wont open up
and with unitab i get this message - even though i have an account:

<TABLE style="WIDTH: 426pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=568><COLGROUP><COL style="WIDTH: 426pt; mso-width-source: userset; mso-width-alt: 20772" width=568><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; WIDTH: 426pt; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20 width=568>UNiTAB</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20>Why am I seeing this page?</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20>You have clicked a Runner name and you either,</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20>do not have a UNiTAB Telebet Account; or,</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20>you have not logged into your account for the past 30 days on this computer.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20>As a free information service available to account holders, UNiTAB.com provides a detailed Form Guide for each runner in each race.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20>The Form Guide contains in-depth statistics to assist you with your betting decision, including last three starts, historical performance at the track, distance and in the wet plus breeding.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20>If you have an account and would like to access the Form Guide, login to your account now. That's all it takes.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20>You are given a 30-day pass to view the guide, whether you are logged in or not. Every time you login, your Form Guide pass is automatically renewed, so, unless you don't login for 30 days, the service is always available.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20>Don’t have a UNiTAB Telebet Account</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20>If you don't have a UNiTAB Telebet Account, visit the Create a New Account page.</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black; BORDER-LEFT: black; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: black; BORDER-RIGHT: black" height=20>You can be up and running (and viewing the Form Guide) in a matter of minutes.




any hints/guidelines from anyone would be appreciated :)

thank you
aussie


</TD></TR></TBODY></TABLE>

[Puntz]

On post 65 of this thread there is a spreadsheet. It may require by now some tweaking. Hope the original "author" of this excel spreadsheet is around to update it with the current Tatts web site.

It still works, but there are some columns that don't figure.
The VBA coding is a bit advance for me, but the final output is great.
The only other thing that may be considered to add is some sort of variable to include ALL_RACES, or races with CODES_ONLY, such as,
AR, SR, BR, MR, ....ETC.
That way it may not take as long to load up.

Thanks.