Collection Tracking, Spreadsheets & Headaches

I have been collecting coins more seriously over the last year or so. Just about every night I am sorting through a pile of coins to find goodies I am missing from my collection. Within a month or so of collecting I became frustrated and quite overwhelmed. I almost stopped collecting because I couldn't figure out a way to catalogue everything that gave me the required reports without a lot of friction. For a long term project such as coin collecting I have strict requirements: Information must be available offline, easy to parse through, easy to add coins, and ideally under my own control.

While Numista is a fantastic resource, The collection and series tracking in my opinion is quite horrible, Especially if you collect by sets, and this is where my problem is born.

1 of every year, type, and mint.

That is the goal I have set forth. Is it doable? No. Is it insane? Yes. Should you also do it? Ill leave that up to you.

I made several posts asking about spreadsheets that collectors use, Talked to several collectors in person and It seems that each person who gave me a new spreadsheet example collected in a completely different way.  In this post I would like to share my own spreadsheet and hopefully give inspiration or ideas for new collectors, or for collectors who want a tighter grip on their collection. This spreadsheet is a Google Sheets  example but can be converted to excel with some formula changes.

Dashboard:

The Dashboard I have created is made up of seven different sections, each updating using various formulas from the ‘Total Collection’ page in the spreadsheet. Nothing here should be touched and should be allowed to auto update when needed.

Section 1: Header and overview

This section displays metrics such as coins owned, Coins you have marked Wantlisted, Coins you need to check if you still own them, Tracked completion percentage, tracked total, oldest owned, price paid vs estimated value vs face value. and more. This is the at-a-glance collection metrics. These use some enhanced formulas but nothing too insane. For example the Newest Owned formula is:  

=IFERROR(MAX(FILTER('Total Collection'!F3:F,REGEXMATCH('Total Collection'!I3:I,"^(Owned|Check Owned)$"),'Total Collection'!F3:F<>"")),"")

Section 2: Recent additions and most valuable items:
 

 This section shows the 10 most recent additions to your collection as well as the 10 most valuable items in your collection based on Estimated Value column.

Section 3 & 4: Graphs

 

I have this section showing collection value over time including worth, as well as the year occurrence graph for coins that I own.  in my sheet the peak years seem to be 1999-2011 across all US denominations. Numista gives you similar graphs on your homepage and I liked them so I took them for my spreadsheet.

Section 5: Completion Status
 

This sorts out what you have in your wantlist and what you own and gives you a visual representation of your progress. 

Section 6: Storage & Totals
 

This field can be auto populated based on what type of storage you use. For example If you use binders instead, In the coin entry add “Binder x” or however you mark your storage, and then in this section type that same storage name, and it will auto date range, show how many coins are in there, their face value and estimated value. This gives a quick visual guide to know where to look at a glance and how much each storage location is worth. If I wanted to find my 2002 quarter, This tells me I should look in BOX-US-QTR-002 which ranges from 2000-2015. 

Section 7: Timeline & Milestones
 

This is simply a date and text space for you to add your own events and milestones you find fun. I use this for high value days where Im sorting through hundreds of coins, or when I got my first proof set, or finally got a coin Ive been looking for for a while. 

All of these sections are auto calculated based on the information entered into ‘Total Collection’ page.

Total Collection: The heart of the data

The total collection has 4 main sections of data: Specifics, Quality, Worth, and Acquisition & Storage, 

Specifics:
 

In the specifics section, you get your numista number, Internal ID, numerical value, denomination type, series/program, year, mint mark, design/type, and status whether it be owned, Wantlisted, check owned, or blank as a reference only. Each is automatically color coded upon selection. 

Quality: 

This section allows you to enter a relative grade, and if that coin needs an upgrade.

Worth:
 

Currency type, Price Paid, Face Value, Estimated value, Metal Type, weight, finess, silver content and melt value are all things you can add here. After you add weight and finess, the silver content and melt value are auto calculated. I enjoy adding weight to non precious coins so I can check how heavy the total collection is because I think that is a fun metric.

Acquisition & Storage:
 

Lastly,  This section gives you a place to add the date you added to your collection, where you got that coin from, where that coin lives in your collection and a small space for notes.


Filling out these categories can take quite a long time especially if you are adding catalogue entries as references for your want list so that you can get an accurate completion percentage. Though I do believe it is worth it and for me has made collecting a LOT more fun. Seeing that percentage tick up every time I add a few more pennies to my collection and then back down as I add more catalogue entries to my wantlist.

Its taken a few months to get this spreadsheet set up the way that I like it and I think it is a fantastic way to get a little more granular with your collection.  I also found it helps when I sit down to sort through piles of coins, I dont need to constantly change the numista page for wheat cents, and memorial cents, I just scroll up and down and if its red, set it aside to be added to the collection. That alone has made the collection process MUCH faster and much more enjoyable without having to memorize the catalogue number or have several open tabs.


So what do you think? Overkill or underkill? Would you use a sheet like this? How do your spreadsheets compare? If enough people are interested, I will post a link to the sheet so you can browse and check it out for yourself. I also have a world coin spreadsheet that is much the same, with a few other sections to show countries and under/over represented areas geographically.

Massive overkill, but I am really impressed with what you have done, it's clear and concise, but with silver prices, value etc - its a lot of work to maintain. It looks like you collection is quite small and manageable now, but wait until you have  a mega collection of 10k or more coins and your collection is worth tens or hundreds of thousands like mine.

Myself I use a Excel worksheet and have been beavering away at it for 6 years or so. I only have around 4,000 coins listed on it, basically any of the main countries I collect and only coins with precious metals unless part of a large series like UK silver denominations (In which I include the 1947 - 1970 issues in cupro nickel for continuity). 

This is my main page listing categories, Gold and Palladium with UK coins (My number 1 country) at top and I have just blanked out the values, as thieves could publicy see them. But I have listed date ranges, value, comments (Boneyard is when coins are replaced with better ones and I keep the replaced coin in one of my A or B silver boxes, hence why their value will be in Bulk silver or Better silver). Dates are when I started collecting that country or denomination seriously. The next date is when I started doing a sheet for it. Each line is a worksheet I can go to except 6d to 1d, British/English which is a single long page.

On Individual pages I list the following info, Date, Type, Condition, when, who, how much and where I bought the coin, its value in my opinion (This needs constant updating due to silver values) and then a comment on the state of the coin. This page shows Morgan Dollars.

I am also a fan of colour coding for date and values as you can see.

The page for the UK Halfcrowns is similar, but a bit more precision and I love to go to town with colours.

I update it all the time and save it back up to my hard drives.

Most of my world change and cheaper coins (Pennies, bronze coins etc) have not been listed but I may do British bronze eventually as I have done Pennies for NZ, Australia, South Africa and US pennies to 1909, Canadian pennies to 1936 and nickels to the same year. Any silver (Half dime, 5 cents Canada up) is fully done. But I will only do significant dates, not like 1981 cent, as my minimum value for any coin is $1, just to keep it easy to use.

I used to like tracking my collection in an Excel file a few years ago, but then Xavier was encouraged to create a customizable export file, and my need for Excel tracking practically disappeared.

I still keep a few useful tabs, for example, to see at a glance which circulating and commemorative euro coins I don't have, or some references to numismatic websites and books.

I continue to monitor the evolution of my issuers to see how things change. For example, the name changes from Puducherry to Pondicherry or from Korea to Joseon. Ober Ost becoming German Occupied Territories and then reverting to Ober Ost. British East Indies, which disappeared two years ago, has reappeared as British Singapore (you have to keep up...).

And especially to keep track of the issuers that disappear because they are merged and my new issuers.

For example, I recently lost the issuer "Kwangtung Province", where on earth did my coin go?

Well, I've looked everywhere, but I won't find it tonight :)

Rather than tracking my collection in Excel, I find it more practical to search within the Word pages of my binders.

But surely my Kwangtung Province coin should be among the Gangxu coins! No, I can't find it there, my classification is far from perfect ...

That looks really complex, you guys are light years ahead of me!

I should have mentioned, all the coins I own are listed on the Numista catalogue, but it would take me forever to do an excel entry on every single one and some near worthless coins like worn and misty Britannia pennies, US 1 cent coins, Aluminium communist 1 and 5 kopekniks etc, in my opinion would be a waste of time.

Moneytane

Massive overkill, but I am really impressed with what you have done, it's clear and concise, but with silver prices, value etc - its a lot of work to maintain. It looks like you collection is quite small and manageable now, but wait until you have  a mega collection of 10k or more coins and your collection is worth tens or hundreds of thousands like mine.

 

Myself I use a Excel worksheet and have been beavering away at it for 6 years or so. I only have around 4,000 coins listed on it, basically any of the main countries I collect and only coins with precious metals unless part of a large series like UK silver denominations (In which I include the 1947 - 1970 issues in cupro nickel for continuity). 

 

 

This is my main page listing categories, Gold and Palladium with UK coins (My number 1 country) at top and I have just blanked out the values, as thieves could publicy see them. But I have listed date ranges, value, comments (Boneyard is when coins are replaced with better ones and I keep the replaced coin in one of my A or B silver boxes, hence why their value will be in Bulk silver or Better silver). Dates are when I started collecting that country or denomination seriously. The next date is when I started doing a sheet for it. Each line is a worksheet I can go to except 6d to 1d, British/English which is a single long page.

 

On Individual pages I list the following info, Date, Type, Condition, when, who, how much and where I bought the coin, its value in my opinion (This needs constant updating due to silver values) and then a comment on the state of the coin. This page shows Morgan Dollars.

 

I am also a fan of colour coding for date and values as you can see.

 

The page for the UK Halfcrowns is similar, but a bit more precision and I love to go to town with colours.

 

 

I update it all the time and save it back up to my hard drives.

 

Most of my world change and cheaper coins (Pennies, bronze coins etc) have not been listed but I may do British bronze eventually as I have done Pennies for NZ, Australia, South Africa and US pennies to 1909, Canadian pennies to 1936 and nickels to the same year. Any silver (Half dime, 5 cents Canada up) is fully done. But I will only do significant dates, not like 1981 cent, as my minimum value for any coin is $1, just to keep it easy to use.

 

 

you do much the same that I do. Thankfully my silver price updates live on refresh of the spreadsheet, one of the perks of google sheets being online, I just pull the data off of a website that reports silver price and it throws it into my spreadsheet for me.  the estimated price of the coins is a sort of a balance between what they are worth in their respective conditions and what I would sell them for if I were to sell individually.

I wouldn't necessarily say its overkill, but its a LOT more work than 90% of people would be willing to do, that is for sure.

Moneytane

That looks really complex, you guys are light years ahead of me!

 

I should have mentioned, all the coins I own are listed on the Numista catalogue, but it would take me forever to do an excel entry on every single one and some near worthless coins like worn and misty Britannia pennies, US 1 cent coins, Aluminium communist 1 and 5 kopekniks etc, in my opinion would be a waste of time.

I wouldnt say lightyears. The sheets I maintain are for the sole purpose of giving me an easy to read percentage of what I own, where it is, what I paid for it and what its worth. I am a very numbers driven person and love watching the completion percentage tick up. I dont care if I have 50k coins if I cant see what percentage of the total available I am at. 

It's a lot of work, and numista has kind of fallen to the side with logging coins, but I am going back through and getting them all logged back into numista, slowly but surely. 

I like what you have done, I think I am the opposite, coins get listed into Numista lightning fast, but sometimes takes ages to land in Excel and even the bags for the country change coins.

I have often added new British coins to my excel only to find the coin next to them, I bought 3 months ago is not there yet. I spread myself thin too, I record weather stats, all my spending on food, bills, rent, money coming in etc and do monthly finance sheets, hence why I need a running total of what my coin collection is worth.

Coin photography is till spreading out and kept in my coin collection folder, again sorted into types.

My coin folder has this mother page and I can click on any folder, which opens to a menu like this

This is my USA folder and I use years as file numbers, in the case of upgrades of the same coin, I usually add a 3 or 4 etc for each upgrade (Some coins have had 2 or 3 upgrades) I tryt to standardise each image for denomination = pixel size ranging from 300px for tiny coins to 500 or 550 for Dollars and crowns.

As you can see above, some folders even have subfolders (In this case all the dollars have their own folder). I also use slang spellings like Dollaz for a giggle. 

The system has flaws as not every coin is photgraphed, some photos are borrowed from listing/dealer photos and especially coins bought in through large lots or I got when I was moving or in 2022/23 when my partner was sick and dying, there are gaps I am slowly reforming. Again its backed up on a external hard drive every few weeks and it has survived at least 2 computer upgrades. Its the main reason I need a full sized desk PC and not just a laptop or phone.

Moneytane

I like what you have done, I think I am the opposite, coins get listed into Numista lightning fast, but sometimes takes ages to land in Excel and even the bags for the country change coins.

 

I have often added new British coins to my excel only to find the coin next to them, I bought 3 months ago is not there yet. I spread myself thin too, I record weather stats, all my spending on food, bills, rent, money coming in etc and do monthly finance sheets, hence why I need a running total of what my coin collection is worth.

 

Coin photography is till spreading out and kept in my coin collection folder, again sorted into types.

 

  

My coin folder has this mother page and I can click on any folder, which opens to a menu like this

 

 

This is my USA folder and I use years as file numbers, in the case of upgrades of the same coin, I usually add a 3 or 4 etc for each upgrade (Some coins have had 2 or 3 upgrades) I tryt to standardise each image for denomination = pixel size ranging from 300px for tiny coins to 500 or 550 for Dollars and crowns.

 

As you can see above, some folders even have subfolders (In this case all the dollars have their own folder). I also use slang spellings like Dollaz for a giggle. 

 

The system has flaws as not every coin is photgraphed, some photos are borrowed from listing/dealer photos and especially coins bought in through large lots or I got when I was moving or in 2022/23 when my partner was sick and dying, there are gaps I am slowly reforming. Again its backed up on a external hard drive every few weeks and it has survived at least 2 computer upgrades. Its the main reason I need a full sized desk PC and not just a laptop or phone.

Thank you! Yeah everything gets logged to the sheet first and then go into numista after I have a rough grade of the coin. With numista you have to constantly swap pages to add coins but with my sheet you just scroll until you find your coin. I would LOVE to add images to the sheet but Im pretty sure it would crash with the sheer amount of photos, If I wanted to do that I would have to design my own private database. I love what you have done with your photos and the standardization looks amazing! 

As for the flaws, Every system has flaws. The important part is if you can live with the flaws or not. In the next few months I will be writing a script to compare differences between Numista and my spreadsheet. Very similar to how the old WinDiff software used to work to check each list against each other and find the differences and highlight them so I have a clear audit list. That way in the future I wont have to delete my collection in numista and start over!

Im pretty confident that I lost a box of coins when I moved a few months ago and a few hundred coins went missing. Plus I dont think I was removing inventory during swaps. For now and until I can find a better system. My spreadsheet will be my source of truth for my collection.

1 spreadsheet for US coins, 1 sheet for World Coins and then 1 master sheet which gives me high level stats against the two sheets.

These are all fantastic ways of cataloging and analysing your coins and must have taken you all an enormous amount of time, but for me would be an instant recipe for divorce.  I barely get a free pass to go out gathering coins for my ever increasing hoard and then have to get them pass the guards when I get home.

Enjoy your coins - however you decide to keep them.