How I keep my APBA replay stats Part I

I thought I’d give you a little glimpse into how I’m keeping track of stats for my 1966 NL replay.  I know not everybody uses MS Excel for statkeeping.  Some use BallStat and others do it by hand.  BBW players don’t even have to worry about stat keeping.  They’re the lucky ones.

…or are they?

Maybe it’s me but I enjoy this part of doing a replay.  Using Excel (for that matter, this article can apply to any spreadsheet software), gives me a sense of setting it up the way I want.  I’m constantly tweaking my spreadsheets and adding different features to it.  By the way, a lot of credit for this goes to my friend Brando and also our league commissioner Mike Bunch.  Both have given me great ideas that I’ve used. 

A disclaimer of sorts:  I like using Excel but I’m certainly no expert in data manipulation.  If I was, I’d be doing actual database programming (like TBL manager Steve Stein who works magic). 

Also, the purpose of this is to give an overall view of how I set things up for my stats.  If the feedback on this is receptive, perhaps I’ll get into more nitty-gritty details in future articles. 

Getting Started:  The team page

The premise for my stats setup is simple though like I said, I tend to add features as I see fit.  I have a separate Excel file for each team in my replay.  In addition, I have a Master spreadsheet which very little data is entered.  The Master spreadsheet is used to collect and display the data in a league form.

[The MS Word document you see listed with the rest of the files at left is simply the score sheet I use.  It’s available for download here.] 

The idea behind all this is that I only need to enter in data once.  Using Excel’s linking feature, I can update league totals, team totals and standings automatically.

For the first article in this series, I’m going to focus on just the team spreadsheet.  In this case, I’ll use the Chicago Cubs for an example.  I have a separate worksheet (or tab) for each hitter and for each pitcher.  For each hitter, I have a table with columns for each stat that I’m keeping.  Each row represents every game they play usually denoted by the date of the game. 

The second row below the header row is a sum of the that players’ stats for the season.  Most of these totals are arrived using Excel’s SUM function.  Averages like batting averages, slugging average and OBP will be arrived differently.  Here’s a small peek at Glenn Beckert’s hitting page:

Note:  I used to have the totals row way down at bottom of the page where I couldn’t see them.  I admit, I love watching stats change automatically as I enter the numbers (especially averages!) so I moved it up to the top row where I can see them.

Way back when, I used to just keep totals and update them every so often.  Now, I enter them by game by game.  Why?  It’s good way to see trends.  For example, with this method, you can see hit streaks or consecutive scoreless innings. 

Once all the hitters are done, I created a worksheet for all the pitchers for the Cubs with the relevant pitcher’s stat categories in the same fashion.  Here’s a snippet of starting pitcher Al Jackson’s pitching sheet:

Now, here’s where linking starts to come into play.  I create another worksheet at the beginning of the chi1966.xls spreadsheet called ‘Chicago Stats’.  This is where I can go if I want a good look at how the Chicago Cub players are doing as a team.  To do this, I create a table in this worksheet with the same stat categories (I just copy and paste the row).  I then copy each players’ totals and ‘Paste as link’ to correspond with their name.  That way, whenever they are updated, those changes are reflected in the Chicago Stats worksheet. 

At the bottom of both the hitters’ and pitchers’ section, I’ve totaled up the team’s stats. 

It looks something like this:

I haven’t done this but one could easily rename the worksheet tabs (see image below) with the names of the players that they represent.  Because I’m lazy, I just chose to keep them named numbers.  By the way, you’ll notice a ‘Schedule’ worksheet tab.  I’m going to hit on that topic in a future article.  It’s a fun little thing I came up with. 

Wrapping up the team spreadsheet

This all might seem like a lot of work but it really isn’t if you don’t re-invent the wheel.  Once I created Beckert’s worksheet, I copied it and used it for Billy Williams and all the other Cubs players (making sure to edit the relevant data, of course).  There’s no need to re-create the formatting or formulas, though. 

Furthermore, once I get the chi1966.xls spreadsheet done, I can copy that entire file and rename it phi1966.xls and use it for the Phillies.  Once again, I would make sure to change the relevant data.  I find it helpful to paste the name as a link from the player’s sheet to the team sheet,  That way I can re-type the name and even the name changes at the top will be reflected. 

For the record, I use a modified version of this team spreadsheet to keep stats for my APBA league as well. Our commissioner has a standardized spreadsheet he has all league members use to send stats.  I just imported it in as a worksheet and linked my players’ stats to the corresponding cells.  When I’m done with my stats, I make a copy of that worksheet and send that to him. 

In a couple days, I’ll show how I set up my Master spreadsheet.  That’s where all the fun begins.  With almost all of the data entered in the team’s spreadsheets, the Master spreadsheet is simply a link to the data found there. 

Enough talking about stats for now.  Gotta play some games tonight.