Soccer Poisson database

Football, Soccer - whatever you call it. It is the beautiful game.
Post Reply
User avatar
Derek27
Posts: 23475
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

Hi All,

I've created an Access database for storing soccer results and spitting out Poisson spreadsheets for individual matches. If anyone wants to try it here are the instructions:-
  • Download the two attached 7z files and extract their contents to a suitable location. They were just slightly over the single file limit. They contain the following files:-
    • Soccer Poisson.accdb - Front end database containing the forms and code.
    • Soccer Poisson_BackEnd.accdb - Backend database containing empty tables for storing match data.
    • 4 CSV files - Sample files containing current league results for importing into the database. I created 3 of them from data downloaded at https://fixturedownload.com
    • Soccer Poisson.xltm - Excel template for outputting match data, must be stored in the same folder as Soccer Poisson.accdb
  • Open your Access VBA editor and make sure the 6 reference libraries below are selected, otherwise the code won't compile.
Capture.GIF
  • Make a copy or copies of Soccer Poisson_BackEnd.accdb, one for each league you're interested in.
  • Open Soccer Poisson.accdb, the 'Connect Database' form should automatically open.
  • Click ADD, select your backend database file and enter a name for your league.
  • Select the database from the list box and click connect or double-click on the list box. You may receive four security warnings, one for each table that gets linked. You can then close the database connect form.
The rest should be self-explanatory. The objects in the navigation panel are as follows:-
  • Database
    • Connect Database - Allows you to add, connect and remove backend databases.
    • Import Matches - Allows you to import the sample CSV files. If you wish to create your own, the CSV must be without a header and in the form:

      [D]D/[M]M/[YY]YY, home team, home score, away team, away score
    • Teams - Allows you to enter team names if you want to enter data manually at the start of a league. If you import results you may want to edit the team abbreviations. By default, West Brom and West Ham both abbreviate to WEST, and Arsenal becomes ARSE!
  • Database Edit
    • Results Entry - Manually enter results
    • Last 7/30 Days and All - Allows you to view, edit and delete individual matches
    • Delete Matches - For deleting old matches before a selected date
  • Information
    • League/Home/Away Table - Displays league tables. Can be used for verifying the accuracy of the match data.
    • Calculate Poisson Dist - Allows you to select two teams and produce a Poisson spreadsheet.
The database code calculates the expected goals based on this smarkets article and writes it to an Excel workbook (a separate sheet for each match), in cells that you can edit and adjust to suit your own assessment of any match. All the Poisson formulas are in the spreadsheet, not the database code. There's a cell (F6) for entering your own price for the draw if it's way off the mark and the home/away prices will adjust but the CS and U/O prices will be unaffected. I've used a crude formula for estimating the average likely time of the goals but you can enter your own formula and save it in the template. (You can put these goals into Soccer Mystic when assessing the over/under markets).

Note for newbies: Don't have high expectations of these spreadsheets. It's just a tool that I made mainly as a toy to play with. It may help to give insight into a match or find inconsistencies in the market but it's certainly not designed to accurately predict what the odds should be. :)
You do not have the required permissions to view the files attached to this post.
Last edited by Derek27 on Sun May 16, 2021 6:04 am, edited 2 times in total.
User avatar
Derek27
Posts: 23475
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

This is what one of tonight's matches looks like. (I see you can only attach three items per post!)
Capture2.GIF
You do not have the required permissions to view the files attached to this post.
User avatar
Realrocknrolla
Posts: 1903
Joined: Fri Jun 05, 2020 7:15 pm

Thanks for posting Derek.

Something that can help me refine my football trades.

TOP MAN!
User avatar
MemphisFlash
Posts: 2126
Joined: Fri May 16, 2014 10:12 pm
Location: Leicester

looks good - nice use of colours , :D
User avatar
Dallas
Posts: 22673
Joined: Sun Aug 09, 2015 10:57 pm
Location: Working From Home

Image

Thanks for sharing with the community
jamesg46
Posts: 3769
Joined: Sat Jul 30, 2016 1:05 pm

Nice job, thank you for sharing!
User avatar
xtrader16
Posts: 411
Joined: Sun Feb 26, 2017 1:00 pm

Wow, this looks amazing. Great job and very generous for sharing it, looks like it would have taken quite a while to build. Thanks.
User avatar
ilovepizza82
Posts: 490
Joined: Thu Nov 02, 2017 3:41 pm
Location: Sewers
Contact:

Thanks Derek ! Great stuff !
However according to https://www.infogol.net/en/matches/engl ... 5-16/70767
West Brom xG is: 0.76 and Liverpool is 2, not 2.97. Same thing on Soccerstats with Liverpool having 2.
Why sucha discrepancy ?
User avatar
Derek27
Posts: 23475
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

ilovepizza82 wrote:
Sun May 16, 2021 12:36 pm
Thanks Derek ! Great stuff !
However according to https://www.infogol.net/en/matches/engl ... 5-16/70767
West Brom xG is: 0.76 and Liverpool is 2, not 2.97. Same thing on Soccerstats with Liverpool having 2.
Why sucha discrepancy ?
I can't find where it lists the expected goals but I'd guess, if they're using the same formula they may have a different dataset. The sample CSV just goes back to the start of the season.
Trader Pat
Posts: 4327
Joined: Tue Oct 25, 2016 12:50 pm

Good effort Derek. You must have been burning the midnight oil on that!
User avatar
Derek27
Posts: 23475
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

ilovepizza82 wrote:
Sun May 16, 2021 12:36 pm
Thanks Derek ! Great stuff !
However according to https://www.infogol.net/en/matches/engl ... 5-16/70767
West Brom xG is: 0.76 and Liverpool is 2, not 2.97. Same thing on Soccerstats with Liverpool having 2.
Why sucha discrepancy ?
Got it! Those figures are the average goals for this season rather than expected goals, which takes into account averages and the opponents stats. While I was checking the calculation I noticed this year in the Premiership there's been 2 more away goals than home goals! I guess covid has taken away the home advantage.

Avg home goals: 1.340845
Avg away goals: 1.346479
Avg home team goals:0.7647059
Avg home team goals conceded:2
Avg away team goals:2
Avg away team goals conceded:1.235294
HTAS: 0.5703164
ATDS: 0.9212803
Expected home goals: 0.7045085
ATAS: 1.485356
HTDS: 0.9212803
Expected away goals: 2.970711
Last edited by Derek27 on Sun May 16, 2021 2:36 pm, edited 1 time in total.
User avatar
Derek27
Posts: 23475
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

Trader Pat wrote:
Sun May 16, 2021 2:26 pm
Good effort Derek. You must have been burning the midnight oil on that!
After doing a lot of scalping I developed a (goal waiting) strategy that allows me to pop out for a fag while trading, or get some other work done. A few 0-0 draws helped it along. :D
jamesg46
Posts: 3769
Joined: Sat Jul 30, 2016 1:05 pm

Is it normal with Poisson for the probability of the outcomes to be less than 100%? Prices seemed to be pretty accurate but there is 3.21% of floating probability on this match.
Archery1969
Posts: 3192
Joined: Thu Oct 24, 2019 8:25 am
Location: Newport

Hi Derek,

Nice work.

Any reason why game lenght is 96 instead of 90 ?

Cheers,
User avatar
Derek27
Posts: 23475
Joined: Wed Aug 30, 2017 11:44 am
Location: UK

jamesg46 wrote:
Sun May 16, 2021 4:36 pm
Is it normal with Poisson for the probability of the outcomes to be less than 100%? Prices seemed to be pretty accurate but there is 3.21% of floating probability on this match.
The 'any other' home/draw/away correct score only counts probabilities of either side scoring up to 6 goals, so in a one-sided match there may be a bit missing off the favourite.
Post Reply

Return to “Football trading”