Jump to content

Collection software/strategies/tips?


Nerosmyfavorite68

Recommended Posts

There's been topics about collection software and whatnot, but I don't know how to locate specific ones.

Better late than never, after 30 years of collecting I decided to try and make a spreadsheet.

Whoops, columns A-C weren't captured in the screenshot: 

A) WHERE B) WHO C)DENOMINATION  - I also added reverse, regnal year, officina (for LRBs and Byzantine). The ID (I changed that title to REFERENCE) would show what's what, but I can note anything special about the obverse/reverse in Notes1 or Notes 2.

I don't think I have Microsoft Access, nor do I have any experience with making databases.  I could make a word doc which looks like Excel, but I wouldn't be able to sort it. I would prefer a spreadsheet like Excel.  It's easier to sort.

This covers only catalogued coins, not bagged bulk lots.

1263108817_Coinspreadsheetexample.jpg.7ff8c9d31d7f3c5fcfed2c4cfe1518de.jpg

I couldn't figure out any easy way to add images.

Since around 2008 I've kept an offline copy of each online purchase.  Many of the htm files have a custom title, so it's fairly easy to find an itemfrom there.  Since it's the webpage it shows everything that was there when I made the purchase.

I haven't added collection number.  That would be another thing to figure out.

Any suggestions of anything obvious that I've overlooked?

 

 

  • Like 5
Link to comment
Share on other sites

11 minutes ago, Nerosmyfavorite68 said:

There's been topics about collection software and whatnot, but I don't know how to locate specific ones.

Better late than never, after 30 years of collecting I decided to try and make a spreadsheet.

Whoops, columns A-C weren't captured in the screenshot: 

A) WHERE B) WHO C)DENOMINATION  - I also added reverse, regnal year, officina (for LRBs and Byzantine). The ID (I changed that title to REFERENCE) would show what's what, but I can note anything special about the obverse/reverse in Notes1 or Notes 2.

I don't think I have Microsoft Access, nor do I have any experience with making databases.  I could make a word doc which looks like Excel, but I wouldn't be able to sort it. I would prefer a spreadsheet like Excel.  It's easier to sort.

This covers only catalogued coins, not bagged bulk lots.

1263108817_Coinspreadsheetexample.jpg.7ff8c9d31d7f3c5fcfed2c4cfe1518de.jpg

I couldn't figure out any easy way to add images.

Since around 2008 I've kept an offline copy of each online purchase.  Many of the htm files have a custom title, so it's fairly easy to find an itemfrom there.  Since it's the webpage it shows everything that was there when I made the purchase.

I haven't added collection number.  That would be another thing to figure out.

Any suggestions of anything obvious that I've overlooked?

Looks good. You can add a filter to all the columns (so you could, for example, find all coins with reverses containing the word 'Pax'. Select all the data, click Data/Filter). I would change the Cost column to remove letters and spaces (' Euros') so that you can add it up (so you might need a conversion column if in different currencies). Also if the purchase column always has a real date in it, you can sort or filter on it (you might need to add a default if you don't know).

The one thing Excel is terrible at is images. You can add them, but they won't stay aligned or even the right shape, and the file size will grow too big without compressing them. So you need somewhere else to put them, but a reference to that image could go in here (perhaps your collection number).

Edited by John Conduitt
  • Like 4
Link to comment
Share on other sites

I got images in my excel spreadsheet but it was a pain in the ass. You have to right click the cell, Insert comment, then right click the comment box and click Format comment. Then go to Color and lines, select the dropdown box under colors, and click fill effects. Then select the picture tab and upload the picture you want. After that you'd need to resize the comment box to fit the picture. After that, you can hover over the cell to show the picture. there really should be an easier way to do this 

image.png.debde667ca365aa867479f08002ce709.png

  • Like 3
  • Popcorn 1
  • Mind blown 1
Link to comment
Share on other sites

After years of experimenting, I settled on a simple spreadsheet. A database is a lot of work and can prove frustrating if you want to change your data structure. Besides, I will likely never possess more coins than a spreadsheet can handle (that's probably true of many collectors). For those who want a free option, LibreOffice works almost identically to Excel.

I made a simple spreadsheet page with the following columns: Date purchased, Description (usually includes the dealer information), Amount Paid, Vendor, Shipping Costs, Exchange rate (at the time of purchase for purchases in Euros, Pounds, etc,) , Notes and Date Received.

Those who want to categorize their coins and filter by topics, etc., will obviously need more columns than this, but this has served me well over the years to track purchases, who I bought them from, and what I paid for them. If I sell any of the coins on this list (hopefully I will someday), I'll add Date sold, amount sold., etc. I've thought of adding categories for "Ancient," "US," "Medieval," etc., but I haven't really had a reason to yet. Maybe someday. This would be easy to do with a spreadsheet.

  • Like 3
Link to comment
Share on other sites

I keep my information on my coins in an Excel spreadsheet too, but my collection is small (fewer than 70 coins currently) and if I had a collection that numbered in the hundreds or thousands, I'd be inclined to use database software rather than a spreadsheet.

Some specific suggestions:

1. Convert the prices you paid for the coins (including all fees, shipping, insurance, etc.) to one currency -- either dollars or Euros -- valued at the time you purchased the coin.  It's the only way you can total the value of your coins.  I doubt that the original currency matters anyway, and you can always look it up or even include an additional column to record the original currency.

2. Excel IS cumbersome to include pictures, but I find it a very valuable inclusion and even if you move things around, it's worth the extra effort to reposition the pictures.  Here's a section of my spreadsheet to illustrate:

image.png.43e8f4ee05e66374abc9505b760121ce.png

I simply drag a picture of the coin into the open spreadsheet and size it using the handles around the image.

While this will increase the size of your spreadsheet, with most computer storage devices in the multi-gigabyte category, your spreadsheet is unlikely ever to be larger than your storage device can handle.

3. The more specific your columns are, the more precisely you can locate and sort your data, but the more effort it will be to enter each coin.  For example, you could have separate columns for weight and maximum dimension, if you think searching or sorting on that information will be useful in the future.  My advice here is to keep it up-to-date as soon as you get a new coin or sell an old coin, otherwise it's easy to neglect it until it becomes a real chore.

  • Like 4
  • Yes 1
Link to comment
Share on other sites

That's a very nice spreadsheet. My excel (2010) must be too old for that. Dragging an image doesn't work. 

Keeping a record of total cost is a good idea but it would require a monstrous amount of effort to do it, retroactively. I'm entering pre-shipping cost.  I'm never going to sell, anyway.

Keeping track of that from this point on, however, is doable.

 

  • Like 1
Link to comment
Share on other sites

1 hour ago, idesofmarch01 said:

While this will increase the size of your spreadsheet, with most computer storage devices in the multi-gigabyte category, your spreadsheet is unlikely ever to be larger than your storage device can handle.

This is true, but if you do get to hundreds of coins (which isn't unlikely!), your spreadsheet might crash with that many original-size photos in it. Eventually, it will crash, and/or start behaving strangely, and/or scroll very, very slowly. (On that note, it is worth creating a backup copy of the spreadsheet every month or so in case of such an eventuality).

You can add a lot more images by compressing them (click on the image, go to Picture Format / Compress Pictures and select the lowest dpi that doesn't destroy the quality of the image). This is fine for Excel as you just want to see which coin it is. But the image will be no use for posting here, for example, so you need to store the original full-size images as jpegs in a folder.

Edited by John Conduitt
  • Like 3
Link to comment
Share on other sites

Just a tip that Google Sheets is very similar to Excel, is online and entirely web-based, syncs in the cloud, has rudimentary version control (no accidentally deleting everything and then saving your changes), and has a fairly simple option for adding photos over cells or constrained within cells. It also syncs to all your devices.

Excel has an online version too but, last time I used it, it was severely lacking in functionality compared to Google Sheets. https://docs.google.com/spreadsheets

I know some here probably prefer having a desktop app over a web app but they work virtually the same.

  • Like 3
  • Smile 1
Link to comment
Share on other sites

I recently moved from Google Sheets to a free and open-source tool called OpenNumismat - and I am very happy.

test.png.bb0bbb91a459556f9b33a8eee40b4ea2.png

Pros:

  • The tool uses an SQLite database file for storing data. Therefore your collection data is not locked to some obscure, proprietary format. You can very easily export your collection back to CSV to use elsewhere. For developers, this is especially nice for integrating your collection with other tools.
$ sqlite3 coins.db 
sqlite> .schema
CREATE TABLE coins (...)
sqlite> select id, title from coins;
1|Julius Caesar. April-August 49 BC. AR Denarius
2|Titus. 79-81 AD. AR Denarius
  • Can use multiple database files for categorizing your coins. If you have a large collection this is nice to have.
  • The SQLite database file can easily be synced across multiple devices using Google Drive, Dropbox or similar. Which makes it easy to have the collection on your phone, laptop, and work computer.
  •   Able to categorize coins for easy searching. With a big collection this is an amazing feature. For example, I created a categorization to filter by Period and Ruler which allows me to drill into all coins within a given period and from there I can further filter coins by ruler from that period. After everything is filtered I can then sort by column "catalog ID (RIC)". In sheets this was very painful.

categorize.png.fff970e45f69fdc7631d579c20ad91c8.pngexample.png.a7d27df38e1d104cd08b74f31e004a8a.png

  • Able to create your own coin status'. The tool comes with, by default, "Owned", "Ordered", and "Sold" status'. But you can edit these.
  • Desktop tool will automatically save and manage backups (which has saved me a few times).
  • Most fields needed for ancient coins are available.
  • Global search. Similar to Ctrl+F in Sheets or Excel.
  • Dedicated fields for images. Obverse and reverse images have their own dedicated sections which is nice. Then there is space to store up to 6 other images.
  • Charts. You can plot fields on bar/progress/pie graphs.

Screenshot_20230115_192557.png.27d68bf42e302b719d4eca167aca8cc0.png

Cons:

  • No support to edit the collection on mobile devices. They had an Android app that they stopped supporting which they recently replaced with a web app, where you can only view your collection (https://opennumismat.github.io/open-numismat-app/) and not edit. Not a deal breaker for me as I usually have my laptop with me for editing. Would be nice to have though, especially to access "Wishlist" or "For Sale" coins on your phone while at shows.
  • No field for die-axis. I use the "shape" field to record this as I don't use "shape" for anything else. There is a feature request to support a die-axis field (https://github.com/OpenNumismat/open-numismat/issues/68).
  • The Market tab (where you fill in where the coins was purchased) is missing a field for currency. My workaround is converting everything to USD.
Edited by rvk
  • Like 6
  • Thanks 1
  • Heart Eyes 1
Link to comment
Share on other sites

It took me all day but I entered the non-group lot purchases of 2022-23.

I'm trying to create a pie or bar graph quantifying what percentage I bought from whom (column J - Dealer).  How do I do that? (I have version 2010).  I tried sorting by Dealer and then insert pie (or bar, tried both), and insert pie, but I just come out with a meaningless mess; it just lists names without doing anything.1205516735_Coinspreadsheetexample2.jpg.2598932eef29493de6eb401a6ae7b82a.jpg

How do people insert the fancy graphs?

The 'purchase' date was whatever day I saved the .htm version, usually on the day, but sometimes slightly before or slightly after.  Knowing the date, I can easily find the htm, which will pull up the saved version of the original vcoins' (or web) page.

  • Like 6
Link to comment
Share on other sites

1 hour ago, Nerosmyfavorite68 said:

It took me all day but I entered the non-group lot purchases of 2022-23.

I'm trying to create a pie or bar graph quantifying what percentage I bought from whom (column J - Dealer).  How do I do that? (I have version 2010).  I tried sorting by Dealer and then insert pie (or bar, tried both), and insert pie, but I just come out with a meaningless mess; it just lists names without doing anything.1205516735_Coinspreadsheetexample2.jpg.2598932eef29493de6eb401a6ae7b82a.jpg

How do people insert the fancy graphs?

The 'purchase' date was whatever day I saved the .htm version, usually on the day, but sometimes slightly before or slightly after.  Knowing the date, I can easily find the htm, which will pull up the saved version of the original vcoins' (or web) page.

You need to create a table (on a different tab) to count the dealers, which would then calculate the percentage, which could then be used for a pie chart.

Create the list for the table by selecting the dealer column, copying it, and pasting into a new tab. Then use the deduplicate button to deduplicate it.

In the column next to the new list, use the formula:
=countifs(ColumnA,ValueA)
…where you select the column with the list of dealers in your original list in the other tab for ColumnA (the name should include the tab name) and select the dealer name next to the formula for ValueA.

If that worked, there will be a number next to the dealer name. Copy that formula down the list.

Then at the bottom put this formula in a cell:
=SUM(ColumnB)
…where ColumnB is the list of numbers. This value should equal your total coins.

Then in the column next to the number, you add the percentage.
= NumberA / $SumValue %
…where you select the number next to this cell for NumberA, and the SUM cell for SumValue. The $ needs to be typed in next to the cell number e.g. if the SumValue is in cell B100, this needs to be B$100. The % also needs to be keyed into the formula. There should be no spaces in the formula.

Then copy that formula down the list. This should add up to 100% (highlight the list and look at the bottom right of the toolbar for the sum).

Lastly, select the list of dealers, hold the ctrl key, and select the list of percentages. Now insert a pie chart.

The table bit could also be done using a pivot table, but I think that’s a little hard to explain without seeing it. The benefit of that is that it could be dynamic, and update when you add new dealers.

Edited by John Conduitt
  • Like 3
  • Mind blown 2
Link to comment
Share on other sites

Thanks for the tip, but why do they make it so hard?  I have no experience with making formulae, and that sounds all Greek to me. I should look up some youtube tutorials how to do formulae.

It's probably less difficult to sort the dealer column and manually count the dealers, which I did.  The sum of column B is 90.  Would it be easier to make a pie or bar graph now?

And the circled button in purple was the closest I could find to a deduplicate button.

 

692784640_Screenshotofquantifiedcoindealers2022.jpg.f47e918d805fcb5027704cf62eeec3a2.jpg

I managed to make rudimentary pie and bar graphs by highlighting columns A and B and insert bar graph (only certain types would work).  I wasn't able to successfully make a pie or bar graph showing percentages.

108038190_rudimentarybargraphexampleA.jpg.33b4b1f7b84df28927ebf9d0f2e3f2e4.jpg

  • Like 2
Link to comment
Share on other sites

59 minutes ago, Nerosmyfavorite68 said:

Thanks for the tip, but why do they make it so hard?  I have no experience with making formulae, and that sounds all Greek to me. I should look up some youtube tutorials how to do formulae.

It's probably less difficult to sort the dealer column and manually count the dealers, which I did.  The sum of column B is 90.  Would it be easier to make a pie or bar graph now?

And the circled button in purple was the closest I could find to a deduplicate button.

 

692784640_Screenshotofquantifiedcoindealers2022.jpg.f47e918d805fcb5027704cf62eeec3a2.jpg

I managed to make rudimentary pie and bar graphs by highlighting columns A and B and insert bar graph (only certain types would work).  I wasn't able to successfully make a pie or bar graph showing percentages.

108038190_rudimentarybargraphexampleA.jpg.33b4b1f7b84df28927ebf9d0f2e3f2e4.jpg

You are pretty much there. Yes, that is the deduplicate button, I forgot what it was called.

For it to show percentages, you need your table to have percentages. In cell C1, you could just type:
=B1/90
...which would give you the percentage. You can copy that down against all the dealers. Then change the format to percentage (on the Home tab). Then select the data in columns A and C and click pie chart. (You can't select column B as it will try to use it).

  • Like 1
Link to comment
Share on other sites

267905642_Coindealerpiechart2022.jpg.c8751c074708d382440b614dff47ffc5.jpg

I had more luck with the pie chart than the bars.  I could get percentages to show, but I couldn't get the bars to be more than those rudimentary bars shown above.

Conclusion; While my previous guesstimate of London Ancient Coins being the main dealer was true, I severely underestimated the number from Marc Breitsprecher.

  • Like 2
Link to comment
Share on other sites

7 hours ago, Nerosmyfavorite68 said:

Thanks for the tip, but why do they make it so hard? 

For what it's worth, in Google Sheets you can just highlight the column and click insert --> chart and it will automatically calculate the count or percentage in a pie chart. Here the list of letters can represent the list of dealer names for each coin in your collection. There's lots of visualisation options too, this is just the default pie chart.

Screenshot 2023-01-16 at 10.15.21.png

Edited by Kaleun96
  • Like 2
Link to comment
Share on other sites

It was a good idea to have an excel list.  I've gotten 2020 (easy), 2022, and 2009 finished.  There's a few coins which I totally forgot about, like a mediocre Cassius denarius.

There's a relatively easy way to have access to images.  I post a url (or path) of saved purchases, whether they be vcoins' order history or offline path.  The drive letter might change over time as drives change, but it's better than nothing and fixable by one letter, if the drive changes.

There's a lot for 2010-11, less for 2012, a smattering for 2013-14, and except for the forgotten Cassius denarius, there was a lull between 2014-2020.

  • Like 3
Link to comment
Share on other sites

I've still not managed to create a successful bar chart with percentages (I can get it to rank by raw numbers, percentages just come up as every bar the same), but here's a pie chart of online purchases, 090101 to 230122, not including bagged lots from dirtyoldcoins.

1544484411_PercentagePieChartofpurchases090101to230122.jpg.65eca8dde7d17a29b09975f0686b001f.jpg

If my dodgy math holds up, counting all flavors of Roman (includes Byzantine and Gallic Empire), that's 82% of the pie chart.  The '0%' ones are ones with 1 entry only.  My interest in Bactrian and Indo-Greek are more than the pie chart would indicate, but the really neat Bactrian ones are too expensive.

Seleucid interest is also higher than the pie chart indicates.  Most of my Seleucid silver was pre-2009.

  • Like 2
Link to comment
Share on other sites

I highly recommend Google Keep, a free note-taking app that also does a nice job as a coin catalog. It's easy to use, always works, and can be accessed through a smartphone app and/or a web browser. It allows you to tag or "label" each coin entry so you can easily classify and filter your collection using a schema that you create. It has search, allows you to add images to each entry, and doesn't have a problem rendering ancient Greek text. It won't give you data insights on your whole collection like Excel, but it's a solid tool for anyone with basic cataloging needs.

Screenshots:

Main screen (newest entries at top)

tjFVWtJ.png

Viewing coin details after tapping on an item:

U53GWut.png

Sorting by tags/labels - these can be tailored to your collecting style.

92jBXii.png

Viewing results that contain the label "Macedon"

wLFdhg6.png

  • Like 5
Link to comment
Share on other sites

  • 4 months later...
On 1/16/2023 at 1:25 AM, rvk said:

I recently moved from Google Sheets to a free and open-source tool called OpenNumismat - and I am very happy.

test.png.bb0bbb91a459556f9b33a8eee40b4ea2.png

Pros:

  • The tool uses an SQLite database file for storing data. Therefore your collection data is not locked to some obscure, proprietary format. You can very easily export your collection back to CSV to use elsewhere. For developers, this is especially nice for integrating your collection with other tools.
$ sqlite3 coins.db 
sqlite> .schema
CREATE TABLE coins (...)
sqlite> select id, title from coins;
1|Julius Caesar. April-August 49 BC. AR Denarius
2|Titus. 79-81 AD. AR Denarius
  • Can use multiple database files for categorizing your coins. If you have a large collection this is nice to have.
  • The SQLite database file can easily be synced across multiple devices using Google Drive, Dropbox or similar. Which makes it easy to have the collection on your phone, laptop, and work computer.
  •   Able to categorize coins for easy searching. With a big collection this is an amazing feature. For example, I created a categorization to filter by Period and Ruler which allows me to drill into all coins within a given period and from there I can further filter coins by ruler from that period. After everything is filtered I can then sort by column "catalog ID (RIC)". In sheets this was very painful.

categorize.png.fff970e45f69fdc7631d579c20ad91c8.pngexample.png.a7d27df38e1d104cd08b74f31e004a8a.png

  • Able to create your own coin status'. The tool comes with, by default, "Owned", "Ordered", and "Sold" status'. But you can edit these.
  • Desktop tool will automatically save and manage backups (which has saved me a few times).
  • Most fields needed for ancient coins are available.
  • Global search. Similar to Ctrl+F in Sheets or Excel.
  • Dedicated fields for images. Obverse and reverse images have their own dedicated sections which is nice. Then there is space to store up to 6 other images.
  • Charts. You can plot fields on bar/progress/pie graphs.

Screenshot_20230115_192557.png.27d68bf42e302b719d4eca167aca8cc0.png

Cons:

  • No support to edit the collection on mobile devices. They had an Android app that they stopped supporting which they recently replaced with a web app, where you can only view your collection (https://opennumismat.github.io/open-numismat-app/) and not edit. Not a deal breaker for me as I usually have my laptop with me for editing. Would be nice to have though, especially to access "Wishlist" or "For Sale" coins on your phone while at shows.
  • No field for die-axis. I use the "shape" field to record this as I don't use "shape" for anything else. There is a feature request to support a die-axis field (https://github.com/OpenNumismat/open-numismat/issues/68).
  • The Market tab (where you fill in where the coins was purchased) is missing a field for currency. My workaround is converting everything to USD.

I'm going to give this a try this weekend, thank you.

Link to comment
Share on other sites

1 hour ago, Captch said:

@seth77 let me know how it goes. I had some trouble setting up the category fields as I would like, but I have a satisfactory catalog for a few coins now. If you run across any cool tricks, you should share them.

I'm particularly interested in access to the database from my phone. I now use my own html tree that links to pages that are stored on a usb stick. I'm also curious if I'll be able to import the data from my html pages, that are pretty standardized. I'll see this weekend when I get more time.

Link to comment
Share on other sites

1 hour ago, seth77 said:

I now use my own html tree that links to pages that are stored on a usb stick.

Ooh, that sounds nice.

I like the built-in html templates that come with open-numismat, but it's not clear how you edit them. Maybe you need to fork the project, make your edits and then rebuild it? Here's what the start of inputting my collection looks like.

Screenshot from 2023-06-08 10-56-36.png

Link to comment
Share on other sites

10 hours ago, seth77 said:

I'm particularly interested in access to the database from my phone. I now use my own html tree that links to pages that are stored on a usb stick. I'm also curious if I'll be able to import the data from my html pages, that are pretty standardized. I'll see this weekend when I get more time.

It's unfortunate but the OpenNumismat team ended development on the Android app a few years ago. The only current existing solution to view the database from your phone is to use their offline web page (https://opennumismat.github.io/open-numismat-app/). Which is very limited and I've found problems with it (not all coins show up?). A crude alternative is to use an SQLite viewer app on your phone.

If there is a Java developer out there.. the Android project is still around but I can't find a way to build it without running into error after error (https://github.com/OpenNumismat/open-numismat-android).

9 hours ago, Captch said:

Ooh, that sounds nice.

I like the built-in html templates that come with open-numismat, but it's not clear how you edit them. Maybe you need to fork the project, make your edits and then rebuild it? Here's what the start of inputting my collection looks like.

You're talking about the reports, right? I remember going down this path. I eventually found that I can create my own reports in the folder "/usr/lib/python3/dist-packages/OpenNumismat/templates/" without rebuilding the project. Its annoying because you need admin privileges but once everything is set up it works well.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...