NixTrader for R9 – for Trades that go Boom


Hopefully everyone is more on top of their Bye issues than last week.  More of the same this week to help you get the most boom for your two weekly trades.

No point restating the bye new rules … if you don’t understand how they work Warnie wrote an excellent article here –

Updated Team Tables

As I’ve said previously, it’s not just a matter of finding someone with the right bye, we should also try to get players with a good lead in and good match ups through the bye period.

The teams giving up lots of points and 100s, or not many, aren’t as obvious as you might think.  Note the position of Gold Coast and Sydney on the against side in the following table.

R09 Team Avgs

The updated lead in run for each team is shown below (ranked in bye groups).

R09 Lead up to Byes

 The percentages are the percentage increase (or decrease) compared to playing the average (or mean) team every round.

Ground Strategy

Knowing your bye spread is one thing.  We really need to understand our player spread – when your players might play, how many you can get onto the ground, and which ones are likely to be the lowest scorers?  You can’t really do this in your head.  It’s only when you lay out the names and potential scores that you realise when you relying on a rookie with low JS in your 18.

NixTrader spreadsheet automatically lays out your team and each player’s average score for each of the 3 bye rounds, and calculates the total score based on best 18 + your nominated captain.

Everyone said DPP would be a lot less important in a 2 trade a week environment.  However, mismatched DPPs will be very helpful during the byes.  I notice a lot of people are dumping Sam Mayes this week – I’m moving him from my forwards onto my mid bench to give me DPP during byes.  I think this will help me more than chasing Jesse Lonergan (mid only).

NixTrader spreadsheet

When you download the spreadsheet it contains the team of the new overall leader.  So before you paste your team into the spreadsheet have a look at the leading team and its structure.  You might get some ideas.

Changes this week (not many this week):

  • Players & Trades tab: this tab includes My Team,  I’m Stalking and Trade Options all of which have the same layout.  The Trade Options ranking box (blue) has a few changes.
    • The order of the columns has been changes so the net trade $ is first
    • You can overwrite the ongoing average for any traded player in column AU (default is column AH if you leave AU blank).  I changed this because I wasn’t happy with how the defaults were treating the Wines to Priddis trade last week.
    • I’ve added a comment on each trade in column AV.  One message that was a bit lost in the ranking approach is that any trade with a positive number is viable in that it covers the money spent or gained.
    • The new bye round player spread for each trade pair is shown to the left of the Which Pair of Trades? box.

Reminder from last week (not a change)

  • My Team in the Bye Rounds – this picks up the players from MyTeam in the first tab.  So if you want to make any player changes you have to do it on the first tab, and this will change all 3 bye teams.   The only change you can make on the second tab is to select your Captains.

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

  • 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.
  • Younger folk: 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.

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

Post below how your bye preparations are going and your average score forecast, this might be useful for other coaches.

Also post if you think an otherwise good trade is being assessed badly by the formulas (such as Wines to Priddis last week).

————————– 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.



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



  • Excellent stuff, before my trades this week it looks like this

    potential onfield

    rd11: 19 (1740pts)
    rd12: 18 (1722pts)
    rd13: 16 (1441pts)

    I reckon i’ll be keeping Mayes like you’re doing getting him into midfield with Whitfield becoming a fwd player with rd 12 bye. Fixes a lot of problems.

    Will definitely consult this before each trade in the next few weeks. Great stuff.

  • During the byes, do you just need 18 players playing in any position, or is it setup where you need to have a certain number of players in each position?

    So for example can you have say 3 backs, 7 mids, 2 rucks & 6 Forwards, or is there a specified amount required for each position?

    • Read the bye article linked in this article for a more indepth look at it. It’s 18 from the starting 22 positions, in any combination you wish.

  • Nix, this is awesome. Well done on the numbers crunching, I’m a complete nerd for this stuff, lovin it!

  • Every day after lockout ends, I’m on the DTTalk site hoping the latest Nix spreadsheet is up…

    Great work again, and many many thanks!

    • Thanks Nick, but you can relax for 1st day, it’s always going take at least to 2nd day. If you are on twitter you will get notification when it’s posted.

  • Absolutely Brilliant!! Thanks for your hard work mate!!

  • This is an excellent Excel file for the serious dreamteamer!! good work buddy!

    What are peoples teams looking like through the byes, would be good to have some insight on other peoples projected scores etc

    Just so i can see what im up against.. lol

    Rnd 11: projected 1720 with 20 potential players
    Rnd 12: projected 1672.5 with 19 potential players
    Rnd 13: projected 1668.3 with 21 potential players ( obviously missing some premo midfielders )

    Thats my team as it stands, with a ranking about 3000 after a very disappointing rnd 8. Obviously there are going to be trades during and before the byes that will alter those numbers, but i have been pretty conscious about the byes the last few weeks and hoping to jump a few spots. Cheers

  • Round 11: 1718 (21 potential, 19 onfield)
    Round 12: 1748 (20 potential, 19 onfield)
    Round 13: 1634 (19 potential, 18 onfield)

    Defenders are quite Round 11 intensive (partially why I brought in Heppell this week as a tentative), but other than that no real problems that can’t be dealt with (like round 13 being heavy on premo mids). I’d rather round 13 be heavy with premo mids than round 11 any day!

    • Forgot, that’s with Swan captain rounds 11 and 12, and Rockliff captain for round 13 (the defaults chosen by the spread sheet)

  • Good to see a few coaches posting their numbers. I don’t think you can do much better than averaging around 1,700 on paper before the byes start. Then we can hit R12 and R13 a bit harder by trading in new players before the later bye rounds.

    Thanks for kind words, glad to see you are enjoying it.

  • Heath Scotland or Beau Waters

  • Have received report of problem with Brandon Ellis in spreadsheet – to date this is only on Mac machines. Problem doesn’t occur in Excel on PC / Windows.

    The problem is that some data for Brandon Ellis is for Adelaide not Richmond so he gets the wrong teams to play and wrong bye grouping. The issue only happens for Brandon Ellis – for some reason Mac seems to confuse him with Cameron Ellis-Yolmen of Adelaide in some formulas.

    Does anyone else have the problem with getting correct info for Brandon Ellis?