Excel/Spreadsheet Help

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
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
0
Comments
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.
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.
Collecting:
Brett Favre Master Set
Favre Ticket Stubs
Favre TD Reciever Autos
Football HOF Player/etc. Auto Set
Football HOF Rc's
Send me an email and I'll reply with an Excel Spreadsheet I created and use.
Mike
sfmays24@hotmail.com
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
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.
Seeking primarily PSA graded pre-war "type" cards
My PSA Registry Sets
34 Goudey, 75 Topps Mini, Hall of Fame Complete Set, 1985 Topps Tiffany, Hall of Fame Players Complete Set
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.
My Collection
Oops just noticed its a little out of date
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
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!
TGF Collection
TGF Sports
Joe
Arthur
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.
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!
TGF Collection
TGF Sports
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.
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
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!
TGF Collection
TGF Sports
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.
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.
My daughter was diagnosed with type 1 diabetes at the age of 2 (2003). My son was diagnosed with Type 1 when he was 17 on December 31, 2009. We were stunned that another child of ours had been diagnosed. Please, if you don't have a favorite charity, consider giving to the JDRF (Juvenile Diabetes Research Foundation)
JDRF Donation
<< <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!!!!
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!
TGF Collection
TGF Sports
Does anyone use their spreadsheets on their PDA? What platform works best? What brands should I avoid if I primarily use Excel?
Collector of Pittsburgh Pirates cards for a slightly less stupid reason.
My Pirates Collection
<< <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.
Now I'm going to go chart some things.
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
Change the format of the cell to text from general or number.
Cheers,
MrG
TGF Collection
TGF Sports
Commissions
Check out my Facebook page
Google Spreadsheets works great for cards.....And it's freeeeee
Check out the link to my Allen and Ginter cards... docs.google.com
CDsNuts, 1/9/15
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
TGF Collection
TGF Sports
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?
Commissions
Check out my Facebook page
<< <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.