HQ Radio Jun 2, 2023: Friday Full Edition w Vlad SedlerMaking the Most of HQ with Excel: Naming
by Patrick Davitt
Click here to return to the Education page.
In our first installment, we discussed the basics of moving BHQ data to your computer and into Excel. In this installment, we'll talk about naming files, tabs and data ranges.
One of the often underappreciated opportunities to make Excel work easier has to do with how you name the data within your workbooks. We'll get to data ranges a little later; to begin, let's set some protocols for naming the files and the tabs.
When you were downloading the Excel projections files, you saw in the "Save Target As..." dialog box that the file is already named to reflect its contents: "12abat" (2012 AL batters), "12apit", "12nbat" or "12npit". These file names are perfectly fine, somewhat descriptive, and will function.
That said, the filenames do not change every day, so simply saving them as-is each period you download will result in over-writing (and therefore losing) the previous file. To enable ongoing research, you want to give each (usually) weekly file a unique, date-specific name. Here's how:
1. Imagine you are downloading the March 8, 2012 projection files. When you right-click the "AL Bat" Excel link to get, and choose "Save Target As...", you enter the name "HQ_030812" in the "File Name" box, a naming protocol that uses "030812" as a serialized version of the date March 8, 2012. You could also use "HQ_03.08.12" if the unpunctuated version doesn't suit your aesthetic.
Naming the files with this protocol has several advantages. First, it will allow you to retain each period 's data with a unique identification, and it will automatically order the files by date in your folder, which is very handy when you want to look up a specific week.
You could leave out the "HQ" and the underscore, or use "HQ" and a space. But don't—when we start naming tabs, we are going to use the same naming rules for consistency, and when we name database ranges, we are going to make them identical to tab names. Excel requires database range names to begin with a letter and not a number—and you can't use spaces. You'll also find it handy to have that "HQ" as an easy reminder what the file is by name alone.
2. When you right-click each of the other files (AL Pit, NL Bat, NL Pit) in turn, save them as is with their original filenames. This might seem counter-intuitive, but we don't have to worry about re-naming these files because we're going to be copying them into and uniquely identifying into workbooks right away, and assigning their unique identifiers then.
Once you've saved all four new projections files, launch Excel and either click on "Open" in the "File" menu or click the Open File icon. Navigate to the folder "HQ Projections," where you have lodged the downloaded projections files. Click ONCE on the file "HQ_030812", which you'll recall is the newly renamed copy of what used to be the "AL Bat" file. Then, hold down the Control (CTRL) key and click ONCE EACH on the files "12apit", "12nbat" and "12npit". Then click "OK" and all four files will open.
Now go to your "Window" menu and click "Arrange...", then choose "Horizontal" and click "OK." All four files will now be shown on your screen. You will notice that while the filenames in the title bars all have the Excel file names, in accordance with normal Windows practice, the "Tabs" on the bottom of each file all say "Sheet1". Returning to our desire to have data uniquely identified, we want to change the tab names. Here's how:
1. Start with the sheet whose filename is HQ_030812. Put your cursor arrow on the tab that says "Sheet1" and double-click. The term "Sheet1" will be highlighted in black. Type (without the quotes but with the underscores), "AL_H_030812," reflecting the fact that this sheet is the AL Hitters for March 8, 2012, and hit the enter key. The tab is renamed.
Go change the names of the other tabs using the same naming protocol: "12npit" becomes "NL_P_030812" and so on. Remember—you're not changing the name of the file, you're just changing the name on the tab.
Once all four tabs are renamed, we have a fairly cool and useful trick: Click on the sheet with AL Pitchers on it, whose tab name should now be "AL_P_030812". Put your cursor on that tab, then click-and-drag the tab into the workbook window titled "HQ_030812", next to the "AL_H_030812" tab. The "AL_P_030812" sheet will stay there, and the window it used to be in will disappear (at least in newer versions of Excel). The file hasn't disappeared—it is still in its folder.
Now drag the other two tabs, "NL_H_030812" and "NL_P_030812" to that same workbook window alongside the other tabs. When you're done, save the file.
You now have a complete, uniquely identified HQ projections workbook file, with all four sheets in it. Each sheet is further uniquely identified by league, hitter/pitcher, and date.
More importantly, you have established a naming protocol you will be able to repeat and use in your ongoing research efforts—a protocol that will becomes second nature to you.
One last thing before we move on: If you plan to do wide-ranging cross-MLB research, or if you play in a mixed league, you will need to add MLB-wide sheets to your books. Here's how:
1. Open the HQ_030812 workbook. Click ONCE on the "AL_H_030812" tab and, while holding down the Control (CTRL) key, drag the tab to the right, past the "NL_P_030812" tab. This will create a copy of the AL_H" tab called "AL_H_030812 (2)". Repeat for the "AL_P_030812" tab.
2. Go to the "NL_H_030812" tab and click-and-drag all of the player names and data but NOT the white-on-green titles at the top. Right-click inside all the highlighted data and choose "Copy", then go to the "AL_H_030812 (2)" tab, put your cursor one cell under the last entry in the "A" column of player names, right-click and choose "Paste". All of the NL hitters will now be on the sheet with their AL brethren.
3. Now we'll re-sort the list. Click in the cell that says "Batter" in the white-on-green category titles. Click-and-drag to highlight the titles, all of the batters and all of their stats. Go the "Data" menu and select "Sort..." Make sure "Header Row" is selected, and then use the first pulldown to choose "Batter" and choose "Ascending" (in later Excel versions, these will be the default settings). Click "OK" and the list will re-sort alphabetically.
4. Do the same for the pitchers to get an MLB data set.
5. Now re-name those tabs to "MLB_H_030812" and "MLB_P_030812"
6. Save the workbook!
One final set of things to do to round up this first session: We've named the workbook file, we've named the individual sheets on their tabs, now we want to name the data themselves.
To do this, you have to know where the "Name Box" is. Click once in the upper-left cell A-1 in the first worksheet, "AL_H_030812". Now just look a little further up and over to the left a bit, where it says "A1". That's the Name Box. Click in cell C-15 to make sure you're looking in the right place. Does it say "C15"? Good. On we go.
Once you are sure you know where the Name Box is, you're ready to name some data. Let's highlight all the batters and their data, but NOT the white-on-green titles. With all the data highlighted, click once in the Name Box, and type in "AL_H_030812"—the same name as the tab. Hit your "Enter" key or click someplace inside the worksheet again to finish the naming procedure.
By doing this, you have given this "range" of data a unique name. You will be able to use that name later to refer to that range without having to retype the cell references or re-highlight the data. And by naming the range exactly the same as the tab, you don't have to remember two different names for the same data.
There's a method to our madness after all!
Repeat this procedure for all the other sheets—highlight the data, go to the Name Box and type in the same name as the sheet tab.
Now you're ready for the next step: Looking up data using the named ranges and the most powerful weapon in the Excel arsenal for our purposes: VLOOKUP.
NEXT: VLOOKUPS
