Horses Again

[Saxman]

I am able to copy and save horse racing odds as a text file as seen below.

4/5 Idler, 4/1 Sir Windsorlot, 6/1 Gone By Sunrise, 12/1 Colbyor, 25/1
Forever Janey, 25/1 Villa Reigns, 33/1 Niceonemyson, 33/1 Whip It In, 40/1
Confused Sphere, 40/1 Pavers Star, 66/1 Only Orsenfoolsies, 100/1 Cherchedi

Importing the file into Excel as a comma separated worksheet, quite
naturally gives the following....

Cell A1 = 4/5 Idler
Cell A2 = 4/1 Sir Windsorlot

Ideally, I would like all the names listed in column A and the numericals
listed in column B. The names could then be matched against similar in
another worksheet.

I guess this could be done with Perl script, but I'm not into that.

 

[Don Guillett]

I am able to copy and save horse racing odds as a text file as seen below..

4/5 Idler, 4/1 Sir Windsorlot, 6/1 Gone By Sunrise, 12/1 Colbyor, 25/1  
Forever Janey, 25/1 Villa Reigns, 33/1 Niceonemyson, 33/1 Whip It In, 40/1  
Confused Sphere, 40/1 Pavers Star, 66/1 Only Orsenfoolsies, 100/1 Cherchedi

Importing the file into Excel as a comma separated worksheet, quite  
naturally gives the following....

Cell A1 = 4/5 Idler
Cell A2 = 4/1 Sir Windsorlot

Ideally, I would like all the names listed in column A and the numericals 
listed in column B.  The names could then be matched against similar in 
another worksheet.

I guess this could be done with Perl script, but I'm not into that.

Send more info and the url where you get the data and your file to me
dguillett1 @gmail.com

 

[Saxman]

Send more info and the url where you get the data and your file to me
dguillett1 @gmail.com

I'll do that as soon as the new information is posted which will be within
the next 24 hours.

Thanks.

 

[Saxman]

Send more info and the url where you get the data and your file to me
dguillett1 @gmail.com

This is the main url.

http://www.racingpost.com/horses2/cards/home.sd

Then individual racecards have to be scraped such as the following.

http://www.racingpost.com/horses2/cards/card.sd?race_id=540486&r_date=2011-10-26#raceTabs=sc_

The fractional odds are at the bottom of the list of horses.

Another alternative source is here.

http://horses.sportinglife.com/Meetings/

There doesn't appear to be an A -Z list of runners with odds.

 

[Don Guillett]

This is the main url.

http://www.racingpost.com/horses2/cards/home.sd

Then individual racecards have to be scraped such as the following.

http://www.racingpost.com/horses2/cards/card.sd?race_id=540486&r_date....

The fractional odds are at the bottom of the list of horses.

Another alternative source is here.

http://horses.sportinglife.com/Meetings/

There doesn't appear to be an A -Z list of runners with odds.

========
Cell A1 = 4/5 Idler
Cell A2 = 4/1 Sir Windsorlot
If it is as you say with no leading space in col A then a simple text
to columns should do it. If not, send me your file dguillett1
@gmail.com

Sub SplitemUp()
Application.DisplayAlerts = False
Columns(1).TextToColumns Destination:=Range("A1"), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 2), Array(4, 1))
Application.DisplayAlerts = True
End Sub

 

[Saxman]

=============================
Option Explicit
Sub ReFormatRaceResults()
Dim rg As Range, c As Range
Dim v As Variant
'Application.ScreenUpdating = False
With Sheet1
.Range("A1", Cells(1, Cells.Columns.Count).End(xlToLeft)).Copy
.Range("A2").PasteSpecial Transpose:=True
Application.CutCopyMode = False
.Range("A1").EntireRow.Delete
Set rg = .Range("A1", Cells(Cells.Rows.Count, "A").End(xlUp))
For Each c In rg
v = Split(Trim(c.Text), " ", 2)
c(1, 1) = v(1)
c(1, 2).NumberFormat = "@"
c(1, 2) = CStr(v(0))
Next c
.Range("A1").Select
End With
'Application.ScreenUpdating = True
End Sub
=================================

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the
macro by name, and <RUN>.

If the Macro works, and you are frequently opening "new" csv files, you
should probably put it into an add-in.

I get a Subscript out of range error at,
c(1, 1) = v(1)

My worksheet is labeled Sheet1 as above.

 

[Saxman]

I get a Subscript out of range error at,
c(1, 1) = v(1)

My worksheet is labeled Sheet1 as above.

Sorry, I forgot to import the data as comma separated in order to get rid
of the commas! The above works perfectly!

Would it be possible to amend the above code to get rid of the commas and
do the same if the following was posted into cell A1?

9/4 Circus Mondao, 11/4 Dynastic, 10/1 Holly Martins, 10/1 Quernstone,
10/1 Tazweed, 14/1 Samedi, 14/1 Yes It´s The Boy, 16/1 Attraction Ticket,
33/1 Graylyn Olivaa, 33/1 Letham Cottage, 33/1 Retromania, 66/1 Silver
Native, 66/1 Vagabond King

It would save having to save the data as a txt file and import data.

 

[Saxman]

I would use regular expressions to do that, simply because it is easier
for me to code and, given the amount of data you might be processing at
any given time, shouldn't make any noticeable speed difference. An
alternative would be to do the Data/Text to columns first, and then use
the preceding Sub.

When you are satisfied this is working correctly, you can UNcomment the
two ScreenUpdating lines. It'll seem to run a bit smoother that way.

==========================
Option Explicit
Sub HorseData()
Dim rg As Range, c As Range
Dim s As String
Dim re As Object, mc As Object, m As Object, sm As Object
Const sPatRemNL As String = "[\r\n]+"
Const sPatHorseData As String = "([^,\s]+)\s+([^,]+)"
Dim i As Long
Set rg = Range("A1")
s = rg.Text

Set re = CreateObject("vbscript.regexp")

With re
.Global = True
.Pattern = sPatRemNL
End With

'Remove NewLine tokens
s = re.Replace(s, " ")

'Match the data
re.Pattern = sPatHorseData

If re.test(s) = True Then
'Application.ScreenUpdating = False
i = 1
Set mc = re.Execute(s)
For Each m In mc
Set sm = m.SubMatches
rg(i, 1).Value = sm(1)
rg(i, 2).NumberFormat = "@"
rg(i, 2).Value = CStr(sm(0))
i = i + 1
Next m
End If
'Application.ScreenUpdating = True
End Sub
================================

Thank you.

Not sure what you mean by 'Data/Text to columns'?

Do you mean select the Data tab and then select the Text to Columns icon?
My data covers many columns after importing the external data from a text
file.

 

[Saxman]

On Thu, 27 Oct 2011 20:31:52 +0100, Saxman Well, if this last provided
macro works, there is no need to get into that.

It works fine Ron.

Thank you very much.