Player Evaluation 102 - The Basic Excel File

[Jackstraw]

Picking back up where we left off yesterday, you should have 2 Excel files for your player lists. The first is your raw data copy that will serve as a back up in case your working file gets corrupted or lost. The second copy is the working copy. Open up your raw data copy and let’s take a look at it.



You should have two tabs at the bottom of the Excel workbook. One of those tabs should be for your hitters and the other for your pitchers. We are going to start with the pitching tab so that we can finish the basic layout of the file. The finished product will have a minimum of three tabs: hitting, starting pitching, and relief pitching. Making the final tab is going to be very easy if your supplied data has the “GS” column for Games Started. If it doesn’t, I hate it but you need to go and find a file that has GS. With no further ado,



1. Add another tab to your worksheet by right-clicking over the tabs. A menu will pop up and select Insert…

2. The next window that appears asks what you want to insert. Select Worksheet and then OK.

3. Rename each of these tabs, if you haven’t already, by right-clicking over each tab and renaming each one the right name corresponding to the three tabs that I mentioned above.

4. Now click on your starting pitching tab that contains all of your pitching data.

5. From the toolbar at the top, select Data > Sort.

6. A new window will appear for you to create the settings for your sorting. Make sure that the button for header row is selected.

7. Now, you will be given three criteria for sorting. We are only interested in the first for now. In the first drop-down list, select “GS.”

8. Click the Descending button next to the drop-down list and then OK.



Your data should have magically reappeared with the list now based on the number of games started. The first entry should be Dontrelle Willis with 35 starts, followed by Jake Peavy and a string of others with 34 starts. By sorting the data by the number of games started, we have in effect sorted from starting to relief to closing. To complete this task, you are going to need to make a few decisions on where you want to draw the line that separates them. When we received the original raw data file from the supplier, the boundary between hitters and pitchers should have been clearly defined, either with a blank row or a row of column headings. Where you create this new boundary is pretty much up to you depending on which relievers you expect to move into starting roles and which ones you expect to stay in the bullpen. I’m going to walk you through doing it at the natural break in data.



1. Scroll down through your list until you reach the row where the last entry in GS equals 1.

2. Click on the first row number where GS equals 0.

3. Hold down Control+Shift and hit the arrow down button.

4. Right-click over the selected area and select Cut.

5. Click on your relievers’ tab at the bottom of your workbook.

6. This time when we cut there wasn’t a header row available, so select cell A2 and right-click Paste.

7. Let’s get the header row and plug it into the relievers’ tab. Click on the starters’ tab, and then click row 1. Right-click Copy.

8. Go back to your relievers’ tab and paste the header row in at cell A1.

9. Voila! You now have an Excel file where you can look at and manipulate the 2007 data on hitters, starting pitchers, and relievers.

10. Click on File > Save As. This is your modified raw data file so give it a name like Raw Data v1.



You should have three files now for evaluating your players. The first file is the raw data file that you started from. We won’t need to go back to that file anymore unless there is a failure in your other files. I would also suggest saving a copy of that, as well as your other files, somewhere off of your computer. Like on a jump-drive or a CD. Online storage is even better.



The second file is your modified raw data file. It is the one with the three major positional tabs. This one will become the new back up for your play data and other work. If you have any MS Access experience, you can open up a new database and then link a table to each one of the tabs in this file. That opens up a whole new world right there. We may discuss more on that later in the season.



The third file is your play data file from yesterday. I’ve shown you most of the basic steps for working your way through an Excel workbook and some more advanced tools. I’m going to leave it up to you to either copy and paste today’s work into your play data file or just save it as a new working title.



Remember to always keep a clean copy of your raw data and your modified raw data saved somewhere safe. It will save you from having to do a lot of manual labor if something happens to your work and play files.



I’m starting to run out of space so I will just wait until tomorrow to go into adding formulas and creating rankings. However, before we close for the day, let’s discuss a little about the separation of the starters from the relievers. I showed you the natural break in the data. If a pitcher didn’t have any starts, I just simply considered him as a reliever. As long as he got one start, then he ended up in the starters data. This may not always be the case. Two important notables in the 2007 data were Ryan Dempster and Brett Myers. Myers got pulled into the starters because he had some starts, so he ended up where he was supposed to be. Dempster is another story. He had no starts, but you knew he was going to be a starter this season. You need to scan through your pitching tabs and move the pitchers to where you think they belong. This is the first real step in creating your own unique evaluation from the basic raw data that is available to everyone.



First bet in a couple of days – DET over 9-. I just love it when they cover the over in the first 3 innings.