Re Profit Selections.

[Robot]

Shaun. My first thread.Have found this site most interesting because of the banter and help from everyone.My question is regarding your Profit selections yesterday. Is the selection picked for 1st place or must be in the four selections of each selector. Regards Robot.

[Shaun]

Same first selection for both tipsters.

[Pauls123]

Hi Shaun,

This isnt related to this particular positing, but I have been and trying to follow some of your excel spreadsheets that you have posted on here over the last couple of years. I am trying to set up a spreadsheet and the only manual inputting I am doing is:

Beaten Margin
Last Start Venue/Distance/Going
Race Time

Without going to too much trouble is there a fast way I can import these details onto an excel spreadsheet,

Thanks, Paul

[Pauls123]

oops, I forgot one category,

Weight last start

Paul

[Shaun]

What version of excel are you using?

[Pauls123]

Office 2010

[Shaun]

This sheet has that info on it, some have already been extracted but others would need some formula to get them from the data sheet in to a column like the other form info, not sure how much excel knowledge you have.

[Pauls123]

Hi Shaun, and thanks for this. Looks rather complicated but I'll try and gain a few hints from that spreadsheet/formulas.

Regards, Paul

[The Ocho]

While your at it Shaun :) , it it possible to put up an excel sheet for 2007 excel for the R&S site to pick out the DIV column on the spreadsheet?

I'm after the highest TAB number selection that is at least 3.00+ in the DIV column (or, if you can't separate them then the 2 or 3, etc, selections with the same DIV amount). I don't want any 3.00+ selection if there is a lower one in that race though.

So basically if there is a 1.90 shot then there is no selection for that race but if the lowest DIV is 3.20 then that is the selection or say 2 horses at 5.50 (numbers 5 & 7) then 7 would be the selection (highest TAB number).

Is this possible? If it is, is it possible to include it as part of the Top Neural/100 Rater/CP/FR/Barrier 5+ sheet you did for me on another thread?

A big ask, I know, but the excel sheet for the above has been fantastic. :)

[Shaun]

Should be easy enough to make a change or 2 to those sheets i posted before to do with the R&S site, will look at it after sat races

[Shaun]

Try this, should be what you need.

[The Ocho]

Thanks Shaun. I tried it on Caulfield today. It has listed all races and the lowest DIV prices twice (see below)
Venue Race No Runner
CAULFIELD 1 1 Rockshaft
CAULFIELD 2 8 Syntonic
CAULFIELD 3 8 Neriani
CAULFIELD 4 3 Ariarni
CAULFIELD 5 4 Launay
CAULFIELD 6 1 Pinwheel
CAULFIELD 7 7 Base
CAULFIELD 8 5 Oforawesome
CAULFIELD 1 1 Rockshaft
CAULFIELD 2 8 Syntonic
CAULFIELD 3 8 Neriani
CAULFIELD 4 3 Ariarni
CAULFIELD 5 4 Launay
CAULFIELD 6 1 Pinwheel
CAULFIELD 7 7 Base
CAULFIELD 8 5 Oforawesome

Is it possible to either list the actual DIV price next to all the selections but before the horse name:

Venue Race No DIV Runner
CAULFIELD 1 1 1.80 Rockshaft
CAULFIELD 2 8 3.10 Syntonic
CAULFIELD 3 8 3.10 Neriani
CAULFIELD 4 3 2.10 Ariarni
CAULFIELD 5 4 3.40 Launay
CAULFIELD 6 1 2.20 Pinwheel
CAULFIELD 7 7 2.05 Base
CAULFIELD 8 5 4.60 Oforawesome

Or (preferably) list only the DIV price that is over 3.00+

Venue Race No DIV Runner
CAULFIELD 2 8 3.10 Syntonic
CAULFIELD 3 8 3.10 Neriani
CAULFIELD 5 4 3.40 Launay
CAULFIELD 8 5 4.60 Oforawesom

And finally after all that, can the above be placed on the same sheet as the CP/FR Barrier 5 / Top Neural / 100 rater sheet thereby only having to go through all the races only once? Big ask I know.

[Shaun]

Sure will look at that, Caulfield listed twice because i had already run it on that meeting to test it.

[Shaun]

I added a min price option for the div, what ever you put in there the price must be higher for the selection or it won't been shown.

[The Ocho]

Thanks Shaun but it hasn't picked up all the picks.

It picked up races 5 and 8 at Caulfield where there is only one selection that is in the 3.00 range but hasn't picked up races 2 and 3 where there are more than one selection in the 3.00 range in each race. This might have something to do with it?

Sorry about this but can you take a look at this when you get a chance?

[Shaun]

So do you want it to pick 1 selection or more than one in a range, right now it will look at your min and if a runner is under that it ignores the race, other wise it will find the next highest price and if there are 2 the same it will select the highest TAB number.

[The Ocho]

Just one selection that is the lowest DIV but 3.00 or higher. The excel sheet has picked the right horses at Caulfield races 5 and 8 but hasn't picked any horses in races 2 and 3 where there are more than one horse in the 3.00-3.90 range for each race.

I need the sheet to pick up the lower DIV of the horses in that range on those races but, at the same time, pick the highest TAB number should 2 or more horses have the same DIV. I hope that makes sense. Thanks for doing it, Shaun.

[The Ocho]

Sorry Shaun I just did the selections for tomorrow and on just about every race it is spot on and picking them as you said. The only problem is it has missed out on some races (see below) where the valid horses should of been selected but weren't.

GAWLER races 4 & 5
Geraldton races 1,2,3 & 7
Sunshine Coast races 2,3,4 & 7
Wangaratta races 5 & 6

Other than those it made all the other right selections so it is nearly awesome but not quite there yet...lol. You've nearly made me lazy by only running the excel sheet but it still seems to have some sort of bug.

Of course the next thing will be to pick out the venue numbers automatically so I don't need to visit the R&S site at all and all I'll have to do is make sure the date is right, press one button and BANG, out spit my selections...lol

[Shaun]

I found the problem, i won't repost but tell you how to fix, on the "Neural's" sheet select columns U to W right click and select unhide in the formula at U1 change it to this =SUM(AA1:AA3) you can re hide that column if you want, be sure to save it.

[The Ocho]

Thanks very very much Shaun. I take it you meant the formula at V1 which worked a treat.

Now you'll have to work on that automatic 1 click sheet...lol :)

Only kidding, thanks Shaun.

[Shaun]

yes thats right sorry, that formula tells the sheet to copy the contents when we have a selection, is was working if you had a selection for the other 2 systems as well but as soon as you only had that single selection it failed, so we just extended the formula, simple error on my part.



Now you'll have to work on that automatic 1 click sheet...lol

Only kidding, thanks Shaun.

Sounds like a challenge :)

[Shaun]

See if this makes it quicker, use the ID button the same as the venue, just highlight what's needed and press.

[The Ocho]

Thanks Shaun. It is certainly a bit easier.

I'm a bit naive about this sort of thing but, know that you've captured the venue ID for each track, why can't you just click on the track and the ID for the track automatically pops into the window and is ready to go rather than having to press the track button and then the ID button.

Also, I still have to look how many races are at each track. Is there some way of auto loading the last race number into that section and having the 1st race loaded into that square when you click on a new venue?

If you've got time of course.

Nearly there :)

[Shaun]

Hahaha, i normally charge for this level of modification but some of these changes i can use in my other sheets.

You only need to select the venue now, it should populate the rest just keep on eye on it.

[beton]

Shaun
Hello. Thanks for your time and effort. I can see what can be done to make life easier. I can see that using this excel sheet I can learn sufficient to know what is required. I sit in front of the computor all day but I am just a trained monkey. I have some unique experience that I can no longer do so I have learnt to translate that into drawings via autocad, learning on the job. I have only learnt enough autocad to do the job. I can see that as more whizkids gain experience then my days are numbered.
Hence my interest in punting. I can see that if stupidity can be avoided it can suppliment the looming pension.
My excel knowledge is very limited using templates to add and subtract. It is basically open previous file, save as, add or delete rows and fill in the data and save then send.
I pressed the start button and an attachment came up I could not see the ok button or is that a right click? ditto with select. is this copy? I can see that I have a lot to learn but because of interest this could be the best medium
Regards Beton

[Shaun]

Not 100% sure what might be wrong, will have to look and check it out, what version of excel do you use?

[beton]

Shaun 2007 Thanks

[The Ocho]

Excellent, EXCELLENT!!!

Thanks Shaun, very nice piece of kit. Working beautifully.

beton

When you press the start button the date pops up and you leave it for today's date or change the date say for tomorrows picks.

You then go across the neural page and click once on what venue you want to look at and then click the venue button. The venue, meeting ID, 1st race and last race will load. You then press on import, wait for it to do its thing, and BANG your selections spit out on the selections page.
:)

[beton]

Thanks Ocho and Shaun
This could be a macro problem. I have put developer into toolbar and pressed marco security. I have also turned on activeX.
I press Start and box comes up cannot run macro may not be available in this workbook or all macros are disabled.
This represents a brick wall to me
Regards Beton

[Shaun]

Ocho no need to visit the website, all is done when you hit start, just need to select the venue in that box and away you go.

Macro security is under file option in trust centre and macro settings, set it to Disable with notification, when you open the workbook it will tell you there are macros and to enable them, this is more secure for other files you don't know what the macros will do.

[The Ocho]

Thanks Shaun.

One more thing I noticed just now when checking tomorrows races. Randwick and Sandown venue numbers don't come up automatically (I had to look them) but the other 3 venues were fine

[Robot]

I Wish to learn how to do this.As a newcomer I'm amazed at what you guys have done.Is it necessary to purchase a program or does someone have one to share. Or is a freebie on the net somewhere.Will supply email address if required.
ROBOT

[Shaun]

I will take a look, think i know problem, only program needed is excel 2007 or 2010 older version may not have all the features.

[Shaun]

If you look on the meetings tab you will see they use the full track name, i am only searching the first part, i will write up a bit of code later to fix this but the best idea for now is just to go to the meetings page and remove the hyphen and everything right of it, if you add the numbers manually to the sheet you will remove the needed code from that box for the other meetings.

[beton]

Shaun
great
I have got past the macro issue. downloaded the program again.
As with Ocho Randwick and sandown present an issue. others all ok. Press import and runtime error '1004' occurs. Press debug and Module 1 (code) highlights "Refresh BackgroundQuery:= False. Thanks Beton

[Shaun]

The venue name and number must be correct other wise it can't find the webpage to download.

To solve the earlier problem add this code to sheet "Data" cell V1 then copy down the column.

=IF(W1<>"",IF(ISERROR(CLEAN(TRIM((LEFT(Meetings!W1,FIND("-",Meetings!W1,1)-1))))),CLEAN(TRIM(Meetings!W1)),CLEAN(TRIM(LEFT(Meetings!W1,FIND("-",Meetings!W1,1)-1)))),"")

[beton]

Shaun
=IF(W1<>"",IF(ISERROR(CLEAN(TRIM((LEFT(Meetings!W1,FIND("-",Meetings!W1,1)-1))))),CLEAN(TRIM(Meetings!W1)),CLEAN(TRIM(LEFT(Me etings!W1,FIND("-",Meetings!W1,1)-1)))),"")
Is the space in meetings after LEFT a typo or not
Regards Beton