http://www.4shared.com/file/18770434/2ad31f22/MLB_System_6_27.html
------------------------------------------------------------
Pages:
1. Main Page - The User Interface. Where the lines are created. The easiest page to use to put the final touches and get the final lines.
2. WQ - Any page with a "WQ" is a Web Query page. These are the pages that need to be updated daily to grab the latest stats. Alignment of cells in these pages is critical.
3. WQ - Matchups - This page does a lot of work for you. It grabs today's matchups, pitching matchups, wind, etc and throws it into the Main Page all nice and pretty. This page is the behind the scenes that drives the main page.
4. Power Rankings - This is a behind the scenes page that interprets all of the data and lays it out in a semi-organized structure. This data is read by the Main Page.
5. Lookup Values - This is a page that shouldnt be touched. This page turns all of the stats into power rankings. It's all of the charts so that the system knows how to read a stat and what value to give it. Hence it "Looks Up" the stat on the chart on this page, and assigns it a power ranking value.
6. SF-Pitchers-Translation - This simply stands for Statfox, Pitchers, Translation. I used to use ESPN to pull pitchers stats, so I had to label this one SF. This page takes all of the WQ - Pitchers updated stats and organizes it to be read better by Excel, and turns it into a giant chart. It is essentially a "Lookup Values" page, just for Pitchers. If you are having a N/A issue with a pitcher, this is the page to fix it on behind the scenes.
7. Translation Pages -Turn web query pages into more readable, and organized format for the system. Typically ascending order, whether it be alphabetically or numerically.
This looks and sounds like a lot of work, but once u understand what to do it takes 10 mins everyday, instead of an hour or two of capping.
1. Refreshing Pitcher's Stats and Data
Every 5 Days you need to do this to keep pitchers stats and power rankings up to date.
Step 1. Grab Latest Pitcher's Stats
Go to the web update Page: "DONTTOUCHWQ-SF-Pitchers"
This is the page where Excel grabs the pitcher's data. Click on cell A1, O1, and AC1 and right click on each cell one at a time, and refresh each stat grouping (highlighted in red in newer versions). To refresh, right click on the CORRECT cell, hit "Refresh".
Important: Make sure the alignment remains the same when you refresh. The stats shouldnt have jumped to a new cell. The alignment and cell placement is sooooo important.
One group is Pitcher's Overall stats, one group is home stats, and one group is away stats.
Step 2. Alphabetize and Maintain
Now that all pitcher's stats have been grabbed. We have to do some maintanence. We have to alphabetize and order everything properly. It's trickier and tough for pitchers right now. We need to re-grab the pitcher's names and stats and re-reference them on the Translation Page....and then alphabetize.
a. Go to "SF-Pitchers-Translation" Page.
b. Click on Cell E3. Typically 'A Burnett'. We are going to make a formula to reference the other page.
c. Type "=". Do not hit enter.
d. Navigate to "DONTTOUCH-WQ-SF-Pitchers"
e. Click on cell B7. Typically 'M Albers'.
f. Hit enter. Congrats, u just wrote a formula.
g. In the cell where it now references M ALBERS instead of A BURNETT, make sure you have that cell clicked on.
h. Autofill to the bottom. Grab the bottom right "handle" and drag it all the way to the bottom where that column runs out of names, just one or two cells passed B Zito.
i. This should change all of the names.
j. Click on cell G3, under the column of #G. We are skipping over the power ranking column because those are formulas that will calculate a power rnaking, and we dont want to mess with it.
k. With your mouse clicked in cell G3, hit "=". Do not hit enter.
l. Navigate to the pitchers SF WQ page again.
m. Click on cell C7. Hit enter.
n. Grab the Autofill handle of that cell G3 and autofill it to the end of the Overall Stats only (Cell P3).
o. Now highlight cells G3 to P3. The ones you just autofilled.
p. Grab the bottom handle of all of these row of cells highlighted, and autofill those cells all the way until the final name on the list. You should see each Pitchers Power Ranking change.
q. Highlight from E3
226. AKA Top left corner of the Overall Stats, to bottom right corner.
r. Data>Sort> By the Name Row Column E, in ascending order....That alphabetizes the names.
Do this for each of the final two lists, first home stats. Then away. Home is below.
Click on R3. Hit "=". Navigate to WQ page. Click on cell P7 on that page. Hit enter. Autofill those names to the bottom. Then click on T3. Hit "=". Navigate to WQ Page. Click on cell Q7. Hit enter. Autofill to the right edge of the home stats. Then with that small row of cells highlighted, autofill to the bottom. You should see each Pitchers Power Ranking change. Then highlight that whole section, and alphabetize in ascending order by last name using Data>Sort.
Step 3. Working with Pitcher's N/A's.
The tricky part with pitcher's, is sometimes a new pitcher makes a start that has never made a start before. He gets added to the list. He may have made 2 home starts, but he hasnt made an away start. That makes all of his away stats ZERO or N/A. That throws a wrench into all of the equations associated with that pitcher and creates an N/A for that pitcher.
N/A is the result of a formula gone bad because something is a ZERO or is not found.
For pitchers you can always trace the error back to the source. The SF-Pitchers - Translation page, lists all of the pitchers. Look that pitcher up on the chart. You may find him under the Overall chart, and he may have a home start, but he might not have a road start. Therefore he is not found, or N/A on the MAIN Pitcher Power Ranking chart on the left hand side. Just eyeball his overall power ranking and his home power ranking. Average them out aka, add the rankings and divide by 2, and manually type that ranking into his slot on the main page.
OK more later.
------------------------------------------------------------
Pages:
1. Main Page - The User Interface. Where the lines are created. The easiest page to use to put the final touches and get the final lines.
2. WQ - Any page with a "WQ" is a Web Query page. These are the pages that need to be updated daily to grab the latest stats. Alignment of cells in these pages is critical.
3. WQ - Matchups - This page does a lot of work for you. It grabs today's matchups, pitching matchups, wind, etc and throws it into the Main Page all nice and pretty. This page is the behind the scenes that drives the main page.
4. Power Rankings - This is a behind the scenes page that interprets all of the data and lays it out in a semi-organized structure. This data is read by the Main Page.
5. Lookup Values - This is a page that shouldnt be touched. This page turns all of the stats into power rankings. It's all of the charts so that the system knows how to read a stat and what value to give it. Hence it "Looks Up" the stat on the chart on this page, and assigns it a power ranking value.
6. SF-Pitchers-Translation - This simply stands for Statfox, Pitchers, Translation. I used to use ESPN to pull pitchers stats, so I had to label this one SF. This page takes all of the WQ - Pitchers updated stats and organizes it to be read better by Excel, and turns it into a giant chart. It is essentially a "Lookup Values" page, just for Pitchers. If you are having a N/A issue with a pitcher, this is the page to fix it on behind the scenes.
7. Translation Pages -Turn web query pages into more readable, and organized format for the system. Typically ascending order, whether it be alphabetically or numerically.
This looks and sounds like a lot of work, but once u understand what to do it takes 10 mins everyday, instead of an hour or two of capping.
1. Refreshing Pitcher's Stats and Data
Every 5 Days you need to do this to keep pitchers stats and power rankings up to date.
Step 1. Grab Latest Pitcher's Stats
Go to the web update Page: "DONTTOUCHWQ-SF-Pitchers"
This is the page where Excel grabs the pitcher's data. Click on cell A1, O1, and AC1 and right click on each cell one at a time, and refresh each stat grouping (highlighted in red in newer versions). To refresh, right click on the CORRECT cell, hit "Refresh".
Important: Make sure the alignment remains the same when you refresh. The stats shouldnt have jumped to a new cell. The alignment and cell placement is sooooo important.
One group is Pitcher's Overall stats, one group is home stats, and one group is away stats.
Step 2. Alphabetize and Maintain
Now that all pitcher's stats have been grabbed. We have to do some maintanence. We have to alphabetize and order everything properly. It's trickier and tough for pitchers right now. We need to re-grab the pitcher's names and stats and re-reference them on the Translation Page....and then alphabetize.
a. Go to "SF-Pitchers-Translation" Page.
b. Click on Cell E3. Typically 'A Burnett'. We are going to make a formula to reference the other page.
c. Type "=". Do not hit enter.
d. Navigate to "DONTTOUCH-WQ-SF-Pitchers"
e. Click on cell B7. Typically 'M Albers'.
f. Hit enter. Congrats, u just wrote a formula.
g. In the cell where it now references M ALBERS instead of A BURNETT, make sure you have that cell clicked on.
h. Autofill to the bottom. Grab the bottom right "handle" and drag it all the way to the bottom where that column runs out of names, just one or two cells passed B Zito.
i. This should change all of the names.
j. Click on cell G3, under the column of #G. We are skipping over the power ranking column because those are formulas that will calculate a power rnaking, and we dont want to mess with it.
k. With your mouse clicked in cell G3, hit "=". Do not hit enter.
l. Navigate to the pitchers SF WQ page again.
m. Click on cell C7. Hit enter.
n. Grab the Autofill handle of that cell G3 and autofill it to the end of the Overall Stats only (Cell P3).
o. Now highlight cells G3 to P3. The ones you just autofilled.
p. Grab the bottom handle of all of these row of cells highlighted, and autofill those cells all the way until the final name on the list. You should see each Pitchers Power Ranking change.
q. Highlight from E3
226. AKA Top left corner of the Overall Stats, to bottom right corner. r. Data>Sort> By the Name Row Column E, in ascending order....That alphabetizes the names.
Do this for each of the final two lists, first home stats. Then away. Home is below.
Click on R3. Hit "=". Navigate to WQ page. Click on cell P7 on that page. Hit enter. Autofill those names to the bottom. Then click on T3. Hit "=". Navigate to WQ Page. Click on cell Q7. Hit enter. Autofill to the right edge of the home stats. Then with that small row of cells highlighted, autofill to the bottom. You should see each Pitchers Power Ranking change. Then highlight that whole section, and alphabetize in ascending order by last name using Data>Sort.
Step 3. Working with Pitcher's N/A's.
The tricky part with pitcher's, is sometimes a new pitcher makes a start that has never made a start before. He gets added to the list. He may have made 2 home starts, but he hasnt made an away start. That makes all of his away stats ZERO or N/A. That throws a wrench into all of the equations associated with that pitcher and creates an N/A for that pitcher.
N/A is the result of a formula gone bad because something is a ZERO or is not found.
For pitchers you can always trace the error back to the source. The SF-Pitchers - Translation page, lists all of the pitchers. Look that pitcher up on the chart. You may find him under the Overall chart, and he may have a home start, but he might not have a road start. Therefore he is not found, or N/A on the MAIN Pitcher Power Ranking chart on the left hand side. Just eyeball his overall power ranking and his home power ranking. Average them out aka, add the rankings and divide by 2, and manually type that ranking into his slot on the main page.
OK more later.
