Form Guide

[Pauls123]

Hi all,....I am looking for a form guide from the internet, free of course where I can copy and paste the following data to excel. A lot of form guides show the form all clustered together which I cant copy and paste into different cells. Racenet for example.

www.ozeform.com (http://www.ozeform.com) comes close but it doesnt give me the "going".

The form that I want is:

Last Start Beaten Margin
Weight Last Start
Last start Venue/Distance and Going

I've been using ozeform and doing the "going" manually from other form guides.

Or if any of you excel experts have better ways of extracting this form.

Thanks, Paul

[Pauls123]

and,.......the "time" of the last start, nearly forgot,...!

[stugots]

Paul, Unitab members form can provide the details you have mentioned - copy & paste into excel.

[Pauls123]

Thanks Stugots, I am assuming you have to be a member,.?

Paul

[Shaun]

What version excel do you have.

[Pauls123]

Thanks Stugots for that. I joined up with Unitab today and at this stage their formguide seems to be quite good.

Shaun, I have Excel 2010.

Sitting here now redoing my spreadsheet to match up with the Unitab formguide. As you could have guessed from my questions I endeavour to give every horse in the field a rating arising from its last start finishing position/weight variation/time outside the "best" time for that distance, at that track on that going.

My VLookup table has now around 1600 times and grows every time I find a runner that raced somewhere that I didnt previously have. Concentrating on Sydney saturdays only.

Last saturday it rated 3 winners on top (R1 $11.00, R3 $4.00 and R4 $6.50). Every horse gets a kilogram rating with all times/margins etc, converted to kgs.

Hopefully with the Unitab formguide it might make my task much easier to do the copying and pasting, it was rather messy before.

[Shaun]

Have you ever tried using web queries to get the data in to excel.

[Pauls123]

Hi Shaun, no,..cant say that I have, I'd have to do some reading as to what they are exactly,..!

Currently trying to work out how to convert the "time" in the Unitab site to seconds, which is currently shown as: "2-30.3" etc. I've got all my times in seconds so that I can subtract one from the other,..any thoughts.

And briefly what are web queries,

Paul

[lomaca]

Hi Shaun, no,..cant say that I have, I'd have to do some reading as to what they are exactly,..!

Currently trying to work out how to convert the "time" in the Unitab site to seconds, which is currently shown as: "2-30.3" etc. I've got all my times in seconds so that I can subtract one from the other,..any thoughts.

And briefly what are web queries,

PaulYou can play around with this, if you have this "2-30.3" in A1 then put this formula in B1 =LEFT(A1,1)*60+MID(A1,3,2)+RIGHT(A1)
and would give you the result 150.3 sec


I haven't used Excel for programming for, well never, but it should give you a start.

Good luck

[lomaca]

You can play around with this, if you have this "2-30.3" in A1 then put this formula in B1 =LEFT(A1,1)*60+MID(A1,3,2)+RIGHT(A1)
and would give you the result 150.3 sec


I haven't used Excel for programming for, well never, but it should give you a start.

Good luckoops
that should be =LEFT(A1,1)*60+MID(A1,3,2)+RIGHT(A1)/10

missed the cut by that much " "

[Pauls123]

Thanks Iomaca for that. I just tried it and had to play with the last little bit a tad. It now works for:

=LEFT(L2,1)*60+MID(L2,3,2)+RIGHT(L2,3)

If I format the active cell to Number and 2 decimal places.

Thanks again, Paul

[Shaun]

Not sure what that time represents maybe you could post an excel sheet with some copied data i could work on.

Web Queries are basically copying the data from the website directly in to excel, it would use the url of the page than download the web tables, you can download the whole page or only part of it, i will post example shortly.

[Shaun]

Here is a simple web query just change the race code and race number and press "Get Race" there is a VBA code in the code section, i have a few sheets floating around this forum that will give you ideas on how to do things, if done right you should be able wo automate the tasks.

[Pauls123]

Thanks Shaun, I shall have to sit down and study this tomorrow, see if I can fathom it out, maybe some more questions then,

Paul

[lomaca]

Thanks Iomaca for that. I just tried it and had to play with the last little bit a tad. It now works for:

=LEFT(L2,1)*60+MID(L2,3,2)+RIGHT(L2,3)

If I format the active cell to Number and 2 decimal places.

Thanks again, PaulWith hindsight, if the time is always expressed in this format then this formula is simpler:

=LEFT(A1,1)*60 + MID(A1,3,4)
Cheers

[Pauls123]

Thanks Iomaca for your help in this. Been out all day (playing bowls) and just getting back into this spreadsheet now,

Paul

[Pauls123]

One of the problems I have encountered with the Unitab formguide, is that it shows the margin as,....eg,.."3.5L",....whether it won OR lost by 3 1/2 lengths,

hmm

[lomaca]

One of the problems I have encountered with the Unitab formguide, is that it shows the margin as,....eg,.."3.5L",....whether it won OR lost by 3 1/2 lengths,

hmmthat should not be a great problem if you also record the finishing pos and number of runners which is the far left cell like "1-9" meaning finished first out of 9 runners.
In this case all you have to do is put a simple formula in the len. cell somet like " if the number in the finished cell is 1 then the value in this cell is multiplied by -1"

[Pauls123]

Thanks again Iomaca, but the format of the cell which shows where the horse finished out of so many runners etc, is in "Date format" and I cant seem to get it to change to the format of what I want,

Paul

[lomaca]

Thanks again Iomaca, but the format of the cell which shows where the horse finished out of so many runners etc, is in "Date format" and I cant seem to get it to change to the format of what I want,

PaulI don't know how you cut and paste your info, I just looked at the Unitab site and I saw the field as for instance "2-8' etc.

Show us your Excel sheet first.

I suggest you ask Shaun to look at your spreadsheet.
As I said I do not code in Excel (VBA) simply because it's not much used commercially.

[Shaun]

My excel formulas can be a bit different as i am not the best at combining them but this should work.

format the cell to text so if A1 has 1-9
A2=TRIM(CLEAN(LEFT(A1,FIND("-",A1)-1)))
A3=IF(A2+0=1,"Winner","")
replace the winner with what ever you want as a flag.

[stugots]

Thanks again Iomaca, but the format of the cell which shows where the horse finished out of so many runners etc, is in "Date format" and I cant seem to get it to change to the format of what I want,

Paul


Paul, try formatting these cells as 'text'.

[Pauls123]

Gentlemen, thanks for your help. Those cells are actually formatted as "custom". Formatting to "text" didnt work. Shaun's idea gave me a #VALUE error,.................I'll keep persevering, thanks again,

Paul

[Shaun]

If you could upload your sheet might be easier to work on or if to large upload part of it.

[stugots]

Gentlemen, thanks for your help. Those cells are actually formatted as "custom". Formatting to "text" didnt work. Shaun's idea gave me a #VALUE error,.................I'll keep persevering, thanks again,

Paul

Paul, first format the area you will paste the form to as 'text' & then paste the form as 'paste special' / 'text' - this will fix the date format issue.

[Pauls123]

Hi all. I'll try and upload a sample of the main part of my spreadsheet here now, it has most of my formulas, you'll probably think its all over the place. The right hand side wont show all my look up "times", they are now up to around 1700 of these. It's basically the G column that is the issue here. As when the data from Unitab is pasted it doesnt differentiate between a winning margin and a losing margin, but we've discussed this already.

Stugots, I tried your theory again but when I copied and pasted "special/text" all the entries come up with the same cell/s, so to speak.

I've pretty much worked out a way around this dilemna, just means each race takes just a couple of minutes longer.

And Shaun, I still havent spent any time on your other sample yet, just been flat out with other things at present, but I will,..!

Thanks again, Paul

[stugots]

yep, should have mentioned you will get different results depending on the browser used - the attached worksheet shows what you you will get with chrome & firefox

[Pauls123]

Now you tell me,..(laughing). So I should be now looking into installing a different browser, which do you prefer.

[Shaun]

When you paste the finishing position try this right click select "Paste Special" then down the bottom it should say "Paste Special" again press that so you get the option box pop up then try" values and number formats" see what it pastes

[Pauls123]

Another minor hiccup with this also is, when there is a scratching or the runner has had less than 3 starts, it throws all your rows out. i.e. You have your rows set up for all runners.

[Pauls123]

Shaun, that facility is "greyed out",....!

[stugots]

Paul, I use have used both & prefer Chrome

& yes, you will need to setup formulas to pickup when there are scratchings

as they say, life wasnt meant to be easy:)

[Pauls123]

Nobody told me there'd be days like these,..!
Stugots, I just installed Chrome, about to suss it out now, thanks again

[Shaun]

lol welcome to the world of excel