Smartgambler
Pro-Punter

Go Back   OZmium Sports Betting and Horse Racing Forums > Public Forums > General Topics
User Name
Password
Register FAQ Search Today's Posts Mark all topics as read

To advertise on these
forums, e-mail us.

Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 29th May 2007, 09:38 PM
Sparky12 Sparky12 is offline
Member
 
Join Date: Jan 2006
Posts: 35
Default Excel Formula Help

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
Reply With Quote
  #2  
Old 29th May 2007, 10:31 PM
Shaun Shaun is offline
Member
 
Join Date: Jan 1970
Location: Western Australia
Posts: 3,456
Default

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.
Attached Files
File Type: xls New Ratings.xls (215.0 KB, 4168 views)
Reply With Quote
  #3  
Old 30th May 2007, 05:53 AM
Sparky12 Sparky12 is offline
Member
 
Join Date: Jan 2006
Posts: 35
Default

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
Reply With Quote
  #4  
Old 30th May 2007, 07:56 AM
Shaun Shaun is offline
Member
 
Join Date: Jan 1970
Location: Western Australia
Posts: 3,456
Default

Np it is easier for me to explain answers when you have the sheet to go by, so fire away when ready.
Reply With Quote
  #5  
Old 31st May 2007, 10:52 AM
Chinbok Chinbok is offline
Member
 
Join Date: Nov 2005
Posts: 50
Default

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
Reply With Quote
  #6  
Old 31st May 2007, 11:42 AM
Chrome Prince Chrome Prince is offline
Member
 
Join Date: Jan 1970
Posts: 4,362
Default

That's an excellent spreadsheet Shaun, well done.
I know how much time it takes to get all that right.
__________________
RaceCensus - powerful system testing software.
Now with over 398,000 Metropolitan, Provincial and Country races!
http://www.propun.com.au/horse_raci...ng_systems.html
*RaceCensus now updated to 29/02/2024
Video overview of RaceCensus here:
http://www.youtube.com/watch?v=W821YP_b0Pg
Reply With Quote
  #7  
Old 31st May 2007, 02:29 PM
Shaun Shaun is offline
Member
 
Join Date: Jan 1970
Location: Western Australia
Posts: 3,456
Default

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.

Last edited by Shaun : 31st May 2007 at 02:42 PM.
Reply With Quote
  #8  
Old 2nd June 2007, 08:29 AM
Sparky12 Sparky12 is offline
Member
 
Join Date: Jan 2006
Posts: 35
Default

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
Reply With Quote
  #9  
Old 2nd June 2007, 11:11 AM
Shaun Shaun is offline
Member
 
Join Date: Jan 1970
Location: Western Australia
Posts: 3,456
Default

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)
Attached Files
File Type: txt Starters.txt (1.2 KB, 2248 views)
File Type: txt RACE.txt (16.7 KB, 2236 views)

Last edited by Shaun : 2nd June 2007 at 11:18 AM.
Reply With Quote
  #10  
Old 2nd June 2007, 10:48 PM
Sparky12 Sparky12 is offline
Member
 
Join Date: Jan 2006
Posts: 35
Default

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 . 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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is On
Forum Jump



All times are GMT +10. The time now is 02:13 AM.


Powered by: vBulletin Version 3.0.3
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
©2008 OZmium Pty. Ltd. All rights reserved . ACN 091184655