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
Pos	Driver	Car Make	Total Pts	Bonus	Laps	Laps Led	Qualifying Speed
Brad Keselowski	Ford
Joey Logano	Ford
Kurt Busch	Ford
Ryan Blaney	Ford
Austin Dillon	Chevrolet
Denny Hamlin	Toyota
Kyle Busch	Toyota
Erik Jones	Toyota
Kevin Harvick	Ford
Kyle Larson	Chevrolet
Ryan Newman	Chevrolet
Jimmie Johnson	Chevrolet
Chase Elliott	Chevrolet
Martin Truex Jr.	Toyota
Kasey Kahne	Chevrolet
Ricky Stenhouse Jr.	Ford
Jamie McMurray	Chevrolet
A. Dillon
D. Wallace Jr.
D. Hamlin
J. Logano
C. Buescher
P. Menard
R. Blaney
R. Newman
M. McDowell
A. Allmendinger

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]78[/TD]

[TD="align: center"]79[/TD]
[TD="align: right"]1[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]80[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]81[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]82[/TD]
[TD="align: right"]4[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]83[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]84[/TD]
[TD="align: right"]6[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]85[/TD]
[TD="align: right"]7[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]86[/TD]
[TD="align: right"]8[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]87[/TD]
[TD="align: right"]9[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]88[/TD]
[TD="align: right"]10[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]89[/TD]
[TD="align: right"]11[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]90[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]91[/TD]
[TD="align: right"]13[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]92[/TD]
[TD="align: right"]14[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]93[/TD]
[TD="align: right"]15[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]94[/TD]
[TD="align: right"]16[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]95[/TD]
[TD="align: right"]17[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]96[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]97[/TD]
[TD="align: right"][/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]98[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]99[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]100[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]101[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]102[/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]103[/TD]
[TD="align: right"][/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]104[/TD]
[TD="align: right"][/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]105[/TD]
[TD="align: right"][/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]106[/TD]
[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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
Pos	Driver	Status
B. Keselowski	Running
Ford·#2
J. Logano	Running
Ford·#22
K. Busch	Running
Ford·#41
R. Blaney	Running
Ford·#12
A. Dillon	Running
Chevrolet·#3
D. Hamlin	Running
Toyota·#11
K. Busch	Running
Toyota·#18
E. Jones	Running
Toyota·#20
K. Harvick	Running
Ford·#4
K. Larson	Running
Chevrolet·#42
R. Newman	Running
Chevrolet·#31
J. Johnson	Running
Chevrolet·#48
C. Elliott	Running
Chevrolet·#9
M. Truex Jr.	Running
Toyota·#78
K. Kahne	Running
Chevrolet·#95
R. Stenhouse Jr.	Running
Ford·#17
J. McMurray	DNF
Chevrolet·#1

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]103[/TD]

[TD="align: center"]104[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]105[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]106[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]107[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]108[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]109[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]110[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]111[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]112[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]113[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]114[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]115[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]116[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]117[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]118[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]119[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]120[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]121[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]122[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]123[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]124[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]125[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]126[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]127[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]128[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]129[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]130[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]131[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]132[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]133[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]134[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]135[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]136[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]137[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

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