The Algorithm – FPL & Sky Prediction & Planning Toolkit

Carl | Wed 10 Jul 2019
7 DAY FREE TRIAL

Win at Fantasy Football.

Join the Hub

Already a member?Log in

Comments

  • @Pinky and Debruyne

    Jul 14 at 11:55AM

    Bloody hell this is good Carl. Thanks for sharing mate

  • @Carl

    Jul 14 at 12:3PM

    Really glad you like it. It will be updated before at various times before the season starts (for transfers and to include Telegraph). During the season I try and update on a weekly basis for player stats and spread firm expect match goals (replace my estimates).

  • @Andy

    Jul 14 at 1:16PM

    In user dashboard, trying to exclude all teams except the 3 promoted teams but no-one then shows in the top 20 predicted players for each position.

    @Carl

    Jul 14 at 6:22PM

    There are two things you need to do here. Firstly, C16 needs to be set to No, then set the teams you want to Yes. You’ still won’t get any of the promoted clubs and just players with zero points. This is because no promoted teams have players meeting the minimum number of matches. If you set C11 to zero then it would make these players eligible.

    The problem with new teams and players is that they have no past perform to base predictions. These are just based on averages for the position and hence will have mainly the same prediction. Of course as the season progresses we’ll start to get data on these players to base projections.

  • @Will Thomas

    Jul 14 at 1:36PM

    Thanks for this Carl – going to be very useful bit of kit for FPL and Sky over the season. Top work  ? 

  • @Will Thomas

    Jul 14 at 1:43PM

    Posted by: Andy

    In user dashboard, trying to exclude all teams except the 3 promoted teams but no-one then shows in the top 20 predicted players for each position.

    Hi Andy, welcome to the site.

    I’m going to leave @darkish to answer this one 

  • @Mike A.

    Jul 14 at 9:28PM

    lovely work mate. in regards to xG data, have you developed this as well?

  • @Ste H

    Jul 14 at 9:32PM

    This is absolutely brilliant stuff Carl. Very clever and useful!

  • @Carl

    Jul 14 at 10:15PM

    No I didn’t develop the xG myself. Originally, I started using shots from https://www.whoscored.comthen I stumbled across https://understat.com/ which is brilliant. Has a break-down by match and player

    @Mike A.

    Jul 16 at 1:20AM

    cheers Carl, I’ve used understat as well

  • @Sophie

    Jul 14 at 10:22PM

    Cool! Very handy Carl! ?

  • @Ian Parrin

    Jul 15 at 10:46PM

    Looks a terrific bit of kit there Carl  ?Agree with Salah and Aguero being key up to the Overhaul, and with Valencia as the captain pick for weeks 1 & 4 (although neither fixture is that great!)First time I’ve logged onto Sky this summer. This is what I came up with in 15 minutes  ? [attach]9[/attach]Fortunately there’s plenty of time to adapt things over the next few weeks…

    @Carl

    Jul 15 at 10:50PM

    Very nice team and done so quickly as well. You may wish to change your keeper as I doubt he’ll be number 1 this season. Palace have a friendly tomorrow and Ro hinted he’ll start with his strongest available starting 11 which may give some insight.

  • @Ian Parrin

    Jul 15 at 11:8PM

    Let’s hope it’s Speroni then – the extra funds would allow a midfield upgrade along the lines of Stanislas to Sigurdsson/Walcott (Everton have an excellent ticker for the first four games) or Ralls to Antonio (West Ham have a pair of plum home fixtures.)

    @Carl

    Jul 16 at 6:39PM

    Let it defo won’t be Speroni. Bit surprised that Hennessey started the friendly match tonight, so may be first choice but I was under the impression it’d be Guaita. You’ll be pleased to hear PVA has scored two already tonight and it’s only half time :)

  • @James Hendrie

    Jul 24 at 4:33PM

    Hey guys, really like this tool. It’s definitely helped me identify my first draft. I set up a league for my pals last year and Dan joined it and won hahaha!! I need all the help i can get this year.How often will the tool be updated? I’ve tried to incorporate Allison but hes not in it yet.

  • @Will Thomas

    Jul 24 at 4:43PM

    Posted by: Jhendrie

    Hey guys, really like this tool. It’s definitely helped me identify my first draft. I set up a league for my pals last year and Dan joined it and won hahaha!! I need all the help i can get this year.

    How often will the tool be updated? I’ve tried to incorporate Allison but hes not in it yet.

    Welcome to the site @jhendrie!

    I’ll leave @animal to apologise for stealing your league winnings haha!

    @darkish usually updates weekly I believe. Although no previous history for Allison, so might not get a prediction for him pre-season

  • @Carl

    Jul 24 at 10:59PM

    Posted by: JhendrieHey guys, really like this tool. It’s definitely helped me identify my first draft. I set up a league for my pals last year and Dan joined it and won hahaha!! I need all the help i can get this year.How often will the tool be updated? I’ve tried to incorporate Allison but hes not in it yet.

    I’m really glad you like the prediction toolkit. I have updated tonight. I’m on holiday soon so won’t update until the final week before the season.Once the season starts I update on a weekly basis, first once I have corrected the data and then on the Thursday or Friday once I have the spread firms expected goals for the weekend.As Will mentioned, the prediction side of things is very difficult for players with no data. The player won’t appear in the top 20 tables or less you reduce the min matches to qualify to zero (though you then get loads of non playing players appearing). You can put them in the planning section.Good luck for the season ahead.

    @Blatch

    Aug 17 at 9:31PM

    Has the sheet been updated? Thanks so much for making this amazing tool.

    @Carl

    Aug 21 at 9:32PM

    In terms of updating, I try and have this finished by Wednesday night each week. On the planning page I will be hiding columns of weeks that have past.

  • @Beerfuelledman

    Jul 26 at 8:27PM

    Am I nuts or is Aguero missing in the FPL planning section?

    @Carl

    Jul 26 at 10:1PM

    You are not going mad. There was a little bug, eg the name ranges being picked up didn’t include the first 5 players including Aguero. I have now corrected this.

    Thanks

  • @smclean

    Jul 27 at 11:49AM

    Huge appreciation here for this toolkit.  Great work fella!

  • @ShaunGannon87

    Jul 30 at 6:26PM

    What is the difference between the top 20 and the bottom 20?

  • @Carl

    Aug 3 at 9:6PM

    Posted by: ShaunGannon87What is the difference between the top 20 and the bottom 20?

    Sorry for the delay in replying, but just returned from holiday.The top 20 is the top 20 by predicted total points.The bottom 20 is a list of the top 20 by predicted total points per £mHope that helps.

  • @Gary Precious

    Aug 21 at 3:8PM

    Is there a way to save the document and this still to continue to update? As each time log in i have to alter my team etcI am probably just being amatuerish at this for the document purposes, but use it each weekThanks in advance

  • @Carl

    Aug 21 at 9:42PM

    Posted by: Gary PreciousIs there a way to save the document and this still to continue to update? As each time log in i have to alter my team etcI am probably just being amatuerish at this for the document purposes, but use it each weekThanks in advance

    You can save a copy of the spreadsheet either into your own one-drive or to your PC. However, when I update the model your information would be in the updated model.For your team, highlight the rows that consist of the team (for “PlanSky” this would be rows 14:24) then copy and paste into a blank spreadsheet (some #N/A will appear but don’t worry about these). When the next spreadsheet is updated then highlight the rows where this was pasted into the blank spreadsheet and copy, goto the prediction model and in cell A14 paste. Hope that makes sense and helps, if not then please let me know.

    @Gary Precious

    Aug 22 at 8:24AM

    Makes complete sense, just wanted to see if another way. Thanks for getting back to me

  • @Hornet

    Aug 24 at 8:20AM

    Hi Carl – I can’t open the spreadsheet – error message says that there is author encryption. Any advice? cheers

    @Hornet

    Aug 24 at 10:28AM

    sorted!  cheers

  • @Carl

    Aug 24 at 12:56PM

    Sorry about that. There is a page I protect and protected the spreadsheet instead by mistake. Glad its working again

  • @TriXaM

    Sep 3 at 3:11PM

    Hi Carl, thanks for such a valuable resource – just starting to get to grips with the toolkit in readiness for the first overhaul. Just wanted to let you know that something funky seems to be going on with the excel sheet in onedrive. When following the algorithm link above it appears to be editable by everyone and the various “PlanXXX” worksheets have gone etc. Should that be the case? I’ve retrieved a previous version from a few days ago which I’ve downloaded to work with as it has the right worksheets, but thought I should mention this here in case something needs changing.

  • @Carl

    Sep 3 at 5:15PM

    Thanks, I’ve updated the prediction following the weekend games.I don’t protect the pages mainly due to time and I keep a master version. I over-write the public once a week (usually a Wednesday evening), but earlier this week due to the international break.If anything looks incorrect in the future then please let me know and I’ll update the file.Good luck with the over-haul.

    @TriXaM

    Sep 4 at 10:2AM

    Ah gotcha, makes sense! Thanks for explaining that, and also for the fresh update ready for the overhaul – just the job, look forward to spending time planning out my strategy over these next two weeks. And all the very best for your planning too, cheers!

  • @Hornet

    Sep 3 at 9:8PM

    Thanks, Carl. The link works but I can’t see how to get to the user dashboard tab at the bottom that I used to use….it seems to have disappeared?  thanks

    @Hornet

    Sep 3 at 9:11PM

    I can see it now – thanks

    @Carl

    Sep 3 at 9:15PM

    Glad to hear it is now working. All the best

  • @Mike108uk

    Mar 6 at 10:7PM

    Hi Does this take into account blank and double game weeks? For example, it doesn’t appear to show GW32 doubles.

    @Will Thomas

    Mar 8 at 11:9AM

    It’s not quite like Ben’s planner Mike, but @darkish should be able to assist further

    @Mike108uk

    Mar 8 at 10:49PM

    Thanks. I had another look tonight and there are’ n/as’ appearing on the user dashboard. If you change the variables the results stay the same.

  • @Mike108uk

    Mar 6 at 10:14PM

    I think you also have got the GW set wrong. The sheet says the current GW is 31 when it should be 30. (FPL)

    @Mike108uk

    Mar 6 at 11:21PM

    I think it is because you have counted the Eve V Man City in GW26 on their own

    @Carl

    Mar 13 at 6:40PM

    Sorry for not replying before but email notifications started going to the spam account. To ensure formula work the Eve v Man city gets its own GW so is a week ahead of FPL

  • @Mike108uk

    Mar 7 at 12:6AM

    sorry one last question, if I save this to my pc, will the odds and calculation get updated as the GW go by?

    @Carl

    Mar 13 at 6:42PM

    Sorry but no. But if you do save a version to your PC you can save your changes and always copy and paste your team when versions are updated.

  • @FPL_Mediocrity

    Mar 14 at 3:32PM

    Hi, This is a great tool, so thanks for doing it. One question, though. When are the prices updated? Declan Rice went up last night, but is still 4.6. Ta.

    @Carl

    Mar 14 at 5:37PM

    I refresh the prices and weekend stats once which is usually the day after the finish of the GW. On Wednesday I take the spreads for the upcoming weekend and then over-write the public version.

    @FPL_Mediocrity

    Mar 14 at 9:24PM

    Ah, cool. Thanks.

  • @DesperatelySeekingDusan

    Mar 15 at 11:41AM

    This is a great tool but as a FH32 player, is there a way to exclude GW32 and just look at 31 & 33? Or do I have to calculate the best players for 31 and 33 separately and manually combine them?

    @Carl

    Mar 15 at 5:38PM

    Sorry but you can’t do it easily in the tool. However, I have duplicated the tool and taken out GW 32 which is linked here https://1drv.ms/x/s!AkYcP_eLvYMVp3zb3yO5BFTrU9Gr As the tool is not in line with FPL the start and end weeks are 32 and 35. This is all set up. Let me know if you have any questions.

  • @Jeff McGow

    Jun 14 at 6:44PM

    Great work, Carl. I attempted a similar thing myself last season in order to estimate FPL and Sun DreamTeam points: apportioning goals/assists based on xG/xA, clean sheets using a poisson distribution for the clean sheet likelihood, etc.

    My stumbling block though was estimating the match score, so I eventually shelved the project. I see you have cracked this problem: most impressive! A bit of research has now pushed me in the direction of Dixon-Coles, but it looks a tad daunting. After all, it is 36 years since I last did some serious maths.

    It’ll be much easier if I run with your excellent algorithm instead! A tweak of the points awards will also allow me to use it for SDT. I’ll just need a random number generator to estimate the SDT ratings LOL (Although I think there might be something on ffstuff.co.uk that might help me identify which events affect the WhoScored ratings. Hmmm, let me see….. )

    Again, this is seriously good work, Carl, and thank you so much for sharing.

    @Carl

    Jun 15 at 9:52AM

    Thanks and glad you like the algorithm. Once you understand that the Poisson drives distribution of goals you are nearly there. The hardest thing as you say is estimating the expected goals. The DIxon-Coles is a bit complex and even then not sure how accurate it is. There are also some adjustments you need for gambling purposes but doesn’t matter so much for fantasy.

    Luckily spread firms (who know more than me provide this each week). Going forward I produced my own rating system for estimates but expect I’ll change this next season to be based on https://projects.fivethirtyeight.com/soccer-predictions/premier-league/ I tested this last season and they actually performed better than the bookie (but not by enough to beat the spread).

    In terms of the Sun I wouldn’t bother with random generators. You just want to calculated expected points, so how many points a player has via the Sun rating system divided by games.

    Good luck in the coming season.

  • @FPL_Jay

    Jul 10 at 5:22PM

    New FFHub member here – this tool looks very nice and looking forward to trying it out! Just an FYI, I’ve noticed Tielemans is not listed in the PlanFPL tab yet.

    @Carl

    Jul 10 at 8:21PM

    Hi Jay and hope you find the tool useful. I will be refreshing the player list at least once a week so any additions to the game will get included.

  • @Desperados747

    Jul 11 at 8:43AM

    Great work! But still trying to find how to set formation in planFPL tab

    @Carl

    Jul 11 at 8:59AM

    Thanks. In terms of the planFPL tab, have your squad in cells (F17:F31). the in the GW it gives a projected points of each player (column L in GW1). The suggested team to get max points is in the column before this (eg column K). The player who has his score highlighted in red is the highest scorer and therefore potential captain option.

    @Desperados747

    Jul 11 at 9:16AM

    So it means that formation will be chosen automatically based on potential points without option to change it?

    @Ashir

    Jul 29 at 7:44PM

    Hi Carl. Could you please make a video with regards to the algorithm. It’s difficult to understand please. I would really appreciate it

  • @gilders

    Jul 29 at 12:57PM

    Hi Carl,
    Last season you had a Sky spreadsheet – Sky summary to pub.xlsx – Microsoft Excel Online

    https://onedrive.live.com/view.aspx?resid=1583BD8BF73F1C46!1984&ithint=file%2cxlsx&app=Excel&authkey=!AFykiiov5LCeXUY

    that I found really useful. Rather than predictions, it was more of a form guide with pts/£m and pts/£m/game. Will this be returning this season?

    I notice on this algorithm that the third tab from the end (labelled “Sky”) looks like it’s showing results, rather than predictions. But there does not seem to be anyway of sorting the order out (i.e. if I want to quickly find out the top 20 GKs based on points/£m)

    @Carl

    Aug 2 at 4:39PM

    Sorry for the delay in replying but have just returned back to England from holiday. I think the spreadsheet you want was given a link in my “Quick Sky analysis” article. And the link is https://1drv.ms/x/s!AkYcP_eLvYMVqVzNdRJY8bNo6Kyv

    @gilders

    Aug 2 at 6:44PM

    Hope you had a good holiday Carl. That’s the spreadsheet I was talking about and I still have access to it.
    Would there be any way of updating it with the new prices? This would then be an excellent tool for choosing my team for the coming season. I find the points/£m/game really useful for seeing which players offer the best value.
    Obviously players from relegated teams could be deleted/ignored and, likewise, there’s no point in including promoted teams as most of the players won’t have Premier league stats.

    I don’t know how big the task of updating the new prices is. So if it’s going to take you too long, no problem, I understand.

  • @Ashir

    Jul 29 at 8:3PM

    Hi Carl. Could you please make a video with regards to the algorithm. It’s difficult to understand please. I would really appreciate it

    @Carl

    Aug 2 at 4:44PM

    Sorry for the delay in replying but have been on holiday and just returned to England. Your suggestion is a good one, though I don’t know how to make a video and also time is limited with the season starting. If you want to chat through the spreadsheet then DM at @DarkishJungle on twitter.

  • @Ashir

    Aug 6 at 6:28PM

    Thank you!

  • @caffeinatorthesecond

    Aug 7 at 12:10PM

    I can’t seem to use this at all. I’m on a MacBook using Safari.

    @Carl

    Aug 7 at 9:13PM

    I have never seen a MacBook. Not sure if anybody else has. Can you open and use other spreadsheets from Excel?

    @caffeinatorthesecond

    Aug 8 at 8:59AM

    I first thought I had to access it through the browser. But then I managed to download the file, but even in Excel, I can’t edit. It says the file is protected and when I go to “un-protect” it, it asks for a password, which I don’t have.

  • @SimonSB13

    Aug 7 at 8:58PM

    Hey Carl, Wonder – is there a video for dummies to use this?

    @Carl

    Aug 7 at 9:15PM

    Never made a video before and not sure how to make one. Is there any particular part of the spreadsheet you need help with?

    @SimonSB13

    Aug 8 at 9:9PM

    Urm… think being an idiot… but I can’t seem to get much use out of it.. everyone saying its useful and I am sure it is but I think I am just being daft and not using it how its intended. (i guess)

    @SimonSB13

    Aug 8 at 10:5PM

    Hey Carl, How do you “Plan ahead with it?” Cant see where you plan anything obviously – thought it would be on Plan FPL, but cant see an insert template anywhere

  • @AlRooney

    Aug 11 at 2:17PM

    Carl

    I’m a member of the Hub and started to use the Algorithm prior to the start of the season (excellent work – it’s pretty incredible)… but now all the cells are autocompleted with a request for another payment?? I had thought it was included in membership but can you clarify please?

    Thanks again
    Al

    @Carl

    Aug 11 at 4:32PM

    Not sure how somebody has done that, this is not what needs to be done. Please make no payment if it happens again. Please DM direct on twitter as well as posting here so I can correct as soon as it happens.

    I’m glad you like the tool and hope not to see a repeat of what has happened.

  • @electrochoc

    Aug 20 at 12:6PM

    very good!!!

  • @electrochoc

    Aug 20 at 12:7PM

    very

  • @walterbithell

    Aug 25 at 8:24PM

    Hey Carl, thanks for putting so much work into this spreadsheet. I’ve used a variety of tools from FFH as well as a few other websites. Is there a way for you to put Headers on the columns? Like Gameweek# specifically? Also, I’ve seen many replies where people have asked you to make a video. I get you don’t know how to make a video so how about a manual? Just a description with instructions for each column. What it is, what it means, how to use it? I downloaded the file to my Mac Excel and it functions. However, when I choose players for a Gameweek it doesn’t carry any of those players forward to the next week. Is that something I have to enter manually for each and every Gameweek? Thanks again.

    @Carl

    Aug 26 at 5:37PM

    In the “User dashboard” page in cells C8:C9 you set which game week these cover.

    In the Plan FPL page row 1 has a number which is the game week.

    If you have your team entered into column F, the players should pull through to the various game weeks. However, the formula can be replaced by new players name. When this happens you will see either the players name of his club become yellow. In these cases then go to the next week where there is no yellow and then copy and paste the players to the yellow players name. Hit me up with a DM at twitter if you want to discuss any of the detail.

    I will look into learning how to make a video.

  • @walterbithell

    Aug 25 at 8:26PM

    One last thing I forgot. Now that I’ve downloaded this file are your weekly updates or changes auto changed in my download? Or do I need to re-download each week?

    @Carl

    Aug 26 at 5:27PM

    The files are updated weekly and you’ll need to download to have the most up to date information.

  • @cmarios54

    Nov 30 at 7:9PM

    Hi, great tool, thanks for sharing. Was this in existence last year as well, and if so, do we have the results? Thanks.

    @Carl

    Dec 2 at 10:38AM

    It was in existance last season. Not sure what you mean by results as a lot of random variation. I didn’t track results last season, but am considering doing more bext in terms of performance. Thanks

  • @MAB

    Jan 14 at 6:42PM

    Hi Carl
    I have just begon to use the algo. but when i choose the next 3 weekes both Pulisic and Kane er amongst the top 10 expected point scorers? Making me scared to trust it :-)
    Brgds Martin

Please sign in or subscribe to add a comment.