Help when scraping from covers

[rfr3sh]

figured it out nvm

[Borat38]

How? I'm currently scraping everything manually, finished one season of NBA in 2 days. Still too much work to do, because I bet on 6 other sports.

[Flying Dutchman]

you guys interested in sharing? We can spread the pain, and shorten the data build up time to allow for more capping.

[gameday10]

I'm interested also. Just need teams, half scores and closing line. I'm currenty using covers also but having to look day by day at scores. Would love to scrape it and possible write something for what I'm doing instea of doing it all by hand.

[rfr3sh]

well I'm kind of cheating
but its still not to bad
just go to the previous scores and select the year you want to scrape...then highlight the table of data copy it,paste special in excel and select unicode

[Maverick22]

What the think tank needs is a resident programmer for hire.

I sure could have used one.. Still do.

[Borat38]

well I'm kind of cheating
but its still not to bad
just go to the previous scores and select the year you want to scrape...then highlight the table of data copy it,paste special in excel and select unicode

That is how I'm doing it. How long did it take you to finish an entire NBA season? My Excel sheets are laid out exactly in the Covers.com format.

Maybe we guys can help each other out by farming out one season of each sport to one person. Two people would be needed for NCAAB and NCAAF; there's too many teams and games every season as compared to the NBA.

[Borat38]

Forgot to mention: I'm intent on putting every season from 2006 to the present from the ff: NBA, WNBA, NFL, CFL, NCAAB, NCAAF, NHL.

[Wrecktangle]

On many for these same sports I have dbs that extend much further back than 2006 or so. If you are interested in cross-compairing or swapping, let me know.

[rfr3sh]

I stopped collecting these sports. going to focus on a small market for a chance to win

[uva3021]

Here, this is an excel macro that grabs the NFL box scores from covers, copy and paste to excel VBA and run

[COLOR=#200080][B]Sub[/B][/COLOR] NFLbox_Covers[COLOR=#308080]([/COLOR][COLOR=#308080])[/COLOR]
[COLOR=#595979]'[/COLOR]
[COLOR=#595979]'This code grabs the NFL box scores from Covers starting from the first game in Week 1 through Week 7[/COLOR]
[COLOR=#595979]'[/COLOR]

[COLOR=#595979]'[/COLOR]
[COLOR=#200080][B]On[/B][/COLOR] [COLOR=#200080][B]Error[/B][/COLOR] [COLOR=#200080][B]Resume[/B][/COLOR] [COLOR=#200080][B]Next[/B][/COLOR]
[COLOR=#200080][B]Dim[/B][/COLOR] numend [COLOR=#200080][B]As[/B][/COLOR] [COLOR=#200080][B]Long[/B][/COLOR]
[COLOR=#200080][B]Dim[/B][/COLOR] numstart [COLOR=#200080][B]As[/B][/COLOR] [COLOR=#200080][B]Long[/B][/COLOR]

[COLOR=#200080][B]Dim[/B][/COLOR] n [COLOR=#200080][B]As[/B][/COLOR] [COLOR=#200080][B]Long[/B][/COLOR]
Application[COLOR=#008c00].[/COLOR]ScreenUpdating [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]False[/COLOR]
Application[COLOR=#008c00].[/COLOR]Calculation [COLOR=#308080]=[/COLOR] xlCalculationManual
 
numend [COLOR=#308080]=[/COLOR] [COLOR=#008c00]27894[/COLOR] [COLOR=#595979]'This corresponds to the desired starting data that fits the [URL="http://covers.com/"]Covers.com[/URL] linking structure[/COLOR]
numstart [COLOR=#308080]=[/COLOR] [COLOR=#008c00]27791[/COLOR] [COLOR=#595979]'Links are number sequentially from a starting point to an ending point by increments of [URL="tel:+1"]+1[/URL][/COLOR]

[COLOR=#595979]'Begin loop from numend to numstart[/COLOR]
[COLOR=#200080][B]For[/B][/COLOR] n [COLOR=#308080]=[/COLOR] numend [COLOR=#200080][B]To[/B][/COLOR] numstart [COLOR=#200080][B]Step[/B][/COLOR] [COLOR=#308080]-[/COLOR][COLOR=#008c00]1[/COLOR]
Worksheets[COLOR=#008c00].[/COLOR]Add[COLOR=#308080]([/COLOR][COLOR=#308080])[/COLOR][COLOR=#308080].[/COLOR][COLOR=#200080][B]Name[/B][/COLOR] [COLOR=#308080]=[/COLOR] n [COLOR=#595979]'Add sheet upon initiating each loop[/COLOR]
Sheets[COLOR=#308080]([/COLOR]n[COLOR=#308080])[/COLOR][COLOR=#308080].[/COLOR][COLOR=#200080][B]Select[/B][/COLOR]

[COLOR=#595979]'URL parameters to grab an NFL box score -- URL = "covers.com/sports/odds/linehistory.aspx?eventId=" & n & "&sport=nfl&t=0"[/COLOR]

 [COLOR=#200080][B]With[/B][/COLOR] ActiveSheet[COLOR=#008c00].[/COLOR]QueryTables[COLOR=#008c00].[/COLOR]Add[COLOR=#308080]([/COLOR]Connection[COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR] [COLOR=#200080][B]_[/B][/COLOR]
        [COLOR=#1060b6]"URL;covers.com/pageLoader/pageLoader.aspx?page=/data/nfl/results/2010-2011/boxscore"[/COLOR] [COLOR=#308080]&[/COLOR] n [COLOR=#308080]&[/COLOR] [COLOR=#1060b6]".html&t=0"[/COLOR] [COLOR=#200080][B]_[/B][/COLOR]
        [COLOR=#308080],[/COLOR] Destination[COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR]Range[COLOR=#308080]([/COLOR][COLOR=#1060b6]"$A$1"[/COLOR][COLOR=#308080])[/COLOR][COLOR=#308080])[/COLOR]
        [COLOR=#308080].[/COLOR][COLOR=#200080][B]Name[/B][/COLOR] [COLOR=#308080]=[/COLOR] n
        [COLOR=#308080].[/COLOR]FieldNames [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]True[/COLOR]
        [COLOR=#308080].[/COLOR]RowNumbers [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]False[/COLOR]
        [COLOR=#308080].[/COLOR]FillAdjacentFormulas [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]False[/COLOR]
        [COLOR=#308080].[/COLOR]PreserveFormatting [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]True[/COLOR]
        [COLOR=#308080].[/COLOR]RefreshOnFileOpen [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]False[/COLOR]
        [COLOR=#308080].[/COLOR]BackgroundQuery [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]True[/COLOR]
        [COLOR=#308080].[/COLOR]RefreshStyle [COLOR=#308080]=[/COLOR] xlInsertDeleteCells
        [COLOR=#308080].[/COLOR]SavePassword [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]False[/COLOR]
        [COLOR=#308080].[/COLOR]SaveData [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]True[/COLOR]
        [COLOR=#308080].[/COLOR]AdjustColumnWidth [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]True[/COLOR]
        [COLOR=#308080].[/COLOR]RefreshPeriod [COLOR=#308080]=[/COLOR] [COLOR=#008c00]0[/COLOR]
        [COLOR=#308080].[/COLOR]WebSelectionType [COLOR=#308080]=[/COLOR] xlEntirePage
        [COLOR=#308080].[/COLOR]WebFormatting [COLOR=#308080]=[/COLOR] xlWebFormattingNone
        [COLOR=#308080].[/COLOR]WebPreFormattedTextToColumns [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]True[/COLOR]
        [COLOR=#308080].[/COLOR]WebConsecutiveDelimitersAsOne [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]True[/COLOR]
        [COLOR=#308080].[/COLOR]WebSingleBlockTextImport [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]False[/COLOR]
        [COLOR=#308080].[/COLOR]WebDisableDateRecognition [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]True[/COLOR]
        [COLOR=#308080].[/COLOR]WebDisableRedirections [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]False[/COLOR]
        [COLOR=#308080].[/COLOR]Refresh BackgroundQuery[COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR][COLOR=#0f4d75]False[/COLOR]
    [COLOR=#200080][B]End[/B][/COLOR] [COLOR=#200080][B]With[/B][/COLOR]
   
[COLOR=#595979]' iSub Clean () -- obtrusive data from sheet[/COLOR]

[COLOR=#200080][B]Dim[/B][/COLOR] myCell [COLOR=#200080][B]As[/B][/COLOR] Range
[COLOR=#200080][B]Dim[/B][/COLOR] myRange [COLOR=#200080][B]As[/B][/COLOR] Range
[COLOR=#200080][B]Dim[/B][/COLOR] LastRow [COLOR=#200080][B]As[/B][/COLOR] [COLOR=#200080][B]Long[/B][/COLOR]
[COLOR=#200080][B]Dim[/B][/COLOR] myCell1 [COLOR=#200080][B]As[/B][/COLOR] Range

[COLOR=#200080][B]Set[/B][/COLOR] myCell1 [COLOR=#308080]=[/COLOR] Range[COLOR=#308080]([/COLOR][COLOR=#1060b6]"A1"[/COLOR][COLOR=#308080])[/COLOR]
    Cells[COLOR=#008c00].[/COLOR]Find[COLOR=#308080]([/COLOR]What[COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR][COLOR=#1060b6]"Print Sheet"[/COLOR][COLOR=#308080],[/COLOR] After[COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR]ActiveCell[COLOR=#308080],[/COLOR] LookIn[COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR]xlFormulas[COLOR=#308080],[/COLOR] LookAt [COLOR=#200080][B]_[/B][/COLOR]
        [COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR]xlPart[COLOR=#308080],[/COLOR] SearchOrder[COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR]xlByRows[COLOR=#308080],[/COLOR] SearchDirection[COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR]xlNext[COLOR=#308080],[/COLOR] MatchCase[COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR] [COLOR=#200080][B]_[/B][/COLOR]
        [COLOR=#0f4d75]False[/COLOR][COLOR=#308080])[/COLOR][COLOR=#308080].[/COLOR]Activate
        [COLOR=#200080][B]Set[/B][/COLOR] myCell [COLOR=#308080]=[/COLOR] ActiveCell
       
        [COLOR=#200080][B]Set[/B][/COLOR] myRange [COLOR=#308080]=[/COLOR] Range[COLOR=#308080]([/COLOR]myCell[COLOR=#308080],[/COLOR] myCell1[COLOR=#308080])[/COLOR]
        myRange[COLOR=#008c00].[/COLOR]EntireRow[COLOR=#008c00].[/COLOR]Delete
LastRow [COLOR=#308080]=[/COLOR] ActiveCell[COLOR=#008c00].[/COLOR]SpecialCells[COLOR=#308080]([/COLOR]xlCellTypeLastCell[COLOR=#308080])[/COLOR][COLOR=#308080].[/COLOR]Row
[COLOR=#200080][B]Set[/B][/COLOR] myCell1 [COLOR=#308080]=[/COLOR] Range[COLOR=#308080]([/COLOR][COLOR=#1060b6]"A"[/COLOR] [COLOR=#308080]&[/COLOR] LastRow[COLOR=#308080])[/COLOR]
    Cells[COLOR=#008c00].[/COLOR]Find[COLOR=#308080]([/COLOR]What[COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR][COLOR=#1060b6]"NFL Boxscores"[/COLOR][COLOR=#308080],[/COLOR] After[COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR]ActiveCell[COLOR=#308080],[/COLOR] LookIn[COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR]xlFormulas[COLOR=#308080],[/COLOR] LookAt [COLOR=#200080][B]_[/B][/COLOR]
        [COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR]xlPart[COLOR=#308080],[/COLOR] SearchOrder[COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR]xlByRows[COLOR=#308080],[/COLOR] SearchDirection[COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR]xlNext[COLOR=#308080],[/COLOR] MatchCase[COLOR=#308080]:[/COLOR][COLOR=#308080]=[/COLOR] [COLOR=#200080][B]_[/B][/COLOR]
        [COLOR=#0f4d75]False[/COLOR][COLOR=#308080])[/COLOR][COLOR=#308080].[/COLOR]Activate
        [COLOR=#200080][B]Set[/B][/COLOR] myCell [COLOR=#308080]=[/COLOR] ActiveCell
       
        [COLOR=#200080][B]Set[/B][/COLOR] myRange [COLOR=#308080]=[/COLOR] Range[COLOR=#308080]([/COLOR]myCell[COLOR=#308080],[/COLOR] myCell1[COLOR=#308080])[/COLOR]
        myRange[COLOR=#008c00].[/COLOR]EntireRow[COLOR=#008c00].[/COLOR]Delete
[COLOR=#200080][B]With[/B][/COLOR] Range[COLOR=#308080]([/COLOR][COLOR=#1060b6]"A2"[/COLOR][COLOR=#308080],[/COLOR] Cells[COLOR=#308080]([/COLOR]Rows[COLOR=#008c00].[/COLOR]Count[COLOR=#308080],[/COLOR] [COLOR=#008c00]1[/COLOR][COLOR=#308080])[/COLOR][COLOR=#308080].[/COLOR][COLOR=#200080][B]End[/B][/COLOR][COLOR=#308080]([/COLOR]xlUp[COLOR=#308080])[/COLOR][COLOR=#308080])[/COLOR]
[COLOR=#308080].[/COLOR]SpecialCells[COLOR=#308080]([/COLOR]xlCellTypeBlanks[COLOR=#308080],[/COLOR] xlTextValues[COLOR=#308080])[/COLOR][COLOR=#308080].[/COLOR]EntireRow[COLOR=#008c00].[/COLOR]Delete
[COLOR=#200080][B]End[/B][/COLOR] [COLOR=#200080][B]With[/B][/COLOR]
[COLOR=#200080][B]Next[/B][/COLOR] n
    Application[COLOR=#008c00].[/COLOR]ScreenUpdating [COLOR=#308080]=[/COLOR] [COLOR=#0f4d75]True[/COLOR]
    Application[COLOR=#008c00].[/COLOR]Calculation [COLOR=#308080]=[/COLOR] xlCalculationAutomatic

[COLOR=#200080][B]End[/B][/COLOR] [COLOR=#200080][B]Sub[/B][/COLOR]

[rfr3sh]

uva, the code just created a bunch of workbooks for me

[Borat38]

a bit of digression: which sports do you guys think is the most stat-friendly? For me, it's NFL totals and NBA 2H covers, esp dogs.

[uva3021]

workbooks or worksheets?

it is working fine for me, make sure u copy and paste the code exactly

[rfr3sh]

Worksheets sorry
and I did paste it exactly with excel 2007

[gameday10]

I am looking for nba 09-10 schedule with half scores or quarter scores. I have the nba 2h lines/totals already. Just would like to have the scores to possibly write code instead of doing this by hand.

[Optional]

quote=rfr3sh;7138211]uva, the code just created a bunch of workbooks for me[/quote]

The URL in the code is wrong if you copy paste it as is. "http://www.covers.com/pageLoader/pag...-2011/boxscore"

I still have the same issue after correcting URL to "http://www.covers.com/pageLoader/pageLoader.aspx?page=/data/nfl/results/2010-2011/boxscore" though... so I guess the forum formatting has broken something else in the code too maybe?

Not sure why it worked for Uva though. So maybe we have older versions of Excel that don't support the code.

[uva3021]

ok i understand, didn't notice the faulty URL, for the URL property enter:

"URL;covers.com/pageLoader/pageLoader.aspx?page=/data/nfl/results/2010-2011/boxscore" & n & ".html&t=0"

[dvsbmx]

After changing the link I'm still getting blank worksheets.

[uva3021]

The url gets cut short when i post, how about i just attach the file

[Borat38]

blank spreadsheet

still got a blank spreadsheet from the zip file...

[pedro803]

I ran the zip file and it worked for me! Don't know what could be wrong Borat. Thank You so very much UVA -- I am hoping I can use this to see what is wrong with some of my stuff, or just adapt it to some websites I want to scrape.

Really appreciate it UVA!

[threeg5]

ok folks how about openoffice to cheap to have excel
i know they run similar but not exact i have not tried it but any thoughts I am bout to run out the door and will check back in a bit.s

[gamecock0118]

This is awesome and a huge help. I am just starting to attempt to gather data efficiently to hopefully backtest some theories.

[Wrecktangle]

I'm still interested in some sort of data sharing group, BTW I have data sets on almost every sport that I use for trade.

[benjy]

still got a blank spreadsheet from the zip file...

To get it to work you have to run the macro. Be sure you're showing the developer tab (Excel button, excel options, popular, click box "show developer tab") and then run the macro (Alt+F8 to show all macros).

[gamecock0118]

You should also increase the numend = "#" as the season goes along to have the most up to date data. I could just send you the file after the macro has been run, but then you would never know how to update it.

I'd probably be interested in trading data, but this is my first shot at this so I don't have any others to trade!

[benjy]

You should also increase the numend = "#" as the season goes along to have the most up to date data. I could just send you the file after the macro has been run, but then you would never know how to update it.

I'd probably be interested in trading data, but this is my first shot at this so I don't have any others to trade!

Thanks for the coding hint.

I'm in the same boat as gamecock - I'm starting to scape and willing to trade or datacheck but I don't have anything to trade (yet).

[pedro803]

same here, I am still trying to learn to scrape, but of course I would be more than happy to trade and share, but alas tengo nada

[demens]

To get it to work you have to run the macro. Be sure you're showing the developer tab (Excel button, excel options, popular, click box "show developer tab") and then run the macro (Alt+F8 to show all macros).

Did this solve anyones issues?

I'm testing this out and i keep getting blank worksheets (formatted thou) like people were saying before. The URLs are correct and i tried the zip file as well. I'm running the macro. But i can't find the show developer tab option in Excel 10.

I think the issue may be with security settings, digging through that now.

[uva3021]

Inside the code, insert a ' before "On Error Resume Next", then post the error message that pops up

[demens]

Inside the code, insert a ' before "On Error Resume Next", then post the error message that pops up

Runtime error 9

Subscript out of range.

Sheets(n).Select is highlighted in debug mode.

(using excel btw)
A bit off topic note. I got the nbaplay-offs sheet with that per2line function to work in oo. Just copied the code (from excel) into the standard module for all worksheets. The weird thing is that when i tried to copy that same code only for that worksheet it would not work.

[brewers7]

I am looking for nba 09-10 schedule with half scores or quarter scores. I have the nba 2h lines/totals already. Just would like to have the scores to possibly write code instead of doing this by hand.

I have anything you need from the 2009-10 NBA season...Shoot me a PM and I can get you that if you still need it...

[Nick@SI]

Would anyone know how if its possible to use the numend & numstart across multiple macros? Or maybe a more effective method? I currently have 3 one does the scores for each period then 2 others do the home and road stats and I'd rather not have input the the variable in all 3 far from an expert but pretty good at modifying to fit my needs. I have included the spreadsheet if anyone decides to help. Thanks in advance

[yak merchant]

Would anyone know how if its possible to use the numend & numstart across multiple macros? Or maybe a more effective method? I currently have 3 one does the scores for each period then 2 others do the home and road stats and I'd rather not have input the the variable in all 3 far from an expert but pretty good at modifying to fit my needs. I have included the spreadsheet if anyone decides to help. Thanks in advance

Not 100% sure I'm answering what you are asking, but if the macros are run separately then the best way would to be to set up a "Setup" worksheet and type them into a field (let's say A1 and C1), and then have each macro pick up the values from that sheet.

i.e.


numstart = Worksheets("Setup").Range("$A$1").Value
numend = Worksheets("Setup").Range("$C$1").Value


If you want to string all of the Macros together you can build a master macro that calls all the others and declare the variables in that macro and then pass those as parameters into the the three macros you have.

Hope that helps.