Spreadsheet for Tracking Bullion

Discussion in 'Bullion Investing' started by SilverMike, Feb 5, 2017.

  1. SilverMike

    SilverMike Well-Known Member

    Hi everyone,
    I'm trying to come up with a spreadsheet to track my bullion that I've stacked. I'd like to list my cost basis and be able to average it across all purchases and also input the current spot price to update the current value. Does anyone have a spreadsheet template you use and like that you'd be willing to share?
    Thanks!
     
  2. Avatar

    Guest User Guest



    to hide this ad.
  3. doug444

    doug444 STAMPS and POSTCARDS too!

    Let's see if we can at least come up with column headings.

    First, going down the page, you'd have sections for dimes, quarters, halves, dollars, rounds, bars, foreign, intact proof sets, and misc., such as 40% Kennedy and war nickels.

    You would need to add rows as you acquire mixed lots of various denominations; that's easy to do.

    For columns, reading across "A" to perhaps "M," etc., you could start a draft with:
    A - date
    B - spot price that day
    C - quantity
    D - BxC (spot for what you bought)
    E - price you actually paid
    F - payment type (cash, check, credit card, trade)
    G - ?

    That's as far as I could draft without knowing more about what you want to accomplish, or what you want to know. Is this a start, or are we on the wrong track? There's no column for face value because you have foreign and sets mixed in, but with what we have already, you can total your entire purchases and know the spot (say, 79.82 Troy ounces) and the total you paid from Day One.

    There is an implicit assumption in this format that you will buy "all" kinds of silver if the price is right,

    How experienced are you with Excel?

    Why don't you enter the row and column headings as per above and save that spreadsheet as a draft, i.e., not enter any transactions yet? You must have headings first, or you cause yourself a ton of work when you change your mind.

    Other CT-er's can come up with more columns (or rows). Don't let the fear of your spreadsheet getting too complicated slow you down. You can delete in a flash, but adding a new and essential category is a nightmare.
     
    SilverMike likes this.
  4. RhinoEmpire

    RhinoEmpire Hi-Yo (Ag)

    I've got one that I posted in the past. I'll send it out again later this week.
     
    SilverMike likes this.
  5. Bman33

    Bman33 Well-Known Member

    I've got one that works great. Calculates everything.
     
    SilverMike likes this.
  6. SilverMike

    SilverMike Well-Known Member

    Thanks for the ideas!
     
  7. GoldFinger1969

    GoldFinger1969 Well-Known Member

    I have one, too, very simple in Excel. Seems like I was beaten to it so hope it works out.
     
  8. Brett_in_Sacto

    Brett_in_Sacto Well-Known Member

    Yeah, I've done a few things.

    1st is to value each of the coins / bars / rounds by % of silver in them (90%, .999, etc) and then you can keep quantities. Add a cell for spot price and then make the calculations across each line for "per coin value" and another column to carry the accumulated totals.

    example:

    ASE / .999 / (spot * percentage calculation) / (quantity) / (spot value * quantity)
    Morgan / .90 / (spot * percentage calculation) / (quantity) / (spot value * quantity)

    I do the same for gold jewelry. grams / ozt * percentage purity (karat) = value

    Also a good way to track your entire collection. Don't know how many trips to SDB I've saved. Then again it's nice to go visiting. :)
     
    SilverMike likes this.
  9. doug444

    doug444 STAMPS and POSTCARDS too!

    "I'd like to list my cost basis and be able to average it across all purchases..."

    I thought of one small flaw for this feature. When you sell an item, presumably you will delete it from Excel. But if you do, that will change the "average cost" for all the remaining items, but you didn't really change that average cost.

    There's ways around this issue, I'm thinking it over.
     
  10. Schexnailder

    Schexnailder New Member

    Did you ever get a spreadsheet for tracking? That's what I'm looking for also.
     
  11. SilverMike

    SilverMike Well-Known Member

    I did. I'll try to upload a blank version in the next day or two. It works pretty well
     
  12. FBLfinder

    FBLfinder Active Member

    i find that running two sheets, one for junk silver with purity and weight calculations and the other for my bullion to work best for me. I recently started a third sheet similar to my bullion sheet but for my collectable silver bullion so i dont throw price average off with my investment silver.
     
    SilverMike likes this.
  13. SilverMike

    SilverMike Well-Known Member

    Well...for whatever reason I can't upload the spreadsheet (I get some error message about an incorrect file extension? It is an Excel spreadsheet, so I don't know how to fix that). I'm happy to email it to anyone who wants it - just PM me your email address. Or, if someone knows a better way to post it here let me know.
     
  14. SilverMike

    SilverMike Well-Known Member

    That's a good idea.
     
  15. Schexnailder

    Schexnailder New Member

    Thanks Mike!
     
  16. mpcusa

    mpcusa "Official C.T. TROLL SWEEPER"

    Unless your running a large corporation, there is no need to dive that deep in to the
    Details, keep it simple and you will just be fine :)
     
  17. RhinoEmpire

    RhinoEmpire Hi-Yo (Ag)

    A few people have PMed me about an updated silver/gold tacker. Here you go!
     

    Attached Files:

Draft saved Draft deleted

Share This Page