• Hi Guest, The forum will be moving hosts on 26 July and as such will be closed from Midday until the move has completed.
    As we will be with new hosts it may take a while before DNS get updated so it could take while before you can get back on the forum.
    I think it will take at least 4 hours but could easily be 48!
    Ark Royal
  • There seems to be a problem with some alerts not being emailed to members. I have told the hosts and they are investigating.
  • Sorry for the ongoing issues that you may have been experiencing whilst using the forum lately

    It really is frustrating when the forum slows down or Server Error 500 pops up.

    Apparently the hosts acknowledge there is a problem.
    Thank you for using our services and sorry for the experienced delay!
    Unfortunately, these errors are due to a higher server load. Our senior department knows about the issue and they are working towards a permanent resolution of the issue, however, I'd advise you to consider using our new cPanel cloud solutions: https://www.tsohost.com/web-hosting


    I will have to investigate what the differences are with what We have know compared to the alternative service they want us to migrate to.
    Keep safe.
    AR

Footy Spreadsheets

Hi all. I thought I would start a new thread where I can post some spreadsheets based around football, rather than clutter up everybody else's.

I have checked all of the calculations on the attached spreadsheet, and to my knowledge they are correct but there are a fair few :gotcha: so if you spot a mistake please let me know and I will fix ASAP.

On this spreadsheet you will be able to look at Poisson calcs, Rankings based on performance, ratings and a fair few more. No formulas are hidden, so you can see and check what I have done and adapt as you will. I often find once the season starts I may I delete half of the stuff on the overview page as none of my systems require it but when I am trying to find a new system I take the more the merrier approach.

Quick user guide-
Data Sheet - Will allow you to change the league and the year. Data is automatically downloaded from football data. It takes seconds.

Fixture sheet - Only need to go if looking at forthcoming fixtures. Click on the "fill it in" button and future fixtures and odds are automatically entered for the league that you are looking at.

Overview page
If A1 is bigger than the number in A2 you are looking at future matches, otherwise you are looking at historical matches.
If cell J1 = Y, then all games are included for calcs based on expected goals, if cell j1 = N, then only home teams @ home matches etc count.
Cell P21 allows you to adjust the K value for ELO calcs and cell S21 allows you to adjust the rating for Steele ratings adjustor

Additional notes/ observations
All of the rankings are based on average points/goals per game. So for example if team A has played 20 games and got 50points, team B has played 25 games and got 51pts. Team A will have the better rating. Something to be aware of: The fill it in button downloads the future fixtures for that league, so if you press it when looking at Prem 2000 data, you will get the forthcoming prem fixtures for 19/20 season.

Version 1.
 

Attachments

  • Football Version 1.xlsm
    1.7 MB · Views: 72
The reason I designed the sheet in the way that I did is to make backtesting systems and ideas easy, If you want to copy the seasons data make A1 =A2 in overview. Ensure that rows 1-7 are the same in the sheets Overview and backtesting data and run the macro "copyfromoverview". The whole seasons data gets copied automatically and then you can test your systems however.


This is a file I made quickly this evening based on the above Meet Google Drive – One place for all your files. Enter your formula in A7, press the update button and have a look at the results. In a couple of weeks or sooner I will give an example of how to input formula into the orig so that you can easily see when your bets come up
 
Hi could you have a look see if new fixtures are downloading for as I'm only getting odd fixtures,

FHRTIPS FHRTIPS .
I have had a quick look and from what I can tell it works. I have tried it for the Italian Serie A and the Scots prem and I get this weekends fixtures.
Please be aware that the data is downloaded from football-data and I believe that he updates the data on a Friday and a Tuesday. You can also enter the fixtures yourself on the "fixtures" sheet if you need it before that site is updated.

--
What I have noticed is that unless you reload the file it doesn't look like you will be able to load the 20/21 season. For ease of use I will be updating the file so that this problem no longer exists. I will add the updated file in at most a couple of days.

As an aside I will be adding a couple of other files as well in the near future

All the best
 
Hi all

Had a look at my file that I attached in post 1, everything seems to be rosy. The league that has started 20/21, shows up with no issues and I expect the rest will do so as well. So for now the file remains unchanged. If problems arise, or issues are pointed out, I will update and attach.

--

The attached files are something that is a little bit different to the above. I use this type of file to help me find a starting point when trying to develop a system,. The intention of these files is to allow people to quickly look at specific groupings within leagues, say home teams with odds of 2 to 3.

This year I have added correct score distributions and time of goals. I have not used these parts before and am unsure of whether they will provide value.

These files should be very easy to use. Only change, the bright yellow cells on the "full time" sheet to change the odds range for the selected market and your done.


--
Additional note
1) All formulas are able to be seen, checked and changed.
2) IF you like these files and want to do them for other leagues unhide the years data, and then copy and paste the new data into columns A to O, ensuring that the column headings match up.
* I get the data from football-data and it is free.
**obviously the time of goals sheet wont work unless you enter the time of goals yourself.
3) These files are new and I have not been able to cross check them as much as I would like. Please point out any possible or suspected errors and I will check and feedback. There are a lot of calculations.
4) The times of the goals were entered by multiple people. Whilst I tried to check the data (I checked to see if the HT score and FT matched up according to the times of the goals and then random selections) we did this for approx 20 leagues and at least three seasons. A lot of data got entered. Again if you think that you spot an error please let me know.

Edit 5) If the goal was scored in the (45th + 2 min), it was recorded as 45, (90 +7) it was recorded as 90.
Edit 6) The under over returns are based on the average odds, not the max odds

All the best and I hope that someone else finds them useful.
 

Attachments

  • Champ data.xlsx
    2 MB · Views: 19
  • League 1 data.xlsx
    2 MB · Views: 15
  • League 2 data.xlsx
    1.9 MB · Views: 11
  • Prem Data.xlsx
    1.8 MB · Views: 20
  • Sheet 1.JPG
    Sheet 1.JPG
    109 KB · Views: 20
Last edited:

Leodis

Yearling
Interesting spreadsheetbettor spreadsheetbettor . I have been working on something similar for about ten years and just about got it to something like I dreamed of :) would be willing to post up the odd match analysis when the season gets going.

Be wary of football data as they chop and change the column data without notice. I have lost count of how many times it has f#####d up my sheet!
 
Leodis Leodis. Thanks for showing an interest. Apologies for the delay in replying but I missed this message.

I have to admit that I have been working on versions of these for a similar amount of time, although I rewrote them this year to include macros.

I look forward, with much interest, to when you share whatever you have.
 

Leodis

Yearling
Leodis Leodis. Thanks for showing an interest. Apologies for the delay in replying but I missed this message.

I have to admit that I have been working on versions of these for a similar amount of time, although I rewrote them this year to include macros.

I look forward, with much interest, to when you share whatever you have.
Hi spreadsheetbettor spreadsheetbettor , the sheets need a few games to function properly so will start posting some games two or three weeks into the season
 
Top