Introduction
My primary coin collection is cataloged in Excel. I like to record all kinds of information about my coins, mostly found in Numista, but until now I had to copy it by hand from Numista into the Excel sheet. This takes quite a bit of time, so I wrote an excel Macro which is able to transfer most of the data from Numista in less than 10 seconds per coin. If you struggled with the same problem, you can download and use my macro.
This post was updated in December, 2019 with version 2.0. More information is harvested, and each record takes about a second.
Why is it useful
First, let me say that storing coins in Excel is not any better than storing it in your Numista account. It is a different approach, many pros and cons for both. I started recording info in Excel, and there’s no way to transfer it into Numista. Besides, for a few reasons, Excel works better for me, so I didn’t want to record everything in two places, it is already time consuming to do it once. I can mark a coin in Numista easily, but there is still quite a bit of info I need to record which cannot be automated, such as the actual location of the coin in the collection.
What it isn’t
If you are looking for a way to extract the Numista catalog, this is not the tool to do it. For that matter, Numista is good as is, and I have no intention to copy their entire catalog over. I simply wanted to speed up the manual copy pasting process. For each record, you will need to get an URL to a coin, so if you wanted to copy over the entire catalog, you would have to repeat the process some two hundred thousand times, which would take months.
How to use it
There is an easy, out of the box way to use the macro, or if you already have an existing Excel sheet, with some easy modifications you can make it work for your data structure. I’ll walk you through the whole thing step by step, even if you don’t know what’s an Excel macro I can explain how to make it work for you.
But first, download the file.
Using it out of the box
When you open the file, you will find a mostly empty coin collection template. There is a column labeled “URL”. This is where you will paste the URL to a particular coin to Numista. I did leave a few there to play with. Position your cursor to a line which has a URL filled in, and press Shift+Control+N. Wait a second or so, and you will find that most cells will be filled out for you. Not all, because some of the cells are for you to fill, for example if you want to record the location of the coin, or add any personal notes. Other fields, such as mint or mintage are not filled out, even if they exist in Numista, because there isn’t an obvious way to grab it, but more about this later.
All the URLs in the sample sheet will work just fine, they are mostly a random sample I tested the macro with.
If you want to get information about any of your coins into the Excel sheet, go to Numista.com, look up your coin, open its page, and paste the URL into the excel sheet into its own row, on the URL column. Now stay on that row, and press Shift+Control+N and watch your Excel sheet update.
The time it takes to pull in the information from Numista depends on many factors, the speed of your computer, whether you are running other applications in the meantime, but most importantly the speed of your internet connection. You will hear a ding when the work is complete.
Information you get
The macro will get the following info from Numista if it exists:
- Country
- Value
- KM number, or any other reference number
- Years minted
- Composition
- Weight
- Diameter
- Thickness
- Edge
- Shape
- Obverse
- Obverse legend (new in V 2.0)
- Obverse designer (new in V 2.0)
- Reverse
- Reverse legend (new in V 2.0)
- Reverse designer (new in V 2.0)
- Currency (new in V 2.0)
- Subject (new in V 2.0)
These are 18 values you won’t have to copy and paste one by one.
Whether you have a database or not, it works the same way. You can make it work with your already existing Excel sheet, or you can recreate and modify the template in any way you want to.
The important thing is that you will have to have the downloaded macro file open when you run the macro, but it doesn’t matter if you paste the information into the downloaded template, or a completely different Excel sheet. As long as your cursor is positioned on the row which contains the URL and press Shift+Control+N, it will get you the info.
If you prefer, you can even move the macro over into your own excel sheet so you don’t have to open the downloaded macro, but that’s a more advanced feature which I will not cover here.
Change the hotkey
If for any reason you don’t like the assigned hotkey, you can easily change it. After opening the harvester file,
- Press Alt-F8
- Select Harvester from the list
- Press options
- Move to the Shortcut key field
- Press the key you would like to change the shortcut key to
- Press OK
Please note that all keys should be pressed without the control key, but when activating the shortcut, the control key needs to be pressed.
For example, if you want the new shortcut to be Control+Shift+C, press Shift+C, which will place a capital C into the field. Now when you press Control+Shift+C the macro will start.
It is recommended to pick a key with a shift combination, because Control+lower-case letters have other functionalities and your chosen shortcut may not work, for example Control + C will copy your current selection to your clipboard, thus you cannot assign this shortcut to that key combination.
Advanced setup
You can make the data appear in any order you like.
If the order of your columns in your Excel sheet are different, you will have to do a few things before you can use the macro.
You will have to open the macro and make a few changes.
Press Alt+F11 to open the VB editor
Press Alt+V then P to get to the project list.
Scroll to the end of the list and select module2. Please note, unlike when you change the key combination, here it is called module2 and not harvester.
You will see a few pages of code, don’t worry, you won’t have to understand any of it, I marked it up for you so that you can easily customize it.
We are looking for lines which begin with “‘ customize”. You can always press Control+F and type “‘ customize”, without the quotes, but starting with the apostrophe to quickly find these lines, there aren’t too many of these.
After these lines you will find information which you may want to change.
Just below the first occurrence of this line, you will find the following:
urllocation = 8
This defines in which column you keep the URL. In our case, it is the H column, that is the 8th, thus the value of this variable will be 8.
If you want to store your URL values in column F, change the line as follows:
urllocation = 6
Now the URL will be taken from column F, if it doesn’t exist, you will get an error message, as the macro will not be able to run.
The following lines are very similar, you will be able to define all the other columns.
Starting with country, define in which column you want the macro to place the country value, the default is the first column, but if you want it to be in column B, change it to:
country = 2
Below you will be able to change all the other column locations, the variable names will be self-explanatory.
However, please note that the coin value contains both the name and denomination of the coin, as Numista defines it this way. I usually just split this into two fields later, or as you will see below, you can also disable it.
Right after this you will have a chance to define which values should be filled into your data sheet, and which should be omitted. Why would you want to do this?
When you get a URL to a coin, there is a good chance that you already know the country, the coin name and denomination, for that matter, you will almost have to know the KM number to get the right URL. So, if these values are filled in already , you may not want to update them, because Numista uses a different format and it may ruin your organization.
Personally, I disable country, coin and KM number, but fill out the rest.
Here you will find a line for each data segment, if you don’t want it, put an apostrophe and space before it, otherwise leave it as is.
For example, if you don’t want to fill in the KM value, your line would be as follows:
‘ km
‘ ActiveSheet.Cells(ActiveCell.Row, km) = kmvalue
But if you do want to have this value filled, change it to the following:
‘ km
ActiveSheet.Cells(ActiveCell.Row, km) = kmvalue
The first of the two lines always has an apostrophe, it just tells you which value you are changing to hopefully make it a bit easier to understand the code structure.
This is all you have to change, everything else will work as explained before. If you ever get an error message you didn’t see before, I’d suggest just redownload the sheet and try again, once you get the hang of it, the customization shouldn’t take too long, and it will make your life much easier for the years to come.
Once you are happy with your changes, I would recommend that you save a copy of it, and move your coin sheet into a separate excel workbook. Now when you want to fill a record, open both your new coin sheet, and the macro file you have just saved. As long as both files are open, the macro shortcut key will work.
Get the code
I know that the Excel sheet I posted is harmless and free of any harmful viruses, but I would understand if you don’t believe me. Therefore, I am also posting the actual code of the macro, which you can copy and paste into your excel sheet. Get it here.
You will have to do the following:
- After you opened the page with the text, copy the entire page content on your clipboard. Behind the scenes, this is just a text file, which you can download and inspect as well.
- Open the excel sheet where you would like to run the macro.
- Press Alt + F11 to open the Visual Basic editor
- Select module on the Insert menu
- Module1 will be created, just paste the entire clipboard content into this blank page
- Save this page, and your Excel sheet
- Now you will have everything in one workbook
- Follow the above instructions to add a shortcut key
- At this point, you will have to change the necessary variables in the macro as described to match your data structure. If you chose to download the actual code there isn’t an easy way around it.
potential issues
There are a few issues which I wasn’t able to work out due to the structure of the coin pages.
If certain fields do not copy over from Numista, most often it means that this information is not available in Numista. For example, there are a few coins where the weight and diameter exists, but the thickness doesn’t, which will remain empty. This is not an error, it just means the data wasn’t found. It is recommended to run the macro on these lines in a few months just in case more information was added.
In some rare cases you may get an error message, where you can choose the debug the problem. If you don’t, just press ok. I tried to make the macro as accurate as possible, but there are a few unexpected issues which can be found on coin pages which break the operation. I would say out of a hundred coins I get errors on one or two. If such error would occur, unfortunately you will have to copy the information over by hand. However, if you send me the link to the Numista page, I will try to fix the issue. Version 2 is much more reliable than version 1 was.
Get in touch
If you have any questions or suggestions, please get in touch with me. I’ll be happy to help you set up the macro, or make the suggested changes if I can. Please follow the contact link on this page to send me a message.
The latest information about this project can always be found at: http://BlindCoinCollector.com/harvester. Here I will provide you a link to the latest download, this blog post which I will update if anything changes, and a link to the contact page.
Future development
If I become aware of any issues, or come up with better solutions, I will update this macro. You can always download the latest version from:
http://BlindCoinCollector.com/harvester
Good luck, and I hope this little macro helps you spend less time with cataloging, and more time enjoying your coin collection.
