Resource AFL Fantasy spreadsheet - 2022

[Jivlain]

Been doing this for a few years now, thought I would introduce it here - a downloadable spreadsheet of AFL Fantasy stats. Updated each round.

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

 

[StatPunks]

This type of sharing is what I love about the BigFooty community.

The average points during wins vs. losses and home vs. away is particularly useful.

Thanks Jivlain!

 

[rimb]

Nice one!

It would be good to have their scoring breakdown - e.g. ave kicks, handballs, tackles etc.

 

[Jivlain]

The average points during wins vs. losses and home vs. away is particularly useful.

Fyi, it's averaged over the current and previous season.

Nice one!

It would be good to have their scoring breakdown - e.g. ave kicks, handballs, tackles etc.

Can do!

 

[DV2000]

Been doing this for a few years now, thought I would introduce it here - a downloadable spreadsheet of AFL Fantasy stats. Updated each round.

AFL Fantasy spreadsheet (round 4)

If you've got suggestions for extra columns you'd like added, feel free to ask (though I can't predict future prices unless someone can clue me in on the formula).

Great job! Looks very interesting.

What do the "L5.LY", "LY", and "R17.x" columns mean? Also, how do you update the spreadsheet - is it automated?

 

[Jivlain]

Great job! Looks very interesting.

What do the "L5.LY", "LY", and "R17.x" columns mean? Also, how do you update the spreadsheet - is it automated?

"Last 5, Last Year", "Last Year average", and "Round x 2017", respectively.

Yeah, I've got a program which scrapes information each round from a few different sources, feeds it into my database, and then produces a few different outputs (including this one).

 

[samFFC]

Our very own Freako - amazing and great work!

 

[samFFC]

In terms of future pricing:

Tooserious gives this a good go, eg Bailey Banfield Salary Prediction - though I imagine it would be difficult to extract this data?
This page also does it in simpler form, for one round at a time: http://fantasysports.win/price-estimator.html

Formula: https://magicnumbertheory.wordpress.com/pricing-formula/

Magic number: https://magicnumbertheory.wordpress.com/magic-number/ - though this is for 2016.

At the start of this season, this was around 7040. (With injury etc adjustments)
eg. Patrick Dangerfield ave 120.5, priced at 849k.

AF website confirms the price changes are influenced by all scores this season and even last season:

• Players’ Prices change from Round to Round depending on how they have been performing over the previous weeks and months.
• The Players’ Prices will change based on a formula that takes into account their past performances. All games played by the Player since the start of the season are taken into account in the calculation of their Price changes, with a sliding scale of weightings with the most recent game receiving the highest weighting, as well as a component of their performance last year - if they played last year!
• The value of your Team is calculated based on the total value of your Team of 30 Players, plus any unused money in your Salary Cap. The value of your Team effectively becomes your weekly Salary Cap and may increase or decrease depending on the Price changes of your Players.

This page is keeping track of the Magic Number: http://fantasysports.win/magic-number.html
This week it is 6889.

I agree with the page that you can exclude the effects of games before 3 weeks ago, hence the formula is:
P(n) = (1 – (K1+K2+K3)) x P(n-1) + M(Rn) x (K1.L1 + K2.L2 + K3.L3)

And that K1+K2+K3 = 21% roughly, with K1>K2>K3, so you could probably say K1 = 10%, K2 = 7%, K3 = 4%

i.e.
Current price = (1-0.21) x Previous Price + Magic number x (0.1 x last score + 0.07 x 2nd last score + 0.04 x 3rd last score)

Having said that, if a player is consistent, then you can simplify that last part even further to just 0.07 x (average of last 3 games), which I think will make it easier to extract from your data (i.e. last 3 average, rather than trying to work out which was the player's last game, 2nd last game etc if they've been suspended for 1 week or dropped). Can you get last 3 average instead of last 5 average?
If they are inconsistent then this estimate will be out by about 10k, which is no big deal I don't think.

So the simplest formula for this week is:
Current price = 0.79 x Previous Price + 6889 x 0.07 x (average of last 3).

Happy to run the numbers over the Freo boys after tomorrow's game, then we can compare and confirm once lock-out ends on Wednesday and see if the formula works.

 

[samFFC]

Okay so have run the numbers, with the more complex formula ("expected 1") and the simpler formula ("expected 2") and we can see how close it is.
The largest differences between the formulae was around 7-8k, and that's for the players with significant variability recently.

Name Expected 1 Expected 2
Fyfe 742k 741k
Neale 684k 682k
Wilson 496k 503k
Langdon 506k 510k
Blakely 604k 608k
Mundy 630k 628k
Ryan 531k 533k
Walters 627k 624k
Matera 520k 518k
Banfield 329k 332k
Tucker 405k 413k
Brayshaw 323k 330k
Crowden 294k 294k
McCarthy 424k 424k
Duman 182k 188k
Cerra 302k 310k
Ballantyne 382k 382k
S.Hill 567k 569k
Pearce 283k 281k
Hamling 335k 337k
Sandilands 544k 546k
Taberner 502k 493k

 

[Jivlain]

Okay so have run the numbers, with the more complex formula ("expected 1") and the simpler formula ("expected 2") and we can see how close it is.
The largest differences between the formulae was around 7-8k, and that's for the players with significant variability recently.

Name Expected 1 Expected 2
Fyfe 742k 741k
Neale 684k 682k
Wilson 496k 503k
Langdon 506k 510k
Blakely 604k 608k
Mundy 630k 628k
Ryan 531k 533k
Walters 627k 624k
Matera 520k 518k
Banfield 329k 332k
Tucker 405k 413k
Brayshaw 323k 330k
Crowden 294k 294k
McCarthy 424k 424k
Duman 182k 188k
Cerra 302k 310k
Ballantyne 382k 382k
S.Hill 567k 569k
Pearce 283k 281k
Hamling 335k 337k
Sandilands 544k 546k
Taberner 502k 493k

I had a bit of a look at it this weekend as well. Based on the more complex formula, I predicted prices for all the players, mostly the correlation was a bit tight but with a few instances of up to 20k difference (Zerrett being the culprit in that instance, rather predictably). So what I then did was add in a few factors - their other (R1) score, and average over the previous season (initially weighted at 0). I then selected the subset of players who'd played all four rounds and had a 2017 average. I then ran a few million iterations of an optimisation algorithm over that subset, finding an optimal formula. Funnily enough, the optimal formula kept returning a negative weighting for "last year average" - i.e. making players who scored strongly last year cheaper (ever so slightly). I couldn't come up with a scenario where it made sense for them to implement it that way so I prevented the weights from going negative. Doing that, and it consistently weighted last year's average at 0

So whatever the "component of their performance last year" is it's clearly not their average. Price change maybe? Don't really get that but who knows. Score in last x rounds?

Anyway, I then applied the formula to the full dataset and it predicted their R4 scores to within 3k. I won't be home for a while tonight but I'll post the calculated formula when I do. It'll be interesting to see how it might evolve over the year.

 

[samFFC]

I had a bit of a look at it this weekend as well. Based on the more complex formula, I predicted prices for all the players, mostly the correlation was a bit tight but with a few instances of up to 20k difference (Zerrett being the culprit in that instance, rather predictably). So what I then did was add in a few factors - their other (R1) score, and average over the previous season (initially weighted at 0). I then selected the subset of players who'd played all four rounds and had a 2017 average. I then ran a few million iterations of an optimisation algorithm over that subset, finding an optimal formula. Funnily enough, the optimal formula kept returning a negative weighting for "last year average" - i.e. making players who scored strongly last year cheaper (ever so slightly). I couldn't come up with a scenario where it made sense for them to implement it that way so I prevented the weights from going negative. Doing that, and it consistently weighted last year's average at 0

So whatever the "component of their performance last year" is it's clearly not their average. Price change maybe? Don't really get that but who knows. Score in last x rounds?

Anyway, I then applied the formula to the full dataset and it predicted their R4 scores to within 3k. I won't be home for a while tonight but I'll post the calculated formula when I do. It'll be interesting to see how it might evolve over the year.

Very interesting - awesome work!
What's your Uni/work background, if you don't mind me asking?

I'm assuming that it won't be last year's average, but rather last x games, as you alluded to. Perhaps something like 1-2% for each of their last 3 games from last season?
Interestingly, many of the top players had slower ends to the season (eg Laird, Titch) which could explain the negative association when compared to overall average.

It wouldn't be explained by variations in magic number as you know the magic numbers for r4 in hindsight now, though it will make future prediction difficult unless you can provide prices over a range of magic numbers.

 

[Jivlain]

Very interesting - awesome work!
What's your Uni/work background, if you don't mind me asking?

I'm assuming that it won't be last year's average, but rather last x games, as you alluded to. Perhaps something like 1-2% for each of their last 3 games from last season?
Interestingly, many of the top players had slower ends to the season (eg Laird, Titch) which could explain the negative association when compared to overall average.

It wouldn't be explained by variations in magic number as you know the magic numbers for r4 in hindsight now, though it will make future prediction difficult unless you can provide prices over a range of magic numbers.

Hah, yeah, I'm a bioinformatics software developer.

My current best approximation is:
K1 = 0.083
K2 = 0.068
K3 = 0.051
K4 = 0.031

Thinking about it, might try a linear equations approach.

edit: Solving using linear equations:
K1 = 0.084
K2 = 0.067
K3 = 0.050
K4 = 0.034

Which is pleasingly similar to the earlier result.

As for the "component of their performance last year", I would guess at this point I'm going to be overfitting the data. Probably the eaisest way of dealing with that is to repeat this process next week (and next, and...), round the factors together, and see if running both sets of data on those produces errors you can fit a component into. Nothing leaps out of the residuals right now.

 

[samFFC]

Hah, yeah, I'm a bioinformatics software developer.

Nice - so cool!

My current best approximation is:
K1 = 0.083
K2 = 0.068
K3 = 0.051
K4 = 0.031

Thinking about it, might try a linear equations approach.

edit: Solving using linear equations:
K1 = 0.084
K2 = 0.067
K3 = 0.050
K4 = 0.034

Which is pleasingly similar to the earlier result.

As for the "component of their performance last year", I would guess at this point I'm going to be overfitting the data. Probably the eaisest way of dealing with that is to repeat this process next week (and next, and...), round the factors together, and see if running both sets of data on those produces errors you can fit a component into. Nothing leaps out of the residuals right now.

Makes sense and nice work. I think by the byes it should be pretty much perfect.
I presume you've tried with different values for "last x rounds" - does anything prior to 4 games make negligible difference?

 

[Jivlain]

AFL Fantasy spreadsheet (round 5)

In terms of pricing predictions, the previous formula got all players to within 9k. I ran the formula-calculator again, and got the follow factors:
K1 = 0.084
K2 = 0.067
K3 = 0.051
K4 = 0.034
K5 = 0.017

Good agreement with the previous results (but an extra round) and, once again, everyone to within 3k. Confidence increasing.

The fact that the following seems to hold pretty well
Kn ≈ Kn+1 + 0.17

Is interesting. What will that mean for next week? Tune in next time.

 

[Jivlain]

AFL Fantasy spreadsheet (round 6)

K1 = 0.085
K2 = 0.068
K3 = 0.051
K4 = 0.034
K5 = 0.017

Well, that's worked out pretty smoothly. I'll start adding predicted prices soon.

 

[Jivlain]

Added two new columns.

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

 

[Jivlain]

AFL Fantasy spreadsheet (round 7)

So I did my stat-scrape a bit earlier this week so I could generate estimated prices for this week and see how they compared to the new prices when the Freako spreadsheet came out and I could scrape that. Plus so I could estimate what would happen to Zorko who I've been carrying in my team. All within 2k as it turned out.

Solved using linear equations again, produced the exact same factors as last time. Adding in earlier rounds or various combinations of last year still doesn't contribute anything, so I'm reckoning that this is pretty much cracked.

 

[Jivlain]

AFL Fantasy spreadsheet (round 8)

Factors remain stable.

 

[samFFC]

AFL Fantasy spreadsheet (round 8)

Factors remain stable.

You’ve cracked the code!!

 

[Jivlain]

AFL Fantasy spreadsheet (round 9)

 

[VSTone]

that some sharing and caring s**t right there!
well done.

 

[samFFC]

Thanks again Jivlain !

Some analyses:

Looking at last month of form, compared to season average.

**Consistency**
Excluding those with season average <55
These are the players who have scored more than their season average each of the past 4 rounds (only 11 in total):

Player - Pos - Price - BE
Jy Simpkin Fwd 392000 37
George Hewett Mid/Fwd 435000 37
Will Hayward Fwd 434000 52
Heath Grundy Def 487000 53
Jack Newnes Mid 458000 50
Bayley Fritsch Fwd 465000 28
Darcy Byrne-Jones Def 556000 0
Jack Riewoldt Fwd 579000 80
Jack Redden Mid 632000 74
Jack Darling Fwd 644000 34
Jack Macrae Mid 885000 105

**Scoring**
Excluding those with season average <55
These are the players whose average over the past month is 20 points higher than their season average (plus Bont who is 19.83 higher)

Player - Pos - Price - BE
Tom Rockliff Mid/Fwd 545000 0
Tom Langdon Def 507000 51
Aaron Hall Mid 566000 0
Jack Redden Mid 632000 74
Brett Deledio Fwd 591000 42
Dayne Zorko Mid 656000 44
Jack Darling Fwd 644000 34
Jeremy Howe Def 644000 55
Heath Grundy Def 487000 53
Christian Salem Def/Mid 571000 72
Tom Hawkins Fwd 523000 56
Danyle Pearce Fwd 490000 82
Marcus Bontempelli Mid 645000 86

Just below this includes Crisp (+18.5), Daisy (+18.4), Macrae (+17.55), Lloyd (+172), Fritsch (+17), DBJ (+15.85), Langdon (+14), Brayshaw (+11), Anderson (+10.3).

On the other hand, these are players who are scoring 15 less than their season average, though not so useful as most have been injury-affected:

Player - Pos - Price - BE
Nick Robertson Def/Mid 418000 88
Blake Acres Mid/Fwd 584000 104
Callan Ward Mid 603000 132
Joe Daniher Fwd 476000 97
Taylor Walker Fwd 483000 105
Luke Shuey Mid 589000 138
Gary Ablett Mid 729000 175
George Horlin-Smith Mid 453000 100
Jeremy Cameron Fwd 537000 121
Stephen Hill Def/Mid 528000 118
Michael Walters Mid/Fwd 550000 114
Tim English Ruc/Fwd 425000 76
Tom Lynch Fwd 505000 127
James Aish Def/Mid 488000 97

Additionally, not far above that list includes: Coniglio (-12.65), Gray (-11.25), Goddard (-10.95), Ziebell (-10.85), Billings (-10.8), Dusty (-10.45), Laird (-10.05) amongst others.

 

[samFFC]

So in summary, with the good players, there is obviously a fair bit of mid price madness in there, but clearly Fritsch is doing well, and amongst the premos you have Redden, Darling, Howe, Macrae, Rocky who many have been touting.

In terms of those who have struggled, you can interpret many ways - are Gray and Dusty off-limits now, or good value? Is it more worthwhile scooping up returning injured players (GAJ, Shuey, Walters) or are they too risky? These decisions will be important!

 

[Jivlain]

AFL Fantasy spreadsheet (round 10)

I added a bye column. Also had another crack at finding something about last year's performances that vaguely correlates to the remaining differences between my calculated price for this week and the actual prices for this week - no luck. So the factors remain what they are.

 

[Rydo]

AFL Fantasy spreadsheet (round 6)

K1 = 0.085
K2 = 0.068
K3 = 0.051
K4 = 0.034
K5 = 0.017

Well, that's worked out pretty smoothly. I'll start adding predicted prices soon.

This is an awesome discussion gents; well done. I love Fantasy and I'm learning data science via R, so this is amazing on both fronts. Jivlain, have you compared the correlation of expected prices to actual from the Footywire website? They have some price predictions (predicted changes at n+1, n+2, n+3); I'm looking at some of their data on a weekly basis, but I haven't bothered to check whether their predictions are accurate.

Loving your work! Thanks!