Software programs for coins.

jkrk

Currently have all factual data on excel spreadsheet.

Looking for a program that maybe can work with a bar code scanner to make the deleting / additions easier?

JUst looking for a yearly reportable P&L and monitoring the location if at CGC, grading service, in transit, etc.

Not looking for a valuation tool.

Trying to make my wife's job easier.

suggestions?

Jinx86

Some of us dealers use Point of Sale systems, kind of what your speaking of. I dont know of any that are for free. Wouldnt be hard to do for a small operation, but with a high volume shop with many raw coins our first attempt failed. If your at Central States, ask around, some of the dealers might have something new or know of a program.

BStrauss3

Most bar code scanners are pretending to be keyboards. So when you do a scan, the characters from the barcode are "typed" into the computer. You can test this in an empty spreadsheet...

If you aren't afraid of a little bit of code in your spreadsheet, it's not hard to implement (I've been meaning to do a lookup in my own sheet for a while).

WARNINGS:

It works for me. If it works for you great. If not, tough. No warranty, no guarantee, no consequential damages
(If you run a business from this spreadsheet, you better have LOTS of backups)

You need to shift to an xlsm from an xlsx file (i.e. with macros).
It may slow down your sheet a bit.

You will need to create a self-signing certificate and trust it so that you don't constantly get prompted when you open the sheet. Instructions here: https://support.office.com/en-ie/article/digitally-sign-your-macro-project-956e9cc8-bbf6-4365-8bfa-98505ecd1c01 and look for this piece:

------------------ that out of the way ------------------

Open the spreadsheet and Save As (a copy) as an .xlxm sheet:

Open the spreadsheet and press ALT-F11 to jump to the code page

On the tools menu, select Digital Signature:

If you have a certificate created above, you'll be prompted to sign code with it:

(If you have multiple certificates, etc. play with the choose button)

Go out to the File | Options dialog and pick Trust Center (bottom left) - click on "Trust Center Settings"

Choose "Macro Settings" and then the 3rd value "Disable all macros except digitally signed macros"

Click OK twice, then save and close the spreadsheet

When you next open it, you may get a prompt... (I'm doing this from memory, so...) ... yes, you really do want to run signed macros.

Here's some sample data:

Press ALT-F11 again and click under "Microsoft Excel Objects" for the sheet that you want to search in (whatever you've called the tab)...

Paste this code in:

Option Explicit

' Copyright (c) 2019 - Burton M. Strauss III
'
'    This program is free software: you can redistribute it and/or modify
'    it under the terms of the GNU General Public License as published by
'    the Free Software Foundation, either version 3 of the License, or
'    (at your option) any later version.
'
'    This program is distributed in the hope that it will be useful,
'    but WITHOUT ANY WARRANTY; without even the implied warranty of
'    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
'    GNU General Public License for more details.
'
'    You should have received a copy of the GNU General Public License
'    along with this program.  If not, see <https://www.gnu.org/licenses/>.

' Very roughly, this means you can use this code for personal OR commercial purposes.
' I don't make ANY guarantees that it will work for you.

'       You can modify it, but you must make the modified code available under the same conditions
'       Redistribution must include the notice

'       Note that this doesn't prevent you selling my work as your own,
'       but you can't remove the notice to hide what you did!


' Change this value to make this monitor another cell in the spreadsheet
'       We use A1 notation here because it's easier to figure out from the sheet.
Const cstrMonitorCellName As String = "$W$2"

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim iMatch As Integer
    Dim iMaxRow As Integer
    Dim iCol As Integer
    Dim iRow As Integer

    On Error Resume Next

    ' Don't trigger on big update
    If (Target.Areas.Count <> 1) Then _
        Exit Sub

    ' Or more than 1 row/column
    If (Target.Rows.Count > 1) Or (Target.Columns.Count > 1) Then _
        Exit Sub

    ' If not the right cell?
    If (Target.Address <> cstrMonitorCellName) Then _
        Exit Sub

    ' NOTE from here on out, we're assuming it's ok to put error messages in the next cell to the right...
    Target.Offset(0, 1).Value = ""

    ' Target cell is Empty
    If (Target.Value = "") Then _
        Exit Sub

    ' or if it's a formula
    If (Target.HasFormula) Then
        Target.Offset(0, 1).Value = "ERROR: Has formula"
        Exit Sub
    End If

    ' The match range is the cell below to the end of the data... we use R1C1 notation because it's easier to code...
    iCol = Target.Column
    iRow = Target.Row
    iMaxRow = Cells.SpecialCells(xlCellTypeLastCell).Row

    ' If there's been an error... note that this one might lock the lookup until next time you open the sheet...
    If (Err.Number <> 0) Then
        Target.Offset(0, 1).Value = "ERROR: " & Err.Number & "(" & Err.Description & ")"
        Exit Sub
    End If

    iMatch = WorksheetFunction.Match(Target.Value, Range(Cells(iRow + 1, iCol), Cells(iMaxRow, iCol)), 0) + iRow

    If (Err.Number = 1004) Then
        Target.Offset(0, 1).Value = "Not found"
        Exit Sub
    End If

    If (Err.Number <> 0) Then
        Target.Offset(0, 1).Value = "ERROR Matching: " & Err.Number & "(" & Err.Description & ")"
        Exit Sub
    End If

    ' And jump to the found location
    Cells(iMatch, iCol).Select

End Sub

Five lines down, you need to change the Cell to whatever you're going to search from (in my sheet it's the yellow B1):

Const cstrMonitorCellName As String = "$B$1"

Close the code page (top right X) and save the whole sheet (alt-s if you don't remember the shortcut key)

Type a value in B2 and watch the magic. Click on B2 and scan a bar code.

If anybody wants a copy of the spreadsheet, message me.

Enjoy!

davids5104

I use coinmanage software by liberty street. You can enter Slab info from PCGS and it auto fills the information. It does not do it for NGC/ANACS coins. You can of course manually enter those. It has a report generator that is pretty robust