How I keep my APBA replay stats Part III

Last week, I started an article series about how I’m keeping my 1966 NL replay stats using Excel.  I started with a post on my how my individual team spreadsheets.  Part II of the series looked at my Master spreadsheet which uses the data from those team spreadsheets to make sense of that data from a league view.  Today, I’m going to finish up with the Master spreadsheet focusing on the hitters and pitchers registers and how I do leaderboards.  Fun stuff!

Hitters (and Pitchers) Register

For me, one of the biggest thrills of doing a replay for me is comparing how hitters and pitchers are doing in relation to each other.  And one the best ways to show the best of the best is via leaderboards.  Before we can have leaderboards, it’s easiest to have a table of all league players that you want to compare in one table.  So what I do is create a register or database of all hitters in one worksheet. 

Again, this sounds complex but it’s really just copying a table from one sheet and pasting it as a link to another.  Then just repeat that process for however many teams you have in your replay. 

Let use Houston Astros as an example.  I highlight all the cells of this table and copy them:

In my Master spreadsheet, I have already created a new worksheet called Hitters Register with the headers row with the same stat categories as the team sheet.  I then paste the Houston hitters as a link.  I do the same thing for every other team pasting their hitters below them also as a link.  The process takes about 10-15 minutes and when I’m done, I’ll have hundreds of rows of hitters stats linked from the team’s sheets. 

When it’s done, it will look like this (I’m posting a snippet of the top of the page so you’re just seeing just a few):

A couple notes:

– One, back in Part I of this series, you may have wondered why I put the team’s name as a field in the team’s spreadsheet.  I mean, all the players in the atl1966.xls spreadsheet play for Atlanta, right?  Now that they are being linked into the hitters register with the nasty Reds and Pirates and such, it makes more sense.  If I wanted to filter or sort by team name (which by the way I just did above), I can. 

– Two, if I want to calculate other statistics from their raw stats, I can.  Averages like PA, BA, Slg, OBP can be calculated and inserted…   or even fun stats like K/9IP or WHIP.  I have a column for Games Team Played which wasn’t too hard to insert. 

– Finally, a mea culpa.  You’ll note that a few players don’t have their first names (hey sue me, I didn’t know what Geiger’s full name was when I was entering stats!).  The good news is that I pasted his name as a link so when I get around to looking at his card more closely and finally editing his name on the team spreadsheet, it will update automatically. 

The pitchers register is done in a similar fashion on a separate worksheet next to the Hitters register.  With both registers, it’s important to keep the stat category order consistent.  I had an issue with one team (it was the first team I set up) having the HRA before the BB and K and for some reason, I changed.  Keep consistent! 

A quick look at the pitchers register in no particular order (actually, I think they’re in reverse alphabetical order for some reason):

Leaderboards

Now that I have registers of all the hitters on one table and all the pitchers in one table, we’re in good shape.  I’m going to show you two methods to do leaderboards.  The first is less complex but also less powerful and requires more work in the long run.  The second, using pivot tables, is a bit more complicated but is quite awesome when it works since it updates the stats automatically. 

Doing leaderboards the ‘old fashioned way’ is simply applying a sort on the table on the particular stat then copying the relevant info to a new worksheet.  Let’s say we want to figure out homerun leaders.  The easiest way to do this is to right-click on any cell in the HR column and choose Sort then Sort Largest to Smallest.  All your hitters should then be sorted by most homeruns.

If you want to copy multiple columns not adjacent to each other, highlight one column then press the control key and highlight the other column.  In this case, I highlighted the top nine players and their teams, press the control key, then highlighted the corresponding homerun column. 

You are then able to copy that to the clipboard and paste it to another worksheet.  After autofitting the column width, it looks like this:

Most other stat categories are done similarly… except averages.  I have Harvey Kuenn in my 1966 replay who is 1 for 1 and would show up leading the league in hitting unless I placed a filter for at-bats or plate appearances.  The same would go for a host of relievers who pitched one or two scoreless innings so far. 

In this case, click Data at the top then Filter.  This will give you down arrows on each stat header.  Click on the arrow for PA or AB) and choose Number Filters then Greater than or equal to.  You will then get this dialog box which will allow you to enter in the minimum playing time required to qualify:

Once you do that, only those player with the required amount of PA or AB will appear.  You can then do the sort as described above. 

Pivot Tables

Doing the leaderboards in the above fashion is fun but if you do them often it is a bit time consuming.  As complex as pivot tables are to understand, once you get the idea of what to do, they’re quicker to implement than the old method.  Plus they automatically update so I’m saving time there, too. 

Essentially, what pivot tables do is sort data from a existing table (like our hitters register) and place that sorted data where you want it, in the format you want.  There are some restrictions as you’ll find out but overall, it’s been working out. 

To use pivot tables, highlight a cell anywhere in the hitters register and click Insert, then click Pivot Table.  It will ask you where you want to put it.  I already have an existing spreadsheet called Hitting Leaders.  You can specify exactly what cell you want to put it in by clicking the button with the red arrow in it (don’t worry, you can move it later, if you want). 

In the Pivot Table Field, list click Player (that’s what I called the field where the name goes) and HR. 

This will give you a table of every hitter and how many HR they have hit.  Of course, we don’t want that; we want it sorted and filtered to the top ones.  Click on the down arrow on cell where it says Row Labels and choose More Sort Options and it will give you the chance to sort HR largest to smallest. 

Finally, click on the down arrow again and choose Value Filters then choose Top Ten.  This will filter down to the top ten highest homerun hitters. 

A couple more things:  if you right click on the header where it says Row Labels, you can change the option so that it will update automatically.  You can also get rid of the Grand Total figure that displays which doesn’t make sense in this context.  You can change the header text by just highlighting them and typing what you want.  I just typed Player and HR as you see from the final result below.

I’ve entered in some of my replay stats since I set this up and it does update automatically as long as you have the box checked that says Refresh data when opening file.  You’ll note that more than ten players are listed because of a tie. 

As with the old method, averages are a bit trickier.  This time I had to include PA as a field in the pivot table.  I included it as a Row Label and filtered by the required amount.  By default, the plate appearances column displays but you can collapse it so it disappears. 

[yeah, Timmy Mac having a great April]

I mentioned a couple limitations of pivot tables.  One, I can’t figure out how (without programming) to automatically input the required amount of plate appearances to qualify for batting average (and etc).  So for now, I will have to update that manually every so often.  Also, I would like to display the team name with the player.  Pivot tables are not set up to do that, apparently. 

Since leaderboards are mostly for show it’s nice to dress them up.  Excel does have some automatic formatting features which are nice. 

So that’s my brief and most likely incomplete introduction to pivot tables.  I do want to thank Scott Fennessy for giving me the idea to try them.  I admit, I still have a lot to learn about them.  For those who want to try pivot tables, I recommend watching this great tutorial on YouTube. 

I hope this stats series has been useful for a few of you (you can see all the articles here).  As I said in a previous post, I know not everyone uses spreadsheets to keep stats.  In a couple days, I’m going to wrap up this series with a summary on stat keeping and some general hints and tips that might be useful for most everybody.