NixTrader for R19 – with colour coded team tables

Round 19, last round before the finals.  Are you going to make the top final 8? Are you going to get a double chance?

You’ve probably noticed a few people posting how different their league points position is compared to their total points rank.  This is an outcome of the 44 trades regime that I predicted.  Leagues are energised because less games were a procession with the highest ranked team winning.  Most finals games will be very close and ebb and flow over the weekend like a test match.  Looking forward to it.

My tip – treat every game as if it’s your last and put the best possible team on the ground.  Winning each games is what matters, with 44 trades you will still get some chance to fix your team next round if a disaster happens.

My team – travelling nicely with consistent scoring (avge 2,226) in 6 rounds since the byes – including a few league wins against opponents above me on ladder and ranked higher overall.  Oh yeah … we’re quietly talking the P-word in our clubrooms .. what about you?

Lets get into it …..

Updated Tables – Team Averages & Next Games

Updated tables showing DT performance for each team, both for and against, are below.  The right hand panel has the averages for just the last 5 rounds so you can see more recent form.  Each table is ranked according to the number in the shaded column.  A change this week is that I’ve colour coded the left hand columns based on how high the teams are.

R18 Team Avgs FOR

R18 Team Avgs AGST

Some points of interest:

  • Essendon continue to crank out the most DT points and 100s, although expect this to be restricted because they have the worst home run.
  • Port, Collingwood, and Essendon continue their drift up the against table.  They are not far off becoming green teams.  In particular, I think the loss of Watson has given a lot more possession to opposition midfielders.
  • Melbourne still show up as conceding the most DT points, although their improvement is reflected by the fact that in the last 5 games they have conceded below average DT points.  On the plus side they still cough up over 4 100s per game.
  • GWS is the reverse, they still give up quite a lot of DT points, but a lot less 100s – now down to 5th least 100s in the last 5 games.  So it is questionable whether they are really a green team for the final run.
  • Brisbane have become a lot harder to DT score against recently – both in DT points and 100s.  Through the season they have worked their way down to become a red team overall.

The Next Rounds table below covers all the remaining games and shows which teams have the most helpful coming fixture for DT scores.  DT Finals rounds are shown in purple.  Two new columns were added on the right of this table showing the ownership percentage for each team.  The teams retain their colour coding from the above 2 tables so it is a bit easier to understand.

R19 Lead up Games

Some points of interest

  • Of all the teams that score consistent high DT points FOR (the blue teams), only one – Freo is at the top of the table.  Significantly all the other blue teams are at the bottom of the table.
  • Freo also has the lowest level of ownership of all the top DT scoring teams (except for North).
  • With the exception of Brisbane and Gold Coast, the teams with the highest player ownership tend to be at the bottom of the table.
  • Repeating caution about GWS and their recent form in targeting some opposition players and restricting their scoring.
  • I like the colours – in particular the against columns remind me a certain pack of fruit life-savers that was my favourite as a youngling.

This looks like a rare time when various DT principles are aligning to produce a clear outcome – the DT gods are all singing from the same hymn-sheet.  All together now … Freeeeooo way to go.

NixTrader Spreadsheet

No changes this week but repeating change from last week for those who missed it:

  • Search tab – 3 new columns added to table for all players:
    • BE – Break even score, for a player to hold his current value
    • Next $ change if the player scores his current average
    • Ownership percentage at end of latest round.

 

Here is link to download spreadsheet (Excel2007) – NixTrader2013 R19 pub

 

Reminders from previous weeks:

  • The team of the current overall leader is in NixTrader spreadsheet when you download it.  You can look at this team to see if you can learn anything from it, before replacing it with your own team.
  • If you used the spreadsheet from previous rounds, you can just select your blocks of players’ names from there and copy and paste them into this round’s spreadsheet.
  • After lockout, you can also use NixTrader to start planning for next week.  Just enter your players’ actual scores in the column for the current round to update your watchlist or trades.
  • Younglings:  if you don’t have Excel2007 software on your device you can still download the file, put it on a usb stick and ask around to find someone with Excel2007 software and ask to borrow their computer for an hour or so.
  • Mac users have reported Brandon Ellis (Richmond) is placed as an Adelaide player in bye and draw tables.  This doesn’t occur in Windows Excel.  Mac users should be aware of this if you add Brandon Ellis to your spreadsheet.

Any comments, questions or suggestions please post below, or tweet to @NixTrader.

 

————————– notes below are mostly the same each week ——————————————-

Brief instructions:

  • 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.

FAQ:

  • 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.
    • The file will not operate fully in either Google docs or Skydrive Excel web app.
    • Mac users report that some of the team / bye data for Brandon Ellis is wrong.  This didn’t occur on Windows / PC and was only reported on a Mac.  Be aware of this if you are on a Mac and looking at Brandon Ellis in the spreadsheet.

7 Comments

  • Love this spreadsheet! Fantastic work once again Nix! Struggling with a work project with a similar formula for the ‘last 3 avg’. Dont suppose you could share this formula could you?

  • Excellent as usual Nix – thanks to you I cracked 2400 last week. Cheers!!

    • Wow … 2,400 is a great score particularly when a lot of teams struggled. I think the credit goes to the coach though for making the right calls.

      • Nah mate – fair bit of the credit is yours (ie. my rnd 1 score without your column was 1730…)
        Trading to maintain the probability of upcoming good scores is a key to this DT caper, and your stats/analysis makes this a no-brainer most weeks.
        Regarding your plug for freo – a worry here is that their easy remaining draw could mean premo’s getting rested or progressively losing scoring intensity (ie. from playing weaker opposition week after week). Also – against weak opposition it is often the lesser lights (ie. not yet premo’s) that carve it up. Very interested in any thoughts you might have on this.
        Cheers!

        • Freo’s run – Carlton (ES), GWS (home), Melb (MCG), Port (home), Saints (ES).

          1. Neither Ross Lyon or Freo have won a premiership and are fairly driven to at least get to GF soon. If they don’t, the decision to poach Lyon will be seen as a failure. They will keep the foot down to try and get a top 4 spot (and I expect they will replace Essendon). Maybe even snatch a top 2 spot somehow.
          2. Assuming their older senior players aren’t carrying injuries (Mundy, Pav, Sandi) they all need hard match practice and form to take into the finals, not a rest.
          3. I don’t think they will rest players at home games while top 4 (top 2?) is up for grabs, and percentage could be important if there is another tied game.
          4. More than anyone Freo play a very positional / zonal type game – this has to be adapted slightly for various ground dimensions. They will want to fine tune this with their best starting team at grounds where finals are likely to be played – e.g. MCG, ES. If an away game was in Qld or Tassie it might be different, but I think they will all turn up in Melbourne.

          So I think they will take all the games seriously – maybe not St.K. depending on ladder position and if someone has an injury – but certainly up to the last game.

          My only concern with Freo is at the individual level – different players get the big scores. Barlow is a lock, but Mundy and Fyfe seem to swap around scoring, as do defenders Ibbo, Johnson & Duff. So it is a bit tricky knowing who to get. Certainly watch Sandi – he could be a cheap POD if he finds some form / match fitness.

          • Thanks – appreciate the detail around some of the subtleties in all this. Your prob right – the double chance is a big carrot that should hopefully limit ‘resting’. However, the other issue is that aside from Barlow there are no other safe bets due to the inconsistent scoring. Sandi could come good – likewise Pav given his past record. Have hung on to ibbo but aside from Barlow, might look elsewhere till I see a bit more. Thanks again.