NixTrader for R10 – now with searchable list

Another round, more injuries and 2 more trades to use this week.  Only 4 trades before first Byes hit.  A big change to the spreadsheet this week to help.  Let’s get into it.

Scores > Magic Number > Prices

Despite the carnage, most coaches got a fairly high score last round.  This is because DT scoring across the board was high – avg for all players was over 72, the highest for the year (normally about 69).

The flip side of this high scoring was a big drop in MN and therefore more modest price rises than many coaches were expecting.  That’s the DT circle of life.

Searchable lists

Ever wanted to be able to search all the AFL players and find a short list of players matching a given set of criteria, for example:

• top 20 midfielders by average DT points,
• who can play in round 13 because no bye
• ranked by price, or by average or by total points

Well now you can.  See the table below –

While you may be able to do things like this elsewhere, I don’t think any other source lets you search with bye round criteria.

This facility is on a new third tab called “search“.  But first a couple of points of caution:

• Your sorting and filter criteria are set by the little down arrows to the right of each column heading.  If you don’t know how to use these I suggest you click on the link to the right of the table.  This will take you to a tutorial.  About half way down this tutorial it explains how the sort / filter dialogue box works.  [Please read this first before posting questions or tweeting me to ask how the search table works].

• In order for you to be able to sort and filter the data it is unprotected.  So be careful not to change or delete any of it.  If you do you will have to reload the spreadsheet and start again.

Other changes for Byes

There are three other changes to help you get ready for the Byes:

• I’m Stalking watchlist is much bigger.  Three more panels are added to the bottom, one for each Bye.  You can use this if you still need players from a particular Bye group to add pre-byes.  Or if you intend to get players in after their bye.  There aren’t many guarantees in DT, but you can be sure that a player who has a R11 bye won’t have a bye again.  So these R11 players are the ones you aim to trade in just before R12.

• My Team in the Bye Rounds  now allows you to make more adjustments to fine tune your bye teams.

• There is a green section at the bottom where you can write in your own plans for each bye round, and add or subtract players etc.  You can then copy this whole section to next week’s spreadsheet and keep updating your plans.

•  There is a green column in each of the bye rounds to give you more control of your team total and onfield player count.  For example my player spread is currently 21 / 21 /18 which looks pretty good.  However, if I make some adjustments based on who is likely to be actually playing onfield it comes  down to 17 / 19 / 15 before this week’s trades – so I’ve got a bit of work to do.
  • Select “LTI/Low JS” if you think one of your players is likely to be out for that round.
  • Select “Can DPP” if you can bring a benched player onto the field using DPP.  This will make their score count (only works if player is already listed as “emg”).

NixTrader spreadsheet

Reminders from previous weeks:

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

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

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

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

 

————————– 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.
  • Last week a Mac user reported that some of the team / bye data for Brandon Ellis was 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.