some excel help please

[DR RON]

Could someone please help?

I was trying to download some form from the ozeform horse search database and have encountered a few problems that go beyond my excel abilities. The major problem is the finishing position and field numbers. For example 1/12
will come out as 1 December. I know that the columns need to be formatted but I cant seem to do it correctly. I would also like to split the two figures into seperate cells. Also with the beaten margin figure, if the horse has won the margin to the second horse comes up and as I am experimenting with the beaten margins I would like excel to turn the margin to zero if the horse has finished first. Thanking you in advance.

my email address is sonnyboy64 at optusnet dot com dot au


Happy new year to everyone on the forum and please drive safely tonite.

[Chrome Prince]

Hi Ron,

I can probably help you....I think.

If you want, send me an excel example and I'll give it a shot.

racestats at hotmail dot com.

[Shaun]

I sent you an E-mail Dr Ron have done lots of work with the Oze Form site as well as cyberhorse site regarding this issue

[DR RON]

Thanks guys, emails have just been sent.

[Chrome Prince]

DR RON,

I've sent you a workaround for this problem just now with an example.

Cheers.

[Shaun]

Mind sending that to met Chrome would like to take a look
shaun2166@hotmail.com

[Chrome Prince]

Copy sent to you Shaun.

Cheers.

[Goldcoaster]

May i also get a copy Chrome
kewldude28 at hot mail dot com
Thanks in advance

[Shaun]

Thanks chrome....i am new at excel and wanted see if what i was doing to extract information was correct....and it was according to your file....i also use this formula to extract information from cyber horse form

=TRIM(MID(A$2,FIND("&",SUBSTITUTE(A$2,",","&",B2))+1,FIND(",",REPLACE(A$2,1,FIND("&",SUBSTITUTE(A$2,",","&",B2)),""))-1))


where B2 has the amount of commas that you want to skip to and A2 contains the form line

[Chrome Prince]

Wow Shaun, you're obviously far more advanced than me at this.

My codes are very basic and you have got a much better grasp at getting the info "parsed".

Well done and thanks for the code.

I'll email you a workaround for Cyberhorse as well that may not require code.

[Shaun]

Sorry to dissapoint you mate...but i was lucky enough to stumble on the best forum for help....you just tell them what you need and they well provide answers very quickly http://www.mrexcel.com/board2/index.php

[Chrome Prince]

Yeah the guys on there are terrific and very quick to help, mostly U.K. guys.

A great resource!!!

[puntz]

How's this for an idea, this thread can continue with these type of spreadsheet tips. The advantage is it may possibly stay tuned to racing, or just mostly what's commong around here,sports and numbers, right ?!,
Point is, lets say a discussion gets going, and most have their own version of things in a spreadsheet, but there is no common ground, or a common platform to experiment with....
So, let's say Shaun's example, that very formula is used for a number crunch.
Let's say then those that participate have excel, and what's always common with these things, the tedious tasks.
So then the solution would be to "employ" the same name experimental file, same name sheets,and when sheets need to be cleared, just learn to paste a universal macro from here. It's harmless, it simply clears the sheets upon any topic of discussion at the time.
My version of clearing sheets by macro is: (contribution)

'+++++++++++++++++++++++++++++++++
'CLEAR SHEET:DOING ++
Sheets("SORT1").Select '+
Cells.Select '+
Application.CutCopyMode = False '+
Selection.Delete Shift:=xlUp '+
Range("A1").Select '+
'CLEAR SHEET:DONE '+
'CLEAR SHEET:DOING '+
Sheets("SORT2").Select '+
Cells.Select '+
Application.CutCopyMode = False '+
Selection.Delete Shift:=xlUp '+
Range("A1").Select '+
'CLEAR SHEET:DONE ++
'++++++++++++++++++++++++++++++++
See !!
there is no need to have a diverse array of lingo's when trying to explain something.
SORT1 and SORT2 are just examples,a start.
Two clean sheets in excel with a macro to clear numbers and get on with the next task when trying to explain, in particular, horse racing number crunch issues,in a way it's understood, here.
It may avoid having to RTFM's ,( Help-less files) and may even possibly avoid end up becoming a rocket scientists !, when all ya wanna do is put a bet on !


Perhaps the icons can be avoided to, somehow.

[Shaun]

Any formulas that i have or can get for people to extract information or to do what they need in excel i will provide np......i could even create a file that would extract the information you need and show you where you imput your info to creat ratings with out you giving away any information that is private....if anyone needs help let me know.

[yuckman]

Have any of you "Spreadsheet Boffins" managed to get the Retirement Staking Plan into an excel spreadsheet format yet?
It's proving difficult for a pea brain such as myself.
Many genuine punters consider the retirement plan to be the beast staking plan ever devised.

[Shaun]

have not used that for ages will have it for you later today

[laish]

Hey all,
I have a version of the rtetirement plan I use in Excel if anyon is interestd. It does most things itself, but the BET is calculated and retyped over top so it becomes a whole number and a bet of $4.46 for example.

Not sure if perfect or not, and am willing to also pass to more genius people than I for input also, wouild be apprciated actually.

Tennedos
my mail is
laish at optusnet dot com dot au
Thanks alll

[brave chief]

wow, you guys look a lot more advanced at this stuff than me. this thread should be interesting.

out of interest, how do you extract the info from Ozeform? I'd have thought you would need a script/bot to do that; ie go to the site, log in, form search, type horse's name (correctly - and if more than one horse with that name in the db, select the right one), etc.

Seems like a lot of hard work to me. Ive been a regular of ozeform for a few years now, but have only used the db on and off as i find the form search tedious.

I created a very basic spreadsheet for the Sportscolour info. It converts the sectionals into a whole number for ease of reading, compares each time to a race average etc. I also have a basic one that converts raw times & 600m sectionals into Time & pace ratings (for Sydney).

I am now working on a new spreadsheet which will use the Ozeform ratings to derive a Class Rating for a race. Any help on extracting info from the site would be appreciated.

[puntz]

I have the 9/4 staking plan on spreadsheet if anyone want's it,
leave email.

Where is the other staking plans ? I may be able to sheet those to.

[Shaun]

Looks like i am a bit late...created a retirement staking plan in excel this morning if anyone interested let me know....brave chief...i don't use ozeform any more but all i used to do was go to the race form page and copy paste to excel....then the excel page i had setup would extract the info...a little crude in some ways but it worked fine because the ozeform pages are evenly space at 9 lines it was easy to do....i now do this with cyberhorse

[Shaun]

Lets see if i can get this to work....maybe not...does the file attachment work on here

[puntz]

i think it works, file attachment-upload
it might need to be zipped

[Shaun]

ok....think it is to big to upload
376 kg

[laish]

here is my retirement I offered for all save emailing me (if mine works). Any input appreciated if required.
Tennedos

[Shaun]

yEAH I THINK IT WAS THE 2000 LINES I HAD IT ...LOL...REDUCED IT TO 100

[laish]

Hey there Shaun, I dwnloaded your plan and there is an error (big). Email me and I will send it to you t show the error if you like, otherwise looks alright
Tennedos
laish at optusnet dot com dot au

[Shaun]

yeah i kinda rushed it this morning....will fix it if you point out the problem

[Shaun]

should be fixed now

[feather]

hi shaun, as I bet two selections per race, and some races i have my 2nd & 3rd choices are on equal points - so sometimes i have three horses in a race, (so far this has proved very profitable). can u tell me what plan u use for this?, at present i am betting the same amount on both each day for the week, then increasing at the start of the new week, any help would be appreciated. thanks

[gizzard]

This is an excellent spreadsheet. Can I suggest going further with it?

By having the divisor as a 'header' input we should be able to automate the incrementing of the divisor by 1 after more losses than the divisor.
My understanding of the Retirement Plan is, for example, if your divisor is 3 then after 3 consecutive losses the divisor is increased to 4 and continues to increase after each loss.

Once we have achieved that then the next 2 items to include are the resetting of a divisor after a win and the increasing of the target after a certain value has been attained.

Looking good!

[Shaun]

Feather....what you are doing sounds ok....you could try dutching the pair to return an amount for example if you had 2 horses priced at $3.50 and $7.00 and you wanted to return $100 no matter who won you could put $30 on your $3.50 horse and $15 on your $7.00 horse.

Gizzard this has been suggested and i will get to it tonight i hope...make life to easy for you guys

[system]

hey shaun great work mate,i take after win u just reset and start again

[Shaun]

No..if you are familiar with the original Retirement Staking Plan you can just go back to the same divisor you only need to reset when you get to 100 bets or you want to change the the percentage of your bank you wish to bet with

here is the original plan
http://www.grandstand.com.au/retirement.html

[system]

cheers buddy

[system]

shaun check ur email

[Shaun]

Thought i would add another spread sheet to help you, this spread sheet can keep track of your investments very basic but does the job....one thing is i can't protect the sheet because of a macro i have in there...not sure how to do it so don't type anything in an area that has a formula writen in it...best thing to do is make a copy of the sheet and use that...that way you could have a sheet for each staking plan you use or each type of investment there is plenty of room to type the bet made...if you have any trouble let me know

[brave chief]

hey Shaun,

I've been looking at the info on the CRIS website (waturf.org). You can download race results in excel format, however the 600m sectionals are not formatted as a decimal.

eg, They appear as (34:41) instead of 34.41

At the moment, I have to type the sectional using the correct format, ie as a decimal, in the next column before i can use the figures. Is there a little macro that will do this for me?

Any ideas would be appreciated.

[Shaun]

send me a copy of one of the excel sheets
shaun2166@hotmial.com

[Shaun]

are those the numbers in the last column

=MID(A1,FIND("(",A1)+1,5)

use this formula
A1= the cell where the sectional time is

[DR RON]

Great work with the spreadsheets Shaun, goes to show you dont have to spend big bucks on programs to get want you want. Why pay for features you dont really want or use when you can keep things nice and simple.

[brave chief]

That didn't quite work, but you got me experimenting Shaun.

I have to do 2 functions, but it works now.

=IF($M1 <> 0, REPLACE($M1, 4, 1, "."), 0)

this converts (34:41) into (34.41), then in the next column i use:

=IF($N1 <> 0, MID($N1, 2, 5), 0)

thus, giving me 34.41.
This will save me a lot of time, thx mate.

[gizzard]

Shaun - have you had any chance to look at the Retirement Plan spreadsheet improvements that were discussed?

[Shaun]

No sorry been a little busy......will check it out as soon as i can.....i am not sure of the code i need to use so will post on excel forum to see what they suggest

[Schooner of New]

Hi,

I'm been following this thread with particular interest regarding the retirement staking plan.

Can anyone who uses the plan let me know if it works best with selections that have a high strike rate or lower strike rate eg. S/R 40% and Av Divi $3.00 or S/R 20% and Av Divi $5.50.



Regards

Schooner

[Shaun]

When using this today for the first time i found a formating proble...easy to fix in the original if you know how but a bit of a pain...so i have fixed and updates it

[aussieincanada]

Thank you Shaun for the Investment Manager, I have had a good selection and staking system for some time now, but needed some good book keeping.

Strike Rate. 28.57%
Average Div. $4.99 (dont know how to round it off)
Return on Investment 46.62%

I found a little glitch in it which I rectified with my little knowledge of Excel. It was giving the wrong average dividend.(I just divided it by 2) which probably accounts for the odd dividend.

It is perfect for me, so thank you for sharing, and hope you back many a winner.

It is the perfect Manager.

aussieincanada.

[Zlotti]

Every Month I am an Aussie in Somewhere.... sometimes 'Aussie in Nigeria', 'Aussie in Brazil', 'Aussie in Congo', you get my drift...

What I'm really impressed with is the 46% strike rate that an 'Aussie in Canada' can get.

I wonder if he would care to share?

OOOoops, sorry mate, have just reread the post amd it was 46.62% ROI. (Which is still impressive.)

[makadollar]

Wow Shaun, you're obviously far more advanced than me at this.

My codes are very basic and you have got a much better grasp at getting the info "parsed".

Well done and thanks for the code.

I'll email you a workaround for Cyberhorse as well that may not require code.
Hi there,I have been using Cyberhorse data for a while now do an aweful lot of typing results and other data.Do you have something that might reduce that? It would be fantastic,I can email you the format it comes in if need be...

Ta

[makadollar]

Any formulas that i have or can get for people to extract information or to do what they need in excel i will provide np......i could even create a file that would extract the information you need and show you where you imput your info to creat ratings with out you giving away any information that is private....if anyone needs help let me know. Hello Shaun, I get my selections based on ratings in the following format.Can you suggest a way or provide a worksheet that I could apply that wil lenable me to match them with actual bets results and profit/loss instead of all the typing out i do every day mate?IT would be fantastic. What I do at the moment is type along side each selection how it went what it paid and what I won lost, qui's etc.

By A Nose - Metro. Race 1, CHELTENHAM 2005, May 28 <o =""></o><st1 =""></st1><o =""></o>

1st Tip: 1. Delabee<o =""></o>

2nd Tip: 5. Jeune Royale<o =""></o>

3rd Tip: 6. Pommery Princess<o =""></o>

Best Each Way Tip: 1. Delabee<o =""></o>

Long Shot tip: 2. Toast to Lenny<o =""></o>



Maka

[Shaun]

shaun 2166 @ hotmail.com


send me some info to look at and a description of what you need

[makadollar]

Hello Shaun, I get my selections based on ratings in the following format.Can you suggest a way or provide a worksheet that I could apply that wil lenable me to match them with actual bets results and profit/loss instead of all the typing out i do every day mate?IT would be fantastic. What I do at the moment is type along side each selection how it went what it paid and what I won lost, qui's etc.

By A Nose - Metro. Race 1, CHELTENHAM 2005, May 28 <o =""></o><st1 =""></st1><o =""></o>

1st Tip: 1. Delabee<o =""></o>

2nd Tip: 5. Jeune Royale<o =""></o>

3rd Tip: 6. Pommery Princess<o =""></o>

Best Each Way Tip: 1. Delabee<o =""></o>

Long Shot tip: 2. Toast to Lenny<o =""></o>



Maka
Well as you can see above, the ratings I use and with some settings I apply I come up with this for each race I rate.With the 3 selections and sometimes a longshot suggestion.What I want to do is have this info in a worksheet, add results, prices,bets and calculate whether or not I won on the race.

I also want to be able to search my data to see what a horse has previously done in my records.What are the chances of something like this???

Cheers

[Glenno]

Sometimes when comparing the results of one system with the results of another system, it can be hard to say which system is really better. I wanted to find a way to compare system results which incorporates the number of selections, the number of winners and the prices of the selections.

I didn't want to just want to look at the strike rate and the profit on turnover of the system results at face value because (as we know) one system can be focused on just favourites and another can be focused on just longshots. So I have developed a spreadsheet for this purpose. It is an adaption of the USCF (United States Chess Federation) rating formula.

[Glenno]

I have modified the spreadsheet above to allow for a correction.

[Glenno]

Sometimes when comparing the results of one system with the results of another system, it can be hard to say which system is really better. I wanted to find a way to compare system results which incorporates the number of selections, the number of winners and the prices of the selections.

I didn't want to just want to look at the strike rate and the profit on turnover of the system results at face value because (as we know) one system can be focused on just favourites and another can be focused on just longshots. So I have developed a spreadsheet for this purpose. It is an adaption of the USCF (United States Chess Federation) rating formula.

I have modified the spreadsheet above to allow for a correction.

<O:p
Here is another updated version of the spreadsheet I have done.</O:p