Resource AFL Fantasy spreadsheet - back for 2020!

Price formula

Jivlain

Hammelmann Appreciation Society
Sep 19, 2015
6,638
11,536
Cambridgeshire
AFL Club
Brisbane Lions
Other Teams
Swansea City, Gumbies FFC
  • Thread starter
  • Moderator
  • #57
Nice work mate! How were the predicted scores calculated/which factors were taken into account?
The pricing formula I use is:
ql_405d4d5e013263ad40c4fea1e14dcae2_l3.png

Where:
Pn is their price ahead of round n
Sn is their score in round n
Mn is the magic number for round n
K = [0.085, 0.068, 0.051, 0.034, 0.017]

See page 1 for discussion of how we got there. And then for making predictions based on this formula:

Predicted score - The score I assumed for the next round in price prediction. What it in fact is is the average of the last five games they have played (ever). Could go for something more sophisticated I guess... but then, if I had an actual way of predicting the next score I probably wouldn't be sharing that :laughing: Anyway, included primarily for transparency, the predicted change is dependent on this. With it there you can choose whether you believe it or not and decide from that.

Predicted change - The predicted change this week. This is based on the difference between the predicted price for next price and what I estimated the price for this round to be - that should (more-or-less) control for inaccuracies in the formula.

Enjoy!
 

Keystone agony

Brownlow Medallist
Aug 15, 2011
18,296
5,708
AFL Club
Essendon
Other Teams
Port Adelaide
Been doing this for a few years now, thought I would introduce it here - a downloadable spreadsheet of AFL Fantasy stats. Updated each round.

Round 1
Round 2
Round 3
Round 4
Round 5
Round 6
Round 7
Round 8
Round 9
Round 10
Round 11
Round 12
Round 13
Round 14
Round 15
Round 16
Round 17
Round 18
Round 19
Round 20

If you've got suggestions for extra columns you'd like added, feel free to ask.

Explanation of columns -
Pos - position(s) available in AFL Fantasy
BE - BreakEven, the score a player will need to produce to hold their price (approximately)
Games - games played (H&A) this season
Average - average of games (H&A) this season
Predicted score - assumed score for price prediction (average last five H&A games ever)
Predicted change - estimated price change next week
Availability - is selected or injured (selected state will only be updated sporadically)
L5LY - average of last 5 H&A games, last year
LY - average of H&A games, last year
Home - average score at home, last two seasons
Away - average score away, last two seasons
Win - average score in wins, last two seasons
Loss - average score in losses, last two seasons
Bye - which round they have a bye during
Rx - score in round x, this season
Kicks, Handballs, Marks, Tackles, Goals, Behinds - average of those stats, this season
FF - average frees for, this season
FA - average frees against, this season
HO - average hitouts, this season
R17.x - score in round x, last season.

All stats refer to senior H&A games only (i.e. no finals, preseason, AFLX, representative, reserves, etc).

Im interested to run some research over the off season.

From memory when i did research i did winning team avgs vs losing team avgs and the difference was like 200 pts between a side that qon and a side that loss.

Anyway because im big into my gambling on fantasy im going to have a look at

Night games vs Day games for the whole of 2019 and see how if there is any difference in scoring.

Typically theres a friday night game and then 2 saturday night games every week
 

Bushchooks

Draftee
Dec 31, 2018
5
0
AFL Club
Collingwood
So I reckon the next big step forward would be to make my score estimation better - obviously it’ll remain limited and could never have guessed what Telly was about to do :)

I agree. Are there sites out there that predict scores for the next 5 games? I think from memory Ultimate Footy might have done it?? If they take into account factors such as the team they are versing then it could provide some additional accuracy. Not as much accuracy as Sportsbet unfortunately, which goes into weather and all sorts.
 

Keystone agony

Brownlow Medallist
Aug 15, 2011
18,296
5,708
AFL Club
Essendon
Other Teams
Port Adelaide
I agree. Are there sites out there that predict scores for the next 5 games? I think from memory Ultimate Footy might have done it?? If they take into account factors such as the team they are versing then it could provide some additional accuracy. Not as much accuracy as Sportsbet unfortunately, which goes into weather and all sorts.

Im gamble a shitload on fantasy lines so would be really keen on this. Ive won a fair bit and take into account many things
 

Jivlain

Hammelmann Appreciation Society
Sep 19, 2015
6,638
11,536
Cambridgeshire
AFL Club
Brisbane Lions
Other Teams
Swansea City, Gumbies FFC
  • Thread starter
  • Moderator
  • #62
Im gamble a shitload on fantasy lines so would be really keen on this. Ive won a fair bit and take into account many things
Yeah, UF does a degree of score projection. Not sure it's particularly more accurate than mine though :laughing:
 

Keystone agony

Brownlow Medallist
Aug 15, 2011
18,296
5,708
AFL Club
Essendon
Other Teams
Port Adelaide
Yeah, UF does a degree of score projection. Not sure it's particularly more accurate than mine though :laughing:

There are a few Twitter accounts ie Fantasy Insider who do fairly sophisticated projections

Unfortunatly you also need to predict the outcome/result of a game to get accuracy with fantasy prediction and this in lies the difficulty

But i love that someone is looking into this and how sophisticated that formula is. I was looking to produce a formula but just dont have that maths genius in me.
 

Keystone agony

Brownlow Medallist
Aug 15, 2011
18,296
5,708
AFL Club
Essendon
Other Teams
Port Adelaide
Things i consider when predicting fantasy scores for players (I may forget some)

  • Form (5 game most recent form)
  • Venue avg
  • Role
  • Weather Conditions (Rain affects tall markers more and those that dont tackle ie Gaff as an example on a wing)
  • Opposition (Easy oppo like Gold Coast more can be scored)
  • Tag? Likelihood of a tag or no tag
  • Avg at home(In state) vs Away (Also covered a bit by Venue avg) - Prefer Venue avg as the more dominant figure
  • What do similar players avg against side so far for season? (Ie Inside bulls... Yeo,Cripps, Oliver, Cunnington, Wines) or Wingers/Outsides (Gaff, T.Phillips, I.Smith, B.Hill) - Small Forwards (Betts, C.Cameron, W.Rioli, L.Ryan, Fantasia, M.Tipungwuti ect)
  • Result (Massive indicator in scores - Prediction of result, likelihood of a big win or big loss ect), Forwards - Benefit from winning teams, Defenders less impacted in losses ect
 

Bushchooks

Draftee
Dec 31, 2018
5
0
AFL Club
Collingwood
Things i consider when predicting fantasy scores for players (I may forget some)

  • Form (5 game most recent form)
  • Venue avg
  • Role
  • Weather Conditions (Rain affects tall markers more and those that dont tackle ie Gaff as an example on a wing)
  • Opposition (Easy oppo like Gold Coast more can be scored)
  • Tag? Likelihood of a tag or no tag
  • Avg at home(In state) vs Away (Also covered a bit by Venue avg) - Prefer Venue avg as the more dominant figure
  • What do similar players avg against side so far for season? (Ie Inside bulls... Yeo,Cripps, Oliver, Cunnington, Wines) or Wingers/Outsides (Gaff, T.Phillips, I.Smith, B.Hill) - Small Forwards (Betts, C.Cameron, W.Rioli, L.Ryan, Fantasia, M.Tipungwuti ect)
  • Result (Massive indicator in scores - Prediction of result, likelihood of a big win or big loss ect), Forwards - Benefit from winning teams, Defenders less impacted in losses ect

Sounds like some serious regression time. Do any sites such as AFL.com.au's fantasy site have any score predictions that can be scraped? 5 games in advance for every player. There is no advantage to having just a few players.

Jivlain, do you think that the accuracy of just using average of last 5 is sufficient?
 

Keystone agony

Brownlow Medallist
Aug 15, 2011
18,296
5,708
AFL Club
Essendon
Other Teams
Port Adelaide
Sounds like some serious regression time. Do any sites such as AFL.com.au's fantasy site have any score predictions that can be scraped? 5 games in advance for every player. There is no advantage to having just a few players.

Jivlain, do you think that the accuracy of just using average of last 5 is sufficient?

It isnt

I mainly keep to a few key criteria, that being:

- Form (5 game recent form)
- Venue avg
- Likelihood of Result (Win or LOSS) - extremely hard to predict in some cases. I kept to strong outlier eg: West Coast vs Gold Coast at Optus for example ... or Richmond vs Gold Coast @ MCG
- Role (Also important but if team sheets dont change than its safe to assume to previous week role will be the same)
 

Bushchooks

Draftee
Dec 31, 2018
5
0
AFL Club
Collingwood
The pricing formula I use is:
View attachment 544585
Where:
Pn is their price ahead of round n
Sn is their score in round n
Mn is the magic number for round n
K = [0.085, 0.068, 0.051, 0.034, 0.017]

See page 1 for discussion of how we got there. And then for making predictions based on this formula:

Jivlain, love your work.

Just to clarify, with your spreadsheets:

Round 1 - Price is referring to the players' prices after the completion of round 1, and round 22 spreadsheet price has their prices after the completion of round 22, and everything in between? Following from that, I suppose that my question is, did you make a spreadsheet for round 23?

Also, did these magic numbers line up with yours? http://fantasysports.win/magic-number.html I guess this is the magic number referring to the round about to be played. In live calculations, we don't actually know what the magic number is going to be until the actual round has been played right?
 

Jivlain

Hammelmann Appreciation Society
Sep 19, 2015
6,638
11,536
Cambridgeshire
AFL Club
Brisbane Lions
Other Teams
Swansea City, Gumbies FFC
  • Thread starter
  • Moderator
  • #68
Jivlain, love your work.

Just to clarify, with your spreadsheets:

Round 1 - Price is referring to the players' prices after the completion of round 1, and round 22 spreadsheet price has their prices after the completion of round 22, and everything in between? Following from that, I suppose that my question is, did you make a spreadsheet for round 23?

Also, did these magic numbers line up with yours? http://fantasysports.win/magic-number.html I guess this is the magic number referring to the round about to be played. In live calculations, we don't actually know what the magic number is going to be until the actual round has been played right?
Yes. I do my spreadsheet following the completion of the round it's named for. So the r1 spreadsheet for next year will be ~March 27/28. I do not make a round 23 spreadsheet because there's no upcoming fantasy games ¯\_(ツ)_/¯

Yes, that's the magic number I use. I just grab it from that site each week, I haven't made the effort to actually calculate it myself. I guess I should, I have the data and it would save me the copy-paste.
 

Bushchooks

Draftee
Dec 31, 2018
5
0
AFL Club
Collingwood
Yes. I do my spreadsheet following the completion of the round it's named for. So the r1 spreadsheet for next year will be ~March 27/28. I do not make a round 23 spreadsheet because there's no upcoming fantasy games ¯\_(ツ)_/¯

Yes, that's the magic number I use. I just grab it from that site each week, I haven't made the effort to actually calculate it myself. I guess I should, I have the data and it would save me the copy-paste.

Thanks Jivlain,

I get a very similar pricing formula to yourself using the previous 5 scores for all relevant players who played round 21 and 22, with R^2 = 0.999999.

Round 22 I didn't actually have any players outside of $1000 differentiation, though round 21 Nathan Jones's predicted price was off by more than -$7,000. It seems a bit too bizarre to have one so off out of 368 observations so perhaps there is some mix-up with the data along the way.

Next I'm going to look at what happens to the formula when they haven't actually played 5 games yet, so early in the season. Perhaps that's the only time when scores from the previous season are actually relevant.

Cheers
 

Bushchooks

Draftee
Dec 31, 2018
5
0
AFL Club
Collingwood
So my formula stayed the same for the Kn's, but as you are missing some the weighting of the previous price is greater in the formula (1 - Sum(Kn's)).

I still can't manage to see where any of last year's values come into the equation. ¯\_(ツ)_/¯ <--- sorry, I had to try it out, it's funny.