BaseballHQ.com

by Patrick Davitt

Click here to return to the Education page.

---

• Downloading the Data
• Naming
• VLOOKUPS
• Sorting & Filtering
• Tips & Tricks

---

One of the core principles governing the relationship BaseballHQ.com has with subscribers is that the information you find here is meant to guide your decisions, not to make them on your behalf.

We report, you decide.

Now before you start to worry about being approached by a peevish tall fellow with a loofah, one of the most powerful tools you have at your disposal is the Excel spreadsheet.

Excel allows you to sort, filter and manipulate the numerical data on this site to better understand it—and to better use it in putting together your strategies and tactics.

Also, it's fun. And strangely addictive.

Must .... run ... regression...

In this and the accompanying articles, we'll offer you a quick run through some of Excel's most important tools and some basic instruction in their use for baseball analysis.

Just so you know, this article and its descendants will assume you have a basic knowledge of Excel, including principles of data entry, formatting, cell references and so on. There will be some hints and tips about these issues, but we are starting from the assumption that when we suggest changing the format of a column to "Currency" with zero places of decimal, you know how to do that.

Through this series, we'll look at these topics:

• Getting BHQ data from the site to create a master Excel workbook;
• "Naming" information in a research workbook.
• Using VLOOKUPs, SORT, FILTERs and other functions to manipulate and study the data.

Along the way, there will be all kinds of "shortcuts" and little hints that even regular Excel users might find useful.

Here's the first one: Save your files frequently. Nothing sucks worse than getting two-thirds of the way through a project and having Excel lock up. Here's the second one: If something goes really wrong, immediately hit CTRL-Z. That's the "Undo" command.

And one more thing: When you get an instruction to type something and it shows quote marks, don't type the quote marks unless specifically told to do so.

GETTING THE DATA INTO EXCEL

Of course, everything starts with getting the raw BHQ data from the site into Excel. There are two different techniques here, one for Player Projections Files and the other for the output from the MACK Engine or the Custom Valuator.

Player Projections files are updated daily, starting during the first week of March. Once logged in, you can access the current projection files from the BHQ home page by going to "Stats & Tools" on top menu bar then clicking the first entry, "Stats and Projections."

To download the files for Excel use, simply follow the instructions listed right there on the page: Using the links listed under "Excel files" (handily enough), right-click the file and choose "Save Target As..."

Save the files into a folder called "HQ Projections," which you have already set up in advance or which you can establish using the "New Folder" icon in the "Save Target As..." dialog box. Get in the habit of downloading all four files, even if you play single-league formats. Players move across leagues and it's nice to have their whole seasons.

For now, download the files with the names just as they are. In the next article, we'll detail some naming protocols to create workbooks that will meet our needs later on.

To capture data from the MACK Engine or the Custom Valuator, first launch Excel and open a new workbook. The default on launch is a new workbook with three tabs, and that's fine for now.

Now run the MACK or the Custom Valuator, and get to the stage where you have the output report.

Using the CV as an example, move the cursor around the word "Hitters" (in the blue category bar) until the arrowhead cursor changes to an "I-bar" cursor like you see in Microsoft Word. Click and drag down-and-right to highlight all the data. It can be slow going, so be patient and don't quit partway through. Remember in CV, the pitchers are below the hitters. In MACK, the reports are separate entities, and you have to do them one after the other.

Once you've highlighted all the players and their data results, let go of the mouse button, put the cursor into the highlighted area (right on a piece of highlighted data, not the white space in between), and right-click. Choose "Copy."

Now go to the Excel workbook where you want to deposit these data. Put your cursor in the upper-left cell where you want the data to start (just use cell A-1 for now, the uppermost-leftmost cell), and press CTRL-V or right-click in the cell and choose "Paste"). It will again take a few seconds, depending on how much processor power and RAM your computer is sporting, but it'll get there.

You'll notice that the data are all still highlighted—leave them like that for a second. You'll also see the column full of names is way too narrow, and the other columns are way too wide. While the data are still highlighted, go to the "Format" menu, choose "Column" and then "AutoFit Selection." The columns will immediately re-size to fit all the data in them.

Because you always want to have pitchers and hitters data on separate sheets, go down to the pitchers and highlight all that data, including the category titles. Then Cut-and-Paste them into a new sheet. If you have only one sheet in this workbook, you'll want to Insert|New Worksheet to add a sheet.

The same process applies for the MACK output: highlight the report data, right-click to copy, go to the Excel sheet, right-click in the upper-left cell and choose "Paste". MACK returns separate pitcher and hitter results, so put them on separate pages. One thing, though: Don't include the Totals from MACK reports in your sheets. We'll let Excel do that later.

In the next installment, we'll look at some useful protocols for naming your data—the files, the tabs, and the data itself can all be given unique names that will help you manage them.

NEXT: Naming