How can I scrape the results from this website into an excel spreadsheet?

[Brad24]

Hi, the code changed on this website, and my visual basic in excel no longer works.

I need to scrape the results of this page:
https://www.nascar.com/results/race...gy-nascar-cup-series/advance-auto-parts-clash


Every week, nascar will put the results of the previous race online, and I scrape the results to use in an excel application I have that tallies up the points of a small group of us in a pool.

This is the code that used to work. I didn't write it. Someone probably on this forum gave it to me:

Uly = "URL;" & raceName

Sheets("Worksheet").Activate

ActiveWindow.SmallScroll Down:=12

With ActiveSheet.QueryTables.Add(Connection:=Uly, Destination:=Range("$A$1"))

.Name = "duck-commander-500_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=288

 

[MartyS]

Due to the way the webpage has been structured, I'm not sure you're going to be able to get the data using that methodology.

There is a different way of web-scraping using the MSTML reference and Internet Control reference...

However, before exploring that - what data did you need from that webpage specifically?

I ask because if it's the following:

Excel 2010

	A	B	C	D	E	F	G	H
78	Pos	Driver	Car Make	Total Pts	Bonus	Laps	Laps Led	Qualifying Speed
79	1	Brad Keselowski	Ford	0	0	75	43	0
80	2	Joey Logano	Ford	0	0	75	1	0
81	3	Kurt Busch	Ford	0	0	75	0	0
82	4	Ryan Blaney	Ford	0	0	75	0	0
83	5	Austin Dillon	Chevrolet	0	0	75	3	0
84	6	Denny Hamlin	Toyota	0	0	75	8	0
85	7	Kyle Busch	Toyota	0	0	75	0	0
86	8	Erik Jones	Toyota	0	0	75	0	0
87	9	Kevin Harvick	Ford	0	0	75	2	0
88	10	Kyle Larson	Chevrolet	0	0	75	1	0
89	11	Ryan Newman	Chevrolet	0	0	75	0	0
90	12	Jimmie Johnson	Chevrolet	0	0	75	0	0
91	13	Chase Elliott	Chevrolet	0	0	75	17	0
92	14	Martin Truex Jr.	Toyota	0	0	75	0	0
93	15	Kasey Kahne	Chevrolet	0	0	75	0	0
94	16	Ricky Stenhouse Jr.	Ford	0	0	75	0	0
95	17	Jamie McMurray	Chevrolet	0	0	75	0	0
96
97		A. Dillon	1
98		D. Wallace Jr.	2
99		D. Hamlin	3
100		J. Logano	4
101		C. Buescher	5
102		P. Menard	6
103		R. Blaney	7
104		R. Newman	8
105		M. McDowell	9
106		A. Allmendinger	10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

..you can get that (and more) at:

https://sports.yahoo.com/nascar/cup/2018/advance-auto-parts-clash/

with the existing code you've got (you'll need to change the URL in the code, of course) - and it will probably appear at different locations in your spreadsheet than before as the HTML page is different... so perhaps try it out on a new workbook to see if it meets your requirements.

BUT.. you CAN get the data!

 

[Brad24]

Hi, I need to get the drivers name and their position. So 40 drivers and their results in the race.

If I can get the results into a spreadsheet every week, I can figure out how to filter out what I need from that. I know that Nascar puts the results of every race up weekly. I will have a look at this yahoo page. Maybe they put it up weekly as well.

 

[MartyS]

Hi, I need to get the drivers name and their position. So 40 drivers and their results in the race.

If I can get the results into a spreadsheet every week, I can figure out how to filter out what I need from that. I know that Nascar puts the results of every race up weekly. I will have a look at this yahoo page. Maybe they put it up weekly as well.

If all you need are the driver's names and their position, the information in the NASCAR website can be brought through as a Google Search!


All you'd need to do is use for the raceName variable in Uly = "URL;" & raceName

[COLOR=#333333]

 [/COLOR]    raceName = "https://www.google.co.uk/search?q=Advance+Auto+Parts+Clash"

This is returned using that web address:


Excel 2010

	A	B	C
103	Pos	Driver	Status
104	1	B. Keselowski	Running
105		Ford·#2
106	2	J. Logano	Running
107		Ford·#22
108	3	K. Busch	Running
109		Ford·#41
110	4	R. Blaney	Running
111		Ford·#12
112	5	A. Dillon	Running
113		Chevrolet·#3
114	6	D. Hamlin	Running
115		Toyota·#11
116	7	K. Busch	Running
117		Toyota·#18
118	8	E. Jones	Running
119		Toyota·#20
120	9	K. Harvick	Running
121		Ford·#4
122	10	K. Larson	Running
123		Chevrolet·#42
124	11	R. Newman	Running
125		Chevrolet·#31
126	12	J. Johnson	Running
127		Chevrolet·#48
128	13	C. Elliott	Running
129		Chevrolet·#9
130	14	M. Truex Jr.	Running
131		Toyota·#78
132	15	K. Kahne	Running
133		Chevrolet·#95
134	16	R. Stenhouse Jr.	Running
135		Ford·#17
136	17	J. McMurray	DNF
137		Chevrolet·#1

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Presumably.. if the NASCAR results are weekly.. then the google search returning the information would be weekly???

Hope that helps!?!

 

[Brad24]

That works but...the 36 races are named at the first of the year, so we have all the names, but sometimes, the race names are changed throughout the year, and it would possibly fail the search.

 

[Brad24]

Marty, did that work for you? When I put this in, it fails and goes to my error handler. The .Name isn't correct, but I thought in the past that I didn't need it anyhow.

Uly = "https://sports.yahoo.com/nascar/cup/2018/daytona-500/"
Sheets("Worksheet").Activate
ActiveWindow.SmallScroll Down:=12
With ActiveSheet.QueryTables.Add(Connection:=Uly, Destination:=Range("$A$1"))
.Name = "duck-commander-500_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.SmallScroll Down:=288

 

[Brad24]

I got it. Uly has to be Uly = "URL;" & "https://sports.yahoo.com/nascar/cup/2018/daytona-500/"

 

[Brad24]

Rats, I need position and car number, not names.

 

[MartyS]

Rats, I need position and car number, not names.

After much searching I found a site that could be what you're looking for...
The URL is contained in the spreadsheet... and is easy to put into your code.

Let me know how you get on...

https://www.dropbox.com/s/al58jfihpt7c11u/MyNascarWebscrape.xlsm?dl=0

 

[MartyS]

Forgot to mention: If you change the name of the workbook (or worksheet), you'll need to change the CONSTs in the code to match.

The workbook is currently named: MyNascarWebscrape.xlsm