Projection Pal Tutorial

[Mike MacGregor 20]

[Edit: Note this is the old tutorial, which still applies, but you may want to check out the newer screencast tutorial so you can see Pal in action.]

 

Okay, what I'm going to do here is demonstrate how to pull data from a website into the Cheatsheet Compiler using Projection Pal. The target I found is Pro Fantasy Sports QB rankings, found here.

 

Hey, that's not projections? It doesn't need to be. With our new Custom Fields and Player Notes Fields in the Compiler, I thought I'd demonstrate how to populate those. Whether you're doing projections, custom fields or notes, the steps in Pal are all similar. In this case I found player rankings which happen to have short notes with them. Although the Custom Fields in the Compiler do not mesh with the projections/stat allocations, someone might think it interesting to refer to a player ranking from another source they trust* on their Compiler created cheatsheets.

 

* I have no idea if Pro Fantasy Sports does good rankings or not. I just found them from an ad in the Pro Football Weekly NFL Preview mag.

 

Let's get started...

 

 

1. Open Projection Pal and here is what you see (above). I labelled the steps in Pal as best I could. Steps 1, 2 and 3 shown here I'm not changing. My Compiler file is in the same directory and still the same name as the default.

 

 

2. Here are the rankings I found. They show player rank, player name, some keeper value color coding (this will not be imported since it is a color, not text, on the webpage) and player notes.

 

 

3. Go to Pal QB Raw tab and input the URL in the QB Projections URL: box at the top. Since this is a new query from the last one in Pal, click New Query. The entire webpage is imported into Pal inside the gray highlighted cells. Most webpages will import as a bit of a mess because they usually include a lot of tables, but we'll clean it up in the next few steps.

 

 

4. Lets see if we can import only the table of data from the webpage that we need, and discard all the other stuff. In the upper-left corner of the gray cells, right-click and choose Edit Query.

 

 

5. A box pops up with the webpage and a bunch of yellow arrows. Find the arrow for the table you want to import, click it to turn it to a green checkmark, and select Import.

 

 

6. Aha! Now only the table we want is imported into Pal. You could have skipped past these few steps if you wanted to, but this is much nicer to work with.

 

 

7. Now we need to change the other yellow cells on this tab in Pal. Identify the columns first... The first column is the Pro Fantasy Sports player rankings, so that is going to our Custom 1 column in the Compiler. Label it as such using the drop-down. Then label the name in the right format, in this case First Name Last Name. Label the Player Note column. All the other columns should be labelled as Other (i.e. do not import into the Compiler).

 

Then fill in the other boxes, identifying the first player row (3), the last player row (52) and check off that you are not importing Projections to the Compiler, but you are importing Custom Fields and Notes.

 

 

8. Lets click to the QB Clean tab just to see what is going on. Note I hid some of the projection columns so we could see all the columns that impact us. The notes look like they are coming through okay. What is up with the Custom 1 column? Even if you see what look like errors here, these may only be temporary, so don't assume the worst at this stage. Keep going.

 

 

9. Back to the QB Raw tab and click Copy to Compiler.

 

 

10. This is the first time we've seen the Cheatsheet Compiler make an appearance. We're on the QB tab at the spots where we expect Pal to put our data. It appears the notes copied over to the Compiler but the rankings did not.

 

 

11. The problem is, all the data that we want to import needs to be to the right of the player names. Our player rank/Custom 1 column was not; it was on the left. To solve that, simply copy that player rank column in Pal to the right of the player names.

 

Remember, once you pull in the data off the webpage, that's all it is - data. You can manipulate it anyway you like or need to so it can work its way into the Compiler.

 

 

12. I've copied the player rank data and I'm going to paste it into that useless "keeper" column to the right of the player names. Obviously, I don't want to paste it over anything else I want to use. I could paste it even further right, beyond the player notes if I wanted to, as long as I label the column heading correctly. Re-label our initial Custom 1 column as Other. Label the column we copied the player rank data to as Custom 1.

 

 

13. Now we click to the QB Clean tab, all looks good. I see player ranks and player notes being pulled from the QB Raw tab.

 

 

14. Back to QB Raw, click Copy to Compiler again.

 

 

15. We're now in the Compiler on the QB tab, and look at that... the player ranks are lined up with the player names in the Compiler. The player notes, where applicable, and lined up with the player names.

 

Note where there wasn't a player note it input a 0 (zero). While that section is highlighted, then we can quickly get rid of that by selecting Edit > Replace > Find: 0, Replace: [blank] and check off "Match entire cell contents". Then choose Replace All.

 

 

16. Nice and clean after our Edit > Replace step.

 

 

17. And where does this go? It feeds off the position tabs in the Compiler into the new alternate cheatsheets with player notes (cs with notes tab). You can see the Custom 1 column is filled with the alternate player rankings, and the Player Notes column is filled too.

 

The Player Notes field on the cheatsheets will test on a player-by-player basis whether the User portion of player notes have data from the position tab. This is what we just filled in. If it is, show that. If the User portion is blank, then show the FF Today portion of player notes. That is why that first note for Peyton Manning was blank from the Projection Pal import, but shows the not so informative FF Today portion "Test player note for Peyton Manning" right now.

 

Now I really think I should have done another screencast instead

[Mike MacGregor 20]

New board screwed with my tutorial

[Mike MacGregor 20]

Fixed

[superwizbang 0]

This might be covered in other locations but for the stats that are not imported (ie., the compiler has a column for a particular stat but your import does not have a value for that stat), they are not averaged with a zero value are they? My scenario is this:

 

1.) I am importing QB stats from "Site B" into the compiler from the projeciton pal.

2.) Site B does not have numbers for passing attempts (null).

3.) The compiler does (say 200 attempts).

4.) Theoretically I have chosen a 50/50 weight ratio for my stats between the two sources.

5.) I compile and create a new cheatsheet.

 

For the stat in question, passing attempts, because the compiler has a stat and the Site B column is "null"/has no value, will the compiler default to 100% FFToday (compiler default stats) for that particular statistic? It won't take the average of 200 and (0) will it?

 

Thanks!

 

-Ken

[Mike MacGregor 20]

Hi Ken. It will take it as zero and average it. What I suggest in cases like that is to copy the FF Today projections for PaAtt, and paste them into Site B PaAtt, if your league does use PaAtt in the scoring.

[superwizbang 0]

Hi Ken. It will take it as zero and average it. What I suggest in cases like that is to copy the FF Today projections for PaAtt, and paste them into Site B PaAtt, if your league does use PaAtt in the scoring.

 

Ok, thanks. As I understand your reply, if my calculations (league) does not require the field, it will be ignored in the cheatsheet? A bit if a dumb question but I would like to be sure. Using my example, if my league does not use PaAtt as a stat, then the null value will be ignored anyway since it is not calcuated?

 

I might suggest for next year's version that we update the calculation to include and if/then statement which looks for null values and defaults to 100% FFToday.

 

-Ken

[superwizbang 0]

Whilest I think about it, using your example above for "comments", if I choose to import comments from another site, does that field append Site B's comments to the FFToday comments or will it replace with the new imported comments? I would rather not overwrite your comments with my imported comments but would rather add them to the existing field.

 

-Ken

[Mike MacGregor 20]

, if my league does not use PaAtt as a stat, then the null value will be ignored anyway since it is not calcuated?

 

That's right. Reads it as zero. Adds zero fantasy points to the calculation.

[Mike MacGregor 20]

Whilest I think about it, using your example above for "comments", if I choose to import comments from another site, does that field append Site B's comments to the FFToday comments or will it replace with the new imported comments? I would rather not overwrite your comments with my imported comments but would rather add them to the existing field.

 

-Ken

 

Comments imported into the Compiler go on the position tabs in the "User Notes" column. This is in part so they don't overwrite the FF Today comments, but also, importantly, so when you run the update it doesn't overwrite comments you've put in there.

 

Now the way it works pulling the comments to the cheatsheets (cs with notes tab), if there is a User Comment, then it shows it. If there is not a User Comment but there is an FF Today Comment, then it shows that. Otherwise, show nothing.

[GoatLocker 0]

Guess I'm blind in one eye and can't see out of the other, but where at on the site do I download the Projection Pal from?

 

Love what I see of both the compiler and Draft Buddy so far.

 

Cliff

[Mike MacGregor 20]

Guess I'm blind in one eye and can't see out of the other, but where at on the site do I download the Projection Pal from?

 

Love what I see of both the compiler and Draft Buddy so far.

 

Cliff

 

Thanks Cliff. Projection Pal is a separate download from the same member access download page that the Compiler and Draft Buddy are downloaded from. The link to Pal is in a small box on the right of this page.

 

http://www.fftoday.com/download

[m4rk0 0]

17. And where does this go? It feeds off the position tabs in the Compiler into the new alternate cheatsheets with player notes (cs with notes tab). You can see the Custom 1 column is filled with the alternate player rankings, and the Player Notes column is filled too.

 

The Player Notes field on the cheatsheets will test on a player-by-player basis whether the User portion of player notes have data from the position tab. This is what we just filled in. If it is, show that. If the User portion is blank, then show the FF Today portion of player notes. That is why that first note for Peyton Manning was blank from the Projection Pal import, but shows the not so informative FF Today portion "Test player note for Peyton Manning" right now.

In this example you pulled in the rankings of QBs from a different site into Projection Pal. After "cleaning up" the data and fields you added the data to the compiler. The compiler now has a column populated with data that ranks the QBs per the other site. So now what does that column do? Is it part of a calculation that averages the rankings of the QBs? Is is just there as an FYI?

 

I guess I'm confused to the purpose of doing all of this. I tried to recreate the steps and it took quite some time. After importing it into compiler I noticed that column AA was not populated with anything, hence my "custom % allocation" seems like it is not working. Also, columns DY to EJ are not populated even though I see that the QB Clean tab in projector is populated with the same info.

[Mike MacGregor 20]

Hi m4rk0. Purpose? Understand that using Projection Pal is completely optional. The Compiler includes default projections (which we think are great), so there is no need to import other projections or any other info if you don't feel it is beneficial. Many people like adding different projections or data to the Compiler to help create or enhance their cheatsheets, which is why these features are in there, but not everyone uses it. In fact, I would gues the people who use it are in the minority.

 

As for whether the imported data adjusts the cheatsheets or not, there are couple different types of data that can be added. One is projections, which are detailed stats and can be mixed in with the FF Today projections and past stats using the allocation key system. This will adjust the projected fantasy points, which works its way up to the cheatsheet rankings.

 

The other two types of data - both new this year - are Custom Fields and Player Notes. Neither of these will impact the player rankings. They only appear on the cs with notes cheatsheets, and are there for reference purposes. Since they are not detailed stats, there is not an appropriate mathematical way of mixing this data (which could be anything) with the exisiting formulas for calculating the projected fantasy points and altering the cheatsheets.

[m4rk0 0]

Thanks for the clarification Mike.

 

Another problem I am having is the player name column. If I import the data in "last name, first name" format and use that as the column type, it does not translate into the "clean" tab. I am forced to go back to the "raw" tab and manually change the field into "First name last name" and select that as the column type. Am I doing something wrong. Manually changing each player's name will be a tedious process.

[Mike MacGregor 20]

Thanks for the clarification Mike.

 

Another problem I am having is the player name column. If I import the data in "last name, first name" format and use that as the column type, it does not translate into the "clean" tab. I am forced to go back to the "raw" tab and manually change the field into "First name last name" and select that as the column type. Am I doing something wrong. Manually changing each player's name will be a tedious process.

 

Leave it in Last Name, First Name format. Once you hit Copy to Compiler then Projection Pal will fix it for you.

[trobes 0]

When I try to put an address into the URL field I get this message:

 

Data on the clip board is not the same size as the selected area.Do you want to paste the data anyway?

 

I click OK and then this message pops up:

 

Cannot change part of a merged cell

 

Then I get this message:

 

Excel cannot paste the data

 

So...what am I doing wrong?

[Mike MacGregor 20]

Probably just Excel being finicky about the cell the URL goes into. It is a merged cell. Try clicking on that cell, and then hit F2 (edit). Also clicking the formula bar while on that cell will put you in the same edit that cell mode. Now try CTRL-V (paste).

[NickCar54 0]

Hi Mike! This is my 2nd year of the CC/DB and I find the whole program to be great! I've been trying to learn how to use the Projection Pal and it was going fine until I hit #9 on the instructions. When I click on "copy to compiler" I get: Run-time error '1004'. I don't know if I located the file in the wrong place or what. If you can help me get past this problem, it would be greatly appreciated. Thanks, Nick

[Mike MacGregor 20]

Hey Nick. Thanks! Glad to have you back.

 

Are you using Excel 2007? A run-time error typically means that Pal cannot find the Compiler. If you are using Excel 2007, then you need to change the Compiler filename on the setup tab in Pal to have .xlsm (Excel 2007 format) on the end. By default, the full filename is: football_compiler_2008_1.1.xlsm

 

Also make sure both Pal and Compiler files are in the same directory, or better yet make sure both are open when you hit Copy to Compiler.

[NickCar54 0]

Thanks Mike! That was the problem, I have Excel 2007. Is there any way to change the filename to xism permanently or will I have to input it every time? If I have to its not a big deal, just wondering... I have enjoyed working with the CC/DB and have received a lot of useful information from all the writers on your site. I will be a here for as long as I play fantasy football! Nick

[Mike MacGregor 20]

When you change it once and hit save then it will always stay the same when you open that copy of Pal. If you open Pal from the download file again, then it will be back to the original. There is no reason to do that though, so you should be all set.

[skittles 0]

Awesome tutorial. The compiler just gets cooler and cooler, and more and more impressive. Gonna love loading some stats into this thing. Last year I just used the base set, and loved it, but with the chance to almost endlessly customize my cheatsheets to my tastes and preferences, this tool is invaluable.

 

Thanks again.

[TBayXXXVII 2,116]

I am using Excel 2007. When I put in the url to the Projection Pal, I get "forgot password?". I used XP last year and never had that problem. I also have XP on a PC at work and don't have this problem. I'm pretty sure it has to deal with the password to the site that I'm using, but like I said, in Excel XP, it's not an issue.

[Mike MacGregor 20]

I am using Excel 2007. When I put in the url to the Projection Pal, I get "forgot password?". I used XP last year and never had that problem. I also have XP on a PC at work and don't have this problem. I'm pretty sure it has to deal with the password to the site that I'm using, but like I said, in Excel XP, it's not an issue.

 

Interesting. Are you able to get past it once it asks for a password, and continue on through Excel?

[TBayXXXVII 2,116]

Interesting. Are you able to get past it once it asks for a password, and continue on through Excel?

 

It doesn't lock anything up, it's just what is in the first cell. In the corner at 1 / 1.

[TBayXXXVII 2,116]

Interesting. Are you able to get past it once it asks for a password, and continue on through Excel?

 

It doesn't lock anything up, it's just what is in the first cell. In the corner at 1 / 1 (where you do the "Edit Query" stuff), and it doesn't not import the projections. That was in the just the QB Raw tab, I didn't check any of the other positions though.

[TBayXXXVII 2,116]

Nothing...?

[Mike MacGregor 20]

Sorry TBay. I thought you said it "doesn't not import the projections". Does it import them, or not? Assuming not, then I think the only solution is to login to the site through your browser, and then copy the projections off the web and paste them into Pal. Basically, skipping the web query steps in the tutorial. Once you paste them into Pal, then continue on with the remaining steps.

[TBayXXXVII 2,116]

Sorry TBay. I thought you said it "doesn't not import the projections". Does it import them, or not? Assuming not, then I think the only solution is to login to the site through your browser, and then copy the projections off the web and paste them into Pal. Basically, skipping the web query steps in the tutorial. Once you paste them into Pal, then continue on with the remaining steps.

 

Sorry, typing faster than I think. It doesn't import them. I'll give it a try and see what happens.

[G-bomb 0]

When I import from fantasy sharks, the player names are in LastName,FirstName format. There is no space between the comma and FirstName. It appears this is causing problems. Besides updating every single player with a space, is there anything I can do?

 

Thanks.

[Mike MacGregor 20]

Hi G-bomb. Yep, that is a problem but one we can solve easily. Highlight all players on the Raw tab. Choose Edit > Replace, and in the "Find" box put a comma ",". In the "Replace" box put a comma-space, ", ". Hit Replace All. Repeat for each Raw tab. Should take about 30 seconds total. Go!

[Mike MacGregor 20]

For those following along these tutorial steps, you may find my new screencast Pal tutorial helpful. Watch the steps in action as I import ESPN projections into the Compiler using Pal. You can find it here:

 

http://www.fftoday.com/compiler/screencast...n_pal/index.htm