Prices & Trades Spreadsheet for R5

nixtrader

OK, spreadsheet out early this week.  Why?  Because I have vowed to be a lot more organised after missing lockout last round – I didn’t make any changes from the week before!

Friday was a travel day for me.  In retrospect the decision to drive via the Yarra Valley and visit a few wineries wasn’t the choice of a responsible DT coach.  A 3 hour trip became a 6 hour trip, and no coaching was done.  Worse still this is the 2nd time this year.  So my team is doing sh*t and I have missed the boat on many players.  At this rate I will be lucky to get into top 10k.

But good news is that I’ve bought a new Chromebook to travel with; so now will be online all the time.  Maybe my team can produce a Melbourne like last quarter and save their coach’s job.

Anyway for new readers this is a spreadsheet to help work out who to trade into, or out of, your team to get the best money changes for the next few rounds.

Changes this week:

  • Actual scores up to R4 and current prices automatically pulled up for every player.
  • Improvements to Trader section.  Put in a pair of players you want to trade to see the outcome. You can compare 5 different trades at the same time.

Here is link to download spreadsheet (excel2007) - DT2013 R5pub

If you used the spreadsheet from previous weeks, you can just select your team block of players’ names from there and copy + paste your team into this spreadsheet.

I am interested in how you are going with this.  Are there any features you would like added for future versions?

If you have any questions, post comments 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 -
    • Team and position for 2013.  If this is different to 2012, their previous team and position is also shown, or if this is their 1st year playing.
    • 2013 starting price.
    • Points expected based on that price (using initial magic number 5,150).  The difference in points between their 2013 base and their points average for all of 2012.  For most players who played last year this difference will be zero.
    • 2012 Performance in 3 columns – average points, games played, standard deviation.
    • Scores from the completed rounds in 2013 – “n” indicates they did not play.
    • 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.
  • The captain’s score isn’t doubled, so the total score won’t match your real DT score for the rounds.

 

At the bottom of the spreadsheet there are 3 more tables.

  • A table looking at the structure of your team,
  • A table looking at distribution of byes in your team.
  • A table that compares different trading options.

 

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

Join Sportingbet

18 Comments

  • Love this ! So good and so helpfull

  • U should make it easier to find names by making it when u press the player’s name’s starting letter it goes to that letter

    Otherwise it is very useful

    • Thanks for comment,

      I’ve looked at this before. It is important to keep the drop down box as unless the player’s name written exactly the same it won’t pull up the correct information about that player. But, unfortunately drop down boxes in excel don’t have predictive text capability.

      For the My Team section it is easier because you can copy the player block from last week, plus the players’ names for other positions are cut down to 3 letters.

      In the Trades section, I looked at just having the players for that position (i.e. back/mid/ruc/for) but then it would be messy for DPPs and it wouldn’t work for a 3 way trade.

      I think is is possible to do prediction with programming macros, but once I put program code into the spreadsheet many virus checkers and corporate fire walls will block it, so some users won’t be able to download it.

      I’ll keep your suggestion in mind, but I don’t think there is an easy solution.

  • I have already traded
    Viney / Evans

    Option #1
    Bartel / Cloke (make 40-50k)

    Option #2
    Stevenson / Birchall (make nothing / higher score)

    Option #3
    Neade / Mayes (make 40-50k)

    Pick one thanks :)

    • You’re right Almen, I’d also like to thank Nix for going to the effort of supplying us with this valuable resource. Cheers Nix.

      • Yes thanks Nix :)
        Which would you pick out of those 3?

        • I would say Stevenson to birchall
          Stevenson isn’t looking great and birchall is
          and birchall has a low breakeven

          • any other opinions?

          • I have one… play around with Nix’s spreadsheet and see if you can figure it out yourself. And when you do, come reply to your own post and share some insightful info with the rest of us.

            Thanks Nix. I vowed not to have a spready for DT this year… but they’re so damn hard to resist!

  • 2 to get rid of out of Bartel, Hodge, Moloney and Viney?

    And who for??

    Want to get Mayes and possibly Birchall..

    103k in bank

    Cheers guys

  • why get rid of bartel are u mad? moloney and viney

  • or moloney and a port def rookie

  • Top stuff mate!
    Could you also add in a section for a watch list of maybe 20 or so lines?
    Got quite a list that I want to keep tabs on ;_)

  • Thanks Nix, handy little spready.

    Thoughts on these trades?
    Kommer/Viney/Neade/Macaffer -> Mayes
    Kieran Jack -> Dane Swan
    L Stevenson -> Birchall

    First one is definate, but not sure which optional rookie to drop?
    Then I need to choose between the second & third option.
    Thanks

  • Nick

    Love your work Nix. I signed up to DTTalk just so I could say how hugely useful this spreadsheet has been (for someone who is too stingy to pay $20 for Assistant Coach).

    The only other thing I’d love to see is if there was a way to compare different players’ projected price rises over a longer period (say, 5 & 10 weeks). It’d be helpful when working out whether to keep a stalling rookie or slaughter the cow for another newer cow.

    Also, I’d back Fivepie’s call for a larger area for players on a watchlist.

    Brilliant, brilliant work!

  • Thanks for your kind words, Nick

    I am an Assistant Coach subscriber. While there is some overlap, this spreadsheet isn’t positioned as an alternative to AC.

    There is a long term plateau price and expected future change beyond round 6 at the right of the spreadsheet. This will give you some idea of where players are headed. Last year I had charts of the full price over the year – I might add these again eventually but until averages and magic number stabilise a bit more, these are not very accurate forecasts.

    I have made a watchlist section after fivepie’s suggestion, it will be in next week’s release. You will be able to copy your list into future releases and get updated data.

    • Nick

      Brilliant, thanks!

      My wife dislikes DT enough without me spending money on it… no amount of league wins makes up for a happy home!