Sunday, September 6, 2009

Bowling Scoring and Statistics Spreadsheet, v. 2.0


I have now updated the BM Bowling Scoring Spreadsheet to give it a number of new features. Unlike the first version, v. 2.0 has built in code (or macros), which give it the ability to do a number of new things. Unfortunately, Microsoft Excel for Macintosh no longer supports Visual Basic macros, so if you are Mac user, you won't be able to use this version unless your Mac can run Windows. Sorry. The old version should still work. (This capability will be returning to Excel for Mac in the future.)

The basic spreadsheet is identical to the first version, but it can be configured for up to 225 bowlers and more than 100 games. For a description of the basic spreadsheet, click here. If you bowl in a league with a three man team and four game series, the spreadsheet can be built this way. If you only want to track your own stats, you can do this, too.

Updates to v. 2.0:

1) Increased flexibility- Can be configured for any number of bowlers and any number of games.
2) Spreadsheet unlocked- The spreadsheet has been unlocked, so formulas can be viewed, and all cells can be selected, copied, and pasted.
3) Marking of high scores- Do you want to know who got the most strikes or spares on your team for the week? Who picked up the greatest percentage of single pin spares? The spreadsheet will automatically highlight the bowler who got the high stat for the week for many statistical categories.
4) Data archiving- the spreadsheet will store your week to week data allowing you to generate reports showing cumulative and average number of strikes, spares, and other statistics for individuals and your team as a whole over the course of a season.
5) Language translation- Do you want to translate the spreadsheet from English into another language? You can do this, too.

Here are a few more screen shots:





Obviously, I have put a lot of work into this spreadsheet, and I have given it away for free. There are no strings attached whatsoever. When I created the first version, I got an overwhelmingly positive reaction, and I received many suggestions for improvement. I have tried to incorporate all of those suggestions in version 2.0. No doubt, I will get additional ideas for changes and improvements. If you have suggestions, just post them as comments below. To support continuing development of this spreadsheet, all I ask is a voluntary donation of $10. Alternatively, you can donate whatever you can afford or think it is worth to you. This is completely voluntary, and the spreadsheet will continue to work whether you donate or not.

Thanks. -Todd

To make a donation, use the Donate button on the upper right hand corner of this blog.

To download version 2.0 of the spreadsheet, right click here, select "save link as...", and save the spreadsheet to your computer.

29 comments:

  1. Hi, my name is Tom Madrecki, an associate editor for Bowlers Journal International magazine. Just wanted to let you know that we're featuring your often hilarious blog in our monthly "Web Watch" feature next month!

    ReplyDelete
  2. That's great. Hopefully it does not cause you to lose half of your subscribership.

    ReplyDelete
  3. I am having problems archiving more than 5 games per person. Why is this?

    ReplyDelete
  4. Well, I'm not sure. Can you tell me exactly what you are trying to do, and exactly what the problem is? It is most likely that it is a problem with the code. It is possible that there is a bug or two that I haven't identified. Also, you could email the spreadsheet to me (toddsurovell@yahoo.com) with an explanation of the problem.

    ReplyDelete
  5. my younger brother and i went bowling on our college campus and were using your wonderful sheet to track our scores and averages and the like. i began running into a problem when trying to archive games above and beyond 5. it keep popping up with an error message saying that the data had already been archived and would i like to replace it. when i checked the statisics report, it only listed us as having played 5 games.

    ReplyDelete
  6. Hmmm. Well, before it archives the data, it checks to see if there has been similar data archived from the same date. If there has, the message will come up. If you press the YES button, it will archive the data. Afterward, it should work.

    ReplyDelete
  7. Another thought on the matter. You should wait until you have entered all of you scores on a single spreadsheet before you press the archive button. If, for example, you have three games on a spreadsheet, and you archive the data after entering only one game, you will have to go to the "archive" sheet, find the data you just archived, and delete it.

    ReplyDelete
  8. I'd love to track the stats on our high school team with this spreadsheet. We have regular tenpins 4 or 5 man team games (depends on the tournament) that could be 3 or 4 game series. And we also have 5 man baker game matches, best 3 out of 5. Is there a way to combine the stats for both types of games for each bowler throughout the season?

    Don't want to create more work for you. Just curious if this can be used for both. Thanks!

    ReplyDelete
  9. Hi Anon,

    Well, the spreadsheet will easily accomodate a four or five man team. All you have to do is change the number of bowlers on the "New Spreadsheet" worksheet. For the archiving functions, however, it will not differentiate between the two formats. One option would be to keep two copies of the spreadsheet for each format. As for Baker games, no, it cannot track the performance of individual bowlers, and it would be a lot of work to get it to do so. So, at the moment, I'm hesistant to change it. It will obviously score and track stats for a Baker game, but not by individual bowlers.

    -Todd

    ReplyDelete
  10. Movement2,
    I am new to bowling and have been working on building a spreadsheet or database to track my and my team's performance. Then I thought why re-invent the wheel and searched on google for a template. Voila, I found the BM site and your tool. It is an outstanding tool and I look forward to using it but when I entered our team data into the New Spreadsheet page and clicked the button to create a new sheet, I got a runtime error 438 with the comment "Object doesn't support this property or method." Do you know what this is or why I get this error message. Once I click the "end" button on the error pop-up, a new sheet is created however it only lists the first bowler's information and the gridlines on the sheet are not visible. Thanks, again for the great tool.

    -Anthony

    ReplyDelete
  11. Hey Anthony,

    I'm not sure what's going on. Did you insert any columns, rows, or cells on the new spreadsheet page? If you send it to me as an attachment at toddsurovell@yahoo.com, I could troubleshoot it for you.

    -Todd

    ReplyDelete
  12. In fact, I would very much appreciate it if you sent it to me. If there is a bug that I don't know about, I would really like to fix it. I have been using the new version for about 12 weeks, and I have not had this problem.

    ReplyDelete
  13. Movement2, no worries. I was using the V2 on an Excel 2003 computer. I switched to Excel 2007 computer and everything works perfectly.

    ReplyDelete
  14. Cool. Thanks for the reply. I really appreciate it. I wonder what was going on. I thought it worked on the older version, too. I'll look into it.

    ReplyDelete
  15. Movement2,
    By the way, when I create a new spreadsheet the charts are extremely large and are not anchored to a specific position. Is there a way to make the charts 3x6 inches and anchored just below the scoring cells? Thanks, again for the tool.

    -Anthony

    ReplyDelete
  16. Hey Anthony,

    Yeah... I know about this problem. I have tried to fix it with no success. A solution that works for me is to save the spreadsheet, close it, and reopen it. For some reason, after I do this, they are properly positioned. In the end, I may just delete them.

    -todd

    ReplyDelete
  17. Todd,
    Thanks that worked for me too.

    ReplyDelete
  18. Todd,
    Can you modify the create spreadsheet macro so that it adds a bowler's handicap to the total pin count for each game? Thanks.

    ReplyDelete
  19. Todd,
    Or can you e-mail me the passwword for the VBA module and I can edit it? Again many thanks.

    ReplyDelete
  20. Todd,
    On second thought, I probably won't be able to figure out the VBA code for a workbook as complicated as this one since I have only a very basic understanding of VBA. If you could modify it or tell me how to do it, it would be much appreciated.

    -Anthony

    ReplyDelete
  21. Sure, i can do this. Where do you think it would be best to put this information?

    ReplyDelete
  22. Either in the column W, or in the score progression chart at the bottom you could add a HDCP line, or both. And then down in the series stats also, a line that showed HDCP average score would be nice also. Thanks.

    -Anthony

    ReplyDelete
  23. I can do both, but it's going to take me a few hours. You are correct. This code is extremely complex. I should be able to get it done in a few hours. I have also added split stats to the spreadsheet, an extra bonus.

    ReplyDelete
  24. Anthony,

    I have revised the spreadsheet, so for each game it calculates individual and team handicapped and scratch scores. I have also added handicapped average to the series stats. I have also added split marking and split statistics. I will post the new version (v. 2.1) to the site in a couple of hours. I can email it to you before then if you email me at toddsurovell@yahoo.com. I don't have your email address, so i can't send it to you otherwise.

    Todd

    ReplyDelete
  25. This comment has been removed by a blog administrator.

    ReplyDelete
  26. Hey Anthony, I deleted your last comment, so your email address isn't posted, but I just posted the revised version, you can find it here:
    http://bowlmovementswy.blogspot.com/2009/12/bm-bowling-spreadsheet-v-21.html

    ReplyDelete
  27. Tried out your spreadsheet. Very nice. One problem I found was in the 10th frame, if a bowler strikes on the first ball, then splits on the second and we mark the 10th frame with a split, the Mark Splits says there cannot be a split in the 10th frame because the first ball was a strike.

    ReplyDelete
  28. I have a question...my son bowls for a college and is participating in baker style games. How can I take his frames and get an individual average from them?

    ReplyDelete
  29. Well, I'm actually working on a baker spreadsheet, but to answer your question...

    I guess I would take the outcome of his frames and string them together. Then take the average score per frame and standardize it to a 10 frame game.

    Does that make sense?

    ReplyDelete

Note: Only a member of this blog may post a comment.