Hello all! I have been gone recently because I am in the middle of a home re-model… taking up lots of time ( and money ) I wanted to pitch a “new” idea in tracking collections. I am currently using a nice spreadsheet I created in excel to keep track of my purchases and how my “investments” are doing. I also use the spreadsheet to track my percentage complete. This is where I ran into a problem. I have a mercury dime collection that is almost complete, I am only missing three coins ( can you guess what they are? ) 1916-D 1921 1921-D According to my excel spreadsheet I have 96% of the coins, but that is not altogether true. A standard calculation of percentage really is not applicable, as it would “value” a 1916-D and a 1945-S as the same 9 as far as percentage is concerned ) so I have devised a different way to calculate percentage of completion. For this example I will use my commemorative type set collection, as it is the only one I have completed. First I create a list of all of the coins in the collection, in this case the classic commemoratives are 50 coins. I list each one out by name, in the next column I put an average market price and round up to the nearest $25.00 range. I am using grey sheet bid prices and make a price assumption based on what I am willing to pay for a coin ( some coins jump a tremendous amount between grades ) After I am done with the price I give the coin a “point” value by dividing the price by 25, basically a coin gets 1 point for every $25.00 it takes to purchase it. In the next column I use to track what coins I have purchased, and enter the coins points I have earned. Now I can take the points I have earned, divide it by the total points, and get a weighted percentage complete. This gives a more accurate percentage complete, and fits into the old rule “buy the key coins first” Any ways, I hope this makes sense and would be of help to some of you all. I have attached a zip file with an example spreadsheet. Some notes on the example: This is not my collection, just an example. Try playing around with the numbers! It shows how if you purchase a Hawaiian Sesquicentennial first, you actually have 18% of the collection complete, which makes sense considering that it is the most expensive coin in a whole raft of expensive coins. This makes sense to me… To better illustrate what coins are “owned” in the collection I have highlighted them. Anywho, I am also considering working in a points system based on grade, it would make it more complex but would account for “filler” coins. In my example a Grant memorial coin is worth 6 points for an ms-64 example, lets say I purchase an AU coin as a filler, it may only be worth 3 points. Even if my collection was complete as far as number of coins go it would not mark my collection as 100% complete because I have fillers… I may be complicating the idea a little to much right now I wanted to put this out there as an idea I had and to see what you guys and gals thought. As a side not this just shows how powerful using excel is to track your collection, you can do so much more then the software you purchase. Plus you learn skills and tricks using excel that translate into the job market Cheers!
There are quite a few collectors that invent thier own spreadsheets with Excell. There are also a few coin collecting programs for the computer out there. They all work very well if your into being a dealer some day and want to make sure of how much you paid versus how much you will sell them for. This worksheet appears to work well for you though. However, I've found that over the years as the quantity, quality and variety of coins increases, a spread sheet becomes very extensive and tiresone. Maybe that's because I'm to old and have lost patience with many things. Myself, I don't care to try to keep track of what I have, only what I want or need. For example I have 10 sets of Lincoln Cents with the highest quality in set #1 and progressively lesser quality going toward set #10. All are basically complete except for the last 3 sets missing the 55DD. With a large collection it is easier to keep a list of what is needed rather than what is present.
my full spreadsheet took some time to set up, but now that its done its super robust. this is just one more thing i am thinking of adding to it. I even have a cool part that gets live gold and silver prices from the internet, then calculates what all of my bullion is worth
