Excel Help

[barn32]

I could use a little advice to do the following:

I want to import (or copy) the data at this site:

http://www1.drf.com/misc/pursevalueindex.pdf

It is the track abbreviations and relative purse values for all North American Tracks.

I'm trying to put it into an Excel file so that it will look like this:




Everything in a nice neat orderly column. With all values in the same column so it is a lot easier to work with.

I'm having a devil of a time doing it. I have ASAP Utilities, but I could find nothing there. I know there must be an easier way other than just doing it one line at a time.

I've done stuff like this before in the past, but it's been a while.

Thanks.

[DeltaLover]

Use the following as a csv:

Albuquerque, NM,Alb,12
Aqueduct, NY,Aqu,61
Arapahoe Park, CO,ArP,14
Arlington, IL,AP,47
Assiniboia Downs, MB,AsD,13
Belmont Park, NY,Bel,68
Betfair Hollywood Park, CA,BHP,56
Beulah, OH,Beu,8
Calder Race Course, FL,Crc,28
Canterbury Park, MN,Cby,24
Charles Town, WV,CT,28
Churchill Downs, KY,CD,55
Colonial Downs, VA,Cnl,27
Columbus, NE,Cls,8
Del Mar, CA,Dmr,77
Delaware Park, DE,Del,37
Delta Downs, LA,DeD,38
Ellis Park, KY,ElP,29
Emerald Downs, WA,EmD,20
Evangeline Downs, LA,EvD,30
Fair Grounds, LA,FG,42
Fair Meadows Tulsa, OK,FMT,16
Fairplex Park, CA,Fpx,41
Finger Lakes, NY,FL,19
Fonner Park, NE,Fon,8
Fort Erie, ON,FE,17
Golden Gate Fields, CA,GG,36
Gulfstream Park, FL,GP,49
Hastings, BC,Hst,22
Hawthorne, IL,Haw,37
Hoosier Park, IN,Hoo,42
Horsemen’s Park, NE,Hpo,9
Indiana Downs, IN,Ind,39
Keeneland, KY,Kee,54
Kentucky Downs, KY,KD,61
Kin Park, BC,Kin,4
Laurel Park, MD,Lrl,39
Lincoln, NE,Lnn,9
Lone Star Park, TX,LS,22
Louisiana Downs, LA,LaD,27
Marquis Downs, SK,MD,4
Meadowlands, NJ,Med,36
Monmouth Park, NJ,Mth,46
Mountaineer Park, WV,Mnr,22
Northlands Park, AB,NP,19
Oaklawn Park, AR,OP,47
Ocala Training Center, FL,OTC,20
Parx Racing, PA,Prx,55
Penn National, PA,Pen,39
Pimlico, MD,Pim,37
Pleasanton, CA,Pln,33
Portland Meadows, OR,PM,7
Prairie Meadows, IA,PrM,34
Presque Isle Downs, PA,PID,45
Remington Park, OK,RP,35
Retama Park, TX,Ret,20
River Downs, OH,RD,8
Ruidoso Downs, NM,Rui,14
Sacramento, CA,Sac,35
Sam Houston, TX,Hou,23
Santa Anita, CA,SA,61
Santa Rosa, CA,SR,35
Saratoga, NY,Sar,79
Suffolk Downs, MA,Suf,19
SunRay Park, NM,SrP,22
Sunflower Downs, BC,Snd,4
Sunland Park, NM,Sun,33
Tampa Bay Downs, FL,Tam,20
Thistledown, OH,Tdn,9
Timonium, MD,Tim,27
Turf Paradise, AZ,TuP,15
Turfway Park, KY,TP,22
Will Rogers Downs, OK,WRD,22
Woodbine, ON,WO,62
Zia Park, NM,Zia,34

[barn32]

Originally Posted by DeltaLover

Use the following as a csv:

Albuquerque, NM,Alb,12
Aqueduct, NY,Aqu,61
Arapahoe Park, CO,ArP,14
Arlington, IL,AP,47
Assiniboia Downs, MB,AsD,13
Belmont Park, NY,Bel,68
Betfair Hollywood Park, CA,BHP,56
Beulah, OH,Beu,8
Calder Race Course, FL,Crc,28
Canterbury Park, MN,Cby,24
Charles Town, WV,CT,28
Churchill Downs, KY,CD,55
Colonial Downs, VA,Cnl,27
Columbus, NE,Cls,8
Del Mar, CA,Dmr,77
Delaware Park, DE,Del,37
Delta Downs, LA,DeD,38
Ellis Park, KY,ElP,29
Emerald Downs, WA,EmD,20
Evangeline Downs, LA,EvD,30
Fair Grounds, LA,FG,42
Fair Meadows Tulsa, OK,FMT,16
Fairplex Park, CA,Fpx,41
Finger Lakes, NY,FL,19
Fonner Park, NE,Fon,8
Fort Erie, ON,FE,17
Golden Gate Fields, CA,GG,36
Gulfstream Park, FL,GP,49
Hastings, BC,Hst,22
Hawthorne, IL,Haw,37
Hoosier Park, IN,Hoo,42
Horsemen’s Park, NE,Hpo,9
Indiana Downs, IN,Ind,39
Keeneland, KY,Kee,54
Kentucky Downs, KY,KD,61
Kin Park, BC,Kin,4
Laurel Park, MD,Lrl,39
Lincoln, NE,Lnn,9
Lone Star Park, TX,LS,22
Louisiana Downs, LA,LaD,27
Marquis Downs, SK,MD,4
Meadowlands, NJ,Med,36
Monmouth Park, NJ,Mth,46
Mountaineer Park, WV,Mnr,22
Northlands Park, AB,NP,19
Oaklawn Park, AR,OP,47
Ocala Training Center, FL,OTC,20
Parx Racing, PA,Prx,55
Penn National, PA,Pen,39
Pimlico, MD,Pim,37
Pleasanton, CA,Pln,33
Portland Meadows, OR,PM,7
Prairie Meadows, IA,PrM,34
Presque Isle Downs, PA,PID,45
Remington Park, OK,RP,35
Retama Park, TX,Ret,20
River Downs, OH,RD,8
Ruidoso Downs, NM,Rui,14
Sacramento, CA,Sac,35
Sam Houston, TX,Hou,23
Santa Anita, CA,SA,61
Santa Rosa, CA,SR,35
Saratoga, NY,Sar,79
Suffolk Downs, MA,Suf,19
SunRay Park, NM,SrP,22
Sunflower Downs, BC,Snd,4
Sunland Park, NM,Sun,33
Tampa Bay Downs, FL,Tam,20
Thistledown, OH,Tdn,9
Timonium, MD,Tim,27
Turf Paradise, AZ,TuP,15
Turfway Park, KY,TP,22
Will Rogers Downs, OK,WRD,22
Woodbine, ON,WO,62
Zia Park, NM,Zia,34

Thank you very much, but can I ask how you did that?

[DeltaLover]

1) Clip the text from the web site
2) Save it in a text file (which will have three lines per track)
3) Write a script to read the file by a batch of three lines per time and construct a comma delimited string which you save as a new line in a text file

The whole process takes less than two minutes (including writing the script)!

[OTM Al]

Originally Posted by DeltaLover

1) Clip the text from the web site
2) Save it in a text file (which will have three lines per track)
3) Write a script to read the file by a batch of three lines per time and construct a comma delimited string which you save as a new line in a text file

The whole process takes less than two minutes (including writing the script)!

Easier if you don't know how to write script
Copy one group of columns at a time and Paste Special>Text into spreadsheet
you should get one column with a number of rows here. Repeat
Text to Columns>Delimit first by Comma, then by Space
Concatenate back together track and state if you wish

[barn32]

Originally Posted by OTM Al

Easier if you don't know how to write script
Copy one group of columns at a time and Paste Special>Text into spreadsheet
you should get one column with a number of rows here. Repeat
Text to Columns>Delimit first by Comma, then by Space
Concatenate back together track and state if you wish

I appreciate all the help, but I'm afraid I'm a little bit lost. I do not now how to write script.

However many ways I try I cannot get the data into Excel in columns. It always imports (or copies) into one row. I tried transposing and everything I could think of, but it just doesn't quite work. The only way I could get it to work is one row for each piece of data, like this:

Saratoga
NY
Sar
79

Sam
Houston
Tx
Hou
23

I have one more thing I want to do with the PDF data from this page. There is a link on top that allows you to view the data in a PDF format. I want to put the data into an Excel spreadsheet, same as the other, in nice neat columns that all line up, but I'm afraid I don't quite have the skills to do it.

http://www.drf.com/events/3-year-best-time-chart

[Red Knave]

I have saved Delta's table as an xls file and attached it to this post. If you use Open from Excel it should open as a proper spreadsheet with 4 columns and 75 rows.
If not, post back and let us know.

PS to PA - I actually tried to upload a csv file but that file type is not allowed but xls, xlsx files are?

[barn32]

Originally Posted by Red Knave

I have saved Delta's table as an xls file and attached it to this post. If you use Open from Excel it should open as a proper spreadsheet with 4 columns and 75 rows.
If not, post back and let us know.

PS to PA - I actually tried to upload a csv file but that file type is not allowed but xls, xlsx files are?

Thanks a lot. I was able to use Delta's tables and import it as a CVS file and get it to work. (I guess I didn't make that clear.)

I need the second link now so I can get the three-year track records in a spreadsheet in the same format as the tracks. Here's the link. (I've been working on it all morning, but I've given up.)

http://www.drf.com/events/3-year-best-time-chart

[At the top of the page is a link that brings up the data in a PDF file.]

Thanks again for the help, I appreciate it.

[barn32]

It took a while but I finally got it. I had to download a "A-PDF to Excel" program with a 15 day free trial, and with a little work I was able to put the PDF file into Excel just the way I wanted.

All I've accomplished is a way to compare shippers and tracks. In the example, EMD to GG I can see at a glance GG is 4/5 of a second slower at 6f, but the purses are about 80% larger at GG.

Baby stuff.

[Tom]

Barn, I had put that same purse value chart into Excel not long ago and then set it up to make a similar class chart to the one in the PAce Makes the Race book - using index numbers. It's not pretty, just a rough work sheet to get a point out of the blue columns, but if anyone is interested, her it is.

Thanks for the link to the PDF program. I can use that one.

[barn32]

Originally Posted by Tom

Barn, I had put that same purse value chart into Excel not long ago and then set it up to make a similar class chart to the one in the PAce Makes the Race book - using index numbers. It's not pretty, just a rough work sheet to get a point out of the blue columns, but if anyone is interested, her it is.

Thanks for the link to the PDF program. I can use that one.

Are the numbers one through nine arbitrary? Just a kind of grouping to get a general idea of class hierarchy by track?

As for the program, it worked, but it had some flaws. When I converted the turf portion to Excel it left out quite a few times, I have no idea why, which I had to insert by hand.

If that didn't work, this procedure looked promising. Open the PDF in Word, covert to a table, and then paste the table into Excel. (I didn't get a chance to try it.)

And then there was this program I was going to try if I couldn't get the other two ideas to work. Not sure if it's free, or free trial, but it looks pretty good.

I'm sure there are others.

All In One PDF Tools

[Red Knave]

Originally Posted by barn32

If that didn't work, this procedure looked promising. Open the PDF in Word, covert to a table, and then paste the table into Excel. (I didn't get a chance to try it.)

This didn't work for me in Libre Office (similar to Word) because there are no definable delimiters between the columns. In the example you posted the column values come into the document with a space between them. If you copy and paste from the DRF PDF there are arbitrary numbers of spaces.
The PDF to Excel software seems like a good solution.

[Tom]

Barn, the numbers 1-9 are the classes.
I wanted to make 10, but erred in my formula and only got 9, but that is good enough.

I divided the who series of number into z-scores and split up them up in sigma groups.

If a horse is going from a 5 track to a 4 track, the adjustment was 1.
It was intended to mimic the Sartin Class Chart.

[barn32]

For anyone who might be interested my spreadsheet is 90% completed.

I've attached a screenshot and a link to the workbook.

It shows the difference in time from one track to another by distance.

It compares purse values track to track.

It lists the track records for both tracks in question by distance and the differences between distances (unless there is a gap of two.)

And there is also a distance calculator for determining the difference between any two distances on the chart for the chosen track, such as sprint to route and route to sprint.

Default zoom is 80%

Turf to dirt and dirt to turf is not completed.