How I keep my APBA replay stats Part II

A couple days ago, I started to take a look at how I do my stats for my replays.  In my first article, I began with the team spreadsheet and how I set those up for each team.  From here we’ll take a look at my Master spreadsheet.  The Master sheet took a little bit of work to set up but once it’s going, it updates automatically and I love how it works.  To be honest though, it’s never really done because it’s in my nature to tinker with it, adding cool new features to it but that’s part of the fun for me!

Before I go on though, here’s a helpful hint if you use Excel.  There’s a lot of worksheet tabs in those team spreadsheets I work with.  I keep hitting stats for each pitcher so their could be upwards of 30 or so worksheets per spreadsheet.  I don’t know about you but if I can avoid moving from the keyboard to the mouse and back again, I will.  The Control-Page Down and Control-Page up keyboard shortcut is invaluable for moving from one worksheet to the next (and vice-versa) instead of mousing down each time.  Thanks to Brando for showing me that one.  It’s a time-saver!

On to the Master Spreadsheet

There’s a lot to my Master spreadsheet; I’ll probably get around to covering half of it in this article and finish the rest in another.  As I said before, the whole point of my stats setup is to enter in data just once and in a game-by-game basis.  With some exceptions, almost all of the replay stats is linked from that data.

Let’s start with the first worksheet in the Master1966.xls file, the Standings page.  Quite simply, it just displays the 1966 NL standings of my replay.  Rather than typing in the wins and losses manually which can lead to mistakes, I link the cells to the team’s pitching wins and losses totals. 

For example, I copy Houston’s total pitching wins and losses from their team worksheet here:

and paste it as a link to here:

I of course, do the rest for the other teams.  The Pct field is a simple division formula (wins/wins+losses).  The Games Back field is kind of tricky but I think I’ve got it down.  This is the formula I’m using:

=(($C$4-C4)+(D4-$D$4))/2

…where C4 is the first place team’s wins cell.  The $ sign keeps those two variables constant so you can paste that in  the first place cell and copy to the rest of the column without it changing. 

You’ll notice that the teams’ names are formatted like a hyperlink.  That’s because that’s what they are.  I just created a link for each team name so that it would open the appropriate team spreadsheet.  It’s more for my own use than anything, quite honestly.

Schedule and Game Results

My next worksheet is labeled Game Results and is simply a schedule for the entire 1966 NL season.  I mentioned that I don’t enter in much data in the Master spreadsheet.  This is the one rare exception.  After a game gets played, I fill in the scores for both teams, the winning and losing pitcher plus if there is a save, I’ll note that too.  Finally, there is a Highlights field for me to enter in anything exciting that happened that game.  This is my one chance to put in some information from a more subjective point of view in a spreadsheet full of cold data. 

By the way, if you’re wondering where I got the original 1966 schedule data from, I imported from a .csv file that I downloaded from retrosheet.org’s Schedules page.  They have actual schedules for pretty much all seasons.  I even imported a full schedule that includes actual scores and lineups into my spreadsheet just for reference.

In my first article of this series, I referred to the “Schedule” worksheet in each of the team spreadsheets.  By doing a filter by each team and sorting by date then pasting as a link, I was able to display each team’s schedule and results in their team spreadsheet which would update automatically.  Very handy. 

Transactions Sheet

My next sheet in the Master spreadsheet is the Transactions page.  I copied and pasted the transactions of 1966 from Baseball Reference (for reference, here’s is the link to the 1966 transactions web page) into a text file and after a little work I was able to import it into Excel, parse out the irrelevant data and get it look halfway decent. 

Also, as a reminder to transfer a player to his new team (because I’m the kind of person who might forget), I placed a hyper link on the Schedules page on every date a transaction took place.  The link would point to the transaction for sake of convenience. 

Team Totals

I have two worksheets for Team Hitting and Team Pitching to display total team stats.  These were relatively easy to set up.  Like the Standings sheet, I just pasted a link from each team page but this time it was a whole row. In this case, it was the totals row of the hitters and pitchers. 

For example, I would the copy the Atlanta Braves hitting totals from their team worksheet here:

and paste as a link here (see crudely drawn arrow):

…and do the rest of the teams.  As always, league totals is done by the sum function and the averages are done by their varying methods. 

This is what the Team Pitching sheet looks like:

(Poor Philly.  It’s early in the season.)

In my next article in this series, I’ll discuss the rest of my Master spreadsheet which includes my hitters and pitchers registers and how I do leaderboards. 

It should be interesting since I’m changing how I do things a little. Scott Fennessy has turned me onto pivot tables which are fantastic for displaying leaderboards that update automatically.  I’m still getting the hang of them but they’re quite powerful. 

8/21 Update:  thanks also to Scott Fennessy for catching a small error in my Games Back formula.  The error has been corrected in the post.