Excel/Spreadsheet Help

XanaduNow

I'm thinking of starting a spreadsheet to keep track of my purchases/collection. I've got no problem doing it by hand with Excel but would be interested to hear if others use a particular type of software designed specifically for this purpose. A few questions ...

1.) Can you attach images to particular Excel entry lines?

2.) How do you sort by columns? (So I could just enter info and have the program sort all entries by either year of issue, price paid, value, etc?)

3.) Is it just easier to purchase a specific software designed to do this and, if so, which one? Where do I find it and how much does it cost?

Thanks guys.

Arthur

stormsurge

Not to sure about usinig Excess but have you ever tried Microsft Access?

stown

I signed up for Beckett's online price guide for one month at $5 (I think). You can pull up the checklists and then copy/paste into excel.

In one workbook, I created a tab for each year and then color code cards by have/need/upgrade. I have probably 15 sets cataloged and the file is 3 MGs. Recently, I added an auto sum which calculates the completion percentage.

You can add images or hyperlink via photobucket or something if you so desire.. However, if you want to send it back and forth through email, you may want to watch the file size.

jradke4

1.) For images you can create hyperlinks
2.) You can sort column data. I not sure how to describe how to do it but there is a function to do so. Check Excel help for a sort function. They are pull down menus. It allows you to cull through all the data and only show what you want in each column.

sfmays24

Hi Arthur,

Send me an email and I'll reply with an Excel Spreadsheet I created and use.

Mike
sfmays24@hotmail.com

AlanAllen

1. I haven't found a way to embed pictures in a particular cell. You can paste pictures and move them seperately, or use hyperlinks as jradke suggested.

2. Select the cells you want to sort, then go to Data->Sort from your dropdown menu.

3. No, Excel is perfect for this purpose IMO.

Joe

cravetopps

you can attach pics by right clicking on the cell you want the image to reside. Make sure you type a description in the cell first to describe the photo. right click on it, choose hyperlink and a pop up will ask you to select the file you want associated with the cell. Make sure you know where your images are first. After you select the image you can now left click on the cell and the image will pop up.

sorting columns - make sure you have all the columns named first. for example, column 1 might be the series, column 2 the year, col. 3 the card description....then the card #, cert # etc....

select the range of entries you want to sort making sure you have included the top row (with the column descriptions) then hit data, sort (make sure you select your data range includes a header row) and then you will be given a choice as to what columns you can sort on.

you can undo a sort if it doesn't look good to you.

hope this helps.

jskirwin

I use Excel for just about everything. No need for MS Access - unless you crave pain.

The key thing about Excel is consistency and simplicity.
Each worksheet tab is your set year/collection year.
Use the same header row on each tab.
Here's an example of mine:
Card # First Name Last Name Own Need Pending Rarity (1-10)

Add each card as its own row - or even better do what I do and list each possible card in each year/set and use "x"s in the Own column to tick off that you indeed own that particular card.

Then remember that autofilter is your friend. If you do the above, you can autofilter on the "Need" column and show only the cards you need.

stormsurge

My collection isn't all that large so I put it on a web page:

My Collection

Oops just noticed its a little out of date

larryallen73

I use a very simple Excel spreadsheet and really like it. I find Excel pretty easy to use once you play with it a little bit.

Fabfrank

Xanadu- A friend of Shagroths wrote a quick Excel spreadsheet for me that does this type of sorting (player name, year) which I use the track my player runs. I record price paid for a card, Cert #, etc. I can email you the file. It doesn't provide for scans, but it's a good start. PM me if you are interested.

SoFLPhillyFan

Maybe one of JEB virtualizard's spreadsheets would be good to use as a template and make modifications.

I use four of his right now and he's very quick to add a set on request.

JEBs Set Registry Spreadsheets


Keith

MrG

If you are savey with Excel, use the "External Data Import" feature to import PSA tables into a new worksheet. You can import anyone's Set Registry, and if you are a registered member, you can import the SMR & Pop Report into Excel! I import the '73-'78 pop reports into multiple tabs of an Excel workbook every month.

Here is what you do:

Open excel, which starts a new worksheet.
Select Data->Import External Data->New Web Query from the main menu.


A "New Web Query" dialog box will appear.
In the Address box enter: www.psacard.com and select GO. (Unless the PSA site is your home page, which will come up by default.)


Now, say for example you want to import someone's Set Registry data:
Select "PSA Set Registry" from the PSA main page, and the Set Registry Page is shown.


Select Baseball and the Baseball registered sets page appears.


Select 1973 Topps Master and all of the sets in the All Time Finest & Current Finest list appears.


Select the #2 ATF set "TGF Collection", and my set appears.


Select the yellow arrow identifing the table of my set, and it turns green with a checkmark.

At the bottom of the "New Web Query" dialog box select import.

You will be asked whether you want the data imported to the existing worksheet or a new sheet.


The data will then import into individual cells.

Once the data has imported you can manipulate the data with sort, filter, etc.

Enjoy!

AlanAllen

That's a fantastic tutorial, MrG! I've always just manually grabbed and pasted data from registry web pages, but the formatting isn't nearly as clear and it takes a little while. Thanks for the info!

Joe

XanaduNow

I agree. Thank you very much MrG. I greatly appreciate the time and effort put in to showing us all how to do that. I think you've made many members' lives much easier here with that. Thanks again.

Arthur

TheVon

In addtion to some of the other features and benefits that the others have mentioned already, one thing I've used Excel to do that I've found very handy is to create a simple, one page checklist that makes searching eBay for needed cards a breeze. In alternating columns I have the card numbers and in the other columns I have a blank cell. Once I have a card from that set I type in the card's grade in the blank cell next to the corresponding number and highlight both cells with a color based upon the card's grade. I also print out the checklist and that way, I can tell at a glance if I have the card that's listed on eBay and if I do I can still see whether or not it could be upgraded and I don't have to mess with toggling windows.

To avoid that annoying mistake of purchasing a card I just bought but haven't received yet I simply put the grade number in the appropriate cell and leave it unhighlighted until I've actually received the card.

Excel really is a great tool for this hobby though. I also use Excel to keep track of what I've spent on my cards and I use it to figure out the effect a certain card would have on my set rating. I've also been so anal as to use Excel to figure out how much it has cost me per 1.00 Set Rating to build a set.

I had better stop there before I publicly expose myself as the OCD hobbyist my wife already suspects I am.

MrG

TheVon,

I resemble your comments!

My Excel workbook is up to about 30 tabs now, and I try to import as much data as possible from the PSA & Beckett sites.


For each year I collect, I have a tab listing the contents of the set, cards I have graded in inventory, and color codes for status.
I have seperate cell blocks for cards that I have sold; and cards that I have cracked for re-grading or raw. I use the "filter" option to show & count
different grades & qualifiers, and have totals at the bottom of the sheet for total spend, average spend, total spend for a certain grade, etc.


I also have a tab for the POP report for that year, and for certain years I have a complete inventory of the set, including raw cards.


Excel is a great tool indeed!

larryallen73

WOW!

Maybe it's because I have a wife and two young kids but I can't imagine having enough time for all that. Very impressive work.

ctsoxfan

MrG - thank you for taking the time to present that! Very helpful.

TheVon

MrG, I feel better knowing I'm not the only one that does so much work with Excel! By the way, that green color you use to highlight cards on your 1973 set is my favorite color to use . . . it matches the green highlighter pens I have.

stown

Very impressive Mr. G.

Mine is comparable but not nearly as detailed and extensive. I only have +/- 15 sets and it's 3 MGs... What's yours?

Major props

MrG

Stown,

My file is only 2.7MB. I say only, that is quite large for an excel! I suppose I could do a better job with Access, and have many more searchable features, but I would have to learn Access. I know Oracle, but it is a little expensive to run on a home computer. And my favorite "dbase" went out with DOS.

Thanks for the Props!

Pharber

I'm more of a lurker than a poster. I'm usually on-line visiting these boards when the golf courses shutout in the Northeast. (serious another addiction).

Only cards I collect are Vintage baseball ('57, '58 and '60 complete and working on '59s and 67s) and Topps Heritage.

I've used Excel for my baseball cards, but compared to what some of you have done, it's finger-painting to your fine arts.

This is what I like about this site. It's not stuff like, "Yankees suck, Red Sox rule!," but things that can help you in this maddening yet infectious world of card collecting. Because of the things I've learned in the last day about Excel and PSA, I've been importing non-stop, putting everything in an organized manner. You have been truly helpful.

This is the best site in the hobby by far, thanks to all you posters.

stown

Here's an example of mine:



I have a color coding system: Yellow - have, blank - dont have, red - needs upgrade, and grey - pending.

If I don't have a card, I'll place a 1 in the column and autosum at the bottom.

The prices reflect Beckett on the left and SMR (5 - 9+) on the right.

Recently, I've hyperlinked the PSA cards I have with photobucket. It's really easy to get carried away and "geek" out.

Bunker

All I can say is WOW!

TheVon

<< <i> This is what I like about this site. It's not stuff like, "Yankees suck, Red Sox rule!," but things that can help you in this maddening yet infectious world of card collecting. >>



I beg to differ . . . the Yankees do suck and the Red Sox do rule!!!!

Porkchopper4

This thread is encouraging to me, as I thought I was the only person that spent a lot of time on Excel building these sportscard masterpieces! I really love Excel for this sort of thing. However, I am unfamiliar with Access, does anyone feel that it is better than Excel as far as sportscard databasing goes? If so, show us some of your screen shots and/or your thoughts on this.

TheVon

I'm curious to see if anyone has tried using the Smart Tags offerred by Excel. I know you can use them to import stock quotes into an Excel document, but I've never really experimented with using them other than for that. Does anyone know if you can use a smart tag to import your set registry information into Excel? That would be a handy one-step process to update your info.

MrG

TV,

Interesting thought. I have never worked with Smart Tags in Excel, so I had to find out if they might be useful. I consulted good-ole Microsoft help and the MS site for instructions. Based on what I read, this would be very difficult to do with the PSA site and the set registry. Basically you would need to set every cell with a Smart Tag for a particular piece of information from the registry. The use of Smart Tags to me, would be good for tracking stocks in a portfolio, doing a list of names or dates for an address book, etc.

If you use the "Import External Data" method I illustrated above, you can update each tab individually or all tabs at once with a mouse click; schedule an update every time you open the excel file; or schedule an update every minute, hour, day, etc. This is very easy to do with the Set Registry because it is not Username/Password protected.

What I did as I was writing this reply, was to create an Excel workbook named "1973 PSA Top3.xls". I created 3 individual tabs, and imported the set registry data for the top 3 active 1973 sets.



Once you use "Import External Data", an External Data Menu is shown either in the menus or by itself in the worksheet:


This menu provides you with various options for changing your query, updating your spreadsheet, or updating the entire workbook:


The Data Range Properties selection brings up a dialog box, for setting parameters for the download, and when you want a spreadsheet, or the entire workbook to update.

Place or remove tick's in the boxes for the following options:

Save query definition - The query used to import the data.
Enable background refresh - Allows you to continue working on a worksheet, while another loads.
Refresh every - Enter how often to update the sheet if the file is open. I.e. 60 min for an hour, 1440 min for a day, 10080 min for a week, etc.
Refresh data on file open - Tick this box if you want an update every time you open the file.
Preserve cell formatting - Do not change the format from the original import. (Tick this if you have added any formatting.
Adjust column width - Untick this if you have set your cell placement and you don't want the import to reset everytime you download.
And some options for what to do if the data has changed. (Like a new card/number is added to the registry composition.)

Other things I did, was to use the "COUNTIF" and "COUNTBLANK" functions to get an idea of how many of what grade each of the us in the top 3 had:



And then I made a chart showing percentages of each card:



The cool thing is that when I open the file again, or I update the data, the COUNT totals and chart data will automatically change with the new data!

Enjoy!

ArchStanton

MrG, my wife hates you. I have been importing data like a madman. I was never aware of that feature.

Does anyone use their spreadsheets on their PDA? What platform works best? What brands should I avoid if I primarily use Excel?

jskirwin

<< <i> However, I am unfamiliar with Access, does anyone feel that it is better than Excel as far as sportscard databasing goes? >>



Avoid Access unless you understand relational databases. Access is a very powerful tool, and I've used it on-off through the years. I'm an IT Architect/business analyst and while databases like Access have their place, Excel is by far one of my favorite computer applications.

It's easy to use, easy to understand, and useful for much more than just numbers and spreadsheets.

But I'm a serious Excel geek.

Porkchopper4

Thanks MrG!

Now I'm going to go chart some things.

Porkchopper4

There probably is a simple answer to this question but I've yet to find it.

When I try adding the cert. numbers that begin with a zero to my Excel spreadsheet, the zero in front disappears when I go to the next cell. Is there something I can do to keep that zero there (other than adding a "space" in front of the zero)?

Eric

MrG

Eric,

Change the format of the cell to text from general or number.

Cheers,
MrG

Porkchopper4

Thanks MrG, that works well. Now I don't have to fret about my cert numbers not lining up perfectly.

alifaxwa2

You can also put an apostrophe in front.... '00023423, BTW, this thread is awesome.

DavidPuddy

I've stopped using Excel for my cards because sometimes I'm at another computer and I don't always remember my flash drive with the Excel file in it.

Google Spreadsheets works great for cards.....And it's freeeeee

Check out the link to my Allen and Ginter cards... docs.google.com

TheMcgwireMuseum

This thread has been very helpful and Im very excited about my new lists. The only thing I cant figure out though, is I think I remember there was somebody that made these spreadsheets if you requested it, and they had the capability to show your gpa,weight, etc, AND would adjust if you manually added a card into your sheet? Am I mistaken, or is there a way to do this? I tried to import the 'set composition' table instead of my actual set to see if that would work, but it didnt.

MrG

You could be looking for this: JEBs Set Registry Spreadsheets

I attempted to import the EDIT/UPDATE pages, but have not been successful. I will try to resolve the issue and post my findings to this thread.

Cheers,
MrG

TheVon

So, have any of you Excel geeks started to fiddle with your spreadsheets to accommodate the new grading system? I was just about to start doing that when I remembered this thread.

Guru

Awesome thread to bring back. What ever happend to XanduNow?

alifaxwa2

Is there a way to use the auto-refresh Import New Web Query for beckett My Collections?

You need a password to get to it, and I am not sure if Excel allows for that?

I have tried to do it the way shown above and it doesn't work. Am I missing something? or an option I need to set up?

gumbyfan

<< <i>Awesome thread to bring back. What ever happend to XanduNow? >>



He posted a link to Net54 that talked about some activities by a company that advertises heavily in the SMR and then he went poof.

....yet Gary Daggett is still around.