Multi-Bullion Inventory Tracker (Excel)

[sfstacker]

Hello everybody. Just got back into stacking now that I'm grounded. For the past few years I lived in the wild of the US and most of my experiences with gold/silver were for barter to aid in my survival. I used to prospect for gold and if you're smart enough you can find "treasure maps" produced by the US Government. But that's all I have to say about that

Now that I'm stacking I wanted to keep track of what I'm acquiring. So I decided to make an excel spreadsheet. I am absolutely not an expert on excel. It was one of those programs that came default on my first computer as a child and I understood how to do very little. But after a few days and many YouTube videos later I have produced this:

This a blank sheet. The categories are at the top. Move you mouse over any of them and a message will pop up to explain. For example "Value" will tell you to enter the denomination of your piece of bullion. You can enter whole numbers, fractions, or decimals. For "Unit" you can choose ounces, grams, ect. Your bullions total value and cost will automatically be calculated as you enter data.

As you enter data into the blank sheet it will automatically format itself. This will continue to format on every page. You list will also have filter functions on it. Meaning you can set it up so it will only show items acquired in January, or only gold bullion in bar shapes in gram sizes, etc. I'll add a search function to it as well once I figure out an aesthetically pleasing to put the box.

This is the price and totals tables. The live spot price of Silver, Gold, Platinum, and Palladium are broken down in ounces, grams, and kilos. The data is taken from APMEX.com. I've got them set to auto refresh every 10 minutes but you can refresh them manually by right clicking on them and choosing "Refresh". The prices will also automatically refresh when you open the excel file provided you have an internet connection and have data connections enabled in your excel settings. If someone can find an internet page that has the real time data for Copper then I can add that to the sheet. Also diamonds as well (you can buy them on apmex).

Just below these four spot price boxes is your bullion value box. Right now Silver is selected. You can click on menu box and select the different bullion. It will then search your inventory for all those bullion and give you their value based on the current. Right now its just setup to calculate one metal at a time. I'm like 99% positive that I can set it up to calculate the grand total of all your metals. It will just take some time to setup the formula.

I will be adding more calculation boxes in the future. I've added one that isn't included here because I took the screen shot before adding it. It will tally up the number of your bullion by its design. I.e. how many bars, rounds, poured items you have.

I will be adding a box that calculates the bullion current market value against its original purchase value.

I've purchased some collectible items that are way above their market value. Collectible items will be left out of the calculations.

Comments, suggestions, ideas? More importantly, any questions?

 

[sfstacker]

Dang, over 100 views and no replies!

 

[milkyspot]

I dont care when i have bought it but i do care at which price i bought it
On my file the number gets green or red depending if market value rises or falls below my purchase price.

I also have a global function where all silvercoins are combined as average and then compared to spot.

 

[milkyspot]

Addon: I have also a column where the peformance in % is shown for each coin.

 

[sfstacker]

I dont care when i have bought it but i do care at which price i bought it
On my file the number gets green or red depending if market value rises or falls below my purchase price.

I also have a global function where all silvercoins are combined as average and then compared to spot.

I thought about doing that with the total bullion value calculation. That's why I set it up so collectible items aren't included in the formula. Because that 1oz collectable coin you purchased for $69 will probably never make it there as the spot price and you'll forever have a red number.

What interests you about knowing your individual coin purchase averages compared to spot? It other words, what does this information do for you?

Addon: I have also a column where the peformance in % is shown for each coin.

The percentage (-,+) of its purchase price and its current value?

 

[sfstacker]

Addon: I have also a column where the peformance in % is shown for each coin.

I've started a new column that will contain the cost vs current value in a %. The formula will look for what metal and unit of measurement as you modify them in real time. As you can see it's a pretty hefty formula. If there's a shorter version that will do the same thing please let me know.

 

[Jislizard]

What interests you about knowing your individual coin purchase averages compared to spot? It other words, what does this information do for you?

If I go to sell a coin I want to make sure that I get more for it than I paid. So some coins will be ready for sale and some will have to wait until spot goes up higher, depending on what I initially paid for them. Knowing how much I paid for a coin lets me know which ones I am happy to sell. (spoiler alert: I never sell the things anyway)

I have some pre decimal coins that I bought below spot and will be ready for sale when I can get double my money for them (which nearly happened once!).

I would also have a column for junk silver. Maybe even a separate page. On that you have columns for the weight and fineness of the coins in order to work out the value.

In my spreadsheet I had different tabs for numismatic, semi numi, gold, silver, fractional silver etc. and a single page that had all the totals linked like a dashboard for easy reference. Made looking for particular items easier.

I also calculate the cost of postage into the cost of the coin.

My old spreadsheet had red cells for items which had lost value, Green for cells that were worth more than I bought them. I had $ and % changes calculated etc.

But I change computers or I buy a collection in and I forget where the spreadsheet is so I just start a new one. I have several different spreadsheets but they are all roughly the same. The ones for collections I am buying are mostly put together so I can work out how much to offer or how much value I just bought so they don't have all the other information added or any of the tracking or performance statistics.

 

[barsenault]

just wait until the collapse of the world currencies, and you'll be just fine...no matter the ounces, no matter the price paid. Not sure how long you'll have to wait, just say'n...

 

[sfstacker]

But I change computers or I buy a collection in and I forget where the spreadsheet is so I just start a new one. I have several different spreadsheets but they are all roughly the same. The ones for collections I am buying are mostly put together so I can work out how much to offer or how much value I just bought so they don't have all the other information added or any of the tracking or performance statistics.

I see that you're also keeping track of the mintage and years. What do you do with this data? Is there some trend you're tracking with this data?

 

[Jislizard]

But I change computers or I buy a collection in and I forget where the spreadsheet is so I just start a new one. I have several different spreadsheets but they are all roughly the same. The ones for collections I am buying are mostly put together so I can work out how much to offer or how much value I just bought so they don't have all the other information added or any of the tracking or performance statistics.

I see that you're also keeping track of the mintage and years. What do you do with this data? Is there some trend you're tracking with this data?

I have a selection of silver coins from around the world, most are just worth spot, but I am a member of a coin club and there might be someone looking for a particular year coin who is willing to pay over spot. This just saves me from having to carry around physical albums.

The mintages have two purposes.

Firstly, I clean all the grease and dirt off my coins, which is a big no-no in the coin club, so you show them the mintages of 39,000,000 and tell them to get one of the other 38,999,999 if they don't like my cleaned one. If it has a low mintage like 400,000 I don't clean them.

Secondly I know very little about foreign coins, to me an Egyptian 10 Quirish from 1937 looks like an Egyptian 10 Quirish from 1939 but if I was going to keep one of them I would chose the one with the lowest mintage (All things else being equal) as it would be slightly rarer.

It makes a difference with coins from a long series where one coin may have been made in significantly smaller numbers, I don't want to keep them all but I would like an indication of which one is likely to be worth more (without having to buy the book). When I see high mintages I have no problem chucking them in the bulk world coin box wihtout fear that I am giving up a really rare coin.

 

[sfstacker]

But I change computers or I buy a collection in and I forget where the spreadsheet is so I just start a new one. I have several different spreadsheets but they are all roughly the same. The ones for collections I am buying are mostly put together so I can work out how much to offer or how much value I just bought so they don't have all the other information added or any of the tracking or performance statistics.

I see that you're also keeping track of the mintage and years. What do you do with this data? Is there some trend you're tracking with this data?

I have a selection of silver coins from around the world, most are just worth spot, but I am a member of a coin club and there might be someone looking for a particular year coin who is willing to pay over spot. This just saves me from having to carry around physical albums.

The mintages have two purposes.

Firstly, I clean all the grease and dirt off my coins, which is a big no-no in the coin club, so you show them the mintages of 39,000,000 and tell them to get one of the other 38,999,999 if they don't like my cleaned one. If it has a low mintage like 400,000 I don't clean them.

Secondly I know very little about foreign coins, to me an Egyptian 10 Quirish from 1937 looks like an Egyptian 10 Quirish from 1939 but if I was going to keep one of them I would chose the one with the lowest mintage (All things else being equal) as it would be slightly rarer.

It makes a difference with coins from a long series where one coin may have been made in significantly smaller numbers, I don't want to keep them all but I would like an indication of which one is likely to be worth more (without having to buy the book). When I see high mintages I have no problem chucking them in the bulk world coin box wihtout fear that I am giving up a really rare coin.

Ok, I understand now.

 

[milkyspot]

What interests you about knowing your individual coin purchase averages compared to spot? It other words, what does this information do for you?

If I go to sell a coin I want to make sure that I get more for it than I paid. So some coins will be ready for sale and some will have to wait until spot goes up higher, depending on what I initially paid for them. Knowing how much I paid for a coin lets me know which ones I am happy to sell. (spoiler alert: I never sell the things anyway)

100% correct. Nailed it on the spot.


@sfstacker. Ok i will be more precise of which colums and features my spreadsheet carries. Imagine i had n=5 Krugerrand in my list

First column: Amount
Second column: Cointype, in our case Krugerrand
Third column: My price ((300$+400$+1200$+666$+999$)/5), so i know my average price for all 5 krugers bought on different dates
Fourth column: Spot
Fivth colum: Column 3-4
Sixt columnt: Spot/100=Column3/x-> calculate x, my formular is not that difficult as yours.

Thats the basic body my file has
If you have any question dont hesitate to ask

 

[sfstacker]

@sfstacker. Ok i will be more precise of which colums and features my spreadsheet carries. Imagine i had n=5 Krugerrand in my list

First column: Amount
Second column: Cointype, in our case Krugerrand
Third column: My price ((300$+400$+1200$+666$+999$)/5), so i know my average price for all 5 krugers bought on different dates
Fourth column: Spot
Fivth colum: Column 3-4
Sixt columnt: Spot/100=Column3/x-> calculate x, my formular is not that difficult as yours.

Thats the basic body my file has
If you have any question dont hesitate to ask

I won't fully understand what this means until I see it. Would you mind posting a screen shot of what this looks like? Feel free to change up the data with arbitrary information before you take a screenshot. I'm just looking to see how your template is built and don't want it to seem like I'm trying to peek at your collection.

 

[milkyspot]

I renamed them in english and made a example for you for better understanding, sorry if its not the very best terminology maybe

 

[Old Codger]

I gave up in disgust trying to maintain accurate date on prices paid, and supplier, about 5+ years ago.

Hopeless!

OC

 

[sfstacker]

http://i.imgur.com/GufCINf.png

I renamed them in english and made a example for you for better understanding, sorry if its not the very best terminology maybe

I understood your words fine enough. My main issue was I just wanted to see the visual representation of that. A lot of people seem to be keeping track of or calculating the same things I've already thought of. However, everyone seems to lay their sheets out differently and seeing how they do that helps me fine tune my own sheet. For example, someone might have a calculation that takes up maybe 3-4 rows of data. I can look at that and then just create a formula that will do the same thing but without taking up so much space on the sheet.

Thanks for posting!