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.

 

[Ron Rosenfeld]

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 will probably come up with a better answer, starting with the URL, but until then, once you've imported the CSV file, and with all of your data in row 1, the following macro will reformat it according to your specifications.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

You may need to use a different Sheet reference than the one I used, although the one I used will probably work. Note that in this instance, Sheet1 does not refer to the tab name, but rather to what is called the CodeName, which can be different from the SheetName on the tab.

Once debugged, you may find that uncommenting the two screenupdating lines will make this execute more quickly.

=============================
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.

 

[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

 

[Ron Rosenfeld]

Don will probably come up with a better answer, starting with the URL, but until then, once you've imported the CSV file, and with all of your data in row 1, the following macro will reformat it according to your specifications.

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

You may need to use a different Sheet reference than the one I used, although the one I used will probably work. Note that in this instance, Sheet1 does not refer to the tab name, but rather to what is called the CodeName, which can be different from the SheetName on the tab.

Once debugged, you may find that uncommenting the two screenupdating lines will make this execute more quickly.

=============================
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.

My routine probably won't apply. I just pasted your data into a text file, imported it as a CSV file, and noted it all wound up in row 1; I see that is probably not the case, and you probably have all your data in column A. That being the case, a simple text-to-columns, as Don indicated, is all you need to split the two segments.

 

[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.

 

[Ron Rosenfeld]

My worksheet is labeled Sheet1 as above.

Yes but since that is a CodeName, it would still refer to that sheet even if you changed the name on the tab.

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.

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
================================

 

[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.

 

[Ron Rosenfeld]

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.

Well, if this last provided macro works, there is no need to get into that.

 

[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.

 

[Ron Rosenfeld]

It works fine Ron.

Thank you very much.

Glad to help.