Excel based tutorial for web scraping

**a4u2fear** · 03-14-14, 04:09 PM

Please keep in mind I'm a father of a young daughter so most of my posts will be on the weekends and my weekday time for gambling/coding/time to myself is usually less than an hour, unless I stay up late!

So let's get to it:

1) Open up Excel and make sure you enabled the Developer tab as I spoke of above.

2) You will notice the Developer tab amongst the Home/Insert/Page Layout etc tabs (in Excel 2007)

3) Once clicking the Developer tab, click the Visual Basic button on the tab

4) A new screen will pop-up for visual basic. Clicking the Insert Tab at the top, followed by module. We will be exclusively using modules in this tutorial for coding, but there are also things called userforms where you can create/modify graphical user interfaces or pop-ups with selections/tabs/photos/ you name it.

5) You will notice in the project toolbar on the far left side your module appeared called Module1. Let's write our first set of code: In visual basic, I mainly use things called "subs" and "functions". They both execute code but when functions are called, return a variable. Please do not hold me to speaking all the correct lingo when discussing these things, as I reference them as I know them and not as someone who vigorously studied them. You basically want to use a function when you want a value computed and returned. You could just call another sub instead of a function, but it would be considered poor coding. You'll understand further as we go along.

**a4u2fear** · 03-14-14, 04:17 PM

6) Most of you will want to web scrape for today's data as well as data that occurred in the past. So let's keep it simple.

Creating our first "sub", code is below. Subs/functions can be called public or private, for different reasons not worth discussing. As long as you call them all public, you will be fine.:

Public Sub MainCode()
'This sub will contain our main code and reference many other subs. This sub will "eventually" import a webpage!
Dim TodaysDate As Date 'TodaysDate is a variable

TodaysDate = Date
Call ImportData(TodaysDate)

End Sub

Public Sub ImportData(ImportDate)
'ImportDate will have the same value as TodaysDate, it is good to keep variables local to their subs
'We will eventually import data for the ImportDate

End Sub

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

omgg I don't feel like retyping but firefox just erased a ton I had just writtttttten. ugh.

Variables are written "Dim" variable "as" type. Where variable is the name you give your variable. It can be whatever you like, just do not include spaces, if you want to include a space, use This_is_myvariable. The type can be a number of things, like integer (1,2,3), double (40.39, 23.00), string aka text (hello, /, ', @, 1, 4) etc.

**a4u2fear** · 03-14-14, 04:37 PM

To run your code we just wrote, click the run tab at the top followed by run sub/userform. The code will execute. We haven't really done anything yet, but this is a good opportunity to show how to debug your code:

Next to your code (the left of it), you'll see a slender gray column. If you click in this gray column directly next to TodaysDate=Date, a red circle will appear and the code will be highlighted in red. This is a stopping point for the code when the code is run. If you click run sub/userform again, the code will stop at TodaysDate=Date and be highlighted yellow. No code has been calculated beyond this line. If you click F8 on your keyboard, this line will be executed and the next line will be stopped at. After clicking this F8, put your cursor over TodaysDate, you will notice it now says 3/14/2014.

Hitting F8 again you the Sub ImportData(ImportDate) will be highlighted. Putting your cursor over ImportDate you will notice it now also has the value of 3/14/2014. Another click of F8 will run to the code in the ImportData (there isn't any so it runs to the end) of End Sub. Clicking F8 again will bring you back to the MainCode sub and eventually End Sub and your code will finish executing.

Let's say you wanted to just see what TodaysDate value was because you were having issues. You could put the red stop point at TodaysDate as I did above. Click F8, see it's value (3/14/2014), and instead of hitting F8 a few more times, you could've hit F5 which would've just executed the rest of the code quickly. You can add many red stopping points.

**a4u2fear** · 03-14-14, 04:43 PM

That's all I have for today.

If you want to see how your code really executes, add this line in the MainCode. It can be entered above the TodaysDate line or below the Call ImportData line:

Sheets("Sheet1").Cells(1,1)="Coding!"

This will enter Coding in cells A1, or (1,1). (1,1)=(row,column)

You could've also entered this line into your code:

Dim mynumber as integer
mynumber=34 'if you enter 34.4 you may sometimes get an error or it will round. To use decimals use the double type
Sheets("Sheet1").Cells(1,1)=mynumber

In my next post we will discuss for loops and if statements

hopefully this simple bit can get you started.

**a4u2fear** · 03-14-14, 04:44 PM

Woops, forgot this, you can comment your code using '. Anything after ' will not be executed in code. If you use the mynumber=34 you can see i commented after it using '.

**a4u2fear** · 03-14-14, 04:45 PM

here is a small thread i started on this:

http://forum.sbrforum.com/hockey-betting/2207848-anyone-interested-pulling-historical-results-odds-sbr.html

I think I posted a screenshot of my excel database in it for you to get an idea.

**antonyp22** · 03-15-14, 02:27 AM

Great thread! Looking forward to learning a few things

**a4u2fear** · 03-15-14, 06:56 AM

Let's go over For loops and If statements. Both extremely useful. For loops, for example, work great when trying to import data.

The URL for an NHL game boxscore is http://www.nhl.com/ice/boxscore.htm?id=2013020001
This would be the boxscore for game 0001 of 2013. I don't know why the "02" is there, but it never changes and is there regardless of the season.

There are 1230 NHL games in a season (be careful, there were not this many in the 48 game shortened season in 12-13).

To use a for loop to import games from 11-12 (last full season):
You need to use a variable as a counter (from 1 to 1230 for importing all games)
we'll call this variable/counter gamenumber as it its appropriate name

This bit of code below will be added into the MainCode Sub, but for now I will just show the for loop:

Dim gamenumber as integer
For gamenumber=1 to 1230
'execute this code, eventually will be importing a webpage and performing code
Call ImportWebpage(gamenumber) ' this sub does not exist yet
Next

This for loop will call the sub ImportWebpage 1230 times, because you will be importing the boxscores from 1230 NHL games. The code in between For and Next will be executed 1230 times.

**a4u2fear** · 03-15-14, 07:02 AM

When importing the NHL's boxscores, we'll need to take the 2013020001 and split into "20" & year_of_import & "02" & gamenumber when importing, but that is to come.

Take note if you paste the for loop above in your code it will give you an error that the ImportWebpage sub does not exist. You can add it and see if you can get the code to work. I'll show it later.

Another simple for loop you can run to see how it works
Dim gamenumber as integer
For gamenumber = 1 to 25
Sheets("Sheet1").Cells(gamenumber,gamenu mber+1)=gamenumber
Next

Run this simple for loop and you will see the number 1 in B1, 2 in C2, 3 in D3 etc.

**a4u2fear** · 03-15-14, 07:07 AM

Sheets("Sheet1") can also be written Sheets(1)

If you change the name of "Sheet3" to "data", if you reference Sheets(3) or Sheets("data") they are the same. I always like referencing sheets by their name. But if you are combing through your sheets one at a time using a foor loop (i.e. Sheets(gamenumber)) that works better than referencing by name.

**a4u2fear** · 03-15-14, 07:16 AM

If statements (people also use case statements, but I like If statements best):

As i showed in the NHL website address above, we will need to use the year, and say you wanted to import multiple years worth of games at once, if statements work well:

we need to add another variable for total number of games in a season and another variable for year

Dim totalgames as integer 'i.e. 1230
Dim NHLyear as integer 'i.e. 12, two digit because the year in NHL's website is only two digits

For NHLyear=11 to 13

If NHLyear=12 then
totalgames=720 'shortened season!
else
totalgames=1230 '2011 and 2013 both were full seasons
end if

Next

**a4u2fear** · 03-15-14, 07:19 AM

The above could also be written:

Dim totalgames as integer 'i.e. 1230
Dim NHLyear as integer 'i.e. 12, two digit because the year in NHL's website is only two digits

For NHLyear=11 to 13

If NHLyear=12 then
totalgames=720 'shortened season!
elseif NHLyear=11 then
totalgames=1230
elseif NHLyear=13 then
totalgames=1230
end if

Next

Wanted to show it this way in case you needed an IF statement where all three Ifs had different outcomes

**a4u2fear** · 03-15-14, 07:21 AM

Now, let's add the If/for loop above with the other for loop where the webpage sub will be called for each game

Dim totalgames as integer 'i.e. 1230
Dim NHLyear as integer 'i.e. 12, two digit because the year in NHL's website is only two digits
Dim gamenumber as integer

For NHLyear=11 to 13

If NHLyear=12 then
totalgames=720 'shortened season!
else
totalgames=1230 '2011 and 2013 both were full seasons
end if

For gamenumber=1 to totalgames
'execute this code, eventually will be importing a webpage and performing code
Call ImportWebpage(gamenumber) ' this sub does not exist yet
Next

Next

**a4u2fear** · 03-15-14, 07:26 AM

It's hard to see what fors/ifs are inside another because SBR does not let use spaces/tabs, it just removes them

Dim totalgames as integer 'i.e. 1230
Dim NHLyear as integer 'i.e. 12, two digit because the year in NHL's website is only two digits
Dim gamenumber as integer

....For NHLyear=11 to 13

........If NHLyear=12 then
.............totalgames=720 'shortened season!
........else
.............totalgames=1230 '2011 and 2013 both were full seasons
........end if

........For gamenumber=1 to totalgames
............'execute this code, eventually will be importing a webpage and performing code
............Call ImportWebpage(gamenumber) ' this sub does not exist yet
........Next

....Next

Here I added periods to show what it would look like in VBA with tabs/spaces. It's easier this way to see what code is inside another piece of code. IF YOU PASTE THE ABOVE CODE INTO VBA WITH THE PERIODS IT WILL NOT WORK.

**a4u2fear** · 03-15-14, 07:28 AM

So, top to bottom the code will execute:
-The for loop will execute for NHLyear=11
-The if statement will execute and since NHLyear does not equal 12, totalgames=1230
-The next for loop will execute 1230 times from gamenumber 1 to totalgames(1230) and call the importwebpage 1230 times.
-After the 1230 webpages are imported, the code will go back to the first for loop, and NHLyear=12 and the process will execute again.

**a4u2fear** · 03-15-14, 01:49 PM

Tomorrow will import first web page

**Bluehorseshoe** · 03-16-14, 09:06 AM

Nice thread!

**a4u2fear** · 03-16-14, 01:13 PM

Originally posted by Bluehorseshoe

Nice thread!

Originally posted by antonyp22

Great thread! Looking forward to learning a few things

**a4u2fear** · 03-16-14, 01:17 PM

Another thing I love about Excel/VBA, is if you do not know how to do something or know what code to use, you can perform the action in Excel and have VBA tell you what the code is for it.

To do this, click on the developer tab, and in the tab ribbon select record a macro and then ok.

Any cells you select, highlight, change font, fill, etc will be recorded. Hit stop recording on the developer ribbon. And if you go into the Visual Basic screen, a new module will appear showing the code that goes along with the actions you performed in the cell area.

**a4u2fear** · 03-16-14, 01:31 PM

Public Sub MainCode()
'This is a comment. This sub will have all of our
'main code and will call other subs

'Below are my variables
Dim totalgames As Integer 'i.e. 1230
Dim NHLyear As Integer 'i.e. 12, two digit because the year in NHL's website is only two digits
Dim gamenumber As Integer

Application.ScreenUpdating = False

For NHLyear = 11 To 11

If NHLyear = 12 Then
totalgames = 720 'shortened season!
Else
totalgames = 1230 '2011 and 2013 both were full seasons
End If

For gamenumber = 1 To 1
Call ImportWebpage(gamenumber, NHLyear)
Call PullData
Next

Next

Application.ScreenUpdating = True

End Sub

Public Sub ImportWebpage(game, gameyear)
'this sub only imports the webpage
Dim gamestring As String

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

If game < 10 Then
gamestring = "000" & game
ElseIf game < 100 Then
gamestring = "00" & game
ElseIf game < 1000 Then
gamestring = "0" & game
Else
gamestring = game
End If

With Sheets("Sheet2").QueryTables.Add(Connect ion:= _
"URL;http://www.nhl.com/ice/boxscore.htm?id=20" & gameyear & "02" & gamestring, 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()
'this sub will pull data from the imported webpage and
'put into a main sheet which will contain all game data

End Sub

**a4u2fear** · 03-16-14, 01:33 PM

Above, I posted my entire code for the tutorial. It imports the first game of 2011-12 - the boxscore from NHL.com.

I specifically set NHL=11 to 11 and gamenumber 1 to 1 because you shouldn't blindly try to import the whole season before you know you have a good set of code to go on.

PLUS! The code I have above simply imports the webpage but does nothing with the data. You should look at the webpage you imported, see what you want to use and put into another sheet where you will store all of your information. I will eventually do this using the sub PullData, right now it is empty.

**a4u2fear** · 03-16-14, 01:34 PM

*******I forgot to mention earlier you should save your Excel file as a macro-based file when saving. When you click save it will ask you if you haven't done this already.

**a4u2fear** · 03-16-14, 01:37 PM

I found that it works best to delete a sheet and create a new one (as I did above with Sheet2). Whenever I tried to just delete the information on the sheet, it caused annoying errors. Sheet2 will be created and deleted for every webpage import.

Application.DisplayAlerts = False
Sheets("Sheet2").Delete
Application.DisplayAlerts = True

When you delete a sheet, it asks you are you sure you want to delete? The code above removes that alert, which we obviously don't care about.

**a4u2fear** · 03-16-14, 01:42 PM

Application.ScreenUpdating = False

By not enabling screen updating, the code will run faster. You will see importing many pages takes a while.

In the webpage sub, you will see I created a string called gamestring. I needed to do this because if I had added these 0s to the game variable which is an integer, adding 00 to 11 would still make game = 11 and it would not work. By using a string variable, which is text, turns the 11 variable to 0011. NHL.coms game numbers have four digits (because 1230 games) and now our string is the perfect length.

If you are feeling good about your code, go ahead and take a look at the page you imported and what you variables you want to move into your main sheet and start filling in code in PullData sub.

I'm too hungover from last night to do any more. Hopefully this gets some of you started

**b_rad_1983** · 03-16-14, 03:45 PM

I was able to run the post #22 code and it pulls in the data like you said but its so ugly.
How much work is involved in making in nice and cleaner?

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

Originally posted by b_rad_1983

I was able to run the post #22 code and it pulls in the data like you said but its so ugly.
How much work is involved in making in nice and cleaner?

Of course its ugly. All we did was import the page. Without importing the page you cannot grab the data. When I get the time over the next day or so I will show how to put the information into the main sheet. It's not a big deal

**b_rad_1983** · 03-16-14, 04:33 PM

Okay in the meantime I will review each line to understand it more.

What I did for this NHL season was use google sheets and they have a nice importhtml feature that I could load all the games in from nhlref... Problem I found is once the site loses the data, so do I. Because each line is continues from the last line which referes to the page.

**b_rad_1983** · 03-17-14, 01:19 AM

I was playing around practicing, but ran into some issues.

I modified post 22. To import NHL stats.
From there I made changes to the macro to make it look nice (delete the junk)

Are you able to quickly make the same thing but simplified? This is my first attempt with alot of clutter in the code.

What i'm trying to accomplish is to,
Import a stats table from the NHL site
Clean it up so all i have is the table
Rename the sheet to today's date while making sure the code can only get executed once per day.

I noticed there stuff from post 22 I do not need in the modified. I tried deleting some stuff and it would not do anything.

Its late, I need sleep!
Thanks

Modified code below.......

Public Sub MainCode()
'This is a comment. This sub will have all of our
'main code and will call other subs

'Below are my variables
Dim totalgames As Integer 'i.e. 1230
Dim NHLyear As Integer 'i.e. 12, two digit because the year in NHL's website is only two digits
Dim gamenumber As Integer

Application.ScreenUpdating = False
Call ImportWebpage(gamenumber, NHLyear)
Application.ScreenUpdating = True

End Sub

Public Sub ImportWebpage(game, gameyear)
'this sub only imports the webpage
Dim gamestring As String

'ActiveWindow.SelectedSheets.Delete
'Sheets("Sheet2").Delete
Application.DisplayAlerts = True
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Sheet2"

With Sheets("Sheet2").QueryTables.Add(Connect ion:= _
"URL;http://www.nhl.com/ice/teamstats.htm?navid=nav-sts-teams#" & gameyear & "02" & gamestring, 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

'Clean up

Rows("1:189").Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-12
Range("A4").Select
Columns("A:A").ColumnWidth = 17.29
ActiveWindow.SmallScroll Down:=51
Rows("61:68").Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-72
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Columns("B:B").ColumnWidth = 18.86
Range("A3").Select
ActiveCell.FormulaR1C1 = ""
Range("A4").Select

'Renames To Todays Date

ActiveSheet.Name = Format(Now(), "dd mmm yyyy")

End Sub

**b_rad_1983** · 03-17-14, 11:11 AM

I think I got it!
When I run the code it looks right.

Public Sub MainCode()

'Erase old data and make a new sheet

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

'Loads in the table with alot of useless stuff

With Sheets("Current").QueryTables.Add(Connec tion:= _
"URL;http://www.nhl.com/ice/teamstats.htm?navid=nav-sts-teams#", 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

'Cleans up all the useless stuff

Rows("1:189").Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-12
Range("A4").Select
Columns("A:A").ColumnWidth = 17.29
ActiveWindow.SmallScroll Down:=51
Rows("61:68").Select
Selection.Delete Shift:=xlUp
ActiveWindow.SmallScroll Down:=-72
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Columns("B:B").ColumnWidth = 18.86
Range("A3").Select
ActiveCell.FormulaR1C1 = ""
Range("A4").Select

End Sub

**b_rad_1983** · 03-17-14, 12:24 PM

I was playing with this more and I have a sheet called "Test1" which is always before my "Current" sheet. Because the above code deletes/creates then re updated from the website. In "Test1" i'm using

=Current!A1

But once I run the code it goes to

=#ref!A1

I'm assuming because i'm deleting it then re-creating.

I'm once again stuck

**a4u2fear** · 03-17-14, 03:44 PM

b_rad, I don't suggest deleting information in your imported webpage because the length of some games differs and what may be 189 rows of information, may be 212 the next time and you will run into issues. That is why I have coded this webpage to be deleted before new information is imported.

I will update my code in a bit to show you what I do with the data.

**b_rad_1983** · 03-17-14, 04:06 PM

Okay. Good news! I'll be waiting

**a4u2fear** · 03-17-14, 04:17 PM

everyone is different, but to show you the example, I wanted to import the goals per period from the imported webpage.

Sheet1 is where I am placing all of my data and will be my main sheet
Cell A1 is Game
Cell B1 is Away Team
Cell C1 is Away team 1P goals
Cell D1 is Away team 2p goals
E1 is away team 3p goals
F1 is away OT goals
G1 is away total goals
H1 is home team
I1 is home 1p goals
J1 is home 2p goals
K1 is home 3p goals
L1 is home ot goals
M1 is home total goals

These cells will always have these headers and we will populate the data below them