1. #1
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    Excel based tutorial for web scraping

    Going to do my best at giving an Excel based tutorial for those interested.

    I am an electrical engineering graduate with both my masters and bachelors in EE. Throughout my studies, I've taken a few different software based courses in java, C++, matlab, and VBA. Since I don't code much for my job, I've found the easiest to forget/remember/ and use is VBA. Visual Basic/VBA is the coding software inside of Microsoft Excel.

    If you haven't ever done so, you will need to enable the "developer tab" in Excel. I have 2007 and to enable it: click the microsoft symbol at the very top left with excel open, and when a tab opens up, click Excel options. Another tab will open and under the "Popular" tab, click "Show Developer Tab" and then ok. If you have another version of Excel, do a quick research on google and someone will show you how to enable it.

    I have not tried some of the other options listed such as Python etc so I cannot comment on those. I have been using VBA/Excel to do all of my web scraping for going on three years and have never been "crippled" by it; other than the fact you cannot easily pull data from ".php" or similar type websites.

    My main focuses are both the NHL and NFL and I have been going through yearrrrrss of data for both and have parsed through quite of bit of ideas/trends/you name it. I've tried to debunk or find truth in current myths etc in sports to see if they made sense or didn't. For example, once I had a good database in the NHL, I wanted to see if teams on games in B2B days really suffered in their second game - I really didn't find much truth to this, and if there was, it was already calculated in the game line.

    I have scraped data from NHL.com/ESPN/SBR as my main webpages.

    Ask any questions you may have as I am an "expert" on VBA and don't proclaim to know it all, but have learned quite a bit over the years. I will try to keep the tutorial as detailed or as ordinary as need be for users of all types.
    Points Awarded:

    nash13 gave a4u2fear 2 Betpoint(s) for this post.

    jonal gave a4u2fear 2 Betpoint(s) for this post.

    Glitch gave a4u2fear 2 Betpoint(s) for this post.

    zeeresa gave a4u2fear 1 Betpoint(s) for this post.

    Nomination(s):
    This post was nominated 1 time . To view the nominated thread please click here. People who nominated: zeeresa

  2. #2
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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.

  3. #3
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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

  4. #4
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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.

  5. #5
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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.

  6. #6
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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.

  7. #7
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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 '.

  8. #8
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    here is a small thread i started on this:
    http://www.sportsbookreview.com/forum/hockey-bet...-odds-sbr.html

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

  9. #9
    antonyp22
    antonyp22's Avatar Become A Pro!
    Join Date: 01-12-14
    Posts: 78
    Betpoints: 2528

    Great thread! Looking forward to learning a few things

  10. #10
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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.

  11. #11
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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,gamenumber+1)=gamenum ber
    Next

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

  12. #12
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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.

  13. #13
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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

  14. #14
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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

  15. #15
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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

  16. #16
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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.

  17. #17
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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.

  18. #18
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    Tomorrow will import first web page

  19. #19
    Bluehorseshoe
    Bluehorseshoe's Avatar Become A Pro!
    Join Date: 07-13-06
    Posts: 13,908
    Betpoints: 66

    Nice thread!

  20. #20
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    Quote Originally Posted by Bluehorseshoe View Post
    Nice thread!
    Quote Originally Posted by antonyp22 View Post
    Great thread! Looking forward to learning a few things

  21. #21
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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.

  22. #22
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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(Connection:= _
    "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
    Last edited by a4u2fear; 03-16-14 at 01:37 PM.

  23. #23
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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.

  24. #24
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    *******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.

  25. #25
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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.

  26. #26
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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

  27. #27
    b_rad_1983
    b_rad_1983's Avatar Become A Pro!
    Join Date: 01-07-13
    Posts: 127
    Betpoints: 625

    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?

  28. #28
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    Quote Originally Posted by b_rad_1983 View Post
    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

  29. #29
    b_rad_1983
    b_rad_1983's Avatar Become A Pro!
    Join Date: 01-07-13
    Posts: 127
    Betpoints: 625

    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.

  30. #30
    b_rad_1983
    b_rad_1983's Avatar Become A Pro!
    Join Date: 01-07-13
    Posts: 127
    Betpoints: 625

    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(Connection:= _
    "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

  31. #31
    b_rad_1983
    b_rad_1983's Avatar Become A Pro!
    Join Date: 01-07-13
    Posts: 127
    Betpoints: 625

    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(Connection:= _
    "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

  32. #32
    b_rad_1983
    b_rad_1983's Avatar Become A Pro!
    Join Date: 01-07-13
    Posts: 127
    Betpoints: 625

    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

  33. #33
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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.

  34. #34
    b_rad_1983
    b_rad_1983's Avatar Become A Pro!
    Join Date: 01-07-13
    Posts: 127
    Betpoints: 625

    Okay. Good news! I'll be waiting

  35. #35
    a4u2fear
    TEASE IT
    a4u2fear's Avatar SBR PRO
    Join Date: 01-29-10
    Posts: 8,030
    Betpoints: 13672

    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

1234 ... Last
Top