Baseball & Excel

97% of y'all will probably want to stop reading... here.

For my job, it often comes in handy to have certain skills with Microsoft Excel. Excel is quite possibly the least utilized program that exists; a good understanding of Excel and its functions can make life incredibly simpler, particularly in most businesses, and for anyone who tries to budget their money. A friend of mine from Cleveland actually designed what is essentially an ugly but advanced version of Quicken, and it didn't take him all that long, using Excel; I learned how to utilize pivot tables, "vlookups", and the "indirect" functions and used them for something far more interesting: Fantasy baseball.

I actually started the idea when I was first learning how to use the pivot tables and other functions; practicing on my baseball league's statistics was just giving me something interesting to look at as I learned how to utilize Excel better. But from that, and with a couple of tips from friends in similar fields, I created an extensive file that tracks everyone in my fantasy league's numbers - by day, week, month... however I want to see it. What's particularly interesting in our league is that we have a cap of 1,250 innings pitched - meaning that it's not about what stats you can compile, but what stats you can compile on average per inning pitched. [Note: We play in a typical 5x5 league - R/HR/RBI/SB/Avg & W/S/K/ERA/WHIP.] This means that you don't want to be leading the league in strikeouts; you want to be leading in K rate (strikeouts/per IP). You also want to make sure you hit all your innings, or even the best rate might not get you the best score for that category (14).

In the end, pitching statistics are very easy to quantify, calculate, and predict - as wins are predominantly (but not always!) a "luck" statistic, and ERA can be fluky, an owner wants to focus on pitchers with a low WHIP, high K rate, and have about 3 closers. Hitting statistics are a little harder; while average is generally easier to measure, most of the other statistics are not - and without an average to work into with no cap except 162 games played, it is simply a matter of compiling the most stats per game played without messing up. It is partially for this reason that my friend Rea (the commissioner of the league who is getting married in a week) and I have a debate over what makes the most sense - to draft more pitchers or hitters early on. He feels that as it's easier to analyze the pitching numbers, and there are usually more "finds" on the free agent market during the year, a person should draft hitting and get pitching later on. I feel that drafting what you know makes more sense, and then after gathering a big lead in those categories, trading some of it away for hitting.

Interestingly, both have worked, though admittedly both of us ended up taking a somewhat balanced approach thanks to how the draft was already going. As of this moment, first place in the league has 112 points; second place is 103; and Rea is at 100, while I am at 99. (Note: A few days ago we were all within three points of one another between 104 and 107.)

While my Excel file is still limited in terms of projections (I lazily set it up to simply calculate what a person's statistics will be based on a 162-game season; for hitting stats, it divides by games played and multiplies by 162, while pitching it takes the person's /IP rates and extrapolates them over 1,250 IP, but only if they'll hit their innings - it adjusts for people who are low on innings by using the 162-game formula based on their /IP rates.), it's fascinating in terms of looking at the past and seeing trends. For instance, despite injuries to Carmona and Young, my pitching still had the best win rate in the league; trading Bedard and Cliff Lee for CC Sabathia and Fuentes helped me have the best K rate; and my Saves rate was second by a slim margin. Meanwhile, the added flexibility of holding extra hitters and subbing in based on matchups let me lead the league in Runs, HRs, and RBIs - and by a large margin over the league leaders, some of whom have traded hitting for pitching recently.

At this point, I'm actually pretty happy with my team and think that I can ride it to a nice finish. The only moves I'd like to make are possibly picking up a fourth closer; consolidating good players into better ones, though most people aren't interested at this point; and if I need to pick up innings, finding another starter, though I doubt I'll need to do that (especially if I can get another closer). The only statistic I'm "low" in is SBs, but I'm pretty much accepting of a 5 in that category, barring a truly worthwhile trade that nets me some more. It does devalue a couple of my own players somewhat, but they're still worth more than what I could get back for them. Here's my current roster, though some of the hitters are merely pickups for a short time while hot/for matchups:

• C Russell Martin
  
• 1B James Loney
  
• 2B Kelly Johnson
  
• SS Hanley Ramirez
• 3B Mark Reynolds
  
• OF Cory Hart
  
• OF Brad Hawpe
  
• OF Aaron Rowand
• UT 1B-3B Jorge Cantu
  
• UT 2B Alexei Casilla
  
• Bench OF Jack Cust
• Bench OF Ben Francisco
• Bench OF Luke Scott
• Bench 3B Brian Buscher
  
• Bench 1B-2B-OF Jeff Baker
  
• SP C.C. Sabathia
  
• SP Chris Young - DL
• SP Fausto Carmona - DL
  
• SP James Shields
• CL Francisco Cordero
  
• CL Brian Fuentes
  
• CL Brad Lidge
  
• RP Masa Koboyashi
  
• RP Heath Bell
  

At just past the halfway mark, my average has slipped this past week to .278, with 440 R, 109 HR, 421 RBI, and 56 SB. Note that I had .275/167/52/157/16 in June, so the hitting is clearly improved (trading Dana Eveland and Hank Blalock for Mark Reynolds helped). My ERA is steady despite Fuentes blowing up last night, holding at 3.36 with a 1.25 WHIP; I have 47 wins and 46 saves with 540 strikeouts in 651-1/3 innings. It's safe to say that about 110 points is what someone needs to shoot for to win the league; I'm 19 runs, 5 RBIs, and a few batting points from having 109. It's safe to project pitching points of 14 (W), 9+ (S), 10+ (K), 12+ (ERA), and 12+ (WHIP) at this point for 57+ (possibly more) pitching points; that means I'd need 53 hitting points or so. With a 5 in SBs, I need to average about a 12 in the other four categories - I currently have 9 (R), 10 (HR), 9 (RBI), and 9.5 (Avg).

Curious if anyone made it far enough and can still follow enough of it to have any comments. :)