Importing horse racing data to excel

[DrFong]

Hi

Could anyone tell me how to import horse racing form from the Racing
Post Website and easily split it in to columns on an excel worksheet.

What I want to do is breakdown the data in an individual horses form.
If you go the RP site http://www.racingpost.co.uk/horses/?MIval=v2_home
and scroll down to Database search, type in a horses name, say BEST
MATE, when the window with it's form comes up you can see the data I
want to break in to separate columns.

I am up to speed with the basics of spreadsheets but I want to know the
quickest and easiest way of performing this task. I have tried to do
this by copying and pasting and using "text to columns" but it is long
winded and the delimiters break the data in the wrong places.



thanks in advance.

 

[Frank Kabel]

Hi
you make it a littlbe bit difficult to helopt you:
- I may be a little bit blidn but I didn't find the 'database' section
- this page seems to require a user login for most available data
(which I won't do)

So you may post some example data (plain text - no attachment please)
to this newsgroup

 

[DrFong]

Sorry about that, this is one line of data.



--------------------------------------------------------------------------------

DATE RACE CONDITIONS RACE OUTCOME JOCKEY OR TS RPR
07May04 Chs 10GS D 3y Mdn (8K) 3/8 1¾L, Iktitaf[5/4F] 9-0 D Holland


--------------------------------------------------------------------------------

The data is, in order it appears:
Date
Course
Dist/going
class
Race type
value
position finished + field size
winner of race
price
weight
jockey

The problems I have met when splitting this down are:

separating the dist/going. if I use the fixed delimiter it separate
the numbers from the letters but also splits double numbers so tha
10GD becomes 1...0GD because you may have mainly single figure number
ie 8GD.

If the race winner has a two word name then the space delimiter slit
it in to two columns.

I have tried to use a macro but as I have used different delimiters a
different stages I have to insert columns to make room for the spli
data which seems to cause problems.

Sorry if I haven't explained this too well but basically I just want t
split this in to as small a sections as possible

 

[DrFong]

If anyone can help on this it would be greatly appreciated.

thank

 

[Frank Kabel]

Hi
I would use the Space as delimiter and afterwardy combine the name (if
it consists of two words) with a formula

 

[graham feeley]

DrFong I do this only in aussie racing please give me a password and may be
able to help you.
I need to look at the page etc
Regards
Graham

 

[AlfD]

Hi!

I can get most of the way there and you might like to see that part o
it and decide whether the missing bit matters - or can be resolved!

I use "text to columns" three times.

First to disentangle the horse's name (may be multiple words)

Note that before the horse's name is a comma: after it is a [.
Use this in text to columns by choosing delimiters of "," and "othe
[".

Now address the bit to the right of the horse's name. Text to column
using <space> as delimiter. (Note: this leaves the jockey's initial i
one column and his last name in another)

Now address the bit before the horse's name. Insert enough columns t
hold the disassembled data (is it 9?) and then, once again, data t
columns using <space> as delimiter.

Residual problems: the formatting of odds (3/1 shows as 1-March: 33/
shows as 33/1). Similarly weights: 9-0 shows as 9-0: 8-7 shows as
Aug. I've failed to sort this by reformatting: in fact my efforts ten
to make it worse!
If it matters, I think I would look to changing the formats of thes
quantities before separating them out (e.g. replace - with somethin
else: replace / with something else) because Excel is so intelligent i
sneaks up on you with assumptions sometimes. - and / don't appear to b
used in any other context

 

[DrFong]

Many thanks very much for your help.

I will try these ideas out.

 

[AlfD]

Hi!

I forgot to mention the deletion of the residual ] s. Do this by usin
find ] and replacing it with nothing.

Al