Excel based tutorial for web scraping

**b_rad_1983** · 03-19-14, 05:03 PM

I pulled in this table

http://www.nhl.com/ice/standings.htm?season=20132014&type=LEA

From there I used the method from your example **'away team statsSheets("Sheet1").Cells(lastrowsheetone + 1, 2) = Sheets("Sheet2").Cells(findmatch + 1, 1)**

To copy the place,team,gf,ga

Something like this

1

ST. LOUIS

226

152

Won 3

I now need to add a loop to get the same thing 30-31 times.

This can all be done after we are completed with your example.

**b_rad_1983** · 03-19-14, 05:07 PM

so far when I run the pull data code it keeps giving me the same thing. I need to add a loop or something to tell the code to move down a next line until place = 30

**a4u2fear** · 03-19-14, 05:19 PM

i quickly imported your page, here's what i would do:

findmatch = Application.Match("DIV", Sheets("Sheet2").Range("C1:C300"), 0)

for x=1 to 30 '30 teams

lastrowsheetone = Sheets("Sheet1").UsedRange.Rows.Count
Sheets("Sheet1").Cells(lastrowsheetone + 1, 2) = Sheets("Sheet2").Cells(findmatch + x, 10) 'this is GF
Sheets("Sheet1").Cells(lastrowsheetone + 1, 3) = Sheets("Sheet2").Cells(findmatch + x,11) 'this is GA
Sheets("Sheet1").Cells(lastrowsheetone + 1, 4) = Sheets("Sheet2").Cells(findmatch + x, 17) 'this is w/l streak

Next

**a4u2fear** · 03-19-14, 05:22 PM

obviously, you will need to change the 2,3,4 in sheet1 to whatever column you want the data in.

DIV is in row 195 here and findmatch=195

195+1 will give st louis row for x=1
195+2 will give 2nd team etc to x=30

you need to recalculate lastrow with each iteration of x because you are adding a new line.

Sorry this is shorthand, I'm busy this week and I'm going on business travel next week. So I'll be limited. Hopefully this helps

**b_rad_1983** · 03-19-14, 05:22 PM

Great! thanks.

But whats the next lesson for your example database?

**a4u2fear** · 03-19-14, 05:23 PM

after i imported the webpage, the GF/GA was in columns 10 and 11, column 17 or Q was win streak.

the 2,3,4 i mention above needs to be changed to whatever columns you want it in in sheet 1

**b_rad_1983** · 03-19-14, 05:23 PM

Everything helps!

**a4u2fear** · 03-19-14, 05:33 PM

It's hard, I was coming up with examples but they turned out to be difficult.

I guess if people posted what they wanted to do a few times we could them involved and different examples bring different hardships and learning examples.

**b_rad_1983** · 03-19-14, 05:37 PM

Teach us more

**b_rad_1983** · 03-19-14, 08:53 PM

Originally posted by a4u2fear

i quickly imported your page, here's what i would do:

findmatch = Application.Match("DIV", Sheets("Sheet2").Range("C1:C300"), 0)

for x=1 to 30 '30 teams

lastrowsheetone = Sheets("Sheet1").UsedRange.Rows.Count
Sheets("Sheet1").Cells(lastrowsheetone + 1, 2) = Sheets("Sheet2").Cells(findmatch + x, 10) 'this is GF
Sheets("Sheet1").Cells(lastrowsheetone + 1, 3) = Sheets("Sheet2").Cells(findmatch + x,11) 'this is GA
Sheets("Sheet1").Cells(lastrowsheetone + 1, 4) = Sheets("Sheet2").Cells(findmatch + x, 17) 'this is w/l streak

Next

is there a way I can use the loop to output all the titles then fill down the 30 teams?

incase I want to use more data than the GF,GA,w/l streak

Maybe I would want to have it all

**b_rad_1983** · 03-19-14, 08:57 PM

I had it scrolling through all the games but I was missing a way to tell it to switch lines after it was done.
so it would overwrite 1-30 (teams x 17 stats) on the first row

**a4u2fear** · 03-19-14, 10:41 PM

Brad the titles never change, just copy paste the titles over.

If you want more data than the three I posted then you need to either add a new line similar to those above for each stat you want or you can add another for loop inside the for loop

**b_rad_1983** · 03-19-14, 11:51 PM

Im so close, im missing something small..

Public Sub PullData()
'this sub will pull data from the imported webpage and
'put into a main sheet which will contain all game data
Dim lastrowsheetone As Integer
Dim findmatch As Integer

lastrowsheetone = Sheets("Sheet1").UsedRange.Rows.Count
findmatch = Application.Match("DIV", Sheets("Sheet2").Range("C1:C300"), 0)

For x = 1 To 30 '30 teams
For Y = 1 To 17 '17 Titles
lastrowsheetone = Sheets("Sheet1").UsedRange.Rows.Count
Sheets("Sheet1").Cells(lastrowsheetone + x - 1, Y) = Sheets("Sheet2").Cells(findmatch + x, Y) 'this is Everything

'Sheets("Sheet1").Cells(lastrowsheetone + 1, 1) = Sheets("Sheet2").Cells(findmatch + x, 1) 'this is Place
'Sheets("Sheet1").Cells(lastrowsheetone + 1, 2) = Sheets("Sheet2").Cells(findmatch + x, 2) 'this is Team
'Sheets("Sheet1").Cells(lastrowsheetone + 1, 3) = Sheets("Sheet2").Cells(findmatch + x, 10) 'this is GF
'Sheets("Sheet1").Cells(lastrowsheetone + 1, 4) = Sheets("Sheet2").Cells(findmatch + x, 11) 'this is GA
'Sheets("Sheet1").Cells(lastrowsheetone + 1, 5) = Sheets("Sheet2").Cells(findmatch + x, 17) 'this is w/l streak

Next
Next

End Sub

**a4u2fear** · 03-20-14, 05:46 AM

You don't need first lastrowsheet1, it gets replaced by second one

I think your issue is the sheets (....,Y)

This would work fine if the data started in column 1 and went through 17, does it? I thought I remember it starting in different cell but I'm at work and don't know

**mark49** · 03-20-14, 06:20 AM

Hi, sorry to interupt the thread a4u2fear.
Its really good of you to take the time to explain this stuff and I have learned a lot already.

I just wondered if you have had any success with this method scraping NFL as the URL is always made up of either the date of a specific game or some combination involving the date and team names instead of the neater game numbers in your NHL examples?

**a4u2fear** · 03-20-14, 06:33 AM

Mark, yes I have successfully done the NFL. What specific website are you looking at? Post it and we can discuss

**mark49** · 03-20-14, 09:58 AM

I have been looking at several, for example

ESPN http://scores.espn.go.com/nfl/boxscore?gameId=330905007 which looks like 33 means 2013 0905 is the date and 007 which doesn't seem to follow any pattern.

Profootball reference is similar http://www.pro-football-reference.co...1309050den.htm it has the date and abreviation of the home team.

I think I might be able to figure out covers.com

covers.com/pageLoader/pageLoader.aspx?page=/data/nfl/results/2013-2014/boxscore37592.html

which looks like it might use sequential game numbers, I will have a go at changing the code and see how it goes,

Thanks again for helping us out.

**a4u2fear** · 03-20-14, 10:23 AM

Ahhh now I remember. Yes ESPN whether it's NFL or NHL is a bitch. Instead of going game 1 thru X, game 1 could be 1 then game 2 could be 22 etc. the last two to three digits is the game.

To to get around this, imported the schedule of dates of games. In your example above, I think 0905 is September 05. Using the NFL schedule I would know there are a certain number of games for that day. Then for the last two digits of the URL, I would loop from 1 to 60. This is knowing that a lot of those game numbers would be invalid and skipped, but eventually you would find the games you need. You'll notice this if you just go to a week in the NFL and open some box scores.

I have not looked at PFF but it looks similar to NFL. We can work thru the code on an ESPN site. It will be difficult for most to understand but it's a mainstream website

also I'm on business travel next week and my wife is tying me up most of this week so my time in this thread will be limited

**mark49** · 03-20-14, 02:50 PM

I managed to change the code to take the score for each quarter for last seasons NFL games, unfortunatley it only seems to get the scores for the 1st & 2nd Qtrs and I can't understand why (well probably because I dont really understand what i'm doing)

Public Sub MainCode()

Dim totalgames As Integer
Dim gamenumber As Integer

Application.ScreenUpdating = False

For gamenumber = 592 To 595
Call ImportWebpage(gamenumber)
Call PullData
Next

Application.ScreenUpdating = True

End Sub

Public Sub ImportWebpage(gamenumber)

Application.DisplayAlerts = False
Sheets("Sheet3").Delete
Application.DisplayAlerts = True
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Sheet3"

With Sheets("Sheet3").QueryTables.Add(Connect ion:= _
"URL;http://www.covers.com/pageLoader/pageLoader.aspx?page=/data/nfl/results/2013-2014/boxscore37" & gamenumber & ".html", Destination:= _
Range("$A$1"))
.Name = ""
.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

End Sub

Public Sub PullData()

Dim lastrowsheetone As Integer
Dim findmatch As Integer

lastrowsheetone = Sheets("Sheet1").UsedRange.Rows.Count
findmatch = Application.Match("Final ", Sheets("Sheet3").Range("B1:B500"), 0)

'away team stats
Sheets("Sheet1").Cells(lastrowsheetone + 1, 2) = Sheets("Sheet3").Cells(findmatch + 1, 1)
Sheets("Sheet1").Cells(lastrowsheetone + 1, 3) = Sheets("Sheet3").Cells(findmatch + 1, 2)
Sheets("Sheet1").Cells(lastrowsheetone + 1, 4) = Sheets("Sheet3").Cells(findmatch + 1, 3)
Sheets("Sheet1").Cells(lastrowsheetone + 1, 5) = Sheets("Sheet3").Cells(findmatch + 1, 4)

'home team stats
Sheets("Sheet1").Cells(lastrowsheetone + 1, 8) = Sheets("Sheet3").Cells(findmatch + 2, 1)
Sheets("Sheet1").Cells(lastrowsheetone + 1, 9) = Sheets("Sheet3").Cells(findmatch + 2, 2)
Sheets("Sheet1").Cells(lastrowsheetone + 1, 10) = Sheets("Sheet3").Cells(findmatch + 2, 3)
Sheets("Sheet1").Cells(lastrowsheetone + 1, 11) = Sheets("Sheet3").Cells(findmatch + 2, 4)

End Sub

This is set for the first five games of the 2013-2014 season from covers and like yours assumes the 1st row of sheet1 is filled, sort of half works, could you tell me what I have done wrong ? Thanks for any help

**mark49** · 03-20-14, 03:12 PM

Sorry, please ignore that. I have changed the PullData to this instead and it works fine now

'away team stats
Sheets("Sheet1").Cells(lastrowsheetone + 1, 2) = Sheets("Sheet3").Cells(findmatch + 1, 2)
Sheets("Sheet1").Cells(lastrowsheetone + 1, 3) = Sheets("Sheet3").Cells(findmatch + 1, 3)
Sheets("Sheet1").Cells(lastrowsheetone + 1, 4) = Sheets("Sheet3").Cells(findmatch + 1, 4)
Sheets("Sheet1").Cells(lastrowsheetone + 1, 5) = Sheets("Sheet3").Cells(findmatch + 1, 5)
Sheets("Sheet1").Cells(lastrowsheetone + 1, 6) = Sheets("Sheet3").Cells(findmatch + 1, 6)

'home team stats
Sheets("Sheet1").Cells(lastrowsheetone + 1, 8) = Sheets("Sheet3").Cells(findmatch + 2, 2)
Sheets("Sheet1").Cells(lastrowsheetone + 1, 9) = Sheets("Sheet3").Cells(findmatch + 2, 3)
Sheets("Sheet1").Cells(lastrowsheetone + 1, 10) = Sheets("Sheet3").Cells(findmatch + 2, 4)
Sheets("Sheet1").Cells(lastrowsheetone + 1, 11) = Sheets("Sheet3").Cells(findmatch + 2, 5)
Sheets("Sheet1").Cells(lastrowsheetone + 1, 12) = Sheets("Sheet3").Cells(findmatch + 2, 6)

I wrongly assumed the last number of each line meant row distance from the 'findmatch' instead of the actual row number, doh!

**a4u2fear** · 03-20-14, 03:49 PM

glad its working for you Mark. Always a good way to learn, is to go back thru my posts about debugging, and follow what's happening.

Even if you can minimize the Excel window and the visual basic window so you can view both simulatneously, and follow the code/what is placed into Excel.

**a4u2fear** · 03-20-14, 03:58 PM

Now that I'm on a computer and it's easier to type. I wanted to explain the ESPN game # better.

Say all 32 teams are playing in a given week, there are 16 games. But, some are played on Mon/Thur/Sat/Sun. So let's say you are just trying to import a Sunday's worth of games, the normal amount would be 14 (1 on Mon/1 on Thur, 14 on Sunday).

the URL below is a typical NFL boxscore from ESPN:

Broncos 49-27 Ravens (Sep 5, 2013) Box Score - ESPN

http://scores.espn.go.com/nfl/boxscore?gameId=330905007

Box score for the Denver Broncos vs. Baltimore Ravens NFL game from September 5, 2013 on ESPN. Includes all passing, rushing and receiving stats.

33 is 2013 (this is a weird one, 32 is 2012 etc)
09 is September or Month
05 is day of Month.
007 is the game number

so, you would expect the game number to be from 001 to 014. But that is not the case!

the game numbers for week 1 of 2012 are 3,5,7,8,9,10,12,16,18,19,20,22,27,34 (I may have messed one or two up), but you can see that there is no pattern or reason. I have never figured out ESPN's way of numbering the games. But I have created an error trap to ignore the import if the game does not exist.

**mark49** · 03-20-14, 04:25 PM

Yes, ESPN looks a bit much for me as a total novice, lot of variables to negotiate.

At the moment I am making some progress with covers and if I can overcome the problem of OT I think I am well on the way.
If a game finishes in OT then the game total, odds & bet total columns move one column right to make room for the OT (on covers it is X) column.

Looks like I had better start learning some IF statements if I am to work round it !

**a4u2fear** · 03-20-14, 04:31 PM

I'm sure some don't know, but you can do many for loops inside of for loops and many if statements inside of if statements and both in each other.

Easy example. From prior statements, we've combined teams playing in a game using =B2 & H2 (gives PHIBOS). say this is in column N and you wanted to use your code to search column N for a team and see if they are playing.

Dim team as string
team="PHI"
gamesplayed=0
For x=2 to 30 'want to search just 30 rows here, can be more/less, data starts in row 2 (row 1 is headings)

if Instr(sheets("Sheet1").cells(x,14),team) > 0 then
'14 = column N, x is row, instr command looks for team ("PHI") in the cell (x,14)
'remember the first game is PHIBOS, so the instr command will return an integer=1
gamesplayed=gamesplayed+1
end if

Next

**a4u2fear** · 03-20-14, 04:33 PM

if the team name is not contained in the cells(x,14), it will return 0.

If the team is contained in the cells(x,14), it is =1, which is >0 and gamesplayed=gamesplayed+1.

You could possibly want to use this information to see how many games a team has played up to a certain matchup.

**a4u2fear** · 03-20-14, 04:36 PM

Now you say, that works great, I can find all of "PHI" games, but theres 29 other teams. That brings us to a thing called Arrays.

Arrays can be multi-dimensional and can contain mannnnnnny values/variables whatever

What you would want to do, is create an array of all teams in the NHL

Dim arrayteams(30) as string 'array can hold 0 to 30 values of strings (team names)

you need to set each array term

arrayteams(1)="BOS"
arrayteams(2)="PHI"
....arrayteams(30)="TB" 'you can fill in the other 27 teams i skipped over

**a4u2fear** · 03-20-14, 04:40 PM

how does this change the previous example i showed? here's the code, with 27 of the teams skipped (you need to fill in):

Dim arrayteams(30) as string
arrayteams(1)="BOS"
arrayteams(2)="PHI"
arrayteams(30)="TB"
'this may seem exahustive to write out all 30, but once you hve it it will save time and can be copied from one piece of code to another
Dim team as string

for y=1 to 30 'this variable y will be for each team
team=arrayteams(y) 'this is our current team we are searching thru the data to see if they played

gamesplayed=0 'reset this variable to 0 for each team

For x=2 to 30 'want to search just 30 rows here (for example i would have 30 games of data), data starts in row 2 (row 1 is headings)

if Instr(sheets("Sheet1").cells(x,14),team) > 0 then
'14 = column N, x is row, instr command looks for team ("PHI") in the cell (x,14)
'remember the first game is PHIBOS, so the instr command will return an integer=1
gamesplayed=gamesplayed+1
end if

Next

Next

**a4u2fear** · 03-20-14, 04:42 PM

so the above code will go thru the first for loop and team=arrayteams(1)="PHI"

gamesplayed=0

for all rows of data we have (2 thru 30), will go thru each row to see if PHI has played and if they did, gamesplayed=gamesplayed+1

i DID NOT do anything with the variable gamesplayed, but you would obvoiusly want to display this information somewhere.

After going thru all 2 thru 30 rows, team=arrayteams(2)="BOS" and so on, all thru arrayteams(30).

**a4u2fear** · 03-20-14, 04:48 PM

arrays can be multi-dimensional as i said. So we could've written

Dim arrayteams (30,2) , 30 rows, 2 columns

So you could set the first column to the team names as we did above

arrayteams(1,1)="PHI"
arrayteams(2,1)="BOS"
arrayteams(30,1)="TB"

and maybe use the second column to track games played

so as I spoke earlier, i didnt do anything with gamesplayed, but you could do this with the code:

Dim arrayteams(30,2) as variant ' setting this to variant means it can be integers and strings or any combo
arrayteams(1,1)="BOS"
arrayteams(2,1)="PHI"
arrayteams(30,1)="TB"
'you only need to do teams for the 1st column, leave 2nd column as nothing for now, we will set it to gamesplayed

for y=1 to 30 'this variable y will be for each team
team=arrayteams(y,1) 'this is our current team we are searching thru the data to see if they played

gamesplayed=0 'reset this variable to 0 for each team

For x=2 to 30 'want to search just 30 rows here (for example i would have 30 games of data), data starts in row 2 (row 1 is headings)

if Instr(sheets("Sheet1").cells(x,14),team) > 0 then
'14 = column N, x is row, instr command looks for team ("PHI") in the cell (x,14)
'remember the first game is PHIBOS, so the instr command will return an integer=1
gamesplayed=gamesplayed+1
end if

Next

arrayteams(y,2)=gamesplayed

Next

**a4u2fear** · 03-20-14, 04:48 PM

see what i did there? at the very end after the 30 rows are gone thru, i set the arrayteams(y,2)=gamesplayed

hopefully you could follow the progression here for arrays and how the code changed.

**a4u2fear** · 03-20-14, 04:50 PM

so if you called the variable, arrayteams(30,1), you would get TB, and if you called arrayteams(30,2) you would get an integer that is the number of gamesplayed TB had, i.e. 3 in this data

**b_rad_1983** · 03-21-14, 03:02 AM

Lots of stuff to learn, We will be busy while you are gone next week!

**b_rad_1983** · 03-21-14, 03:39 AM

Originally posted by a4u2fear

You don't need first lastrowsheet1, it gets replaced by second one

I think your issue is the sheets (....,Y)

This would work fine if the data started in column 1 and went through 17, does it? I thought I remember it starting in different cell but I'm at work and don't know

I Got it! This will pull in the entire table using loops. (NHLStandings)

Public Sub PullData()

Dim lastrowsheetone As Integer
Dim findmatch As Integer

lastrowsheetone = Sheets("Sheet1").UsedRange.Rows.Count
findmatch = Application.Match("DIV", Sheets("Sheet2").Range("C1:C300"), 0)

For x = 1 To 30 '30 teams
For Y = 1 To 17 '17 Titles

Sheets("Sheet1").Cells(lastrowsheetone + x - 1, Y) = Sheets("Sheet2").Cells(findmatch + x - 1, Y) 'this is Everything

Next
Next
End Sub

**b_rad_1983** · 03-21-14, 08:23 PM

For some odd reason every once in a while when i run the pull data code, it puts it in a different spot, sometimes after the previous information in the sheet. Is there a way to prevent something like this from happening? like a safety line at the beginning or something

**b_rad_1983** · 03-21-14, 08:23 PM

Mark, did you get everything sorted out?