Originally Posted by uva3021
stick a comment,', before the "On Error Resume Next" sequence, then post the error message, if any
it could be merely statfox being offline, or a bad internet connection
I get:
Run-time error '9':
Subscript out of range
Thanks for the help -- and again thanks for the heads up that excel can scrape web pages!
click debug and tell me what line it highlights
I clicked debug and step into in the VB code window and it highlighted the very first line:
Sub NFLfromStatfox()
but I kinda don't think that is what you were looking for, I don't know how to use debug
did you define this range?
Code: [View]For i = 1 To Range("NFLteams").Rows.Count
well that line is in the code that you provided, so I guess I did
in your instructions you wrote:
Then select all the teams in the table and define a name to the range, a brief survey of the code and one can see I named the range "NFLTeams"
I wasn't exactly sure what to do, I highlighted the column of teams and pushed the button at the top "define name" on the formulas tab and named it NFLteams
stepping through the code when it gets to the line:
Sheets(sht).Select
i get the error message
run time error '1004'
application-defined or object defined-error
that's because "sht" doesn't exist as a sheet. There is something wrong with your naming conventions
I.E. this is how my range, "NFLTeams" is structured
Arizona2009
Atlanta2009
....
NY+JETS2004
....
SAN+DIEGO2000
Every team from 2009 to 2000 is named in accordance to how they are formatted in the statfox link
Copy the names from a team report page, replace all spaces with a "+", the run the code
I am giving up for the night -- I have tried everything I can think of for now. I did import the table from the destination page with the excel browser (I could only import the whole page, wasn't able to get the table separate) and I have done the find and replace -- and I have done my best to name the range but I am not sure I am doing this right. I get the names of all the sheets e.g. NY+Giants2004 but none of the sheets have anything in them.
thanks for all of your help, I will come back to this!
Amazing thread. Thanks for all the great info.
Hi everyone, new guy here..
I didn't read this entire thread, but I got the gist of it from the first few pages. I started doing handicapping a few years ago as a hobby. I started by doing calculations on spreadsheets. Since then I've moved on to running all out, full-blown simulations of football games with some Python scripts I wrote. I run each matchup 10,000 times, and it gives me each team's % chance of winning straight up or against a given spread, average scores, rush attempts and yards and pass attempts, completions and yards. I also use the scripts to rank the teams, and I like my rankings better than most of the ones used in the BCS.
I'm not here to gloat. I can't, actually, because I haven't yet used my results to gamble with. I also don't have hard numbers on exactly how successful my projections are, though I will in the coming days I hope. All I know for sure is that I consistently do very well on ESPN.com's college pick'em, as that was my initial reason for starting all of this. That said, I'm only here to answer any questions anyone has about my methods, my scripts, or whatever else you can think of.
A little more detail:
I've built a database of each and every college football play for the last 2 years (and I can go back further just by running a script). For every play, the database has the down & distance, the yardline, the quarter and time left, the current score, the type of play, yards gained or lost, turnover, penalty... the whole shebang. With this information I can build my own boxscores with almost any type of information I need. More importantly, I use the info to build a sort of profile of each team, with their individual offensive, defensive, and special teams strengths and weaknesses.
These team profiles consist of a series of ratings which, when compared to any given opponents ratings, can be fed to the simulation script which churns out 10,000 simulated games between the given teams.
That's the basics... if you have any questions or would like any tips, ask away.
First post took a while for moderator approval, and then went up twice. Sorry!
Last edited by ScoreProphet; 09-01-10 at 03:13 PM. Reason: double post
Sounds interesting. The bolded part reminds me a bit of opponent modeling in poker-botting. If you don't mind me asking, what type of information/stats do you use for the team profiles? If your information is detailed enough, I think you could create a pretty robust play-by-play prediction system.More importantly, I use the info to build a sort of profile of each team, with their individual offensive, defensive, and special teams strengths and weaknesses.
These team profiles consist of a series of ratings which, when compared to any given opponents ratings, can be fed to the simulation script which churns out 10,000 simulated games between the given teams.
That's the basics... if you have any questions or would like any tips, ask away.
Without going into mind-numbing detail, each team is rated on their run "power" and pass "power", and also such things as their punt/kick return ability and whatnot. The rating system itself is on a scale centered around 1.0, which would be an average rating. On offense, anything over 1.0 is good, and on defense less than 1.0 is good. Let's say that across all FBS teams, the average running play is 5 yards per carry. If Team A has a run rating of 1.1, then that team should average about 5 * 1.1 = 5.5 yards per carry against an average (1.0 run defense) team. If instead of an average team, Team A played against a team with a run defense rating of 0.8 (very good), then Team A will probably average about 5 * 1.1 * 0.8 = 4.4 yards per rush.If you don't mind me asking, what type of information/stats do you use for the team profiles?
The simulation script itself will randomize Team A's carries throughout the games in a way that after 10,000 games they will average the "correct" yards per play. It does the same for each pass completion (and similarly completion %), and also accounts for turnovers and penalties, and like I mentioned kick/punt returns. Each team's profile also has info regarding their pass/rush ratio, which is also accounted for in the simulation.
Simulated coaching decisions, such as passing more often when you're trailing toward the end of the game (or running more with a large lead), are also taken into account to provide more realistic results.
ScoreProfit: How do you then back test this? I have somewhat of a stats database, and some formulas I've created similar to your rating each thing based around 1.0... I have limited knowledge of Python, but really want to back test my data with my formulas to find the final scores vs. the actual scores and spreads.
Currently... I made it so I have a drop down box with each team, then it pulls all their stats into the fields for my formulas to read (using an Array formula in Excel)...but it's insanely time consuming (and outright laughable, really) to switch every single matchup and look at every single score and compare them all by hand... then change the formula slightly to make it more accurate and then redo all of this by hand again.........
Anyone who wants to help, thanks!
Yeah, as long as you're dealing with spreadsheets, you will be doing a lot of things by hand. My knowledge of Python and programming in general is pretty limited, as well, but I know enough to get by for my needs. You could use python to work with CSV files, but storing all of your data in a database is much neater (SQLite works great). If you're going to get more into Python, look into SQLalchemy... it makes working with databases with python pretty easy. It's one more thing to learn, but it beats having to write SQL queries all the time, and then figuring out how to deal with the lists, and then this and that. It handles a lot of the legwork and confusing bits for you.ScoreProfit: How do you then back test this? I have somewhat of a stats database, and some formulas I've created similar to your rating each thing based around 1.0... I have limited knowledge of Python, but really want to back test my data with my formulas to find the final scores vs. the actual scores and spreads.
Currently... I made it so I have a drop down box with each team, then it pulls all their stats into the fields for my formulas to read (using an Array formula in Excel)...but it's insanely time consuming (and outright laughable, really) to switch every single matchup and look at every single score and compare them all by hand... then change the formula slightly to make it more accurate and then redo all of this by hand again.........
Anyone who wants to help, thanks!
As for your situation, I haven't dealt with spreadsheets in a while, so it's hard for me to say how you should backtest your results, especially without knowing the details of how you have all your data laid out. It sounds to me, though, that instead of using the dropdown lists, you should find a way to import the week's results onto one sheet. Just a long list, with each row containing one game. I would imagine columns A&B having the home and away team names, C&D your predicted scores, and E&F the actual scores. This way it's easy to put formulas in G&H for the difference between your projections and the actual scores, or whatever other calculations you want to see.
