Well what a round. Last Thursday seems a long time ago. Coaches have lived a month’s worth of stress in the mean time.
My own personal tragedy was that I lost a league game to Dr.DreamTeam. My team is cr*p, and I had 2 donuts to his full team, but I still got within 10 points … if Josh Kennedy had just kicked that last goal … sigh. Grrr.
Anyway time to focus on next week and how to use our 2 trades wisely. Good to see some premos drifting into the target range, particularly from WCE as I need to shift some byes into round 12. Also good that more people are starting to see positive benefits of 2 trades locked in – like going to a club knowing you’re going to pull a threesome, you only have to work out who you want in it.
Scoring by Teams
Before we get onto the spreadsheet I have a table showing which teams are scoring the most DT points, and 100s, on average over the first 6 rounds. More importantly which teams are conceding the most points and 100s on average.
This data is used in the spreadsheet to show whether each player is likely to score more, or less, than usual over the next few weeks depending on their draw.
Magic Number (N rated content)
Warning …. nerdy stuff ahead! Non-nerds might want to put their Snuggies over their eyes for next few paragraphs.
@TSWalesy at TooSerious (mainly a Supercoach website) belled the cat last week and showed how Supercoach Gold was underestimating BEs and overestimating future prices.
I am a happy Assistant Coach subscriber and user, so I am not suggesting people shouldn’t get AC. However, users should be aware that AC does the same thing – by default it uses MN from the current round going forward. This assumption is OK for later in the year when MN levels out and starts to bounce around. [See MN graph in NixTrader spreadsheet]. However it is overly optimistic for the first part of the year when MN is falling.
MN behaviour was going to be a bit of an unknown this year with no new expansion team – we have less rookies and their scoring is more vest effected in traditional teams. So the MN fall was always going to be less than pre Gold Coast era, but it is still there.
If you are getting BEs or price projections from any site or source you should try to understand how that source is doing their calculations and what bias they might have. AC users should be aware that long term price projections for players are a bit on the high side. Although now the issue has been called, I suspect AC might try to improve their calculations if they can do so easily.
OK … so back to G rated content
Changes this week:
- Structure: Spreadsheet has been reorganised. The contents tabs was removed since the links didn’t travel well, and remainder is now split into 2 tabs.
- Players & Trades tab: this tab includes My Team, Trade Options and I’m Stalking all of which have the same layout.
- The historical data such as each round score has been moved to the right hand side as this is going to keep getting bigger with each new round
- There is more statistical information – each player is ranked by average and total points scored. How many DT teams they are in is also shown.
- The Trade Options ranking part, has a second box to show if it is better or worse to wait a week for each trade option (although there is a bit more work to be done on part of this).
- Structure & Byes tab: this tab includes the Team Structure, The Bye Rounds and and new chart showing Magic Number over time (now that’s something you don’t see often).
Here is link to download spreadsheet (excel2007) – NixTRader2013 R7 pub
If you used the spreadsheet from previous rounds, you can just select your blocks of players’ names from there and copy + paste them into this round’s spreadsheet.
Any comments, questions or suggestions please post below or tweet to @NixTrader
————————– notes below are mostly the same each week ——————————————-
- You only have to enter information into the green cells, the other cells are protected to prevent any errors.
- Confirm or change Magic Number estimate for the end of next two rounds. I have put in values for MN, but if you think you know enough about MN to prefer other numbers you can change them.
- Enter your players using the drop down menu. In the menu only the players who fit that position are named in full, other players are reduced to 3 letters. If you used the spreadsheet from previous rounds you should be able to select and copy groups of players from there into this spreadsheet.
- For each player in your team data is pulled up automatically.
- A projection for that player headed “Points Proj.”. This is used as default in the calculations. If a player has played more than 3 games this is their current average; if less than 3 games the projection is calculated from their starting price.
- There are 2 green cells to enter the actual scores (or your estimates) for your players for the next 2 rounds. Based on these scores the last other columns calculate the averages, BEs, price change and new prices after the rounds.
- If you leave any cell blank, the calculation assumes the player gets their 2013 base score for that round – i.e. the score in column headed “Points Proj.” is used as default in the calculations.
- The player has to play 3 games for their price to change, so if the players miss games their price change won’t happen until after they play 3 games.
- If one of your players misses a round, put an “n” in the green cell for that round. The calculations for the next round will then update to take account of this.
- There is a section to show the next 5 teams that player will face. At the right of these 5 teams there are 4 percentages. These show the variation in points and 100 scores that are predicted to come from playing that string of teams, over the next 3 and 5 weeks. Percentages are based on the number of points and 100s each team has conceded so far this season. Positive percentage says this run of teams is expected to concede more points and hundreds than average; negative percentage says these teams are likely to concede less points and hundreds than average.
- Why are these calculations different to other published BEs and valuations?
- Everyone’s calculations depend on their assumptions about Magic Number, projections of future scores and how they round averages and values. This spreadsheet should replicate any other published calculations if you make the same projections, including changing the magic number. However, this spreadsheet is unique in that it allows you to change the estimates to do your own analysis.
- Tech talk:
- You need a device and software that supports microsoft excel 2007 spreadsheets to be able to open and use the spreadsheet. It won’t open on mobiles or tablets unless you have software or apps that support excel files.
- Even if you have other software that claims to support Excel files, the file still might not work properly, as every excel formula might not be supported. This current file uses array formulas, I expect many non-excel spreadsheets won’t support array formulas, despite supporting other excel like functions.