Silver Spreadsheet Available Here!

H

haveblue

New Member
Hi All,
I have made an excel spreadsheet that automatically calculates:

1. The value of the $AUD to the $USD

2. The margin you paid over spot as a %,

3. Average cost per oz both including and excluding postage

4. Your gain or loss as a %, both including and excluding postage costs

It also tally's how many OZ's you have automatically, and works out what the current value of your stack is once you enter the current spot price.

I have protected the formulas in the spreadsheet (cause I ripped most of my hair out trying to figure them out!! :D) so that you can't edit them by mistake once your do enter your data.

This spreadsheet is probably best suited to someone like myself who is stacking but not selling. What I mean is I haven't included provision for sales in the spreadsheet (not yet anyway).



1535_your_silver_log_1.png



1535_your_silver_log_3.png


Anyhow if you would like the excel (2007) file send me an email or PM me, I will be able to email you a copy in a couple of days once I have finished with work over the weekend.

Cheers,
Haveblue.

EDIT: I've had to take the password off the file in order to successfully email it as an attachment. Damn microsoft hotmail was sending it off into cyberspace never to be seen again!!! Nooooo...
 
I have a sheet in my spreadsheet to fetch the current spot price from kitco on demand - have a look here if you want to add it to your sheet. Very nicely laid out, yours is. :)

http://cl.ly/6Bev
 
Thanks zygote,
Now thats a good idea! I will have a good look at it in the next couple of days..I spent the better part of today procrastinating with this spreadsheet...so many things I should be doing, but don't want to :D
 
Nice sheet, picked up some ideas off yours to add to mines :)

I also have some other columns though such as Mintage, Company (PM, PAMP etc), Year, Purity (0.999, 5 9s)

Might look into what zygote did, seems like a great idea!
 
looks great, been wanting to do this myself alas XL and i are not on good terms.
let us know when your done, might even flip you for it ;)
 
In case anyone wanted to do what zygote did with live prices, if you have EXCEL 2007 you can do it via the following method:

Go to the top and click Data, on the right top bit you can see Get External Data; click on From Web and type www.kitco.com as data source.

Then you can just import whatever tables you want using that yellow arrow thing.

Great stuff never knew excel had that capability ;)
 
theres a cool feature you might not know of, auto update from a website

In Excel 2007

-Click on the cell you want the data to update e.g. Price gold
-Click Data>From Web
-Paste the website into the search bar
-Boxes will apear around the data you want
-Click on the box to accept
-Then click add data
-Every minute the data will refresh

I use Perth mint spot prices in $A

http://www.perthmint.com.au/metalPrices.aspx
 
fishball said:
In case anyone wanted to do what zygote did with live prices, if you have EXCEL 2007 you can do it via the following method:

Go to the top and click Data, on the right top bit you can see Get External Data; click on From Web and type www.kitco.com as data source.

Then you can just import whatever tables you want using that yellow arrow thing.

Great stuff never knew excel had that capability ;)
Click to expand...

Do you have a webiste that lists the spot price so I can have it auto update in my database.
 
I also like to have listed date ordered (& or paid for) & the delivery date, just another column to consider or not.
 
mine has % over spot, spot at time of purchase, $ +/- spot, etc.

Also have a calculator for making purchases - enter weight, price, postage to calculate if it's a deal. i Love Excel
 
silverc said:
mine has % over spot, spot at time of purchase, $ +/- spot, etc.

Also have a calculator for making purchases - enter weight, price, postage to calculate if it's a deal. i Love Excel
Click to expand...

Yes Excel is a powerful tool, I have been using my sheet as a sort of pseudo calculator by entering the prices to see what the margin is over spot before I buy :)
 
A couple of thoughts for you all ?

Do you list each sale as an item individually ie: 2010 Kooks purchased over three purchases, are these listed as such or just added to the total as one SKU for example ?

How do you convert Kg to Oz's on this or any of the other sheets ?

Does this work for bullion only, if so what do people do for their Numismatic purchases ie: PM releases etc...

Looks like a good start, and other peoples ideas are great as well good job.
 
Austacker said:
Does this work for bullion only, if so what do people do for their Numismatic purchases ie: PM releases etc...
Click to expand...

This is something I would find very useful. A lot of my stack is made up of bullion coins so for me, the premiums are a very important component of calculating the value of my stack - without a detailed inventory, I'm only looking at the bare minimum value I'd get from tossing them into the melting pot.

For example, I'd probably value Perth Mint 1oz coins at [SPOT] + AUD$5.00 because that's what I'd get if I were to sell them.
 
For each purchase I track the following:

Purchase Details
Purchase Date Dealer State Location PM Type "Purchase Price A$" Invoice No. "Unit Weight (ozt)" Unit Qty Unit Cost "Cost per ozt A$" "Actual Spot Price A$" "Qty Purchased (ozt)" "Qty Purchased (kg)"

Then Calculate Current Value

Current Ag Value
"Qty (ozt)" "Current Value A$" Growth in Value A$ "Growth %"

Current Au Value
"Qty (ozt)" "Current Value A$" Growth in Value A$ "Growth %"

Then tally it up and graph it per asset class per entity.
 
Austacker said:
A couple of thoughts for you all ?

Do you list each sale as an item individually ie: 2010 Kooks purchased over three purchases, are these listed as such or just added to the total as one SKU for example ?

How do you convert Kg to Oz's on this or any of the other sheets ?

Does this work for bullion only, if so what do people do for their Numismatic purchases ie: PM releases etc...

Looks like a good start, and other peoples ideas are great as well good job.
Click to expand...


Hi Austacker,
I list each purchase individually, but I could another table to the sheet if you want, which could add up the total amount of coins, or bars in oz's that you have in your stack, as separate items.

For the kg to oz's conversion I use 1kg = 32.15oz's. So if you had a 1kg bar you could enter this as 32.15oz's in the "oz's" column and the formula's will still work.

As for the Numismatic purchases...well to tell you the truth I hadn't even thought about it... because I don't have any! :) Once I've got some more time on my hands I will have a look into how I can fit it into the sheet and make it work.

At the moment I'm trying to refine the automatic price update from the web, so that you don't have to enter the spot price. I'm also looking at adding drop down menus from some of the columns to streamline the sheet a bit more.

All ideas welcomed, thanks for the input, cheers :)

Haveblue.
 
aleks said:
How about another tab for gold?
Click to expand...

Working on another sheet for gold :) To start with I will copy the current format for the silver log.

Tried adding the live price update for the silver log tonight, but everytime I load the sheet the microsoft security settings prompt me to enable the data connection, which overcourse defeats the purpose of an automatic update. I know how to override this problem on my computer, but if I emailed the sheet to someone they would have they same problem. So I'm leaving the manual price update for now until I've figure out the problem.

Cheers,
Haveblue.
 
Does anyone know of a free web service that gives out spot prices on demand? I'd like to have a single spot price cell in my sheet that can be refreshed on demand i.e. a "Refresh" button that fires off a VBA macro to fetch the price. I can't find anything.
 
You must log in or register to reply here.
Back
Top