Using Microsoft Excel for your hockey pool

See how my Microsoft Excel template can give you an big advantage in your hockey pool!

 

WHY USE EXCEL?

  1. Rank scoring methods vary per pool. Use a formula to create a rank for all players.
  2. Strategy study the data based on your rank. How many forwards, defensemen and goalies will be selected? When should you pick a goalie?
  3. Injury Updates include links and comments. Adjust your rank based on injury updates.
  4. Draft Day players are selected quickly. Use slicers, data validation and conditional formatting to monitor who’s still available.

 

POOL SCORING

Let’s say we have 13 participants in our pool and these are the rules:

  1. non keeper league (2018-2019 season only)
  2. position selections 15 forwards + 9 defensemen + 3 goalies
  3. points goal=6, assist=3, hit=1, blocked shot=1, giveaway=-1. Goalies: win=8, shutout=10

Next? Get the relevant data and create formulas.

 

GET THE DATA!

Pool Guide Data

Prediction data from a pool guide may only include the most common stats (goals, assists, goalie wins). Some work is required if you want an accurate rank.

Missing Data

Estimate stats like hits and blocked shots using historical data. Guesstimate missing rookie stats by their playing style unless you find their pre-NHL data.

 

RANK FORMULA

A rank formula combines pool scoring rules with prediction data. Manual override column will allow you to enter data to adjust the rank (injuries, trades, etc).

Formulas can also monitor the quota of selected forwards, defensemen and goalies.

 

STRATEGY

If your pool allows substitutions consider selecting an injury prone star player. If not you’ll want to be more careful. Is a player past his prime? How will coaching changes and trades affect player stats? Study your data, add comments and flag important info.

 

INJURY UPDATES

My Excel file includes links to injury updates. There’s always a couple of players who miss a good portion of the regular season due to pre-season injuries. Reduce a player’s rank based on an injury.

 

DRAFT DAY

Excel has many helpful features: slicers (for filtering), conditional formatting (highlighting), data validation (selections). Get comfortable using these features before draft day.

 

EXCEL FILE

Get my Excel Hockey Pool Template. The data is NOT pool guide forecast data. Why? It wouldn’t be fair for me to share their data as it’s how they make income.

My Excel file also contains various links to blogs (i.e. tips, injury updates, etc).

 

POOL & DRAFT ADVICE

  • Suggest using stats not found in pool guides. The more work required to prep the better it is for you! Most of these stats aren’t that difficult to find and formulas can create a more precise rank.
  • Increase the required picks so that the pool is deeper and allows for more rookies, bounce back players and sleepers. This is where your homework will pay off!
  • Consider advice that’s relevant to your pool rules. Some advice might be great for keeper leagues (long term picks) but not for single season pools.
  • Matt Larkin’s (THN Ultimate Pool Guide) great advice about selecting goalies (‘Play It Cool With Goalies’). My favorite is ‘Grab a guaranteed volume guy’. A goalie might not be top goalie but if he plays more games he’ll get more points. Beware of amazing goalies on rebuilding teams.
  • Brian Costello (THN Ultimate Pool Guide) reminds us that some promising rookies may not get a chance this year. Blues GM Doug Armstrong added veterans who will use a lot of ice time.
  • Guides I’ve used: Hockey NewsSports Forecaster, Dobber pack (includes excel file).
  • There are so many blogs covering local teams. Lots of knowledge and gossip.
  • Subscribe to The Hockey News to keep up with players and teams throughout the year. The alternative is a lot of cramming prior to your draft.
  • Buy multiple guides and compare their forecasts. Most players are projected very close but some are very different (i.e. 2018-2019 projections for Brad Marchand)

 

ABOUT ME

My name is Kevin Lehrbass. I’m a Data Analyst. I live in Markham Ontario.

That’s me in Montreal beside a statue of Ken Dryden. As a kid growing up in the late 70s spring time meant the habs would win another cup and I’d be crying (i’m a leafs fan). Thanks a lot Ken 🙁

But hopefully things are changing now! In the meantime hockey pools are a fun way to combine two of my hobbies: data & hockey!

Oh…and Mike Palmateer will always be my favorite goalie! As an 8 year old (Feb 17 1979) I watched the Leafs defeat Marcel Dionne and the Kings. At one point Palmateer raced out to the blue line and it was more exciting than a goal being scored! I can’t forget to mention that Walt McKechnie scored a hat trick! 🙂

 

2 Comments Using Microsoft Excel for your hockey pool

Leave a Reply

Your email address will not be published. Required fields are marked *