As I’ve begun my little 21-game project between the Monday Monsters and the Tuesday Terribles, I of course wanted to keep stats. That was part of the fun, right? To establish how bad the Terribles were as opposed to the mighty Monsters.
But did I really want to re-invent the wheel? Did I have to re-create a whole new stats environment within Excel for a simple 21 game project?
Fortunately, no. I really just had to copy one team spreadsheet from my current 1966 replay stats setup and use that. I made a copy and renamed it to something more appropriate. Then I cleared all stat data from it including players’ names. Once I started filling in the names and stat data from the MCM-TCT project, it worked like a dream.
Below, I’m linking my team spreadsheet that I’m using for the MCM-TCT project. I believe I’ve made a version of this available before but since then, I have refined it a bit and made some revisions that make it work for me a lot better.
Before I go on, I know a lot of you have used BallStat and are quite happy with it. It honestly sounds like quite a program and I tell myself I really should try it some time just so I can properly review it. For those interested, Jeff Pappas did review of BallStat on The APBA Blog a while back. For now, I’m pretty tied to my method of keeping stats using Excel simply because I can customize to my needs. And quite honestly, I like to tinker. Just playing with the method of keeping stats is part of the fun for me.
A quick look
Because the Monsters-Terribles project is a little unconventional to put it mildly, I’ve created a spreadsheet with some fictional names who play for the “Hometown Champions” and some random stats in the sheet just so you can get a sense of how it works. You can delete the stats from the individual players’ worksheets and use it how you wish.
Note: when you delete the stats for your own use, delete them from the player’s worksheet NOT from the Team worksheet.
The players entry page:
With the system that I use, each file represents one team and each player has his own worksheet within the spreadsheet. Almost all stat entry takes place in these worksheet pages.
One of the features I added was a instant total line at the top so it gets immediately calculated as you enter in stats. It certainly wasn’t necessary but I liked it. Also, the formatting of the ‘Game’ column can be in number format as shown above or date depending if you’re doing a tournament/short season or a more strict season replay.
As I mentioned, each player and pitcher on the team has his own worksheet. Here’s an example of a pitcher’s worksheet.
Now on to the Team sheet. Once I got this worksheet set up the way I wanted it, I don’t edit this sheet in any way. All data is linked to this sheet from the individual players’ sheets described above. If you add a game’s stats to Jerry Ribbie’s sheet for example, his totals immediately and automatically get updated on this sheet. This is how it looks with just three hitters entered…
…and if you want add another hitter, you can do so. If you navigate to the sheet just past Homer Batta (by the way, Control-Page Down or Page Up is very handy in doing this), you can enter the new player’s name and stats and they will show up on the Team sheet in the row right under Homer Batta.
For both hitting and pitching, the team stats automatically get updated and displayed below the players’ stats.
You’ll note that I have a column for Team and have the team initials “HC” (for Hometown Champions) in every field. Why would I do that if everyone is on the same team? Well, if I am doing a league or tournament project, I will usually link the stats from the team sheet to a master spreadsheet. Doing this will allow me to carry the team affiliation along with the player. This is handy for leaderboards and such. See the example pivot table from my Monsters-Terribles project at right for an example.
That is definitely a topic for another article but if you are interested, look into pivot tables. If you like to play around with Excel spreadsheets, they are a fun way to do leaderboards.
I generally calculate BA, OBP, SLG and ERA on the team sheet but if you want, you can insert any formula you want. There’s K/9 IP, BB/9 IP, K/BB ratio, it’s really up to you. I do a lot of these on the fly.
Download the Spreadsheet
That should be enough to get you started if you’re interested in trying this out. Here is the link to the actual Microsoft Excel file I’ve been referring to in this article. If you like it, feel free to use it. Just clear out the fictional names and data from the individual worksheets and put in your own names and stats from your project. You can use the spreadsheet on its own or as I’ve mentioned above, link it to a master league spreadsheet which totals the league stats, puts together a players registry and calculates leaderboards. I go into a little detail on this with a series of articles I did a while back.
If you have any questions, ideas or suggestions, please leave a comment. I hope this is helpful to some of you out there.
A lot of credit for this spreadsheet goes to Mike Bunch and John Brandeberry. One of them (not sure which) created the template for this spreadsheet a long time ago and both them have made improvements to it before I took it and made my own changes.