Player Evaluation 101 - Using Excel

[Jackstraw]

Today let’s move onto something a little more concrete. Something that is a little late in the making, but is important at becoming better in baseball gaming. It is the primary tool that you will use on draft day: your player evaluation sheets. It will also become part of your reference material for the rest of the season.



This is just going to be a very simple set of evaluations for you to see how it is built. Once you have the idea of how to build it, then you can tailor it to fit your needs and theories. I am going to assume that everyone knows how to use Excel. If you don’t then shoot me a message and I can try to give you some pointers.



The first thing that you are going to need is a list of players and their season stats. There are many ways to get one of these. There are very difficult ways (going to some popular website and copying/pasting their lists) and there are easy ways (just paying for it). I do not recommend the copy/paste method. It can take awhile. It is cumbersome. And it can cause you to make mistakes. Paying for it is much easier and normally it will give you all of the data you need in one file. Most importantly, it gives you trustworthy information… Bad data is like brittle bones.



I’m going to skip the copy/paste method and assume that you have purchased your player stat list. Most likely this is going to arrive via email or download as a tab or comma-delimited file. Some of you may find access to one that is already in Excel format. If so then you can skip this part, or you can read and maybe add another tool to your belt.



1. Save the tab or comma-delimited file to your desktop.

2. Open a blank Excel spreadsheet.

3. Click on Data in the toolbar.

4. Select Import External Data > Import Data.

5. A browser window is going to open up and it is probably going to be directed to a folder called My Data Sources. Just go to your Desktop folder.

6. At the bottom in the “Files of type:” drop-down list, find and select Text Files (containing *.csv and/or *.tab files).

7. The player file should now appear. Double-click on it.

8. The Import Wizard will open up. In the first window, check the “Delimited” button.

9. Next, set the import row to whichever row that the column headings are at in the list.

10. Click Next.

11. The next window wants to know if this is a tab or comma-delimited file. Check the appropriate box for your file, and then click Next.

12. On the next window, don’t do anything except click Finish.

13. In the final window, Excel asks you where you want to locate the file. If you are on a blanksheet, just type in “=A1” and then click OK.



Your data should have magically appeared in your spreadsheet. Now, a good data file will include the player’s name, positional data, and pitching/hitting statistics. The positional data is sometimes an option, but it is really nice to get if you can. It will make things a lot easier down the road. Your hitting statistics should include all of the traditional measures (G, AB, H, R, HR, RBI, SB for hitters at a minimum; G, GS, IP, ER, H, BB, W, K, SV for pitchers at a minimum).



The last task for the day is splitting the pitching data and the hitting data. You may have gotten two separate files for each position. Just follow the steps from above for each one of the files, importing them into separate tabs in your workbook. Some of us will get all of that data in one file.



Almost every file I have seen comes through with the pitching statistics at the bottom of the file. Hitting is listed at the top. Following these steps, we can separate the data into two separate tabs:



1. Scroll down through your list until you find the break in data. There may be a blank line between the two, or it just may flow from hitting statistics into pitching statistics.

2. Click on the row number that contains the headings for the pitching statistics (or hitting, if hitting is at the bottom of your file).

3. Hold down Control+Shift and hit the arrow down button. This will select all of the rows of data from the first row that you selected down to the last entry.

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

5. Click on a new tab at the bottom of your workbook.

6. Select cell A1 and right-click Paste.

7. At this point, if you haven’t saved, save now! Click on File > Save As. Type in a filename and save it to a location where it will be easy to find.

8. Now just as a safeguard against having to go back through these steps again, save another copy under a different name. I usually call my raw data something like “Player Data – Raw.” I’ll just call my play data “PlayData.”



Until we meet again tomorrow, don’t do anything with your raw data file. However, take some time and play around with the “PlayData” file. That is what it is for. Practice sorting. If you got positional data, practice using an auto-filter. You can do a lot of different things with just this data. If you screw up your file, just delete it. Re-open the raw data file and save another copy.



Here’s a little homework. Find a website or a book that will give you the formulas for calculating batting average, ERA, and WHIP. Or if someone feels happy, you can post them here. We’re going to need those tomorrow since many of the supplied data files won’t give you those statistics. Usually they will give you the values you need to calculate them yourself. Don’t scoff at this. This is what you really want. Eventually we will start steering down the path of sabermetrics in our evaluations. You may get OBP in a file but you can forget Contact Rate. Like it or not, to be good at fantasy baseball or betting you need to know a little math.



And it’s like spinach… It’s just good for you :rolleyes:

[bjoak]

Thanks, but I'm already enrolled in the graduate level course, about a season away from my PhD.

[Spyhunter]

Umm KoQ or I will offer the graduate level class in excel if needed... Bjoak, I didn't know you were part of the hard core excel geek squad as well



My personal next step is going to be to teach myself SAS. I want to find a high quality data source where I can buy the last 15-20 years of baseball data broken down by at bat. Seriously, I think I can create a regression analysis model that will validate (or disprove) such common theories as Age 27, Contract Year, and other performance theories. Anyone have any ideas where such a robust source of baseball stats can be found?



Spy

[bjoak]

I have no idea how that was not clear.



You can do some reading on studies already done on those things. Contract year affects hitters by a positive 15% I believe. There is not a strong enough correlation to support it affects pitchers as I recall. Age 27 is more like 26-27 but it differs based on career paths, body types, etc.

[Spyhunter]

Originally posted by bjoak:

I have no idea how that was not clear.



You can do some reading on studies already done on those things. Contract year affects hitters by a positive 15% I believe. There is not a strong enough correlation to support it affects pitchers as I recall. Age 27 is more like 26-27 but it differs based on career paths, body types, etc. I have read various studies, as a true geek however, I want to see if I can't do a more robust job. So far, what I have seen seems to have all the data summarized at the year level. www.baseball1.com is a fantastic resource, but not at the level of detail I want to do this at



Spy

[BaseBrawler]

Thats alot of words.... LOL

All I see are words words words LOL

[sportsbettingman]

I do have excel...but am clueless as to how to use it well.



I'd appreciate any PM from you guys on how to set it up to make life easier.



On a side note...



I was curious how the successful folks here treat a pitcher coming off injury or a callup from the minors.



Do you wait and see him pitch once or twice before activating him?



What if you feel his talent is better than what you have starting?



I know most will throw a hitter right in...but it seems they are more selective with pitchers.



Do you only activate a guy when he has a solid matchup?



Is there statistical evidence to back that decision up? (example...a SP coming off DL or callup performs lower than his average start 65% of the time.)



Just wondering out loud.



~Lance

[Jackstraw]

Originally posted by Spyhunter:

My personal next step is going to be to teach myself SAS. I want to find a high quality data source where I can buy the last 15-20 years of baseball data broken down by at bat. Seriously, I think I can create a regression analysis model that will validate (or disprove) such common theories as Age 27, Contract Year, and other performance theories. Anyone have any ideas where such a robust source of baseball stats can be found?



Spy Spy, I would just do a search for Sean Lahman or use Lance's baseball1 link. It is the most robust database that I have found for stats going back that far. The search on Lahman will give you some links to SABR (Society for American Baseball Research). The data you are looking for is probably held captive by these guys.



I started doing some analysis like you were talking about from Lahman's DB but the only problem is that you are limited to cumulative yearly stats



Still it gave some interesting results.

[Jackstraw]

Originally posted by Spyhunter:

I have read various studies, as a true geek however, I want to see if I can't do a more robust job. So far, what I have seen seems to have all the data summarized at the year level. www.baseball1.com is a fantastic resource, but not at the level of detail I want to do this at



Spy Oops... Your baseball1 link :rolleyes: Shoulda paid a little more attention before responding.



I agree it's fantastic but limited. To get at the level you are looking for I just had to subscribe to a stat provider and start building my own data set. Unfortunately I didn't start until last year. So I got one year's full data broken down into single game increments.

[Jackstraw]

Originally posted by TheFoot:

Thats alot of words.... LOL

All I see are words words words LOL I know, I know... This would be much better if I could insert screenshots.



I'll have to work on my website so that maybe I can create hyperlinks from here to images on my site.

[Quahogs]

Originally posted by sportsbettingman:

I do have excel...but am clueless as to how to use it well.



I'd appreciate any PM from you guys on how to set it up to make life easier.



On a side note...



I was curious how the successful folks here treat a pitcher coming off injury or a callup from the minors.



Do you wait and see him pitch once or twice before activating him?



What if you feel his talent is better than what you have starting?



I know most will throw a hitter right in...but it seems they are more selective with pitchers.



Do you only activate a guy when he has a solid matchup?



Is there statistical evidence to back that decision up? (example...a SP coming off DL or callup performs lower than his average start 65% of the time.)



Just wondering out loud.



~Lance not sure this might help but this is what works for me...



-the freshly picked up new callup or off DL guy inserted into lineup instead of observing 1st start - 100% of time



-new callup/DL guy gets rocked - 100% of time



-move him to reserve to observe a little longer, 2nd start is better than 1st start - 100% of time



-back in lineup, 3rd start worst than 1st - 100% of the time



-dump back into pool - 100% of the time



-gets grabbed and activated by 1st place team and pitches lights @$&*! out for rest of season? -100% of time.

[sportsbettingman]

Sounds eerily familiar.