I’ve wanted to share this for a while but well I’ve held off. So please read and pardon the length I think it’s worth the read. Let me give you a little background to what I’m going to be talking about. First of all, this all started a year ago because of my desire to see Portland finish out the season strong. I created a spreadsheet that I listed out the last 15 games of the season for Portland lifted straight from ESPN.com’s Blazer schedule and made columns for my predicted wins and losses and a column for my notes as to why I thought they would win or lose. So I followed by sheet for the remainder of the season and watched Portland come out at 41-41. That was really cool to me and yet I swiftly forgot about this till the next season started. I created a new sheet for the new season that had the entire season listed, had a place to put in my predictions and then a place to put in the end result so I could follow my predictions for the whole season. At the time I was excited about this and wanted to share it. I posted it here. I was trashed for posting it because it was an excel file with a macro and people’s flags went up about it being a virus. I understand the virus thing, I didn’t like peoples trashing me because I was a “lurker” here who rarely posted. Even though I’ve lurked and followed the people migration from the ESPN boards, to BBF to here. Anyway, As the season progressed, I found myself studying the sheet and wanting to know more information. I wanted to be able to look at the numbers and better understand Portlands place in things and how their season would come out. Partially to help sooth my soul after a loss and something to trumpet to my co workers when we won, I expanded the workbook all season long. Now it is comprised of seven distinct sheet sections that track and monitor different pieces of information. 1 - Win Loss Record This is the original sheet for this season with a few new modifications. Here I’ve got the entire Portland season with columns for: • Game Number • Game Date • Home/Away • # of Days Rest • Opponent • Opponent Conference (E/W) • Predicted Win/Loss • Predicted Win Total • Predicted Loss Total • Actual Win/Loss • Actual Win Total • Actual Loss Total • Actual Win Percentage • Opponent Win Percentage • Prediction Accurate (Y/N) Really once the season started all I had to do was put in a W or L in the Actual column and let the sheet do the rest. It fills in the details and colors things accordingly. Which was nice. I can glance at it and see the whole schedule quickly. I even segmented it with markers for ¼ season, ½ season, ¾ season and All Star Break. I also delineated the separation of months as well for additional tracking that will be discussed later. My need for more information, or really an analysis of what I had collected on that first sheet prompted me to make an additional one. 2 - Records Vs. The Records Vs. sheet is where I did most of my number crunching. We all hear stats rattled off at us on every sports show, pregame, post game, game commentator… They are everywhere. I wanted to use what I had collected to give me a birds eye view of how Portland was doing. So I turned my data into a collection of tables. • Western Conference Standings - with team by team win/loss, total games against and total games remaining. • Eastern Conference Standings - with team by team win/loss, total games against and total games remaining. • Vs East or West – Win and Losses broken down by Conference • Monthly records – Wins and losses broken down by month with monthly win percentage. • Rest records – wins and losses based on number of days of rest • Home records – Our wins and losses at home, sub categorized by east vs west. • Away record – our wins and losses while Away, sub categorized by east vs west. • An alternate view of the NBA standings broken down by playoffs or non-playofs and number of games remaining. • Win Loss Record vs Percentages. I made a dynamic table that scans the teams and groups them into levels, .800, .700, .600…. tells me how many teams are in each level, how many wins and losses we have against teams in that level, what our winning percentage is against the level, and how many games remain against that level. Then based on that winning percentage and the number of games remaining I calculate out a projected number of wins of remaining games to total up into a projected win total for the season. (Currently sits at 52) • Then I totaled things up wins and losses of teams above and below .500. As you can tell there is a lot of information going on here that just provides hope. As the season progressed I tweaked this so that the team that we are playing next is highlighted automatically so I can tell that the next game is against the Pacers, a .412 team, it’s out last game against them, we are 1-0 against them so far this season, 9-4 against the east in away games, 6-3 in March, 25-14 with 1 day of rest, and 9-1 against .400 level teams this season. We have 2 games remaining against the .400 level and we are projected to win both. 3 - RAW Data Developing at the same time as the Records Vs. was another sheet that provided some additional data that wasn’t on the Portland schedule. I needed to have each of the teams, their win and loss records and their winning percentage. This information changed daily and I needed it to filter/sort rankings to make some of the lists on the Records Vs. page. I found a website that published free each night an XML feed with the nightly standings that contained all the information that I was looking for. So I wrote a macro that when a button is clicked it goes out and grabs the XML and then updates the sheet and filters and sorts the data. So really that is all that is there and I really don’t spend any time there to look at information. I just go there to push the button once a day to update my opponent team stats. 4 & 5 - Player Stats & Game Info About a month ago I was looking at my sheet and was rather proud of where it was at. But I still felt that it was lacking. There was a wealth of information that I wasn’t including in this. I was looking at the next game, a home game against I think it was the Lakers. I could make a guess on how the team would do but what about individual players? Why not add player statistics? I could have their stats and base them off all the key categories that I’ve already done for the Records Vs. section. It seemed like a LOT of work so I didn’t do it at first. But then I hit a long weekend and I found myself going through the box scores of the season and just collecting the data and combining it with the game number from the first sheet. So then I could see all the stats for Brandon Roy, against say Houston. So I created some dynamic pivot tables that use these stats and combine them based on a drop down selection. I can look at the player stat averages based on, wins, losses, home, away, east, west, all games. It was pretty easy then to expand that into an additional table with extended stats with shooting percentages and makes and attempts. I also made a third table showing totals for all the main categories instead of averages all of these keyed to the same drop down selection. The last pivot table I made was based off of the initial one with player stat averages, but this time I made the drop down select individual teams rather then home/away etc. Now I could get really granular and see that Brandon Roy averaged X number of points against the west and Y number of points against the Spurs. This was really exciting to me. Having the raw data allows me to then make all sorts of comparisons if I wanted to for better understanding how Portland is going to do against a given team.
6 – Final Schedules The most recent section that I created was the Schedules sheet. The reason for this is quite honestly the same as my original concept for this only expanded. 20 games remained in the schedule and Portland was in a terribly tight race for the western playoff positions of 3rd to 8th. So I took and collected the daily schedules of 7 of the 9 teams fighting for those spots. I left LA off as they are not going to be caught. I also left off the Spurs, they are reachable, but honestly are beyond I think realistic goals. My goal is to really make sure we 1. Get home court advantage, 2. Win our Division, and 3. Not fall to 9th in the west. So I have side by side listing of Portland, Denver, Utah, New Orleans, Houston, Dallas, and Phoenix. I left room to place a W or L and calculate their new record based off that number. On this sheet I also placed a column for our “Magic” number based on comparing our record to Phoenix’s. So nightly I check to see which teams I want to win and lose and root appropriately. Last night for example, Houston and New Orleans played against each other. Houston had 25 losses, the same as Portland, while New Orleans had 24. I was rooting for Houston even though their “Percentage” was higher than Portland because of wins but were even in the loss column. To me at this point of the season it’s all about the loss column. New Orleans needed to be even with us if not have more losses. I used color to predict my wins and loss guesses for the teams and added them up to get totals. So as we win and lose I compare that to the actual and see how my prediction guesses shook out. One great thing about seeing the schedules like this, I could quickly see who had home and away games, days off, and what teams each was playing. Every exciting stuff if you ask me and really feels like I’ve come full circle from where I started a year or so ago. Hollingers Playoff Odds Early in the Season I started watching the Hollinger Playoff Odds page. It was interesting to me to see that the ESPN servers were doing nightly a 5000 game simulation of the season and projecting which teams would shake out where. To see my Portland at or near the top in the west all season long was incredible. So early on I decided to just track this information. I’ve not done anything with it at this time, and really it’s incomplete. I started trying to grab the information every day of the season from when I started, but soon gave that up for sampling it every couple days or so as I thought of it. Even with just a sampling, the data is terribly interesting to look at over the length of the season. Because I dated the entries I can look back at the schedule and see where Portlands record was at the time and how it compared to the playoff odds. Really this is just informational at this time and something of interest but who knows perhaps there is something here that could be mined and added to the stats to bring better clarity to the numbers and stats the other sheets contain. Conclusion So there you have it. A run down of what I’ve been doing and how I’ve been following the blazers this season in my own geekiness. I bring all this up because I’m looking for help. I want to make this better for next year. I’d like to refine this even further but I’m not sure where to take it yet. I’ve shown this to a few friends and they have wondered if I could expand it to include the whole league rather than just Portland. With the right number crunching, their thought is that perhaps it would be helpful for a Fantasy Basketball Manager. My question is what should I add for next season? What tweeks and changes should I make to make it better? What stats would you like to see tracked and included? PER? WP48? Stats/48, Other metrics that can be gleaned from the data? I’ve started a features list that I want to work off of this summer as I revise and edit. What are your thoughts? I’m also looking for suggestions on where/how I can automate getting the data so I don’t have to copy and past box scores for every game/team. If your interested in this spreadsheet to take a look at it, drop me a PM, I won’t post the Excel here directly any more. Personally I think this is exciting. Several times this season it’s been helpful to me as the ebb and flow of the season brings wins and losses. Where I might have freaked out and had a knee jerk reaction to a loss, I’ve been able to temper that a bit and see how this win or that loss affects the numbers and how Portland is playing strong even with a seemingly demoralizing loss at the wrong time. Sorry this explanation has been so long, I think I’ll break it up into several posts and with a little luck this time it wont be met with such venom and bile to make a person not want to be around. If you cannot provide constructive criticism, please just ignore these postings.
First and foremost, I'd ditch Excel for this project and move it over to Google Documents. It's absolutely awesome for sharing spreadsheets with big bulletin boards like this. It's pretty crude--no macros, limited filtering--think Excel circa 1992. But the ability to share with anyone realtime numbers (and select who can and can't edit the spreadsheet) more than makes up for it. Here's an example: http://spreadsheets.google.com/ccc?key=pb-ph1G6i2GQEtzGrZ5WVGg
btw--that looks like a lot of pretty fun work. I know so few people who've played much with pivot tables--they're truly one of the most awesome features in Excel. I've developed a ton of Access apps that refine the data into bite-size chunks that Excel then can go in and manipulate. When you have both working together, you can tackle literally a million records of data. (After that it's really time to start talking SQL.)
I've been tossing around the idea of moving to Access this summer. using the down time to refine things. Access or SQL or mySQL all would hold the "data" much better. It's the reporting or face portion of it that I think is going to take the most work.
here is a list of the things I'm either currently tracking or want to add to next years app. All Player Stat Averages All Player Stat Averages vs Each Team All Player Stat Averages: East/West All Player Stat Averages: Home/Away All Player Stat Averages: vs Win Percentage All Player Stat Averages: Wins/Losses Custom Player Tracking (For Fantasy) Daily Scoreboard Generator Daily Team Standings: Defensive Efficiency Daily Team Standings: East/West Daily Team Standings: Home/Away Daily Team Standings: Offensive Efficiency Daily Team Standings: Percentage Daily Team Standings: Record Last 10 Daily Team Standings: Strength of Schedule Daily Team Standings: Wins/Losses Player Defensive Rating: All Player Defensive Rating: Portland Player Fantasy Rating Player PER: All Player Per: Portland Player Stat Averages: + or - Player Stat Averages: Per 48 Minutes Player Stat Games Injured Player Stat: Games Played Player Stat: PER based on Salary Portland Estimate Wins: East/West Portland Estimate Wins: Home/Away Portland Estimate Wins: Percentage Portland Estimate Wins: Season Portland Games Remaining Back to Back Portland Games Remaining vs Each Team Portland Games Remaining vs East/West Portland Games Remaining vs Home/Away Portland Magic Number Portland Player Stat Averages Portland Player Stat Averages vs Each Team Portland Player Stat Averages: East/West Portland Player Stat Averages: Home/Away Portland Player Stat Averages: vs Win Percentage Portland Player Stat Averages: Wins/Losses Portland Player Stat Totals Portland Record vs: Back to Back Portland Record vs: Days Rest Portland Record vs: East/West Portland Record vs: Home/Away Portland Record vs: Individual Teams Portland Record vs: Month of Season Portland Record vs: Winning Percentages Schedule Details: Days Rest Schedule Details: Division Schedule Details: East/West Schedule Details: Game Number Schedule Details: Home/Away Schedule Details: Opp Win % Season Game Prediction Results Season Game Predictions for Portland Season Game Results for All NBA Teams Season Game Results for Portland Simulate Next Game Outcome Stat Leaders: Assists Stat Leaders: Assists Stat Leaders: Blocks Stat Leaders: Defensive Rebounds Stat Leaders: Offensive Rebounds Stat Leaders: PER Stat Leaders: Points Stat Leaders: Shooting Percentage Stat Leaders: Turn Overs Team Pace Team Rosters: All Team Rosters: Portland Team Schedule Sheet for All NBA Teams Team Schedule Sheet for Portland Trade Button Macro Automate Win/losses Automate Standings Automate Game Stats Automate Game Scoreboard
You know, you could import in Storyteller's salary stats for even more convoluted fun. Then you could track the dollar value of every minute played. Factoring in PER and games/minutes played and salary all in one place could tell you a lot about who should make the most money when contracts get renewed.
I have something like that on item #26, A Players PER weighted by Salary. I was thinking mostly for Fantasy Leagues, but still an interesting Metric to look at. Measuring a players value to the team both on and off the court.
So this is where Tom Penn has been hiding at.. although is there any way to view the spreadsheets at full size?
Good. Recognizing you have a serious problem is the first step to rehabilitation. Do try to get a bit of sunlight now and then.
It's not like I did all this in a weekend. But I've took bites out of it all season long. What other statistics would be good to track? I know there is all sorts of talk of new types of metrics that better represent how a player and team play. Another thing I was thinking about was figuring out a way to "scrap" the ESPN pages of the game log to pull more specific details about things like turnovers. Like offensive fouls, bad passes, who assists who. I was thinking about Roy and how he is averaging 5 assists a game. Who is the recipient of those passes the most? the Guards in kicking out for threes? The PF and Centers who are fed the ball in the post? Ok I admit I'm a freak of nature who just wants to know as much as I can about my favorite team. Is that such a bad thing?
Not at all, and your Avatar shows you're more knowledgeable than most about the value of certain player combinations to their team.
That is a real interesting macro view of the Blazers. As you expand, I hope you do two things; make these sheets public for other geeks, add more micro stats so that we could eventually go to one local for all our stat needs. I don't know if you ever go to 82games.com, but they have some real interesting metrics. Aside from that, great job. It's nice when working hard at a project actually produces something fun. repped
NICE WORK. Man i just finished my last Finance Final of my college career and I thought I was done with spreadsheets for a while! However, yours is most definitely the most interesting I've analyzed in a long time! Your work is straight up proof how far ahead of the curve this blazers team is and how good we are for such a young team in such an amazingly hard conference. In your face haters, and Mixum's alike!
