04-27-2010, 08:19 PM #1
Player Checklist's from Beckett's site - How to do it
There's been plenty of posts asking about it, so I thought I'd go through with a step-by-step way of creating an Excel spreadsheet from Beckett's website.
The complaints are always that the links / icons paste into Excel and it's messy..... and that all the information is in one column. Using the features in Excel & Notepad, we can fix this.
This will NOT give you a perfect checklist. There is too much of a variance in too many of the rows to make it perfect. This will get you something very close to perfect.... and a little bit of manual touching up will make it look great.
The list below makes it seems long, but in all honesty - it takes me about five minutes to do this with a player who has 800 cards. It's EASY, you just need to know how to use excel.
(Notepad gets used at the beginning, because it's SO basic, that it doesn't understand the links, and just removes them).
Here's what you do:
- Beckett's Main Page. Type name in search box, press enter
- Click the List View tab
- Change to 128 items per page
- Copy and paste the search results into NOTEPAD
- Repeat for each page of results
- If you copied any of the page headers (something like "SELECT : All None Add To Organize ") remove them now
- Begin Search & Replace to remove or edit unwanted data:
- Search for "Card 1" replace with "1"
- Search for "Card 2" replace with "2"
- Search for "Buy Pricing!" replace with nothing
- Search for "Item has no pricing." replace with nothing
- Search for player's name (i.e. "Bill Ranford") replace with nothing
- Now all the Junk has been removed
- Copy and paste the whole thing into an Excel sheet (one column)
- Go to Data tab
- Highlight your column, press "Text to Columns" button
- Wizard Opens up
- Step 1 - Choose Fixed width, press next
- Step 2 - Excel will likely recognize that the year data is the first column (YYYY-YY) It might now. You may have to adjust. Once the line is adjusted to being just right of the years, press next
- Step 3 - Ensure 'General' is selected. Click finish.
- At this point, you may have to make a few manual corrections. Some sets are listed as 2009, instead of 2009-10 (as an example).
- They will have cut the first few letters from the set name, and kept it with the year. Move them back. Make year look the same as the others, if you like
- Highlight the 2nd column (not the Years) and do a search for "S#". Replace with "S~"
- Highlight the 2nd column (not the Years) Click Text to columns
- Wizards Opens up
- Step 1 - Choose Delimited, press next
- Step 2 - Select 'Other' and put a # into the text box. Unselect any other options that may have been selected. Press finish
- This should have split any base cards nicely. Year / Set / Number are the three columns
- Click the Home tab
- Highlight the column with the Numbers (plus the extra info it's got)
- Click on the left Justify button (Much easier to follow along if you do)
- Go back to the Data tab
- Highlight the 3rd column again (with numbers, etc). Click Text to Columns
- Wizards Opens up
- Step 1 - Choose Delimited, press next
- Step 2 - Select 'Other' and put a ~ into the text box. Unselect any other options that may have been selected. Press finish
- This has created a 4th column, with serial #ed print runs in it (this is why we replaced S# with S~. It created a unique character to split the text with, and wasn't messed up when we split on card numbers)
You can repeat that step as many times as you like, trying to filter out Memorabilia, Autograph, or any other designation in the 3rd column text.
If anyone has any questions, please ask. It really is pretty easy - so if I haven't been clear with anything, I'll try and explain better.
04-27-2010, 08:39 PM #2
Good Idea, but I give it 4 days before someone makes a post asking where to get a players checklist. lol
Last edited by zunato; 04-28-2010 at 02:22 AM.base wants/needs;Hidden Content
Looking for 12/13 OPC Blacks
04-28-2010, 02:04 AM #3
Very nice this is here Sean. I hope everyone sources this out.Searching for: Jagr, Palffy, Stumpel, and Boyd
Here is my Hidden Content My Photobucket is out of date. I am now fully uploaded to the SCF Inventory Manager!
From the land of Rider Nation, and the Home of the 2013 Grey Cup Champion Saskatchewan Roughriders!!
04-28-2010, 01:28 PM #4
Great info and nice to know that there are more features that i didnt realize that will better help. I mostly copied everything into notepad and ran it through an app that would clean it up for me before I moved it to excel.
But apparantly according to Beckett you cannot do this because it violates their Terms of Service Agreement.
Im not here defending Beckett, personally i think they are now since they have changed everything about them. But thought i would let people know.
Last edited by RGM81; 12-15-2010 at 11:16 AM.Collecting:
Rick Nash (942/2498 37.710%) (17 Incoming)Hidden Content
$210 saved and Marching towards a Premier rookie /99 to be #1000
06-08-2010, 12:42 AM #5
excellent step-by-stepEric Staal PC= 1791 different and counting
06-08-2010, 04:32 AM #6
I should add - for paying subscribers of Beckett's online price guide. There are secured .PDF checklist files available to save you the hassle of having to transfer everything to Excel. All you have to do is type in the player's name in the search query, click on the player's name after the search, and there will be a link to the right of the player's photo. It will list all Beckett listed cards for that specific player, set, subset, etc. along with the current book values at the time of you downloading the file.
06-09-2010, 07:41 PM #7
LOOKING FOR DOUG GILMOUR & LEAFS
06-10-2010, 12:20 PM #8
Sorry Mr. Ozzie - I cant seem to accomplish this feat!!
There is no photo or download button. I am however a Beckett Online user looking for Claude Giroux.
Could you please elaborate?
06-11-2010, 02:09 AM #9
06-11-2010, 11:54 PM #10
Okay, I've attached a few images below for a step-by-step illustration. I used Phil Kessel as an example. Let me know if it's not showing up.