(06-29-2012 02:45 PM)harbl_the_cat Wrote:  Aaaaaah that's how you import external data... I'm so dumb!
Thanks for the .xls!

(06-30-2012 10:59 AM)Viper_99 Wrote:  Thank El, you do a lot for our community! I appreciate it.

(06-29-2012 02:40 PM)EL34 Wrote:  Spread sheet updated 01/20/13

Now has Gold and Silver plus live prices from the Kitco web site
Latest update - Kitco changed the column location for spot prices on their web page and I just fixed that
If you want to change your existing spreadsheet, here's the calculations that go into The Gold and Silver spot cells
Gold =Sheet2!J39, (it was J40)
Silver =Sheet2!J41 (it was J2)

There's a screen shot down below showing how to update your existing spread sheet

-----------------------------------------------------------------------------------------

I use an Excel spread sheet to keep track of my stack
A couple people have asked if they could get a copy and so I am attaching my .xls spreadsheet to this post

It keeps track of what I paid, spot plus or minus and actual spot paid, extra charges like shipping, etc and how much the stack is worth at current spot price.
It does bunch of calculations after you enter some basic info

There are two sheets in the spreadsheet
Sheet 2 is the captured Kitco web page where the spot prices are found. Do not delete sheet 2.

Sheet 1 - There's three sections to sheet number 1
The top section is for gold
The middle section is for US 90% silver coins
The bottom section is for silver bullion, rounds, etc

Below both sections is the grand totals for just the silver and grand totals for gold and silver combined

I have included an example for each section so you can see how it works.
I have been dialing in this spread sheet for quite a while.
Let me know how you like it or have any suggestions for improvement

BTW, this was created using Excel from Office 97 (Yes I still use office 97)

The sheet is protected so that you don't erase the formulas for the blue calculated fields.
The black fields accept data input
If you unprotect the sheet, you can edit the locked fields.
I would re-protect it again after you edit because it is very easy to erase the formulas by accident
I added sheet number two which is a data query

Sheet number two goes to Kitco and gets the current spot prices for Gold and silver
The whole sheet will update when you refresh the data from Kitco

There is nothing to edit on sheet number two because it pulls all that messy data in from a Kitco web page
The way I refresh the whole sheet in excel 97 is on the Data tool bar, I use the refresh all button
The refresh all button will update the Kitco prices and all the calculations at one time
I am not sure if it is the same in other versions of excel

The updated spread sheet is attached to this post - enjoy

The screen shot below shows the button you push to update the prices from Kitco. If this button is not on your toolbar you can add it.
It is called Refresh All and can be found in the toolbar Data menu

(01-20-2013 02:45 PM)Mark777 Wrote:  Microsoft Works
Works spreadsheet does not support hyperlinks.
Hyperlinks will be converted to plain text values.

(01-20-2013 04:54 PM)onagoth Wrote:  Nice spreadsheet, only recommendation I would make, which I have factored into mine is the buyback premium on certain items. Depending on the composition of your stack, not every item has a spot price fair value.

(01-20-2013 06:19 PM)EL34 Wrote:  

(01-20-2013 02:45 PM)Mark777 Wrote:  Microsoft Works
Works spreadsheet does not support hyperlinks.
Hyperlinks will be converted to plain text values.

I am guessing that is only sheet #2 where it fetches a Kitco page and then parses out the spot prices for Gold and Silver?
I am not familiar with newer versions of Excel, so I don't know how to fix that.
Maybe someone that knows excel better than me knows how this is done in more recent versions?

Mark, try the version I attached to this post
It does not have the Kitco price updates
you have to enter spot prices manulally
let me know if this one works

(01-20-2013 06:41 PM)Mark777 Wrote:  Yup. It appears this one worked but now I have to enter the spot prices manually.

(01-20-2013 06:48 PM)EL34 Wrote:  

(01-20-2013 06:41 PM)Mark777 Wrote:  Yup. It appears this one worked but now I have to enter the spot prices manually.

If I had a more recent version of excel I could figure it out.
Maybe they discontinued the ability to fetch hyperlinks in later versions?

(01-20-2013 06:20 PM)EL34 Wrote:  

(01-20-2013 04:54 PM)onagoth Wrote:  Nice spreadsheet, only recommendation I would make, which I have factored into mine is the buyback premium on certain items. Depending on the composition of your stack, not every item has a spot price fair value.

Not sure I understand
Buy back from who and what coins and years in particular?

(01-20-2013 06:56 PM)EL34 Wrote:  Weird, I don't know
Neither sheet1 or sheet to have a U column

Sheet 1 ends at S and Sheet 2 ends at N

(01-24-2013 07:20 AM)EL34 Wrote:  Which version of Excel are you using?

Just curious so I can figure out if the live prices works in other versions