Player Evaluation 101 - Using Excel
Posted: Thu Apr 03, 2008 6:31 am
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:
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:
