Results 1 to 17 of 17

Thread: Looking for a specific stat

  1. #1
    Novice
    Join Date
    Dec 2014
    Posts
    16
    Thanks: 1
    Thanked 0 Times in 0 Posts

    Default Looking for a specific stat

    Hi,

    So I have been looking a very specific stat which is how often there were an another goal in the match after goal in first 0-30 minutes. I have googled and tryed to find that kind of a stat but without success. I would appreciate any help in finding this stat if there is one.

  2. #2
    Forum Addict Wednesday 13's Avatar
    Join Date
    Oct 2009
    Location
    Montenegro
    Posts
    331
    Thanks: 229
    Thanked 359 Times in 169 Posts

    Default

    You should check www.CGMBet.com ; they offer free Excel database with some 100.000 matches that include time of each goal, and software to manipulate those data; I can't remember now if you can get those specific stats from within the program, but if not, you can manipulate it through raw data in Excel. (Times are given as minute of the goal in 9 columns for home team and 9 columns for away team; so, you should add auxiliary column to check if there was a goal within 30 minutes, yes/no, and if yes, then check if there was another goal, which answers your question).

  3. #3
    Novice
    Join Date
    Dec 2014
    Posts
    16
    Thanks: 1
    Thanked 0 Times in 0 Posts

    Default

    Wednesday 13 thanks for that link. I have been looking for awhile now for databases for football where I can find as much data as possible especially with time shoving when the event happened (goal, card etc.). So I would be very thankful if somebody could help me find data sources or atleast where look for them?

  4. #4
    Forum Guru doubleback's Avatar
    Join Date
    Oct 2009
    Posts
    3,365
    Thanks: 3,294
    Thanked 4,994 Times in 2,061 Posts

    Default

    Another link for your perusal ...

    http://www.geekstoy.com/forum/showth...ith-goal-times

  5. The Following User Says Thank You to doubleback For This Useful Post:
    Wednesday 13 (6th January 2015)
  6. #5
    Novice
    Join Date
    Dec 2014
    Posts
    16
    Thanks: 1
    Thanked 0 Times in 0 Posts

    Default

    Doubleback, thanks. I just found almost what im looking for from www.betoutlet.net for example the first goal I can find exactly what i need but that website doesnt seem to update the match list very often since newest match from EPL is 11.09.2014 for Arsenal and also the stats are not really 100% accurate. Is there any other sites like betoutlet or any excel sheets or anything like that which would provide stat and if possible other stats as well as i described in my first post? About that www.CMGbet.com I'm probably just thick or something but I cant find/manipulate the tables in a way to get the stats im looking for so if Wednesday 13 or someone could explain for me in detail how to get the stat out of the CMG? Do I need to be VIP at CMG to get the stat or? I would appreciate help.

  7. #6
    Forum Addict Wednesday 13's Avatar
    Join Date
    Oct 2009
    Location
    Montenegro
    Posts
    331
    Thanks: 229
    Thanked 359 Times in 169 Posts

    Default

    Quote Originally Posted by Aleh00 View Post
    I cant find/manipulate the tables in a way to get the stats im looking for so if Wednesday 13 or someone could explain for me in detail how to get the stat out of the CMG? Do I need to be VIP at CMG to get the stat or? I would appreciate help.
    I think you just need to register to be able to download the data, and it's free. Once you register, go to "Archive" page, and there you can find Excel files all the leagues, for this season and past seasons.

    Let me show you how to get the data you're after, on example of Premier league downloaded from CGM; it states that last update was on 04.01.2015, and indeed it contains data for all matches so far (total of 200), and also fixtures for next matchday.

    Here is the file after "manipulation": http://www.betgps.com/files/CGM-GeeksToy.xlsx (right click and "Save as", it's Excel 2007, ~100 kB).

    Here is what I did:

    Goal minutes in original file are in columns V to AM; however, they are formatted as text and preceded by P for penalty or O for own goal; so I got rid of texts and got clear numbers in columns AO to BF. Column BH shows time of first goal. Then, column BI checks if the conditions you look for have been met: time of first goal before (and including) 30 minute, and more goals after that (which means, FT goals over 1.5); if conditions were met, it displays "Yes", otherwise "No"; columns BJ and BK then just summarize those answers, and it appears that your conditions were met in 95 out of 200 matches so far. Column BL shows number of 0-0 matches, just for information.

    You said 30 minute, but I guess you may want to check other lines, so I didn't "hard-code" that limit of 30 minute - if you want to check it by any other minute, just replace number 30 in cell BI1 (blue one) with desired value. Please note that selected minute is included in stats, i.e., when it's 30, it includes goals scored in 30 minute (if you don't want it, just type 29 instead of 30, or replace sign "<=" within formula with "<", whatever you prefer).

    When you download new updated file or another league, I think it's easiest if you add new matches to the bottom of this sheet, and copy formulas down. You may also copy formulas from this sheet into that new one. If you intend to use this frequently, it's maybe easier if you create one "master file" that will draw the data from downloaded sheet; I mean, file from CGM is named "england-01-current.xls", so you just download and open it, and other book extracts data from it, so you don't have to copy anything.

    Now, what worries me here; CGM file has column "Score flag" which says if data on goal minutes are reliable ("True") or not ("False"); I see unusually high number of "False" flags, I don't know why; one would expect it in obscure leagues, but not in Premier league. Though, I've checked several matches with flag "False", and all minutes are correct, so I don't know if "False" flag is mistake or something else... Will try to contact him, I'll let you know if I have any info on this...

  8. #7
    Novice
    Join Date
    Dec 2014
    Posts
    16
    Thanks: 1
    Thanked 0 Times in 0 Posts

    Default

    Wednesday, thank you. The file was in Excel 2007 (.xlsx like you said) and Im using OpenOffice but I finally managed to get sheet working on OpenOffice but I started to think that there is one thing missing from this sheet which I accidently didnt say in my first post. That is that now the sheet shows if there were goal for example in under 30minutes, and if there were an another goal, the BI column show yes or no. Problem is that sheet doesnt show if the first condition (goal in under 30 minutes) were met but the second condition (more goal) werent met and therefore I got no idea how often there were no more goals when the first goal was scored in 30 minutes. Got any idea how to get that showing as well in the sheet? Once again Wednesday for so much help you have given me already and the time you have used for that help.

  9. #8
    Forum Addict Wednesday 13's Avatar
    Join Date
    Oct 2009
    Location
    Montenegro
    Posts
    331
    Thanks: 229
    Thanked 359 Times in 169 Posts

    Default

    You welcome!

    Well, it wasn't difficult to adjust the sheet; you can download it from the same link above, it's grown up to 140 kB now.

    Column BI shows if there was a goal untill preset time (30 minute in this example, please note once again: including 30th minute)
    Column BJ shows if there was another goal in case that first goal was scored untill that time. So, if there was no goal within 30 minutes, it shows nothing - it only shows if there was another goal in case that first goal went in untill preset time.

    Column BK shows total count of matches where goal was scored untill preset minute.

    Column BL shows count of matches where goal was scored untill preset minute and there were more goals after that (FT goals > 1.5).
    Column BM shows count of matches where goal was scored untill preset minute and there were no more goals after that (FT goals exactly 1).

    So, so far there was a goal untill 30 minute in 102 out of 200 matches. Out of those 102 matches, 95 matches saw more goals, while in 7 matches there were no more goals, i.e., result remained 1-0 or 0-1.

    Hope I understood it well now, and this is what you were after?

    These are interesting findings, so I did two more columns for each of these parameters: percent and fair odds, and also did the same for Premier league archive file, that contains previous 10 seasons; you can download it from here: http://www.betgps.com/files/CGM-Geek...L-archive.xlsx , it's about 2.5 MB. I have no time to dabble with other leagues, but if you want, you can download them and just copy-paste the formulas in first row and copy them down as needed; just pay attention if sequence of columns is the same in all leagues, i.e., if all leagues have the same data in same columns, otherwise, of course, it will not be correct, you need to adjust them.

    It's interesting that in this archive file there are only 8 false flags out of 3800 matches, so I'm inclined to believe that high number of these flags in current season file is sort of a mistake.

  10. The Following User Says Thank You to Wednesday 13 For This Useful Post:
    Aleh00 (8th January 2015)
  11. #9
    Forum Addict Wednesday 13's Avatar
    Join Date
    Oct 2009
    Location
    Montenegro
    Posts
    331
    Thanks: 229
    Thanked 359 Times in 169 Posts

    Default

    Quote Originally Posted by Wednesday 13 View Post
    Now, what worries me here; CGM file has column "Score flag" which says if data on goal minutes are reliable ("True") or not ("False"); I see unusually high number of "False" flags, I don't know why; one would expect it in obscure leagues, but not in Premier league. Though, I've checked several matches with flag "False", and all minutes are correct, so I don't know if "False" flag is mistake or something else... Will try to contact him, I'll let you know if I have any info on this...
    OK, I've just got feedback from him, he confirmed it was an error and data were correct.

  12. #10
    Novice
    Join Date
    Dec 2014
    Posts
    16
    Thanks: 1
    Thanked 0 Times in 0 Posts

    Default

    That improved sheet you made is stats wise exactly what I was looking for so thanks you very much for the help Wednesday . I also managed to make the excel files for each league with that your example file you made. I need to try some how make the sheet a bit easier to find each teams stats in row to make the in-play usage easier. Good to know also that the "false" flags were just an error. I also checked couple "false" flags and the data in sheet were correct so I thought too that it was just some error. This is a bit off topic but I was trying to find some excel/OpenOffice guides on how to play around with the sheet (functions) but couldnt find any guides without the "pig latin" that the excel functions are to me. Is there any kind of a noob friendly excel/OpenOffice guides? I mean the example sheet you made Wednesday, I wouldnt have been able to do it on my own so it would be nice to widen my knowledge on excel and/or OpenOffice and I hate to be asking someone all the time to sacrifice their time to make me an example sheets for me to copy .

  13. #11
    Forum Addict Wednesday 13's Avatar
    Join Date
    Oct 2009
    Location
    Montenegro
    Posts
    331
    Thanks: 229
    Thanked 359 Times in 169 Posts

    Default

    You welcome, glad we sorted it out!

    Quote Originally Posted by Aleh00 View Post
    This is a bit off topic but I was trying to find some excel/OpenOffice guides on how to play around with the sheet (functions) but couldnt find any guides without the "pig latin" that the excel functions are to me. Is there any kind of a noob friendly excel/OpenOffice guides? I mean the example sheet you made Wednesday, I wouldnt have been able to do it on my own so it would be nice to widen my knowledge on excel and/or OpenOffice and I hate to be asking someone all the time to sacrifice their time to make me an example sheets for me to copy .
    Well, there are a lot of tutorials online and offline that can gradually introduce you to Excel, from the begginners level. However, I don't see anything wrong in asking in various sites online - I did it myself many times when problem was specific so I was not able to find the solution myself or by Google. There are numerous forums online, and my favorite ones are excelforum.com and mrexcel.com. You may also want to check chandoo.org, theexceladdict.com and computergaga.com, as well as Excel section at stackoverflow.com, also excellent place to ask the questions.

    Those people who answer your questions do that voluntarily, nobody forces them to do so, so they don't waste their time, they do it for various motives - I for one take it as a challenge in my spare time, "Hmmm, let's see if I can do it!". If you asked your question one month ago, or in one week, you would probably not hear from me - I'm on vacation these days, and weather is bad, so I was pretty boring, and your question just came as a little exercise for me, and another item for my collection of betting spreadsheets for my site. I guess it's similar for other people who answer questions like this - nobody is forced to spend the time, everyone has his own motives behind it, so you don't need to worry about it!

    So, my advice is first to google your question, and if you don't manage to find the answer or adjust it to your specific problem, then go to the forum and ask! The only thing I would advise is not to post it simultaneously in multiple places, except if it is something urgent - post it in one forum, and if you don't receive the answer in several days to one week, then post it in another place, and so on.

    When I said "google your question", I meant question in simple words; let me tell you the example: for this spreadsheet here, I needed to rank cells that contained text; Excel's RANK formula ranks only the numbers, but I was sure there is a workaround, so I googled "how to rank text in Excel"; sure enough, I got hundreds of results that explain it in layman terms.

    Quote Originally Posted by Aleh00 View Post
    I need to try some how make the sheet a bit easier to find each teams stats in row to make the in-play usage easier.
    Well, as I said, I'm pretty bored today, so I played further with this sheet, and added two more features; new workbook is available at http://www.betgps.com/files/CGM-1stgoal.xlsx (right click and "Save as", Excel 2007, ~600 kB).

    However, I don't use Open Office, and I have no clue if the formulas I used here will work for you...

    As you look for team stats, I added another sheet, "Summary"; there you can select two teams from drop-down list, and stats for all their matches will be displayed - whom they played, time of 1st goal, FT result, whether your conditions were met, and 1X2 and Under/Over 2.5 odds. As you mentioned in-play usage, and it appears majority of matches where 1st goal is scored before 30 minute see more goals, I thought you would like to know how long you would wait for 2nd goal, so I added it, too - time of 2nd goal. If you don't want to see all these data, just hide the columns.

    Matchdays are ordered with the newest on the top; as CGMBet seems to offer not only past results, but also fixtures for next matchday, that cell will turn yellow if the date is in the future, so you know it is fixture, not result.

    Cell where you set minute of first goal has been moved to this new sheet, you enter it in blue cell in top right corner. Below that is list of teams, and below the list, there is stats summary for the league.

    The other new feature is update of the stats; so far, you had either to copy formulas into newly downloaded file, either to copy data from downloaded file onto this sheet, and then copy formulas down the sheet; it's pain in the back to do it for many leagues and after every matchday, so I use another method. In my previous post I mentioned "Master sheet", where you don't need to update anything, you just use it to extract data from the source sheet, which is, in this case, sheet downloaded from CGMBet.

    Here is how it works: files downloaded from CGMBet have the names according to the league: "england-01-current.xls", "england-02-current.xls", "spain-01-current.xls", etc., and sheets within those files have the same name. In order to use it with the same "master file", we need to "unify" it, i.e., to use always the same name, so master sheet can recognize it.

    So, download a file from CGMBet, for example "england-01-current.xls"; rename it to "source.xls"; open it, and rename the sheet from "england-01-current" to "source", too. Pay attention, all small letters. If it's not in the same folder as master sheet, place it in the same folder, open it, and then open master sheet (i.e., this workbook I provided above, "CGM-1stgoal.xlsx"). And that's it - adjust time limit for 1st goal if you want, choose desired teams from drop-down list, and read the stats.

    Depending on your settings, you may get the message "This workbook contains links to another workbook", and you should allow it to update the data.

    If you want to do it for another league, first close both master sheet and "source.xls", and then just delete this existing "source.xls" (or rename it back to its originbal name), download new file for another league, and repeat the process above - rename new file and sheet to "source", place it in the same folder as master sheet, open it and then open master sheet. You will see "#N/A" errors within tables, but that's because you need to select teams from drop-down menu - cells still show teams from previously selected league.

    I said to first close master sheet when switching the league. If you leave it open, and then delete or rename sheet "source.xls", or open master sheet and there is no "source.xls" with sheet "source", master sheet will lose reference to source data, and will stop working - renaming the file back to "source.xls" will not help, and master sheet will be irreversibly damaged; the only thing you can do is to download a new copy from the link above (or better, create a backup copy yourself, or even much better: repair the references yourself: damaged references are located on sheet "data", in cells from A2 to AM553; original formula in A2 is "=IF(source.xls!A2="","",source.xls!A2)"; damaged one reads "=IF(REF!A2="","",REF!A2)"; just rewrite the original one, and copy it accros the range A2 to AM553; copy just the formulas (paste special - formulas), otherwise you will need to format column B as date again).

    Actually, if you plan to use it much, I think it's easiest if you create a separate folder for each league, so to have one master sheet for each league, and you can just download updated file for that league, delete the old one, rename the new one, and there you go.

    Now, what are the possible flaws to beware of.

    First, I've checked CGMBet's files for several leagues, and they all contain the data ordered in the same order (league, date, hour, season, round, home team, away team,..., and so on), in columns A to AM. If CGMBet in the future decides to change the way he displays these data (switch the columns, remove some of them, add new ones, etc.), master sheet, of course, will not work; so, in that case, you will need to update the master sheet; for example, home team is located in column F, so master sheet in row 2 contains the formula "=IF(source.xls!F2="","",source.xls!F2)". If CGMBet moves it to another column, you will need to update this reference accross the entire range.

    Second, list of teams, located in master sheet, "Summary", on the left side, is extracted from matchday 1 - first matchday of the season usually takes place as scheduled. In (rare) case that some match(es) in round 1 get postponed, list will not be correct, so tables will not work; you need then to manually correct the list and add missing teams (in which case you cannot use that master sheet for other leagues any longer - as you entered the teams manually, it will not update when you switch the league; that's also why I'd create a separate folder and its own master sheet for each league).

    BTW, sheet handles leagues of up to 24 teams (which is 552 matches a season).

    I'm not aware of other possible flaws, I've checked data for several leagues and teams, and they are correct; still, please check it yourself once more, as I cannot accept responsibility for losses you may incur due to possible errors in this sheet... :\

  14. The Following User Says Thank You to Wednesday 13 For This Useful Post:
    ForeTwo (9th January 2015)
  15. #12
    Novice
    Join Date
    Dec 2014
    Posts
    16
    Thanks: 1
    Thanked 0 Times in 0 Posts

    Default

    I managed to get that new sheet working with the current season data on every league but with archive + current season data, the sheet aint working, probaly because there is more teams and matches. Problem is probably going to be the fact that every leagues archive + current season data is going to happen different number of teams and matches. But overall that new sheet you made is excellent and would make it alot easier to get the needed stats out in play.

  16. #13
    Forum Addict Wednesday 13's Avatar
    Join Date
    Oct 2009
    Location
    Montenegro
    Posts
    331
    Thanks: 229
    Thanked 359 Times in 169 Posts

    Default

    Yes, you're correct - it is intended to work with a single league and single season file. It would be a bit more complicated to get it working with multiple leagues and seasons, not sure I can dabble with it now.

    As for previous seasons for a single league, you can split archive file to separate seasons, and then this will work for them, too. I mean, archive file for Premier league contains 10 seasons in rows 2 to 3801; split them to 10 files, so always to have matches in rows 2 to 381, and that's it.

  17. #14
    Novice
    Join Date
    Dec 2014
    Posts
    16
    Thanks: 1
    Thanked 0 Times in 0 Posts

    Default

    Quote Originally Posted by Wednesday 13
    Yes, you're correct - it is intended to work with a single league and single season file. It would be a bit more complicated to get it working with multiple leagues and seasons, not sure I can dabble with it now.

    As for previous seasons for a single league, you can split archive file to separate seasons, and then this will work for them, too. I mean, archive file for Premier league contains 10 seasons in rows 2 to 3801; split them to 10 files, so always to have matches in rows 2 to 381, and that's it.
    I separated the archive's and the sheet seems to work fine. I was thinking that reversing this first goal stats could also be quite usefull. Stat from which you could see how often there were no goal (match end 0-0) when there has been no opening goal before xx mins for example before 30 mins. That kind of a stats could be usefull in play and also when analysing match pre match. I dont know how hard this kind of a stat would be to do but If possible it would be great if you could do example sheet (like earlier) where I would just copy it and put it into archive file.

  18. #15
    Forum Addict Wednesday 13's Avatar
    Join Date
    Oct 2009
    Location
    Montenegro
    Posts
    331
    Thanks: 229
    Thanked 359 Times in 169 Posts

    Default

    Hi, Aleh; I'm glad you got it sorted for archive files. As for the new request, it's not difficult to do, but I'm back to work and pretty busy, so I'm not sure when will I have spare time to complete it. I'll post it here as soon as I do it, or maybe someone else might do it in the meantime...

  19. #16
    Novice
    Join Date
    Dec 2014
    Posts
    16
    Thanks: 1
    Thanked 0 Times in 0 Posts

    Default

    Yeah, thats okay since its would be just a nice bonus when analysing some matches. If you have some spare time at some point.

  20. #17
    Forum Addict Wednesday 13's Avatar
    Join Date
    Oct 2009
    Location
    Montenegro
    Posts
    331
    Thanks: 229
    Thanked 359 Times in 169 Posts

    Default

    OK, here it is: http://www.betgps.com/files/CGM-1stgoal.xlsx (right click and "Save as", Excel 2007, ~700 kB).

    Now, you will see another sheet there; "Summary" contains lague related stats, while "H2H" contains stats between two teams, as earlier. You set the time limit on sheet "Summary", and it is displayed in H2H sheet, too, for convenience.

    Quote Originally Posted by Wednesday 13 View Post
    Matchdays are ordered with the newest on the top; as CGMBet seems to offer not only past results, but also fixtures for next matchday, that cell will turn yellow if the date is in the future, so you know it is fixture, not result.
    That is pretty annoying, as those matches have result 0-0, so it gets counted. I slightly adjusted my sheet, and I can propose following workaround: when you prepare sheet "source.xls", delete results from fixtures, i.e., from matches that have not been played yet. They are located in columns H to K. That way, they will not count towards entire stats, while you will be able to see the fixtures in H2H sheet. Note: delete results from workbook "source.xls", not from this workbook!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Talented Mavericks Limited
26 Buttercup Close
Corby
Northants
England
NN18 8LB

UK Company # 04872848
UK VAT # 887366268

Facebook TwitterYouTube



Betfair    Betdaq    VISA  Mastercard  American Express  Dinners Club  Discover  PayPal  Skrill